Friday, July 29, 2011

Flashback Architecture In Oracle

Oracle Flashback Technology  is  a  group  of  Oracle  Database  features  that  let  us  view  past states  of  database  objects or  to  return  database  objects  to  a  previous  state  without  using point-in-time media  recovery. Flashback Database  is  a  part  of  the  backup &  recovery  enhancements  in  Oracle  10g Database that are called Flashback Features .                           

Flashback Database enables us to wind our entire database backward in time, reversing the effects of unwanted database changes within a given time window. The effects are similar to database point-in-time recovery.  It is similar to conventional point in time recovery in its effects, allowing us to return a database to its state at a time in the recent past.

Flashback Database can be used to reverse most unwanted changes to a database, as long as the datafiles are intact.  Oracle Flashback Database lets us quickly recover an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.

What are the Benefits?
According to many studies and reports, Human Error accounts for 30-35% of data loss episodes. This makes Human Errors one of the biggest single causes of downtime.  With Flashback Database feature Oracle is trying to fight against user and operator errors in an extremely fast and effective way. 

In most cases, a disastrous logical failure caused by human error can be solved by performing a Database Point-in-Time Recovery (DBPITR). Before 10g the only way to do a DBPITR was incomplete media recovery. Media Recovery is a slow and time-consuming process that can take a lot of hours. On the other side, by using of Flashback Database a DBPITR can be done in an extremely fast way: 25 to 105 times faster than usual incomplete media recovery and in result it can minimize the downtime significantly.

Flashback Database provides :
  • Very effective way to recover from complex human errors.
  • Faster database point-in-time recovery.
  • Simplified management and administration .
  • Little performance overhead .
  • It provides a lot of benefits and almost no disadvantages.
The Flashback Database is not just our database “rewind” button. It is a “Time Machine” for our Database data that is one single command away from us.

The Flashback Database Architecture : 
Flashback Database uses its own type of log files, called Flashback Database Log Files. To support this mechanism, Oracle uses new background process called RVWR (Recovery Writer) and a new buffer in the SGA,  called Flashback Buffer.  The Oracle database periodically logs before images of data blocks in the flashback buffer. The flashback buffer records images of all changed data blocks in the database. This means that every time a data block in the database is altered, the database writes a before image of this block to the flashback buffer. This before image can be used to reconstruct a datafile to the current point of time.

The maximum allowed memory for the flashback buffer is 16 MB. We don’t have direct control on its size. The flashback buffer size depends on the size of the current redo log buffer that is controlled by Oracle. Starting at 10g R2, the log buffer size cannot be controlled manually by setting the initialization parameter LOG_BUFFER.

In 10G R2, Oracle combines fixed SGA area and redo buffer together. If there is a free space after Oracle puts the combined buffers into a granule, that space is added to the redo buffer. The sizing of the redo log buffer is fully controlled by Oracle. According to SGA and its atomic sizing by granules, Oracle will calculate automatically the size of the log buffer depending of the current granule size. For smaller SGA size and 4 MB granules, it is possible redo log buffer size + fixed SGA size to be multiple of the granule size. For SGAs bigger than 128 MB, the granule size is 16 MB. We can see current size of the redo log buffer, fixed SGA and granule by querying the V$SGAINFO view , and can query the V$SGASTAT view to display detailed information on the SGA and its structures.
To find current size of the flashback buffer, we can use the following query:
SQL> SELECT * FROM v$sgastat WHERE NAME = 'flashback generation buff';

There is no official information from Oracle that confirms the relation between 'flashback generation buff' structure in SGA and the real flashback buffer structure. This is only a suggestion. A similar message message is written to the alertSID.log file during opening of the database .   
Allocated 3981204 bytes in shared pool for flashback generation buffer Starting background process RVWR RVWR started with pid=16, OS id=5392 . 

RVWR writes periodically flashback buffer contents to flashback database logs. It is an asynchronous process and we don’t have control over it. All available sources are saying that RVWR writes periodically to flashback logs. The explanation for this behavior is that Oracle is trying to reduce the I/O and CPU overhead that can be an issue in many production environments.

Flashback log files can be created only under the Flash Recovery Area (that must be configured before enabling the Flashback Database functionality). RVWR creates flashback log files into a directory named “FLASHBACK” under FRA. The size of every generated flashback log file is again under Oracle’s control. According to current Oracle environment – during normal database activity flashback log files have size of 8200192 bytes. It is very close value to the current redo log buffer size. The size of a generated flashback log file can differs during shutdown and startup database activities. Flashback log file sizes can differ during high intensive write activity as well.

Flashback log files can be written only under FRA (Flash Recovery Area). FRA is closely related and is built on top of Oracle Managed Files (OMF). OMF is a service that automates naming, location, creation and deletion of database files. By using OMF and FRA, Oracle manages easily flashback log files. They are created with automatically generated names with extension .FLB. For instance, this is the name of one flashback log file: O1_MF_26ZYS69S_.FLB

By its nature flashback logs are similar to redo log files. LGWR writes contents of the redo log buffer to online redo log files, RVWR writes contents of the flashback buffer to flashback database log files. Redo log files contain all changes that are performed in the database, that data is needed in case of media or instance recovery. Flashback log files contain only changes that are needed in case of flashback operation. The main differences between redo log files and flashback log files are :
  • Flashback log files are never archived - they are reused in a circular manner.
  • Redo log files are used to forward changes in case of recovery while flashback log files are used to backward changes in case of flashback operation. 
  • Flashback log files can be compared with UNDO data (contained in UNDO tablespaces) as well. While UNDO data contains changes at the transaction level, flashback log files contain UNDO data at the data block level. While UNDO tablespace doesn’t record all operations performed on the database (for instance, DDL operations), flashback log files record that data as well. In few words, flashback log files contain the UNDO data for our database.
To  Summarize   :
  • UNDO data doesn’t contain all changes that are performed in the database while flashback logs contain all altered blocks in the database .
  • UNDO data is used to backward changes at the transaction level while flashback logs are used to backward changes at the database level .
We can query the V$FLASHBACK_DATABASE_LOGFILE to find detailed info about our flashback log files. Although this view is not documented it can be very useful to check and monitor generated flashback logs.

There is a new record section within the control file header that is named FLASHBACK LOGFILE RECORDS. It is similar to LOG FILE RECORDS section and contains info about the lowest and highest SCN contained in every particular flashback database log file . 
(size = 84, compat size = 84, section max = 2048, section in-use = 136,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 139, numrecs = 2048)
Thread 1 flashback log links: forward: 2 backward: 26
size: 1000 seq: 1 bsz: 8192 nab: 0x3e9 flg: 0x0 magic: 3 dup: 1
Low scn: 0x0000.f5c5a505 05/20/2006 21:30:04
High scn: 0x0000.f5c5b325 05/20/2006 22:00:38

What does a Flashback Database operation ?

When we perform a flashback operation, Oracle needs all flashback logs from now on to the desired time. They will be applied consecutively starting from the newest to the oldest.  For instance, if we want to flashback the database to SCN 4123376440, Oracle will read flashback logfile section in control file and will check for the availability of all needed flashback log files. The last needed flashback log should be this with Low scn and High scn values between the desired SCN 4123376440 .

In current environment this is the file with name: O1_MF_26YSTQ6S_.FLB and with values of:
Low SCN    : 4123374373
High SCN   : 4123376446

Note:  If we want to perform successfully a flashback operation we will always need to have available at least one archived (or online redo) log file. This is a particular file that contains redo log information about changes around the desired flashback point in time (SCN 4123376440). In this case, this is the archived redo log with name: ARC00097_0587681349.001 that has values of:
First change#: 4123361850
Next change#: 4123380675

The flashback operation will not succeed without this particular archived redo log. The reason for this : Flashback log files contain information about before-images of data blocks, related to some SCN (System Change Number). When we  perform flashback operation to SCN 4123376440, Oracle cannot apply all needed flashback logs and to complete successfully the operation because it applying before-images of data. Oracle needs to restore each data block copy (by applying flashback log files) to its state at a closest possible point in time before SCN 4123376440. This will guarantee that the subsequent “redo apply” operation will forward the database to SCN 4123376440 and the database will be in consistent state. After applying flashback logs, Oracle will perform a forward operation by applying all needed archive log files (in this case redo information from the file: ARC00097_0587681349.001) that will forward the database state to the desired SCN.

Oracle cannot start applying redo log files before to be sure that all data blocks are returned to their state before the desired point in time. So, if desired restore point of time is 10:00 AM and the oldest restored data block is from 09:47 AM then we will need all archived log files that contain redo data for the time interval between 09:47 AM and 10:00 AM. Without that redo data, the flashback operation cannot succeed.   When a database is restored to its state 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.

Note  :  Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. (In practice, however, redo logs are generally needed much longer than the flashback retention target to support point-in-time recovery.)

Flashback logs are not independent. They can be used only with the redo data that contains database changes around the desired SCN. This means that if we want to have working flashback window (and to be able to restore the database to any point in time within this window) we need to ensure the availability of redo logs as well.  If we are familiar with this information then we will be able to work in a better way with this feature and to ensure that it will help us  to perform faster recovery without unexpected problems.

Rules for Retention and Deletion of Flashback Logs : 
The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:
  • A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space in the flash recovery area.
  • A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
  • If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
  • If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
Note : Re-using the oldest flashback log shortens the flashback database window. If enough flashback logs are reused due to a lack of disk space, the flashback retention target may not be satisfied.

 Limitations  of  Flashback  Database :
  • Since Flashback Database works by undoing changes to the datafiles that exist at the moment that we run the command, it has the following limitations:Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidental deletion of datafiles.
  • we cannot use Flashback Database to undo a shrink datafile operation.
  • If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. We cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
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  we perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 , and we 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.

If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If we expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.

Finally few important point to be noted : 

  • The Flashback Database should be part of our Backup & Recovery Strategy but it not supersedes the normal physical backup & recovery strategy. It is only an additional protection of our database data.
  • The Flashback Database can be used to flashes back a database to its state to any point in time into the flashback window, only if all flashback logs and their related archived redo logs for the spanned time period are physically available and accessible.
  • Always ensure that archived redo logs covering the flashback window are available on either the tape or disk.
  • We cannot perform flashback database operation if we have media failure. In this case we must use the traditional database point-in-time media recovery method.
  • Always write down the current SCN or/and create a restore point (10g R2) before any significant change over our database: applying of patches, running of batch jobs that can can corrupt the data, etc. As we know: The most common cause for downtime is change.
  • Always write down the current SCN or/and create a restore point (10g R2) before to start a flashback operation .
  • Flashback database is the only one flashback operation that can be performed to undone result of a TRUNCATE command (FLASHBACK DROP, FLASHBACK TABLE, or FLASHBACK QUERY cannot be used for this).
  • Dropping of tablespace cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.
  • Shrink a datafile cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.
  • Resizing of datafile cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation. If we need to perform flashback operation in this time period, we must offline this datafile before performing of flashback operation.
  • Recreating or restoring of control file prevents using of Flashback Database before this point of time.
  • We can flashback database to a point in time before a RESETLOGS operation. This feature is available from 10g R2 because the flashback log files are not deleted after RESETLOGS operation. We cannot do this in 10g R1 because old flashback logs are deleted immediately after an RESETLOGS operation.
  • Don’t exclude the SYSTEM tablespace from flashback logging. Otherwise we will not be able to flashback the database.
  • The DB_FLASHBACK_RETENTION_TARGET parameter is a TARGET parameter. It doesn’t guarantee the flashback database window. Our proper configuration of the Flashback Database should guarantee it.
  • Monitor regularly the size of the FRA and generated flashback logs to ensure that there is no space pressure and the flashback log data is within the desired flashback window

For Flashback Configuration and examples .Click Here

Reference :

Enjoy   :-) 


Sarwar Hossain said...

Your blog was very informative and gives answers of a lot of questions that were in my mind.

Would you please clear the point---Necessity of atleast one archive log files.

Anonymous said...

Hello, this weekend is fastidious for me, as this occasion i am
reading this fantastic educational post here at my residence.

my web blog ... newest iphone games apps