Thursday, June 9, 2011

Restore Point Prerequisites change in 11.2

For 10.2 and 11.1 to enable guarantee restore points following were prerequisites, from Oracle Documentation (Database Backup and Recovery User Guide)

To support the use of guaranteed restore points, the database must satisfy the
following prerequisites:
The COMPATIBLE initialization parameter must be set to 10.2 or greater.

The database must be running in ARCHIVELOG mode. To rewind your database to a guaranteed restore point, the FLASHBACK DATABASE command requires the use of archived redo logs starting from around the time of the restore point.

A flash recovery area must be configured, Guaranteed restore points use a mechanism similar to flashback logging. As with flashback logging, Oracle Database must store the required logs in the flash recovery area.

If Flashback Database is not enabled, then the database must be mounted, not open, when creating the first guaranteed restore point (or if all previously created guaranteed restore points have been dropped).


If a restore point command is issued on 11.1 in the open mode or without first creating a restore point in the mount mode following error would be thrown
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL> create restore point first_restore guarantee flashback database;
create restore point first_restore guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'FIRST_RESTORE'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when
flashback database is off.
But the prerequisites has changed in 11.2 now the only pre-req is (as per Database Backup and Recovery User Guide) To use guaranteed restore points, the database must satisfy the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater and that's all.

Same steps above done on a 11.2 db
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> create restore point first_restore guarantee flashback database;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                        SCN TIME                           DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ------------------------------ --------------------- --- ------------
FIRST_RESTORE          17213755 09-JUN-11 12.05.26.000000000                       2 YES     15941632
On 11.2 it is possible to create restore points without first having the database in a mount mode (when flashback is off)

Update 17 September 2012
When a restore point is created with flashback is not enabled the recovery writer processes (RVWR) get started. Following could be seen on the alert log
Mon Sep 17 11:19:52 2012
Starting background process RVWR
Mon Sep 17 11:19:52 2012
RVWR started with pid=41, OS id=32173
Allocated 15937344 bytes in shared pool for flashback generation buffer
Created guaranteed restore point FIRST_RESTORE
and monitoring the oracle processes the newly started RVWR could be seen.
ps ax | grep ora_
...
32162 ?        Ss     0:00 ora_q000_ent11g2
32164 ?        Ss     0:00 ora_q001_ent11g2
32173 ?        Ss     0:00 ora_rvwr_ent11g2
Also the flashback_on will be set to "restore point only". Before restore point creation on 11gR2
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
After restore point creation on 11gR2
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY