Administration

Flashback Database

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Sunday, 05 July 2009 16:39
Last Updated on Monday, 12 March 2012 13:54
Published on Sunday, 05 July 2009 16:39
Written by Guy Lambregts
Hits: 5924

Flashback Database

 

10G R1

Traditional point in time recovery required restoring databasefiles and required applying redo data forward to a given point in time, to a given SCN. Flashback database requires flashback logs to be applied backwards, so Oracle is backing out the changes, one does not need to restore backupped database files anymore. As such the bigger the database is, the lesser the transactional activity is, the more interesting it becomes to use the flashback database feature in order to bring the database back in time. 

In order to be able to back out the changes Oracle 10G uses a new kind of log files : the Flashback Database Logs which are created in the flash recovery area ( db_recovery_file_dest ). These new type of log files are written towards the flash recovery area by a new background process RVWR which sequentially writes from the flashback buffer towards the recovery area.  The flashback buffer - part of the SGA - depends on the sizing of the log buffer, once more it is recommended for databases with high transactional activity to have an appropriate log buffer sizing. 

In order to have the flashback database feature enabled the database must be running in  archivelog mode.

By default the flashback database feature is not enabled, and in order to activate the flashback database feature the database must be mounted by an instance in exclusive mode and the database must have been shutdown cleanly before. (not require recovery)

 

SQL> select flashback_on from v$database;

FLASHBACK


NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 162887636 bytes
Database Buffers 41943040 bytes
Redo Buffers 4096000 bytes
Database mounted.


SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;

FLASHBACK


YES

SQL> alter database open;
Database altered.

 

Let' s check the flashback retention target in minutes = how far can we bring the database back in time ? The default is 1 day ( 24 * 60 )


SQL> show parameter db_flashback_retention_target

db_flashback_retention_target integer
1440

Let us alter this retention target. This retention target is a retention goal , however not a guaranteed interval. ( see also at the bottom end of this page where I write about the 10G R2 enhancements ) Flashback logs are generated in the flash recovery area, the physical destination is defined by the instance initialization parameter db_recovery_file_dest and the maximum size can be controlled with db_recovery_file_dest_size. In periods of peak load the flashback logs are reused and the real retention period might decrease, lower than the period defined by the db_recovery_file_dest_size. As well rman backups -if taken to disk- are by default written in the flash recovery area, as such an appropriate sizing of the db_recovery_file_dest_size need to be supervised.


SQL> alter system set db_flashback_retention_target=2880 scope=both;
System altered.

SQL> select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE OLDEST_FLASHBACK_SCN OLDEST_F


0 155648000 6048406 24/07/05

 

What is the actual SCN of the database ?

 

SQL> select current_scn from v$database;

CURRENT_SCN


6052503

SQL> connect testora10/testora10
Connected.
SQL> select count(*) from porder;

COUNT(*)


500000

A user is doing an unwanted operation, a mistake

SQL> truncate table porder;
Table truncated.

SQL> select count(*) from porder;
COUNT(*)


0

SQL> select current_scn from v$database;

CURRENT_SCN


6054696

 

 

In order to definitively reset the database to that SCN one need to open the database with resetlogs. The DBA can temporary take a look and open the flash backed db in read only mode, the flashback is not permanent



SQL> connect sys/coswin as sysdba
Connected.
SQL> flashback database to scn 6052503;
flashback database to scn 6052503

ERROR at line 1:
ORA-38757: Database must be mounted EXCLUSIVE and not open to FLASHBACK.

Let 's shutdown and startup mount the instance

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.


SQL> flashback database to scn 6052503;

Flashback complete.

Let' s take a look at the alertfile

Completed: ALTER DATABASE MOUNT

Mon Jul 25 17:59:13 2005
flashback database to scn 6052503
Mon Jul 25 17:59:14 2005
Starting Flashback Database to before SCN 6052504
Flashback Restore Start
Starting Flashback Restore of datafile 1.
Datafile 1: 'C:\ORACLE\ORADATA\ORA10\SYSTEM01.DBF'
Starting Flashback Restore of datafile 2.
Datafile 2: 'C:\ORACLE\ORADATA\ORA10\UNDOTBS01.DBF'
Starting Flashback Restore of datafile 3.
Datafile 3: 'C:\ORACLE\ORADATA\ORA10\SYSAUX01.DBF'
Starting Flashback Restore of datafile 4.
Datafile 4: 'C:\ORACLE\ORADATA\ORA10\USERS01.DBF'
Starting Flashback Restore of datafile 5.
Datafile 5: 'C:\ORACLE\ORADATA\ORA10\EXAMPLE01.DBF'
Starting Flashback Restore of datafile 7.
Datafile 7: 'C:\ORACLE\ORADATA\ORA10\DAMIEN.DBF'
Starting Flashback Restore of datafile 9.
Datafile 9: 'C:\ORACLE\ORADATA\ORA10\TEST.DBF'
Starting Flashback Restore of datafile 10.
Datafile 10: 'C:\ORACLE\ORADATA\ORA10\CW4ORAD1.DBF'
Starting Flashback Restore of datafile 11.
Datafile 11: 'C:\ORACLE\ORADATA\ORA10\CW4ORAI1.DBF'
Starting Flashback Restore of thread 1.
Mon Jul 25 17:59:30 2005
Completed Flashback Restore of thread 1.
Flashback Restore Complete
Flashback Media Recovery Start
Mon Jul 25 17:59:33 2005
Recovery of Online Redo Log: Thread 1 Group 2 Seq 760 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\ORA10\REDO02.LOG
Mon Jul 25 17:59:44 2005
Media Recovery Applied UNTIL CHANGE 6052507
Flashback Media Recovery Complete
Completed: flashback database to scn 6052503

Let ' s open the database in read only mode in order to have a look

SQL> alter database open read only;

Database altered.

SQL> select count(*) from testora10.porder;

COUNT(*)


500000


OK, let ' s go definitively go back to that scn number 

 

 

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.

 

Either we go definitively back with the command below

 

SQL> alter database open resetlogs;

Database altered.

Either we decide to go to the actual SCN, our database hasn' t been flashbacked definitively, we are recovering the flashback databases with the commands below

 

SQL> recover database;
Media recovery complete.


SQL> alter database open;
Database altered.

SQL> select count(*) from testora10.porder;

COUNT(*)


0

 

Hence we see the flashback database was recovered up to the current SCN. As long as we do not open the flashbacked database with resetlogs we can undo our flashback operation. This can be interesting, suppose we have truncated a table, we flashback our database, we open it in read only mode, we export the table, we recover the table up to the current SCN and we import the table again.

Flashback database cannot be used in order to flashback through

1. Recreation of controlfile

2. Drop tablespace

3. Shrunk datafile

4. Resetlogs

 

10G R2

The flashback database buffer nor the redo log buffer is user configurable anymore. The log buffer is automatically sized no matter what the user configuration is about and depends on the size of the SGA.

A very interesting new feature of 10G R2 are the restore points, You may want to read the Oracle Backup and recovery Guide section  Restore Points and Flashback Database.

There are 2 types of restore points.

 

1. normal restore points

2. guaranteed restore points

 

If you create a normal restore point with the create restore point command you get a user friendly mapping with a SCN. So instead of flashback to SCN you can flashback to your_restore_point. There is no guarantee however you actually can flashback to your restore point. As soon as the flash recovery area becomes under space pressure the oldest flashback logs are overwritten and the real flashback retention window is more limited than the one you defined with the flashback_retention_target initialiazation parameter.

 

If you create a guaranteed restore point with the create guaranteed restore point command your availability to flashback to that restore point is guaranteed. ( sounds totally logic if you ask me ) If the flash recovery area becomes under spaces and you have a guaranteed restore which avoids flashbacks logs are overwritten, the database instance(s) may halt, even an instance crash would be possible. So once you used a guaranteed restore point you have to minitor the flash recovery area.

 

Note during the gym posted here under I use

1. v$controlfile_record_section in order to retrieve restore point control file info

2. v$restore_point in order to retrieve restore point info

3.v$flash_recovery_area_usage in order to monitor the flash recovery area usage

 

 

SQL> drop restore point my_first_restore_point;

Restore point dropped.

SQL> create restore point my_first_restore_point;

Restore point created.

SQL> create restore point my_first_guaranteed_restore_point guarantee flashback database;

ERROR at line 1:
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.
SQL> connect sys/very_secret_password@tns as sysdba
Connected.

SQL> create restore point my_first_guaranteed_restore_point guarantee flashback database;

Restore point created.


SQL> desc v$restore_point;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------

 SCN                                                 NUMBER
 DATABASE_INCARNATION#               NUMBER
 GUARANTEE_FLASHBACK_DATABASE  VARCHAR2(3)
 STORAGE_SIZE                                 NUMBER
 TIME                                                 TIMESTAMP(9)
 NAME                                                VARCHAR2(128)


SQL> column time format a30
SQL> column name format a35


SQL> select scn,time,name,storage_size from v$restore_point;

1167487609 14/03/08 17:17:32,000000000   MY_FIRST_GUARANTEED_RESTORE_POINT     3981312

1167470667 14/03/08 17:12:44,000000000   

MY_FIRST_RESTORE_POINT                           0


SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES


CONTROLFILE                  0                         0                 0
ONLINELOG                     0                         0                 0
ARCHIVELOG                  ,9                         0               13
BACKUPPIECE             11,05                         0               12
IMAGECOPY                      0                         0                0
FLASHBACKLOG            2,86                        ,7              76

6 rows selected.

SQL> select type,record_size,records_total,records_used from v$controlfile_record_section where type like '%RESTORE POINT%';

TYPE                             RECORD_SIZE RECORDS_TOTAL RECORDS_USED


GUARANTEED RESTORE POINT                 212          2048            1
RESTORE POINT                                      212          2083            3


SQL> drop restore point my_first_restore_point;

Restore point dropped.

SQL> drop restore point my_first_guaranteed_restore_point;

Restore point dropped.

 

 

Note that you can even create guaranteed restore point even when flashback database is not enabled. This is of special interest for upgrading scenario' s. The database needs to be at mount stage in order to create a guaranteed restore point when flashback database has not been enabled.

When flashback database has been enabled for your database

 

1. Turn flashback database off. ( avoid a lot off flashback logs )

2. Create a guaranteed restore point

3. Start database upgrade ( and monitor v$flash_recovery_area_usage )

4. Turn flashback database on. ( avoid a lot off flashback logs )

5. Delete guaranteed restore point

 

or when flashback database has not been enabled for your database

 

1. Startup mount the database instance.

2. Create a guaranteed restore point

3. alter database open

4. Start database upgrade ( and monitor v$flash_recovery_area_usage )

5. Delete guaranteed restore point