Switchover the
database to physical standby
Primary DB Name: -
PROD
Standby DB Name: -
STDBY
Prerequisite for
switchover to be successful
At least ensure the below before starting the switchover
Primary and standby must be in sync
Log shipping must be happening
Listener parameter on both primary and standby
must be up and running
There must not be any error in
v$archive_dest_status error column
No users should be connected (kill the session
to optimize switchover time)
If broker configuration is in place and you want
to use SQL for switchover disable the broker configuration and set the
db_broker_start parameter to False
At least cross check the below parameters in
both DC and DR and make sure they must have the expected and correct value
db_unique_name
|
service_names
|
fal_client
|
fal_server
|
db_file_name_convert
|
log_file_name_convert
|
log_archive_dest_1
|
log_archive_dest_2
|
db_recovery_file_dest
|
db_recovery_file_dest_size
|
standby_file_management
|
dg_broker_start
|
standby_archive_dest
|
log_archive_config
|
Execute the below
steps On the Current Primary Database side (PROD)
Stop the Log Shipping from Primary Database
ALTER
SYSTEM SWITCH LOGFILE;
ALTER
SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER scope=spfile;
System altered.
Note: - You cannot proceed for the switchover if the below
query output is not “SESSIONS ACTIVE” or
“TO STANDBY”
select
switchover_status from v$database;
SWITCHOVER_STATUS
------------------
SESSIONS ACTIVE
If we get the "SESSIONS ACTIVE" status use the below
query
alter database commit to switchover to physical standby with
session shutdown;
Database altered.
Shutdown immediate =====&; Don’t do this until the standby has received all the redo!
ORA-01507: database not mounted
ORACLE instance shut down.
Note: - At this point your Primary has been switched over
Execute the below
steps on the current Standby Database Side (STDBY)
select switchover_status from
v$database;
SWITCHOVER_STATUS
------------------
NOT ALLOWED
Wait for some more time, and query again, we should receive
status like “TO PRIMARY"
SQL> select switchover_status
from v$database;
SWITCHOVER_STATUS
------------------
TO PRIMARY
Note: if we waited 5 mins, still if status is not changing ,
issue the following command.
alter database commit to
switchover to primary with session shutdown;
If you are getting the status TO PRIMARY you can use the
below command to switchover the standby to primary
alter database commit to
switchover to primary;
Database altered.
shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
Startup the new Standby( ie Original primary PROD)
Execute the below
commands on PROD database ie Original Primary
startup nomount
ORACLE instance started.
Total System Global Area 4300184864 bytes
Fixed Size
736544 bytes
Variable Size
4261412864 bytes
Database Buffers
16777216 bytes
Redo Buffers
21258240 bytes
alter system set
log_archive_dest_state_2=DEFER scope=both;
System altered.
alter database mount standby
database;
Database altered.
alter database recover managed
standby database disconnect from session;
Database altered.
SQL> alter database recover
managed standby database cancel;
Database altered.
If required Add the
standby logfiles
alter database add standby logfile '/u02/oradata /srl01.log'
size 50M;
alter database add standby logfile '/u02/oradata /srl02.log'
size 50M;
alter database add standby logfile '/u02/oradata /srl03.log'
size 50M;
alter database add standby logfile '/u02/oradata /srl04.log'
size 50M;
SQL> alter database recover
managed standby database disconnect from session;
Database altered.
Execute the below
commands on STDBY database ie Original Standby
Startup the new Primary
startup
ORACLE instance started.
Total System Global Area 4300184864 bytes
Fixed Size
736544 bytes
Variable Size
4261412864 bytes
Database Buffers
16777216 bytes
Redo Buffers
21258240 bytes
Database mounted.
Database opened.
Enable Log Shipping and Switch Logfile to Check Shipping is
happening:
ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SWITCH LOGFILE;
