12c DataGuard Switch Over Steps || 2 Nodes RAC Database Switch Over Steps

12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)
11.2 Data Guard Physical Standby Switchover Best Practices using SQL*Plus (Doc ID 1304939.1)

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

Pre Checks:

1) Verify Managed Recovery Process is running on the standby

2) Verify there are no Gap betwbeen PROD and DR

Switchover:

1) Cancel any apply delay for the target standby and make both PROD and DR in sync

2) Create Guaranteed Restore Points (optional)

3) Verify that the primary database can be switched to the Standby Role 

4) If The Primary is a RAC, then shutdown all other instances keeping one instances active.

5) Switchover the primary to a standby database first.

6) Verify that the standby database is not ready to be switched to the primary role

7) Switchover the standby database to a primary

8) Open new primary database

9) Restart the new standby with apply MRP preocess

PROD Env:

DB: RAC12C

Instances: RAC12C1 & RAC12C2

Server: node1.localdomain.com & node2.localdomain.com

Oracle Home: /u01/app/oracle/product/12.2.0.1/dbhome_1

DR Env:

DB: RACSB

Instances: RACSB1 & RACSB2

Server: oraclenode1.localdomain.com & oraclenode2.localdomain.com

Oracle Home: /u01/app/oracle/product/12.2.0.1/dbhome_1

Primary:

srvctl config database -d RAC12C

srvctl status database -d RAC12C

SQL> select name,open_mode,database_role from v$database;

Standby:

srvctl config database -d RACSB

srvctl status database -d RACSB

SQL> select name,open_mode,database_role from v$database;

Prechecks Checks:

Step 1: Check MRP is running on the standby or not:

SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Execute the below statement in primary to check "real time apply"

SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

Start MRP on standby if it is not running:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 2: Verify both PROD and DR are in sync

Verify the state of data guard on both the databases with following SQL queries:

PROD:

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SELECT THREAD#, SEQUENCE# FROM V$THREAD;

DR:

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG

WHERE APPLIED = 'YES'

AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')

GROUP BY THREAD#;

Step 3: Cancel any apply delay for the on standby:

alter database recover managed standby database cancel;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT FROM SESSION;

Step 4: Create Guaranteed Restore Point (optional)

In 11g and 12c we need to create GRP point on both PROD and DR where as in 19c we need to create only on PROD.

DR:

Stop MRP process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> CREATE RESTORE POINT SWITCHOVER_PROD_TO_DR GUARANTEE FLASHBACK DATABASE;

Start MRP process:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

PROD:

Create a guaranteed restore point

CREATE RESTORE POINT SWITCHOVER_PROD_TO_DR GUARANTEE FLASHBACK DATABASE;

Step 5: Verify the database is ready for switch over or not?

SQL> alter database switchover to RACSB verify;

Step 6: Verify that the primary database can be switched over to the standby role

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 7: If The Primary is a RAC, then shutdown all other instatances keep only one active:

srvctl status database -d RAC12C

srvctl stop instance -d RAC12C -i RAC12C2

Step 8: Switchover the primary to a standby database

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

tail -f alert_RAC12C1.log

Step 9: Verify that the standby database can be switched to the primary role

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Step 10: Switchover the standby database to a primary

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

tail -f alert_RACSB1.log

Step 11: Open the new primary database

ALTER DATABASE OPEN;

srvctl status database -d RACSB

Step 12: Start the new standby and start recovery

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 13: Post Switchover drop the GRP:

On New Standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> DROP RESTORE POINT SWITCHOVER_PROD_TO_DR;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

On New Primary:

SQL> DROP RESTORE POINT SWITCHOVER_PROD_TO_DR;

Note: Some useful commands:

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

Enable and disable log_archive_dest_state_2 / log shipping to standby:

alter system set log_archive_dest_state_2=DEFER scope=both sid='*';

alter system set log_archive_dest_state_2=enable scope=both sid='*';

Start and stop the log shipping apply at DR database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

alter database recover managed standby database cancel;

How to add/Remove listener to Cluster:

srvctl add listener -l LISTENER_RACSB -p 1523 -o /u01/app/oracle/product/12.2.0.1/dbhome_1

srvctl start listener -l LISTENER_RACSB

srvctl stop listener -l LISTENER_RACSB

srvctl status listener -l LISTENER_RACSB

lsnrctl status LISTENER_RACSB

lsnrctl start LISTENER_RACSB

lsnrctl stop LISTENER_RACSB

srvctl remove listener -l LISTENER_RACSB

srvctl config listener -l LISTENER_RACSB

Move Password file to DiskGroup on from local storage:

ASMCMD>mkdir +DATA/RACSB

ASMCMD>pwcopy '/tmp/orapwRAC12C' '+DATA/RACSB/orapwRACSB'

How to set the password location using srvctl:

srvctl modify database -d RACSB -pwfile '+DATA/RACSB/PASSWORD/orapwRACSB'

Verify Standby Logs:

select * from v$standby_log;

select group#, thread#, sequence#, archived, status from v$standby_log;

set lines 190 pages 190

col instance for a8

col member for a50

SELECT t.INSTANCE, l.THREAD#, l.GROUP#,  l.SEQUENCE#, l.bytes, l.archived, l.status, lf.MEMBER

FROM v$log l, v$logfile lf, v$thread t

WHERE t.THREAD# = l.THREAD# 

AND l.GROUP# = lf.GROUP#

ORDER BY l.THREAD#, GROUP#;

col member format a50

select GROUP#,TYPE,MEMBER from v$logfile;

select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024, MEMBERS,STATUS from v$log;

Regards,

Mallik