A! Help: Failover and Reinstate With Multiple Physical Standbys

The data guard configuration consists of three databases.

 DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest3 - Primary database
    dgtest  - Physical standby database
    dgtest2 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 67 seconds ago)

At the moment dgtest3 is the primary. One of the standby (dgtest2) databases is open in mount mode.

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
dgtest2                        MOUNTED

The other standby (dgtest) is an active data guard and is open in read only mode with log apply.

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
dgtest                         READ ONLY WITH APPLY

To simulate primary DB failure it is shutdown with abort.

SQL> shutdown abort;
ORACLE instance shut down.

Checking the data guard configuration from dgtest2 shows an error state.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest3 - Primary database
    Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    dgtest  - Physical standby database
    dgtest2 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 0 seconds ago)

One of the existing standbys could be choosen for failover. In this case dgtest2( instance in mount mode) was chosen. Validating the dgtest2 show that it is ready for failover.

DGMGRL> validate database dgtest2

  Database Role:     Physical standby database
  Primary Database:  dgtest3
    Warning: primary database was not reachable

  Ready for Switchover:  No
  Ready for Failover:    Yes (Primary Not Running)

  Flashback Database Status:
    dgtest3:  Unknown
    dgtest2:  On

  Managed by Clusterware:
    dgtest3:  Unknown
    dgtest2:  YES
    Validating static connect identifier for the primary database dgtest3...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip-132)(PORT=1521))(CONNECT                                                                                                                          _DATA=(SERVICE_NAME=dgtest3_DGMGRL)(INSTANCE_NAME=dgtest3)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Temporary Tablespace File Information:
    dgtest3 TEMP Files:  Unknown
    dgtest2 TEMP Files:  3

  Data file Online Move in Progress:
    dgtest3:  Unknown
    dgtest2:  No

  Transport-Related Information:
    Transport On:  No
    Gap Status:    Unknown
    Transport Lag:  0 seconds (computed 24 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    dgtest3 Standby Redo Log Files:  Unknown
    dgtest2 Online Redo Log Files:   Unknown
    dgtest2 Standby Redo Log Files:  Unknown

Initiate failover to dgtest2

DGMGRL> failover to dgtest2;
Performing failover NOW, please wait...
Failover succeeded, new primary is "dgtest2"

During the failover process the active data guard instance continue to function. It will cancel the current redo apply service and start a new one with the incarnation that results from the failover. At the same time any references to the old primary will be removed (such as fal_server).

2021-09-20T09:53:29.718199+00:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2021-09-20T09:53:29.720039+00:00
PR00 (PID:5312): MRP0: Background Media Recovery cancelled with status 16037
2021-09-20T09:53:29.720747+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5312.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:5312): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4236455
Stopping change tracking
2021-09-20T09:53:29.943216+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5312.trc:
ORA-16037: user requested cancel of managed recovery operation
2021-09-20T09:53:30.067266+00:00
Background Media Recovery process shutdown (dgtest)
2021-09-20T09:53:30.720982+00:00
Managed Standby Recovery Canceled (dgtest)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
2021-09-20T09:53:32.641452+00:00
ALTER SYSTEM SET fal_server='dgtest2tns' SCOPE=BOTH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2021-09-20T09:53:32.658894+00:00
Attempt to start background Managed Standby Recovery process (dgtest)
Starting background process MRP0
2021-09-20T09:53:32.673122+00:00
MRP0 started with pid=63, OS id=5816
2021-09-20T09:53:32.674424+00:00
Background Managed Standby Recovery process started (dgtest)
2021-09-20T09:53:36.270863+00:00
 rfs (PID:5820): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:11152)
Deleted Oracle managed file +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_0_seq_0.371.1083750817
2021-09-20T09:53:37.697852+00:00
 Started logmerger process
2021-09-20T09:53:37.712394+00:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:5826): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2021-09-20T09:53:37.888440+00:00
 rfs (PID:5824): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is SYNC (PID:10754)
 rfs (PID:5824): New archival redo branch: 1083750811 current: 1067878072
 rfs (PID:5824): Primary database is in MAXIMUM AVAILABILITY mode
 rfs (PID:5824): Changing standby controlfile to RESYNCHRONIZATION level
 rfs (PID:5824): Standby controlfile consistent with primary
2021-09-20T09:53:37.951928+00:00
 rfs (PID:5824): Selected LNO:6 for T-1.S-3 dbid 4024401720 branch 1083750811
2021-09-20T09:53:37.974747+00:00
Parallel Media Recovery started with 8 slaves
2021-09-20T09:53:38.018389+00:00
Stopping change tracking
PR00 (PID:5826): Media Recovery Waiting for T-1.S-382
2021-09-20T09:53:38.440514+00:00
 rfs (PID:5855): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:11166)
2021-09-20T09:53:38.505442+00:00
 rfs (PID:5855): Selected LNO:7 for T-1.S-2 dbid 4024401720 branch 1083750811
 rfs (PID:5855): A new recovery destination branch has been registered
 rfs (PID:5855): New Archival REDO Branch(resetlogs_id): 1083750811  Prior: 1067878072
 rfs (PID:5855): Archival Activation ID: 0xf0d1268f Current: 0xf0d199cf
 rfs (PID:5855): Effect of primary database OPEN RESETLOGS
 rfs (PID:5855): Managed Standby Recovery process is active
2021-09-20T09:53:38.536066+00:00
Incarnation entry added for Branch(resetlogs_id): 1083750811 (dgtest)
2021-09-20T09:53:38.548638+00:00
Setting recovery target incarnation to 2
2021-09-20T09:53:38.678029+00:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2021-09-20T09:53:39.126620+00:00
PR00 (PID:5826): MRP0: Incarnation has changed! Retry recovery...
2021-09-20T09:53:39.127329+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5826.trc:
ORA-19906: recovery target incarnation changed during recovery
PR00 (PID:5826): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Stopping change tracking
2021-09-20T09:53:39.233028+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest/dgtest/trace/dgtest_pr00_5826.trc:
ORA-19906: recovery target incarnation changed during recovery
2021-09-20T09:53:39.432821+00:00
 rfs (PID:5858): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:11172)
2021-09-20T09:53:39.480429+00:00
 rfs (PID:5858): Opened log for T-1.S-382 dbid 4024401720 branch 1067878072
2021-09-20T09:53:39.509873+00:00
 rfs (PID:5858): Archived Log entry 746 added for B-1067878072.T-1.S-382 ID 0xf0d199cf LAD:2
2021-09-20T09:53:39.573601+00:00
 Started logmerger process
2021-09-20T09:53:39.614023+00:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:5861): Managed Standby Recovery starting Real Time Apply
2021-09-20T09:53:39.622286+00:00
ARC8 (PID:5169): Archived Log entry 747 added for T-1.S-2 ID 0xf0d1268f LAD:1
2021-09-20T09:53:39.678880+00:00
max_pdb is 3
2021-09-20T09:53:39.878314+00:00
Parallel Media Recovery started with 8 slaves
2021-09-20T09:53:39.894206+00:00
Media Recovery start incarnation depth : 1, target inc# : 2, irscn : 4236456
Stopping change tracking
2021-09-20T09:53:39.917344+00:00
 rfs (PID:5863): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:11158)
2021-09-20T09:53:39.974576+00:00
 rfs (PID:5863): Opened log for T-1.S-1 dbid 4024401720 branch 1083750811
2021-09-20T09:53:40.001952+00:00
 rfs (PID:5863): Archived Log entry 748 added for B-1083750811.T-1.S-1 ID 0xf0d1268f LAD:2
2021-09-20T09:53:40.011908+00:00
PR00 (PID:5861): Media Recovery Log +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_1_seq_382.371.1083750819
2021-09-20T09:53:40.134398+00:00
PR00 (PID:5861): Resetting standby activation ID 4040268239 (0xf0d199cf)
2021-09-20T09:53:40.138397+00:00
Media Recovery End-Of-Redo indicator encountered
2021-09-20T09:53:40.138471+00:00
Media Recovery Continuing
2021-09-20T09:53:40.401844+00:00
PR00 (PID:5861): Media Recovery Log +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_1_seq_1.454.1083750819
2021-09-20T09:53:40.499037+00:00
PR00 (PID:5861): Media Recovery Log +FRA/DGTEST/ARCHIVELOG/2021_09_20/thread_1_seq_2.372.1083750819
2021-09-20T09:53:40.692602+00:00
PR00 (PID:5861): Media Recovery Waiting for T-1.S-3 (in transit)
2021-09-20T09:53:40.698462+00:00
Recovery of Online Redo Log: Thread 1 Group 6 Seq 3 Reading mem 0
  Mem# 0: +FRA/DGTEST/ONLINELOG/group_6.423.1067949163
2021-09-20T09:53:41.024853+00:00
 rfs (PID:5824): Changing standby controlfile to MAXIMUM AVAILABILITY level
2021-09-20T09:53:41.041788+00:00
 rfs (PID:5824): Selected LNO:7 for T-1.S-4 dbid 4024401720 branch 1083750811
2021-09-20T09:53:41.045351+00:00
ARC9 (PID:5171): Archived Log entry 749 added for T-1.S-3 ID 0xf0d1268f LAD:1
2021-09-20T09:53:41.118868+00:00
PR00 (PID:5861): Media Recovery Waiting for T-1.S-4 (in transit)
2021-09-20T09:53:41.124519+00:00
Recovery of Online Redo Log: Thread 1 Group 7 Seq 4 Reading mem 0
  Mem# 0: +FRA/DGTEST/ONLINELOG/group_7.424.1067949165

The instance failed over to will stop it's current redo apply service and convert to a read/write primary. It is intresting to see some of the lines in the log output "switchover" instead of failover.

2021-09-20T09:53:24.419649+00:00
Beginning failover to database dgtest2.
Starting background process NSV1
2021-09-20T09:53:24.492112+00:00
NSV1 started with pid=16, OS id=12324
2021-09-20T09:53:29.976461+00:00
ALTER DATABASE FAILOVER TO dgtest2
2021-09-20T09:53:29.976612+00:00
RSM0 (PID:11547): The Time Management Interface (TMI) is being enabled for role transition
RSM0 (PID:11547): information.  This will result in messages beingoutput to the alert log
RSM0 (PID:11547): file with the prefix 'TMI: '.  This is being enabled to make the timing of
RSM0 (PID:11547): the various stages of the role transition available for diagnostic purposes.
RSM0 (PID:11547): This output will end when the role transition is complete.
TMI: dbsdrv failover to target BEGIN 2021-09-20 09:53:29.977021
Terminal Recovery requested in process 11547
TMI: adbdrv termRecovery BEGIN 2021-09-20 09:53:29.978960
RSM0 (PID:11547): Terminal Recovery: Stopping real time apply
2021-09-20T09:53:29.980343+00:00
PR00 (PID:11627): MRP0: Background Media Recovery cancelled with status 16037
2021-09-20T09:53:29.980556+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_11627.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:11627): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4236455
Stopping change tracking
2021-09-20T09:53:30.221440+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_11627.trc:
ORA-16037: user requested cancel of managed recovery operation
2021-09-20T09:53:30.319390+00:00
Background Media Recovery process shutdown (dgtest2)
2021-09-20T09:53:30.981370+00:00
RSM0 (PID:11547): Terminal Recovery: Stopped real time apply
2021-09-20T09:53:30.986937+00:00
Attempt to do a Terminal Recovery (dgtest2)
TMI: adbdrv termRecovery END 2021-09-20 09:53:30.986957
2021-09-20T09:53:30.987291+00:00
Media Recovery Start: Managed Standby Recovery (dgtest2)
2021-09-20T09:53:30.992470+00:00
Serial Media Recovery started
RSM0 (PID:11547): Managed Standby Recovery not using Real Time Apply
max_pdb is 3
Stopping change tracking
RSM0 (PID:11547): Begin: SRL archival
RSM0 (PID:11547): End: SRL archival
RSM0 (PID:11547): Terminal Recovery timestamp is '09/20/2021 09:53:31'
RSM0 (PID:11547): Terminal Recovery: applying standby redo logs.
RSM0 (PID:11547): Terminal Recovery: thread 1 seq# 382 redo required
2021-09-20T09:53:31.259258+00:00
RSM0 (PID:11547): Terminal Recovery:
2021-09-20T09:53:31.264259+00:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 382 Reading mem 0
  Mem# 0: +FRA/DGTEST2/ONLINELOG/group_5.423.1067949477
Terminal Recovery finished with No-Data-Loss
2021-09-20T09:53:31.297911+00:00
Incomplete Recovery applied until change 4236456 time 09/20/2021 09:52:41
2021-09-20T09:53:31.312164+00:00
Media Recovery Complete (dgtest2)
Terminal Recovery: successful completion
RSM0 (PID:11547): Forcing ARSCN to IRSCN for TR SCN:0x000000000040a4a8
RSM0 (PID:11547): Attempt to set limbo arscn SCN:0x000000000040a4a8 irscn SCN:0x000000000040a4a8
RSM0 (PID:11547): Resetting standby activation ID 4040268239 (0xf0d199cf)
Stopping change tracking
2021-09-20T09:53:31.464571+00:00
ALTER DATABASE SWITCHOVER TO PRIMARY (dgtest2)
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-09-20 09:53:31.466893
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-09-20 09:53:31.467051
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-09-20 09:53:31.468362
Backup controlfile written to trace file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_rsm0_11547.trc
Standby terminal recovery start SCN: 4236455
RESETLOGS after incomplete recovery UNTIL CHANGE 4236456 time 09/20/2021 09:52:41
RSM0 (PID:11547): ORL pre-clearing operation disabled by switchover
Online log +DATA/DGTEST2/ONLINELOG/group_1.289.1067949469: Thread 1 Group 1 was previously cleared
Online log +FRA/DGTEST2/ONLINELOG/group_1.419.1067949471: Thread 1 Group 1 was previously cleared
Online log +DATA/DGTEST2/ONLINELOG/group_2.290.1067949471: Thread 1 Group 2 was previously cleared
Online log +FRA/DGTEST2/ONLINELOG/group_2.420.1067949473: Thread 1 Group 2 was previously cleared
Online log +DATA/DGTEST2/ONLINELOG/group_3.291.1067949473: Thread 1 Group 3 was previously cleared
Online log +FRA/DGTEST2/ONLINELOG/group_3.421.1067949473: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4236454
2021-09-20T09:53:31.635945+00:00
Setting recovery target incarnation to 2
2021-09-20T09:53:31.673002+00:00
RSM0 (PID:11547): RT: Role transition work is not done
RSM0 (PID:11547): The Time Management Interface (TMI) is being enabled for role transition
RSM0 (PID:11547): information.  This will result in messages beingoutput to the alert log
RSM0 (PID:11547): file with the prefix 'TMI: '.  This is being enabled to make the timing of
RSM0 (PID:11547): the various stages of the role transition available for diagnostic purposes.
RSM0 (PID:11547): This output will end when the role transition is complete.
RSM0 (PID:11547): Redo network throttle feature is disabled at mount time
2021-09-20T09:53:31.710578+00:00
RSM0 (PID:11547): Database role cleared from PHYSICAL STANDBY [kcvs.c:1099]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2021-09-20 09:53:31.712243
TMI: dbsdrv failover to target END 2021-09-20 09:53:31.712367
Failover completed with No-Data-Loss.
Completed: ALTER DATABASE FAILOVER TO dgtest2
2021-09-20T09:53:31.975876+00:00
RSM0 (PID:11547): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18222]
2021-09-20T09:53:31.976280+00:00
ARC9 (PID:11172): Becoming the 'no SRL' ARCH
2021-09-20T09:53:31.983848+00:00
ALTER SYSTEM SET log_archive_dest_2='service="dgtesttns"','SYNC AFFIRM delay=0 optional compression=disable max_fai                                                                                                                          lure=0 reopen=300 db_unique_name="dgtest" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
2021-09-20T09:53:32.015296+00:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
Completed: ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY
ALTER DATABASE OPEN
2021-09-20T09:53:32.030768+00:00
TMI: adbdrv open database BEGIN 2021-09-20 09:53:32.030695
Data Guard Broker initializing...
Ping without log force is disabled:
  instance mounted in exclusive mode.
Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED
2021-09-20T09:53:32.116297+00:00
Crash Recovery excluding pdb 2 which was cleanly closed.
2021-09-20T09:53:32.116442+00:00
Crash Recovery excluding pdb 3 which was cleanly closed.
Endian type of dictionary set to little
2021-09-20T09:53:32.136779+00:00
Assigning activation ID 4040238735 (0xf0d1268f)
LGWR (PID:10754): Primary database is in MAXIMUM AVAILABILITY mode
2021-09-20T09:53:32.140956+00:00
LGWR (PID:10754): LAD:2 is UNSYNCHRONIZED
LGWR (PID:10754): LAD:1 is not serviced by LGWR
2021-09-20T09:53:33.177813+00:00
Thread 1 advanced to log sequence 2 (thread open)
Redo log for group 2, sequence 2 is not located on DAX storage
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: +DATA/DGTEST2/ONLINELOG/group_2.290.1067949471
  Current log# 2 seq# 2 mem# 1: +FRA/DGTEST2/ONLINELOG/group_2.420.1067949473
Successful open of redo thread 1
2021-09-20T09:53:33.246706+00:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
2021-09-20T09:53:33.287479+00:00
ARC1 (PID:11156): Archived Log entry 520 added for T-1.S-1 ID 0xf0d1268f LAD:1
2021-09-20T09:53:33.290677+00:00
TT03 (PID:12462): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2021-09-20T09:53:33.549541+00:00
Undo initialization recovery: Parallel FPTR failed: start:740398 end:740405 diff:7 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 740397 end: 740435 diff: 38 ms (0.0 seconds)
[11547] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 740435 end: 740959 diff: 524 ms (0.5 seconds)
Undo initialization finished serial:0 start:740397 end:740967 diff:570 ms (0.6 seconds)
Dictionary check beginning
Dictionary check complete
2021-09-20T09:53:34.245425+00:00
Database Characterset is AL32UTF8
No Resource Manager plan active
2021-09-20T09:53:35.468394+00:00
joxcsys_required_dirobj_exists: directory object exists with required path /opt/app/oracle/product/19.x.0/dbhome_2/                                                                                                                          javavm/admin/, pid 11547 cid 1
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2021-09-20T09:53:36.133706+00:00
AQPC started with pid=70, OS id=12472
PDB$SEED(2):Pluggable database PDB$SEED opening in read only
2021-09-20T09:53:36.942191+00:00
PDB$SEED(2):Autotune of undo retention is turned on.
2021-09-20T09:53:37.055480+00:00
TT03 (PID:12462): Enabling archival of deferred physical standby SRLs
2021-09-20T09:53:37.152868+00:00
TT03 (PID:12462): Archived Log entry 521 added for T-1.S-382 ID 0xf0d199cf LAD:1
2021-09-20T09:53:37.246644+00:00
PDB$SEED(2):Endian type of dictionary set to little
PDB$SEED(2):Undo initialization finished serial:0 start:744276 end:744276 diff:0 ms (0.0 seconds)
PDB$SEED(2):Pluggable database PDB$SEED dictionary check beginning
2021-09-20T09:53:38.232036+00:00
Thread 1 advanced to log sequence 3 (LGWR switch),  current SCN: 4236634
  Current log# 3 seq# 3 mem# 0: +DATA/DGTEST2/ONLINELOG/group_3.291.1067949473
  Current log# 3 seq# 3 mem# 1: +FRA/DGTEST2/ONLINELOG/group_3.421.1067949473
2021-09-20T09:53:38.316860+00:00
PDB$SEED(2):Pluggable Database PDB$SEED Dictionary check complete
2021-09-20T09:53:38.327245+00:00
ARC5 (PID:11164): Archived Log entry 522 added for T-1.S-2 ID 0xf0d1268f LAD:1
2021-09-20T09:53:38.328930+00:00
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2021-09-20T09:53:38.503847+00:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2021-09-20T09:53:38.642191+00:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2021-09-20T09:53:38.835626+00:00
PDB$SEED(2):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x0, new 0x0 (no suplog)
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2021-09-20T09:53:39.622239+00:00
Starting background process CJQ0
Completed: ALTER DATABASE OPEN

After the failover completes the data guard broker status shows old primary is disabled and need reinstating.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 40 seconds ago)

If the old primary issue is rectified it would be reinstated as a standby (instead of adding as a new standby). When the old primary is started the DG broker will detect failover has happened and will prevent the opening the opening of the DB in read/write mode.

Completed: ALTER DATABASE MOUNT /* db agent *//* {0:0:312} */
ALTER DATABASE OPEN /* db agent *//* {0:0:312} */
Data Guard Broker initializing...
2021-09-20T09:58:19.408297+00:00
Starting Data Guard Broker (DMON)
Starting background process INSV
2021-09-20T09:58:19.425650+00:00
INSV started with pid=49, OS id=5982
2021-09-20T09:58:23.448529+00:00
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
2021-09-20T09:58:23.467893+00:00
Starting background process NSV1
2021-09-20T09:58:23.484569+00:00
NSV1 started with pid=50, OS id=5987
2021-09-20T09:58:27.484306+00:00
Starting background process NSV2
2021-09-20T09:58:27.501947+00:00
NSV2 started with pid=51, OS id=5992
2021-09-20T09:58:30.605237+00:00
Starting background process RSM0
2021-09-20T09:58:30.620725+00:00
RSM0 started with pid=52, OS id=5997
2021-09-20T09:58:31.448386+00:00
Data Guard: broker startup completed
Data Guard determines a failover has occurred - instance will not be opened
ORA-16649 signalled during: ALTER DATABASE OPEN /* db agent *//* {0:0:312} */...

The database will be left at mount mode.

SQL> select db_unique_name,open_mode from v$database;

DB_UNIQUE_NAME                 OPEN_MODE
------------------------------ --------------------
dgtest3                        MOUNTED

From the current primary DG broker run the reinstate command.

DGMGRL> reinstate database dgtest3
Reinstating database "dgtest3", please wait...
Reinstatement of database "dgtest3" succeeded

During the reinstate process the old primary will be flashback to the time where failover occured and converted to a physical standby.

2021-09-20T09:59:51.612704+00:00
FLASHBACK DATABASE TO SCN 4236454
2021-09-20T09:59:51.774069+00:00
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
 Started logmerger process
2021-09-20T09:59:52.058152+00:00
max_pdb is 3
2021-09-20T09:59:52.297907+00:00
Parallel Media Recovery started with 8 slaves
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_373.332.1083750351
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_374.264.1083750365
2021-09-20T09:59:52.812785+00:00
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_375.265.1083750375
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_376.267.1083750649
2021-09-20T09:59:53.157016+00:00
Media Recovery End-Of-Redo indicator encountered
2021-09-20T09:59:53.157085+00:00
Media Recovery Continuing
2021-09-20T09:59:53.202162+00:00
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_377.271.1083750659
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_378.269.1083750671
Flashback Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_20/thread_1_seq_379.268.1083750683
2021-09-20T09:59:53.834774+00:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 380 Reading mem 0
  Mem# 0: +DATA/DGTEST3/ONLINELOG/group_1.299.1069533175
  Mem# 1: +FRA/DGTEST3/ONLINELOG/group_1.460.1069533177
2021-09-20T09:59:53.955443+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 381 Reading mem 0
  Mem# 0: +DATA/DGTEST3/ONLINELOG/group_2.300.1069533177
  Mem# 1: +FRA/DGTEST3/ONLINELOG/group_2.459.1069533177
2021-09-20T09:59:54.017466+00:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 382 Reading mem 0
  Mem# 0: +DATA/DGTEST3/ONLINELOG/group_3.301.1069533179
  Mem# 1: +FRA/DGTEST3/ONLINELOG/group_3.458.1069533179
2021-09-20T09:59:54.086085+00:00
Incomplete Recovery applied until change 4236455 time 09/20/2021 09:52:41
2021-09-20T09:59:54.094097+00:00
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 4236454
alter database convert to physical standby
2021-09-20T09:59:54.263414+00:00
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (dgtest3)
Clearing standby activation ID 4040262352 (0xf0d182d0)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Offline data file 2 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 4 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 6 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
RSM0 (PID:5997): Database role changed from PRIMARY to PHYSICAL STANDBY [kcvs.c:8936]
2021-09-20T09:59:54.325231+00:00
RSM0 (PID:5997): Waiting for all non-current ORLs to be archived
2021-09-20T09:59:54.325324+00:00
RSM0 (PID:5997): All non-current ORLs have been archived
RSM0 (PID:5997): Clearing online redo logfile 1 +DATA/DGTEST3/ONLINELOG/group_1.299.1069533175
RSM0 (PID:5997): Clearing online redo logfile 2 +DATA/DGTEST3/ONLINELOG/group_2.300.1069533177
RSM0 (PID:5997): Clearing online redo logfile 3 +DATA/DGTEST3/ONLINELOG/group_3.301.1069533179
Clearing online log 1 of thread 1 sequence number 380
Clearing online log 2 of thread 1 sequence number 381
Clearing online log 3 of thread 1 sequence number 382
2021-09-20T10:00:03.959609+00:00
RSM0 (PID:5997): Clearing online redo logfile 1 complete
RSM0 (PID:5997): Clearing online redo logfile 2 complete
RSM0 (PID:5997): Clearing online redo logfile 3 complete
RSM0 (PID:5997): RT: Role transition work is not done
RSM0 (PID:5997): Redo network throttle feature is disabled at mount time
Physical Standby Database mounted.
2021-09-20T10:00:03.970236+00:00
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby

At the same time the DG broker will add log archive destination on the primary for the reinstated physical standby.

ALTER SYSTEM SET log_archive_dest_3='service="dgtest3tns"','SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="dgtest3" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;

The other standby will get an update on its fal_server with reference to the reinstated standby.

ALTER SYSTEM SET fal_server='dgtest2tns','dgtest3tns' SCOPE=BOTH;

At the end the data guard configuration will have same number of standbys as before but with a different primary.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest2 - Primary database
    dgtest  - Physical standby database
    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 58 seconds ago)

A! Help

Labels

Tuesday, September 28, 2021

Failover and Reinstate With Multiple Physical Standbys

About Me

Downloads

Quick Response

Popular

Blog Archive

Total Pageviews

Followers

Oracle Documentation