Special Case of Bi-Directional Replication Part 4

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Dec 3, 2015

Special Case of Bi-Directional Replication Part 4


In this part we walk through the steps to upgrade 11.2.0.3 single instance database having bi-directional streams setup to RAC machine having ODA setup.

Link to Part 1

Link to Part 2

Link to Part 3

Introduction:
This is the last part of this Blog series. In this part we walk through the steps to upgrade 11.2.0.3 single instance database having bi-directional streams setup to RAC machine having ODA setup.

Once the database is moved to RAC machine, we’ll convert that database to 2 Node RAC Database & re-enable the bi-directional Streams setup.

Upgrade_part4

Move Steps:

########################################################

STEP 1: Create pfile on the ODA Server (RAC1) for PRODB database as below
-----------------------------------------------------------------

*._ENABLE_NUMA_SUPPORT=FALSE

*._FILE_SIZE_INCREASE_INCREMENT=2143289344

*.audit_file_dest='/u01/app/oracle/admin/PRODB/adump'

*.audit_sys_operations=TRUE

*.audit_trail='db'

*.compatible='11.2.0.3.0'

*.control_files='+DATA/PRODB/control01.ctl','+RECO/PRODB/control02.ctl'

*.cpu_count=12

*.db_block_checking='FULL'

*.db_block_checksum='FULL'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_create_online_log_dest_1='+REDO'

*.db_domain=''

*.db_files=1024

*.db_lost_write_protect='TYPICAL'

*.db_name='PRODB'

*.db_recovery_file_dest='+RECO'

*.db_recovery_file_dest_size=1932735283200

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODBXDB)'

*.fast_start_mttr_target=300

*.filesystemio_options='setall'

*.global_names=TRUE

*.log_archive_format='%t_%s_%r.dbf'

*.log_buffer=67108864

*.open_cursors=1000

*.os_authent_prefix=''

*.parallel_adaptive_multi_user=FALSE

*.parallel_execution_message_size=16384

*.parallel_min_servers=0

*.parallel_threads_per_cpu=2

*.pga_aggregate_target=12884901888

*.processes=1200

*.remote_login_passwordfile='exclusive'

*.session_cached_cursors=100

*.sga_target=25769803776

*.sql92_security=TRUE

*.undo_retention=900

*.undo_tablespace='UNDOTBS1'

*.use_large_pages='ONLY'

STEP 2: On SERVER 2 "PRODB" database, Disable Block change tracking if not disable:

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

SQL> select status from v$block_change_tracking;

STATUS

----------

ENABLED

STEP 3: Stop the replication between (SERVER 1) & (SERVER 2). Record the Streams Process from Both the Servers.

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

STEP 4: CLEAN SHUTDOWN and Start up the database (PRODB ON SERVER 2) in mount mode & perform RMAN COLD Backup of the Database.

(Make sure we have ample free space in the backup mount point).Take Redo details as well.

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

shutdown immediate

startup mount;- TAKE CURRENT STATUS OF ONLINE REDO LOGS

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

 set lines 134

 col member for a35

 select a.GROUP#,THREAD# ,SEQUENCE#,MEMBERS,a.STATUS,member from v$log a,v$logfile b where a.GROUP#=b.GROUP#;

 

Note "Note the redo Log Details".

--RMAN COLD BACKUP of PRODB database -- RUN THIS IN BACKGROUND

rman target /

RMAN> RUN

{

ALLOCATE CHANNEL cha1 TYPE DISK;

ALLOCATE CHANNEL cha2 TYPE DISK;

ALLOCATE CHANNEL cha3 TYPE DISK;

ALLOCATE CHANNEL cha4 TYPE DISK;

ALLOCATE CHANNEL cha5 TYPE DISK;

ALLOCATE CHANNEL cha6 TYPE DISK;

ALLOCATE CHANNEL cha7 TYPE DISK;

ALLOCATE CHANNEL cha8 TYPE DISK;

BACKUP DATABASE FORMAT '/opt/b590_orabck/RMAN/PRODB/bkup_pieces/PRODB_%U' TAG "Before_Migration";

Release CHANNEL cha1;

Release CHANNEL cha2;

Release CHANNEL cha3;

Release CHANNEL cha4;

Release CHANNEL cha5;

Release CHANNEL cha6;

Release CHANNEL cha7;

Release CHANNEL cha8;

};

 

 --AGAIN CHECK CURRENT STATUS OF ONLINE REDO LOGS

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

 set lines 134

 col member for a35

 select a.GROUP#,THREAD# ,SEQUENCE#,MEMBERS,a.STATUS,member from v$log a,v$logfile b where a.GROUP#=b.GROUP#

Note "Again note the redo Log details".

STEP 5 : Shut down the PRODB database on SERVER 2 and perform OS backup of REDO and Controlfiles:-

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

IMPORTANT: AT THIS POINT, ONCE PRODB database is SHUTDOWN, we will NOT START it again ,

IF SERVER 2 starts, even by an accident, Streams will start on PRODB, and propagate to PRODA on SERVER 1 and ODA DB will be USELESS.

-- logon to PRODB on SERVER 2

SQL> shutdown immediate;

SQL> exit

-- On SERVER 2 , ENSURE NO PMON and also RENAME the ORACLE HOME to prevent PRODB DB Accidental Startup

$ ps -ef | grep pmon

$ mv /u01/app/oracle/product/11.2.0.3/dbhome_1 /u01/app/oracle/product/11.2.0.3/dbhome_1_PREVENT_PRODB_STARTUP

-- on SERVER 2

cp /oradata1/PRODB/control01.ctl /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oradata1/PRODB/control02.ctl /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oradata2/PRODB/control03.ctl /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oralogs/PRODB/redo31.dbf /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oralogs/PRODB/redo21.dbf /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oralogs/PRODB/redo11.dbf /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oralogs/PRODB/redo12.dbf /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oralogs/PRODB/redo22.dbf /opt/b590_orabck/RMAN/PRODB/bkup_pieces

cp /oralogs/PRODB/redo32.dbf /opt/b590_orabck/RMAN/PRODB/bkup_pieces

STEP 6 : Get the /opt/b590_orabck mount point from SERVER 2 to ODA server (RAC1). Follow-up with sysadmins for this.

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

ASM DGs have before Free space details

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME

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

+ASM1            RAC1

SQL> select name,total_mb,free_mb from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB

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

DATA                              7372800    6481076 <<--- DG for Datafiles / one control file multiplexed

RECO                              9625120    9498656 <<--- DG for Recovery area / one control file multiplexed

REDO                               763120     635996 <<--- DG for the online redo logs

STEP 7 : on ODA (RAC1) copy the redo/controlfile backup taken in step:5 from backup location TO ASM.

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

LOGON AS GRID USER and SET +ASM1 env. Invoke the ASMCMD command line.

[grid@mbatloda1 ~]$ . oraenv

ORACLE_SID = [grid] ? +ASM1

The Oracle base has been set to /u01/app/grid

-- COPY one controlfile to +DATA/PRODB

ASMCMD> cd +DATA/PRODB

ASMCMD> pwd

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/control01.ctl .

ASMCMD> ls -lt

-- COPY another controlfile to +RECO/PRODB

ASMCMD> cd +RECO/PRODB

ASMCMD> pwd

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/control02.ctl .

ASMCMD> ls -lt

-- COPY ALL online redo to +REDO/PRODB

ASMCMD> cd +REDO/PRODB

ASMCMD> pwd

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/redo31.dbf .

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/redo21.dbf .

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/redo11.dbf .

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/redo32.dbf .

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/redo22.dbf .

ASMCMD> cp /opt/b590_orabck/RMAN/PRODB/bkup_pieces/redo12.dbf .

ASMCMD> ls -lt

 

-- Ensure all Online redo log are on ASM

STEP 8: Use the PFILE created on ODA as per STEP 1 and Startup MOUNT the PRODB database on RAC1. This will be SINGLE INSTANCE DB.

-- Ensure Controlfiles exist on ASM and path in correct in PFILE

-- Ensure all Local dest paths exist on ODA

 

STEP 9: WHILE the DB is MOUNTED, Check and Rename the redo logfiles.(ODA-RAC1)

 

SQL> select name from v$controlfile;

SQL> select member from v$logfile;

Note "Here it would show old location for redo logfiles as it was on PRODB (SERVER 2).

Please rename it using "alter database rename file" to same location and redo name which we used to copy redo file backup in step 7.

STEP 10: on ODA (RAC1), Catalog the backupieces if required. If Backup mount point available on SERVER 2 is mounted on ODA box.

We would not require to catalog backupieces. Validate the backupieces.

RMAN > list backup TAG='Before_Migration' --- Is this the backup we would like to restore ?

RMAN > crosscheck backup TAG='Before_Migration' --- is this backup available?

STEP 11: Once backups are successfully validated for availability, restore the PRODB database on ODA (RAC1) to ASM diskgroups.

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

SOME POINTS TO CONSIDER

i) Run the restore in background

ii). Please validate restore script and make sure there are no duplicate file names entries.

iii). Please make sure we have enough space in diskgroup to perform the restore.

-- Run Below in background on RAC1 Server

export ORACLE_SID=PRODB

rman target /

RMAN> run

{

allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

allocate channel ch3 device type disk;

allocate channel ch4 device type disk;

allocate channel ch5 device type disk;

allocate channel ch6 device type disk;

allocate channel ch7 device type disk;

allocate channel ch8 device type disk;

allocate channel ch9 device type disk;

allocate channel ch10 device type disk;

set newname for database to '+DATA';

restore database;

switch datafile all;

release channel ch1;

release channel ch2;

release channel ch3;

release channel ch4;

release channel ch5;

release channel ch6;

release channel ch7;

release channel ch8;

release channel ch9;

release channel ch10;

}

STEP 11: Once restore completes successfully. Validate and make sure no datafiles are in recovery state.

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

Validate datafile,redo,control,tempfile location and make sure they are not pointing to old/unknown locations.

v$recover_file view should not have any rows.

STEP 12: If validation in step 11 completes successfully.

Open the database without resetlogs. (noresetlogs).

SQL> alter database open;

Verify the tempfile and handle them.

Step 13: Validate the restored database and alert log and make sure alert log is clean. Configure listener for the database.

BCT can be left disabled and we can configure it after RAC conversion or when backup is configured.

######################################################## 

RAC Conversion Steps:-

STEP 1: Create second thread of online redo logs in order to start instance 2 and enable thread 2.

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

SQL> alter database add logfile thread 2 group 7 ('+REDO') size 100m reuse;

SQL> alter database add logfile thread 2 member '+REDO' to group 7 size 100M;

SQL> alter database add logfile thread 2 group 8 ('+REDO') size 100m reuse;

SQL> alter database add logfile thread 2 member '+REDO' to group 8 size 100M;

SQL> alter database add logfile thread 2 group 9 ('+REDO') size 100m reuse;

SQL> alter database add logfile thread 2 member '+REDO' to group 9 size 100M;

ALTER DATABASE ADD LOGFILE THREAD 2

GROUP 4 ('+REDO/PRODB/redo41.dbf','+REDO/PRODB/redo42.dbf') SIZE 100m,

GROUP 5 ('+REDO/PRODB/redo51.dbf','+REDO/PRODB/redo52.dbf') SIZE 100m,

GROUP 6 ('+REDO/PRODB/redo61.dbf','+REDO/PRODB/redo62.dbf') SIZE 100m;

SQL> select member from gv$logfile;

SYS> alter database enable public thread 2;

STEP 2: Create undo tablespace for second instance using recommended automatic undo management feature.

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

SQL> create undo tablespace UNDOTBS2 datafile  '+DATA' size 500M autoextend on maxsize 30g;

STEP 3: Add cluster related parameters

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

The duplicated instance does not have any cluster related parameters, we need to add the cluster parameter in order to convert single instance to RAC.

The CLUSTER_DATABASE=true parameter needs to set before a database can be started in cluster mode. 

The CLUSTER_DATABASE_INSTANCES parameter specifies the number of instances configured as part of the cluster database.

You should set this parameter value to the number of instances in the Real Application Cluster. 

The INSTANCE_NUMBER is a unique number that maps instances to database.

Add below cluster related parameters to initPRODB_RAC.ora:

SQL> show parameter spfile

SQL> show parameter pfile 

SQL> create pfile='$ORACLE_HOME/dbs/initPRODB_RAC.ora' from spfile; 

Edit $ORACLE_HOME/dbs/initPRODB_RAC.ora & add following parameters 

*.cluster_database_instances=2

*.cluster_database=true

*.remote_listener='RAC:1521’

*._disable_interface_checking=TRUE

*._gc_policy_time=0

*._gc_undo_affinity=FALSE

PRODB1.instance_number=1

PRODB2.instance_number=2

PRODB1.thread=1

PRODB2.thread=2

PRODB1.undo_tablespace='UNDOTBS1'

PRODB2.undo_tablespace='UNDOTBS2' 

STEP 4 : Copy the updated "$ORACLE_HOME/dbs/initPRODB_RAC.ora" file to node2 of ODA Setup (RAC2) and rename the files as per instance name.

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

NOTE: KEEP THE BACKUP OF ANY EXISTING FILES BEFORE CHANGING THEM. PLS MENTION WHAT ENTRIES ADDED/EDITED in these FILES 

 ON RAC1 :- scp $ORACLE_HOME/dbs/initPRODB_RAC.ora RAC2:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs 

 ON RAC1 :-  mv $ORACLE_HOME/dbs/initPRODB_RAC.ora $ORACLE_HOME/dbs/initPRODB1.ora

 ON RAC2 :-  mv $ORACLE_HOME/dbs/initPRODB_RAC.ora $ORACLE_HOME/dbs/initPRODB2.ora 

STEP 5 : Configure the listener.ora & tnsnames.ora for PRODB on ODA Servers.

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

NOTE: KEEP THE BACKUP OF ANY EXISTING FILES BEFORE CHANGING THEM. PLS MENTION WHAT ENTRIES ADDED/EDITED in these FILES

STEP 6: Shutdown the database

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

SYS> shutdown immediate 

STEP 7: Update the environment and start the database

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

NOTE: For the orapwd command ,pls ensure you set the correct SYS password.

If we do not know this password, feel free to RESET SYS PASSWORD first. 

Set the environment variable for each instance, recreate the password file and start the instances individually on two different nodes.

 

Environment variable setup on node1 (RAC1):

 

[oracle@RAC1]$ export ORACLE_SID=PRODB1

[oracle@RAC1]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

[oracle@RAC1]$ export PATH=$PATH:$ORACLE_HOME/bin

[oracle@RAC1]$ orapwd file=$ORACLE_HOME/dbs/orapwPRODB1 password=xxxxxxx

[oracle@RAC1]$ sqlplus /nolog

 

SYS@PRODB1> connect /as sysdba

 

SQL>startup

 

SQL> select instance_name from v$instance; 

 

Environment variable setup on node2 (RAC2) :

 

[oracle@RAC2]$ export ORACLE_SID=PRODB2

[oracle@RAC2]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

[oracle@RAC2]$ export PATH=$PATH:$ORACLE_HOME/bin

[oracle@RAC2]$ orapwd file=$ORACLE_HOME/dbs/orapwPRODB2 password=xxxxxxx

[oracle@RAC2]$ sqlplus /nolog 

SYS@PRODB2> connect /as sysdba 

SQL>startup

SQL> select instance_name from v$instance;

SQL>

STEP 8: VERIFY IF THE PRODB RAC DATABASE IS REGISTERED WITH CLUSTER MANAGERS [CRS]

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

srvctl status database -d PRODB

IF ITS NOT REGISTERED THEN REGISTER IT as below

[oracle@RAC1]$ srvctl add database -d PRODB -o /u01/app/oracle/product/11.2.0.3/dbhome_1

[oracle@RAC1]$ srvctl add instance -d PRODB -i PRODB1 -n RAC1

[oracle@RAC1]$ srvctl add instance -d PRODB -i PRODB2 -n RAC2

Now the instances are registered with CRS, use SRVCTL to stop and start the database.

Once the database has been started with SRVCTL the conversion process is complete.

Stop the database using this command:

[oracle@RAC1]$ srvctl stop database -d PRODB

Start the database using this command:

[oracle@RAC1]$ srvctl start database -d PRODB

Use the following command to check the status of all instances converted RAC database:

SQL> select * from v$active_instances

Check the status of all resources:

[oracle@RAC1]$ crsctl stat res -t

The State must be “ONLINE” for all resources.

STEP 9:  Create the spfile in ASM

SQL> create spfile='+DATA/PRODB/PARAMETERFILE/spfilePRODB.ora' from pfile;

Restart the database in order to take effect of spfile.:

[oracle@RAC1]$ srvctl stop database  -d PRODB

[oracle@RAC1]$ srvctl start database -d PRODB

Node 1 (RAC1)

$ cd $ORACLE_HOME/dbs

$ cp initPRODB1.ora initPRODB1.ora.bkp

$ vi initPRODB1.ora

### add the below line alone in the pfile.

spfile='+DATA/PRODB/PARAMETERFILE/spfilePRODB.ora'

Node 2 (RAC2)

$ cd $ORACLE_HOME/dbs

$ cp initPRODB2.ora initPRODB2.ora.bkp

$ vi initPRODB2.ora

### add the below line alone in the pfile.

spfile='+DATA/PRODB/PARAMETERFILE/spfilePRODB.ora'

 

STEP 10: Enable the BCT

STEP 11: Esteblish the connectivity between SERVER 1 & ODA Servers,

Make sure the TNS-ENTRY of PRODA ON SERVER 1 is now pointing to ODA SERVERS (RAC1 & RAC2).

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

On Host SERVER 1:-

cd $ORACLE_HOME/network/admin/

cp tnsnames.ora tnsnames.ora.bkup

vi tnsnames.ora

Update the hostname in PRODB TNS-ENTRY to "RAC1.clv.clverify.com"

check the tnsping as:-

tnsping PRODB

check the remote connectivity as:-

strmadmin/strmadmin@PRODB

On Hosts RAC1 & RAC2

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

cd $ORACLE_HOME/network/admin/

cp tnsnames.ora tnsnames.ora.bkup

vi tnsnames.ora

Update/ADD the hostname in PRODA TNS-ENTRY to following:

PRODA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PRODA.WORLD)

    )

  )

PRODA.WORLD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SID = PRODA)

    )

  )

check the tnsping as :-

tnsping PRODA

check the remote connectivity as:-

strmadmin/strmadmin@PRODA

STEP 12: ENABLE THE REPLICATION ON PRODA ON SERVER 1 & PRODB ODS SERVERS

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

==> ==> Streams ENABLING :-

#####################################################

SQL> set lines 700 pages 300

SQL> col host_name for a30

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME

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

PRODA        SERVER1

SQL> conn strmadmin/strmadmin

SQL> set lines 700 pages 300

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

Session altered.

SQL> select CAPTURE_NAME, QUEUE_NAME, STATUS from dba_capture ;

CAPTURE_NAME                   QUEUE_NAME                     STATUS

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

ABC_BOS_CAPTURE          ABC_BOS_CAPTURE_Q        ABORTED

DEF_BOS_CAPTURE              DEF_BOS_CAPTURE_Q            ABORTED

SQL> select CAPTURE_NAME, STATE from v$streams_capture;

no rows selected

SQL> select propagation_name,status,error_message from dba_propagation;

PROPAGATION_NAME               STATUS

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

ERROR_MESSAGE

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

PROP_DEF_BOS_TO_DEF_ATL    ABORTED

PROP_ABC_BOS_TO_ABC_ATL    ABORTED

SQL>  select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_DBLINK, DESTINATION_QUEUE_NAME, STATUS from dba_propagation ;

PROPAGATION_NAME               SOURCE_QUEUE_NAME              DESTINATION_DBLINK                                                                                   DESTINATION_QUEUE_NAME          STATUS

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

PROP_DEF_BOS_TO_DEF_ATL    DEF_BOS_CAPTURE_Q            PRODB.WORLD                                                                                        DEF_ATL_APPLY_Q       ABORTED

PROP_ABC_BOS_TO_ABC_ATL    ABC_BOS_CAPTURE_Q        PRODB.WORLD                                                                                        ABC_ATL_APPLY_Q           ABORTED

SQL> !tnsping PRODB.WORLD

SQL> select APPLY_NAME, QUEUE_NAME, STATUS from dba_apply ;

APPLY_NAME                     QUEUE_NAME                     STATUS

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

ABC_BOS_APPLY            ABC_BOS_APPLY_Q          ABORTED

DEF_BOS_APPLY                DEF_BOS_APPLY_Q              ABORTED

SQL>  select APPLY_NAME, SOURCE_DATABASE, LOCAL_TRANSACTION_ID,  SOURCE_TRANSACTION_ID, SOURCE_COMMIT_SCN, ERROR_CREATION_TIME, ERROR_MESSAGE from dba_apply_error ;
no rows selected

SQL>

ON ODA SERVERS (RAC1): -

SQL> set lines 700 pages 300

SQL>  col host_name for a30

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME

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

PRODB          RAC1

SQL> conn strmadmin/strmadmin

SQL> set lines 700 pages 300

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

Session altered.

SQL> select CAPTURE_NAME, QUEUE_NAME, STATUS from dba_capture;

CAPTURE_NAME                   QUEUE_NAME                     STATUS

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

ABC_ATL_CAPTURE          ABC_ATL_CAPTURE_Q        DISABLED

DEF_ATL_CAPTURE              DEF_ATL_CAPTURE_Q            DISABLED

SQL> select CAPTURE_NAME, STATE from v$streams_capture;

no rows selected

SQL> select propagation_name,status,error_message from dba_propagation;

 

PROPAGATION_NAME               STATUS

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

ERROR_MESSAGE

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

PROP_ABC_ATL_TO_ABC_BOS    DISABLED

PROP_DEF_ATL_TO_DEF_BOS    DISABLED

SQL> select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_DBLINK, DESTINATION_QUEUE_NAME, STATUS from dba_propagation;

PROPAGATION_NAME               SOURCE_QUEUE_NAME              DESTINATION_DBLINK                                                                                   DESTINATION_QUEUE_NAME          STATUS

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

PROP_ABC_ATL_TO_ABC_BOS    ABC_ATL_CAPTURE_Q        PRODA.WORLD                                                                                       ABC_BOS_APPLY_Q           DISABLED

PROP_DEF_ATL_TO_DEF_BOS    DEF_ATL_CAPTURE_Q            PRODA.WORLD                                                                                       DEF_BOS_APPLY_Q       DISABLED

SQL> !tnsping PRODA.WORLD

SQL> select APPLY_NAME, QUEUE_NAME, STATUS from dba_apply ;

APPLY_NAME                     QUEUE_NAME                     STATUS

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

ABC_ATL_APPLY            ABC_ATL_APPLY_Q          DISABLED

DEF_ATL_APPLY                DEF_ATL_APPLY_Q              DISABLED

SQL> select APPLY_NAME, SOURCE_DATABASE, LOCAL_TRANSACTION_ID,  SOURCE_TRANSACTION_ID, SOURCE_COMMIT_SCN, ERROR_CREATION_TIME, ERROR_MESSAGE from dba_apply_error ;

no rows selected

SQL>

Starting the Replication between SERVER1 (PRODA) & ODA Servers [RAC1] (PRODB)

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

From B590 to ODA Servers [mbaloda1]

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

PRODA SQL > exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'ABC_BOS_CAPTURE');

PRODA SQL > exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'DEF_BOS_CAPTURE');

PRODB [mbaloda1] SQL > exec dbms_apply_ADM.START_APPLY(APPLY_NAME => 'ABC_ATL_APPLY');

PRODB [mbaloda1] SQL > exec dbms_apply_ADM.START_APPLY(APPLY_NAME => 'DEF_ATL_APPLY');

PRODA SQL > exec DBMS_PROPAGATION_ADM.START_PROPAGATION (PROPAGATION_NAME => 'PROP_ABC_BOS_TO_ABC_ATL');

PRODA SQL > exec DBMS_PROPAGATION_ADM.START_PROPAGATION (PROPAGATION_NAME => 'PROP_DEF_BOS_TO_DEF_ATL');

PRODA SQL > select CAPTURE_NAME, QUEUE_NAME, STATUS from dba_capture;

PRODA SQL > select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_DBLINK, DESTINATION_QUEUE_NAME, STATUS from dba_propagation;

From ODA Server [RAC1] to SERVER 1

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

PRODB [RAC1] SQL > exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'ABC_ATL_CAPTURE');

PL/SQL procedure successfully completed.

PRODB [RAC1] SQL > exec DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'DEF_ATL_CAPTURE');

PRODA SQL > exec dbms_apply_ADM.START_APPLY(APPLY_NAME => 'DEF_BOS_APPLY');

PRODA SQL > exec dbms_apply_ADM.START_APPLY(APPLY_NAME => 'ABC_BOS_APPLY');

PRODB [RAC1] SQL > exec DBMS_PROPAGATION_ADM.START_PROPAGATION (PROPAGATION_NAME => 'PROP_DEF_ATL_TO_DEF_BOS');

PRODB [RAC1] SQL > exec DBMS_PROPAGATION_ADM.START_PROPAGATION (PROPAGATION_NAME => 'PROP_ABC_ATL_TO_ABC_BOS');

PRODB [RAC1] SQL > select CAPTURE_NAME, QUEUE_NAME, STATUS from dba_capture;

PRODB [RAC1] SQL > select PROPAGATION_NAME, SOURCE_QUEUE_NAME, DESTINATION_DBLINK, DESTINATION_QUEUE_NAME, STATUS from dba_propagation;

#######################################################

Checking Replication sync status:-

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

ON SERVER1 (PRODA)

SQL> select * from ABC.test;

SQL> select * from DEF.test;

SQL> INSERT INTO ABC.TEST values ('Inserted after Migrating to ODA',sysdate);

SQL> INSERT INTO DEF.TEST values ('Inserted after Migrating to ODA',sysdate);

SQL> commit;

SQL> select * from ABC.test;

SQL> select * from DEF.test;

ON PRODB [RAC1]
SQL> select * from ABC.test;

SQL> select * from DEF.test;

SQL> select * from ABC.test;

SQL> select * from DEF.test;

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

From ODA Servers TO SERVER 1:-

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

SQL> INSERT INTO ABC.TEST values ('Inserted after Migrating to ODA',sysdate);

SQL> INSERT INTO DEF.TEST values ('Inserted after Migrating to ODA',sysdate);

SQL> commit;

SQL> select * from DEF.test;

SQL> select * from ABC.test;

SQL>

ON SERVER1 :-

SQL> select * from DEF.test;

SQL> select * from ABC.test;

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

Conclusion: The benefit we get here by moving the single instance setup of streams environment to the RAC setup is to ensure high availability benefits for one of the streams site. With this we come to an end of the blog series. All the parts have fully covered different database scenarios of Oracle installation, restoration, recovery, upgradation and streams setup with necessary steps that were executed in one complete project. In case of any doubts click below:


Ask Ravi

Link to Part 1

Link to Part 2

Link to Part 3