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!