DATAGUARD SWITCHOVER ON CASCADE STANDBY

DATAGUARD SWITCHOVER ON CASCADE STANDBY

TESTDB = Primary Database(Prod) -> 10.69.1.102
SB_TESTDB = Existing Standby -> 10.69.1.180
SB2_TESTDB = New Standby(Will be the new Prod) -> 10.69.1.98

Steps:

1. Switchover Primary to Standby(TESTDB->SB2_TESTDB)

- Make sure that the last log is applied
SQL>alter system switch logfile;
SQL>alter system archive log current;

- Verify if archivelog is applied to all Standby.

- Bounce the primary database to clear up any outstanding process
SQL>shutdown immediate;
SQL>startup;

- Switchover Primary to Standby
setsid TESTDB
sqlplus '/as sysdba'
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. Switchover Standby to Primary(SB2_TESTDB->TESTDB)
setsid TESTDB
sqlplus '/as sysdba'
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Shutdown old Primary
sqlplus '/as sysdba'
SQL>shutdown immediate

4. Change New Primary(SB2_TESTDB) to TESTDB

- backup the existing spfile
cd $ORACLE_HOME/dbs
cp initTESTDB.ora initTESTDB.ora.0415102

idle>create pfile from spfile;

- shutdown the new Primary
idle>shutdown immediate

- change the parameter setting and remove db_unique_name & log_archive_config

*.fal_client='TESTDB'
*.fal_server='sb_TESTDB'
*.log_archive_dest_1='LOCATION=/u02/oradata/arch/TESTDB'
*.log_archive_dest_2='service=sb_TESTDB optional reopen=15'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'

- Change the TNS Entry on the new Primary DB

cd $ORACLE_HOME/network/admin
cp tnsnames.ora tnsnames.ora.04162010

TNS Entry:

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

SB_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.180)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

- create the spfile for the changes made on initTESTDB.ora
idle> create spfile from pfile='?/dbs/initTESTDB.ora';

5. Change the Old Standby(SB_TESTDB) to default Standby.

- shutdown the standby.
setsid TESTDB
sqlplus '/as sysdba'
idle> alter database recover managed standby database cancel;
idle> shutdown immediate

- backup the existing spfile
cd $ORACLE_HOME/dbs
cp initTESTDB.ora initTESTDB.ora.0415102

- change the parameter setting and remove db_unique_name & log_archive_config

*.fal_client='sb_TESTDB'
*.fal_server='TESTDB'
*.log_archive_dest_1='LOCATION=/u02/oradata/arch/TESTDB'
*.log_archive_dest_2='service=TESTDB optional reopen=15'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='defer'

- Change the TNS Entry on the new Primary DB

TNS Entry:

SB_TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.180)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.69.1.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

- create the spfile for the changes made on initTESTDB.ora
idle> create spfile from pfile='?/dbs/initTESTDB.ora';

6. Start the new Standby(SB_TESTDB)

setsid TESTDB
sqlplus '/as sysdba'
idle> startup nomount;
idle> alter database mount standby database;
idle> alter database recover managed standby database disconnect;

7. Start the new Primary(TESTDB) formerly SB2_TESTDB

setsid TESTDB
sqlplus '/as sysdba'
idle> startup;


8. Check the Standby if archive logs are being applied.

- Archivelogs will be shipped but will not be applied. Recovery is not active.

MRP0: Background Media Recovery process shutdown (TESTDB) --> on alertlog

In order to fixed the issue you need to restart the Standby.

idle> shutdown immediate
idle> startup nomount;
idle> alter database mount standby database;
idle> alter database recover managed standby database disconnect;


- Initial shipping will take a minute or two due to Standby adjusting to the new Primary.

Primary(TESTDB)

SQL>alter system switch logfile;
SQL>/

idle> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oradata/arch/TESTDB/
Oldest online log sequence 67
Next log sequence to archive 69
Current log sequence 69

Standby(SB_TESTDB)

SQL> select sequence#, archived, applied from v$archived_log order by sequence#;

SEQUENCE# ARC APP
---------- --- ---
67 YES YES
68 YES YES

9. Validate RMAN backup to check if there will be an error on the nightly scheduled backup. If you see an error on archivelog missing just grab it from old Prod. It should be on the same location.

Validate Full Database backup:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;