Tuesday, July 03, 2012

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>

No comments: