Monday, March 22, 2010

Guarantee restore ponit - new feature of 10g

2 Document Overview
This document explains the best practice to recover the database with minimum outage and data loss with high availability of the database through Guaranteed Restore Point.

2.1 Scope
The Flashback Recovery includes the following activities

1) Guaranteed Restore Point overview
2) Requirement for Guaranteed Restore Point
3) Creation of Guaranteed Restore Point
4) DML Tasks
5) Restoration

2.2 Objectives
The objectives of Guaranteed Restore Point is to

• Recovering the database with point-in-time recovery
• Minimal outage of Production & DR.
• Enabling the Flashback Mode is not necessary.

3 Guaranteed Restore Point
3.1 Guaranteed Restore Point overview

Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However, they also provide specific functionality related to the use of the Flashback Database feature.

Creating a guaranteed restore point at a particular SCN enforces the requirement that you can perform a Flashback Database operation to return your database to its state at that SCN, even if flashback logging is not enabled for your database. If flashback logging is enabled, creating a guaranteed restore point enforces the retention of flashback logs required for Flashback Database back to any point in time after the creation of the earliest guaranteed restore point.

A guaranteed restore point 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 flash recovery area to store the needed logs. As with Flashback Database, even the effects of NOLOGGING operations like direct load inserts can be reversed using guaranteed restore points.

Limitations that apply to guarantee restore points: For example, shrinking a datafile or dropping a tablespace can prevent flashing back the affected datafiles to the guaranteed restore point.
3.2 Requirement for Guaranteed Restore Point

 Database must be running in ARCHIVELOG mode.
 Flash recovery area should be configured
 ORACLE Version should be compatible with 10.2 or more.

3.3 Creation of Guaranteed Restore Point
 Creation of table for testing.

 For creating Guaranteed Restore Point, We should have SYSDBA privilege.

 To create first guaranteed restore point when flashback off, first we need to start the database in mount state after a consistent shut down otherwise we will get an error like below.

 After opening in mount state we can create first guaranteed restore point like below.

 Subsequent guaranteed restore points can be created when database is open. When you want to see the available guaranteed restore points you can use the select below

3.4 DML operations done for Test case

Some DML actions on the Created Table RSTR_EMP and create Restore points for that as below for test.

 Checking for number of restore points created

3.5 Restoration

 Set the database in mount stage.

 Restoring the database to the before_upgrade level.

 Checking for success of Restoration.

1 comment:

  1. This include new feature of 10g. All the features are explained well in this post. I am surely going to try these steps myself. I want to explore these features. This information is very useful. This is fantastic work. Keep it up. Thanks a lot.
    sap upgrade