Advanced Replication (Part 2 - Setting Up the Master Definition Database)

This posts explains how to set up Advanced Replication in the Master Definition database. First you need to set up a replication group where you define which tables to copy and where they need to be sent:

SQL> conn replication_administrator/replication_administrator

Connected.

SQL> execute dbms_repcat.create_master_repgroup -

> (gname => 'REP_TEST');

PL/SQL procedure successfully completed.

SQL> select gname, master from dba_repgroup

  2  /

GNAME                          M

------------------------------ -

REP_TEST                       Y

SQL>

You add the names of the master destination sites like this:

SQL> execute dbms_repcat.add_master_database -

> (gname => 'REP_TEST', -

>  master => 'NODE2.WORLD');

PL/SQL procedure successfully completed.

SQL>

Wait until the master destination site appears in DBA_REPSITES with the appropriate GNAME. It seems that the master definition database is included as a master destination database by default:

SQL> select gname, dblink, masterdef, master

  2  from dba_repsites

  3  /

GNAME      DBLINK          MASTERDEF MASTER

---------- --------------- --------- ------

REP_TEST   NODE1.WORLD     Y         Y

REP_TEST   NODE2.WORLD     N         Y

SQL>

I set up a user called ANDREW earlier with 2 empty tables, SYNONYMS and TABLESPACES. They need to have primary keys defined. You can arrange for them to be replicated like this:

SQL> exec dbms_repcat.create_master_repobject -

> (sname => 'ANDREW',    -

>  oname => 'SYNONYMS', -

>  type  => 'TABLE',     -

>  gname => 'REP_TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_repcat.create_master_repobject -

> (sname => 'ANDREW',    -

>  oname => 'TABLESPACES', -

>  type  => 'TABLE',     -

>  gname => 'REP_TEST');

PL/SQL procedure successfully completed.

SQL>

At this point you would normally define conflict resolution. This tells Oracle what to do if 2 master destination sites update the same row(s) simultaneously. This is not required in our situation so I will not go into it.

Next you need to generate replication support for objects in the replication group:

SQL> exec dbms_repcat.generate_replication_support -

> (sname => 'ANDREW', -

>  oname => 'SYNONYMS', -

>  type =>  'TABLE');

PL/SQL procedure successfully completed.

SQL> exec dbms_repcat.generate_replication_support -

> (sname => 'ANDREW', -

>  oname => 'TABLESPACES', -

>  type =>  'TABLE');

PL/SQL procedure successfully completed.

SQL>

Connected.

SQL>

DBA_REPCATLOG shows any problems with the replication setup. So, for example, if you decided not to bother with primary keys, you will see an ORA-23416 and I don’t think it will go away. You may need to click on your browser's zoom facility to look at the next piece of SQL:

SQL> l

  1  select distinct message from dba_repcatlog

  2* where gname = 'REP_TEST'

SQL> /

MESSAGE

----------------------------------------------------------------------------------------------

ORA-23416: table "ANDREW"."SYNONYMS" does not contain a primary key constraint

ORA-23416: table "ANDREW"."TABLESPACES" does not contain a primary key constraint

SQL>

Assuming everything is OK, DBA_REPCATLOG will eventually be empty. This can take up to 20 minutes:

SQL> select count(*) from dba_repcatlog

  2  where gname = 'REP_TEST'

  3  /

  COUNT(*)

----------

         0

SQL>

Once that happens, you can start the replication:

SQL> execute dbms_repcat.resume_master_activity -

> (gname => 'REP_TEST');

PL/SQL procedure successfully completed.

SQL>