Below are all the steps to be followed to SWITCHOVER from Primary to DR database using Oracle 19c DGMGRL broker utility.
Prepare for the switchover
Before starting the switchover process, make sure that both the primary and standby databases are synchronized and that all required logs have been applied to the standby database. Also, ensure that the broker is configured and running on both the primary and standby sites.
Step 1: Check Archvie Gap
Before starting the switchover activity let's verify the archive gap between primary and standby.
On Primary:
SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d
where v.resetlogs_change# = d.resetlogs_change#
group by thread# order by 1;
THREAD# Last Primary Seq# Generated
---------- ---------------------------
1 27On Standby:
SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d
where v.resetlogs_change# = d.resetlogs_change#
group by thread# order by 1; 2 3
THREAD# Last Primary Seq# Generated
---------- ---------------------------
1 27Step 2: Connect to the broker
Connect to the broker using the dgmgrl utility on the primary database. For example, you can run the command "dgmgrl /" to connect to the default broker configuration.
[oracle@primary ~]$ dgmgrl sys/pwd
Welcome to DGMGRL, type "help" for information.
Connected to "digital"
Connected as SYSDBA.Step 3: Verify the configuration
Verify that the broker configuration is correct by running the "show configuration" command. This will display the current configuration settings for the broker.
DGMGRL> show configuration
Configuration - digital
Protection Mode: MaxPerformance
Members:
digital - Primary database
digidr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 51 seconds ago)In this configuration, we have Primary Database "DIGITAL" and the standby Database is "DIGIDR".
Step 4: Prepare for the switchover
Prepare for the switchover by running the "validate database" command to ensure that both the primary and standby databases are ready for switchover.
Validate Primary Database
DGMGRL> validate database digital;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
digital: Off
Managed by Clusterware:
digital: NO
Validating static connect identifier for the primary database digital...
The static connect identifier allows for a connection to database "digital".Validate Standby Database
DGMGRL> validate database digidr;
Database Role: Physical standby database
Primary Database: digital
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
digital: Off
digidr : Off
Managed by Clusterware:
digital: NO
digidr : NO
Validating static connect identifier for the primary database digital...
The static connect identifier allows for a connection to database "digital".
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(digital) (digidr)
1 3 2 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(digidr) (digital)
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on digital
Step 5: Initiate the switchover
Initiate the switchover by running the "switchover to " command in dgmgrl. This will start the switchover process and automatically failover the primary database to the standby database.
DGMGRL> switchover to digidr;
Performing switchover NOW, please wait...
Operation requires a connection to database "digidr"
Connecting ...
Connected to "digidr"
Connected as SYSDBA.
New primary database "digidr" is opening...
Operation requires start up of instance "digital" on database "digital"
Starting instance "digital"...
Connected to an idle instance.
ORACLE instance started.
Connected to "digital"
Database mounted.
Database opened.
Connected to "digital"
Switchover succeeded, new primary is "digidr"Step 6: Verify the switchover
After the switchover, verify that the new primary database is running by running the "show database" command in dgmgrl.
Current Primary digidr
DGMGRL> show database digidr
Database - digidr
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
digidr
Database Status:
SUCCESSCurrent standby digital
DGMGRL> show database digital
Database - digital
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
digital
Database Status:
SUCCESSDatabase Warnings
Sometimes you will face the below error, then you have to check the log_archive_dest_1 and log_archive_dest_2 parameter on the standby side and configure it properly.
DGMGRL> show database digital
Database - digital
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: ON
Instance(s):
digital
Database Warning(s):
ORA-16854: apply lag could not be determined
ORA-16856: transport lag could not be determined
ORA-16858: last communication time from redo source could not be determined
Database Status:
WARNING
DGMGRL> show database digidr;
Database - digidr
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
digidr
Error: ORA-16736: unable to find the destination entry of member "digital" in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property RedoRoutes found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property LogXptMode found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property DelayMins found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property Binding found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property MaxFailure found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property ReopenSecs found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Warning: ORA-16728: consistency check for property RedoCompression found ORA-16777 error
Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
Database Status:
ERRORStep 7: Reconfigure the environment
If necessary, reconfigure the environment to point to the new primary database. For example, update connection strings, DNS entries, or load balancer settings to point to the new primary database.
Step 8: Start Recovery
After the switchover start the MRP process using the broker or manually.
Enable with Broker:
DGMGRL> EDIT DATABASE digital SET STATE='APPLY-ON';
Succeeded.Check MRP status using SQL command, if no row is selected that means MRP is disabled.
SQL> select inst_id, process, status from gv$managed_standby where process='MRP0';
INST_ID PROCESS STATUS
---------- --------- ------------
1 MRP0 APPLYING_LOGDisable with Broker
DGMGRL> EDIT DATABASE digital SET STATE='APPLY-OFF';Enable/Disable manually:
SQL> alter database recover managed standby database disconect from session;
SQL> alter database recover managed standby database cancel;Note
Note that the exact steps for performing a switchover using a broker may vary depending on your specific configuration and requirements. It is important to carefully review the Oracle Data Guard documentation and consult with experienced database administrators before attempting a switchover.
