Oracle Advanced Replication - Setup

Steps for Setting Up Advanced Replication
Step 1 - Create 2 users: REPADMIN and REPSYS.
REPADMIN will perform all administration tasks related with advanced replication. REPSYS performs operations on SYS's behalf, as required by the advanced replication packages. SYSTEM will hold the replication tables. Set up the db links and grant the users the appropriate privileges.

At the master definition site:

REM Run this first on the main database as SYS.
REM Function: sets up the advanced replication users.
REM
SET echo on
SPOOL cr_rep1.out
CONNECT sys/&sys_passwd
ALTER database rename global_name to NEMP.WORLD;

CREATE USER repsys identified by &repsys_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools;

GRANT connect,resource to repsys;
DROP public database link PRODREP.world;

CREATE PUBLIC database link PRODREP.WORLD using 'PRODREP.WORLD';
DROP database link PRODREP.WORLD;

CREATE DATABASE link PRODREP.WORLD connect to repsys identified by &repsys_passwd;

CREATE USER repadmin identified by &repadmin_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools;

GRANT dba to repadmin;
GRANT execute on dbms_defer to repadmin with grant option;
GRANT execute on dbms_defer_query to repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
EXECUTE dbms_repcat_auth.grant_surrogate_repcat('repsys');
CONNECT repadmin/&repadmin_passwd
DROP database link PRODREP.world;

CREATE DATABASE link PRODREP.WORLD connect to repadmin
identified by &repadmin_passwd;
GRANT execute on sys.dbms_defer to ACME;

CONNECT ACME/&ACME_passwd
DROP database link PRODREP.world;

CREATE DATABASE link PRODREP.WORLD connect to ACME
identified by &ACME_passwd;
SPOOL off;
Step 2 - Create the replication administration users at the master site, with the appropriate database links and database privileges.
REM To be run on the master site as SYS
REM Sites up the replication admin users and objects
REM
SET echo on
SPOOL cr_rep1.out
CONNECT sys/&sys_passwd
ALTER database rename global_name to PRODREP.WORLD;

CREATE USER repsys identified by &repsys_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools;

GRANT connect,resource to repsys;
DROP public database link NEMP.WORLD;

CREATE PUBLIC database link NEMP.WORLD using 'NEMP';
DROP database link NEMP.WORLD;

CREATE DATABASE link NEMP.WORLD connect to repsys
identified by &repsys_passwd;

CREATE USER repadmin identified by &repadmin_passwd
default tablespace tools
temporary tablespace temp
quota unlimited on tools
/

GRANT dba to repadmin;
GRANT execute on dbms_defer to repadmin with grant option;
GRANT execute on dbms_defer_query to repadmin;
EXECUTE dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
EXECUTE dbms_repcat_auth.grant_surrogate_repcat('repsys');
CONNECT repadmin/&repadmin_passwd
DROP database link NEMP.WORLD;

CREATE DATABASE link NEMP.WORLD connect to repadmin
identified by &repadmin_passwd;

GRANT execute on sys.dbms_defer to ACME;
CONNECT ACME/&ACME_passwd
DROP database link NEMP.WORLD;

CREATE DATABASE link NEMP.WORLD connect to ACME
identified by &ACME_passwd;
SPOOL off;
SET echo off
From here on, you must perform all tasks as the REPADMIN user!

Step 3 - At the master site, pre-create the objects to be replicated.
If the table is constantly changing at the master definition site, it is better to create just the structures and have the replication manager copy the rows while creating the replication objects.

Step 4 - At the master definition site, create a replication schema.
exec dbms_repcat.create_master_schema('ACME');
Step 5 - At the master definition site, create the required replication group.
exec dbms_repcat.create_master_repgroup('BILLING_RUN');
Step 6 - Add the objects to the group. For example:
exec dbms_repcat.create_master_repobject(-
SNAME=>'ACME',ONAME=>'BILLINGSMELTERREDUCTION',TYPE=>'TABLE',-
USE_EXISTING_OBJECT=>true,COPY_ROWS=>true,GNAME=>'BILLING_RUN');
Step 7 - Generate replication support for the object.
This will create the packages and triggers for advanced replication.

exec dbms_repcat.generate_replication_support('BILLING_RUN', 'BILLINGSMELTERREDUCTION','TABLE');
Step 8 - Add one or more master site to the group.
exec dbms_repcat.create_master_database(-
SNAME => 'ACME',oname => 'BILLINGSMELTERREDUCTION',type=>'TABLE',-
USE_EXISTING_OBJECT=>true,copy_rows => true,gname => 'BILLING_RUN');
At this point, there should be a jobs submitted automatically to the master site and master definition site:

JOB WHAT
---- ------------------------------------------------------------
INTERVAL LAST_DATE LAST_SEC BR NEXT_DATE NEXT_SEC
-------------------- --------- -------- -- --------- --------
41 dbms_repcat.do_deferred_repcat_admin('"ACME"', FALSE);
SYSDATE + (5/1440) 08-JUL-98 18:03:19 N 08-JUL-98 18:13:19
This job will execute the tasks listed in the view DBA_REPCATLOG. For example, add replication object, add master site, etc.

At the master definition site, the job to replicate all SQL command should also be automatically submitted. For example:

JOB WHAT
---- ------------------------------------------------------------
INTERVAL LAST_DATE LAST_SEC BR NEXT_DATE NEXT_SEC
-------------------- --------- -------- -- --------- --------
27 sys.dbms_defer_sys.execute(destination=>'PRODREP.WORLD', execute_as_user=>TRUE,
batch_size=>140);
sysdate+5/1440 08-JUL-98 18:01:12 N 08-JUL-98 18:06:12
Step 9 - Resume master activity.
Once DBA_REPCATLOG is cleared, ie, job 41 is completed successfully, you can resume master activity. Errors in executing job 41 will be recorded in DBA_REPCATLOG. To resume master activity, issue the command listed below.

Exec dbms_repcat.resume_master_activity('BILLING_RUN');
From this point the replication group should change from QUIESCED to NORMAL mode. While the database is in QUIESCED mode, only SELECT statements are allowed against the database. In NORMAL mode, all DMLs are allowed.


HAPPY LEARNING!