Skip to content

Insight and analysis of technology and business strategy

How to Test Data Guard Fast-Start Failover by Shutting Down Standby Server

This post will demonstrate the procedure for testing Oracle Data Guard Fast-Start Failover by shutting down the server where the standby database is running from. The environment is a single instance database without any grid infrastructure components.

Review primary host and start observer:

[oracle@ol7-121-dg1 sql]$ sqlplus / as sysdba
 
 SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:42:54 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:(SYS@cdb1):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
 
 [oracle@ol7-121-dg1 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
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  cdb1 - Primary database
  cdb1_stby - Physical standby database
 
 Fast-Start Failover: DISABLED
 
 Configuration Status:
 SUCCESS (status updated 13 seconds ago)
 
 DGMGRL> enable fast_start failover
 Enabled.
 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 12 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> show database cdb1
 
 Database - cdb1
 
  Role: PRIMARY
  Intended State: TRANSPORT-ON
  Instance(s):
  cdb1
 
  Database Error(s):
  ORA-16820: fast-start failover observer is no longer observing this database
 
 Database Status:
 ERROR
 
 DGMGRL> show database cdb1_stby
 
 Database - cdb1_stby
 
  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
  Real Time Query: ON
  Instance(s):
  cdb1
 
  Database Error(s):
  ORA-16820: fast-start failover observer is no longer observing this database
 
 Database Status:
 ERROR
 
 DGMGRL> start observer
 [P001 01/17 20:46:01.38] Authentication failed. DGM-16979: Unable to log on to the primary or standby database as SYSDBA Failed.
 DGMGRL> connect sys@cdb1
 Password:
 Connected as SYSDBA.
 DGMGRL> start observer
 Observer started
 

Restart standby host, listener, and database:

resetlogs@ghost 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`
 
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $ vagrant up
 Bringing machine 'default' up with 'virtualbox' provider...
 
 ====================================================================
 resetlogs@ghost 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`.
 
 ====================================================================
 resetlogs@ghost MINGW64 /g/oraclebase/vagrant/dataguard/ol7_121/node2 (master)
 $ vagrant ssh
 Last login: Fri Jan 17 20:11:35 2020 from 10.0.2.2
 [vagrant@ol7-121-dg2 ~]$ sudo su - oracle
 Last login: Fri Jan 17 20:11:44 UTC 2020 on pts/0
 [oracle@ol7-121-dg2 ~]$ . oraenv <<< cdb1
 ORACLE_SID = [cdb1] ? The Oracle base remains unchanged with value /u01/app/oracle
 [oracle@ol7-121-dg2 ~]$ lsnrctl start
 
 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-JAN-2020 20:53:20
 
 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 17-JAN-2020 20:53:22
 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
 [oracle@ol7-121-dg2 ~]$ cd /sf_working/sql
 [oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba
 
 SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 20:53:38 2020
 
 Copyright (c) 1982, 2014, Oracle. All rights reserved.
 
 Connected to an idle instance.
 
 SYS@cdb1> 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.
 SYS@cdb1> @stby.sql
 
 Session altered.
 
 *** v$database ***
 
 DB OPEN DATABASE REMOTE SWITCHOVER DATAGUARD PRIMARY_DB
 UNIQUE_NAME MODE ROLE ARCHIVE STATUS BROKER UNIQUE_NAME
 ----------- ---------------------- ------------------ ---------- --------------- ---------- ---------------
 cdb1_stby MOUNTED PHYSICAL STANDBY ENABLED NOT ALLOWED ENABLED cdb1
 
 *** gv$archive_dest ***
 
  MOUNT
  THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS ID
 -------- -------- ------------------------- ------------ ---------------- -------- ---------- -----
  1 1 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE ARCH 0
  1 32 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE RFS 0
 
 *** gv$archive_dest_status ***
 
  DATABASE RECOVERY
  INST_ID DEST_ID STATUS MODE MODE GAP_STATUS ERROR
 -------- -------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
  1 1 VALID MOUNTED-STANDBY IDLE NONE
  1 32 VALID UNKNOWN IDLE NONE
 
 *** v$thread ***
 
  THREAD# CURRENT LOG SEQUENCE STATUS
 -------- -------------------- ------------
  1 26 OPEN
 
 *** gv$archived_log ***
 
  DEST_ID THREAD# APPLIED MAX_SEQ MAX_TIME DELTA_SEQ DETA_MIN
 -------- -------- --------- -------- -------------------- --------- --------
  1 1 NO 25 17-JAN-2020 20:53:53 2 41.68333
  1 1 YES 23 17-JAN-2020 20:12:12
 
 *** v$archive_gap ***
 
 no rows selected
 
 *** GAP can also be verified using RMAN from STANDBY ***
 
 RMAN1
 ------------------------------------------------------------
 list archivelog from sequence 24 thread 1;
 
 *** v$dataguard_stats ***
 
 NAME VALUE UNIT
 ------------------------- ------------------ ------------------------------
 transport lag +00 00:00:00 day(2) to second(0) interval
 apply lag day(2) to second(0) interval
 
 *** gv$managed_standby ***
 
 no rows selected
 
 SYS@cdb1> 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
 [oracle@ol7-121-dg2 sql]$
 

Screen output from observer:

DGMGRL> start observer
 Observer started
 [W000 01/17 20:48:58.27] The primary database has requested a transition to the UNSYNC/LAGGING state.
 [W000 01/17 20:48:58.28] Permission granted to the primary database to transition to UNSYNC/LAGGING state.
 [W000 01/17 20:50:01.29] The primary database has been in UNSYNC/LAGGING state for 63 seconds.
 [W000 01/17 20:51:04.31] The primary database has been in UNSYNC/LAGGING state for 126 seconds.
 [W000 01/17 20:52:07.33] The primary database has been in UNSYNC/LAGGING state for 189 seconds.
 [W000 01/17 20:53:10.36] The primary database has been in UNSYNC/LAGGING state for 252 seconds.
 [W000 01/17 20:54:13.39] The primary database has been in UNSYNC/LAGGING state for 315 seconds.
 [W000 01/17 20:54:16.39] The primary database returned to SYNC/NOT LAGGING state.
 

Validate Data Guard configuration:

[oracle@ol7-121-dg2 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
 
 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 10 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> exit
 [oracle@ol7-121-dg2 sql]$
 

Open database read only:

This is required because the database is not registered to a cluster resource.
[oracle@ol7-121-dg2 sql]$ sqlplus / as sysdba
 
 SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 17 21:33:07 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:(SYS@cdb1):PHYSICAL STANDBY> alter database open read only;
 
 Database altered.
 
 OL7-121-DG2:(SYS@cdb1):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
 
 [oracle@ol7-121-dg2 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 database cdb1_stby
 
 Database - cdb1_stby
 
  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: 1.00 KByte/s
  Real Time Query: ON
  Instance(s):
  cdb1
 
 Database Status:
 SUCCESS
 
 DGMGRL> exit
 [oracle@ol7-121-dg2 sql]$
 
It's a good idea and best practice to shut down a standby host to validate that the environment can restart without any issues.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner