Sometimes it is more efficient to roll-back changes in a database rather than do a point-in-time recovery. Flashback Database can rewind the entire database and changes that occurred within a given time window. The effects are similar to database point-in-time recovery.
Normal Restore Points
You can create restore points to enable you to Flashback the database to a particular point in time or SCN. You can
think of it as a bookmark or alias that can be used with commands that recognize a RESTORE POINT clause as
shorthand for specifying an SCN. In essence, before you perform any operations that you may have to reverse you can
create a normal restore point. The name of the restore point and SCN are then recorded within the control file.
So basically creating a restore point eliminates the need to determine the current SCN before performing operations or finding the proper one after the fact. A label for an SCN or time. For commands that support an SCN or time, you can often specify a restore point. Normal restore points exist in the circular list and can be overwritten in the control file. However, if the restore point pertains to an archival backup, then it will be preserved in the recovery catalog. You can use RESTORE POINTS to specify the target SCN in the
following contexts:
- RECOVER DATABASE and FLASHBACK DATABASE commands within RMAN
- FLASHBACK TABLE in SQL*Plus
- The Fast Recovery Area must be configured (db_recovery_file_dest +db_recovery_file_dest_size).
- FLASHBACK_DATABASE must be enabled.
- Archivelog mode must be active.
- It is aligned with the parameter: db_flashback_retention_target.
- This means that logs older than the specified parameter will be deleted/overwritten, which can result in insufficient information to perform a flashback.
- Without a previous Flashback_Database operation, the Restore Point is useless (warning).
- It causes more I/O because different block versions are retained at different points in time.
- It can generate a larger number of Flashback logs.
Creating Normal Restore Points
Execute a statement such as the following, which creates a restore point named Restore_Point1:
SQL> create restore point Restore_Point1;
Restore point created.
How It Works
Restore points are named positions in time. While flashing a database back, you can specify a restore point as a destination instead of specifying an SCN or timestamp. However, flashing back to a restore point is possible only if the flashback logs are available for the time associated with the restore point. Because the restore points created by following this recipe are not guaranteed, they are known as unguaranteed or normal restore points. Normal restore points are the default type.
Guaranteed Restore Points (GRP)
A Guaranteed Restore Point can be used to perform a Flashback Database operation even if flashback logging is not
enabled for your database. It can be used to revert a whole database to a known good state days or weeks ago, as
long as there is enough disk space in the flash recovery area to store the needed logs. Even effects of NOLOGGING
operations like direct load inserts can be reversed using guaranteed restore points.
Limits to both types of Restore Points include shrinking a datafile or dropping a tablespace can prevent flashing back
the affected datafiles to the restore point. A restore point for which the database is guaranteed to retain the flashback logs for an Oracle Flashback Database
operation. Unlike a normal restore point, a guaranteed restore point does not age out of the control file and must be explicitly dropped. Guaranteed restore points utilize space in the flash recovery area, which must be defined.
- The Fast Recovery Area must be configured (db_recovery_file_dest + db_recovery_file_dest_size).
- Archivelog mode must be active.
- It is a fixed, never-expiring pointer in the database.
- It must be manually deleted, or else the Fast Recovery Area may become full.
- FLASHBACK_DATABASE does not need to be enabled.
- It is independent of the Retention_target parameter.
- This functionality is also used by the Autoupgrade utility.
- Only Flashback Database operations use Flashback logs.
- It generates fewer Flashback logs.
- It causes less I/O because only one copy of the block image before the change is stored in the Flashback log.
- The user must have the SYSDBA system privileges.
Creating Guaranteed Restore Points
Add the guarantee keyword to your create restore point command. For example:
SQL> create restore point Restore_Point2 guarantee flashback database;
Restore point created.
How It Works
When a guaranteed restore point is defined, the associated flashback logs are never deleted unless the
restore point is dropped. This will reduce the available space in the fast recovery area. A filled-up fast recovery area will cause the database instance to abort, with the failure in the recovery writer (RVWR) process. So, create guaranteed restore points only when you need to go back to them after some preestablished event to be completed in the near future, such as doing a test run of the application and then reverting to the starting data sets. After the test is completed, drop the guaranteed restore points.
Flashing Back to a Specific SCN
Flash the database back to your desired SCN. For instance, to flash back to SCN 1050951 ,
issue the following RMAN command:
RMAN> flashback database to scn 1050951;
Starting flashback at 03-APR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished flashback at 03-APR-24
This command flashed the database back
Flashing Back to a Specific Time
Use the following command to flash back to a time just two minutes ago. Since a day has
24 hours, with 60 minutes each, 2 minutes happen to be 2/60/24 of a day:
RMAN> flashback database to time ’sysdate-2/60/24’;
Starting flashback at 13-APR-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 70 is already on disk as
file +FRA/cdb1/archivelog/2024_04_13/thread_1_seq_70.279.789267613
archived log for thread 1 with sequence 71 is already on disk as
file +FRA/cdb1/archivelog/2024_04_13/thread_1_seq_71.280.789285641
media recovery complete, elapsed time: 00:00:40
Finished flashback at 13-APR-24
If you want to flashback to a specific time, not in reference to a time such as sysdate,
you can use the timestamp instead of a formula:
RMAN> flashback database to time "to_date(’08/03/2012 22:00:00’,’mm/dd/yyyyhh24:mi:ss’)";
This flashes the database back to that specific timestamp.
Please feel free to ask. thank you 🙂
Toufique Khan
No comments:
Post a Comment