Oracle Advanced Replication Scripts

Remove replication support from database

spool repdel connect repadmin/repadmin REM Stop replication execute dbms_repcat.suspend_master_activity(gname=>’MYREPGRP’); REM Delete replication groups — execute dbms_repcat.drop_master_repobject(‘SCOTT’, ‘EMP’, ‘TABLE’); execute dbms_repcat.drop_master_repgroup(‘MYREPGRP’); execute dbms_repcat.remove_master_databases(‘MYREPGRP’, ‘TD2.world’); REM Remove private databse links to other master databases drop database link TD2.world; connect sys REM Remove the REPADMIN user execute dbms_defer_sys.unregister_propagator(username=>’REPADMIN’); execute dbms_repcat_admin.revoke_admin_any_schema(username=>’REPADMIN’); drop user repadmin cascade; REM Drop public database…

Apply/ Delete Errors

connect repadmin/repadmin spool apply_errors.sql select ‘exec dbms_defer_sys.execute_error(”’ || deferred_tran_id ||”’,”’|| destination || ”’)’ from deferror; spool off @apply_errors commit; spool delete_errors.sql select ‘exec dbms_defer_sys.delete_error(”’ || deferred_tran_id ||”’,”’|| destination || ”’)’ from deferror; spool off @delete_errors commit; — Deletes all errors in one go… — execute dbms_defer_sys.delete_error(NULL,NULL); — Clear entries from the local RepCatLog (DBA_REPCATLOG) –…

Show transaction details – including old and new column values

connect repadmin/repadmin CREATE OR REPLACE PROCEDURE “REPADMIN”.”SHOW_CALL” (IntxAn IN VARCHAR2, Incall IN NUMBER) IS argNo NUMBER; argtyp NUMBER; argForm NUMBER; CallNo NUMBER; tRanid VARCHAR2(30); typdsc CHAR(15); RowId_val ROWID; Char_val VARCHAR2(255); nChar_val NVARCHAR2(255); Date_val DATE; Number_val NUMBER; vArchar2_val VARCHAR2(2000); nvArchar2_val NVARCHAR2(2000); Raw_val RAW(255); arg_Name VARCHAR2(30); arg_Name_c CHAR(30); Table_Name VARCHAR2(100); col_Name VARCHAR2(100); pk_Char CHAR(1); Version VARCHAR2(10) :=…

Monitor replication status, sites and groups

connect repadmin/repadmin set pages 50000 col sname format a20 head “SchemaName” col masterdef format a10 head “MasterDef?” col oname format a20 head “ObjectName” col gname format a20 head “GroupName” col object format a35 trunc col dblink format a35 head “DBLink” col message format a25 col broken format a6 head “Broken?” prompt Replication schemas/ sites select…

Define replication groups with replication objects (more advanced)

set pages 50000 spool repdef connect repadmin/repadmin REM Create replication group for MASTERDEF site execute dbms_repcat.create_master_repgroup(‘MYREPGRP’); REM Register objects within the group execute dbms_repcat.create_master_repobject(‘SCOTT’, – ‘EMP’, ‘TABLE’, gname=>’MYREPGRP’); execute dbms_repcat.make_column_group( – sname => ‘SCOTT’, – oname => ‘EMP’, – column_group => ‘EMP_COLGRP’, – list_of_column_names => ‘EMPNO’); execute dbms_repcat.add_update_resolution( – sname => ‘SCOTT’, – oname =>…

Define replication groups with replication objects (simple)

et pages 50000 spool repdef connect repadmin/repadmin REM Create replication group for MASTERDEF site execute dbms_repcat.create_master_repgroup(‘MYREPGRP’); REM Register objects within the group execute dbms_repcat.create_master_repobject(‘SCOTT’, – ‘EMP’, ‘TABLE’, gname=>’MYREPGRP’); execute dbms_repcat.create_master_repobject(‘SCOTT’, – ‘DEPT’, ‘TABLE’, gname=>’MYREPGRP’); REM Add master desination sites execute dbms_repcat.add_master_database(‘MYREPGRP’, ‘TD2.world’); REM Generate replication support for objects within the group execute dbms_repcat.generate_replication_support(‘SCOTT’, ‘EMP’, ‘table’);…

Setup users, DB Links and schedules for Oracle Advanced Replication

set pages 50000 spool repprep connect sys — @?/rdbms/admin/catrep.sql REM Check if INIT.ORA parameters are OK for replication select name, value from sys.v_$parameter where name in (‘job_queue_processes’, ‘job_queue_interval’, ‘global_name’) REM Assign global name to the current DB alter database rename global_name to TD1.world; — Change to your DB name + domain REM Create public db…