Tuesday, July 6, 2021

19c Guarantee Restore Point GRP on PDB and Flashback PDB to GRP

 Here we will see how to create a Guarantee Restore Point (GRP) on 19c Pluggable database PDB and then how to Flashback the database to that GRP point in time. 

Create Guarantee Restore Point for a Pluggable Database PDB

Login to Container Database CDB as SYS user

Enable Flashback database if it's not enabled. Also check if the database has local undo enabled.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON

NO

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

LOCAL_UNDO_ENABLED TRUE true if local undo is enabled

SQL>  alter database flashback on;

SQL> COLUMN CURRENT_SCN FORMAT 99999999999999999999999999

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> create restore point GRP1 for pluggable database N11EBS guarantee flashback database;

SQL> column scn format 99999999999999999999

SQL> select NAME,TIME,SCN,PDB_RESTORE_POINT,GUARANTEE_FLASHBACK_DATABASE from V$RESTORE_POINT;

Flashback the database to GRP point in time 

Login to CDB as SYS user

SQL> alter pluggable database PDB1 close instances=all;

check alert log file until Pluggable database PDB1 is closed

SQL> SELECT * FROM V$PDBS;

SQL> flashback pluggable database PDB1 to restore point GRP1;

CDB1 SQL> alter pluggable database PDB1 open resetlogs; --On Instance 1

Now On Instance 2 open the database WITHOUT restorelogs

CDB2 SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;

Now Restart all the instances using srvctl.

srvctl stop database -d CDB

srvctl start database -d CDB

srvctl status database -d CDB