Oracle 11g Data Gaurd switch over (Primary database to Physical standby)

# switch over primary to standby and vice-versa

1.Switch log file on primary database.

sql>alter system switch logfile;

2. check gap on primary database.

sql> select status, gap_status from v$archive_dest_status where dest_id=2;

3. Check delay minutes on primary databases;

sql> select delay_mins from v$archive_dest where dest_id = 2; 

4. Check switchover status before switching database.

sql> select switchover_status from v$database;

output must be “TO STANDBY”

5. Check database role status.

sql> select database_role from v$database; 

6.  Switch primary database to standby database.

sql>alter database commit to switchover to physical standby with session shutdown;

7. Shutdown primary database.

sql>shutdown immediate;

8. Start primary database with mount mode.

sql>startup mount;

# steps to perform on standby database 

1. Verify switchover status:- 

sql> select switchover_status from v$database

2. Cancel recovery on standby database. 

sql> alter database recover managed standby database cancel;

3. Switch to primary from standby database

sql>alter database commit to switchover to primary with session shutdown;

sql>shutdown immediate;

sql>startup;

Check if switch over success;

a. Check database role on both servers

sql> select database_role from v$database

b. Switch over recovery on old primary database. 

sql> alter database recover managed standby database using current logfile disconnect from session;

c. check the health 

sql> select al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied” 

from (select thread# thrd, max(sequence#) almax from v$archived_log 

where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#) al, (select thread# thrd, max(sequence#) lhmax from v$log_history where 

first_time=(select max(first_time) from v$log_history) 

group by thread#) lh

where al.thrd = lh.thrd;