Thursday, February 11, 2021

Oracle restore point without enabling flashback

 


We all might heard of guaranteed restore point . However very few  know that we can create guaranteed  restore point without enabling flashback .  

Fantastic feature called guaranteed restore point came with Oracle 10G R2. By this feature,
you can set a restore point which will remain until you drop it. The best part of this feature is,
it uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database.
The only thing you must do is setting db_file_recovery_dest parameter and create a guaranteed restore point (for the first restore point you must be in mount mode). Before image of changed blocks,
will be kept on flashback logs after you created the guaranteed restore point.
Flashback logs of guaranteed restore points are not big because of keeping before image of changed blocks for only 1 time after first change.

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 create your first guaranteed restore point like below. 

SQL>CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;


Below are high level steps  : before proceeding ensure recover dest size and retention is set 

1) Do few log switches in Primary and Make sure primary and Standby in sync
2) Disable Dataguard and log shipping .
3) Comment  arch redo maintenance in primary and  in standby
4)  create guaranteed restore point  on standby
- create restore point BEFORE_REL guarantee flashback database;
 
5) do a couple of log switches on primary
6)  create guaranteed restore point PRE_RELEASE on primary
7) continue to monitor the flashback space


No comments:

Post a Comment