Primary db - TROY
standby db - TROYDR
downstream db - TORYMNG
GG target - TROYTGT

Currently our downstream db TROYMNG is receiving archives from primary database TROY. Once we perform switchover our new primary will be TROYDR.

so before switchover we need to make some chnages so that new primary db – TROYDR is capable of sending archived to downstream db – TROYMNG. Also once switchover is complete we need to repoint our extract to new primary db (TROYDR)

we need to make changes only in EXTRACT parameter files only. There is no changes required in PUMP and REPLICAT side as we are using downstream configuration.

setup on standby db before switchover


SQL> select name,open_mode from v$database;
NAME OPEN_MODE

TROY MOUNTED

SQL> sho parameter uniq
NAME TYPE VALUE

db_unique_name string TROYDR
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME

YES YES
SQL> sho parameter config
NAME TYPE VALUE

dg_broker_config_file1 string +DATA/TROYDR/broker2.dat
dg_broker_config_file2 string +FRA/TROYDR/broker2.dat
log_archive_config string dg_config=(TROYDR,TROY)

update log_archive_config and dest_3

we will add downstream name and location as metioned below for sending archives.

SQL> alter system set log_archive_config='dg_config=(TROYDR,TROY,TROYMNG)' scope=both sid='*';
System altered.
SQL> sho parameter config
NAME TYPE VALUE

dg_broker_config_file1 string +DATA/TROYDR/broker2.dat
dg_broker_config_file2 string +FRA/TROYDR/broker2.dat
log_archive_config string dg_config=(TROYDR,TROY,TROYMNG)

SQL> sho parameter log_archive_dest_3
NAME TYPE VALUE

log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string

SQL> alter system set log_archive_dest_3='SERVICE=TROYMNG SYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=TROYMNG' scope=both sid='*';
System altered.
SQL> sho parameter log_archive_dest_3
NAME TYPE VALUE

log_archive_dest_3 string SERVICE=TROYMNG SYNC NOREGIST
ER VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) REOPEN=10 DB_UNI
QUE_NAME=TROYMNG
log_archive_dest_30 string
log_archive_dest_31 string

make sure to add downstream db – TORYMNG tns entry on both nodes of standby database and verify using tnsping

SQL> !tnsping TROYMNG
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-AUG-2020 12:55:27
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/oracle/product/11.2.0.3/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel06.internal.vista.net)(PORT = 1977)) (CONNECT_DATA = (SERVICE_NAME = TROYMNG)))
OK (10 msec)

setting on downstream db

add TROYDR in log_archive_config parameter

SQL> select name,open_mode from v$database;
NAME OPEN_MODE

TROYMNG READ WRITE
SQL> sho parameter config
NAME TYPE VALUE

dg_broker_config_file1 string /u01/oracle/product/11.2.0.3/d
b_2/dbs/dr1TROYMNG.dat
dg_broker_config_file2 string /u01/oracle/product/11.2.0.3/d
b_2/dbs/dr2TROYMNG.dat
log_archive_config string DG_CONFIG=(TROY,TROYDR,
TROYMNG)

add tns entry on both nodes of TROYMNG database

add tns entry
TROYDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel02.internal.vista.net)(PORT = 1977))
(CONNECT_DATA =
(SERVICE_NAME = TROYDR)
)
)
[oracle@oel0601 admin]$ tnsping TROYDR
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-AUG-2020 13:02:27
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/oracle/product/11.2.0.3/db_2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel02.internal.vista.net)(PORT = 1977)) (CONNECT_DATA = (SERVICE_NAME = TROYDR)))
OK (10 msec)

[oracle@oel0602 admin]$ tnsping TROYDR
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 25-AUG-2020 13:02:27
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/oracle/product/11.2.0.3/db_2/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oel02.internal.vista.net)(PORT = 1977)) (CONNECT_DATA = (SERVICE_NAME = TROYDR)))
OK (10 msec)

now try connecting to standby from downstream from ggsci. it will give you error for now but once db is switchover to TROYDR this will work as will see later

now you can try connecting from mining db to standby TROYDR
right now TROYDR is in mount mode so no connection allowed
GGSCI (oel0601) 1> dblogin userid ggadmin@TROYDR, password ggadmin
ERROR: OCI Error ORA (status = 1033-ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
).

after switchover

Now once you switchover is doen and new primary database is TROYDR then stop the extract on downstream db and update connect string with standby (new primary – TROYDR)

GGSCI (oel0601) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E1_ICR01 00:16:26 00:00:01
EXTRACT RUNNING E_ICR01 00:16:27 00:00:01
EXTRACT RUNNING P1_ICR01 00:00:00 00:00:09
EXTRACT RUNNING P_ICR01 00:00:00 00:00:00

GGSCI (oel0601) 8> stop extract E_ICR01
Sending STOP request to EXTRACT E_ICR01 …
Request processed.

GGSCI (oel0601) 9> stop extract E1_ICR01
Sending STOP request to EXTRACT E1_ICR01 …
Request processed.

GGSCI (oel0601) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED E1_ICR01 00:16:54 00:00:11
EXTRACT STOPPED E_ICR01 00:16:48 00:00:17
EXTRACT RUNNING P1_ICR01 00:00:00 00:00:06
EXTRACT RUNNING P_ICR01 00:00:00 00:00:07

update connect string in extract to connect to DR database. we have two extracts E_ICR01 and E1_ICR01

GGSCI (oel0601) 14> view params E_ICR01
Extract E_ICR01
userid ggadmin@TROYDR password ggadmin
tranlogoptions mininguser mngadmin@TROYMNG miningpassword mngadmin
tranlogoptions integratedparams (downstream_real_time_mine Y)
exttrail /u01/acfs/ogg/EXTRACT/TROY/EA
DDL INCLUDE ALL
table siebel.*;

GGSCI (oel0601) 15> view params E1_ICR01
Extract E1_ICR01
userid ggadmin@TROYDR password ggadmin
tranlogoptions mininguser mngadmin@TROYMNG miningpassword mngadmin
tranlogoptions integratedparams (downstream_real_time_mine Y)
exttrail /u01/acfs/ogg/EXTRACT/TROY/EB
DDL INCLUDE ALL
table BI_ETL.; table CPQ.;
table CUST_JAVA_APP_DEV.; table DM_TEMP.;
table EIMUSER.; table EVE.;
table FNMPAUDIT.; table IMPERVA.;
table JV_UTIL.; table LDAPUSER.;
table OCN_BCN.; table OMPDBUSER.;
table ORABPEL.; table ORAESB.;
table ORAWSM.; table PROD_TEMP.;
table RTQM_GENERIC.; table SADMIN.;
table SDMPROD01.; table SDMPRODR01.;
table SIEBELCOL.; table TEMP_HARISH.;
table TEST.; table USERLDAP.;
table USERLDAP1.; table WMUSER.;

start the extract

GGSCI (oel0601) 16> dblogin userid ggadmin@TROYDR password ggadmin
Successfully logged into database.

GGSCI (oel0601 as ggadmin@TROYDR_2) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED E1_ICR01 00:16:54 00:07:23
EXTRACT STOPPED E_ICR01 00:16:48 00:07:29
EXTRACT RUNNING P1_ICR01 00:00:00 00:00:06
EXTRACT RUNNING P_ICR01 00:00:00 00:00:08

GGSCI (oel0601 as ggadmin@TROYDR_2) 18> start extract E_ICR01
Sending START request to MANAGER …
EXTRACT E_ICR01 starting

GGSCI (oel0601 as ggadmin@TROYDR_2) 24> start extract E1_ICR01
Sending START request to MANAGER …
EXTRACT E1_ICR01 starting

GGSCI (oel0601 as ggadmin@TROYDR_2) 25> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING E1_ICR01 00:39:08 00:00:08
EXTRACT RUNNING E_ICR01 00:00:04 00:00:05
EXTRACT RUNNING P1_ICR01 00:00:00 00:00:04
EXTRACT RUNNING P_ICR01 00:00:00 00:00:04

There is no need to change anything on PUMP and REPLICAT side. Even we don’t need to stop them during this entire process. Once you switch back to TROY db then you need to just stop extract and change the connect string in extract to belwo and start the extract.

userid ggadmin@TROY password ggadmin