I recently wrote an article about how to combine Oracle SEHA database running on ODA 2-HA and dbvisit StandbyMP solution. See my article here : https://www.dbi-services.com/blog/combine-oracle-seha-on-oda-with-dbvisit-standbymp/. But are really all those high availability and disaster recovering solutions working together? Do Oracle SEHA relocate and failover functionalities work in conjunction with dbvisit StandbyMP switchover? Let’s see and try it!
Test environment
Just to recall my test environment
- an ODA X5-2-HA with 2 nodes dbioda02 and dbioda03
- an ODA X8-2M with node named dbi-oda-x8
The ODA X5-2-HA will host an HA database with db_unique_name DBISEHA, which I could relocate to the other nodes or benefit of the automatic failover in case the privilege nodes is crashing.
The ODA X8-2M will host a single instance database, with db_unique_name DBISEHA_STD, initially the standby one.
Perform a graceful swithover
Let’s run a switchover between primary node dbi0da02 and dbi-oda-x8 nodes.
The primary database instance is hosted on node dbioda02:
oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle 18471 1 0 Jan27 ? 00:01:09 ora_pmon_DBISEHA oracle@dbioda03:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle@dbioda03:/home/oracle/ [DBISEHA]
The primary database :
oracle@dbioda02:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISEHA OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO *************************************
The standby database:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : MOUNTED DB_UNIQUE_NAME : DBISEHA_STD OPEN_MODE : MOUNTED LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO *************************************
Let’s ship all archive logs from primary to standby database:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 30570) dbvctl started on dbvisitvippri: Fri Feb 17 10:00:14 2023 ============================================================= >>> Obtaining information from standby database (RUN_INSPECT=Y)... done Thread: 1 Archive log gap: 0. Transfer log gap: 0 >>> Sending heartbeat message... skipped >>> Performing a log switch... done >>> Transferring Log file(s) from DBISEHA on dbvisitvippri to dbi-oda-x8: thread 1 sequence 7 (thread_1_seq_7.333.1129024819)... done ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 10:00:46 2023 =============================================================
Let’s apply them on the standby database:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 2528) dbvctl started on dbi-oda-x8: Fri Feb 17 10:00:46 2023 ============================================================= >>> Sending heartbeat message... skipped >>> Applying Log file(s) from dbvisitvippri to DBISEHA on dbi-oda-x8: thread 1 sequence 7 (1_7_1127209532.arc)... done Last applied log(s): thread 1 sequence 7 Next SCN required for recovery 3952980 generated at 2023-02-17:10:00:19 +01:00. Next required log thread 1 sequence 8 ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 10:01:11 2023 =============================================================
Let’s confirm there is no gap between the primary and the standby databases:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 20814) dbvctl started on dbvisitvippri: Fri Feb 17 10:50:04 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA at 202302171050: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 3954545 2023-02-17:10:50:05 +01:00 Destination 3952980 2023-02-17:10:00:19 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:49:46 Report for Thread 1 ------------------- SOURCE Current Sequence 8 Last Archived Sequence 7 Last Transferred Sequence 7 Last Transferred Timestamp 2023-02-17 10:00:45 DESTINATION Next Required Recovery Sequence 8 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 10:50:07 2023 =============================================================
Transfer and apply log gap output been 0, both primary and standby databases are in sync.
Let’s switchover!
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -o switchover ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 23650) dbvctl started on dbvisitvippri: Fri Feb 17 10:52:07 2023 ============================================================= >>> Starting Switchover between dbvisitvippri and dbi-oda-x8 Running pre-checks ... done =>Do you want to proceed with Graceful Switchover? [no]: yes Your input: 1 Is this correct? [Yes]: Pre processing ... done Processing primary ... done Processing standby ... done Converting standby ... done Converting primary ... done Completing ... done Synchronizing ... done Post processing ... done >>> Graceful switchover completed. Primary Database Server: dbi-oda-x8 Standby Database Server: dbvisitvippri >>> Dbvisit Standby can be run as per normal: dbvctl -d DBISEHA As part of the Switchover process, the primary and standby controlfiles have been exchanged.Unless you are using RMAN catalog database, you may need to cross-check all backups and review RMAN settings using the SHOW ALL command on the new Primary/Standby databases. Confirm the path set for the SNAPSHOT CONTROLFILE NAME TO setting is valid on both sides. PID:23650 TRACE:23650_dbvctl_switchover_DBISEHA_202302171052.trc ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 10:59:28 2023 =============================================================
My new primary database is DBISEHA_STD hosted on dbi-oda-x8:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISEHA_STD OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO *************************************
My new standby database is DBISEHA hosted on dbioda02:
oracle@dbioda02:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : MOUNTED DB_UNIQUE_NAME : DBISEHA OPEN_MODE : MOUNTED LOG_MODE : ARCHIVELOG DATABASE_ROLE : PHYSICAL STANDBY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO *************************************
Let’s generate 3 archive logs on the primary database:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 17 11:04:54 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter system archive log current; System altered. SQL> alter system archive log current; System altered. SQL> alter system archive log current; System altered.
Send those archive logs to the new standby database:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 2981) dbvctl started on dbi-oda-x8: Fri Feb 17 11:06:12 2023 ============================================================= >>> Obtaining information from standby database (RUN_INSPECT=Y)... done Thread: 1 Archive log gap: 3. Transfer log gap: 3 >>> Transferring Log file(s) from DBISEHA on dbi-oda-x8 to dbvisitvippri: thread 1 sequence 14 (thread_1_seq_14.360.1129028709)... done thread 1 sequence 15 (thread_1_seq_15.361.1129028711)... done thread 1 sequence 16 (thread_1_seq_16.362.1129028715)... done ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 11:06:21 2023 =============================================================
Apply them on the new standby database:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 57569) dbvctl started on dbvisitvippri: Fri Feb 17 11:07:18 2023 ============================================================= >>> Applying Log file(s) from dbi-oda-x8 to DBISEHA on dbvisitvippri: thread 1 sequence 14 (1_14_1127209532.arc)... done thread 1 sequence 15 (1_15_1127209532.arc)... done thread 1 sequence 16 (1_16_1127209532.arc)... done Last applied log(s): thread 1 sequence 16 Next SCN required for recovery 3958195 generated at 2023-02-17:11:05:13 +01:00. Next required log thread 1 sequence 17 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 11:07:32 2023 =============================================================
Control transfer and apply gap to make sure both primary and standby databases are in sync:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 6687) dbvctl started on dbi-oda-x8: Fri Feb 17 11:08:22 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA_STD at 202302171108: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 3958348 2023-02-17:11:08:22 +01:00 Destination 3958195 2023-02-17:11:05:13 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:03:09 Report for Thread 1 ------------------- SOURCE Current Sequence 17 Last Archived Sequence 16 Last Transferred Sequence 16 Last Transferred Timestamp 2023-02-17 11:06:21 DESTINATION Next Required Recovery Sequence 17 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 11:08:27 2023 =============================================================
Relocate Oracle SEHA database
The Oracle SEHA database, DBISEHA, has currently the standby role. Of course I could test and relocate the standby database to the other node, dbioda03, or decide to test the relocation functionality having the database in the primary role. Let’s do that, and this will give me the opportunity as well to first test the switchover back.
Switchover back to dbioda02, hosting the VIP dbvisitvippri.
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -o switchover ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 8871) dbvctl started on dbi-oda-x8: Fri Feb 17 11:09:47 2023 ============================================================= >>> Starting Switchover between dbi-oda-x8 and dbvisitvippri Running pre-checks ... done =>Do you want to proceed with Graceful Switchover? [no]: yes Your input: 1 Is this correct? [Yes]: Pre processing ... done Processing primary ... done Processing standby ... done Converting standby ... done Converting primary ... done Completing ... done Synchronizing ... done Post processing ... done >>> Graceful switchover completed. Primary Database Server: dbvisitvippri Standby Database Server: dbi-oda-x8 >>> Dbvisit Standby can be run as per normal: dbvctl -d DBISEHA As part of the Switchover process, the primary and standby controlfiles have been exchanged.Unless you are using RMAN catalog database, you may need to cross-check all backups and review RMAN settings using the SHOW ALL command on the new Primary/Standby databases. Confirm the path set for the SNAPSHOT CONTROLFILE NAME TO setting is valid on both sides. PID:8871 TRACE:8871_dbvctl_switchover_DBISEHA_202302171109.trc ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 11:17:41 2023 =============================================================
So my primary database instance is really running on the node 0, dbioda02:
oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle 77736 1 0 11:16 ? 00:00:00 ora_pmon_DBISEHA oracle@dbioda02:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 inet 10.36.0.246/24 brd 10.36.0.255 scope global secondary pubnet:2 oracle@dbioda02:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISEHA OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO ************************************* oracle@dbioda03:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle@dbioda03:/home/oracle/ [DBISEHA] oracle@dbioda03:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 oracle@dbioda03:/home/oracle/ [DBISEHA] oracle@dbioda03:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : STOPPED *************************************
And both primary and standby are in sync:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 97164) dbvctl started on dbvisitvippri: Fri Feb 17 11:27:16 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA at 202302171127: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 3962813 2023-02-17:11:27:16 +01:00 Destination 3962604 2023-02-17:11:21:48 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:05:28 Report for Thread 1 ------------------- SOURCE Current Sequence 26 Last Archived Sequence 25 Last Transferred Sequence 25 Last Transferred Timestamp 2023-02-17 11:22:32 DESTINATION Next Required Recovery Sequence 26 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 11:27:19 2023 =============================================================
Doing a relocate from the instance database to the other nodes will not be a problem for dbvisit even if we were having some gap. Why? Because…
- The FRA is on the shared storage, mandatory for both instances to have access to the same FRA
- Dbvisit archive log file system is also an ACFS shared file system
- Dbvisit software directory is also running on an ACFS shared file system
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +RECO db_recovery_file_dest_size big integer 200G oracle@dbioda02:/home/oracle/ [DBISEHA] df -h /u90/app/oracle/dbvisit_arch Filesystem Size Used Avail Use% Mounted on /dev/asm/dbvarclog-269 60G 767M 60G 2% /u90/app/oracle/dbvisit_arch oracle@dbioda02:/home/oracle/ [DBISEHA] df -h /u01/app/dbvisit Filesystem Size Used Avail Use% Mounted on /dev/asm/dbvisit-269 100G 1.3G 99G 2% /u01/app/dbvisit
We will test having an archive log gap between the primary and the standby with the Oracle SEHA failover.
Relocate Oracle SEHA primary database to node 1, dbioda03:
[root@dbioda02 ~]# odacli modify-database -in DBISEHA -g 1 { "jobId" : "c38c2457-5849-4623-9e16-b5e1c75b4de1", "status" : "Created", "message" : "Modify database", "reports" : [ ], "createTimestamp" : "February 17, 2023 11:36:58 AM CET", "resourceList" : [ { "resourceId" : "bed8b9fb-214a-423e-b0e8-d8c084b45dd1", "resourceType" : "DB", "resourceNewType" : null, "jobId" : "c38c2457-5849-4623-9e16-b5e1c75b4de1", "updatedTime" : "February 17, 2023 11:36:58 AM CET" } ], "description" : "Modify database : DBISEHA", "updatedTime" : "February 17, 2023 11:36:58 AM CET", "jobType" : "" } [root@dbioda02 ~]# odacli describe-job -i c38c2457-5849-4623-9e16-b5e1c75b4de1 Job details ---------------------------------------------------------------- ID: c38c2457-5849-4623-9e16-b5e1c75b4de1 Description: Modify database : DBISEHA Status: Success Created: February 17, 2023 11:36:58 AM CET Message: Modify database Task Name Start Time End Time Status ---------------------------------------- ----------------------------------- ----------------------------------- ---------- Relocate database {DBISEHA} February 17, 2023 11:36:59 AM CET February 17, 2023 11:37:54 AM CET Success
Relocate the VIP, dbvisitvippri, on the node 1:
oracle@dbioda02:/home/oracle/ [DBISEHA] crsctl relocate resource dbvisitvippri -f CRS-2673: Attempting to stop 'dbvagentmanager' on 'dbioda02' CRS-2677: Stop of 'dbvagentmanager' on 'dbioda02' succeeded CRS-2673: Attempting to stop 'dbvisitvippri' on 'dbioda02' CRS-2677: Stop of 'dbvisitvippri' on 'dbioda02' succeeded CRS-2672: Attempting to start 'dbvisitvippri' on 'dbioda03' CRS-2676: Start of 'dbvisitvippri' on 'dbioda03' succeeded CRS-2672: Attempting to start 'dbvagentmanager' on 'dbioda03' [dbvagentmanager] Starting Dbvisit dbvagentmanagerSubmitted check for dbvagentmanager with result not running correctly, return 1 CRS-2676: Start of 'dbvagentmanager' on 'dbioda03' succeeded
I can check and confirm that the oracle instance, the vip and the Dbvisit process has been relocated from dbioda02 to dbioda03:
oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle@dbioda02:/home/oracle/ [DBISEHA] oracle@dbioda02:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 oracle@dbioda02:/home/oracle/ [DBISEHA] oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [d]bv oracle@dbioda02:/home/oracle/ [DBISEHA] oracle@dbioda03:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle 93947 1 0 11:37 ? 00:00:00 ora_pmon_DBISEHA oracle@dbioda03:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 inet 10.36.0.246/24 brd 10.36.0.255 scope global secondary pubnet:3 oracle@dbioda03:/home/oracle/ [DBISEHA] ps -ef | grep [d]bv oracle 23378 1 0 11:53 ? 00:00:00 /u01/app/dbvisit/standbymp/bin/dbvagentmanager service run oracle 23514 23378 0 11:53 ? 00:00:00 /u01/app/dbvisit/standbymp/bin/dbvhelper -agentManagerId 2v7stu05tncsp -directorId 3pheqx762v9xr -hostAddress dbvisitvippri -natsAddress dbi-oda-x8 -natsPort 5533 -configurationType Oracle -helperProcessKey oracle:2v7stu05tncsp
Instance is running on node 1 now:
oracle@dbioda02:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : STOPPED ************************************* oracle@dbioda03:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISEHA OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO *************************************
Let’s create a few archive logs:
oracle@dbioda03:/home/oracle/ [DBISEHA] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 17 13:16:55 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter system archive log current; System altered. SQL> / System altered. SQL> / System altered.
Send the new archive logs to the standby database:
oracle@dbioda03:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 53287) dbvctl started on dbvisitvippri: Fri Feb 17 13:18:32 2023 ============================================================= >>> Obtaining information from standby database (RUN_INSPECT=Y)... done Thread: 1 Archive log gap: 3. Transfer log gap: 3 >>> Transferring Log file(s) from DBISEHA on dbvisitvippri to dbi-oda-x8: thread 1 sequence 26 (thread_1_seq_26.345.1129036623)... done thread 1 sequence 27 (thread_1_seq_27.346.1129036627)... done thread 1 sequence 28 (thread_1_seq_28.347.1129036629)... done ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 13:18:42 2023 =============================================================
Apply them on the standby database:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 89786) dbvctl started on dbi-oda-x8: Fri Feb 17 13:19:21 2023 ============================================================= >>> Applying Log file(s) from dbvisitvippri to DBISEHA on dbi-oda-x8: thread 1 sequence 26 (1_26_1127209532.arc)... done thread 1 sequence 27 (1_27_1127209532.arc)... done thread 1 sequence 28 (1_28_1127209532.arc)... done Last applied log(s): thread 1 sequence 28 Next SCN required for recovery 3967784 generated at 2023-02-17:13:17:08 +01:00. Next required log thread 1 sequence 29 ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 13:19:27 2023 =============================================================
Check transfer and apply gap:
oracle@dbioda03:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 55244) dbvctl started on dbvisitvippri: Fri Feb 17 13:20:01 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA at 202302171320: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 3967925 2023-02-17:13:20:02 +01:00 Destination 3967784 2023-02-17:13:17:08 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:02:54 Report for Thread 1 ------------------- SOURCE Current Sequence 29 Last Archived Sequence 28 Last Transferred Sequence 28 Last Transferred Timestamp 2023-02-17 13:18:42 DESTINATION Next Required Recovery Sequence 29 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 13:20:04 2023 =============================================================
I tested a switchover to dbi-oda-x8:
oracle@dbioda03:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -o switchover ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 58371) dbvctl started on dbvisitvippri: Fri Feb 17 13:21:39 2023 ============================================================= >>> Starting Switchover between dbvisitvippri and dbi-oda-x8 Running pre-checks ... done =>Do you want to proceed with Graceful Switchover? [no]: yes Your input: 1 Is this correct? [Yes]: Pre processing ... done Processing primary ... done Processing standby ... done Converting standby ... done Converting primary ... done Completing ... done Synchronizing ... done Post processing ... done >>> Graceful switchover completed. Primary Database Server: dbi-oda-x8 Standby Database Server: dbvisitvippri >>> Dbvisit Standby can be run as per normal: dbvctl -d DBISEHA As part of the Switchover process, the primary and standby controlfiles have been exchanged.Unless you are using RMAN catalog database, you may need to cross-check all backups and review RMAN settings using the SHOW ALL command on the new Primary/Standby databases. Confirm the path set for the SNAPSHOT CONTROLFILE NAME TO setting is valid on both sides. PID:58371 TRACE:58371_dbvctl_switchover_DBISEHA_202302171321.trc ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 13:29:11 2023 =============================================================
And after sending/applying a few archive logs, I switchovered back:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -o switchover ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 14235) dbvctl started on dbi-oda-x8: Fri Feb 17 13:31:22 2023 ============================================================= >>> Starting Switchover between dbi-oda-x8 and dbvisitvippri Running pre-checks ... done =>Do you want to proceed with Graceful Switchover? [no]: yes Your input: 1 Is this correct? [Yes]: Pre processing ... done Processing primary ... done Processing standby ... done Converting standby ... done Converting primary ... done Completing ... done Synchronizing ... done Post processing ... done >>> Graceful switchover completed. Primary Database Server: dbvisitvippri Standby Database Server: dbi-oda-x8 >>> Dbvisit Standby can be run as per normal: dbvctl -d DBISEHA As part of the Switchover process, the primary and standby controlfiles have been exchanged.Unless you are using RMAN catalog database, you may need to cross-check all backups and review RMAN settings using the SHOW ALL command on the new Primary/Standby databases. Confirm the path set for the SNAPSHOT CONTROLFILE NAME TO setting is valid on both sides. PID:14235 TRACE:14235_dbvctl_switchover_DBISEHA_202302171331.trc ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 13:39:30 2023 =============================================================
Oracle SEHA failover with active Dbvisit StandbyMP configuration running
Let’s try the automatic failover functionality from Oracle SEHA. Purpose will be to crash the active node 1, dbioda03, where the primary database is running.
To recall, the primary database instance, the VIP and dbvisit processes are currently running on node dbioda03.
oracle@dbioda03:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle 92866 1 0 13:38 ? 00:00:00 ora_pmon_DBISEHA oracle@dbioda03:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 inet 10.36.0.246/24 brd 10.36.0.255 scope global secondary pubnet:3 oracle@dbioda03:/home/oracle/ [DBISEHA] ps -ef | grep [d]bv oracle 23378 1 0 11:53 ? 00:00:06 /u01/app/dbvisit/standbymp/bin/dbvagentmanager service run oracle 23514 23378 0 11:53 ? 00:00:00 /u01/app/dbvisit/standbymp/bin/dbvhelper -agentManagerId 2v7stu05tncsp -directorId 3pheqx762v9xr -hostAddress dbvisitvippri -natsAddress dbi-oda-x8 -natsPort 5533 -configurationType Oracle -helperProcessKey oracle:2v7stu05tncsp oracle@dbioda03:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISEHA OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO ************************************* oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle@dbioda02:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [d]bv oracle@dbioda02:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : STOPPED *************************************
Let’s create a gap between primary and standby database:
oracle@dbioda03:/home/oracle/ [DBISEHA] sqh SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 17 13:49:36 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> alter system archive log current; System altered. SQL> / System altered. SQL> / System altered.
We can see that the last created archive log file have neither been transferred nor applied on the standby database:
oracle@dbioda03:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 16473) dbvctl started on dbvisitvippri: Fri Feb 17 13:50:02 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA at 202302171350: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 3975799 2023-02-17:13:50:02 +01:00 Destination 3975504 2023-02-17:13:42:22 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:07:40 Report for Thread 1 ------------------- SOURCE Current Sequence 50 Last Archived Sequence 49 Last Transferred Sequence 46 Last Transferred Timestamp 2023-02-17 13:42:37 DESTINATION Next Required Recovery Sequence 47 Transfer Log Gap 3 Apply Log Gap 3 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 13:50:05 2023 =============================================================
Let’s crash ODA X5-2-HA node 1, dbioda03, running the primary database instance:
[root@dbioda03 ~]# date Fri Feb 17 13:53:52 CET 2023 [root@dbioda03 ~]# systemctl poweroff Connection to 10.36.0.233 closed by remote host. Connection to 10.36.0.233 closed.
Very quickly, about one minute later, I can see that the instance of oracle SEHA database, DBISEHA, got automatically failovered on node 0, dbioda02. The VIP, dbvisitvippri, has been also automatically relocated on node 0. The implemented dbvagentmanager script has automatically started, with the VIP relocation, the needed dbvisit processes.
oracle@dbioda02:/home/oracle/ [DBISEHA] date Fri Feb 17 13:55:07 CET 2023 oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [p]mon | grep -i dbiseha oracle 31132 1 0 13:54 ? 00:00:00 ora_pmon_DBISEHA oracle@dbioda02:/home/oracle/ [DBISEHA] ip addr sh pubnet | grep 10.36.0.246 inet 10.36.0.246/24 brd 10.36.0.255 scope global secondary pubnet:2 oracle@dbioda02:/home/oracle/ [DBISEHA] ps -ef | grep [d]bv oracle 30423 1 0 13:54 ? 00:00:00 /u01/app/dbvisit/standbymp/bin/dbvagentmanager service run oracle 30496 30423 0 13:54 ? 00:00:00 /u01/app/dbvisit/standbymp/bin/dbvhelper -agentManagerId 2v7stu05tncsp -directorId 3pheqx762v9xr -hostAddress dbvisitvippri -natsAddress dbi-oda-x8 -natsPort 5533 -configurationType Oracle -helperProcessKey oracle:2v7stu05tncsp oracle@dbioda02:/home/oracle/ [DBISEHA] DBISEHA ********* dbi services Ltd. ********* STATUS : OPEN DB_UNIQUE_NAME : DBISEHA OPEN_MODE : READ WRITE LOG_MODE : ARCHIVELOG DATABASE_ROLE : PRIMARY FLASHBACK_ON : NO FORCE_LOGGING : YES VERSION : 19.15.0.0.0 CDB Enabled : NO *************************************
All seems ok and our Dbvisit configuration should go on working.
We still have our 3 archive log gap, plus one additional following the instance failover:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 51716) dbvctl started on dbvisitvippri: Fri Feb 17 14:04:16 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA at 202302171404: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 4077647 2023-02-17:14:04:18 +01:00 Destination 3975504 2023-02-17:13:42:22 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:21:56 Report for Thread 1 ------------------- SOURCE Current Sequence 51 Last Archived Sequence 50 Last Transferred Sequence 46 Last Transferred Timestamp 2023-02-17 13:42:37 DESTINATION Next Required Recovery Sequence 47 Transfer Log Gap 4 Apply Log Gap 4 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 14:04:20 2023 =============================================================
We can ship these 4 archive logs to the standby side:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 56938) dbvctl started on dbvisitvippri: Fri Feb 17 14:05:32 2023 ============================================================= >>> Obtaining information from standby database (RUN_INSPECT=Y)... done Thread: 1 Archive log gap: 4. Transfer log gap: 4 >>> Transferring Log file(s) from DBISEHA on dbvisitvippri to dbi-oda-x8: thread 1 sequence 47 (thread_1_seq_47.359.1129038579)... done thread 1 sequence 48 (thread_1_seq_48.360.1129038581)... done thread 1 sequence 49 (thread_1_seq_49.361.1129038583)... done thread 1 sequence 50 (thread_1_seq_50.365.1129038891)... done ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 14:05:43 2023 =============================================================
Apply them on the standby side:
oracle@dbi-oda-x8:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 68994) dbvctl started on dbi-oda-x8: Fri Feb 17 14:06:18 2023 ============================================================= >>> Applying Log file(s) from dbvisitvippri to DBISEHA on dbi-oda-x8: thread 1 sequence 47 (1_47_1127209532.arc)... done thread 1 sequence 48 (1_48_1127209532.arc)... done thread 1 sequence 49 (1_49_1127209532.arc)... done thread 1 sequence 50 (1_50_1127209532.arc)... done Last applied log(s): thread 1 sequence 50 Next SCN required for recovery 4076181 generated at 2023-02-17:13:54:50 +01:00. Next required log thread 1 sequence 51 ============================================================= dbvctl ended on dbi-oda-x8: Fri Feb 17 14:06:24 2023 =============================================================
And confirm both primary and standby are in sync:
oracle@dbioda02:/home/oracle/ [DBISEHA] /u01/app/dbvisit/standbymp/oracle/dbvctl -d DBISEHA -i ============================================================= Dbvisit Standby Database Technology (11.2.1_0_g8a57214c) (pid 59910) dbvctl started on dbvisitvippri: Fri Feb 17 14:07:06 2023 ============================================================= Dbvisit Standby log gap report for DBISEHA at 202302171407: ------------------------------------------------------------- Description | SCN | Timestamp ------------------------------------------------------------- Source 4077849 2023-02-17:14:07:06 +01:00 Destination 4076181 2023-02-17:13:54:50 +01:00 Standby database time lag (DAYS-HH:MI:SS): +00:12:16 Report for Thread 1 ------------------- SOURCE Current Sequence 51 Last Archived Sequence 50 Last Transferred Sequence 50 Last Transferred Timestamp 2023-02-17 14:05:43 DESTINATION Next Required Recovery Sequence 51 Transfer Log Gap 0 Apply Log Gap 0 ============================================================= dbvctl ended on dbvisitvippri: Fri Feb 17 14:07:09 2023 =============================================================
Summary
I could successfully test Dbvisit StandbyMP configured with Oracle SEHA database, running switchover, relocate and automatic failover. After a relocate of the Oracle SEHA instance and a relocate of the VIP used with Dbvisit, Dbvisit configuration could work properly, included switchover. Crashing the node running the Oracle SEHA instance, we could see that the oracle database instance has been automatically relocated to the other node as well as the VIP used for Dbvisit configuration. The system was immediately ready to move forward and sync with the other disaster recover cluster node.