Feature enthusiasm: Oracle Flashback Database

Introduction

On a daily basis database changes to production database systems happen everywhere. In order to provide rollback options for failed changes people most often use the regular rman restore (point in time recovery) strategy. However, restoring databases (or parts of databases) can take long, especially as databases are getting larger and larger. As longer downtime obviously is not something people look for, Flashback database maybe something to think of. I am very enthusiastic about this underrated feature

Background

Since Oracle version 10.1, "Oracle Flashback Database" was introduced. In the documentation Oracle stated the following:

"This feature introduces the FLASHBACK DATABASE statement in SQL. It let you quickly bring your database to a prior point in time by undoing all the changes that have taken place since that time. This operation is fast, because you do not need to restore the backups. This in turn results in much less downtime following data corruption or human error."

So, Flashback database is simpler and faster to use then point in time recovery because it does not require restoring datafiles from backup and it requires applying fewer changes from the archived redo logs.

Although most of us already know about availability of this option, it isn't used as often as rollback strategy for database changes.

To be complete in this background details, Flashback technology brings more options to the table:
· Oracle Flashback Table, which returns one or more tables to their contents at a previous time;
· Oracle Flashback Drop, which undoes the effects of the DROP TABLE operation;
· Oracle Flashback Query, which is used to query the contents of the database at a past time;
· Oracle Flashback Version Query, which lets you view past states of data;
· Oracle Flashback Transaction Query, which is used to review transactions affecting a table over time.

This blog entry is about database Flashback only because it is that functionality that comes in very handy for undoing database changes or testing cycles.

Normal and guaranteed restore points

For Flashback Database there are two type of restore points:
1. Normal restore points
2. Guaranteed restore points

The documentation says "Restore points provide capabilities related to Flashback Database as well as other recovery operations. Guaranteed restore points, in particular, provide a complementary capability to Flashback Database, allowing you to select an SCN and enforce the requirement that Flashback Database be usable to that SCN, though not necessarily to SCNs between the guaranteed restore point and the present SCN"

So, in order to make sure changes can be rolled back you can imagine the guarantee option is the one we need. Because, if space in the Flash recovery area is low, then Flashback logs may be deleted to free space for files required by the configured retention policy and that is not what we want.

During space pressure, the Flashback logs are deleted under two conditions:
1. When an archivelog is deleted, it would also delete the Flashback logs that are dependent
on the archived log.
2. When quota is shrunk, then Flashback logs are deleted to reclaim diskspace. This is decreasing the DB_RECOVERY_FILE_DEST_SIZE to such a value that the Flashback log itself will run into a space pressure and than the old Flashback logs will get deleted.

So, using guaranteed restore points is the only way to ensure that you can use Flashback Database to return to a specific point in time and guarantee the size of the Flashback window. I.e. NO DELETION of Flashback logs.

In the end, when a database is 'Flashed back' to its state is at some past target time using Flashback Database, each block changed since that time is restored from the copy of the block in the Flashback logs most immediately prior to the desired target time. The redo log is then used to re-apply changes since the time that block was copied to the Flashback logs. In one of the examples below you will see that redo is applied to roll forward within the Flashback procedure.

Be careful of the following remark: "if no files are eligible for deletion from the Flash recovery area because of the requirements imposed by your retention policy and the guaranteed restore point, then the database behaves as if it has encountered a disk full condition. In many circumstances, this causes your database to halt".

You may get messages about a full Flash Recovery Area (FRA) in your alert.log, also you may see this kind of error messages when your diskgroup is filled up with Flashback logs:


ORA-19624: operation failed, retry possible
ORA-19504: failed to create file "+RK"
ORA-17502: ksfdcre:4 Failed to create file +RK
ORA-15041: diskgroup space exhausted

An extra advantage of guaranteed restore points is that each block that changes is logged only once. Hence space requirement and performance overhead is lesser with guaranteed restore points then with normal restore points.

How to setup and use Flashback

Using Flashback requires extra Flash Recovery Area diskspace as Flashback logs are not recycled when a Guaranteed Restore Point is in effect. Actually, you don't want any Flashback file from a restore point to be dropped until you are sure you don't need it again.

Remaining question is: How large should my FRA be sized extra in order to use Flashback ? Well, as a general rule of thumb the answer is: The total size of all the archive logs that would be created for the same change will suffice as required space for flashback logs. Please note that you need this size as additional space and this is just an estimate because how much space Flashback logs will take depends on your actions.

In one test I did, that only inserted data into a table 1.5GB of archives was created with only 85 megs of Flashback. Results of testing with updates and deletes will be added later.

In order to use Flashback, the two init.ora parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE should be set. The DB_RECOVERY_FILE_DEST_SIZE parameter specifies the maximum total bytes to be used for the FRA. DB_RECOVERY_FILE_DEST specifies the location of the FRA.

Please note, in case of RAC databases the value requires to be the same on all instances. Most of us already are using the DB_RECOVERY_FILE_DEST* parameters for their RMAN backups and archive logs. However, by default Flashback is disabled as you can see by querying v$database, so you still need to enable that.


SQL> SELECT flashback_on from v$database;
FLASHBACK_ON
------------
NO

So, no Flashback is enabled and no Flashback logs exist:


SQL> SELECT * FROM v$flashback_database_log;
no rows selected

If you try to create your first guaranteed restore point while the database is running (open) you receive the following error:


SQL> create restore point rk1 guarantee flashback database;
create restore point rk1 guarantee flashback database
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RK1'.
ORA-38787: Creating the first guaranteed restore point requires mount mode when flashback database is off.


So, before creating the guaranteed Flashback the database has to be brought into mount mode:


oracle@node01(xrku1):/var/opt/oracle> srvctl stop database -d xrku -o immediate
oracle@node01(xrku1):/var/opt/oracle> srvctl start instance -i xrku1 -d xrku -o mount

Now the restore point can be made and the database can be opened :


SQL> create restore point rk1 guarantee flashback database;
Restore point created.
SQL> alter database open;

You should now be able to see the restore point and identify the Flashback logfile:


SQL> SELECT flashback_on from v$database;
FLASHBACK_ON
------------
RESTORE POINT ONLY


SQL> SELECT NAME, SCN, TIME,
SQL> GUARANTEE_FLASHBACK_DATABASE
SQL> FROM V$RESTORE_POINT
SQL> WHERE GUARANTEE_FLASHBACK_DATABASE='YES';


NAME SCN TIME GUARANTEE_FL
---------- ---------- --------------------------------------------------------------------------- ------------
RK1 2066217 29-JUL-09 10.34.05.000000000 AM


SQL> select name from V$FLASHBACK_DATABASE_LOGFILE;
NAME
--------------------------------------------
+FLASH_RECOVERY_AREA/xrku/flashback/log_1.931.693484445

So, as an example, after creating or dropping some tables, in order to execute a Flashback database, the database has to be placed into mount mode again:


SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to restore point rk1;
Flashback complete.

This is what you will see in the alert.log of your instance:


Wed Jul 29 12:03:38 2009
flashback database to restore point rk1
Wed Jul 29 12:03:38 2009
Flashback Restore Start
Flashback Restore Complete
Completed: flashback database to restore point rk1

After Flashing back your database, open it with an "alter database open resetlogs" command, you should now have the situation as of before the creation or dropping of the tables.

Benefits, specials and things to be careful of with Flashback database

As said, Flashback database can be used as a strategy for point in time recovery (PITR) and as an alternative to the rman restore and recover. As a matter of fact, the Flashback log captures old versions of changed blocks. You could look at this as a continuous backup. Then, the logs are 'replayed' to restore the database to a certain time. Flashback 'restores' just changed blocks. The process responsible for writing the Flashback log is the rvwr process:


oracle@node01(xrku1):/ora/rku/admin/xrku1/bdump> ps -ef | grep oracle | grep rvwr
oracle 2677 1 0 12:30 ? 00:00:00 ora_rvwr_xrku1

You will only see this process if Flashback is enabled.

Flashback database also has limitations that you should be aware of before using it:

1. Flashback Database can only undo changes to a datafile made by an Oracle database. For example:

Users created will be Flashed back as these are database changes. sysdba users however, will not be removed from password file (v$pwfile_users). The same counts for the init.ora file where Flashback doesn't Flashback init.ora settings.

2. Flashback Database cannot be used to repair media failures, or to recover from accidental deletion of datafiles.

3. You cannot use Flashback Database to undo a shrink datafile operation, actually, shrinking a datafile or dropping a tablespace can prevent Flashing back the Database. For example:


SQL> create tablespace x datafile size 10M;
Tablespace created


SQL> select file_name from dba_data_files where tablespace_name = 'X';
FILE_NAME
---------------------------------------------------------
+DATA_AREA01/xrku/datafile/x.329.693491457
1 row selected.


SQL> alter database datafile '+DATA_AREA01/xrku/datafile/x.329.693491457' resize 5M;
Database altered.


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


SQL> startup mount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2085360 bytes
Variable Size 331353616 bytes
Database Buffers 197132288 bytes
Redo Buffers 6299648 bytes
Database mounted.


SQL> flashback database to restore point rk2;
flashback database to restore point rk2
*
ERROR at line 1:
ORA-38766: cannot flashback data file 8; file resized smaller
ORA-01110: data file 8: '+DATA_AREA01/xrku/datafile/x.329.693491457'

As another example, tablespaces created after the restore point will be dropped and files will be deleted upon Flashback execution (as expected):


Wed Jul 29 12:18:20 2009
flashback database to restore point rk1
Wed Jul 29 12:18:20 2009
Flashback Restore Start
Deleted Oracle managed file +DATA_AREA01/xrku/datafile/y.305.693490587
Flashback: deleted datafile #9 in tablespace #9 from control file.
Flashback: dropped tablespace #9: 'Y' from the control file.
Deleted Oracle managed file +DATA_AREA01/xrku/datafile/x.329.693490579
Flashback: deleted datafile #8 in tablespace #8 from control file.
Flashback: dropped tablespace #8: 'X' from the control file.
Flashback Restore Complete
Completed: flashback database to restore point rk1
Wed Jul 29 12:18:25 2009

Tablespaces created after restore point X and before restore point X+1 will be Flashed back and 'recovered' automatically, Think about this and realize what a great idea it is from Oracle to combine rolling forwards and rolling backwards:


flashback database to restore point rk2
Wed Jul 29 12:30:57 2009
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Media Recovery apply resetlogs offline range for datafile 1, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 2, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 3, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 4, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 5, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 6, incarnation : 0
Media Recovery apply resetlogs offline range for datafile 7, incarnation : 0
parallel recovery started with 3 processes
Wed Jul 29 12:30:57 2009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: +DATA_AREA01/xrku/onlinelog/group_1.354.689779921
Mem# 1: +DATA_AREA01/xrku/onlinelog/group_1.307.689779921
Successfully added datafile 8 to media recovery
Datafile #8: '+DATA_AREA01/xrku/datafile/x.329.693491457'
Wed Jul 29 12:30:57 2009
Incomplete Recovery applied until change 2066572
Flashback Media Recovery Complete
Completed: flashback database to restore point rk2

4. If the database control file is restored from backup or re-created, all accumulated Flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

5. When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.

6. For combinations Flashback and features like streams and dataguard with Flashback you should be aware of some extra constraints:

Having the ability to enable your source (primary) database with Flashback Guaranteed Restore Points (and actually using Flashback) requires the physical standby database to be in full Flashback mode. Flashing back the 'primary' to 'SCN' requires to Flashback the target (to SCN-20) so that the log sequence can catch up.

Note that enabling full Flashback on the target database requires extra disk space.
As a general guideline Oracle advices 2x the total size of archives that will be created as a requirement for flashback data. You have to test this for your environment.

Another thing to watch out for: "If the switchover involved a physical standby database, the primary and standby database roles are preserved during the Flashback operation. That is, the role in which the database is running does not change when the database is Flashed back to the target SCN or time to which you Flashed back the database." i.e. you have to do this your self.

A last note on the combination flashback and dataguard: be sure to use the command 'flashback standby database ...' on your standy database

For streams this can of course only be done when there is a 1:1 relation ship between source and target. Note that the with Streams source and target database have their own log thread. It's obviously more difficult to find an SCN in source and in target that are on exact the same time.

Disk usage

The information about required disk space is collected after some basic tests and is only applicable to Flashback database with a guaranteed restore points. Each action (delete, update, insert, drop) on a database generates an different amount of flashback logs. Also, as with normal actions, the amount of flashback data also depends on whether complete tables are updated or only specific columns. Depending on the action and the amount of data you may need more or less space, though, in general this list of values can be used as a guide.

For my test a table with a size of 1000M was created, without an index.
For insertion, deletion, updating (and dropping) of the complete table information was collected on the amount of flashback logs. For each action the most possible redo was generated:

storage_table.jpg

Summary

As discussed guaranteed restore points can come in very helpful to rollback database changes. It's a requirement to configure your Flash Recovery Area (FRA) if that's not already done. In order to use FRA DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE need to be set. Flashback database could also come in very handy in testing cycles where you want to re-test performance for example over and over with the same set of data.

In order to Flashback, bring your database down to mount state and Flashback to that restore point. Also, be sure to understand the mentioned limitations of this wonderful option so that you know in what state your database is after Flashing it back.

Also know how to handle Flashback in combination with solutions like Dataguard and Streams. Carefully test this before heading for production. Make yourself aware with the technology.

Be sure to reserve at least 1 x the amount you would need for archives to hold your Flashback logs, since Flashback logs requires storage also. Active monitoring off free FRA space is highly advised because a full FRA will halt your database. For this use the view v$flash_recovery_area_usage;

Not using the FRA for you archives (i.e. using log_archive_dest_*) is not recommended, but brings us an extra option which maybe interesting for you as a temporary extra safety machanism: Since the location for archived logs is not FRA in this situation, a separate (temporary) diskgroup could be made for the FRA that will only be used for flashback logs.

The advantage of this solution is that filling up the diskgroup (with Flashback logs) will not risk database availability for other databases. In order to create this temporary diskgroup of course free disks need to be available. It's however recommend by Oracle to put archives as well as flashback logs all in FRA.

References

464542.1 Can I Open A Physical Standby Db For R/W And Use Flashback to Put It Back?
728374.1 How To Flashback Primary Database In Standby Configuration
330535.1 Restore Points in Oracle10g Release2
565535.1 Flashback Database Best Practices & Performance
305648.1 What is a Flash Recovery Area and how to configure it
369759.1 FLASH RECOVERY AREA and FLASHBACK database
369755.1 Flashback Logs-Space management
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) - 7.5 Reversing Database Changes with Flashback Database

Rene Kundersma
Oracle Expert Services, The Netherlands

Comments:

Hi,

I need to check with some points, hope you can help me out. We have in our production environment some tables are in nologging mode. If we use guranteed flash back database feature does it affects on the datablocks at the time of recovery from that restore point? These are the steps we are thinking to perform.

shutdown the datbase
put it in the mount mode.
create guranteed flashback restore point.
(we are not enabling flashback logs on as its not necessary for guranteed flashback, we will do restore upto the restore point only not in between)
Open the database
Planned activity will be performed here, if something goes wrong flashback database to the restore point by putting in the mount mode which is followed by alter database resetlogs;

Do after restore no log tables would get currupted?

Another point is its in standby environment, do opening with resetlogs currupts the standby or not.

We have planned to differ it first before creating the flashback point.

Thanks in Advance.

Posted by guest on January 19, 2012 at 09:20 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Blog of Rene Kundersma, Consulting Member of Technical Staff at Oracle Development USA. I am designing and evaluating solutions and best practices around database MAA focused on Exadata. This involves HA, backup/recovery, migration and database consolidation and upgrades on Exadata. Opinions are my own and not necessarily those of Oracle Corporation. See http://www.oracle.com/technology/deploy/availability/htdocs/maa.htm.

Search

Archives
« July 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today