Flashback a Pluggable Database in 12c R2

In Oracle Database 12.1 flashback database operations were limited to the root container, and therefore affected all pluggable databases (PDBs) associated with the root container. Oracle Database 12.2 now supports flashback of a pluggable database.

My environment details- 

Primary - SRORCL
Standby - TRORCL

At primary-

C:\Users\Mahesh>set ORACLE_SID=SRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:02:16 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
true if local undo is enabled

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     SRORCL       READ WRITE           PRIMARY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ WRITE NO
         4 SRPDB_DUP                      READ WRITE NO
SQL> select flashback_on from v$database ;

FLASHBACK_ON
------------------
YES

SQL>

At standby-

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     TRORCL       READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ ONLY  NO
         4 SRPDB_DUP                      READ ONLY  NO

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CLOSING               1        107          1        380
ARCH      CLOSING               1        105      51200         32
ARCH      CLOSING               1        106      20480       1341
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        108      23870          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1        108      23870     409600

11 rows selected.

SQL> select flashback_on from v$database ;

FLASHBACK_ON
------------------
YES

SQL>

At Primary database truncate one table-

SQL> alter session set container=SRPDB;

Session altered.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
06-JUN-20 11.55.39.355000 AM +05:30

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SRPDB                          READ WRITE NO
SQL>
SQL> select count(1) from hr.job_history;

  COUNT(1)
----------
        10

SQL> truncate table hr.job_history;

Table truncated.

SQL>

SQL> select count(1) from hr.job_history;

  COUNT(1)
----------
         0

SQL>

Now flashback the primary database to get the table content back.

SQL> alter session set container=SRPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SRPDB                          READ WRITE NO
SQL> shut immediate ;
Pluggable Database closed.
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SRPDB                          MOUNTED
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

C:\Users\Mahesh>set ORACLE_SID=SRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:05:36 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> FLASHBACK PLUGGABLE DATABASE SRPDB TO TIMESTAMP TO_TIMESTAMP('2020-06-06 11:55:39', 'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>

Below is the alert log entry-

Flashback Restore Start
2020-06-06 12:13:57.668000 +05:30
Restore Flashback Pluggable Database SRPDB (3) until change 4401023
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
2020-06-06 12:13:59.012000 +05:30
Recovery of Online Redo Log: Thread 1 Group 2 Seq 106 Reading mem 0
  Mem# 0: F:\ORADATA\SRORCL\ONLINELOG\O1_MF_2_H5544RWG_.LOG
  Mem# 1: F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_2_H5545FBT_.LOG
2020-06-06 12:14:05.213000 +05:30
Recovery of Online Redo Log: Thread 1 Group 3 Seq 107 Reading mem 0
  Mem# 0: F:\ORADATA\SRORCL\ONLINELOG\O1_MF_3_H5544RXG_.LOG
  Mem# 1: F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_3_H5545FQ0_.LOG
Recovery of Online Redo Log: Thread 1 Group 1 Seq 108 Reading mem 0
  Mem# 0: F:\ORADATA\SRORCL\ONLINELOG\O1_MF_1_H5544RTJ_.LOG
  Mem# 1: F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_1_H5545F40_.LOG
2020-06-06 12:14:06.551000 +05:30
Incomplete Recovery applied until change 4404404 time 06/06/2020 11:55:40
Flashback Media Recovery Complete
Flashback Pluggable Database SRPDB (3) recovered until change 4404404, at 06/06/2020 11:55:40
Completed: FLASHBACK PLUGGABLE DATABASE SRPDB TO TIMESTAMP TO_TIMESTAMP('2020-06-06 11:55:39', 'YYYY-MM-DD HH24:MI:SS')

SQL> alter pluggable database SRPDB open resetlogs;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ WRITE NO
         4 SRPDB_DUP                      READ WRITE NO

SQL> alter session set container=SRPDB;

Session altered.

SQL> select count(1) from hr.job_history;

  COUNT(1)
----------
        10

SQL>

We recovered our table back, but if you check the standby alert logfile, could see MRP has been crashed. Below is the alertlog entry from standby database.

Recovery of pluggable database SRPDB aborted due to pluggable database open resetlog marker.
To continue recovery, restore all data files for this PDB to checkpoint SCN lower than 4404404, or timestamp before 06/06/2020 11:55:40, and restart recovery
MRP0: Background Media Recovery terminated with error 39874
Errors in file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_pr00_4336.trc:
ORA-39874: Pluggable Database SRPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4404404.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2020-06-06 12:15:43.266000 +05:30
Recovered data files to a consistent state at change 4407458
Errors in file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_pr00_4336.trc:
ORA-39874: Pluggable Database SRPDB recovery halted
ORA-39873: Restore all data files to a checkpoint SCN lower than 4404404.

C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:18:41 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CLOSING               1        107          1        380
ARCH      CLOSING               1        105      51200         32
ARCH      CLOSING               1        106      20480       1341
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        108      26760          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

10 rows selected.

SQL>

Check the current PDB incarnation from  v$pdb_incarnation and we can confirm that same SCN has been recorded in standby alert logfile as well.

SQL> alter session set container=SRPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SRPDB                          READ WRITE NO
SQL> select status,INCARNATION_SCN from v$pdb_incarnation;

STATUS  INCARNATION_SCN
------- ---------------
CURRENT         4404404
PARENT          1490582

SQL>

Let flashback the standby PDB to a SCN lower than 4404404, let say to SCN 4404402.

C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 6 12:26:53 2020
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ ONLY  NO
         4 SRPDB_DUP                      READ ONLY  NO
SQL> alter pluggable database SRPDB close immediate ;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          MOUNTED
         4 SRPDB_DUP                      READ ONLY  NO
SQL>

Let's try to do the flashback now.

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

SQL>

So it looks like it's not possible to flashback a PDB just by mounting only the affected PDB. We need to start the standby CDB in mount mode.

SQL> shut immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area  734003200 bytes
Fixed Size                  8923240 bytes
Variable Size             348129176 bytes
Database Buffers          369098752 bytes
Redo Buffers                7852032 bytes
Database mounted.
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 SRPDB                          MOUNTED
         4 SRPDB_DUP                      MOUNTED
SQL> flashback pluggable database SRPDB to scn 4404402;

Flashback complete.

SQL>

Alertlog entry-

flashback pluggable database SRPDB to scn 4404402
2020-06-06 12:36:53.275000 +05:30
Flashback Restore Start
Restore Flashback Pluggable Database SRPDB (3) until change 4400347
2020-06-06 12:36:54.305000 +05:30
Flashback Restore Complete
Completed: flashback pluggable database SRPDB to scn 4404402

Lets enable MRP now 

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        108      36864       1656
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        109        360          1

8 rows selected.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        108      36864       1656
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       RECEIVING             1        109        380          1
MRP0      APPLYING_LOG          1        108      38518      38519

9 rows selected.

SQL>

I did some log switch on the primary and verified standby got catch up.

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”,
  2      (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG
  3     WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  4     (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)
  5     FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                    110                   110          0

SQL>

Let's Open the PDB in read only and verify the table content 

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 SRPDB                          MOUNTED
         4 SRPDB_DUP                      MOUNTED
SQL> alter database open ;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          MOUNTED
         4 SRPDB_DUP                      MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ ONLY  NO
         4 SRPDB_DUP                      READ ONLY  NO

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD      ALLOCATED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
ARCH      CLOSING               1        110          1         18
ARCH      CLOSING               1        108      36864       1656
ARCH      CLOSING               1        109          1        430
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1        111        403          1
MRP0      APPLYING_LOG          1        111        403     409600

9 rows selected.

SQL> alter session set container=SRPDB;

Session altered.

SQL> select count(1) from hr.job_history;

  COUNT(1)
----------
        10

SQL>

Flashback and restore point in Container architecture - A study note

Flashback at container database-

In Oracle Database 12.1 flashback database operations were limited to the root container, and therefore affected all pluggable databases(PDBs) associated with the root container. Oracle Database 12.2 now supports flashback of a pluggable database.

In Oracle Database 12c Release 1 all containers in an instance shared the same undo tablespace. This mode is called SHARED UNDO MODE. In Oracle 12c Release 2 each container in an instance can use its own undo tablespace this is called LOCAL UNDO MODE. This mode is the default mode in this release.

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

Irrespective of local undo enabled or not, you can flashback a PDB in 12cR2.

In oracle 12.2 if local_undo is not enabled, then we can use AUXILIARY DESTINATION parameter , which will create an auxiliary instance with container database’s system,sysaux and undo tablespace, and then restore the PDB using rollback transactions. However this flahback will take a long time and will consume additional space on the auxiliary destination.

SQL> alter pluggable database SRPDB close;

SQL> flashback pluggable database SRPDB to SCN 345678 auxiliary destination '/oradata/aux_inst';

SQL> alter pluggable database SRPDBopen resetlogs;

Some facts about restore point in container database-

CDB restore points are accessible to every pluggable database (PDB) within the CDB. However, a CDB restore point does not reflect the PDB sub-incarnation of any of its PDBs.

CDB restore points are useful in the following scenarios:

The whole CDB needs to be recovered to a particular point in time.

Multiple PDBs in a CDB need to be recovered to a particular point in time.

PDB restore points are accessible only to the PDB in which they are defined. Creating a guaranteed PDB restore point requires careful consideration because such a restore point can prevent required flashback logs in the multitenant container database (CDB) from being reused. This can potentially impact CDB functioning because the fast recovery area could run out of space.

Clean PDB Restore Points

A clean PDB restore point is a PDB restore point that is created when the PDB is closed and when there are no outstanding transactions for that PDB. Clean PDB restore points are only applicable to CDBs that use shared undo.

Clean PDB restore points can be normal or guaranteed restore points. Use the CREATE CLEAN RESTORE POINT command to explicitly create a clean PDB restore point. For a CDB that uses shared undo, if a PDB is closed and it has no outstanding transactions, any PDB restore point created is marked as a clean PDB restore point.

If you anticipate that you may need to rewind a PDB to a particular point in time, for example, to a state just before an application upgrade, then it is recommended that you create a clean PDB guaranteed restore point.

For CDBs that use shared undo, a Flashback Database operation to a clean PDB restore point is faster than a Flashback Database operation to an SCN or other restore points that are not clean PDB restore points. This is because RMAN does not need to restore any backups while performing a flashback operation to a clean PDB restore point.

PDB restore points are accessible only to the PDB in which they are defined.