PDB Point-in-Time Recovery and Flashback in Oracle 20c

The most significant point about the Oracle 20c database architecture is that non-CDB Oracle Database upgrades to non-CDB architecture are desupported. Meaning you need a container database in 20c and your data will reside within a pluggable database.

But then how about if you need to restore one PDB to any time in the recent past?

In Oracle database 20c, flashback and PITR (=point-in-time recovery) are supported when recovering PDBs to an ancestor or orphan PDB incarnations. These operations were not possible in 19c and below. Just as a reminder, in Oracle 12.1 flashback database operations were possible on root container level and thus affected all PDBs under the root container. Oracle 12.2 started supporting flashback of a PDB.

There is one restriction though in 20c: you cannot perform PDB flashback or PITR operation to a PDB incarnation within an orphan database incarnation. In other words, you can flashback as long as the CDB incarnation does not change. Or restated: you can flashback a PDB to an orphan PDB incarnation that is either within the same CDB incarnation or in an ancestor CDB incarnation. Also, flashback of a PDB to an orphan incarnation is supported only when the database uses local undo.

Now, this might confusing. Let me first shortly explain what is an ancestor incarnation and an orphan incarnation.

Database incarnations have the following relationships to each other:

– The current incarnation is the one in which the database is currently operating
– The incarnation from which the current incarnation originated after an OPEN RESETLOGS operation is the parent incarnation of the current incarnation
– The parent of the parent incarnation is an ancestor incarnation and any parent of an ancestor incarnation is also an ancestor of the current incarnation
– A noncurrent incarnation that is not a direct ancestor of the current incarnation is called orphan incarnation

During the flashback of the PDB, Oracle modifies only the data files for that PDB. The data in the other PDBs is not impacted. The point in time for the flashback can be one of the following:

– System Change Number
– Specific time in the past
– CDB restore point
– PDB restore point
– PDB clean restore point
– PDB guaranteed restore point

Here is an example of how flashback to any time in the recent past works in Oracle 20c.

We have lost at 2:30pm a table called RDBMS_BRANDS and a materialized zone map RDBMS_ZMAP from a pluggable database called NOVOPDB2. We have a restore point called rp1_novo_pdb2 created before the “disaster” at 8am in the morning. So, let us first flashback and verify we get the 2 objects back:

SQL> select systimestamp from dual;

SYSTIMESTAMP
-------------------------------------------------------------
05-JUL-20 02.35.30.569344 PM +00:00

SQL> SELECT table_name FROM DBA_TABLES where TABLE_NAME like '%RDBMS%';

no rows selected

SQL> ALTER PLUGGABLE DATABASE novopdb2 CLOSE;

Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE novopdb2 TO RESTORE POINT rp1_novo_pdb2;

Flashback complete.

SQL> ALTER PLUGGABLE DATABASE novopdb2 OPEN RESETLOGS;

Pluggable database altered.

SQL> SELECT table_name FROM DBA_TABLES where TABLE_NAME like '%RDBMS%';

TABLE_NAME
----------------------------------------------------------------------
RDBMS_BRANDS
RDBMS_ZMAP
 
Well, unfortunately, now we notice that slightly before noon time data was loaded into a new table called RDBMS_HISTORY which was not at 8am in the PDB. All SCNs between 8am and the current time are now on an orphan PDB incarnation. We will flahsback again using another restore point created at 12 o’clock.

SQL> ALTER PLUGGABLE DATABASE novopdb2 CLOSE;

Pluggable database altered.

SQL> FLASHBACK PLUGGABLE DATABASE novopdb2 TO RESTORE POINT rp2_novo_pdb2;

Flashback complete.

SQL> ALTER PLUGGABLE DATABASE novopdb2 OPEN RESETLOGS;

Pluggable database altered.

SQL> SELECT table_name FROM DBA_TABLES where TABLE_NAME like '%RDBMS%';

TABLE_NAME
--------------------------------------------------------------------------------
RDBMS_BRANDS
RDBMS_ZMAP
RDBMS_HISTORY

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
05-JUL-20 02.48.30.569344 PM +00:00

So, we managed to flashback to a point few hours after we opened with RESETLOGS from the previous flashback. But within the same incarnation of the CDB.

DBAs can follow the process of restore and recovery using the V$SESSION_LONGOPS and V$RECOVERY_PROGRESS views, respectively.

For the restore, the V$SESSION_LONGOPS view’s column OPNAME should be ‘Flashback Database’. Just like this:

SELECT sofar, totalwork, units
FROM v$session_longops
WHERE opname = 'Flashback Database';

The column SOFAR shows the data currently read in megabytes while the column TOTALWORK shows the total number of megabytes of flashback logs that must be read.

Restore points are created with the following command:

SQL> CREATE RESTORE POINT rp17 FOR PLUGGABLE DATABASE novopdb2;

Restore point created.