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.
Oracle Database Consulting Services
Ready to optimize your Oracle Database for the future?
.svg){kind=small}
.svg){kind=small}