Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements..

-- Convert primary database to standby

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

if the above error occurred, then try to shut the database then startup then try again.

-If still the same error persist,do the following in sequence: -

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; (value for this should be 0)

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

SQL> alter database commit to switchover to PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the standby database issue the following commands: -

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;



SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required


if the above error occurred then execute the below commands:


SQL> alter database recover managed standby database finish;


SQL> alter database commit to switchover to physical primary with session shutdown;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

enjoy:-)