📋Steps in this guide1/5

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

Oracle Database 12.2 now supports flashback of a pluggable database, making flashback database relevant in the multitenant architecture again.

oracle 12cconfigurationintermediate

by OracleDba

13 views

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. We can now enable/disable flashback database with the following commands. We can check the status of flashback database using the following query. The amount of flashback logs retained is controlled by the parameter, which indicates the retention time in minutes.

Code/Command (click line numbers to comment):

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER DATABASE FLASHBACK ON;
--ALTER DATABASE FLASHBACK OFF;

SELECT flashback_on FROM v$database;

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

1 row selected.

SQL>

-- 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. 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. Alternatively you can create them from the root container by using the clause. Information about restore points can be displayed using the view.

Code/Command (click line numbers to comment):

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;

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;

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;

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. They can also be created from the root container. All restore points created while a pluggable database is closed are marked as clean, as shown by the column in the view.

Code/Command (click line numbers to comment):

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;

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;

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

The basic procedure to flashback a CDB is as follows. The flashback operation itself can take one of several forms. 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. The flashback operation itself can take one of several forms. 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.

Code/Command (click line numbers to comment):

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;

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;

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

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;

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. Make a change inside the PDB. Flashback the PDB to the restore point. Check to see the table is missing. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

CONN / AS SYSDBA

CREATE RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;

CONN test/test@pdb1

CREATE TABLE t1 (
  id NUMBER
);

INSERT INTO t1 VALUES (1);
COMMIT;

SELECT * FROM t1;

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

SQL>

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes;
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;

CONN test/test@pdb1

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

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!