Guaranteed Restore Points came with oracle 10gR2. It can ensure that you
can rewind the database to a time without tradional point in time recovery. It
uses db_file_recovery_dest to keep the changed blocks, even if flashback
logging is not enabled for your database. It never age out of the
control file and must be explicitly dropped.
A normal restore point assigns a
restore point name to an SCN or specific point in time. The control file can
maintain a record of thousands of normal restore points with no significant
effect on database performance. Normal restore points eventually age out of the
control file if not manually deleted, so they require no ongoing maintenance.
SQL>CREATE RESTORE POINT before_upgrade;
Creating a normal restore point
eliminates manually recording an SCN in advance or determines the correct SCN
after the fact by using features such as Flashback Query. Like a normal restore
point, a guaranteed restore point serves as an alias for an SCN in recovery
operations.
A guaranteed restore point ensures that
you can use Flashback Database to rewind a database to its state at the restore
point SCN, even if the generation of flashback logs is disabled. If flashback
logging is enabled, then a guaranteed restore point enforces the retention of
flashback logs required for Flashback Database to any SCN after the earliest
guaranteed restore point.
Caution: If we are using Guaranteed Restore Points with Flashback logging turned
on, we need to exercise care that we drop restore points that are not in use or
that we allocate sufficient space for the flashback logs as specified by the
init.ora parameter db_recovery_file_dest_size. This is because the flashback
logs will be retained and not overwritten due to space constraints when we
create guaranteed restore points.
To create first guaranteed restore
point when flashback off, you first start the database in mount state after
a consistent shut down. After opening in mount state you can create
your first guaranteed restore point like below.
SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK
DATABASE;
You can use the LIST command to list
either a specific restore point or all restore points known to the RMAN
repository
LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;
SCN
RSP Time Type
Time Name
—————- ——— ———-
———
—---
----
431843 4-NOV-13 4-NOV-13 NORMAL_RS
448619 4-NOV-13 GUARANTEED 4-NOV-13 GUARANTEED_RS
RMAN indicates the SCN and time of the
restore point, the type of restore point, and the name of the restore
point. You can useV$RESTORE_POINT control file view to check a list of
all currently defined restore points (normal and guaranteed).
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
You can use the below query to find only Guaranteed restore points:
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE = ‘YES’;
Note: For normal restore
points, STORAGE_SIZE is zero. For guaranteed restore points,
STORAGE_SIZE indicates the amount of disk space in the flash recovery area
used to retain logs required to guarantee FLASHBACK DATABASE to that
restore point.
Dropping
Restore Points:
When you are satisfied that you do not
need an existing restore point, or when you want to create a new restore point
with the name of an existing restore point, you can drop the restore point.
SQL>DROP RESTORE POINT before_app_upgrade;
Note: The same statement is
used to drop both normal and guaranteed restore points. Normal restore points
eventually age out of the control file, even if not explicitly dropped where as
guaranteed restore points never age out of the control file. They remain until
they are explicitly dropped.
How
to Flashback database till Restore Points:
Login to RMAN and
bring the database to mount mode.
FLASHBACK DATABASE TO RESTORE POINT ‘before_upgrade’;
FLASHBACK DATABASE TO SCN 448619;
ALTER DATABASE OPEN;
Requirement
for using Guaranteed Restore Points:
The compatible restore point must be
set to 10.2 or greater.
The database must be running in archivelog mode.
A flashback recovery area must be configured.
If flashback database is not enabled, then the database must be mounted
(not open) when creating the first guaranteed restore point.
Checking
Existing Parameter before using Guaranteed Restore Points:
Check the existing database version.
You can use the guaranteed restore point facility since the oracle database
version 10.2 or higher.
SQL> Select * from V$Version;
Check the database mode. If it is not in archivelog then you need to
convert it into archive log mode. Check the separate post to convert archivelog
mode: Convert Noarchive to Archive
SQL> select log_mode from v$database;
SQL> show parameter db_recovery_file_dest;
The DB_FLASHBACK_RETENTION_TARGET parameter should be at least 1 day (24 hours). You need enough space for
flashback logs to keep 1 day archivelogs size.
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET;
If required set LOG_BUFFER to at least 8 MB to give flashback database more buffer space in memory
and setPARALLEL_EXECUTION_MESSAGE_SIZE to at least 8192.
This will improve the media recovery phase of any flashback database operation.
SQL> show parameter LOG_BUFFER;
SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE;
Enable Flashback database. If flashback database is not enabled, then
the database must be mounted (not open) when creating the first guaranteed
restore point.
SQL> select FLASHBACK_ON from v$database;
SQL> Alter database flashback on;
SQL> Select FLASHBACK_ON from v$database;
SQL> CREATE RESTORE POINT before_date_20121202 GUARANTEE FLASHBACK
DATABASE;
Now create or update some object for the test purpose before applying
the restore point.
SQL> Create table pay_payment_master_demo as select * from
pay_payment_master;
SQL> commit;
SQL> flashback database to restore point before_date_20121202;
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
Thus to use this future your database status should be at mount mode.
Shutdown immediate;
Startup mount;
SQL> flashback database to restore point before_date_20121202;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from pay_payment_master_demo;
ERROR at line 1:
ORA-00942: table or view does not exist
We can not see that table which we have been created before restore.
Thus our database revert back to restore point..
