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

Enable/Disable Flashback Database

Pre-request:

1.Must be in archivelog mode

2.Before enable flashback database we need to make sure the database is in archivelog mode.

3.Must do this from the root container.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 00:53:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> conn / as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

We can now enable/disable flashback database with the open mode in 12c.

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

We can check the flashback status from v$database.

SQL> select flashback_on from v$database;

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

SQL>

with db_flashback_retention_target parameter we can set flashback retention time.

SQL> sho parameter retention

NAME                                   TYPE          VALUE
------------------------------------ ----------- ----------------------
db_flashback_retention_target          integer        1440
undo_retention                         integer        900

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

System altered.

SQL>
Creating Restore Points
  • Creating restore point is just like same in normal database.
  • Its alias for a SCN.
  • Guaranteed restored point prevents the database from removing any flashback logs between that point and the current time.
  • Always remove unwanted guaranteed restore point.
Normal restore point:
SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> create restore point rsp;

Restore point created.

SQL> drop restore point rsp;

Restore point dropped.

SQL>
Guaranteed restore point:
SQL> create restore point rsp1 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp1;

Restore point dropped.

SQL>

Creating PDB level restore point and Guarantee restore point as follows.

SQL> conn / as sysdba
Connected.

SQL> alter session set container=pdb5;

Session altered.

SQL> create restore point rsp;

Restore point created.

SQL> drop restore point rsp;

Restore point dropped.

SQL> create restore point rsp1 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp1;

Restore point dropped.

From the root container also we can create PDB level restore point and Guarantee restore point

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> create restore point rsp for pluggable database pdb5;

Restore point created.

SQL> drop restore point rsp for pluggable database pdb5;

Restore point dropped.

SQL>

Guarantee restore point from root container:

SQL> 
SQL> create restore point rsp for pluggable database pdb5 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp for pluggable database pdb5;

Restore point dropped.

SQL>

Clean restore points can be created while connected to the PDB

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 02:09:44 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.
SQL> col name for a15;
SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            MOUNTED
PDB6            MOUNTED
PDB1            MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            READ WRITE
PDB6            READ WRITE
PDB1            READ WRITE

SQL> alter session set container=pdb5;

Session altered.


SQL> shutdown;
Pluggable Database closed.


SQL> create clean restore point crsp;
create clean restore point crsp
*
ERROR at line 1:
ORA-39891: Clean PDB restore point cannot be created when CDB is in local undo
mode.

SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME
----------------------------------------------------------------------PROPERTY_VALUE
-----------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
  • 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.
SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> alter database local undo off;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> col property_name for a20;
SQL> col property_value for a20;

SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME          PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED     FALSE

SQL>
SQL> create clean restore point crsp;

Restore point created. 

SQL> drop restore point crsp; 

Restore point dropped. 

SQL>
SQL> create clean restore point crsp guarantee flashback database;

Restore point created. 

SQL> drop restore point crsp; 

Restore point dropped. 

SQL> startup;


ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size		    8795904 bytes
Variable Size		  671090944 bytes
Database Buffers	 1828716544 bytes
Redo Buffers		    7979008 bytes
Database mounted.
Database opened.

They can also be created from the root container.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL> conn / as sysdba 
Connected. 

SQL> 

SQL> create clean restore point rsp for pluggable database pdb5; 
Restore point created. 

SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. 

SQL>

SQL> create clean restore point rsp for pluggable database pdb5 guarantee flashback database; 

Restore point created. 

SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped.

 SQL>
Flashback CDB and  PDB:
SQL> select * from t1;

ID
----------
1
2
3
4
5

SQL> create restore point rsp; 

Restore point created.

SQL> desc v$restore_point
Name                                        Null?      Type
-----------------------------------      -------- --------------------
SCN                                                 NUMBER
DATABASE_INCARNATION#                               NUMBER
GUARANTEE_FLASHBACK_DATABASE                        VARCHAR2(3)
STORAGE_SIZE                                        NUMBER
TIME                                                TIMESTAMP(9)
RESTORE_POINT_TIME                                  TIMESTAMP(9)
PRESERVED                                           VARCHAR2(3)
NAME                                                VARCHAR2(128)
PDB_RESTORE_POINT                                   VARCHAR2(3)
CLEAN_PDB_RESTORE_POINT                             VARCHAR2(3)
PDB_INCARNATION#                                    NUMBER
CON_ID                                              NUMBER

SQL> select name,scn from v$restore_point;

NAME                                          SCN
--------------------                       ----------
RSP                                           3096868
RSP1                                          3098148
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.

SQL> conn / as sysdba
Connected.


SQL> flashback database to restore point rsp;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Flashback can work in different situations:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> flashback database to scn 3096868;

Flashback complete.

SQL> flashback database to before scn 3096868;

Flashback complete.

SQL> flashback database to restore point rsp1;

Flashback complete.

Flashback of a PDB differ on whether local undo mode or not.

SQL> conn / as sysdba 
Connected.

SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL>flashback pluggable database pdb5 to restore point rsp;

Flashback complete.

SQL> alter pluggable database pdb5 open resetlogs;

Database altered.

Flashback can work in different situations:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> flashback database pdb5 to scn 3196745;

Flashback complete.

SQL> flashback database pdb5 to before scn 3196745;

Flashback complete.

SQL> flashback database pdb5 to restore point rsp1;

Flashback complete.
Flashback a Pluggable Database:
  • create a restore point.
  • connect inside the PDB and make changes.
  • Flashback the PDB to the restore point.
  • Check the table content is missing.
SQL> startup 
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> conn / as sysdba
Connected.

SQL> create restore point rsp2 for pluggable database pdb5;

Restore point created.

SQL> conn hari/hari@pdb5
Connected.

SQL> select * from t1; 

ID 
---------- 
1
2 
3 
4 
5

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL> flashback pluggable database pdb5 to restore point rsp2;

Flashback complete.

SQL> alter pluggable database pdb5 open resetlogs;

Pluggable database altered. 


SQL> conn hari/hari@pdb5
Connected.

SQL> select * from t1; 
select * from t1 
* 
ERROR at line 1: 
ORA-00942: table or view does not exist


 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Leave a Reply

Your email address will not be published. Required fields are marked *