Flashback PDB in a Multi Standby Data Guard Configuraiton

There is an earlier post which looks flashback use with various configuration such as PDB, non-CDB and CDB on 12.2. This post looks flashback use when multiple standbys are invovlved and when each are open in different modes. The 19c release introduced a change which flashback the standby when primary is flashback. Therefore unlike the previous versions, there's no need to flashback the standby PDB after the primary is flashback. However, this transparent flashback of standby PDB doesn't work if any of them are open in read only mode at the time of primary flashback.
The DG configuration consits of 3 instances.

DGMGRL> show configuration

Configuration - test_dg

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

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 34 seconds ago)

The dgtest2 instance is open in read only mode.

SQL> select open_mode,db_unique_name from v$database;

OPEN_MODE            DB_UNIQUE_NAME
-------------------- --------------
READ ONLY WITH APPLY dgtest2

DGMGRL> validate database dgtest2

  Database Role:     Physical standby database
  Primary Database:  dgtest

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

  Managed by Clusterware:
    dgtest :  YES
    dgtest2:  YES

The dgtest3 is open in mount mode.

SQL>  select open_mode,db_unique_name from v$database;

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

DGMGRL> validtae database dgtest3
Unrecognized command "validtae", try "help"
DGMGRL> validate database dgtest3

  Database Role:     Physical standby database
  Primary Database:  dgtest

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

  Managed by Clusterware:
    dgtest :  YES
    dgtest3:  YES

To verify the flashback works correctly, create a guarantee restore point, truncate a table and flashback the PDB in primary.

SQL> alter session set container=dgpdb;

Session altered.

SQL> create restore point pdb_restore_point guarantee flashback database;

Restore point created.

truncate table x;

SQL> alter session set container=dgpdb;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
DGPDB

SQL> alter pluggable database close;

Pluggable database altered.


SQL> flashback pluggable database DGPDB to restore point PDB_RESTORE_POINT;

Flashback complete.


SQL> alter pluggable database open resetlogs;

Pluggable database altered.

On the primary DB alert log could see the flashback activity on the PDB

2021-09-23T13:27:24.756572+00:00
DGPDB(3):alter pluggable database close
2021-09-23T13:27:24.780090+00:00
DGPDB(3):Pluggable database DGPDB closing
DGPDB(3):JIT: pid 14510 requesting stop
DGPDB(3):Closing sequence subsystem (10152431219).
DGPDB(3):Buffer Cache flush started: 3
DGPDB(3):Buffer Cache flush finished: 3
Pluggable database DGPDB closed
DGPDB(3):Completed: alter pluggable database close
2021-09-23T13:27:52.164215+00:00
DGPDB(3):flashback database to restore point PDB_RESTORE_POINT
DGPDB(3):ORA-65040 signalled during: flashback database to restore point PDB_RESTORE_POINT...
2021-09-23T13:28:16.884252+00:00
DGPDB(3):flashback pluggable database DGPDB to restore point PDB_RESTORE_POINT
2021-09-23T13:28:17.044790+00:00
DGPDB(3):Flashback Restore Start
DGPDB(3):Restore Flashback Pluggable Database DGPDB (3) until change 4361208
DGPDB(3):Flashback Restore Complete
DGPDB(3):Flashback Media Recovery Start
2021-09-23T13:28:17.169821+00:00
DGPDB(3):Serial Media Recovery started
DGPDB(3):max_pdb is 3
2021-09-23T13:28:17.262801+00:00
DGPDB(3):Recovery of Online Redo Log: Thread 1 Group 1 Seq 22 Reading mem 0
DGPDB(3):  Mem# 0: +DATA/DGTEST/ONLINELOG/group_1.264.1067878075
DGPDB(3):  Mem# 1: +FRA/DGTEST/ONLINELOG/group_1.418.1067878077
2021-09-23T13:28:17.285159+00:00
DGPDB(3):Incomplete Recovery applied until change 4361208 time 09/23/2021 13:25:36
DGPDB(3):Flashback Media Recovery Complete
DGPDB(3):Flashback Pluggable Database DGPDB (3) recovered until change 4361208
DGPDB(3):Completed: flashback pluggable database DGPDB to restore point PDB_RESTORE_POINT
2021-09-23T13:29:06.616625+00:00
Thread 1 advanced to log sequence 23 (LGWR switch),  current SCN: 4362053
  Current log# 2 seq# 23 mem# 0: +DATA/DGTEST/ONLINELOG/group_2.262.1067878077
  Current log# 2 seq# 23 mem# 1: +FRA/DGTEST/ONLINELOG/group_2.419.1067878079
2021-09-23T13:29:06.733706+00:00
ARC8 (PID:13201): Archived Log entry 800 added for T-1.S-22 ID 0xf0d12d4d LAD:1
2021-09-23T13:29:22.740922+00:00
Control autobackup written to DISK device

handle '+FRA/DGTEST/AUTOBACKUP/2021_09_23/s_1084022962.365.1084022963'

2021-09-23T13:29:56.709000+00:00
DGPDB(3):alter pluggable database open resetlogs
2021-09-23T13:29:56.810137+00:00
Online datafile 11
Online datafile 10
Online datafile 9
Online datafile 8
DGPDB(3):Pluggable database DGPDB pseudo opening
DGPDB(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
DGPDB(3):Autotune of undo retention is turned on.
DGPDB(3):Endian type of dictionary set to little
DGPDB(3):Undo initialization recovery: Parallel FPTR failed: start:10304853 end:10304857 diff:4 ms (0.0 seconds)
DGPDB(3):Undo initialization recovery: err:0 start: 10304850 end: 10304912 diff: 62 ms (0.1 seconds)
DGPDB(3):[14628] Successfully onlined Undo Tablespace 2.
DGPDB(3):Undo initialization online undo segments: err:0 start: 10304912 end: 10305010 diff: 98 ms (0.1 seconds)
DGPDB(3):Undo initialization finished serial:0 start:10304850 end:10305014 diff:164 ms (0.2 seconds)
DGPDB(3):Database Characterset for DGPDB is AL32UTF8
DGPDB(3):Pluggable database DGPDB pseudo closing
DGPDB(3):JIT: pid 14628 requesting stop
DGPDB(3):Closing sequence subsystem (10305197570).
2021-09-23T13:29:57.761732+00:00
DGPDB(3):Buffer Cache flush started: 3
2021-09-23T13:29:57.831904+00:00
DGPDB(3):Buffer Cache flush finished: 3
DGPDB(3):Pluggable database DGPDB opening in read write
DGPDB(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
DGPDB(3):Autotune of undo retention is turned on.
DGPDB(3):Endian type of dictionary set to little
DGPDB(3):Undo initialization recovery: Parallel FPTR complete: start:10305988 end:10305999 diff:11 ms (0.0 seconds)
DGPDB(3):Undo initialization recovery: err:0 start: 10305987 end: 10305999 diff: 12 ms (0.0 seconds)
DGPDB(3):[14628] Successfully onlined Undo Tablespace 2.
DGPDB(3):Undo initialization online undo segments: err:0 start: 10305999 end: 10306236 diff: 237 ms (0.2 seconds)
DGPDB(3):Undo initialization finished serial:0 start:10305987 end:10306247 diff:260 ms (0.3 seconds)
DGPDB(3):Pluggable database DGPDB dictionary check beginning
2021-09-23T13:29:58.820047+00:00
DGPDB(3):Pluggable Database DGPDB Dictionary check complete
2021-09-23T13:29:58.831530+00:00
DGPDB(3):Database Characterset for DGPDB is AL32UTF8
DGPDB(3):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)
2021-09-23T13:30:00.195291+00:00
DGPDB(3):Opening pdb with no Resource Manager plan active
DGPDB(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/app/oracle/product/19.x.0/dbhome_2/javavm/admin/, pid 14628 cid 3
Control autobackup written to DISK device

handle '+FRA/DGTEST/AUTOBACKUP/2021_09_23/s_1084023000.364.1084023001'

Pluggable database DGPDB closed
DGPDB(3):Completed: alter pluggable database open resetlogs

On the alter log of the standby instance open in mount mode (dgtest3) entries show flashback happening transparaently.

2021-09-23T13:29:56.669475+00:00
(3):Recovery of pluggable database DGPDB aborted due to pluggable database open resetlog marker.
(3):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 4361208, or timestamp before 09/23/2021 13:25:36, and restart recovery
PR00 (PID:13309): MRP0: Detected orphaned datafiles!
PR00 (PID:13309): Recovery will possibly be retried after pluggable database flashback...
2021-09-23T13:29:56.670731+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest3/dgtest3/trace/dgtest3_pr00_13309.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
PR00 (PID:13309): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.46).
Datafiles are recovered to a consistent state at change 4363117 but controlfile could be ahead of datafiles.
Stopping change tracking
2021-09-23T13:29:56.878879+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest3/dgtest3/trace/dgtest3_pr00_13309.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
2021-09-23T13:30:16.929698+00:00
MRP0 (PID:13302): Recovery coordinator performing automatic flashback of pluggable database 3 to SCN:0x0000000000428bf7 (4361207)
Flashback Restore Start
Restore Flashback Pluggable Database DGPDB (3) until change 4356167
Flashback Restore Complete
 Started logmerger process
2021-09-23T13:30:17.324525+00:00
PR00 (PID:14265): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2021-09-23T13:30:17.694838+00:00
Parallel Media Recovery started with 8 slaves
2021-09-23T13:30:17.739944+00:00
Stopping change tracking
2021-09-23T13:30:17.802774+00:00
PR00 (PID:14265): Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_23/thread_1_seq_21.285.1084022085
2021-09-23T13:30:17.975446+00:00
PR00 (PID:14265): Media Recovery Log +FRA/DGTEST3/ARCHIVELOG/2021_09_23/thread_1_seq_22.282.1084022947
2021-09-23T13:30:18.373527+00:00
PR00 (PID:14265): Media Recovery Waiting for T-1.S-23 (in transit)
2021-09-23T13:30:18.378327+00:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 23 Reading mem 0
  Mem# 0: +FRA/DGTEST3/ONLINELOG/group_4.461.1069533181
(3):Applying tablespace dictionary check redo for tablespace #0
(3):Applying tablespace dictionary check redo for tablespace #1
(3):Applying tablespace dictionary check redo for tablespace #2
(3):Applying tablespace dictionary check redo for tablespace #3
(3):Dropping offline tempfile '+DATA'
(3):Applying tablespace dictionary check redo for tablespace #4
2021-09-23T13:30:19.374861+00:00
(3):Applying datafile dictionary check redo for datafile #8
2021-09-23T13:30:19.431584+00:00
(3):Applying datafile dictionary check redo for datafile #9
(3):Applying datafile dictionary check redo for datafile #10
(3):Applying datafile dictionary check redo for datafile #11

However, on the alert log of the standby instance open in read only mode (dgtest2) shows recovery process shutting down.

2021-09-23T13:29:56.441259+00:00
(3):Recovery of pluggable database DGPDB aborted due to pluggable database open resetlog marker.
(3):To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 4361208, or timestamp before 09/23/2021 13:25:36, and restart recovery
PR00 (PID:12442): MRP0: Detected orphaned datafiles!
PR00 (PID:12442): Recovery will possibly be retried after pluggable database flashback...
2021-09-23T13:29:56.442191+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_12442.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
PR00 (PID:12442): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 4363117
Stopping change tracking
2021-09-23T13:29:56.585631+00:00
Errors in file /opt/app/oracle/diag/rdbms/dgtest2/dgtest2/trace/dgtest2_pr00_12442.trc:
ORA-39874: Pluggable Database DGPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4361208.
2021-09-23T13:30:17.728791+00:00
MRP0 (PID:12431): Recovery coordinator encountered one or more errors during automatic flashback on standby
2021-09-23T13:30:17.728863+00:00
Background Media Recovery process shutdown (dgtest2)

This is expected as flashback cannot happen when database is in open mode even if it is read only. The DG broker shows an error state.

DGMGRL> show configuration

Configuration - test_dg

  Protection Mode: MaxAvailability
  Members:
  dgtest  - Primary database
    dgtest2 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

    dgtest3 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 48 seconds ago)

DGMGRL> show database dgtest2

Database - dgtest2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          2 minutes 15 seconds (computed 1 second ago)
  Average Apply Rate: 20.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    dgtest2

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Database Status:
ERROR

Stopping and starting the PDB on the standby instance does not start the recovery process.

Stopping and starting the apply process (apply-off/on) on the standby instance also does not start the recovery process.

Keeping the PDB in mount mode and stopping and starting the apply service also doesn't start the recovery process.

Trying to flashback the PDB while PDB is in mount also fails.

SQL> flashback pluggable database to scn 4361208;
flashback pluggable database to scn 4361208
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

Intresting the error says DB must be in mount mode even while it's mounted. What it actually refers here is that the CDB must also be in a mounted state not just the PDB.

To resolve the error and start the recovery process stop the CDB and start it in mount mode.

srvctl stop database -db $ORACLE_SID
srvctl start database -db $ORACLE_SID -startoption mount

This will result in flashback happening transparently on the PDB similar to instance that was in mount mode and apply process will continue without any issue.
After recovery on the standby has caught up with primary, it could be open in read only mode again.

Related Post
Flashback Primary DB in a Data Guard - PDB vs non-CDB vs CDB