| Enviroment Details | Primary | Standby |
| DB SID | INDIA | INDIA |
| DB ROLE | Primary | Physical Standby |
| DB Unique Name | INDIA | CHINA |
| Server IP | 192.168.25.159 | 192.168.25.158 |
| DB Version | 19.2.00 | 19.2.00 |
| OS | Oel7 | Oel7 |
Switchover database from Primary to Standby
> Planned failover to standby database
> Original primary becomes new standby
> Original standby becomes new primary
> No data loss
> Can switchback at anytime
Step 1:-
Check database role and database name
Primary database:-
SQL> select name,open_mode,database_role from v$database;
Standby database:-
SQL> select name,open_mode,database_role from v$database;
Step 2:- Up to 11g we need to check below query
SQL> 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 ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Sql> select switchover_status from v$database;
From 12c Onwards
SQL> select switchover_status from v$database;
Step 3: Just do defer and enable dest_state_2 for synchronize propose and also check the sequence number.
Sql> alter system, set log_archive_dest_state_2=defer;
Sql> alter system, set log_archive_dest_state_2=defer;
Sql> select max(sequence#) from v$archived_log;
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Step 4: Stop the MRP process and start the MRP from standby for synchronize. Please check it in alert log.
On Primary database:-
Step 5:
SQL> alter database commit to switchover to standby;
Step 6:- Old primary now changed to standby
Sql> shutdown immediate
Sql>startupnomount
Sql> alter database mount standby database;
Step 7:- Check the Mode of the database.
Sql> select name,open_mode,database_role from v$database;
Step 8 : Start the MRP from new standby old primary.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 9 – New primary old standby
SQL> alter database commit to switchover to primary;
Database altered.
From new primary side.
Step 10 : Check alertlog and login to primary database “alter system switch logifile” 2 times
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
Step 12 : Check from the both the side and this should be same.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
2 3 4 5 6 7
