Special Case of Bi-Directional Replication Setup Part 2

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Nov 19, 2015

Special Case of Bi-Directional Replication Setup Part 2


In this part we will complete complete the Restoration & BI-Directional Streams Setup on the crashed Node.

Link to Part 1 
Link to Part 3
Link to Part 4

database_replication_new.jpgIntroduction:

This is the second instalment in a two part blog series. 
 Click here to read the first part. Let’s continue from where we left off in Special Case of Bi-Directional Replication part 1 (include link), we will now fulfil the requirements to restore the database on server 2 (Linux 2). We ended the last blog at Step 6 so we will start off here with Step 7.

STEP 7: USING RMAN, RESTORE/RECOVER  DB "PRODB" on LINUX2 using the backup of PRODA Until SCN noted at step 6.

The following requirements need to be met for restoring the database on server 2 (Linux 2):- 

  1. DATABASE MUST BE RESTORED AND RECOVERED UNTIL SCN Noted at Step 6
  2. Datafiles go on "/oradata1" & "/oradata2".
  3. Redo log files in "/oralogs" , UNDO TABLESPACE/TEMP TABLESPACE in "/oradata1" & "/oradata2"
  4. Archivelogs in "/oraarch".
  5. Multiplex controlfilesaccross "/oradata1" & "/oradata2"
  6. init.ora file to be used is "$ORACLE_HOME/dbs/initPRODB.ora"
  7. Mount the backup directory from Server 1 (linux1) to Server 2 (linux2) "/opt/orabck/RMAN/PRODA/bkup_pieces" . This is to avoid the huge backup pieces across the servers.

Copy any further archivelogs/arch backups needed from from Server 1 (linux1).Do not copy backup pieces which are NOT NEEDED. 

The following directories need to be created on server 2 (linux2):-

=====================================

/u01/app/oracle/admin/PRODB/pfile

/u01/app/oracle/admin/PRODB/dpdump

/u01/app/oracle/admin/PRODB/scripts

/u01/app/oracle/admin/PRODB/adump

/oradata1/PRODB/

/oradata2/PRODB

/oraarch/PRODB/flash_recovery_area

/oraarch/PRODB/arch

/etc/ORACLE/WALLETS/oracle

=====================================

The following files were copied from server 1 (linux1) to server 2 (linux2):-

================================== 

$ORACLE_HOME/network/admin/sqlnet.ora, $ORACLE_HOME/network/admin/listener.ora,

$ORACLE_HOME/network/admin/tnsnames.ora , /etc/ORACLE/WALLETS/oracle/ewallet.p12 & $ORACLE_HOME/dbs/initPRODB.ora

=================================== 

Edit the "$ORACLE_HOME/dbs/initPRODB.ora" on server 2 (linux2) with respect to the directories created on server 2 (linux2) 

Start the DB in Nomount on server 2 (linux2)

Restore controlfile and mount DB 

Disable block change tracking file

        -- ALTER DATABASE DISABLE BLOCK CHANGE TRACKING; 

Restore & Recover DB onserver 2 (linux2)

====================================
Following RMAN Restore script was used: 

ORACLE_SID=PRODB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

PATH=$ORACLE_HOME/bin:$PATH 

export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH 

rman target / <<! >> /home/oracle/restore.log 

RUN {

ALLOCATE CHANNEL C1 TYPE DISK;

ALLOCATE CHANNEL C2 TYPE DISK;

ALLOCATE CHANNEL C3 TYPE DISK;

ALLOCATE CHANNEL C4 TYPE DISK; 

SET NEWNAME FOR DATAFILE 1 TO '/oradata1/PRODB/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO '/oradata1/PRODB/sysaux01.dbf';.

SET NEWNAME FOR DATAFILE 101 TO '/oradata2/PRODB/default_lob_sewd_ts15.dbf';

SET NEWNAME FOR DATAFILE 102 TO '/oradata2/PRODB/default_lob_sewd_ts16.dbf';.

SET NEWNAME FOR TEMPFILE 1 TO '/oradata1/PRODB/temp01.dbf'; 

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL; 

RECOVER DATABASE UNTIL SCN <SCN Noted at Step 6>; 

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

RELEASE CHANNEL C4;

OPEN RESTRICTED;

}

EXIT;

!

====================================

AFTER RECOVERY ENSURE THAT THE DATABASE OPENED in RESTRICTED MODE. [DO NOT OPEN DB without RESTRICTED SESSION]

Open database in resetlog mode. 

Remove streams configuration

===================================== 

-- CONNECTED AS SYS on "PRODB" on SERVER 2 (linux2) 

EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION(); 

Validate streams component if any problem then follow below commands to drop streams component manually. 

-- CONNECTED AS STRMADMIN/STRMADMIN on SERVER 2 (linux2)

EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE('ABC_BOS_CAPTURE',TRUE);

EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE('ABC_BOS_CAPTURE',TRUE);

EXEC DBMS_CAPTURE_ADM.STOP_CAPTURE('DEF_BOS_CAPTURE',TRUE);

EXEC DBMS_CAPTURE_ADM.DROP_CAPTURE('DEF_BOS_CAPTURE',TRUE);

EXEC DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROP_ABC_BOS_TO_ABC_TPA',TRUE);

EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION('PROP_ABC_BOS_TO_ABC_TPA',TRUE);

EXEC DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROP_DEF_BOS_TO_DEF_TPA',TRUE);

EXEC DBMS_PROPAGATION_ADM.DROP_PROPAGATION('PROP_DEF_BOS_TO_DEF_TPA',TRUE);

EXEC DBMS_APPLY_ADM.STOP_APPLY('ABC_BOS_APPLY',TRUE);

EXEC DBMS_APPLY_ADM.DROP_APPLY('ABC_BOS_APPLY',TRUE);

EXEC DBMS_APPLY_ADM.STOP_APPLY('DEF_BOS_APPLY',TRUE);

EXEC DBMS_APPLY_ADM.DROP_APPLY('DEF_BOS_APPLY',TRUE); 

===================================== 

Rename database to "PRODB" 

Create spfile for PRODB on Server 2 (linux2) & start the database using same spfile 

Enable block change tracking on PRODB on Server 2 (linux2) 

Open wallet for PRODB on Server 2 (linux2)

Start listeners & make TNS-entries on both server 1 (linux1)&server 2 (linux2) 

-- CONNECTED AS SYS in PRODB on Server 2 (linux2)

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO PRODB.WORLD; 

Ensure that PRODB init parameters are set as BELOW
================================================================================

service_names                        string      PRODB.WORLD

db_name                              string      PRODB

instance_name                        string      PRODB

local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO

                                                 ST = linux2)(PORT = 1525))

================================================================================ 

-- CONNECTED AS SYS in PRODB on Server 2 (linux2)

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; 

-- CONNECTED AS SYS in PRODB on Server 2 (linux2)

-- CHECK THE VALUES IN TEST TABLE 

ALTER SESSION SET NLS_DATE_FORMAT='DD/MON/YYYY HH24:MI:SS';

select * from ABC.TEST;

select * from DEF.TEST;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NOTE: CONNECT AS STRMADMIN in PRODB on Server 2 (linux2)

THROUGH STEP 13 

STEP 8: Create DB LINK and SCHEMA INSTANTIATION on "PRODB"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

--connected as strmadmin at "PRODB on SERVER 2 (linux2)" 

connectstrmadmin/strmadmin

CREATE DATABASE LINK PRODA.WORLD CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'PRODA.WORLD' 

BEGIN

DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION('ABC','KEYS');

END;

/
BEGIN

DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION('DEF','KEYS');

END;

/

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

SQL> select * from DBA_CAPTURE_PREPARED_SCHEMAS; 

STEP 9: CREATE CAPTURE AND APPLY QUEUES on "PRODB on SERVER 2 (linux2)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--connected as strmadmin at "PRODB on SERVER 2 (linux2)"

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'ABC_ATL_APPLY_QT',

          queue_name  => 'ABC_ATL_APPLY_Q',

          queue_user  => 'STRMADMIN');

END;

/

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'DEF_ATL_APPLY_QT',

          queue_name  => 'DEF_ATL_APPLY_Q',

          queue_user  => 'STRMADMIN');

END;

/

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'DEF_ATL_CAPTURE_QT',

          queue_name  => 'DEF_ATL_CAPTURE_Q',

          queue_user  => 'STRMADMIN');

END;

/

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'ABC_ATL_CAPTURE_QT',

          queue_name  => 'ABC_ATL_CAPTURE_Q',

          queue_user  => 'STRMADMIN');

END;

/
STEP 10 : Configure PROPAGATION process "PRODB on SERVER 2 (linux2)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--connected as strmadmin at "PRODB on SERVER 2 (linux2)"

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

          schema_name => 'ABC',

          streams_name => 'PROP_ABC_ATL_TO_ABC_BOS',

          source_queue_name => 'ABC_ATL_CAPTURE_Q',

          destination_queue_name => 'ABC_BOS_APPLY_Q@PRODA.WORLD',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODB.WORLD');

END;

/

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

          schema_name => 'DEF',

          streams_name => 'PROP_DEF_TPA_TO_DEF_BOS',

          source_queue_name => 'DEF_TAMPA_CAPTURE_Q',

          destination_queue_name => 'DEF_BOS_APPLY_Q@PRODA.WORLD',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODB.WORLD');

END;

/
STEP 11: Configure CAPTURE process "PRODB on SERVER 2 (linux2)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--connected as strmadmin at "PRODB on SERVER 2 (linux2)"

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'ABC',

          streams_type => 'CAPTURE',

          streams_name => 'ABC_ATL_CAPTURE',

          queue_name => 'ABC_ATL_CAPTURE_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODB.WORLD',

          inclusion_rule => TRUE);

END;

/

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'DEF',

          streams_type => 'CAPTURE',

          streams_name => 'DEF_ATL_CAPTURE',

          queue_name => 'DEF_ATL_CAPTURE_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODB.WORLD',

          inclusion_rule => TRUE);

END;

/
-- START THE CAPTURE

BEGIN

  DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name  => 'ABC_ATL_CAPTURE');

END;

/

BEGIN

  DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name  => 'DEF_ATL_CAPTURE');

END;

STEP 12: Set the SCHEMA INSTANTIATION SCN at "PRODB on SERVER 2 (linux2)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--connected as strmadmin at "PRODB on SERVER 2 (linux2)" 

BEGIN

inst_scn := [until_scn - 1]; -- Here use the "UNTIL SCN of STEP 6 MINUS 1"

     DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

          source_schema_name => 'ABC',

          source_database_name => 'PRODA.WORLD',

          instantiation_scn =>inst_scn,

          recursive => TRUE);     

END;

/

BEGIN

inst_scn := [until_scn - 1]; -- Here use the "UNTIL SCN of STEP 6 MINUS 1"

     DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

          source_schema_name => 'DEF',

          source_database_name => 'PRODB.WORLD',

          instantiation_scn =>inst_scn,

          recursive => TRUE);     

END;

/

STEP 13: CONFIGURE AND START THE APPLY PROCESS ON "PRODB on SERVER 2 (linux2)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--connected as strmadmin at "PRODB on SERVER 2 (linux2)" 

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'ABC',

          streams_type => 'APPLY',

          streams_name => 'ABC_ATL_APPLY',

          queue_name => 'ABC_ATL_APPLY_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODA.WORLD');

END;

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'DEF',

          streams_type => 'APPLY',

          streams_name => 'DEF_ATL_APPLY',

          queue_name => 'DEF_TAMPA_APPLY_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODA.WORLD');

END;

BEGIN

     DBMS_APPLY_ADM.START_APPLY(

          apply_name => 'ABC_ATL_APPLY');

END;

/

 

BEGIN

     DBMS_APPLY_ADM.START_APPLY(

          apply_name => 'DEF_ATL_APPLY');

END;

/
STEP 14: Get the SCN number of "PRODB on SERVER 2 (linux2)"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--connect as sysdba on "PRODB on SERVER 2 (linux2)"

selectinstance_name from v$instance;

select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual; 

Record this SCN for use below. 

DISCONNECT FROM "PRODB on SERVER 2 (linux2)"and CONNECT TO "PRODA on SERVER 1 (linux1)"as strmadmin for below steps 

STEP 15: START THE PROPAGATION ON "PRODA on SERVER 1 (linux1)" that we stopped at Step 4.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

-- Connect as strmadmin on PRODA on SERVER 1 (linux1) 

BEGIN

DBMS_PROPAGATION_ADM.START_PROPAGATION (PROPAGATION_NAME => 'PROP_ABC_BOS_TO_ABC_ATL');

END;

/

BEGIN

DBMS_PROPAGATION_ADM.START_PROPAGATION (PROPAGATION_NAME => 'PROP_DEF_BOS_TO_DEF_ATL');

END;

/

STEP 16: Set the schema instantiation SCN in PRODA on SERVER 1 (linux1)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Connect as strmadmin on PRODA on SERVER 1 (linux1) 

BEGIN

     DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

          source_schema_name => 'ABC',

          source_database_name => 'PRODB.WORLD',

          instantiation_scn => [Use the SCN noted at Step 14],

          recursive => TRUE);     

END;

/

BEGIN

     DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

          source_schema_name => 'DEF',

          source_database_name => 'PRODB.WORLD',

          instantiation_scn => [Use the SCN noted at Step 14],

          recursive => TRUE);     

END;

/
STEP 17: CONFIGURE AND START THE APPLY on "PRODA on SERVER 1 (linux1)"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Connect as strmadmin on PRODA on SERVER 1 (linux1) 

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'ABC',

          streams_type => 'APPLY',

          streams_name => 'ABC_BOS_APPLY',

          queue_name => 'ABC_BOS_APPLY_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODB.WORLD');

END;

/

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'DEF',

          streams_type => 'APPLY',

          streams_name => 'DEF_BOS_APPLY',

          queue_name => 'DEF_BOS_APPLY_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODB.WORLD');

END;

BEGIN

     DBMS_APPLY_ADM.START_APPLY(

          apply_name => 'ABC_BOS_APPLY');

END;

/

BEGIN

     DBMS_APPLY_ADM.START_APPLY(

          apply_name => 'DEF_BOS_APPLY');

END;

Step 18: if everything went well, then Check the values in ABC.TEST and DEF.TEST tables "PRODB on SERVER 2 (linux2)" to see if it received values after the capture started.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select * from ABC.test union select * from DEF.test;


Conclusion:

The above sections complete the Restoration and BI-Directional Streams Setup on the crashed Node. In next parts we’ll cover the upgrade of this 11.1.0.7 BI-Directional Streams Setup to 11.2.0.3, then move the upgraded database to ASM Based 2 Node RAC Setup on ODA Servers.


Link to Part 1 
Link to Part 3
Link to Part 4


If you have any questions on the above steps then click below:
Ask Ravi