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.
Tweet
I wasted half a day to notice that “Not all dbms_repcat procedures have an auto-commit“ as described at 