If this query returns ‘No transactions’ in both columns, then no transactions have been propagated to the specified remote site since the statistics were last cleared.

set linesize 120;
COLUMN AV_BYTES HEADING ‘Average Bytes’ FORMAT 999999999
COLUMN AV_TRIPS HEADING ‘Average Round Trips’ FORMAT 9999999
SELECT
substr(DBLINK,1,30) Server_Name , DECODE(TOTAL_TXN_COUNT, 0, ‘No Transactions’,
round((TOTAL_BYTES_SENT + TOTAL_BYTES_RECEIVED) / TOTAL_TXN_COUNT)) AV_BYTES,
DECODE(TOTAL_TXN_COUNT, 0, ‘No Transactions’, (TOTAL_ROUND_TRIPS / TOTAL_TXN_COUNT)) AV_TRIPS
FROM DEFSCHEDULE WHERE DBLINK like ‘%&1%’;

Find out total number of error X’actions

SELECT   COUNT ( * )
FROM   (SELECT   DEFERRED_TRAN_ID,
ORIGIN_TRAN_DB,
DESTINATION,
TO_CHAR (START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’)
TIME_OF_ERROR,
ERROR_NUMBER,
ERROR_MSG
FROM   DEFERROR);

Locate Earliest date for the Error X’actions

SELECT   TO_CHAR (MAX (START_TIME), ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR
FROM   DEFERROR;

Locate Latest date for the Error X’actions

SELECT   TO_CHAR (MIN (START_TIME), ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR
FROM   DEFERROR;

Locate details of Error X’actions

COLUMN DBLINK HEADING ‘SERVER NAME’ FORMAT AAAAAA
COLUMN AVG_LATENCY  HEADING ‘Average Latency’ FORMAT 9999999
COLUMN DEFERRED_TRAN_ID HEADING ‘Deferred|Transaction|ID’ FORMAT A13
COLUMN ORIGIN_TRAN_DB HEADING ‘Origin|Database’ FORMAT A20
COLUMN DESTINATION HEADING ‘Destination|Database’ FORMAT A20
COLUMN TIME_OF_ERROR HEADING ‘Time of|Error’ FORMAT A12
COLUMN ERROR_NUMBER HEADING ‘Oracle|Error|Number’ FORMAT 999999
COLUMN ERROR_MSG HEADING ‘Oracle|Error|Message’ FORMAT A35
COLUMN RECEIVER HEADING ‘Receiver’ FORMAT A25

SELECT   DEFERRED_TRAN_ID,
ORIGIN_TRAN_DB,
DESTINATION,
TO_CHAR (START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR,
ERROR_NUMBER,
ERROR_MSG
FROM   DEFERROR
ORDER BY   START_TIME;

Deferred                                                              Oracle Oracle
Transaction   Origin               Destination          Time of        Error Error
ID            Database             Database             Error         Number Message
————- ——————– ——————– ———— ——- ———————————–
7.26.5257664  CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found
04:39:37

4.9.5280783   CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found
04:39:46

4.19.5280577  CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found

This call will delete all Errors from replication Queue i.e deferror & deftran

Also look at this slightly more complicated example. This example will first try to re-apply the error before deleting it:

Here’s how to delete all errors having XYZ.COM as a destination:

Above API can only delete records. If you had built up a massive replication queue then Deftran, DefError, DefTranDest would grow to enormous size. Even after deleting all records a simple query “select count(*) from Deftrans, DefError£ can takes hours to return results. Under this case , please run following truncate to release space back to system.

truncate table SYSTEM.def$_aqerror

truncate table SYSTEM.def$_aqcall

I am trying to add a new master database into existing replication cluster using API DBMS_REPCAT.ADD_MASTER_DATABASE. The system returned error : ORA-23493: “REPC.US.ORACLE.COM” is not a new site for extension request “57C8B3C5C100528AE0440060B0C193C6”

Full Rrror : ERROR at line 1:
ORA-23493: “REPC.US.ORACLE.COM” is not a new site for extension request
“57A6AC50B2801525E0440060B0C193C6”
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

Please see post “add-a-new-node-using-add-master-database-group-quiescing-method”

repA>select * from dba_repextensions;

.Master
Definition
EXTENSION_ID REQUEST Site?. EXP REPCATLOG_ID
——————————– ————— ———- — ————
EXTENSION_STA FLASHBACK_SCN BRE BRE PERCENTAGE_FOR_CATCHUP_MDEF
————- ————- — — —————————
CYCLE_SECONDS_MDEF PERCENTAGE_FOR_CATCHUP_NEW CYCLE_SECONDS_NEW
—————— ————————– —————–
57C8B3C5C100528AE0440060B0C193C6 ADD_NEW_MASTERS REPA.US.OR NO 17
ACLE.COM
INSTANTIATING 384340 NO NO 80
60 80 60

repB> select * from dba_repextensions;

.Master
Definition
EXTENSION_ID REQUEST Site?. EXP REPCATLOG_ID
——————————– ————— ———- — ————
EXTENSION_STA FLASHBACK_SCN BRE BRE PERCENTAGE_FOR_CATCHUP_MDEF
————- ————- — — —————————
CYCLE_SECONDS_MDEF PERCENTAGE_FOR_CATCHUP_NEW CYCLE_SECONDS_NEW
—————— ————————– —————–
57C8B3C5C100528AE0440060B0C193C6 ADD_NEW_MASTERS REPA.US.OR 15
ACLE.COM
INSTANTIATING NO NO 80
60 80 60

repC> select * from dba_repextensions;

no rows selected

Solution: BEGIN
DBMS_REPCAT.UNDO_ADD_NEW_MASTERS_REQUEST ( extension_id => ’57C8B3C5C100528AE0440060B0C193C6′ , drop_contents => FALSE);
END;

now

repA>select * from dba_repextensions;

no rows selected

repB>select * from dba_repextensions;

no rows selected

repC>select * from dba_repextensions;

no rows selected

Count of Objects replicated in Database

SELECT   base_sname “OWNER”, COUNT (UNIQUE base_oname) NUMBER_OF_OBJECTS
FROM   REPCAT$_GENERATED
GROUP BY   base_sname;

List number of replicated objects with corresponding groups

SELECT   gname Replication_Group, COUNT ( * ) NUMBER_OF_TABLES
FROM   REPCAT$_REPOBJECT
WHERE   TYPE = 2
GROUP BY   gname
ORDER BY   1;                                                              — List number of replicated objects

SELECT   COUNT ( * ) NUMBER_OF_OBJECTS, TYPE TYPE_OF_OBJECT
FROM   REPCAT$_REPOBJECT
GROUP BY   TYPE;

List replication Group and Object Name

SELECT   gname Replication_Group, oname OBJECT_NAME
FROM   REPCAT$_REPOBJECT
WHERE   TYPE = 2
ORDER BY   1

Can I locate which tables, indexes and other objects are replicated?

SELECT   BASE_TYPE “Type of Object”,
COUNT (UNIQUE base_oname) NUMBER_OF_OBJECTS
FROM   REPCAT$_GENERATED
WHERE   BASE_TYPE = &1
GROUP BY   base_type;

where BASE_TYPE 1 INDEX
2 TABLE
4 VIEW
5 SYNONYM
6 SEQUENCE
7 PROCEDURE
8 FUNCTION
9 PACKAGE
10 PACKAGE BODY
12 TRIGGER
13 TYPE
14 TYPE BODY
32 INDEX TYPE

What is a size in MB of each replicated object?

SELECT   Segment_Name TABLE_NAME, ROUND (SUM (bytes) / 1000000) Size_In_MB
FROM   dba_segments
WHERE   segment_name IN (  SELECT   UNIQUE oname FROM REPCAT$_REPOBJECT)
GROUP BY   segment_name
ORDER BY   2 DESC;

The dbms_repcat.resume_master_activity() package has an override parameter that defaults to FALSE. This parameter can be set to TRUE to allow the
group to become normal even if there are pending admin requests in DBA_REPCATLOG for this group.

This override should ONLY be used in extreme emergency situations, and ONLY when the ramifications and resolution of operating the replicated environment when those pending admin requests have not been applied are fully understood and acceptable. This parameter should not be used until you have contacted Oracle Support Services and addressed the situation. Use of this parameter can result in an invalidation of your replication environment, requiring the environment to be rebuild and / or resynchronised.

repA>SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
NOARCHIVELOG

repA> alter system set log_archive_dest_1=’location=/opt/oracle/oraarchive/repA/’
scope=spfile;

repA>show parameter log_archive_format

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_format string %t_%s.dbf

repA>alter system set log_archive_format=’repA_%s.arc’ scope=spfile;

System altered.

repA>alter system set log_archive_start=TRUE scope=spfile;

System altered.

repA>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

repA>startup mount
Total System Global Area 320563864 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes
Database mounted.

repA>alter database archivelog;

Database altered.

repA>alter database open;

Database altered.

repA>alter system switch logfile;

System altered.

repA>SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
————
ARCHIVELOG

$ ls -l
total 788
-rw-r—– 1 oracle dba 395264 Sep 25 11:06 repA_25.arc
-rw-r—– 1 oracle dba 2048 Sep 25 11:06 repA_26.arc

Dropping Multi-master Replication

Execute following SQL at Master Defn Site (here repa)
$ sqlplus repadmin/repadmin@repa

REM Disable replication
execute dbms_repcat.suspend_master_activity(gname=>’SCOTT_REPG’);

REM Delete replication groups
execute dbms_repcat.drop_master_repobject(‘SCOTT’,’EMP’,’TABLE’);
execute dbms_repcat.drop_master_repobject(‘SCOTT’ ,’DEPT’,’TABLE’);
execute dbms_repcat.drop_master_repgroup(‘SCOTT_REPG’);
execute dbms_repcat.remove_master_databases(‘SCOTT_REPG’,’repb’);

REM Remove private database links to other master databases
drop database link repb;

connect sys/repb@repb

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 links to other master databases
drop public database link repb;

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’);

I have 2 databases (repA and repB) which are part of multi master replication cluster server A and Server B. I now need to add another Server C with database name of repC. Consider this as a Live system and require a very minimum downtime.

Pre-Requisites :

Step 1> Check there are no pending X’actions at both sites repA,repB

Step 1> Find if there are any Transactions Currently Being Propagated to a Remote Master

repAA> SELECT /*+ ORDERED */ P.XID "Tran Being Propagated",
(MAX(C.CALLNO) + 1) "Number of Calls in Tran",
(P.SEQUENCE/MAX(C.CALLNO) + 1) * 100 "% Processed Calls"
FROM V$REPLPROP P, DEFCALL C
WHERE P.NAME LIKE '%SLAVE%'
AND upper(P.DBLINK) = 'REPB'
AND C.DEFERRED_TRAN_ID = P.XID
GROUP BY P.XID, P.SEQUENCE;

no rows selected

repBB> SELECT /*+ ORDERED */ P.XID "Tran Being Propagated",
(MAX(C.CALLNO) + 1) "Number of Calls in Tran",
(P.SEQUENCE/MAX(C.CALLNO) + 1) * 100 "% Processed Calls"
FROM V$REPLPROP P, DEFCALL C
WHERE P.NAME LIKE '%SLAVE%'
AND upper(P.DBLINK) = 'REPA'
AND C.DEFERRED_TRAN_ID = P.XID
GROUP BY P.XID, P.SEQUENCE;

no rows selected

Step 2> Listing the Number of Deferred Transactions for Each Destination Master Site

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

no rows selected

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

no rows selected

Step 3> Determining the Total Number of Transactions Queued for Propagation

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

Transactions Queued
——————-
0

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

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

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

SNAME M STATUS
—————————— – ———
SCOTT_REPG Y NORMAL
repAA> Alter system set job_queue_processes=0 scope=both;

repBB> Alter system set job_queue_processes=0 scope=both;

repAA> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

repBB>>select sname, master, status from sys.dba_repcat;
SNAMEM STATUS
—————————— – ———
SCOTT_REPGY NORMAL
repAA>cd /opt/oracle/oradata
repAA>ls -l
total 4
drwxr-xr-x 2 oracle dba 1024 Sep 18 16:57 repA
drwxr-xr-x 2 oracle dba 1024 Sep 18 17:08 repB

repAA>ls -l
total 2468686
-rw-r—– 1 oracle dba 1871872 Sep 23 16:47 control01.ctl
-rw-r—– 1 oracle dba 1871872 Sep 23 16:47 control02.ctl
-rw-r—– 1 oracle dba 1871872 Sep 23 16:47 control03.ctl
-rw-r–r– 1 oracle dba 20975616 Sep 23 16:47 drsys01.dbf
-rw-Br–r– 1 oracle dba 143527936 Sep 23 16:47 example01.dbf
-rw-r–r– 1 oracle dba 26218496 Sep 23 16:47 indx01.dbf
-rw-r–r– 1 oracle dba 20975616 Sep 23 16:47 odm01.dbf
-rw-r—– 1 oracle dba 104858624 Sep 23 11:08 redo01.log
-rw-r—– 1 oracle dba 104858624 Sep 23 16:47 redo02.log
-rw-r—– 1 oracle dba 104858624 Sep 22 12:19 redo03.log
-rw-r–r– 1 oracle dba 346034176 Sep 23 16:47 system01.dbf
-rw-r–r– 1 oracle dba 99618816 Sep 18 17:10 temp01.dbf
-rw-r–r– 1 oracle dba 10489856 Sep 23 16:47 tools01.dbf
-rw-r–r– 1 oracle dba 209719296 Sep 23 16:47 undotbs01.dbf
-rw-r–r– 1 oracle dba 26218496 Sep 23 16:47 users01.dbf
-rw-r–r– 1 oracle dba 39981056 Sep 23 16:47 xdb01.dbf
Note : The same timestamp for all datafiles is same & means we have consistent backup set.

*** Prepare repCC instance as a new Master to be added into repication Cluster.

repAA>

cp -Rf repAA repCC
repAA>ls -l
total 6
drwxr-xr-x 2 oracle dba 1024 Sep 18 16:57 repAA
drwxr-xr-x 2 oracle dba 1024 Sep 18 17:08 repBB
drwxr-xr-x 2 oracle dba 1024 Sep 23 16:55 repCC

I have copied initrepB.ora init file as initrepC.ora and changed all datafile paths to repCC. I aos edited job_queue_processes and set it to 0;

repAA> alter database backup controlfile to trace;

Database altered.

repCC>export ORACLE_SID=repCC
repCC>set| grep ORACLE_SID
ORACLE_SID= repCC

$ orapwd file=orapwrepCC password=repcc entries=5

repCC> conn / as sysdba
Connected to an idle instance.

repCC> startup nomount pfile=’/opt/oracle/admin/repC/pfile/initrepC.ora’;
ORACLE instance started.

Total System Global Area 320563864 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 1060864 bytes

repCC> show parameter job_queue

NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 0

repCC> show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string repAA

repCC> CREATE CONTROLFILE set DATABASE “repAA” NORESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ‘/opt/oracle/oradata/repCC/redo01.log’ SIZE 100M,
GROUP 2 ‘/opt/oracle/oradata/repCC/redo02.log’ SIZE 100M,
GROUP 3 ‘/opt/oracle/oradata/repCC/redo03.log’ SIZE 100M
DATAFILE
‘/opt/oracle/oradata/repCC/system01.dbf’,
‘/opt/oracle/oradata/repCC/undotbs01.dbf’,
‘/opt/oracle/oradata/repCC/drsys01.dbf’,
‘/opt/oracle/oradata/repCC/example01.dbf’,
‘/opt/oracle/oradata/repCC/indx01.dbf’,
‘/opt/oracle/oradata/repCC/odm01.dbf’,
‘/opt/oracle/oradata/repCC/tools01.dbf’,
‘/opt/oracle/oradata/repCC/users01.dbf’,
‘/opt/oracle/oradata/repCC/xdb01.dbf’
CHARACTER SET WE8ISO8859P1;
/

repCC>alter database open NORESETLOGS;

Database altered.

repCC> ALTER TABLESPACE TEMPORARY ADD TEMPFILE ‘/opt/oracle/oradata/repCC/TEMPORARY2.dbf’
SIZE 5242880 REUSE AUTOEXTEND OFF; 2

Tablespace altered.

repCC>select instance from v$thread;

INSTANCE
—————-
repAA

At any cost do not drop replication objects using API "EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE'); "
Just drop replication group - >
repCC>execute dbms_repcat.drop_master_repgroup('SCOTT_REPG');

Problem : At one event Oracle returned error so I used following API to drop Objects before Group.. Don't do this.
repCC>>execute dbms_repcat.drop_master_repgroup('SCOTT_REPG');
BEGIN dbms_repcat.drop_master_repgroup('SCOTT_REPG'); END;
*
ERROR at line 1:
ORA-23353: deferred RPC queue has entries for object group "PUBLIC"."SCOTT_REPG" so drop all individual objects before dropping group.

repCC>> EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'EMP', 'TABLE');
repCC> EXECUTE Dbms_Repcat.Drop_Master_Repobject('SCOTT', 'DEPT', 'TABLE');
PL/SQL procedure successfully completed.

I dropped objects using above API which were then added to repCC administrative queue. When enabled replication ,it dropped objects at repBB.
Also this method failed to move group from QUIESCED to NORMAL condition which could prove a disaster on live system.
The trick here is dropping replication group by keeping DB name as repAA and then renaming database to repCC. Please make sure jobs are set to 0.
Rename repAA instance after dropping replication group to RepCC

repCC>CREATE CONTROLFILE set DATABASE “repCC” RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ‘/opt/oracle/oradata/repCC/redo01.log’ SIZE 100M,
GROUP 2 ‘/opt/oracle/oradata/repCC/redo02.log’ SIZE 100M,
GROUP 3 ‘/opt/oracle/oradata/repCC/redo03.log’ SIZE 100M
DATAFILE
‘/opt/oracle/oradata/repCC/system01.dbf’,
‘/opt/oracle/oradata/repCC/undotbs01.dbf’,
‘/opt/oracle/oradata/repCC/drsys01.dbf’,
‘/opt/oracle/oradata/repCC/example01.dbf’,
‘/opt/oracle/oradata/repCC/indx01.dbf’,
‘/opt/oracle/oradata/repCC/odm01.dbf’,
‘/opt/oracle/oradata/repCC/tools01.dbf’,
‘/opt/oracle/oradata/repCC/users01.dbf’,
‘/opt/oracle/oradata/repCC/xdb01.dbf’
CHARACTER SET WE8ISO8859P1;
repCC>>alter database rename global_name to repCC;

Database altered.

6. Add new database instance repCC at $ORACLE_HOME/network/admin/listener.ora

LSNRCTL>reload<br>Connectingto(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bmhp01.uk.pri.o2.com)(PORT=1521)))
The command completed successfully

7. Create fresh DB links from existing master sites to new one and vice versa. Create new links at new database repC. Note repC does have links pointing to repA.

repCC – Should have links for repAA, repBB
repAA – Should have links for repBB, repCC
repBB – Should have links for repAA, repCC

repCC>>alter database rename global_name to repCC;

Database altered.
repAA>conn sys/repa@repaa as sysdba
Connected.
repBB>create public database link repcc using ‘repcc’;

Database link created.

repBB>conn sys/repb@repbb as sysdba
Connected.
repB>create public database link repcc using ‘repcc’;

Database link created.

repBB>conn sys/repc@repcc as sysdba
Connected.

repC>create public database link repbb using ‘repbb’;
create public database link repb using ‘repb’
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

repCC>alter database rename global_name to repCC;

Database altered.

repCC>create public database link repbb using ‘repbb’;

Database link created.

repAA>connect repadmin/repadmin@repaa

repAA>create database link repcc connect to repadmin identified by repadmin;

repBB>connect repadmin/repadmin@repbb

repBB>create database link repcc connect to repadmin identified by repadmin;

repBB>>select count(*) from tab@repcc;

COUNT(*)
———-
0
repCC>connect repadmin/repadmin@repcc

repCC>create database link repbb connect to repadmin identified by repadmin;

repCC>show parameter db_name

NAME TYPE VALUE
———————————— ———– ——————————
db_name string repCC

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

no rows selected
repAA>>select sname, master, status from sys.dba_repcat;

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

repAA>>BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => ‘SCOTT_REPG’);
END;
/

PL/SQL procedure successfully completed.
repAA>>select sname, master, status from sys.dba_repcat;

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

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

COUNT(*)
———-
0
repAA>>show user
USER is “REPADMIN”
repAA>> BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘scott_repg’,
master => ‘repcc’,
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
/
PL/SQL procedure successfully completed.

repAA>> 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;

GNAME SUBSTR(DBLINK,1,20) M
—————————— ——————– –
SCOTT_REPG REPAA.US.ORACLE.COM Y
SCOTT_REPG REPBB.US.ORACLE.COM N
SCOTT_REPG REPCC.US.ORACLE.COM N

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

COUNT(*)
———-
9

repBB>>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;

GNAME SUBSTR(DBLINK,1,20) M
—————————— ——————– –
SCOTT_REPG REPBB.US.ORACLE.COM N
SCOTT_REPG REPAA.US.ORACLE.COM Y
SCOTT_REPG REPCC.US.ORACLE.COM N

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

COUNT(*)
———-
0

repCC>>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;

GNAME SUBSTR(DBLINK,1,20) M
—————————— ——————– –
SCOTT_REPG REPCC.US.ORACLE.COM N
SCOTT_REPG REPAA.US.ORACLE.COM Y
SCOTT_REPG REPBB.US.ORACLE.COM N

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

COUNT(*)
———-
6

repBB> select count(*) from REPCAT$_REPOBJECT;

COUNT(*)
———-
6
repAA>select count(*) from REPCAT$_REPOBJECT;

COUNT(*)
———-
6
repCC>select count(*) from REPCAT$_REPOBJECT;
COUNT(*)
———-
0

You should wait until the DBA_REPCATLOG view is empty. This view has temporary information that is cleared after successful execution.

Run do_deferred_repcat_admin Manually to push the Admin Queue. Execute the following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:

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

COUNT(*)
———-
9
repBB>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
0
repCC>SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = ‘SCOTT_REPG’;

COUNT(*)
———-
6

The easiset method to reoslve admin request is to use grid contorl else Oracle EM. I used it at instances repAA,BB,CC to resolve pending admin requests.

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

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

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

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

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

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

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

I had to run dbms_defer_sys.push as well as do_deferred_repcat_admin at repAA/CC to get this group ito normal State

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

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

repAA

repBB

repCC

Most important Checks after a new database is added into Replication Clustyer

1. Make sure the target (remote) database does have all replication support created for objects.
select sname,oname,type,STATUS from DBA_REPOBJECT minus
select sname,oname,type,STATUS from DBA_REPOBJECT<at>remote_db;

select object_name from dba_objects where object_name like ‘%$RP%’
minus
select object_name from dba_objects<@>remote_db where object_name like ‘%$RP%’;

If above SQL return any values menas there is a replication support missing at those objects at target database. Please use post

2. If you have used SQLPLUS “RECOVER until Change ” to carry recovery at new master  then DBID would be same for remote database as existing master. This would prove a problem for rman backup.
Please use nid utility to change DBID of target Database

1. Tracking the Average Number of Row Changes in a Replication Transaction

SELECT   DECODE (TXNS_ENQUEUED,
 0, 'No Transactions Enqueued',
 (CALLS_ENQUEUED / TXNS_ENQUEUED))
 "Average Number of Row Changes"
 FROM   V$REPLQUEUE;

2. Tracking the Rate of Transactions Entering the Deferred Transactions Queue

SELECT   (R.TXNS_ENQUEUED / ( (SYSDATE - I.STARTUP_TIME) * 24 * 60 * 60))
 "Average TPS"
 FROM   V$REPLQUEUE R, V$INSTANCE I;

3. Determining the Average Network Traffic Created to Propagate a Transaction

set linesize 120;
COLUMN AV_BYTES HEADING 'Average Bytes' FORMAT 999999999
COLUMN AV_TRIPS HEADING 'Average Round Trips' FORMAT 9999999

SELECT   SUBSTR (DBLINK, 1, 30) Server_Name,
 DECODE (
 TOTAL_TXN_COUNT,
 0,
 'No Transactions',
 ROUND (
 (TOTAL_BYTES_SENT + TOTAL_BYTES_RECEIVED) / TOTAL_TXN_COUNT
 )
 )
 AV_BYTES,
 DECODE (TOTAL_TXN_COUNT,
 0, 'No Transactions',
 (TOTAL_ROUND_TRIPS / TOTAL_TXN_COUNT))
 AV_TRIPS
 FROM   DEFSCHEDULE
 WHERE   DBLINK LIKE '%C5%';

If this query returns ‘No transactions’ in both columns, then no transactions have been propagated to the specified remote site since the statistics were last cleared.

4. Determining the Average Amount of Time to Apply Transactions at Remote Sites

SELECT   AVG_LATENCY "Average Latency"
 FROM   DEFSCHEDULE
 WHERE   UPPER (DBLINK) LIKE 'REPB%';

Average latency is the average number of seconds between the first call of a transaction on the current site and the confirmation that the transaction was applied at the remote site. The

5. Clearing the Statistics for a Remote Master Site in the DEFSCHEDULE View

To clear the propagation statistics in the DEFSCHEDULE view for a particular remote master site, use

BEGIN
 DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (dblink => 'REPB');
END;

6. Determining the Transactions Currently Being Propagated to a Remote Master

SELECT                                                        /*+ ORDERED */
 P  .XID "Tran Being Propagated",
 (MAX (C.CALLNO) + 1) "Number of Calls in Tran",
 (P.SEQUENCE / MAX (C.CALLNO) + 1) * 100 "% Processed Calls"
 FROM   V$REPLPROP P, DEFCALL C
 WHERE       P.NAME LIKE '%SLAVE%'
 AND P.DBLINK = 'CEL1.WORLD'
 AND C.DEFERRED_TRAN_ID = P.XID
GROUP BY   P.XID, P.SEQUENCE;

• Listing General Information About the Purge Job
• Checking the Definition of the Purge Job
• Determining the Amount of Time Since the Last Purge
• Determining the Total Number of Purged Transactions

1. Listing General Information About the Purge Job

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN PRIV_USER HEADING ‘Privilege|Schema’ FORMAT A10
COLUMN BROKEN HEADING ‘Broken?’ FORMAT A7
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A25

SELECT JOB,
PRIV_USER,
BROKEN,
TO_CHAR(NEXT_DATE, ‘DD.MM.YYYY:HH:MI:SS AM’) next_start,
INTERVAL
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_defer_sys.purge%’
ORDER BY 1;

2. Checking the Definition of the Purge Job

SELECT WHAT “Definition of the Purge Job”
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_defer_sys.purge%’ ORDER BY 1;

3. Determining the Amount of Time Since the Last Purge

SELECT ((SYSDATE – LAST_PURGE_TIME) / 60) “Minutes Since Last Purge”
FROM V$REPLQUEUE;

4. Determining the Total Number of Purged Transactions

SELECT TXNS_PURGED “Transactions Purged”
FROM V$REPLQUEUE;

• Listing the Number of Deferred Transactions for Each Destination Master Site
• Determining the Next Start Time and Interval for the Push Jobs
• Determining the Total Number of Transactions Queued for Propagation

1. Listing the Number of Deferred Transactions for Each Destination Master Site

CONNECT repadmin/repadmin@repA

COLUMN DEST HEADING ‘Destination’ FORMAT A45
COLUMN TRANS HEADING ‘Def Trans’ FORMAT 9999

SELECT DBLINK DEST, COUNT(*) TRANS
FROM DEFTRANDEST D
GROUP BY DBLINK;

Destination Def Trans
——————————————— ———
repA 1

2. Determining the Next Start Time and Interval for the Push Jobs

CONNECT repadmin/repadmin@repA.WORLD

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN DBLINK HEADING ‘Destination’ FORMAT A22
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A25

SELECT JOB,
DBLINK,
TO_CHAR(NEXT_DATE, ‘DD-MON-YYYY HH:MI:SS AM’) next_start,
INTERVAL
FROM DEFSCHEDULE
WHERE DBLINK != (SELECT GLOBAL_NAME FROM GLOBAL_NAME)
AND JOB IS NOT NULL
ORDER BY 1;

3. Determining the Total Number of Transactions Queued for Propagation

Run the following query to display the total number of transactions in the deferred transaction queue that are waiting to be propagated:

repA>sqlplus repadmin/repadmin@repA

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

1. Listing General Information About Administrative Requests at the Master
2. Determining the Cause of Administrative Request Errors at the Master
3. Listing Information About the Job that Executes Administrative Requests at the Master

Listing General Information About Administrative Requests at the Master

COLUMN ID HEADING ‘Admin|Request|ID’ FORMAT 999999
COLUMN REQUEST HEADING ‘Request’ FORMAT A25
COLUMN STATUS HEADING ‘Status’ FORMAT A15
COLUMN MASTER HEADING ‘Master|Site’ FORMAT A25

SELECT ID, REQUEST, STATUS, MASTER FROM DBA_REPCATLOG;

Determining the Cause of Administrative Request Errors at the Master

COLUMN ID HEADING ‘Admin|Request|ID’ FORMAT 999999
COLUMN REQUEST HEADING ‘Request’ FORMAT A30
COLUMN ERRNUM HEADING ‘Error|Number’ FORMAT 999999
COLUMN MESSAGE HEADING ‘Error|Message’ FORMAT A32

SELECT ID, REQUEST, ERRNUM, MESSAGE
FROM DBA_REPCATLOG WHERE STATUS = ‘ERROR’;

Listing Information About the Job that Executes Administrative Requests at the Master

You can query the DBA_JOBS data dictionary view to list the following information about this job.

The DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN porcedure executes the local outstanding deferred administrative procedures for the specified master group at the current master site, or (with assistance from job queues) for all master sites.

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN PRIV_USER HEADING ‘Privilege|Schema’ FORMAT A10
COLUMN BROKEN HEADING ‘Broken?’ FORMAT A7
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A20

SELECT JOB,
PRIV_USER,
BROKEN,
TO_CHAR(NEXT_DATE,’DD.MM.YYYY:HH:MI:SS AM’) next_start,
INTERVAL
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_repcat.do_deferred_repcat_admin%’
ORDER BY 1;

You can retrieve following details :

• The number of unpropagated deferred transaction-destination pairs. Each deferred transaction can have multiple destinations to which it will be propagated, and each destination is a single deferred transaction-destination pair.
• The number of deferred transaction errors (error transactions) for each master group
• The number of administrative requests for each master group
• The number of administrative request errors for each master group

SELECT G.GNAME,
NVL(T.CNT1, 0) deftran,
NVL(IE.CNT2, 0) deftranerror,
NVL(A.CNT3, 0) adminreq,
NVL(B.CNT4, 0) adminreqerror
FROM
(SELECT DISTINCT GNAME FROM DBA_REPGROUP WHERE MASTER=’Y’) G,
(SELECT DISTINCT RO.GNAME, COUNT(DISTINCT D.DEFERRED_TRAN_ID) CNT1
FROM DBA_REPOBJECT RO, DEFCALL D, DEFTRANDEST TD
WHERE RO.SNAME = D.SCHEMANAME
AND RO.ONAME = D.PACKAGENAME
AND RO.TYPE IN (‘TABLE’, ‘PACKAGE’, ‘MATERIALIZED VIEW’)
AND TD.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
GROUP BY RO.GNAME ) T,
(SELECT DISTINCT RO.GNAME, COUNT(DISTINCT E.DEFERRED_TRAN_ID) CNT2
FROM DBA_REPOBJECT RO, DEFCALL D, DEFERROR E
WHERE RO.SNAME = D.SCHEMANAME
AND RO.ONAME = D.PACKAGENAME
AND RO.TYPE IN (‘TABLE’, ‘PACKAGE’, ‘MATERIALIZED VIEW’)
AND E.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
AND E.CALLNO = D.CALLNO
GROUP BY RO.GNAME ) IE,
(SELECT GNAME, COUNT(*) CNT3 FROM DBA_REPCATLOG GROUP BY GNAME) A,
(SELECT GNAME, COUNT(*) CNT4 FROM DBA_REPCATLOG
WHERE STATUS = ‘ERROR’
GROUP BY GNAME) B WHERE G.GNAME = IE.GNAME (+)
AND G.GNAME = T.GNAME (+)
AND G.GNAME = A.GNAME (+)
AND G.GNAME = B.GNAME (+) ORDER BY G.GNAME;

COLUMN GNAME HEADING ‘Master Group’ FORMAT A20
COLUMN DBLINK HEADING ‘Sites’ FORMAT A25
COLUMN MASTERDEF HEADING ‘Master|Definition|Site?’ FORMAT A10

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;

Here I am going to configure Oracle to use latest timestamp method to settle conflict ON SCOTT.EMP table’s EMPNO column.

We need to

1. Define a column group for use by the resolution method.
2. Define the resolution method.
3. Add additional support as required.

1. Define the Column Group

repA> BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => 'scott_repg');
END;

repa>execute dbms_repcat.make_column_group('SCOTT','EMP','EMP_COLGP','*');

In this example, we are defining a column group called EMP_COLGP on the EMP table in the SCOTT schema.
This column group will include every column in the table, since the ‘*’ is being used.
It is not necessary to include all columns in the column group. Since the EMPNO column can act as a primary key, we could have only defined EMPNO in our column group, which would have caused Oracle to resolve only conflicts on that column. By defining all columns in the column group, any conflict within the column group will use the conflict resolution method.

2. Define the Conflict Resolution Method

execute dbms_repcat.add_update_resolution(sname => 'SCOTT',
oname => 'EMP',
column_group => 'EMP_COLGP',
sequence => 1,
method => 'LATEST TIMESTAMP',
parameter_column_name => 'EMPNO');

Returned error
ERROR at line 1:
ORA-23325: parameter type is not TIME, TIME WITH TIME ZONE, TIMESTAMP,
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, DATE
ORA-06512: at “SYS.DBMS_REPCAT_CONF”, line 814
ORA-06512: at “SYS.DBMS_REPCAT_CONF”, line 1639
ORA-06512: at “SYS.DBMS_REPCAT”, line 307
ORA-06512: at line 2

Reason : EMPNO is not a DATETIME/TIMESTAMP field which Oracle could use in case of a Conflict. I will use SITE PRIORITY resolution method.

If we wanted to define a second method, we could use the same command, but replace the method parameters and change the sequence to equal two. Oracle will apply the two conflict resolution methods in the sequence order.

SQL> execute dbms_repcat.define_site_priority('scott_repg','SITE_PRI');
PL/SQL procedure successfully completed.

SQL> execute dbms_repcat.add_site_priority_site(‘scott_repg’,’SITE_PRI’,’repB’,10);
PL/SQL procedure successfully completed.

SQL> execute dbms_repcat.add_site_priority_site(‘scott_repg’,’SITE_PRI’,’repA’,100);
PL/SQL procedure successfully completed.

I set here repA as a primary site with strength of 100 while repB is only 10. In case of conflict Oracle should use values at SiteA.

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

repA>BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'scott',
oname => 'emp',
type => 'TABLE',
min_communication => TRUE);
END;

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

repA> update scott.emp set STATUS='SITE_A';

repA> select status from scott.emp;

STATUS
———-
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A

repB>update scott.emp set STATUS='SITE_B';

.repB>select status from scott.emp;

STATUS
———-
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B
SITE_B

Execute manually job dbms_defer_sys.push or wait for INTERVAL to see an update.

repB> select status from scott.emp;

STATUS
———-
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A
SITE_A

Please see updated COLUMN value from SITE_B to SITE_A at Site_B as a result of site priority.

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.