Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Restore Points and Guaranteed Restore Points:

Before using flashback database solution to revert any changes made on the database, you can give a name to the SCN or specific Time until which you want to restore. This name you give is called a restore point.
A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter. A guaranteed restore point enables you to flash the database back deterministically to the restore point regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space being available in the flash recovery area.
Both normal Restore points and guaranteed restore points are stored in the control file. The main difference is that normal restore points aged out from the control file where a guaranteed restore points never age out of the control file and must be manually dropped. In normal restore point Flashback logs may be deleted in response to space pressure.

A guaranteed restore point combined with the Flashback Database command rewinds the database to its state at the restore point SCN even if the flashback database features in not enabled. If you choose to disable the flashback logging, then you cannot use the flashback database to restore the database to any point from the guaranteed restore point. However, you can flashback to the guaranteed restore point and then use the archive logs to recover to SCNs from the guaranteed restore point until the time you want to recover to.

Guaranteed Restore Points with Flashback Logging Disabled:

When you create a guaranteed restore points and the flashback database is disabled then the first time a data file block is modified Oracle stores an image of the block before the modification in the flashback logs. Later modifications to the same block are not logged again in the flashback logs unless another guaranteed restore point was created after the block was last modified.
This solution can be used when you want to revert database changes made by the application (during an upgrade for example).
In the following example, we will show you how to use Guaranteed Restore Points with Flashback Logging Disabled to revert database changes in a RAC 11gR2 environment.

The example:

[oracle@orac2 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node orac1
Instance RAC2 is running on node orac2
[oracle@orac2 ~]$ 

[oracle@orac1 bin]$ ./crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       orac1
               ONLINE  ONLINE       orac2
ora.asm
               ONLINE  ONLINE       orac1                    Started
               ONLINE  ONLINE       orac2                    Started
ora.eons
               ONLINE  ONLINE       orac1
               ONLINE  ONLINE       orac2
ora.gsd
               OFFLINE OFFLINE      orac1
               OFFLINE OFFLINE      orac2
ora.net1.network
               ONLINE  ONLINE       orac1
               ONLINE  ONLINE       orac2
ora.ons
               ONLINE  ONLINE       orac1
               ONLINE  ONLINE       orac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       orac2
ora.orac1.vip
      1        ONLINE  ONLINE       orac1
ora.orac2.vip
      1        ONLINE  ONLINE       orac2
ora.rac.db
      1        ONLINE  ONLINE       orac1                    Open
      2        ONLINE  ONLINE       orac2                    Open
ora.rac.rac1_srvc.svc
      1        ONLINE  ONLINE       orac1
ora.rac.rac2_srvc.svc
      1        ONLINE  ONLINE       orac2
ora.rac.test_fan_srvc.svc
      1        ONLINE  ONLINE       orac1
ora.scan1.vip
      1        ONLINE  ONLINE       orac2
[oracle@orac1 bin]$ 

Prior to 11gR2 Guaranteed Restore Points must be defined in mount state. From 11gR2, you can create the restore points when the database is in open state.

SQL> select FLASHBACK_ON, LOG_MODE from v$database;

FLASHBACK_ON       LOG_MODE
------------------ ------------
NO                 ARCHIVELOG

SQL> 

SQL> CREATE RESTORE POINT before_app_upg GUARANTEE FLASHBACK DATABASE;

Restore point created.

You can query the GV$RESTORE_POINT to see the restore points.

[oracle@orac1 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 9 12:13:29 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set linesize 200
SQL> set pagesize 200
SQL> col name format a20
SQL> col time format a40
SQL> set trims on
SQL> desc GV$RESTORE_POINT
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 INST_ID                                                                                                                    NUMBER
 SCN                                                                                                                        NUMBER
 DATABASE_INCARNATION#                                                                                                      NUMBER
 GUARANTEE_FLASHBACK_DATABASE                                                                                               VARCHAR2(3)
 STORAGE_SIZE                                                                                                               NUMBER
 TIME                                                                                                                       TIMESTAMP(9)
 RESTORE_POINT_TIME                                                                                                         TIMESTAMP(9)
 PRESERVED                                                                                                                  VARCHAR2(3)
 NAME                                                                                                                       VARCHAR2(128)

SQL> select NAME,SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM GV$RESTORE_POINT;

NAME                        SCN TIME                                     DATABASE_INCARNATION# GUA STORAGE_SIZE
-------------------- ---------- ---------------------------------------- --------------------- --- ------------
BEFORE_APP_UPG          1600167 09-JAN-12 11.59.19.000000000 AM                              2 YES     16384000
BEFORE_APP_UPG          1600167 09-JAN-12 11.59.19.000000000 AM                              2 YES     16384000

SQL> 

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DATA
db_recovery_file_dest_size           big integer 4G
SQL>
SQL> conn wissem
Enter password:
Connected.
SQL> create table during_upg(P_DATE DATE);

Table created.

SQL> insert into during_upg values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select * from during_upg;

P_DATE
-------------------
09-01-2012 12:25:07

SQL> 

After defining the restore point, some flashback logs are created in the flash recovery area.

[oracle@orac1 bin]$ /u01/app/11.2.0/grid/bin/asmcmd
Connected to an idle instance.
ASMCMD> ls
ASMCMD-08102: no connection to ASM; command requires ASM to run
ASMCMD> exit
[oracle@orac1 ~]$ export PATH=/u01/app/11.2.0/grid/bin:$PATH;
[oracle@orac1 ~]$ export ORACLE_HOME=/u01/app/11.2.0/grid
[oracle@orac1 ~]$ export ORACLE_SID=+ASM1
[oracle@orac1 ~]$ asmcmd
ASMCMD> cd DATA/RAC/FLASHBACK
ASMCMD> ls
log_1.317.772113565
log_2.318.772113845
ASMCMD> ls -l
Type       Redund  Striped  Time             Sys  Name
FLASHBACK  UNPROT  COARSE   JAN 09 12:00:00  Y    log_1.317.772113565
FLASHBACK  UNPROT  COARSE   JAN 09 12:00:00  Y    log_2.318.772113845
ASMCMD> 

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .44                         0               1
REDO LOG                              0                         0               0
ARCHIVED LOG                       3.42                      2.42              28
BACKUP PIECE                      35.03                      4.66              13
IMAGE COPY                        34.33                     34.33               6
FLASHBACK LOG                       .39                         0               2
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

SQL> 

Now, we are going to perform a restore in mount state. wissem.during_upg table is created after the restore point,so we should get a table or view does not exist message.

[oracle@orac1 ~]$ srvctl stop database -d RAC
[oracle@orac1 ~]$ 

[oracle@orac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 9 13:03:00 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             369101212 bytes
Database Buffers           41943040 bytes
Redo Buffers                6103040 bytes
Database mounted.

SQL> flashback database to restore point before_app_upg;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> drop restore point before_app_upg;

Restore point dropped.

SQL> select * from wissem.during_upg;
select * from wissem.during_upg
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@orac1 ~]$ 

[oracle@orac1 ~]$ srvctl start database -d RAC
[oracle@orac1 ~]$ 

Conclusion:

Guaranteed Restore Points with Flashback Logging Disabled offer less time in the restore and an easy way to revert database changes to a restore point without any need to use RMAN restore / recover commands.