Friday, August 21, 2015

Oracle11g Data Guard manual switch over steps

The Manual Switch over steps were tested in oracle and below steps were followed in Critical production database many times in the past and switch over were completed successfully!
We have primary and two standby database on this data guard environment. I tested the switch over between primary(devdb12) and first standby(devdb12_dg).
Step 1 Database verification
  1. Verify the standby database and make sure standby database is in sync with primary database.
  2. Tail the alert log on both primary and standby to monitor the database during the entire switch over time.  Use this command  :  tail -f  alert_$ORACLE_SID.log
  3. Make sure below data guard parameters(log_archive_dest, log_archive_dest_state, log_archive_config, fal_client, fal_server etc) are correctly configured on standby site. This would help log  shipping go smooth after the switch over.
Let application team bring the application down.  Now DBA start switching over the database!
Step 2 Database Active Connection Verification
Make sure, no application connection on the database.
select count(*),username from v$session group by v$session
To be safer side, run the below script to kill any non local process to complete our switch over smoothly.
tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; echo $tokill;
tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; kill -9 $tokill;

Step 3 The primary and standby should show the status as below.
set linesize 200
col DB_UNIQUE_NAME form a30
col DATABASE_ROLE for a20
col OPEN_MODE for a30
select DB_UNIQUE_NAME,Database_role,open_mode,switchover_status from v$database;

The Primary database SWITCHOVER_STATUS can be either TO STANDBY or SESSIONS ACTIVE.  The standby database SWITCHOVER_STATUS should be NOT ALLOWED.

Step 4: Convert Primary to Standby:
On Primary
Execute the below command on primary (devdb12)
alter database commit to switchover to physical standby with session shutdown;

At this stage, the primary database(devdb12) is not completely converted to standby.  The primary database is down and it is ready to covert for standby database. The standby database (devdb12_dg and devdb12_dr) are ready to convert to primary.  After completing Step 6, the primary(devdb12) will be turned to standby. Step 6 can be executed on this step. But it is always good practice to start standby after starting the primary to reduce the application down time. All three database status should show as below after running the above command.

The devdb12 SWITCHOVER_STATUS should be  RECOVERY NEEDED. The devdb12_dg,devdb12_dr  database SWITCHOVER_STATUS should be TO PRIMARY

Step 5:  Convert Standby to Primary
We are converting devdb12_dg to primary database. Hence login as devdb12_dg and run the below command.

alter database commit to switchover to primary with session shutdown;
alter database open;

After opening the database, the status should be as below. The switchover_status is FAILED_DESTINATION.

Now devdb12_dg becomes primary and ready to take the transactions.  Still it is not ready to ship the archive log file.  At this moment, DBA can ask application team to start the application. Now application is pointing to new primary(devdb12_dg). The old primary(devdb12) and second standby(devdb12_dr)  status should be as below. The  old primary(devdb12) switch over status is RECOVERY_NEEDED. The second standby(devdb12_dr) switch over status is NOT ALLOWED.

Step 6: - Convert Original Primary(devdb12)  to Standby
Login to old primary(devdb12) and run the below command.

shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
-- Wait for few minutes and make sure archive logs are shipping to standby database
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Step 7  Verify the database mode for both primary and standby. Now devdb12 is standby and devdb12_dg is primary database. Devdb12_dr is still standby and no change on devdb12_dr.

Step 8 Verify that archive logs are shipping correctly.

No comments: