- Introduction: In Oracle, a switchover is a role transition operation between the primary and standby databases in a Data Guard environment. A switchover allows you to switch roles between the primary and standby databases in a planned manner, without data loss. This is typically done for maintenance, testing, or load balancing. Here we will do switchover activity using DG broker step-by-step.
- Prerequisites: o Downtime required at Database end for 30 mins.
- Environment:
- Now start Switchover activity step by step:
o Application and Database connectivity for Standby site.
o Primary and Standby databases should be in sync.
o DG Broker should be configured between Primary and Standby.
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
At Primary:
|
SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,SWITCHOVER_STATUS from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS --------- ------------------------------ ---------------- -------------------- -------------------- ORCL ORCLDC PRIMARY READ WRITE TO STANDBY SQL> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a, (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b, (select db_unique_name, database_role from v$database) d where a.thread#=b.thread#; SQL> Session altered. SQL>DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF ------------------------------ ---------------- ---------- ---------- ----------- -------------------- ---------- ORCLDC PRIMARY 1 62 62 24-JAN-2025 10:27:01 0 |
|
SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,SWITCHOVER_STATUS from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS --------- ------------------------------ ---------------- -------------------- -------------------- ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY NOT ALLOWED SQL> set lines 200 pages 300 SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS'; SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp, round((sysdate - a.last_app_timestamp) * 24 * 60, 2) Gap_in_Mins, round((sysdate - a.last_app_timestamp) * 24 * 60 * 60, 2) Gap_in_Seconds FROM (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a, (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b, (select db_unique_name, database_role from v$database) d where a.thread#=b.thread#; SQL> Session altered. SQL>DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS ------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- -------------- ORCLDR PHYSICAL STANDBY 1 62 62 0 24-JAN-2025 10:27:01 12.85 771 |
|
SQL> alter database switchover to orcldr verify; Database altered. |
If you receive any warning message like below then check DB alert log and take action accordingly.
|
SQL> alter database switchover to orcldr verify; alter database switchover to orcldr verify * ERROR at line 1: ORA-16475: succeeded with warnings, check alert log for more details |
|
[oracle@source ~]$ dgmgrl sys@orcldc DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Jan 24 10:40:44 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Password: Connected to "ORCLDC" Connected as SYSDBA. DGMGRL> DGMGRL> switchover to orcldr Performing switchover NOW, please wait... Operation requires a connection to database "orcldr" Connecting ... Connected to "ORCLDR" Connected as SYSDBA. New primary database "orcldr" is opening... Operation requires start up of instance "orcldc" on database "orcldc" Starting instance "orcldc"... Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLDC_DGMGRL)(INSTANCE_NAME=orcldc)(SERVER=DEDICATED))) ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. [W000 2025-01-24T10:42:16.151+05:30] Failed to attach to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLDC_DGMGRL)(INSTANCE_NAME=orcldc)(SERVER=DEDICATED))). Please complete the following steps to finish switchover: start up instance "orcldc" of database "orcldc" DGMGRL> exit |
At new Primary:
|
[oracle@target ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 24 10:43:15 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.24.0.0.0 SQL> SQL> set lines 200 pages 1000 col open_mode for a15 col HOST_NAME for a15 select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance; SQL> NAME DATABASE_ROLE OPEN_MODE INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS --------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------ ORCL PRIMARY READ WRITE 1 orcldr target 19.0.0.0.0 24-JAN-25 OPEN SQL> show parameter dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_online_log_dest_2 string log_archive_dest_2 string service="orcldc", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="orcldc" net_timeout=30, valid_for=(online_logfile,all_roles) log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string SQL> |
|
[oracle@source ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 24 10:43:00 2025 Version 19.24.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> SQL> startup mount ORACLE instance started. Total System Global Area 1459616616 bytes Fixed Size 9177960 bytes Variable Size 1174405120 bytes Database Buffers 268435456 bytes Redo Buffers 7598080 bytes Database mounted. SQL> set lines 200 pages 1000 col open_mode for a15 col HOST_NAME for a15 select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance; SQL> NAME DATABASE_ROLE OPEN_MODE INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS --------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------ ORCL PHYSICAL STANDBY MOUNTED 1 orcldc source 19.0.0.0.0 24-JAN-25 MOUNTED SQL> SQL> show parameter dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_online_log_dest_2 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string SQL> |
5. Now do some log switches on new Primary and check the sync.
|
SQL> alter system switch logfile; System altered. SQL> / System altered. |
Thanks for visiting!!
0 comments:
Post a Comment