Oracle Replication APIs
- Create a Master replication group:
BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP ( gname => '<group name>'); END;
- Drop a Master replication group:
BEGIN DBMS_REPCAT.DROP_MASTER_REPGROUP ( gname => '<group name>', drop_contents => TRUE, all_sites => TRUE); END;
- Create a Master replication object:
BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => '<group name>', type => 'TABLE', oname => '<object name>', sname => '<owner name>', use_existing_object => TRUE, copy_rows => FALSE); END;
- Drop a Master replication object:
EXECUTE DBMS_REPCAT.DROP_MASTER_REPOBJECT ( '<owner name>', '<object name>', 'TABLE');
- View objects belonging to a replication group:
SELECT * FROM ALL_REPOBJECT WHERE gname = '<group name>';
- Check status of replication group:
SELECT gname,status FROM DBA_REPGROUP;
- Generate Replication support:
BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => '<owner name>', oname => '<object name>', type => 'TABLE', min_communication => TRUE); END;
- Check status of Replication support:
SELECT sname, oname, generation_status FROM ALL_REPOBJECT WHERE GENERATION_STATUS!='GENERATED';
- Suspend replication:
BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => '<group name>'); END;
- Resume replication:
BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => '<group name>'); END;
- Add a new Master database:
BEGIN DBMS_REPCAT.ADD_MASTER_DATABASE ( gname => '<group name>', master => '<db name>', use_existing_objects => TRUE, copy_rows => FALSE, propagation_mode => 'ASYNCHRONOUS'); END;
- Remove a Master database:
On Master database to remove: EXECUTE DBMS_REPCAT.DROP_MASTER_REPGROUP ('<group name>'); On Master definition site: EXECUTE DBMS_REPCAT.REMOVE_MASTER_DATABASES ('<group name>','<db name>');
- View Master databases:
SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = '<group name>';
- Relocate a Master Group's Definition Site:
EXECUTE DBMS_REPCAT.RELOCATE_MASTERDEF ('<group name>', '<old_masterdef>', '<new_masterdef>');
- Change a Snapshot Group's Master Site:
BEGIN DBMS_REPCAT.SWITCH_MVIEW_MASTER ( gname => '<group name>', master => '<db name>'); END; Note: Put snapshot logs on the master tables at the new master site so that you can use fast refreshes.
- Refresh manually a refresh group:
EXECUTE DBMS_REFRESH.REFRESH('mviewadmin.<refresh_group>');
- Schedule push job:
BEGIN DBMS_DEFER_SYS.SCHEDULE_PUSH ( destination => '<db name>', interval => 'SYSDATE + 1/21600', next_date => SYSDATE, stop_on_error => FALSE, delay_seconds => 0, parallelism => 0); END;
- Unschedule push job:
EXECUTE DBMS_DEFER_SYS.UNSCHEDULE_PUSH ('<db name>');
- Manual push job:
EXEC DBMS_DEFER_SYS.PUSH ('<db name>');
- Schedule purge job:
BEGIN DBMS_DEFER_SYS.SCHEDULE_PURGE ( next_date => SYSDATE, interval => 'SYSDATE + 1/144', delay_seconds => 0); END;
- Unschedule purge job:
EXEC DBMS_DEFER_SYS.UNSCHEDULE_PURGE ();
- View jobs schedule:
SELECT DBLINK, JOB, LAST_DATE, NEXT_DATE, LAST_ERROR_MESSAGE FROM DEFSCHEDULE;
- Issue a DDL statement in a replication environment:
* At the master definition site:
EXEC DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ('<group name>'); EXEC DBMS_REPCAT.ALTER_MASTER_REPOBJECT( '<owner name>','<object name>','TABLE', ddl_text=> '<ddl statement>'); EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY ('<group name>');
- At the snapshot sites:
DROP MATERIALIZED VIEW <object name>; CREATE MATERIALIZED VIEW <object name> REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM <object name> WHERE ...; EXEC DBMS_REPCAT.DROP_MVIEW_REPOBJECT( '<owner name>','<object name>','SNAPSHOT'); BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => '<group name>', sname => '<owner name>', oname => '<object name>', type => 'SNAPSHOT', min_communication => TRUE); END;
- Add a new table to the replication environment:
- At the master definition site:
EXEC DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ('<group name>'); BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT ( gname => '<group name>', type => 'TABLE', oname => '<object name>', sname => '<owner name>', use_existing_object => TRUE, copy_rows => FALSE); END; BEGIN DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ( sname => '<owner name>', oname => '<object name>', type => 'TABLE', min_communication => TRUE); END; EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY ('<group name>');
- At the snapshot sites:
CREATE MATERIALIZED VIEW <object name> REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM <object name> WHERE ...; BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => '<group name>', sname => '<owner name>', oname => '<object name>', type => 'SNAPSHOT', min_communication => TRUE); END; BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.<group name>', list => '<owner name>.<object name>', lax => TRUE); END;
- Drop a table from the replication environment:
- At the master definition site:
EXEC DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ('<group name>'); EXEC DBMS_REPCAT.DROP_MASTER_REPOBJECT ('<owner name>', '<object name>', 'TABLE'); EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY ('<group name>');
- At the snapshot sites:
DROP MATERIALIZED VIEW <owner name>.<object name>; EXEC DBMS_REPCAT.DROP_MVIEW_REPOBJECT('<owner name>','<object name>','SNAPSHOT');
- More details on replication errors:
SELECT DISTINCT SCHEMANAME,PACKAGENAME,PROCNAME FROM defcall; SELECT DISTINCT PACKAGENAME FROM DEFCALL WHERE DEFERRED_TRAN_ID IN (SELECT DEFERRED_TRAN_ID FROM DEFERROR); SET SERVEROUTPUT ON; EXEC show_call('DEFERRED_TRAN_ID', CALLNO); [From DEFERROR entries]
- Re-execute a deferred transaction that did not initially complete successfully:
EXEC DBMS_DEFER_SYS.EXECUTE_ERROR ('<deferred_tran_id>','<destination>');
- Delete a transaction from the DEFERROR view:
EXEC DBMS_DEFER_SYS.DELETE_ERROR ('<deferred_tran_id>','<destination>');
- Delete a transaction from the DEFTRANDEST view:
EXEC DBMS_DEFER_SYS.DELETE_TRAN ('<deferred_tran_id>','<destination>');
- Determine Differences Between Replicated Tables:
EXEC DBMS_RECTIFIER_DIFF.DIFFERENCES (....); EXEC DBMS_RECTIFIER_DIFF.RECTIFY (....);
- View Administrative Requests:
SELECT ID, REQUEST, STATUS, MASTER, TIMESTAMP FROM DBA_REPCATLOG;
- Execute Administrative Requests:
EXECUTE DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN('<group name>', TRUE);
- Purge Administrative Requests:
EXEC DBMS_REPCAT.PURGE_MASTER_LOG (null, null, '<group name>');
- Disable Replication (Current Session):
EXEC DBMS_REPUTIL.REPLICATION_OFF;
- Re-enable Replication (Current Session):
EXEC DBMS_REPUTIL.REPLICATION_ON;
- Drop a Materialized View Group Created Manually:
BEGIN DBMS_REPCAT.DROP_MVIEW_REPGROUP ( gname => '<group name>', drop_contents => TRUE); END; Note: When you execute this procedure and are connected to the target master site or master materialized view site, the metadata for the target materialized view group at the master site or master materialized view site is removed. If you cannot connect, then clean up the master site by doing: BEGIN DBMS_REPCAT.UNREGISTER_MVIEW_REPGROUP ( gname => '<group name>', mviewsite => '<db name>'); END; BEGIN DBMS_MVIEW.UNREGISTER_MVIEW ( mviewowner => '<owner name>', mviewname => '<object name>', mviewsite => '<db name>'); END; BEGIN DBMS_MVIEW.PURGE_MVIEW_FROM_LOG ( mviewowner => '<owner name>', mviewname => '<object name>', mviewsite => '<db name>'); END;