Problem

SQL> BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'scott_repg',
master => 'repb',
use_existing_objects => TRUE,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;
/
BEGIN
*
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-00001: unique constraint (SYSTEM.REPCAT$_REPCAT_PRIMARY) violated
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 2649
ORA-06512: at "SYS.DBMS_REPCAT_RPC", line 313
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 223
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2248
ORA-06512: at "SYS.DBMS_REPCAT", line 146
ORA-06512: at line 2

Solution :

1. Delete rows from SYSTEM.REPCAT$_REPCAT view.
2. Stop push job “dbms_defer_sys.push(destination=>’REPA’)”
3. “dbms_defer_sys.push(destination=>’REPB’)” else you will receive lock errors.
4. ReEnable the jobs when done.

SQL> BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘scott_repg’);
END;
BEGIN
*
ERROR at line 1:
ORA-23419: regenerate replication support before resuming master activity
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 3528
ORA-06512: at “SYS.DBMS_REPCAT”, line 826
ORA-06512: at line 2

Run followng commands for creating rep Support

SQL> BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ’emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

SQL> BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ‘dept’,
type => ‘TABLE’,
min_communication => TRUE);
END;

SQL> BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘scott_repg’);
END;
PL/SQL procedure successfully completed.

When you want to alter a table structure you should use QUIESCE the group and use following API to replicate DDL changes at all sites.

BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ‘SCOTT’,
oname => ‘EMP’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.EMP ADD (STATUS VARCHAR2(10))’);
END;

I wanted to observe what Oracle does when we push changes without using above API

repa> ALTER TABLE SCOTT.EMP ADD (STATUS VARCHAR2(10) );
Table altered.

repa> update EMP set STATUS=’ACTIVE’ where EMPNO=7369;

repa>COMMIT:

Strange enough but no errors returned and nothing was replicated at REPB site.

repA> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
0
repA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

I then inserted a new ROW into EMP at repA site

repA> Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO, STATUS)
Values (1000, ‘SAGAR’, ‘CONSULTAN’, 7369, TO_DATE(’09/19/2008 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), 20, ‘ACTIVE’);
repa>COMMIT;

On this occassion I could see a record being replicated to site repB leaving column STATUS which was added at repA site.

Do U get the point? Oracle didn’t throw any error message at all for missing STATUS column at Site B but went ahead and only copied all data except STATUS Column.

How do we fix this Problem?

.repA>SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
—————————— ———
SCOTT_REPG NORMAL

SQL> BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘scott_repg’);
END;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

repA> SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
—————————— ———
SCOTT_REPG QUIESCED

repB> SELECT GNAME, STATUS FROM DBA_REPGROUP;

GNAME STATUS
—————————— ———
SCOTT_REPG QUIESCED

repA>BEGIN
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
sname => ‘SCOTT’,
oname => ‘EMP’,
type => ‘TABLE’,
ddl_text => ‘ALTER TABLE SCOTT.EMP ADD (STATUS VARCHAR2(10))’); 2
END; 3 4 5 6 7
8 /
BEGIN
*
ERROR at line 1:
ORA-23318: a ddl failure has occurred
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 679
ORA-06512: at “SYS.DBMS_REPCAT”, line 344
ORA-06512: at line 2

But this Statement did add a new Column STATUS at repB. I thought now changes at repA.EMP should be replicated. Next I tried genrating a replication support for EMP which returned following error

.repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ’emp’,
type => ‘TABLE’,
min_communication => TRUE); 2
END;
/
3 4 5 6 7 8 BEGIN
*
ERROR at line 1:
ORA-23308: object SCOTT.EMP does not exist or is invalid
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.DBMS_REPCAT_MAS”, line 2906
ORA-06512: at “SYS.DBMS_REPCAT”, line 766
ORA-06512: at line 2

repA> select role,source,request,message from DBA_REPCATLOG;

ROLE SOURCE REQUEST MESSAGE
MASTERDEF REPA.US.ORACLE.COM ALTER_MASTER_REPOBJECT ORA-01430: column being added already exists in table

repB>desc scott.emp;
Name Null? Type
—————————————– ——– —————————-
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
STATUS VARCHAR2(10)

repA>update EMP set STATUS=’ACTIVE’ where EMPNO= 7369;

repA>update EMP set STATUS=’ACTIVE’ where EMPNO= 7499;

At this moment I was thinking that I may see updates at STATUS COLUMN from repA but I couldn’t.

repB>select STATUS from scott.emp;

STATUS
———-

repA>select STATUS from scott.emp;

STATUS
———-
ACTIVE

ACTIVE

So how did I fixed it ?

Drop replication support for EMP table
repA>execute dbms_repcat.drop_master_repobject(‘SCOTT’,’EMP’,’TABLE’);

Create replication object with COPY_ROWS=TRUE if you want to COPY data from repA to repB.

repA>BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘scott_repg’,
type => ‘TABLE’,
oname => ‘EMP’,
sname => ‘scott’,
use_existing_object => TRUE,
copy_rows => TRUE);
END;
/

Generate replication Suport for an EMP
repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ’emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/

Start Replication & monitor the changes

repA>BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘scott_repg’);
END;
/

repA>select STATUS from scott.emp;

Status
—————
ACTIVE
ACTIVE

ACTIVE
ACTIVE

repB>select STATUS from scott.emp;

STATUS
———-
ACTIVE
ACTIVE

ACTIVE
ACTIVE

DBA_REPCATLOG describes the status for all asynchronous administrative requests and all error messages in the database.

DBA_REPCOLUMN describes the replicated columns for all the tables in the database.

DBA_REPCOLUMN_GROUP describes the column groups for all the tables in the database.

DBA_REPCONFLICT describes the conflict resolution method for all the tables in the database on which a conflict resolution method has been defined.

DBA_REPDDL contains the DDL for each replicated object in the database.

DBA_REPGENOBJECTS describes each object in the database that was generated to support replication.

DBA_REPGROUP describes all of the replication groups in the database that are being replicated

DBA_REPGROUP_PRIVILEGES contains information about the users who are registered for privileges in all the replication groups in the database.

DBA_REPGROUPED_COLUMN describes all of the columns that make up the column groups for each table in the database.

DBA_REPKEY_COLUMNS The replication key column(s) is an alternate column or group of columns, instead of the primary key, used to determine which columns of a table to compare when using row-level replication. You can set the replication key columns using the SET_COLUMNS procedure in the DBMS_REPCAT package.

DBA_REPOBJECT contains information about the objects in each replication group in the database.

DBA_REPPARAMETER_COLUMN contains information about the columns that are used to resolve conflicts for each replicated table in the database.

DBA_REPPRIORITY contains the value and priority level of each priority group member in each priority group in the database.

DBA_REPPRIORITY_GROUP describes the priority group or site priority group defined for each replication group in the database.

DBA_REPPROP indicates the technique used to propagate operations on each replicated object to the same object at another master site. This view shows all objects in the database.

DBA_REPRESOL_STATS_CONTROL describes statistics collection for conflict resolutions for all replicated tables in the database.

DBA_REPRESOLUTION indicates the methods used to resolve update, uniqueness, or delete conflicts for each table in the database that is replicated using row-level replication for a given schema.

DBA_REPRESOLUTION_METHOD lists all of the conflict resolution methods available in the database.

DBA_REPRESOLUTION_STATISTICS lists information about successfully resolved update, uniqueness, and delete conflicts for all replicated tables in the database.

DBA_REPSITES lists the members of each replication group in the database.

What objects can we Replicate?

A replication object is a database object existing on multiple servers in a distributed database system.

In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Advanced Replication enables you to replicate the following types of objects:

• Tables
• Indexes
• Views and Object Views
• Packages and Package Bodies
• Procedures and Functions
• User-Defined Types and Type Bodies
• Triggers
• Synonyms
• Index types
• User-Defined Operators

Let’s go ahead and create 2 multi master sites “repA” and “repB” replicating EMP/DEPT objects in SCOTT schema.

Log files repA, repB

Step 1> Setup Replication Environment at 2 master sites
Start 2 telnet windows and set prompts

$export PS1=”repA>”
SQLPLUS>set sqlprompt “repA>”
$export PS1=”repB>”
SQLPLUS>set sqlprompt “repB>”

Setup at site repA
[oracle@localhost ~]$ sqlplus system/repa@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 08:39:55 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
SQL> alter system set global_names=TRUE;
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

SQL> alter system set job_queue_processes = 1;
System altered.

SQL> CREATE USER repadmin IDENTIFIED BY repadmin;
User created.

SQL> BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ‘repadmin’);
END;
PL/SQL procedure successfully completed.

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;
Grant succeeded.

SQL> BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘repadmin’);
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

PL/SQL procedure successfully completed.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:26:00 2008

SQL> BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘SYSDATE + 1/24’,
delay_seconds => 0);
END;
/
PL/SQL procedure successfully completed.

Setup at site repB

[oracle@localhost ~]$ sqlplus system/repb@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:24:00 2008
SQL> CREATE USER repadmin IDENTIFIED BY repadmin;
User created.
SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
SQL> alter system set global_names=TRUE;
System altered.
SQL> show parameter job_queue_processes
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

SQL> alter system set job_queue_processes = 1;
System altered.

SQL> BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => ‘repadmin’);
END;
/
PL/SQL procedure successfully completed.

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;
Grant succeeded.

SQL> BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => ‘repadmin’);
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => ‘repadmin’,
privilege_type => ‘receiver’,
list_of_gnames => NULL);
END;
/
PL/SQL procedure successfully completed.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:26:00 2008
SQL> BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => ‘SYSDATE + 1/24’,
delay_seconds => 0);
END;
/< br >
PL/SQL procedure successfully completed.
I had problems with above settings mainly delay_second. Please read more at www.dbspecialists.com/files/presentations/mm_replication.html

PL/SQL procedure successfully completed.
Create Databse links needed between RepA and repB sitesREPB
[oracle@localhost ~]$ sqlplus system/repa@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:30:52 2008
SQL> create public database link repb using ‘repb’;
Database link created.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repa
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:31:18 2008

SQL> create database link repb connect to repadmin identified by repadmin;
Database link created.
REPB [oracle@localhost ~]$ sqlplus system/repb@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:30:52 2008

SQL> create public database link repa using ‘repa’;
Database link created.
[oracle@localhost ~]$ sqlplus repadmin/repadmin@repb
SQL*Plus: Release 11.1.0.6.0 – Production on Fri Sep 12 09:31:18 2008

SQL> create database link repa connect to repadmin identified by repadmin;
Database link created.

Step 2> Define a schedule for each database link to create scheduled links.
Create a scheduled link by defining a database link when you execute the SCHEDULE_PUSH procedure. The scheduled link determines how often your deferred transaction queue is propagated to each of the other master sites. You need to execute the SCHEDULE_PUSH procedure for each database link that you created in Step 1. The database link is specified in the destination parameter of the SCHEDULE_PUSH procedure.

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repa

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘repb’,
interval => ‘SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500);
END;
/

[oracle@localhost ~]$ sqlplus repadmin/repadmin@repb

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => ‘repa’,
interval => ‘SYSDATE + (1/144)’,
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500);
END;
/

Use “Create Master Group” to hold replication objects. I am going to use the default SCOTT schema with following 2 tables DEPT & EMP

For release 10g and up, unlock scott account using
repA> alter user scott account unlock;
User altered.
$sqlplus repadmin/repadmin@repa
repA> begin
dbms_repcat.create_master_repgroup (
gname => ‘scott_repg’);
end;
/
Follow following steps at RepA Master Definition Site
Add objects to master group. (only at RepA)
repA> BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘scott_repg’,
type => ‘TABLE’,
oname => ‘EMP’,
sname => ‘scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/
repA> BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => ‘scott_repg’,
type => ‘TABLE’,
oname => ‘DEPT’,
sname => ‘scott’,
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

Add additional master site (only at RepA)
repA>BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘scott_repg’,
master => ‘repb’,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/
PL/SQL procedure successfully completed.
You should wait until repB appears in the DBA_REPSITES view before continuing.
SQL> conn repadmin/repadmin
Connected.
SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = ‘SCOTT_REPG’;
DBLINK
———————————————————————-

REPA
REPB

In this example, the use_existing_objects parameter in the ADD_MASTER_DATABASE procedure is set to TRUE because it is assumed that the scott schema already exists at all master sites.

Step 3> Generate replication support – Site A.
repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => ‘scott’,
oname => ’emp’,
type => ‘TABLE’,
min_communication => TRUE);
END;
/
PL/SQL procedure successfully completed.

repB>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'scott',
oname => 'dept',
type => 'TABLE',
min_communication => TRUE);
END;
/

Step 4> Start Replication & monitor the changes
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'scott_repg');
END;
/

Check Replication Status
select sname, master, status from sys.dba_repcat;

SNAME MASTER STATUS
SCOTT_REPG Y NORMAL

Make sure STATUS is not returned as QUISCED

If Status <> ‘NORMAL’ then Check for Replication Errors

SELECT ONAME, REQUEST, MESSAGE FROM dba_repcatlog WHERE status= ‘ERROR’

Executed At: Master Definition Site

Replication Status: Quiesced

Step 1 Connect to the master definition site as the replication administrator.

CONNECT repadmin/repadmin@repA.world

Make sure the site you want to drop is not Master definition site for any replication group.

GNAME                          DBLINK     MASTERDEF
O5_A-B_ALL                     repA       N
O5_A-B_ALL                     repAA      N
O5_A-B_ALL                     repAAA     N
O5_A-B_ALL                     repB       N
O5_A-B_ALL                     repBB      N
O5_A-B_ALL                     repBBB     Y

O5_BROKER_A-B                  repA       N
O5_BROKER_A-B                  repAA      N
O5_BROKER_A-B                  repAAA     Y

Following SQL could be used to confirm number of replication groups was part of repAA  Master

Step 2 If the replication status is normal for the master group, then locate MASTER defn Site & change the status to quiesced.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘&1’);
END;

Enter value for 1: O5_A-B_ALL

Step 3 Remove the master site using the REMOVE_MASTER_DATABASES procedure.

You should wait until the DBA_REPCATLOG view is empty.

Execute following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:

SELECT * FROM DBA_REPCATLOG WHERE GNAME = ‘HR_REPG’
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘O5_A-B_ALL’);
END;

Here we have dropped site repA successfully.

Imagine you now want to add same node at later stage. Under this case you need to remove all replication groups and a replication support created for those tables.

For all groups execute SQL> execute dbms_repcat.drop_master_repgroup(‘&1’);
Enter value for 1: O5_A-B_ALL

PL/SQL procedure successfully completed.

If you want to remove complete replication support then look at this post

Quiescing
Quiescing is the mode that transfers a replication environment from the normal mode to the quiesced mode. While a replication environment is quiescing, the user is no longer able to execute a transaction against a replicated object, but any existing deferred transactions are propagated. Queries against a quiescing table are allowed. When all deferred transactions have been successfully propagated to their respective destinations, the replication environment proceeds to the quiesced mode.

Example : Suspending Master Activity
Whenever some administration tasks needs to be taken, such as adding an object to a replication group, you must suspend master activity for the nominated group. This will put the group on the master and master definition site into QUIESCED mode. At this stage, only SELECT statements are allowed on the objects within the group. Any other DML or DDL statements will generate ORA-23311 or ORA-23326.

ORA-23311 object group “name” is quiesced
Cause: SUSPEND_MASTER_ACTIVITY has been called before the object group has resumed normal operation.
Action: If a RESUME_MASTER_ACTIVITY request is pending, wait until it completes, and then reinvoke SUSPEND_MASTER_ACTIVITY.

ORA-23326 the system is being quiesced
Cause: A deferred remote procedure call operation was attempted while the database was quiesced.
Action: Resume database activity with the DBMS_REPCAT.RESUME_MASTER_ACTIVITY call.
For example,

How tp put system into “QUIESCED mode”
exec dbms_repcat.suspend_master_activity(‘BILLING_RUN’);
You must issue this command only on the master DEFINITION site. All master sites involved in the group will be QUIESCED accordingly.

1. Suspending Master Activity
2. Resume Master Activity
3. Applying Administration Tasks
4. Applying Transactions
5. Removing Transactions
6. Removing Errors
7. Drop Replication Object

Suspending Master Activity
Whenever some administration tasks needs to be taken, such as adding an object to a replication group, you must suspend master activity for the nominated group. This will put the group on the master and master definition site into QUIESCED mode. At this stage, only SELECT statements are allowed on the objects within the group. Any other DML or DDL statements will generate ORA-23311 or ORA-23326.

ORA-23311 object group “name” is quiesced
Cause: SUSPEND_MASTER_ACTIVITY has been called before the object group has resumed normal operation.
Action: If a RESUME_MASTER_ACTIVITY request is pending, wait until it completes, and then reinvoke SUSPEND_MASTER_ACTIVITY.
ORA-23326 the system is being quiesced
Cause: A deferred remote procedure call operation was attempted while the database was quiesced.
Action: Resume database activity with the DBMS_REPCAT.RESUME_MASTER_ACTIVITY call.

For example, exec dbms_repcat.suspend_master_activity(‘BILLING_RUN’);
You must issue this command only on the master DEFINITION site. All master sites involved in the group will be QUIESCED accordingly.

Resume Master Activity
Once administration tasks are completed, ie, there is nothing in the DBA_REPCATLOG, you may put the database back into normal mode. Again, this command must only be entered at the master DEFINITION site. For example: exec dbms_repcat.resume_master_activity(‘BILLING_RUN’);

Applying Administration Tasks
Administration tasks are queued in DBA_REPCATLOG. The job dbms_repcat.do_deferred_repcat_admin(‘”ACME”‘, FALSE); at both the master and master definition site should apply all these requests. You may choose to execute this manually, or run the job.

Applying Transactions
Transactions are queued in DEFTRAN. Each transaction has one or more corresponding DEFCALL. For a simple one-way replication, you should not have any transactions in the master site! Otherwise you need to add some conflict resolution rules. In our example setup, ACME is only using simple one-way replication.

The job sys.dbms_defer_sys.execute will apply these transactions. Again, you can wait for the job to automatically execute, or run it manually. While it is running, you should see the number of DEFCALL and DEFTRAN decrease as transactions are applied.

Removing Transactions
If transactions in the DEFTRAN are no longer required, use the command DBMS_DEFER_SYS.DELETE_TRAN to remove the given transaction. For example, if you added a replication object to a group, and removed it later on, the transactions associated with these groups may still be left in DEFTRAN. Use DBMS_DEFER_SYS.DELETE_TRAN to remove then.

In drastic situations, you can delete them from SYSTEM.DEF$_CALL. Deleting a call will remove the corresponding entry in SYSTEM.DEF$_TRAN.

Removing Errors
Errors in administration tasks, such as adding groups, are found in DBA_REPCATLOG. This view also holds admin tasks that are still pending. For example:

SELECT ONAME, REQUEST, MESSAGE
FROM dba_repcatlog WHERE status = ‘ERROR’

Once transactions are submitted, any errors are logged in the DEFERROR view. There is a procedure to reapply the error, DBMS_DEFER_SYS.EXECUTE_ERROR. Or you can delete the error using DBMS_DEFER_SYS.DELETE_ERROR. As a last resort, you can remove errors by deleting entries from the table SYSTEM.DEF$_ERROR.

Drop Replication Object
When an object no loner needs to be part of replication group, use the DBMS_REPCAT.DROP_MASTER_REPOBJECT package. For example:

exec dbms_repcat.drop_master_repobject(-
sname => ‘ACME’,oname => ‘PARENTPARTICIPANT’,type => ‘TABLE’,drop_objects=>false);

There are 2 ways to add a new master while the replication sites are suspended.
1. Adding Using ADD_MASTER_DATABASE Procedure
2. Adding with Offline Instantiation Using Export/Import

Using ADD_MASTER_DATABASE Procedure
1. Connect to the master definition site and if replication status is normal, change the status to quiesced. We need to switch off all applications before switching to Quiesced mode.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘XYZ);
END;

3. Shutdown the Master defn site or one of the master used for building new master site
4. Break the mirror and startup mount new database
5. Rename the database at init.ora and by re-creating new controlfile
6. Drop all database links at new database
7. Create fresh DB links from existing master sites to new one and vice versa

8. Use ADD_MASTER_DATABASE procedure to add the new master site.
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘XYZ’,
master => ‘new_master.world’,
use_existing_objects => FALSE,
copy_rows => FLASE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
9. Resume replication activity
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘hr_repg’);
END;

Advantage:
a. Can almost guarantee consistency of database since no updates are going through the system while new master being added
b. Less complicated than adding a new master group online

Disadvanage :
Full outage on replication cluster needed until entire work is done

Adding with Offline Instantiation Using Export/Import
1. Connect to the master definition site and if replication status is normal, change the status to quiesced. We need to switch off all applications before switching to Quiesced mode.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘XYZ);
END;

2. Begin offline instantiation procedure.
BEGIN
DBMS_OFFLINE_OG.BEGIN_INSTANTIATION (
gname => ‘XYZ’,
new_site => ‘new_site.world’);
END;

3. Shutdown the Master defn site or one of the master used for building new master site
4. Break the mirror and startup mount new database
5. Resume partial replication activity.
Because it may take some time to complete the offline instantiation process, we can resume replication activity for
the remaining master sites (excluding the new master site) by executing the RESUME_SUBSET_OF_MASTERS procedure in the DBMS_OFFLINE_OG

BEGIN
DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS (
gname => ‘XYZ’,
new_site => ‘new_site.world’);
END;

6. Rename the database at init.ora and by re-creating new controlfile
7. Drop all database links at new database
8. Create fresh DB links from existing master sites to new one and vice versa
9. Complete instantiation process.
After completing the steps at the new master site, you are ready to complete the offline instantiation process.
Executing the END_INSTANTIATION procedure in the DBMS_OFFLINE_OG package completes the process and resumes normal replication activity at all master sites. Make sure you execute the following procedure at the master definition site.

BEGIN
DBMS_OFFLINE_OG.END_INSTANTIATION (
gname => ‘XYZ’,
new_site => ‘new_site.world’);
END;

Advantage : Less outage than first method is required

Both these method are documented with oracle export/import utility and since we are carrying disk backup to create new masters , it will need a good testing.

Delete all Errors from replication sites
Check if there are any errors "select * from deferror;"

select sname, master, status from sys.dba_repcat; - make sure the status is not QUISCED
select id, TIMESTAMP, Master Status from dba_repcatlog

Now if there are any values returned then use following API and replace ID in parameter with values returned above.
repA> exec dbms_repcat.purge_master_log(&ID,’repa’,’scott_repg’);
repB> exec dbms_repcat.purge_master_log(&ID,’repa’,’scott_repg’);

Errors in administration tasks, such as adding groups, are found in DBA_REPCATLOG. This view also holds admin tasks that are still pending. For example:

SELECT ONAME,
REQUEST,
MESSAGE
FROM dba_repcatlog
WHERE status = ‘ERROR’

Once transactions are submitted, any errors are logged in the DEFERROR view. There is a procedure to reapply the error, DBMS_DEFER_SYS.EXECUTE_ERROR. Or you can delete the error using DBMS_DEFER_SYS.DELETE_ERROR. As a last resort, you can remove errors by deleting entries from the table SYSTEM.DEF$_ERROR.

The EXECUTE_ERROR procedure forces execution of a transaction that originally failed , leaving a record in DEFERROR. You might call this procedure if you have repaired the error (for example, a conflict in the advanced replication option) and you now wish to re-attempt the transaction. If another error occurs during EXECUTE_ERROR, the attempt is aborted and the last error encountered is returned as an exception. Upon successful completion, the procedure deletes the entries from the DEFERROR data dictionary view. If the original call has been applied to all other destinations, then the procedure also removes the entries from DEFCALL and DEFTRAN.

As with the DELETE_ERROR and DELETE_TRAN procedures, you may pass NULLs to indicate wildcards.

DBMS_DEFER_SYS.EXECUTE_ERROR (
deferred_tran_id IN VARCHAR2,
destination IN VARCHAR2)

deferred_tran_id
The ID number from the DefError view of the deferred transaction that you want to re-execute. If this parameter is null, all transactions queued for DESTINATION that originated from the DEFERRED_TRAN_DB are re-executed.

destination
The fully qualified database name from the DefError view of the database to which the transaction was originally queued. This parameter must not be null.

Restrictions
Note the following restrictions on calling EXECUTE_ERROR:
The destination parameter may not be NULL.
The deferred_tran_id and deferred_tran_db parameters must either both be NULL or both be NOT NULL. If they are NULL, all transactions in DEFERROR destined for destination are applied.

To delete a transaction from the DefError view . If there are not other DefTranDest or DefError entries for the transaction, the transaction is deleted from the DefTran and DefCall views as well.

DBMS_DEFER_SYS.DELETE_ERROR(
deferred_tran_id IN VARCHAR2,
destination IN VARCHAR2)

deferred_tran_id
The ID number from the DefError view of the deferred transaction that you want to remove from the DefError view. If this parameter is null, all transaction meeting the requirements of the other parameters are removed.

destination
The fully qualified database name from the DefError view of the database to which the transaction was originally queued. If this parameter is null, all transactions meeting the requirements of the other parameters are removed from the DefError view.

I have a live 4 node 4 way cluster between repA,repAA,repB & repBB instances. The node repA is Master which I want to move to repAA.

Database Release : 9.2.0.8 On HP UNIX

All Master Sites Are Available

DBMS_REPCAT.RELOCATE_MASTERDEF  was running for more than 2 hours

Observation :

• Locate which DBMS jobs are running in system
• “select /*+ RULE */ what from dba_jobs where job in( select /*+ RULE */  job  from dba_jobs_running)”
• dbms_repcat.do_deferred_repcat_admin(‘”O5_A-B”‘, FALSE);
• Observed SQLPLUS/TOAD session showing following SQL waiting for ENQUEUE Latch.SELECT  MASTER  FROM SYSTEM.repcat$_repcat WHERE sname = :b1 AND gowner = :b2  FOR UPDATEI wasted half a day to notice that  “Not all dbms_repcat procedures have an auto-commit“ as described at Note:1026571.6A commit after DBMS_REPCAT.RELOCATE_MASTERDEF solved my problem.repA>SELECT gname, substr(dblink,1,20),masterdef “Is MasterDefinition Site”
  FROM dba_repsites WHERE
  gname in(‘O1_A-B_ALL’,’O1_ERROR_A-B’)
  order by 3 desc;

  GNAME SUBSTR(DBLINK,1,20) I
  —————————— ——————– –
  O1_ERROR_A-B REPA.US.ORACLE.COM Y
  O1_A-B_ALL REPA.US.ORACLE.COM Y
  O1_ERROR_A-B REPB.US.ORACLE.COM N
  O1_A-B_ALL REPB.US.ORACLE.COM N
  O1_A-B_ALL REPBB.US.ORACLE.COM N
  O1_A-B_ALL REPAA.US.ORACLE.COM N
  O1_ERROR_A-B REPAA.US.ORACLE.COM N
  O1_ERROR_A-B REPBB.US.ORACLE.COM N

  repA>show user
  USER is “REPADMIN”
  repA>BEGIN
  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname => ‘O1_ERROR_A-B’,
  old_masterdef => ‘repA’,
  new_masterdef => ‘repAA’,
  notify_masters => TRUE,
  include_old_masterdef => TRUE);
  END;
  /

  COMMIT;

  /

  PL/SQL procedure successfully completed.

  repA>SELECT gname, substr(dblink,1,20),masterdef “Is MasterDefinition Site”
  FROM dba_repsites WHERE
  gname in(‘O1_A-B_ALL’,’O1_ERROR_A-B’)
  order by 3 desc;

  GNAME SUBSTR(DBLINK,1,20) I
  —————————— ——————– –
  O1_ERROR_A-B REPAA.US.ORACLE.COM Y
  O1_A-B_ALL REPA.US.ORACLE.COM Y
  O1_ERROR_A-B REPA.US.ORACLE.COM N
  O1_A-B_ALL REPB.US.ORACLE.COM N
  O1_A-B_ALL REPBB.US.ORACLE.COM N
  O1_A-B_ALL REPAA.US.ORACLE.COM N
  O1_ERROR_A-B REPBB.US.ORACLE.COM N
  O1_ERROR_A-B REPB.US.ORACLE.COM N

  8 rows selected.

  Relocate the master definition site Master Definition site Available

  BEGIN
  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname => ‘hr_repg’,
  old_masterdef => ‘orc1.world’,
  new_masterdef => ‘orc2.world’,
  notify_masters = > TRUE,
  include_old_masterdef => TRUE);
  END;

  Relocate the master definition site The Old Master Definition Site Not Available

  BEGIN
  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname => ‘hr_repg’,
  old_masterdef => ‘orc1.world’,
  new_masterdef => ‘orc2.world’,
  notify_masters = > TRUE,
  include_old_masterdef => FALSE);
  END;

  Reference :

  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname IN VARCHAR2,
  old_masterdef IN VARCHAR2,
  new_masterdef IN VARCHAR2,
  notify_masters IN BOOLEAN : = TRUE,
  include_old_masterdef IN BOOLEAN : = TRUE)

  gname The name of the object group whose master definition your want to relocate.

  old_masterdef The fully qualified database name of the current master definition site.

  new_masterdef The fully qualified database name of the existing master site that you want to make the new master definition site.

  notify_masters If NOTIFY_MASTERS is TRUE, the procedure synchronously multicasts the change to all masters (including OLD_MASTERDEF only if INCLUDE_OLD_MASTERDEF is TRUE). If any master does not make the change, rollback the changes at all masters.

  include_old_masterdef If NOTIFY_MASTERS is TRUE and INCLUDE_OLD_MASTERDEF is also TRUE, the old master definition site is also notified of the change.

Prerequisites for Advanced Replication

1. Must have distributed and replication option installed
Must run $ORACLE_HOME/rdbms/admin/catrep.sql as INTERNAL

2. Password for repsys, repadmin, and ACME should be the same on master and master definition site

3. Mandatory INIT.ORA parameters on both databases:
shared_pool_size: 25M
global_names=true
job_queue_processes=2
open_links=6
job_queue_interval= <less than interval for dba_jobs submitted>
job_queue_keep_connections=false

Master Definition Site
The database that has all the objects that you wish to replicate. All suspend and resume commands are to be entered from here, and will be automatically propagated to the master sites.

Master Site
The database that will hold the replicated objects. One master definition site can be replicated to a multiple
number of master definitionsites.

Note that as this is only a one way replication, inserts, updates and deletes should only be performed on the master definition site. Otherwise, a conflict resolution strategy must beimplemented.

Deferred Transactions and Remote Procedure Calls
Oracle’s advanced replication option relies primarily on deferred transactions and remote procedure calls (RPCs). When you commit a transaction against a replicated table, for example, the replication support triggers queue a deferred transaction to do your bidding in one or more remote databases.
In addition to providing many of the underpinnings for the advanced replication option, the packages used to create and manipulate deferred calls are available for direct use in yourapplications.

Package Description
DBMS_DEFER_SYS Performs administrative tasks such as scheduling,executing, and deleting queued transactions.
DBMS_DEFER Builds deferred calls.
DBMS_DEFER_QUERY Provides access to parameters passed to deferred calls, primarily for diagnostic purposes.< BR>

There are eight data dictionary views that contain data about deferred transactions and RPCs. You can query these views to determine information such as the destination of RPC calls, error messages, and scheduled execution times. Most of the packages associated with deferred calls reference and/or modify the data in these views.

Package	Description
DEFCALL	Contains information about all deferred RPCs. Queries SYSTEM.DEF$_CALL table.
DEFCALLDEST	Contains the destination database(s) for each deferred RPC in DEFCALL. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_CALLDEST, SYSTEM.DEF$_ERROR, and SYSTEM.REPCAT$_REPPROP.
DEFDEFAULTDEST	Contains the default destinations for deferred RPCs. Queries SYSTEM.DEF$_DEFAULTDEST.
DEFERROR	Contains error information for deferred calls that could not be applied at their destination. Queries SYSTEM.DEF$_ERROR.
DEFERRORCOUNT	Contains the count of errors for each destination. Queries SYSTEM.DEF$_ERROR.
DEFSCHEDULE	Contains information about the scheduling of deferred jobs. Queries SYSTEM.DEF$_DESTINATION and SYS.JOB$.
DEFTRAN	Contains information about all deferred calls. Queries SYSTEM.DEF$_CALL and SYS.USER$.
DEFTRANDEST	Contains the destination database(s) for each deferred transaction. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_ERROR, SYSTEM.DEF$_CALLDEST, and SYSTEM.REPCAT$_REPPROP

Task: We have a working setup of 4 node 4 way replication cluster built on Advanced/MultiMaster replication. 2 nodes out of these 4 nodes should be moved to a different physical location without a downtime.

Solution : Add 2 more nodes into system and run a 6 node 6 way replication for some time. Once things are stable, remove 2 nodes from cluster.

Please see Metalink NOTE:296473.1 metalink.oracle.com/CSP/ui/flash.html#tab=Home(page=Home&id=fli2gm32()),(page=KBNavigator&id=fli3rubg(viewingMode=1141&&userQuery=Adding%20Master%20Sites&searchControl=1146))

Step 1 at Each New Master Site

Setup replication at the first site if replication is not already existing

Pre-requisite

• The replication administrator at each new master site
• A scheduled link from each existing master site to each new master site
• A scheduled link from each new master site to each existing master site
• A schedule purge job at each new master site

Step 2 at Master Definition Site

Take RMAN backup of the database including the archive logs of master definition database.

Step 3. Run the SPECIFY_NEW_MASTERS procedure on the first site

repA>begin
DBMS_REPCAT.SPECIFY_NEW_MASTERS (‘scott_repg’,’REPC.US.ORACLE.COM’);
end;

This procedure specifies the master sites you intend to add to an existing replication group without quiescing the group. This procedure must be run at the master definition site of the specified master group.

If necessary, this procedure creates an extension_id that tracks the process of adding new master sites to a master group. You use this extension_id in the other procedures that you run at various stages in the process. You can view information about the extension_id in the DBA_REPSITES_NEW and DBA_REPEXTENSIONS data dictionary views.

DBMS_REPCAT.SPECIFY_NEW_MASTERS (
gname IN VARCHAR2,
{ master_list IN VARCHAR2
| master_table IN DBMS_UTILITY.DBLINK_ARRAY});

Step 4 Execute the ADD_NEW_MASTERS procedure on master definition site

repA> VARIABLE masterdef_flashback_scn NUMBER;
VARIABLE extension_id VARCHAR2(32);
begin
DBMS_REPCAT.ADD_NEW_MASTERS (
export_required => false,
available_master_list => null,
masterdef_flashback_scn => :masterdef_flashback_scn,
extension_id => :extension_id,
break_trans_to_masterdef => false,
break_trans_to_new_masters => false,
percentage_for_catchup_mdef => 80,
cycle_seconds_mdef => 60,
percentage_for_catchup_new => 80,
cycle_seconds_new => 60);
end;

repA>print masterdef_flashback_scn;

MASTERDEF_FLASHBACK_SCN
———————–
1666794

repA>print extension_id ;

EXTENSION_ID
——————————–
57A6AC50B2801525E0440060B0C193C6

repA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y QUIESCED

This procedure adds the master sites in the DBA_REPSITES_NEW data dictionary view to the master groups specified when the SPECIFY_NEW_MASTERS procedure was run. Information about these new master sites are added to the replication catalog at all available master sites.

Caution: After running this procedure, do not disable or enable propagation of the deferred transactions queue until after the new master sites are added. The DBA_REPEXTENSIONS data dictionary view must be clear before you disable or enable propagation. You can use the Replication Management tool or the SET_DISABLED procedure in the DBMS_DEFER_SYS package to disable or enable propagation.

Note:

• This step will provide the extension_id and the flashback_scn.
• The flashback_scn is need to duplicate the database in the next step.
• The extension_id can be used to find out if the steps are proceeding correctly.
• You can query the dba_repsites_new and dba_repextensions at both the sites with this extension_id

We need to use system change number (SCN) returned by the masterdef_flashback_scn parameter for the FLASHBACK_SCN export parameter. At this point Master defn site will start building up transactions for new master site.

Step 5. Create a duplicate database until the flashback_scn returned in the above step using RMAN.

RMAN>DUPLICATE TARGET DATABASE TO repC UNTIL SCN 1666794;

We are using disk to disk replication so File transfer between sites and Resync disks to a new master site.

Step 6. Change the global name of the new database

ALTER DATABASE RENAME GLOBAL_NAME TO repC;

Step 7. Create database links between the two database as per the replicaton requirement

Step 8. Execute the PREPARE_INSTANTIATED_MASTER procedure on the new master site (new database)

Note:
This procedure enables the propagation of deferred transactions from other prepared new master sites and existing master sites to the invocation master site. This procedure also enables the propagation of deferred transactions from the invocation master site to the other new master sites and existing master sites.

begin
DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER (
extension_id => :extension_id);
end;

extension_id value returned from ADD_NEW_MASTERS is used here.

Now this method is not without drawback and “The master definition site must be the same for all of the master groups”.

————– CASE STUDY- USING DISK REPLICATION INSTEAD OF RMAN NO SCN BASED RECOVERY ——————-

We have a really large database which needs to be attached into 4 way replication cluster. We could use rman but it is going to take more time to backup and restore database so we decided on the DISK replication which could copy Tera bytes in matter of hours than days.

I have 2 DB instances repA ad repB , built with 2 way replication using http://www.oracledbasupport.co.uk/2008/09/11/how-to-setup-master-sites-in-multimaster-replication/

Pre-requisite

• The replication administrator at each new master site
• A scheduled link from each existing master site to each new master site
• A scheduled link from each new master site to each existing master site
• A schedule purge job at each new master site

repA>begin
DBMS_REPCAT.SPECIFY_NEW_MASTERS (‘scott_repg’,’REPC.US.ORACLE.COM’);
end;

Since we need a master Defn site to build new instance repC, I took offline backup of repA. Before I backed up repA, I put the scott_repg into QUISCED condition.

repA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y QUIESCED

repA> VARIABLE masterdef_flashback_scn NUMBER;
VARIABLE extension_id VARCHAR2(32);
begin
DBMS_REPCAT.ADD_NEW_MASTERS (
export_required => false,
available_master_list => null,
masterdef_flashback_scn => :masterdef_flashback_scn,
extension_id => :extension_id,
break_trans_to_masterdef => false,
break_trans_to_new_masters => false,
percentage_for_catchup_mdef => 80,
cycle_seconds_mdef => 60,
percentage_for_catchup_new => 80,
cycle_seconds_new => 60);
end;

repA>print masterdef_flashback_scn;

MASTERDEF_FLASHBACK_SCN
———————–
1666794

repA>print extension_id ;

EXTENSION_ID
——————————–
57A6AC50B2801525E0440060B0C193C6

I copied repA as repC and changed database sid as repC. – “ALTER DATABASE RENAME GLOBAL_NAME TO repC;”

repC> begin
DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER (
extension_id => ’57A6AC50B2801525E0440060B0C193C6′);
end;

After executing above API I could see repA,repB,repC present at repa,repb but repC wouldn’t update like repA. Please see image below for details.

repA>select extension_id, request, masterdef, extension_status,flashback_scn from dba_repextensions;

.Master
Definition
EXTENSION_ID REQUEST Site?. EXTENSION_STA
——————————– ————— ———- ————-
FLASHBACK_SCN
————-
57A6AC50B2801525E0440060B0C193C6 ADD_NEW_MASTERS REPA.US.OR INSTANTIATING
ACLE.COM
1666794

The system is in INSTANTIATING status for last 12 hours.

I can see at one of internal view “select * from dba_repextensions” a flashback_SCN captured and I am sure it is tracked while building up x’actions at master defn. I built test setup for 2 tables with 10 rows and the system is not responding for last 12 hours. It seems gone into some sort of endless loop not sure why. None of the Oracle alrtlog have any detailed information.

repA>SELECT DBLINK DEST, COUNT(*) TRANS
FROM DEFTRANDEST D
GROUP BY DBLINK; 2 3

no rows selected

repA>SELECT COUNT(DISTINCT DEFERRED_TRAN_ID) “Transactions Queued” FROM DEFTRANDEST;

Transactions Queued
——————-
0

Why ? This sould happen.

If you see above documents you will know I really didn’t carry a SCN based recovery. Also before the backup wastaken scott_repg was put into QUIESCED mode. No x’actions were allowed though.

I can see at one of internal view “select * from dba_repextensions” a flashback_SCN captured and I am sure it is tracked while building up x’actions at master defn. I built test setup for 2 tables with 10 rows and the system is not responding for last 1 hour. It seems gone into some sort of endless loop not sure why. None of the Oracle alrtlog have any detailed information.

I tried getting scott_repg out of QUIESCED mode but didn’t work.

repA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y QUIESCED

repA> BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname => 'scott_repg'); END;<pre>

PL/SQL procedure successfully completed.

repA>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y QUIESCED

SELECT GNAME, DBLINK, MASTERDEF
FROM DBA_REPSITES
WHERE MASTER = ‘Y’
AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = ‘Y’)
ORDER BY GNAME; returns

————– CASE STUDY- USING SCN BASED RECOVERY with DISK REPLICATION ——————-

Make sure the replicated clustered Databases repA,repB are in ARCHIVELOG mode.

Use posts http://www.oracledbasupport.co.uk/enable-archivelog-put-database-into-archive-mode/ & http://www.oracledbasupport.co.uk/how-to-setup-2-way-multimaster-replication/

repB>select sname, master, status from sys.dba_repcat;

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL

repA>conn system/repa@repa
Connected.
repA>create public database link repc using ‘repc’;

Database link created.

repA>conn repadmin/repadmin@repa
Connected.
repA>create database link repc connect to repadmin identified by repadmin;

repA>conn system/repb@repb
Connected.
repA>create public database link repc using ‘repc’;

Database link created.

repA>conn repadmin/repadmin@repb
Connected.
repA>create database link repc connect to repadmin identified by repadmin;

repB>select db_link from dba_db_links;

DB_LINK
——————————————————————————–
REPA.US.ORACLE.COM
REPC.US.ORACLE.COM
REPA.US.ORACLE.COM
REPC.US.ORACLE.COM

repA>SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
ARCHIVELOG

repA>begin
DBMS_REPCAT.SPECIFY_NEW_MASTERS (‘scott_repg’,’REPC’);
end;

repA & repB>select sname, master, status from sys.dba_repcat;

SNAME                          M STATUS
—————————— – ———
SCOTT_REPG                     Y NORMAL

repA> VARIABLE masterdef_flashback_scn NUMBER;
VARIABLE extension_id VARCHAR2(32);
begin
DBMS_REPCAT.ADD_NEW_MASTERS (
export_required => false,
available_master_list => null,
masterdef_flashback_scn => :masterdef_flashback_scn,
extension_id => :extension_id,
break_trans_to_masterdef => false,
break_trans_to_new_masters => false,
percentage_for_catchup_mdef => 80,
cycle_seconds_mdef => 60,
percentage_for_catchup_new => 80,
cycle_seconds_new => 60);
end;

repA>print masterdef_flashback_scn;

MASTERDEF_FLASHBACK_SCN
———————–
232079

repA>print extension_id ;

EXTENSION_ID
——————————–
582FD00C0B00208CE0440060B0C193C6
repA>select sname, master, status from sys.dba_repcat;

SNAME                          M STATUS
—————————— – ———
SCOTT_REPG                     Y NORMAL
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

repA>SELECT GNAME, substr(DBLINK,1,20), MASTERDEF
FROM DBA_REPSITES
WHERE MASTER = ‘Y’
AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = ‘Y’)
ORDER BY GNAME;

.Master
Definition
GNAME .Sites. Site?.
—————————— ————————- ———-
SCOTT_REPG REPA.US.ORACLE.COM Y
SCOTT_REPG REPB.US.ORACLE.COM N
SCOTT_REPG REPC.US.ORACLE.COM N

repB>SELECT GNAME, DBLINK, MASTERDEF
FROM DBA_REPSITES
WHERE MASTER = ‘Y’
AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = ‘Y’)
ORDER BY GNAME;

.Master
Definition
GNAME .Sites. Site?.
—————————— ————————- ———-
SCOTT_REPG REPB.US.ORACLE.COM N
SCOTT_REPG REPA.US.ORACLE.COM Y
SCOTT_REPG REPC.US.ORACLE.COM N

repA>select extension_id, request, masterdef, extension_status,flashback_scn from dba_repextensions;

EXTENSION_ID                     REQUEST
——————————– —————
MASTERDEF
——————————————————————————–
EXTENSION_STA FLASHBACK_SCN
————- ————-
582FD00C0B00208CE0440060B0C193C6 ADD_NEW_MASTERS
REPA.US.ORACLE.COM
INSTANTIATING        232079

repB>select extension_id, request, masterdef, extension_status,flashback_scn from dba_repextensions;

EXTENSION_ID                     REQUEST
——————————– —————
MASTERDEF
——————————————————————————–
EXTENSION_STA FLASHBACK_SCN
————- ————-
582FD00C0B00208CE0440060B0C193C6 ADD_NEW_MASTERS
REPA.US.ORACLE.COM
INSTANTIATING

repA>SELECT COUNT(*) FROM DBA_REPCATLOG;

COUNT(*)
———-
1

repB>SELECT COUNT(*) FROM DBA_REPCATLOG;

COUNT(*)
———-
0
repA>select substr(extension_id,1,35),gname,substr(DBlink,1,20), full_instantiation  from DBA_REPSITES_NEW;

SUBSTR(EXTENSION_ID,1,35)        GNAME
——————————– ——————————
SUBSTR(DBLINK,1,20)  F
——————– –
582FD00C0B00208CE0440060B0C193C6 SCOTT_REPG
REPC.US.ORACLE.COM   Y

repB>select substr(extension_id,1,35),gname,substr(DBlink,1,20), full_instantiation  from DBA_REPSITES_NEW;

SUBSTR(EXTENSION_ID,1,35)        GNAME
——————————– ——————————
SUBSTR(DBLINK,1,20)  F
——————– –
582FD00C0B00208CE0440060B0C193C6 SCOTT_REPG
REPC.US.ORACLE.COM   Y

All master sites instantiated with object-level export/import must be accessible at this time. Their new replication groups are added in the quiesced state. Master sites instantiated through full database export/import or through changed-based recovery do not need to be accessible.

repA> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
repA is now shutdown and copied as repC

repA> cp -Rf  repA repC
I willnow carry a SCN based recovery of database for SCN returned  232079

SQL> STARTUP NOMOUNT pfile=’/opt/oracle/admin/repC/pfile/initrepC.ora’;
ORACLE instance started.

Total System Global Area  320562952 bytes
Fixed Size                   734984 bytes
Variable Size             285212672 bytes
Database Buffers           33554432 bytes
Redo Buffers                1060864 bytes
SQL> CREATE CONTROLFILE reuse DATABASE “repA” NORESETLOGS  ARCHIVELOG
MAXLOGFILES 50  2
3      MAXLOGMEMBERS 5
4      MAXDATAFILES 100
5      MAXINSTANCES 1
6      MAXLOGHISTORY 113
LOGFILE
7    GROUP 1 ‘/opt/oracle/oradata/repC/redo01.log’  SIZE 100M,  8
9    GROUP 2 ‘/opt/oracle/oradata/repC/redo02.log’  SIZE 100M,
GROUP 3 ‘/opt/oracle/oradata/repC/redo03.log’  SIZE 100M 10
DATAFILE
11    ‘/opt/oracle/oradata/repC/system01.dbf’,
12    ‘/opt/oracle/oradata/repC/undotbs01.dbf’, 13
14    ‘/opt/oracle/oradata/repC/drsys01.dbf’,
‘/opt/oracle/oradata/repC/example01.dbf’,
15    ‘/opt/oracle/oradata/repC/indx01.dbf’, 16
‘/opt/oracle/oradata/repC/odm01.dbf’, 17
18    ‘/opt/oracle/oradata/repC/tools01.dbf’,
‘/opt/oracle/oradata/repC/users01.dbf’,
19    ‘/opt/oracle/oradata/repC/xdb01.dbf’
20  CHARACTER SET WE8ISO8859P1
21  ; 22

Control file created.

repC> select first_change#-1 from v$log where status=’CURRENT’;

no rows selected

repC>  recover database until CHANGE 232079;
Media recovery complete.

Let’s drop the DB link at new repC instance and build links at repC to repA

SQL> drop database link REPC;
drop database link REPC
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

repC>  alter database rename global_name to test;

Database altered.

repC> drop public database link repc;

Database link dropped.

repC>  alter database rename global_name to repC;

Database altered.

SQL> create public database link repa using ‘repa’;

Database link created.

SQL> conn repadmin/repadmin@repc
Connected.
SQL> create database link repa connect to repadmin identified by repadmin;

repC>select count(*) from tab@repb;

COUNT(*)
———-
0

repC>select count(*) from tab@repa;

COUNT(*)
———-
0
repC>EXECUTE Dbms_Repcat.Drop_Master_Repgroup(‘scott_repg’);

PL/SQL procedure successfully completed.
repC>BEGIN
DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER (
extension_id  => ‘582FD00C0B00208CE0440060B0C193C6’);
END;
/  2    3    4    5
BEGIN
*
ERROR at line 1:
ORA-23493: “REPC.US.ORACLE.COM” is not a new site for extension request
“582FD00C0B00208CE0440060B0C193C6”
ORA-01403: no data found
ORA-06512: at “SYS.DBMS_REPCAT_ADD_MASTER”, line 2640
ORA-06512: at “SYS.DBMS_REPCAT”, line 1200
ORA-06512: at line 2

Opened up a TAR with Oracle Support and looking for a resolution.