Introduction
A switchover is a planned role transition between the primary and standby databases in a Data Guard environment, done for maintenance, testing, or periodic load balancing, without any data loss. We covered performing a switchover manually with SQL*Plus in an earlier post. This guide covers the same operation done through Data Guard Broker instead, using a single DGMGRL command rather than a sequence of manual SQL statements on each side. Broker handles most of the coordination automatically, though there's one connectivity quirk worth knowing about in advance, covered in Step 3.
Prerequisites
- Plan for roughly 30 minutes of database downtime during the switchover window.
- Confirm application and database connectivity are ready at the standby site.
- Primary and standby databases must be fully in sync before starting.
- Data Guard Broker must already be configured between the primary and standby. See our Data Guard Broker configuration guide if you haven't set this up yet.
Environment Used in This Guide
| 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 |
Step 1: Check Database Status and Sync
Even with Broker managing the environment, it's worth confirming both databases agree on their roles and that the standby has no unapplied redo before triggering the switchover. This check is identical whether you're switching over manually or via Broker.
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';
Session altered.
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#;
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
At Standby:
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';
Session altered.
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#;
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
Step 2: Verify Switchover Readiness on the Primary
Run the same verify-only check used in a manual switchover before doing anything through Broker. This checks for problems without making real changes.
SQL> alter database switchover to orcldr verify;
Database altered.
If this completes cleanly, proceed to Step 3. If you see a warning like the one below, check the database alert log before continuing.
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
Step 3: Run the Switchover Through DGMGRL
This is where Broker changes the workflow meaningfully compared to the manual method. Instead of running separate commands on each database, one switchover to command from DGMGRL handles the whole role transition, including attempting to start the old primary as the new standby.
[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
This ORA-12514 error is a common and mostly harmless quirk: Broker successfully switches the roles, but it can't remotely start the old primary's instance because that instance isn't registered with the listener yet (it just went down as part of the role change). This isn't a failure of the switchover itself, the role transition already completed, it just means you need to manually start the new standby instance yourself, which is exactly what Step 4 does.
Step 4: Start the New Standby and Verify the New Primary
The new primary typically comes up on its own once Broker completes its side of the transition, so this step is mostly about starting the new standby instance manually (working around the TNS error from Step 3) and confirming both sides.
At new Primary: verify it's already open and read/write. Notice log_archive_dest_2 is now set automatically, Broker configured this for you as part of the role swap, you didn't have to set it manually the way you would in a non-Broker switchover.
[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> set lines 200 pages 1000
SQL> col open_mode for a15
SQL> col HOST_NAME for a15
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;
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>
At new Standby: manually start and mount this instance, since Broker couldn't reach it automatically due to the listener error in Step 3.
[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> 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
SQL> col open_mode for a15
SQL> col HOST_NAME for a15
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;
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> 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>
Note: on the new standby, log_archive_dest_2 shows blank here, but it resets automatically once Broker fully reconciles the configuration, and managed recovery (MRP) starts on its own as well. This is the main advantage of switching over via Broker: you don't manually re-set redo transport parameters like you would in a non-Broker switchover.
Step 5: Confirm Sync on the New Primary
Switch a couple of logs on the new primary and confirm the new standby is receiving and applying them, using the same verification pattern as Step 1.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Switching Back
A switchback follows the same steps in reverse. Run switchover to orcldc from DGMGRL on what is now the primary (ORCLDR), and repeat Steps 2 through 5 exactly as above.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment