Special Case of Bi-Directional Replication Part 1

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Nov 18, 2015

Special Case of Bi-Directional Replication Part 1


The database in this case was restored, recovered, upgraded & migrated to an ODA (Oracle Database Appliance) server as a part of bi-directional replication streams setup.

Link to Part 2 
L
ink to Part 3
L
ink to Part 4

This is the first installment in a four part blog series:

database_replicationRestoring, recovering, upgrading and migrating are synonymous with database functioning and maintenance, however sometimes you come, across a rare and a unique case and it is always an opportunity to learn and to share with fellow database administrators. The database in this case was restored, recovered, upgraded & migrated to an ODA server as a part of bi-directional replication streams setup. Let’s look at the steps below:

Original Setup:Server 1:- Oracle Database running with version 11.1.0.7

Server 2:- Oracle Database running with version 11.1.0.7

Bi-Directional streams setup between above two mentioned servers. 

We were recently called in to help a company recover from the following scenario.

Due to an incident the company’s Server 2 crashed completely and was not totally recoverable. It had to be rebuilt from scratch. The incident had not only affected the company’s main sever but their backup was lost also.  

As a result the following requirement came into effect: 

  • Install 11.1.0.6 on server 2
  • Upgrade 11.1.0.6 to 11.1.0.7 on server 2 with same patches as in server 1
  • Restore the database on server 2 using the RMAN Backups of server 1 database
  • Setup Bi-Directional streams between server 2 & server 1
  • Upgrade database on server 2 to 11.2.0.3
  • Migrate server 2 single instance to ODA RAC Servers
  • Convert the Migrated database on ODA Servers to RAC.

As one can see that the project was huge and the database involved here was of multi-terabytes.

The challenge was to ship the huge backup pieces across wire from server 1 to server 2. This would involve a significant amount of time to restore the database on server 2.

We recommended to mount the backup file system from server 1 to server 2.

The following steps were followed to restore the bi-directional streams replicated database on server 2, once the Oracle Binaries of same patch sets were installed on servers 2 as compared to server 1.

SETTING UP the 2-way SCHEMA REPLICATION BETWEEN the 2 DB Servers for Schema abc, def 

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

Server        : linux1 [EXISTING PROD - we intend not to affect its availability]

DB     : proda 

Server        : linux2 [New Server Built]

DB     : prodb 

STEP 1:Connect to "proda" to create/configure STRMADMIN schema

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

connect / as sysdba

CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE STREAMS_TBS QUOTA UNLIMITED ON STREAMS_TBS;

GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to strmadmin; 

EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => 'strmadmin',grant_privileges => true); 

SELECT * FROM DBA_STREAMS_ADMINISTRATOR; 

          Output should like:

          +++++++++++++++++++++++++++

          USERNAME                       LOC ACC

          ------------------------------ --- ---

          STRMADMIN                      YES YES

 

STEP 2:Create DB LINK and SCHEMA INSTANTIATION on "proda"

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

NOTE: STAY CONNECTED as STRMADMIN on PREVIEWWH through Step 6 below. 

connectstrmadmin/strmadmin

CREATE DATABASE LINK prodb.world CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'prodb.world';

BEGIN

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

END;

/

BEGIN

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

END;

/ 

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

select * from DBA_CAPTURE_PREPARED_SCHEMAS;

 

STEP 3:Create APPLY and CAPTURE QUEUE on "proda"

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

-- connected as strmadmin on proda on linux1

 

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'ABC_BOS_APPLY_QT',

          queue_name  => 'ABC_BOS_APPLY_Q',

          queue_user  => 'STRMADMIN');

END;

/

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'DEF_BOS_APPLY_QT',

          queue_name  => 'DEF_BOS_APPLY_Q',

          queue_user  => 'STRMADMIN');

END;

/

 

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'ABC_BOS_CAPTURE_QT',

          queue_name  => 'ABC_BOS_CAPTURE_Q',

          queue_user  => 'STRMADMIN');

END;

/ 

BEGIN

     DBMS_STREAMS_ADM.SET_UP_QUEUE(

          queue_table => 'DEF_BOS_CAPTURE_QT',

          queue_name  => 'DEF_BOS_CAPTURE_Q',

          queue_user  => 'STRMADMIN');

END;

/

 

STEP 4:Configure PROPAGATION process on "proda"

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

-- connected as strmadmin on proda on linux1

 

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

          schema_name => 'ABC',

          streams_name => 'PROP_ABC_BOS_TO_ABC_ATL',

          source_queue_name => 'ABC_BOS_CAPTURE_Q',

          destination_queue_name => 'ABC_ATL_APPLY_Q@PRODB.WORLD',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODA.WORLD');

END;

/
BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(

          schema_name => 'DEF',

          streams_name => 'PROP_DEF_BOS_TO_DEF_ATL',

          source_queue_name => 'DEF_BOS_CAPTURE_Q',

          destination_queue_name => 'DEF_ATL_APPLY_Q@PRODB.WORLD',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODA.WORLD');

END;

/

 

--STOP THE PROPAGATION AS THE DESTINATION is YET TO BE CREATED.

 

BEGIN

  DBMS_PROPAGATION_ADM.STOP_PROPAGATION(

propagation_name  => 'PROP_ABC_BOS_TO_ABC_ATL');

END;

/ 

BEGIN

  DBMS_PROPAGATION_ADM.STOP_PROPAGATION(

propagation_name  => 'PROP_DEF_BOS_TO_DEF_ATL');

END;

/

STEP 5:Configure CAPTURE process ON "PRODA"

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

-- connected as strmadmin on proda on linux1

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'ABC',

          streams_type => 'CAPTURE',

          streams_name => 'ABC_BOS_CAPTURE',

          queue_name => 'ABC_BOS_CAPTURE_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODA.WORLD',

          inclusion_rule => TRUE);

END;

/

BEGIN

     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(

          schema_name => 'DEF',

          streams_type => 'CAPTURE',

          streams_name => 'DEF_BOS_CAPTURE',

          queue_name => 'DEF_BOS_CAPTURE_Q',

          include_dml => TRUE,

          include_ddl => FALSE,

          source_database => 'PRODA.WORLD',

          inclusion_rule => TRUE);

END;

/

-- START THE CAPTURE

BEGIN

  DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name  => 'ABC_BOS_CAPTURE');

END;

/

BEGIN

  DBMS_CAPTURE_ADM.START_CAPTURE(

capture_name  => 'DEF_BOS_CAPTURE');

END;

/ 

STEP 6:Determine the until SCN for the RMAN DUPLICATE command:

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

-- connected as sysdba on PRODA on linux1

 

selectinstance_name from v$instance;

 

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

until_scn NUMBER;

BEGIN

until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;

      DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);

END;

/

Make a note of the until SCN returned to be used in steps below. 

--PERFORM multiple LOG SWITCHES

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 

-- Insert some values in TEST table to check later for replication test

INSERT INTO ABC.TEST values ('Change after capture started at Boston',sysdate);

INSERT INTO DEF.TEST values ('Change after capture started at Boston',sysdate);

COMMIT;

Link to Part 2 
Link to Part 3
Link to Part 4

For any questions for Ravi, click below:


Ask Ravi