This post will demonstrate the procedure to test Oracle Data Guard Fast-Start Failover by shutting down the server where the primary database is running from. The environment is a single instance database without any grid Infrastructure components. The same process should work for RAC environment as my colleague has used the same process to test for RAC running on ODA.
Note: Primary Database: cdb1_stby is because the failover was previously performed.
This also demonstrates why it may not be a good idea to suffix stby for standby database.
Review Data Guard using sqlplus:
OL7-121-DG2:([email protected]):PRIMARY> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string cdb1 db_unique_name string CDB1_STBY pdb_file_name_convert string OL7-121-DG2:([email protected]):PRIMARY> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string cdb1 OL7-121-DG2:([email protected]):PRIMARY> ******************************************************************************** OL7-121-DG1:([email protected]):PHYSICAL STANDBY> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string cdb1 db_unique_name string cdb1 pdb_file_name_convert string OL7-121-DG1:([email protected]):PHYSICAL STANDBY> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string cdb1_stby OL7-121-DG1:([email protected]):PHYSICAL STANDBY>
Review Data Guard configuration:
DGMGRL> show configuration verbose Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1_stby - Primary database cdb1 - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'USER' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' Fast-Start Failover: DISABLED Configuration Status: SUCCESS ******************************************************************************** DGMGRL> show database verbose cdb1_stby Database - cdb1_stby Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): cdb1 Properties: DGConnectIdentifier = 'cdb1_stby' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = 'cdb1' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_stby_DGMGRL)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS ******************************************************************************** DGMGRL> show database verbose cdb1 Database - cdb1 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: 2.00 KByte/s Active Apply Rate: 0 Byte/s Maximum Apply Rate: 0 Byte/s Real Time Query: ON Instance(s): cdb1 Properties: DGConnectIdentifier = 'cdb1' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '0' TransportLagThreshold = '0' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DbFileNameConvert = '' LogFileNameConvert = '' FastStartFailoverTarget = 'cdb1_stby' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=cdb1_DGMGRL)(INSTANCE_NAME=cdb1)(SERVER=DEDICATED)))' StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Database Status: SUCCESS DGMGRL>
Validate Data Guard configuration:
DGMGRL> validate database verbose cdb1_stby Database Role: Primary database Ready for Switchover: Yes Capacity Information: Database Instances Threads cdb1_stby 1 1 Temporary Tablespace File Information: cdb1_stby TEMP Files: 1 Flashback Database Status: cdb1_stby: On Data file Online Move in Progress: cdb1_stby: No Transport-Related Information: Transport On: Yes Log Files Cleared: cdb1_stby Standby Redo Log Files: Cleared Automatic Diagnostic Repository Errors: Error cdb1_stby No logging operation NO Control file corruptions NO System data file missing NO System data file corrupted NO System data file offline NO User data file missing NO User data file corrupted NO User data file offline NO Block Corruptions found NO ******************************************************************************** DGMGRL> validate database verbose cdb1 Database Role: Physical standby database Primary Database: cdb1_stby Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Capacity Information: Database Instances Threads cdb1_stby 1 1 cdb1 1 1 Temporary Tablespace File Information: cdb1_stby TEMP Files: 3 cdb1 TEMP Files: 3 Flashback Database Status: cdb1_stby: On cdb1: On Data file Online Move in Progress: cdb1_stby: No cdb1: No Standby Apply-Related Information: Apply State: Running Apply Lag: 0 seconds (computed 1 second ago) Apply Delay: 0 minutes Transport-Related Information: Transport On: Yes Gap Status: No Gap Transport Lag: 0 seconds (computed 1 second ago) Transport Status: Success Log Files Cleared: cdb1_stby Standby Redo Log Files: Cleared cdb1 Online Redo Log Files: Cleared cdb1 Standby Redo Log Files: Available Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (cdb1_stby) (cdb1) 1 3 4 Sufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (cdb1) (cdb1_stby) 1 3 4 Sufficient SRLs Current Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (cdb1_stby) (cdb1) 1 50 MBytes 50 MBytes Future Configuration Log File Sizes: Thread # Smallest Online Redo Smallest Standby Redo Log File Size Log File Size (cdb1) (cdb1_stby) 1 50 MBytes 50 MBytes Apply-Related Property Settings: Property cdb1_stby Value cdb1 Value DelayMins 0 0 ApplyParallel AUTO AUTO Transport-Related Property Settings: Property cdb1_stby Value cdb1 Value LogXptMode ASYNC ASYNC RedoRoutes Dependency DelayMins 0 0 Binding optional optional MaxFailure 0 0 MaxConnections 1 1 ReopenSecs 300 300 NetTimeout 30 30 RedoCompression DISABLE DISABLE LogShipping ON ON Automatic Diagnostic Repository Errors: Error cdb1_stby cdb1 No logging operation NO NO Control file corruptions NO NO SRL Group Unavailable NO NO System data file missing NO NO System data file corrupted NO NO System data file offline NO NO User data file missing NO NO User data file corrupted NO NO User data file offline NO NO Block Corruptions found NO NO DGMGRL>
Validate Data Guard connectivity from all hosts:
[[email protected] sql]$ dgmgrl DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect [email protected] Password: Connected as SYSDBA. DGMGRL> connect [email protected]_stby Password: Connected as SYSDBA. DGMGRL> exit [[email protected] sql]$ ******************************************************************************** [[email protected] sql]$ dgmgrl DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect [email protected] Password: Connected as SYSDBA. DGMGRL> connect [email protected]_stby Password: Connected as SYSDBA. DGMGRL> exit [[email protected] sql]$
Start Data Guard observer from standby:
Note: This is not a good practice for real-world scenarios. It is for testing purposes only.
[email protected] sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:21:28 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG1:([email protected]):PHYSICAL STANDBY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [[email protected] sql]$ dgmgrl DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect [email protected]_stby Password: Connected as SYSDBA. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1_stby - Primary database Warning: ORA-16819: fast-start failover observer not started cdb1 - (*) Physical standby database Warning: ORA-16819: fast-start failover observer not started Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 16 seconds ago) DGMGRL> start observer Observer started DGMGRL>
Shutdown primary host:
[[email protected] sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:26:51 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options OL7-121-DG2:([email protected]):PRIMARY> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [[email protected] sql]$ logout [[email protected] ~]$ logout Connection to 127.0.0.1 closed. [email protected] MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant halt ==> default: Attempting graceful shutdown of VM... [email protected] MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $
Failover succeeded:
DGMGRL> start observer
Observer started
21:27:46.27 Wednesday, January 15, 2020
Initiating Fast-Start Failover to database "cdb1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdb1"
21:27:51.99 Wednesday, January 15, 2020
Review Data Guard configuration:
[[email protected] ~]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database Warning: ORA-16829: fast-start failover configuration is lagging cdb1_stby - (*) Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 41 seconds ago) DGMGRL>
Start primary host:
[email protected] MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant status Current machine states: default poweroff (virtualbox) The VM is powered off. To restart the VM, simply run `vagrant up` [email protected] MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant up [email protected] MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $ vagrant status Current machine states: default running (virtualbox) The VM is running. To stop this VM, you can run `vagrant halt` to shut it down forcefully, or you can run `vagrant suspend` to simply suspend the virtual machine. In either case, to restart it again, simply run `vagrant up`. [email protected] MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master) $
Start listener:
[[email protected] ~]$ lsnrctl start LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-JAN-2020 21:33:11 Copyright (c) 1991, 2014, Oracle. All rights reserved. Starting /u01/app/oracle/product/12.1.0.2/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0 - Production System parameter file is /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 15-JAN-2020 21:33:12 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/ol7-121-dg2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg2.localdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "cdb1_stby_DGMGRL" has 1 instance(s). Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [[email protected] ~]$
Startup mount database:
[[email protected] sql]$ ps -ef|grep pmon oracle 17762 17567 0 21:34 pts/0 00:00:00 grep --color=auto pmon [[email protected] sql]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 15 21:34:09 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. [email protected]> startup mount; ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2924928 bytes Variable Size 520097408 bytes Database Buffers 1073741824 bytes Redo Buffers 13848576 bytes Database mounted. [email protected]> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [[email protected] sql]$
Review Data Guard configuration:
[[email protected] sql]$ dgmgrl / DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production Copyright (c) 2000, 2013, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> show configuration ORA-16795: the standby database needs to be re-created Configuration details cannot be determined by DGMGRL DGMGRL>
Review Observer:
DGMGRL> start observer
Observer started
21:27:46.27 Wednesday, January 15, 2020
Initiating Fast-Start Failover to database "cdb1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "cdb1"
21:27:51.99 Wednesday, January 15, 2020
21:34:56.44 Wednesday, January 15, 2020
Initiating reinstatement for database "cdb1_stby"...
Reinstating database "cdb1_stby", please wait...
Reinstatement of database "cdb1_stby" succeeded
21:35:15.40 Wednesday, January 15, 2020
Review and Validate Data Guard configuration:
DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - (*) Physical standby database Warning: ORA-16829: fast-start failover configuration is lagging Fast-Start Failover: ENABLED Configuration Status: WARNING (status updated 54 seconds ago) DGMGRL> show configuration Configuration - my_dg_config Protection Mode: MaxPerformance Members: cdb1 - Primary database cdb1_stby - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 24 seconds ago) DGMGRL> validate database cdb1 Database Role: Primary database Ready for Switchover: Yes DGMGRL> validate database cdb1_stby Database Role: Physical standby database Primary Database: cdb1 Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) DGMGRL>
It’s a good idea to validate Data Guard configuration even when failover testing is not required to be prepared for any sort of disaster.
If there is a need to test Data Guard failover, then hopefully the demo provided above will be of use.
2 Comments. Leave new
Hi Michael,
Thank for you awesome blog. It is very helpful. I am looking for script that can monitor failover and notify via email if failover happened due to FSFO.
Could you share if you have it.
Thank you!!
Hi Arjun,
I do not have a script. If you have script that monitor alert log, then I believe the same can be used to monitor the observer log.
Thanks Michael.