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 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL:1> CREATE RESTORE POINT test_anand GUARANTEE FLASHBACK DATABASE;

Restore point created.

11:32:41 SYS@ORCL:1>
11:33:27 SYS@ORCL: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 SYS@ORCL:1> create user anand identified by anand123 default tablespace users;

User created.

11:36:16 SYS@ORCL:1>
11:36:17 SYS@ORCL:1> grant connect,resource to anand;

Grant succeeded.

11:36:25 SYS@ORCL:1>
11:36:42 SYS@ORCL:1> grant dba to anand;

Grant succeeded.

11:36:48 SYS@ORCL:1> conn anand/anand123
Connected.
11:36:52 ANAND@ORCL:1>
11:36:53 ANAND@ORCL:1> create table test as select * from all_objects;

Table created.
11:37:52 ANAND@ORCL:1> insert into test select * from test;

72583 rows created.

...................
....................
....................

11:39:01 ANAND@ORCL:1> insert into test select * from test;

1161328 rows created.

11:41:11 ANAND@ORCL: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 ANAND@ORCL:1>
11:41:31 ANAND@ORCL:1>
11:41:31 ANAND@ORCL: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 ANAND@ORCL:1>
11:42:35 ANAND@ORCL: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 ANAND@ORCL:1> insert into test select * from test;

2322656 rows created.

11:43:47 ANAND@ORCL:1> commit;

Commit complete.

11:44:47 ANAND@ORCL: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 ANAND@ORCL:1>

Now, lets try to flashback the database to restore point

11:52:24 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL:1>  flashback database to restore point test_anand;

Flashback complete.

13:58:02 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL: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 SYS@ORCL:1> alter database open resetlogs;

Database altered.

15:11:58 SYS@ORCL:1>
15:12:06 SYS@ORCL:1> select username,account_status,default_tablespace,profile from dba_users where username='ANAND';

no rows selected
About these ads
  1. nice explanation…. Anand

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: