Flashback PDB with Version 12.1.0.2

Flashback Pluggable Database

Oracle 12.2 will be general available within the next few weeks including several new fetaures for Flashback Pluggable Database. But it will take some more month before customers will adopt 12.2 for their production database and we need some more experience with the PDB Undo management. Because thats the requirement for Flashback Pluggable Database. But you can use this feature alread with 12.1.0.2 if your environment relies on Data Guard.This configuration might be an interesting alternative because it ends up with two individual PDBs – the original and the flashback version.


This is the base configuraiton:

  • Server 1: beethovens1 with the primary database  LUDWIG_S1 and the PDB DEMO
  • server 2: beethovens2 with the standby database LUDWIG_S2

The goal is to create a pluggable database named DEMO_CLONE as a flashback of DEMO at 10:00 A.M. with a full functioning Data Guard configuration.

Flashback Standby Datenbank

For this tasks it would have been sufficient to create the PDB while the CDB is opened read only. But often it’s necessary to open the PDB or the CDB for some changes in advance (e.g. to unlock an account). That’s the reason why we are using the Snapshot Standby feature of Data Guard.

oracle@beethovens2[LUDWIG1]% dgmgrl sysdg/sysdgLUDWIG@LUDWIG_S2

DGMGRL> show configuration

Configuration - LUDWIG_DG

  Protection Mode: MaxAvailability
  Members:
  LUDWIG_S1 - Primary database
    LUDWIG_S2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 29 seconds ago)

DGMGRL> show database "LUDWIG_S2"

Database - LUDWIG_S2

  Enterprise Manager Name: LUDWIG_S2.hv.devk.de
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 1 second ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Average Apply Rate:      5.00 KByte/s
  Real Time Query:         OFF
  Instance(s):
    LUDWIG1 (apply instance)
    LUDWIG2

Database Status:
SUCCESS

DGMGRL> CONVERT DATABASE "LUDWIG_S2" TO SNAPSHOT STANDBY;
Converting database "LUDWIG_S2" to a Snapshot Standby database, please wait...
Database "LUDWIG_S2" converted successfully

DGMGRL> EXIT;

racle@beethovens2[LUDWIG1]%:~> sqlplus / as sysdba

SQL> select db_unique_name, database_role, open_mode, flashback_on from v$database;

DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
LUDWIG_S2                        SNAPSHOT STANDBY READ WRITE

SQL> exit

As you can see the standby database is now opened read write. In the next step a Guaranteed Restore Point (GRP) will be created and the flashback database to the timestamp 10:00 A.M. will be initiated:

oracle@beethovens2[LUDWIG1]%:~> sqlplus / as sysdba

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

Total System Global Area 1.5737E+10 bytes
Fixed Size                  3729312 bytes
Variable Size            2919237728 bytes
Database Buffers         1.2784E+10 bytes
Redo Buffers               29822976 bytes
Database mounted.

SQL> CREATE RESTORE POINT before_flashback GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> FLASHBACK DATABASE TO TIMESTAMP to_date('23.02.2017 10:00:00','DD.MM.YYYY HH24:MI:SS');

Flashback complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

The datbase is now opened read write and we could create a manifest (UNPLUG INTO XML) to copy the PDB to an independant CDB (e.g. via a firewall or other restricted area). But in our case a database link is sufficient.

oracle@beethovens1[LUDWIG1]%:~> sqlplus / as sysdba
SQL> !mkdir /u03/temp/LUDWIG/DEMO_CLONE

SQL> CREATE DATABASE LINK "LUDWIG_S2" CONNECT TO c##johannes IDENTIFIED BY abc123def USING 'LUDWIG_S2';

SQL> CREATE PLUGGABLE DATABASE demo_clone FROM DEMO@LUDWIG_S2 file_name_convert=('DEMO','DEMO_CLONE');

Before opening the PDB_DEMO_CLONE we need to reactivate the Data Guard configuration. It is of course possible to use RMAN but if it’s not required because of the timeframe we can simply copy the closed data files (see Blog “PDB Cloning with Data Guard“). There is one minor restriction: before we can flashback the CDB to the GRP the PDB$SEED needs to be opened read write otherwise the following error will show up:

SQL> flashback database to restore point before_flashback;
flashback database to restore point before_flashback
*
ERROR at line 1:
ORA-01122: database file 2 failed verification check
ORA-01110: data file 2: '/u03/oradata/TC02/pdbshared/pdbseed/system.dbf'
ORA-01207: file is more recent than control file - old control file

Since it is officially not possible or supported to open the Seed database read write we need a kind of a hack but that one is obvious because otherwise it wouldn’t be possible to patch or update the seed PDB. The SESSION parameter “_oracle_script” (who ever will associate this parameter with the PDB$SEED?) will allow the seed database to be opened read write and apply changes if necessary. To overcome the flashback issue it’s sufficient to open it read write once and reopen it read only afterwards. And now the flashback database to GRP works:

oracle@beethovens2[LUDWIG1]%:~> sqlplus / as sysdba

SQL> ALTER SESSION SET "_oracle_script"=TRUE;

Session altered.

SQL> ALTER PLUGGABLE DATABASE pdb$seed OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb$seed OPEN READ ONLY FORCE;

Pluggable database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.5737E+10 bytes
Fixed Size                  3729312 bytes
Variable Size            2919237728 bytes
Database Buffers         1.2784E+10 bytes
Redo Buffers               29822976 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO RESTORE POINT before_flashback;

Flashback complete.
SQL> DROP RESTORE POINT before_flashback;

Now the snapshot standby can be reconverted into a physical standby again:

oracle@beethovens1[LUDWIG1]% dgmgrl sysdg/sysdgLUDWIG@LUDWIG_S1

DGMGRL> convert database "LUDWIG_S2" to physical standby;
Converting database "LUDWIG_S2" to a Physical Standby database, please wait...
Database "LUDWIG_S2" converted successfully

That’s it mainly. But there is the know problem with the creation of a PDB in a Data Guard environment. Because the PDB DEMO_CLONE has been created we need to recover the standby database. This works as described in the blog “PDB Cloning with Data Guard“. It’s sufficient to copy the data files to the standby databaes and start the apply as often as there are data files. Now the Data Guard is in sync again and we have an additional PDB with a former point in time.

Have fun while testing and using this feature and please provide some feedback!

Leave a Comment

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

Scroll to Top