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
