Step by Step how to switchover to physical standby

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;