Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 8 January 2013

Guaranteed Restore Points in Oracle

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           24-NOV-12            24-NOV-12 NORMAL_RS
448619           24-NOV-12 GUARANTEED 24-NOV-12 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 use V$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;
Check the separate post to set the recovery size and destination: Set Flash Recovery Destination
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 set PARALLEL_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.

1 comments:

  1. HI,

    I already dropped the guaranteed restore points, performed rman backup still I encountered this error:

    ORA-19815: WARNING: db_recovery_file_dest_size of 41943040000 bytes is 85.09% used, and has 6252279808 remaining bytes available.
    19815, 00000, "WARNING: %s of %s bytes is %s%% used, and has %s remaining bytes available."
    // *Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
    // *Action: One of the following:
    // 1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
    // 2. Backup files to tertiary device using RMAN.
    // 3. Consider changing RMAN retention policy.
    // 4. Consider changing RMAN archived log deletion policy.
    // 5. Delete files from recovery area using RMAN.

    ReplyDelete