The Manual Switch over steps were tested in oracle 11.2.0.4 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
Step 2 Database Active Connection Verification
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.
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
- Verify the standby database and make sure standby database is in sync with primary database.
- 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
- 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.
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;
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.
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
col SWITCHOVER_STATU 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;
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
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.
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;
-- 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.