Oracle Database Switchover and Failover steps

Switchover Steps
1) There are few steps we need to check before performing the switchover in dataguard environment 
a)If we are using the OEM Enable a blackout for primary database in enterprise manager.
b)Check the crontab jobs for Primary database need to disabled.
c)Check for executing backups/exports and cancel.
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

2)Verify that it is possible to perform a switchover operation On the primary database

On the primary database the switchover_status column of v$database check which is possible to perform a switchover operation

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

------------------

TO STANDBY

-In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.

SWITCHOVER_STATUS 

-------------------------------

 SESSIONS ACTIVE 

 -Then you should either disconnect all sessions manually (or) when performing step 3 you should append the “with session shutdown” clause.

For example:

 SQL> alter database commit to switchover to standby with session shutdown;

(Note: that the clause "with session shutdown" also works with the switchover to primary command 

Ex: alter database commit to switchover to primary with session shutdown;)

SWITCHOVER_STATUS 

-----------------------------

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. 

SWITCHOVER_STATUS 

-----------------------------

SESSIONS 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_STATUS 

-----------------------------

SWITCHOVER PENDING - 

-This is a standby database and the primary database switchover request has been received but not processed. 

SWITCHOVER_STATUS 

-----------------------------

SWITCHOVER LATENT - 

-The switchover was in pending mode, but did not complete and went back to the primary database.

SWITCHOVER_STATUS 

-----------------------------

TO PRIMARY                  

 - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

SWITCHOVER_STATUS 

-----------------------------

TO STANDBY 

- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

SWITCHOVER_STATUS 

-----------------------------

RECOVERY NEEDED 

- This is a standby database that has not received the switchover request.

During normal operations it is acceptable to see the following values for

SWITCHOVER_STATUS 

-----------------------------

 SESSIONS ACTIVE (or) TO STANDBY. ( on the primary database)

 During normal operations  it is acceptable to see these values

SWITCHOVER_STATUS 

-----------------------------

NOT ALLOWED (or) SESSIONS ACTIVE.  (on the standby database)

-During normal operations  it is acceptable to see these values 

3. Convert the primary database to the new standby:

SQL> alter database commit to switchover to physical standby ;

Database altered.

4. Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2217792 bytes
Variable Size             528484544 bytes
Database Buffers          285212672 bytes
Redo Buffers                2486272 bytes

SQL> alter database mount standby database;

Database altered.

5. Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_3=defer;

6. Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

------------------

SWITCHOVER PENDING

(Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 7.

7. Convert the physical standby to the new primary:

SQL> alter database commit to switchover to primary;

Database altered.

8. Shutdown and startup the new primary:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2217792 bytes
Variable Size             482347200 bytes
Database Buffers          331350016 bytes
Redo Buffers                2486272 bytes
Database mounted.
Database opened.

9. Enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_2=enable;

10. Start managed recover on the new standby database:

SQL> recover managed standby database disconnect;

Media recovery complete.

11) There are few steps we need to check after performing the switchover in dataguard environment 
a)If we are using the OEM check the status of  primary database in enterprise manager.
b)check the crontab jobs for Primary database need to enabled
c)Check for executing backups/exports and start as per schedule
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

FAILOVER Steps:

1. Initiate the failover on the standby database STAN:

SQL>connect /@STAN as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2. Immediately after issuing command in step 2, shut down and restart the standby instance STAN:

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

STAN is now transitioned to the primary database role.Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this change, again query the Database_role column of V$DATABASE. Now it will return “PRIMARY”.

SQL> Select Database_role from v$Database;