May 4, 2016

Flashback Pluggable Database (PDB) in Oracle Database 12c Release 2


Oracle Database 12.1 flashback database operations were limited to the root container, hence affected all pluggable databases (PDBs) associated with the root container. Oracle Database 12.2 now supports flashback of a pluggable database, making flashback database relevant in the multitenant architecture again.






Enable/Disable Flashback Database

Before we can enable flashback database we need to make sure the database is in archivelog mode. You must do this from the root container.
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
We can now enable/disable flashback database with the following commands.
ALTER DATABASE FLASHBACK ON;
--ALTER DATABASE FLASHBACK OFF;
We can check the status of flashback database using the following query.
SELECT flashback_on FROM v$database;

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

1 row selected.

SQL>
The amount of flashback logs retained is controlled by the DB_FLASHBACK_RETENTION_TARGET parameter, which indicates the retention time in minutes.
-- Retention for 7 days.
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=10080 SCOPE=BOTH;

Creating Restore Points

A restore point is just a text alias for a SCN. A guaranteed restored point prevents the database from removing any flashback logs between that point and the current time, so you should always remove unnecessary guaranteed restored point.
Creating restore points at the CDB level is the same as for the non-CDB architecture. The following examples create and drop a normal and guaranteed restore point at the CDB level.
CONN / AS SYSDBA

-- Normal restore point.
CREATE RESTORE POINT cdb1_before_changes;
DROP RESTORE POINT cdb1_before_changes;

-- Guaranteed restore point.
CREATE RESTORE POINT cdb1_before_changes GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT cdb1_before_changes;
There are several options for creating restore points at the PDB level. If you connect to the PDB you can issue the commands as normal.
CONN / AS SYSDBA

ALTER SESSION SET CONTAINER=pdb1;

-- Normal restore point.
CREATE RESTORE POINT pdb1_before_changes;
DROP RESTORE POINT pdb1_before_changes;

-- Guaranteed restore point.
CREATE RESTORE POINT pdb1_before_changes GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT pdb1_before_changes;
Alternatively you can create them from the root container by using the FOR PLUGGABLE DATABASE clause.
CONN / AS SYSDBA

-- Normal restore point.
CREATE RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;
DROP RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;

-- Guaranteed restore point.
CREATE RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1 GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;
Information about restore points can be displayed using the V$RESTORE_POINT view.

Creating Clean Restore Points

 Ignore this section if you are running in local undo mode.
It is preferable for the container database to be running in local undo mode, but flashback PDB does not depend on it. If the CDB is running in shared undo mode, it is more efficient to flashback to clean restore points. These are restore points taken when the pluggable database is down, with no outstanding transactions.
Clean restore points can be created while connected to the PDB as follows.
CONN / AS SYSDBA

ALTER SESSION SET CONTAINER=pdb1;

SHUTDOWN;

-- Clean restore point.
CREATE CLEAN RESTORE POINT pdb1_before_changes;
DROP RESTORE POINT pdb1_before_changes;

-- Clean guaranteed restore point.
CREATE CLEAN RESTORE POINT pdb1_before_changes GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT pdb1_before_changes;

STARTUP;
They can also be created from the root container.
CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;

-- Normal restore point.
CREATE CLEAN RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;
DROP RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;

-- Guaranteed restore point.
CREATE CLEAN RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1 GUARANTEE FLASHBACK DATABASE;
DROP RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;

ALTER PLUGGABLE DATABASE pdb1 OPEN;
All restore points created while a pluggable database is closed are marked as clean, as shown by the CLEAN_PDB_RESTORE_POINT column in the V$RESTORE_POINT view.

Flashback Container Database (CDB) and Pluggable Database (PDB)

The basic procedure to flashback a CDB is as follows.
CONN / AS SYSDBA

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT cdb1_before_changes;
ALTER DATABASE OPEN RESETLOGS;

-- Open all pluggable databases.
ALTER PLUGGABLE DATABASE ALL OPEN RESETLOGS;
The flashback operation itself can take one of several forms.
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
FLASHBACK DATABASE TO RESTORE POINT my_restore_point;
The flashback of a PDB varies depending on whether local undo mode is used or not. Typically, you will be using local undo mode, so the procedure will be as follows.
CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
The flashback operation itself can take one of several forms.
FLASHBACK PLUGGABLE DATABASE pdb1 TO TIMESTAMP my_date;
FLASHBACK PLUGGABLE DATABASE pdb1 TO BEFORE TIMESTAMP my_date;
FLASHBACK PLUGGABLE DATABASE pdb1 TO SCN my_scn;
FLASHBACK PLUGGABLE DATABASE pdb1 TO BEFORE SCN my_scn;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT my_restore_point;
If you are using shared undo mode, then the syntax is a little different as you will have to specify a location for an auxiliary instance.
FLASHBACK PLUGGABLE DATABASE my_pdb TO SCN my_scn AUXILIARY DESTINATION '/u01/aux';
FLASHBACK PLUGGABLE DATABASE my_pdb TO RESTORE POINT my_restore_point AUXILIARY DESTINATION '/u01/aux';

Flashback a Pluggable Database (PDB) Example

Create a restore point.
CONN / AS SYSDBA

CREATE RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;
Make a change inside the PDB.
CONN test/test@pdb1

CREATE TABLE t1 (
  id NUMBER
);

INSERT INTO t1 VALUES (1);
COMMIT;

SELECT * FROM t1;

        ID
----------
         1

SQL>
Flashback the PDB to the restore point.
CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
Check to see the table is missing.
CONN test/test@pdb1

SELECT * FROM t1;
SELECT * FROM t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

Post a Comment

Note: Only a member of this blog may post a comment.

 
TECH SUPPORT © 2012-2017