Switchover in Oracle 12C Dataguard

Precheck for Switchover

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
 DEST_NAME
 --------------------------------------------------------------------------------
 STATUS ERROR
 --------- -----------------------------------------------------------------
 LOG_ARCHIVE_DEST_2
 VALID



SQL> select message from v$dataguard_status;


select switchover_status from v$database;

Switchover_Status
-----------------
TO Standby


The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request



On Primary database:-

Connect to sqlplus and switchover to standby database

[oracle@pr ~]$ sqlplus / as sysdba


SQL> alter database commit to switchover to standby;
 Database altered.


Shutdown primary database



SQL> shutdown immediate;


Startup nomount old primary database as new standby database



SQL> startup nomount
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 805307128 bytes
 Database Buffers 159383552 bytes
 Redo Buffers 5988352 bytes




SQL> alter database mount standby database;
 Database altered.


Start the MRP process.

SQL> alter database recover managed standby database disconnect from session;
 Database altered.


Verify the Status of old primary database.



select status,instance_name,database_role,protection_mode from v$database,v$instance;


On Standby database:



[oracle@dr ~]$ sqlplus / as sysdba


Convert old standby database as primary and shutdown database:

SQL> alter database commit to switchover to primary;
 Database altered.


Shutdown old primary database and start the database as new primary database.

SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.


SQL> startup
 ORACLE instance started.
 Total System Global Area 972898304 bytes
 Fixed Size 2219272 bytes
 Variable Size 717226744 bytes
 Database Buffers 247463936 bytes
 Redo Buffers 5988352 bytes
 Database mounted.
 Database opened.


Verify the Status on new primary

select status,instance_name,database_role,protection_mode from v$database,v$instance;