Guaranteed restore point

In front of any activity on the database ( example UPGRADE / application changes ) over the RMAN backup , in addition to exports and other
types of rescue, a fast method (especially then in case of rollback ) to have a consistency point is to create
a GUARANTEED RESTORE POINT for a possible rollback database.
Requirements :
COMPATIBLE parameter must be set to 10.2 or higher in the initialization parameter file of the database.
the database must be active in ARCHIVELOG mode . The operation used FLASHBACK DATABASE to bring the database to a guaranteed restore point requires the use of the archive redo log all the time until the desired recovery point .
the flash recovery area must be configured
If in the flashback database is not enabled , the database must be in mount state , and not open when you create the first guaranteed restore point
use :
## CREATING A GUARANTEED RESTORE POINT FOR ANY DATABASE ROLLBACK
– Connect to the DB in sqlplus
– Create a Guaranteed Restore Point assigning an alias ( example : POINT_PRE_UPGRADE ) whith the command:
set time on timing on
CREATE RESTORE POINT POINT_PRE_UPGRADE GUARANTEE FLASHBACK DATABASE;
– Verify the proper creation of the restore point with the following query from sqlplus
set lines 222
col TIME for a40
col name for a30
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;
## HOW TO REMOVE THE FLASHBACK GUARANTEED RESTORE POINT
– Drop flashback guaranteed restore point
set time on timing on
DROP RESTORE POINT POINT_PRE_UPGRADE;
## HOW TO PERFORM THE OPERATION OF ROLLING BACK FOR A FLASHBACK DATABASE
– Open two SSH sessions to the two RAC nodes to monitor alter log DB during operations Flashback , stop and start database
– Connect to the machine with another DB session
– Stop database
srvctl stop database -d NOME_DATABASE
– Make sure that all instances of the DB are NOT in running state ( must be SHUTDOWN )
srvctl status database -d NOME_DATABASE
– Login with sqlplus instance DB 1
example:
[oracle@namemachine ~]$ . oraenv
ORACLE_SID = [TEST1] ? TEST1
The Oracle base for ORACLE_HOME=/sw/oracle/db/11.2 is /sw/oracle/orabase/oracle
[oracle@namemachine ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 10 16:39:47 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
– start database in mount
startup mount;
– check the database is in mount state with this query:
set lines 222
select NAME, OPEN_MODE from v$database;
– Proceed with the operation of Flashback Database ( with the alias assigned to the Restore Point created before login )
set time on timing on
FLASHBACK DATABASE TO RESTORE POINT POINT_PRE_UPGRADE;
– If the operation is successful flashback ( Flashback complete ), open the database with
ALTER DATABASE OPEN RESETLOGS;
– Verify that the DB is in OPEN state ( READ WRITE )
set lines 222
select NAME, OPEN_MODE from v$database;
– Power off again from DB sqlplus
shutdown immediate;
exit;
– A restart of the DB from the cluster and verify that all instances are started correctly
srvctl status database -d NOME_DATABASE
srvctl start database -d NOME_DATABASE
srvctl status database -d NOME_DATABASE