There are many ways via we can create restore point at pdb level.
Create restore point at pdb and dropping it.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> create restore point flashback_test_pdb guarantee flashback database;
Restore point created.
SQL> drop restore point flashback_test_pdb;
Restore point dropped.
Creating clean restore point at pdb level.
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.
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 and clean restore point can be taken when CDB is running in shared undo mode only
SQL> show con_name CON_NAME ------------------------------ ORCLPDB SQL> SHUTDOWN; Pluggable Database closed. CREATE CLEAN RESTORE POINT flashback_test_pdb_clean; DROP RESTORE POINT flashback_test_pdb_clean; CREATE CLEAN RESTORE POINT flashback_test_pdb_clean GUARANTEE FLASHBACK DATABASE; DROP RESTORE POINT flashback_test_pdb_clean; SQL> startup Pluggable Database opened.
We can create restore point on PDB from CDB as well.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> CREATE RESTORE POINT ORCLPDB_Flashback_test FOR PLUGGABLE DATABASE ORCLPDB; Restore point created. SQL> alter session set container=ORCLPDB; Session altered. SQL> show con_name CON_NAME ------------------------------ ORCLPDB SQL> drop restore point ORCLPDB_FLASHBACK_TEST; Restore point dropped. SQL> CREATE RESTORE POINT ORCLPDB_Flashback_test FOR PLUGGABLE DATABASE ORCLPDB GUARANTEE FLASHBACK DATABASE; Restore point created. SQL> SQL> SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> alter session set container=orclpdb; Session altered. SQL> show con_name CON_NAME ------------------------------ ORCLPDB SQL> drop restore point ORCLPDB_Flashback_test; Restore point dropped.
Now lets test flashback of pdb. The flashback of a PDB varies depending on whether local undo mode is used or not. we are using local undo mode here.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> CREATE SEQUENCE scott.test_seq11 START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE NOORDER;
Sequence created.
SQL> CREATE TABLE scott.test11 (sequence_number number(38));
Table created.
SQL> BEGIN
FOR v_LoopCounter IN 1..9 LOOP
INSERT INTO scott.test11 (sequence_number) VALUES (scott.test_seq11.NEXTVAL);
commit;
END LOOP;
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> select count(*) from scott.test11;
COUNT(*)
----------
9
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL>
SQL> CREATE RESTORE POINT ORCLPDB_Flashback_test FOR PLUGGABLE DATABASE ORCLPDB GUARANTEE FLASHBACK DATABASE;
Restore point created.
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> BEGIN
FOR v_LoopCounter IN 1..9 LOOP
INSERT INTO scott.test11 (sequence_number) VALUES (scott.test_seq11.NEXTVAL);
commit;
END LOOP;
END;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> select count(*) from scott.test11;
COUNT(*)
----------
18
SQL> ALTER PLUGGABLE DATABASE ORCLPDB CLOSE;
Pluggable database altered.
SQL> FLASHBACK PLUGGABLE DATABASE ORCLPDB TO RESTORE POINT ORCLPDB_Flashback_test;
Flashback complete.
SQL> ALTER PLUGGABLE DATABASE ORCLPDB OPEN RESETLOGS;
Pluggable database altered.
SQL> select count(*) from scott.test11;
COUNT(*)
----------
9
Database Adminstration 