One of the new features introduced in Oracle 19c is to run the Data Guard Broker Observer in OBSERVE ONLY MODE.
Without impacting the current Data Guard configuration, we can test how various fast start failover scenarios would work in our environment before actually enabling the change to the Observer configuration.
The Data Guard Broker adds messages to the observer log and broker log indicating that fast-start failover threshold has been crossed and would a FSFO have been initiated in this particular case .
Let us have a look at a worked example of this feature in action.
################################## SECURE EXTERNAL PASSWORD STORE (SEPS) CREDENTIAL FOR DATA GUARD OBSERVER###########################
Note this has to be performed on both primaty as well as standby database server and performed for all databases in the Data Guard configuration
[oracle@rac02 admin]$ vi sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/app/oracle/dgb_wallet)))
SQLNET.WALLET_OVERRIDE=true
[oracle@rac02 admin]$ mkdir -p /u01/app/oracle/dgb_wallet
[oracle@rac02 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -create
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
[oracle@rac02 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -createCredential oradb1_sb SYS
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[oracle@rac02 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -createCredential oradb1 SYS
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[oracle@rac02 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
2: oradb1 SYS
1: oradb1_sb SYS
[oracle@rac02 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -viewEntry oracle.security.client.connect_string1
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
oracle.security.client.connect_string1 = oradb1_sb
[oracle@rac02 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -viewEntry oracle.security.client.connect_string2
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
oracle.security.client.connect_string2 = oradb1
[oracle@rac01 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -viewEntry oracle.security.client.username1
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
oracle.security.client.username1 = SYS
[oracle@rac01 admin]$ mkstore -wrl /u01/app/oracle/dgb_wallet -viewEntry oracle.security.client.password1
Oracle Secret Store Tool Release 19.0.0.0.0 – Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
oracle.security.client.password1 = Dreamliner787##
####################################CONFIGURE OBSERVER IN OBSERVE ONLY MODE ###################################
DGMGRL> connect /@oradb1
Connected to “oradb1”
Connected as SYSDBA.
DGMGRL> edit database oradb1 set property FastStartFailoverTarget=’oradb1_sb’;
Property “faststartfailovertarget” updated
DGMGRL> edit database oradb1_sb set property FastStartFailoverTarget=’oradb1′;
Property “faststartfailovertarget” updated
DGMGRL> edit database oradb1 set property LogXptMode=’SYNC’;
Property “logxptmode” updated
DGMGRL> edit database oradb1_sb set property LogXptMode=’SYNC’;
Property “logxptmode” updated
DGMGRL> edit configuration set protection mode maxavailability;
Succeeded.
##################################### START MASTER OBSERVER ON STANDBY DATABASE SERVER #####################################
[oracle@rac02 admin]$ dgmgrl /@oradb1_sb
DGMGRL for Linux: Release 19.0.0.0.0 – Production on Sun Jun 14 09:44:10 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type “help” for information.
Connected to “oradb1_sb”
Connected as SYSDBA.
DGMGRL> start observer in background file is /home/oracle/master_observer.dat logfile is /home/oracle/master_observer.log connect identifier is oradb1;
Connected to “oradb1”
Submitted command “START OBSERVER” using connect identifier “oradb1”
DGMGRL> connect /@oradb1
Connected to “oradb1”
Connected as SYSDBA.
DGMGRL> ENABLE FAST_START FAILOVER OBSERVE ONLY;
Enabled in Observe-Only Mode.
DGMGRL> show fast_start failover;
Fast-Start Failover: Enabled in Observe-Only Mode
Protection Mode: MaxAvailability
Lag Limit: 0 seconds
Threshold: 30 seconds
Active Target: oradb1_sb
Potential Targets: “oradb1_sb”
oradb1_sb valid
Observer: rac02.localdomain1
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Write Errors YES
Oracle Error Conditions:
(none)
DGMGRL> start observer in background file is /home/oracle/backup_observer_1.dat logfile is /home/oracle/backup_observer_1.log connect identifier is oradb1;
Connected to “oradb1”
Submitted command “START OBSERVER” using connect identifier “oradb1”
DGMGRL> show configuration
Configuration – oradb1_dg
Protection Mode: MaxAvailability
Members:
oradb1 – Primary database
oradb1_sb – (*) Physical standby database
Fast-Start Failover: Enabled in Observe-Only Mode
Configuration Status:
SUCCESS (status updated 29 seconds ago)
DGMGRL> show fast_start failover
Fast-Start Failover: Enabled in Observe-Only Mode
Protection Mode: MaxAvailability
Lag Limit: 0 seconds
Threshold: 30 seconds
Active Target: oradb1_sb
Potential Targets: “oradb1_sb”
oradb1_sb valid
Observers: (*) rac02.localdomain1
rac01.localdomain1
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Observer Reconnect: (none)
Observer Override: FALSE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Write Errors YES
Oracle Error Conditions:
(none)
DGMGRL> show observer
Configuration – oradb1_dg
Primary: oradb1
Active Target: oradb1_sb
Observer “rac02.localdomain1” – Master
Host Name: rac02.localdomain
Last Ping to Primary: 1 second ago
Last Ping to Target: 1 second ago
Observer “rac01.localdomain1” – Backup
Host Name: rac01.localdomain
Last Ping to Primary: (unknown)
Last Ping to Target: (unknown)
######################################## STOP LISTENER ON PRIMARY #########################################################
######################################## MASTER OBSERVER ON RAC02 CANNOT CONTACT PRIMARY ON RAC01 ###################################
[grid@rac01 admin]$ srvctl stop listener
DGMGRL> show configuration
Configuration – oradb1_dg
Protection Mode: MaxAvailability
Members:
oradb1 – Primary database
Error: ORA-16820: fast-start failover observer is no longer observing this database
oradb1_sb – (*) Physical standby database
Fast-Start Failover: Enabled in Observe-Only Mode
Configuration Status:
ERROR (status updated 56 seconds ago)
######################################## NOTE LAST PING TO PRIMARY VALUE IS INCREASING #########################################################
DGMGRL> show observer
Configuration – oradb1_dg
Primary: oradb1
Active Target: oradb1_sb
Configuration – oradb1_dg
Primary: oradb1
Active Target: oradb1_sb
Observer “rac02.localdomain1” – Master
Host Name: rac02.localdomain
Last Ping to Primary: 398 seconds ago
Last Ping to Target: 3 seconds ago
Observer “rac01.localdomain1” – Backup
Host Name: rac01.localdomain
Last Ping to Primary: (unknown)
Last Ping to Target: (unknown)
DGMGRL> /
Configuration – oradb1_dg
Primary: oradb1
Active Target: oradb1_sb
Observer “rac02.localdomain1” – Master
Host Name: rac02.localdomain
Last Ping to Primary: 404 seconds ago
Last Ping to Target: 3 seconds ago
Observer “rac01.localdomain1” – Backup
Host Name: rac01.localdomain
Last Ping to Primary: (unknown)
Last Ping to Target: (unknown)
######################################## PRIMARY ###############################
SQL> select FS_FAILOVER_OBSERVER_HOST, FS_FAILOVER_OBSERVER_PRESENT from v$database;
FS_FAILOVER_OBSERVER_HOST FS_FAIL
—————————— ——-
rac02.localdomain NO
######################################## STANDBY ########################################
SQL> col FS_FAILOVER_OBSERVER_HOST format a30
SQL> select FS_FAILOVER_OBSERVER_HOST, FS_FAILOVER_OBSERVER_PRESENT from v$database;
FS_FAILOVER_OBSERVER_HOST FS_FAIL
—————————— ——-
rac02.localdomain YES
######################################## MASTER OBSERVER LOG ########################################
Unable to connect to database using oradb1
[W000 2020-06-14T13:18:37.539+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:18:37.539+08:00] Fast-Start Failover threshold has not exceeded. Retry for the next 30 seconds
[W000 2020-06-14T13:18:38.540+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:18:38.545+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:18:39.545+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:18:39.548+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:18:40.549+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:18:40.554+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:18:41.555+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:19:04.684+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:19:04.684+08:00] Fast-Start Failover threshold has not exceeded. Retry for the next 3 seconds
[W000 2020-06-14T13:19:05.685+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:19:05.688+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:19:05.688+08:00] Fast-Start Failover threshold has not exceeded. Retry for the next 2 seconds
[W000 2020-06-14T13:19:06.688+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:19:06.692+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:19:06.692+08:00] Fast-Start Failover threshold has not exceeded. Retry for the next 1 second
[W000 2020-06-14T13:19:07.693+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:19:07.700+08:00] Primary database cannot be reached.
[W000 2020-06-14T13:19:07.700+08:00] Fast-Start Failover threshold has expired.
[W000 2020-06-14T13:19:07.700+08:00] Try to connect to the standby.
[W000 2020-06-14T13:19:07.700+08:00] Making a last connection attempt to primary database before proceeding with Fast-Start Failover.
[W000 2020-06-14T13:19:07.700+08:00] Check if the standby is ready for failover.
ORA-12541: TNS:no listener
Unable to connect to database using oradb1
[W000 2020-06-14T13:19:07.727+08:00] A fast-start failover would have been initiated…
[W000 2020-06-14T13:19:07.727+08:00] Unable to failover since this observer is in observe-only mode
[W000 2020-06-14T13:19:07.727+08:00] Fast-Start Failover is not possible because observe-only mode.
[W000 2020-06-14T13:19:07.727+08:00] Try to connect to the primary.
ORA-12541: TNS:no listener
