11gR2Flashback databaseOracleRestore Point

Flashback : Guaranteed Restore Point

Oracle Flashback database and restore points enables us to rewind the database back in time to correct any problems caused by logical data corruption or user errors and it doesn’t require any restoration of backup. There are 2 types of restoration points —
1. Normal Restore Point –> assigns a restore point name to an SCN or specific point in time.The control file stores the name of the restore point and the SCN.
2. Guaranteed Restore Point –> Like Normal restore point, it also serves as an alias for an SCN in recovery operation. The only difference is that the guaranteed restore points never age out of the control file and must be explicitly dropped.

Logging for Guaranteed Restore Points with Flashback Logging Disabled
Assume that you create a guaranteed restore point when logging for Flashback Database is disabled. In this case, the first time a data file block is modified after the time of the guaranteed restore point, the database stores an image of the block before the modification in the flashback logs. Thus, the flashback logs preserve the contents of every changed data block at the time that the guaranteed restore point was created. Later modifications to the same block do not cause the contents to be logged again unless another guaranteed restore point was created after the block was last modified.

11:31:49 [email protected]:1> select name,database_role,open_mode,flashback_on,log_mode from v$database;
NAME      DATABASE_ROLE    OPEN_MODE            FLASHBACK_ON       LOG_MODE
--------- ---------------- -------------------- ------------------ ------------
ORCL      PRIMARY          READ WRITE           NO                 ARCHIVELOG
11:31:54 [email protected]:1> show parameter recovery
NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------------------------------------------------------------------------------------
db_recovery_file_dest          string      D:\oracle\flashback\orcl
db_recovery_file_dest_size     big integer 2G
recovery_parallelism           integer     0
11:31:56 [email protected]:1>  SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';
no rows selected

Create Guaranteed restore point —

11:31:58 [email protected]:1> CREATE RESTORE POINT test_anand GUARANTEE FLASHBACK DATABASE;
Restore point created.
11:32:41 [email protected]:1>
11:33:27 [email protected]:1>  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
------------ ---------- ----------------------------------- --------------------- --- ------------
TEST_ANAND      1297697 14-JAN-13 11.32.39.000000000 AM                         2 YES     52428800

Alert log shows —

Mon Jan 14 11:32:39 2013
Starting background process RVWR
Mon Jan 14 11:32:39 2013
RVWR started with pid=25, OS id=7032
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point TEST_ANAND

Lets create an user, table and do some dmls

11:35:36 [email protected]:1> create user anand identified by anand123 default tablespace users;
User created.
11:36:16 [email protected]:1>
11:36:17 [email protected]:1> grant connect,resource to anand;
Grant succeeded.
11:36:25 [email protected]:1>
11:36:42 [email protected]:1> grant dba to anand;
Grant succeeded.
11:36:48 [email protected]:1> conn anand/anand123
Connected.
11:36:52 [email protected]:1>
11:36:53 [email protected]:1> create table test as select * from all_objects;
Table created.
11:37:52 [email protected]:1> insert into test select * from test;
72583 rows created.
...................
....................
....................
11:39:01 [email protected]:1> insert into test select * from test;
1161328 rows created.
11:41:11 [email protected]:1> select * from V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ------------------- ---------------- -------------- ------------------------
             1297697 2013.01.14 11:32:41             1440      104857600                        0
11:41:30 [email protected]:1>
11:41:31 [email protected]:1>
11:41:31 [email protected]:1> select * from V$FLASHBACK_DATABASE_STAT;
BEGIN_TIME          END_TIME            FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2013.01.14 11:32:41 2013.01.14 11:41:50       12484608  199311360  278596608                        0
11:41:50 [email protected]:1>
11:42:35 [email protected]:1> select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME                                                               LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIME          TYPE
------------------------------------------------------------ ---------- ---------- ---------- ---------- ------------- ------------------- ---------
D:\ORACLE\FLASHBACK\ORCL\ORCL\FLASHBACK\O1_MF_8H77W05C_.FLB           1          1          1   52428800       1297697 2013.01.14 11:32:41 NORMAL
D:\ORACLE\FLASHBACK\ORCL\ORCL\FLASHBACK\O1_MF_8H77W2JM_.FLB           2          1          1   52428800             0                     RESERVED

V$FLASHBACK_DATABASE_LOG –> displays information about the flashback data. Use this view to help estimate the amount of flashback space required for the current workload.
V$FLASHBACK_DATABASE_STAT displays statistics for monitoring the I/O overhead of logging flashback data.

11:43:32 [email protected]:1> insert into test select * from test;
2322656 rows created.
11:43:47 [email protected]:1> commit;
Commit complete.
11:44:47 [email protected]:1> select * from V$FLASHBACK_DATABASE_STAT;
BEGIN_TIME          END_TIME            FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
------------------- ------------------- -------------- ---------- ---------- ------------------------
2013.01.14 11:32:41 2013.01.14 11:44:57       26501120  482787328  558669824                        0
11:44:57 [email protected]:1>

Now, lets try to flashback the database to restore point

11:52:24 [email protected]:1> flashback database to restore point test_anand;
flashback database to restore point test_anand
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

To flashback the database must be in mount mode. Shutdown the db and mount it. Before mounting the database, moved all the archive logs generated from creating the restore point till the shutdown and tried flashback.

11:54:44 [email protected]:1> flashback database to restore point test_anand;
flashback database to restore point test_anand
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1297662 to SCN 1297697
ORA-38761: redo log sequence 13 in thread 1, incarnation 2 could not be accessed
13:28:36 [email protected]:1> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
------------------ ------------------- --------------------- -------------------
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
           1309113 2013.01.14 11:52:35                     0
13:29:21 [email protected]:1>

Moved the archive log seq 13 (which was the seq# when guaranteed restore point was created)to the archive log destination and tried flashback

13:57:35 [email protected]:1>  flashback database to restore point test_anand;
Flashback complete.
13:58:02 [email protected]:1>

Alert log shows —

Mon Jan 14 13:57:54 2013
 flashback database to restore point test_anand
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
Flashback Media Recovery Log D:\ORACLE\ARCHIVE\ORCL\ORCL_0001_0000000013_0804355822
Mon Jan 14 13:58:02 2013
Incomplete Recovery applied until change 1297698 time 01/14/2013 11:32:41
Flashback Media Recovery Complete
Completed: flashback database to restore point test_anand
13:58:46 [email protected]:1> select * from V$FLASHBACK_DATABASE_LOG;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
             1297697 14-01-13 11:32:41             1440      104857600                   172032
13:59:07 [email protected]:1> select * from V$FLASHBACK_DATABASE_STAT;
BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
14-01-13 11:53:38 14-01-13 13:59:10          16384   25108480          0                        0
14:00:19 [email protected]:1> select CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME from v$datafile;
CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME
------------------ ------------------- --------------------- -------------------
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
           1297698 2013.01.14 11:32:41                     0
15:11:28 [email protected]:1> alter database open resetlogs;
Database altered.
15:11:58 [email protected]:1>
15:12:06 [email protected]:1> select username,account_status,default_tablespace,profile from dba_users where username='ANAND';
no rows selected

3 thoughts on “Flashback : Guaranteed Restore Point

  1. Hi Anand,
    You mean to say that with flashback logging disabled, the block changes after guaranteed restore point are made once only. For example if I try to insert and delete into single table, there wont be any capture for the data being deleted from the same table- Is that right?

Leave a Reply