Tuesday, 6 December 2016

Multitenant : Flashback of a Container Database (CDB) in Oracle Database 12c Release 1

Setup

This article assumes the following things are in place for the examples to work.
  • You have a container database (CDB). You can see how to create one.
  • Your container database (CDB) has at least one pluggable database (PDB). You can see how to create one.
  • You have the flashback database feature enabled on the CDB. You can see how to do that.
  • You have backups of your CDB and PDBs. You can see how to do that.
With this in place, you can move on to the next sections.

Flashback of Container Database (CDB)

The basic procedure for performing a flashback database operation on a container database (CDB) is the same as that for a non-CDB database in 12c and previous versions, as described. So for example, if we want to flashback the CDB to a point in time 5 minutes ago, we might do one the following in SQL*Plus.

$ sqlplus / as sysdba

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(5/24/60);
ALTER DATABASE OPEN RESETLOGS;

-- Open all pluggable databases.
ALTER PLUGGABLE DATABASE ALL OPEN;

Or the following in RMAN.

$ rman target=/

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIME 'SYSDATE-(5/24/60)';
ALTER DATABASE OPEN RESETLOGS;

# Open all pluggable databases.
ALTER PLUGGABLE DATABASE ALL OPEN;

In both cases we connect to a root user with the SYSDBA or SYSBACKUP privilege.

The restrictions on the use of flashback database are similar to those of a non-CDB database, with one extra restriction. If you perform a point in time recovery of a pluggable database (PDB), you can not use flashback database to return the CDB to a point in time before that PITR of the PDB took place. This issue and the workaround for it are discussed in the next section.

Point In Time Recovery (PITR) of Pluggable Database (PDB) Restrictions

As mentioned previously, if you perform a point in time recovery of a pluggable database (PDB), you can not use flashback database to return the CDB to a point in time before that PITR of the PDB took place. The following example shows this.

Perform a PITR of a PDB to 5 minutes ago.

$ rman target=/

RUN {
  ALTER PLUGGABLE DATABASE pdb1 CLOSE;
  SET UNTIL TIME "TO_DATE('30-DEC-2013 10:15:00','DD-MON-YYYY HH24:MI:SS')";
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
  ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

Then we flashback the CDB to 15 minutes ago.

$ rman target=/

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SET UNTIL TIME "TO_DATE('30-DEC-2013 10:00:00','DD-MON-YYYY HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;

This results in the following error.

media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of flashback command at 12/28/2013 23:20:08
ORA-39866: Data files for Pluggable Database PDB1 must be offline to flashback across PDB point-in-time recovery.

The workaround for this is to do the following.
  • Take a backup of everything (CDB and PDBs). It's always a good idea to take a backup before doing anything major to your database.
  • Shutdown the PDB.
  • Offline all datafiles for the PDB.
  • Flashback the CDB.
  • Restore and recover the PDB to the point it was at before the flashback of the CDB.
You can see an example of this below.

rman target=/

# Backup everything.
BACKUP DATABASE PLUS ARCHIVELOG;

# Close PDB and take the datafiles offline.
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL OFFLINE;

# Flashback the CDB, along with all the PDBs.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO TIME "TO_DATE('30-DEC-2013 10:00:00','DD-MON-YYYY HH24:MI:SS')";
ALTER DATABASE OPEN RESETLOGS;

# Open all pluggable databases, except pdb1.
ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 OPEN;

# PITR of pdb1.
RUN {
  # PDB already closed. No SET UNTIL. We want to recover to the latest time.
  #ALTER PLUGGABLE DATABASE pdb1 CLOSE;
  #SET UNTIL TIME "TO_DATE('30-DEC-2013 10:15:00','DD-MON-YYYY HH24:MI:SS')";
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
  ALTER PLUGGABLE DATABASE pdb1 DATAFILE ALL ONLINE;
  ALTER PLUGGABLE DATABASE pdb1 OPEN;
}