Flashback RAC Database to Guarantee Restore Point ( GRP )

Here is an example of how to create a guarantee  restore point ( GRP ), and how to flashback the RAC database to this guarantee  restore point ( GRP ).

1)Check and make sure database is running at archivelog mode.

SQL> set pagesize 120
SQL> set linesize 180
SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON from v$database;
LOG_MODE FORCE_LOGGING CURRENT_SCN FLASHBACK_ON
------------ --------- ----------  ------------
ARCHIVELOG    YES         6622166            NO

2) Create the guarantee restore point.

SQL> select * from v$restore_point;
no rows selected

SQL> create table before_patching ( id number, name varchar2(30));
Table created.

SQL> create restore point BEFORE_PATCHING guarantee flashback database;
Restore point created.

SQL> create table after_GRP ( id number, name varchar2(30));
Table created.

SQL> select * from v$restore_point;
SCN        DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                        RESTORE_POINT_TIME PRE NAME           CON_ID
----------   
 6623316      1                  YES 4294967296  29-NOV-16 02.22.55.000000000 PM                 YES BEFORE_PATCHING     0

3) Applying the patches unsuccessfully.  Rollback is required to flashback the database to guarantee restore point ( GRP ).

4) Shutdown RAC database,  and then start up current instance only in mount mode.

$ srvctl stop database -d RACTEST
$ srvctl start instance -d RACTEST -i RACTEST1 -o mount;

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> flashback database to restore point BEFORE_PATCHING;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select LOG_MODE,FORCE_LOGGING,CURRENT_SCN,FLASHBACK_ON from v$database;

LOG_MODE FORCE_LOGGING CURRENT_SCN  FLASHBACK_ON
--------- ------------  ----------  -----------------
ARCHIVELOG YES             6625602  ESTORE POINT ONLY

SQL> select count(*) from before_patching;

 COUNT(*)
----------
 0

SQL> select count(*) from after_GRP;  <---DB was flasged back to the correct point.
 select count(*) from after_GRP
 *
ERROR at line 1:
ORA-00942: table or view does not exist

5) Start up all the instances.

$ srvctl stop database -d RACTEST
$ srvctl start database -d RACTEST
$ srvctl status database -d RACTEST
Instance RACTEST1 is running on node racnode1
Instance RACTEST2 is running on node racnode2
Instance RACTEST3 is running on node racnode3
Instance RACTEST4 is running on node racnode4

6) Drop the guarantee  restore point ( GRP ).

SQL> drop restore point BEFORE_PATCHING;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected
Advertisements
This entry was posted in FLASHBACK, RMAN Restore and Recovery and tagged , , , , , , , , . Bookmark the permalink.

2 Responses to Flashback RAC Database to Guarantee Restore Point ( GRP )

  1. Pingback: How to Install Database Component of a Patch onto ORMB Database | James Huang Oracle DBA Blog

  2. Pingback: How to Apply Database Component Of a Patch Onto OR|MB Database | James Huang – Databases Consultant

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s