in Oracle

12c MultiTenant Posts — 5 : Flashback a PDB

12.2 allows FLASHBACK DATABASE for a Pluggable Database.

Note that ALTER DATABASE FLASHBACK ON is not enabled by default in a newly created database — you must issue this command at the CDB level to enable Flashback for all the Pluggable Databases.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> select count(*) from obj_list;

COUNT(*)
----------
145282

SQL> select count(*) from hkc_store_file;

COUNT(*)
----------
1

SQL> truncate table obj_list;

Table truncated.

SQL> drop table hkc_store_file;

Table dropped.

SQL>

Now, let me Flashback the PDB.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database newpdb close;

Pluggable database altered.

SQL> flashback pluggable database newpdb to timestamp sysdate-3/1440;

Flashback complete.

SQL> alter pluggable database newpdb open;
alter pluggable database newpdb open
*
ERROR at line 1:
ORA-01113: file 19 needs media recovery
ORA-01110: data file 19:
'/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4'


SQL> alter pluggable database newpdb open resetlogs;

Pluggable database altered.

SQL>

Let me test the data.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select count(*) from obj_list;

COUNT(*)
----------
145282

SQL> select count(*) from hkc_store_file;

COUNT(*)
----------
1

SQL>

Yes, the FLASHBACK DATABASE is successful.

What are the pre-requisites ?

1. ALTER DATABASE FLASHBACK ON at the CDB
2. ARCHIVELOG at the CDB
3. LOCAL UNDO enabled — highly recommended else a subsequent Point In Time Recovery of the CDB may prevent OPENing the PDB
4. OPEN RESETLOGS for the PDB

.
.
.