First, the case of UNDO tablespace:

In Oracle 12cR1 the undo tablespace was common and shared between all container databases, so features like flashback was only feasible on the container-level.

In 12cR2 this is changed as you create a local undo tablespace.

By executing the following query:

SQL> SELECT con_id, tablespace_name

FROM   cdb_tablespaces

WHERE  tablespace_name LIKE ‘UNDO%’

ORDER BY con_id;

 

Only the root container has UNDO tablespace

 

An existing pluggable database with no local undo tablespace

To enable Local Undo Tablespace:

Access the database as sysdba to the root container CBD$ROOT

SQL>shutdown immediate;

SQL> startup upgrade;

SQL> alter database local undo on;

SQL> shutdown immediate;

SQL>startup;

To check that its successful, execute the following query:

SQL>set pages 1000

SQL>set lines 300

SQL> SELECT property_name, property_value

FROM   database_properties

WHERE  property_name = ‘LOCAL_UNDO_ENABLED’;

To Enable Flash Back:

 

Configure flash recovery area:

 

SQL> alter system set db_recovery_file_dest = ‘C:\app\emodb\oradata\oraclehero\fast_recover_area\’ scope=both;

 

SQL> archive log list;

 

SQL>  alter system set db_flashback_retention_target = 2880 scope = both;

 

**retention specified will be for 2 days 2880 minutes

Second, Enable flashback from CDB$root level:

SQL> alter database flashback on;

 

If you try to enable flashback from pluggable database level you will receive the following error:

 

SQL> alter session set container=PDB_EXPLORE;

 

SQL> alter database flashback on;

alter database flashback on

*

ERROR at line 1:

ORA-03001: unimplemented feature

Important remark: it’s important to enable force logging on the pluggable database level:

SQL> select pdb_name, force_logging, force_nologging from cdb_pdbs where pdb_name=’PDB_EXPLORE’;

To enable it:

 

SQL> alter session set container=PDB_EXPLORE;

SQL> alter pluggable database pdb_explore enable force logging;

 

Also, check that tablespaces are configured for flash back for pluggable database PDB_EXPLORE:

select name,flashback_on from v$tablespace;

Now to explore recover we can do it from different perspectives:

• SCN
• Specific date
• Recovery point

 

SQL> Select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

————————

13524572

 

SQL> select to_char (sysdate, ‘mm-dd-yyyy hh24: mi: ss’) cdate from dual;

 

CDATE

———————

05-12-2017 12: 53: 35

 

SQL> create restore point test_flash guarantee flashback database;

 

 

Now to explore it, current Table called CITIES has the current data:

 

*** insert new records in the table:

INSERT INTO “EXP_SCHEMA”.”CITIES” (CITY_NAME, COUNTRY_NAME, TOURISM_LEVEL) VALUES (‘ BARCELONA’, ‘SPAIN’, ‘8’);

INSERT INTO “EXP_SCHEMA”.”CITIES” (CITY_NAME, COUNTRY_NAME, TOURISM_LEVEL) VALUES (‘ BERLIN’, ‘GERMANY’, ‘7’);

INSERT INTO “EXP_SCHEMA”.”CITIES” (CITY_NAME, COUNTRY_NAME, TOURISM_LEVEL) VALUES (‘TOKYO’, ‘JAPAN’, ‘6’);

Commit;

 

 

SQL> alter pluggable database pdb_explore close;

 

SQL> flashback pluggable database PDB_EXPLORE to restore point test_flash;

 

 

SQL> alter pluggable database pdb_explore open resetlogs;

 

 

Now check the table data again , its back as it was !!!