Oracle Data Guard Manual Switchover.
Description:
In this blog, we are going to see step by step process of oracle data guard manual switchover process physical standby to primary.
Switch Over:
A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role. The transition occurs without having to re-enable either database.
Overall Steps:
1. Check Primary and Standby database role and open_mode. 2. Change nls_date_format and check current log sync status. 3. Check switch over status both primary and standby servers. 4. Primary side switch over process. 5. Standby side switch over process. 6. Check the log sync status both sides. 7. New primary database parameter configuration. 8. Switch log files and check the log sync status.
Step 1:- Check primary and standby database role and open_mode.
SQL> select name,open_mode,database_role from v$database;
Step 2:- Change nls_date_format and check current log sync status.
check the log sequence on both primary and standby sides.
SQL> ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;
Session altered.
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
Step 3:- Check switch over status both primary and standby servers.
SQL> select switchover_status from v$database;
Step 4:- Primary side switch over process.
Switch over to standby.
SQL> alter database commit to switchover to standby;
Database altered.
Bounce the database.
SQL> shut immediate
SQL> startup nomount
Mount standby database.
SQL> alter database mount standby database;
Start MRP process.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
check the database role.
SQL> select name,open_mode,database_role from v$database;
Step 5:- Standby side switchover process:
Stop the MRP process.
SQL> alter database recover managed standby database cancel;
Database altered.
Switch Over to Primary.
SQL> alter database commit to switchover to primary;
Database altered.
Check database role changed or not and open_mode.
SQL> select name,open_mode,database_role from v$database;
Open the database.
SQL> alter database open;
Database altered.
Check the database role and open_mode.
SQL> select name,open_mode,database_role from v$database;
Step 6:- Check the log sync status both sides.
SQL> archive log list
Check both side log sync status.
Step 7:- New primary database parameter configuration for log sync.
SQL> alter system set log_archive_dest_1 =’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=local’ scope=spfile;
System altered.
SQL> alter system set log_archive_dest_2 =’service=cloud async valid_for=(online_logfiles,primary_role) db_unique_name=cloud’ scope=spfile;
System altered.
SQL> alter system set db_file_name_convert=’/u01/app/oracle/oradata/LOCAL/datafile/’,’/u01/app/oracle/oradata/CLOUD/datafile/’ scope=spfile;
System altered.
SQL> alter system set log_file_name_convert=’/u01/app/oracle/oradata/LOCAL/onlinelog/’,’/u01/app/oracle/oradata/CLOUD/onlinelog/’ scope=spfile;
System altered.
SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(local,cloud)’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET fal_server=’cloud’ SCOPE=both;
System altered.
SQL> ALTER SYSTEM SET fal_client=’local’ SCOPE=both;
System altered.
SQL> select DEST_NAME,STATUS,ERROR from v$ARCHIVE_DEST where status!=’INACTIVE’;
DEST_NAME
——————————————————————————–
STATUS ERROR
——— —————————————————————–
LOG_ARCHIVE_DEST_1
VALID
Step 8:-Switch log files and check the log sync status.
Primary side switch logfiles:
enable log_archive_dest_state_2.
SQL> alter system switch logfile;
SQL> archive log list
Check log file sync status on standby side:-
Restart the MRP process.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
check log sync status.
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;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
