Oracle Flashback Technology (Part V)

Part V: Flashback Database Restore Points

Introducing Flashback Database, Restore Points, and Guaranteed Restore Points

Oracle Flashback requires a combination of factors and options, such as control of undo retention, Oracle Flashback retention, and relevant undo tablespace utilization, block change tracking, and supplemental log management in many scenarios. Besides, Oracle Flashback Database and restore points are indeed related data protection features that enable DBAs to reverse data back in time to fix any problems caused by logical data corruption or user errors within a designated time window, providing a better approach than conventional database point-in-time recovery (DBPITR).

The following examples return the database to a specified SCN or restore point:

FLASHBACK DATABASE TO RESTORE POINT 'before_OrclFullBkp';

FLASHBACK DATABASE TO SCN ;

Flashback Database

In general, Flashback Database is accessible through the RMAN command and SQL statement FLASHBACK DATABASE. The DBA can use either interface to promptly recover the database from logical data corruptions or user errors. In its outcome, Flashback Database is similar to conventional point-in-time recovery in its effects. Similarly, Flashback Database uses its own logging mechanism, creating flashback logs and storing them in the fast recovery area. The DBA can only use Flashback Database when flashback logs are available. To take advantage of this feature, the DBA must set up the database in advance to create flashback logs. Thus, to enable Flashback Database, he then configures a fast recovery area and set a flashback retention target. This retention target specifies how far back it is possible to reverse the database with Flashback Database. From that time onwards, at regular intervals, the database copies images of each altered block in every data file into the flashback logs such that these block images can later be reused to reconstruct the data file contents for any moment at which logs were captured.

The database restores the version of each block that is immediately before the target time, once Oracle has determined which blocks changed after the target time and restores them from the flashback logs. Likewise, Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. In real world practice, redo logs are usually needed much longer than the flashback retention target to support point-in-time recovery.

Flashback Database Window

The range of SCNs for which there is currently enough flashback log data to support the FLASHBACK DATABASE command is called the flashback database window. The flashback database window cannot extend further back than the earliest SCN in the available flashback logs.

In particular, some database operations, such as dropping a tablespace or shrinking a data file, cannot be reversed with Flashback Database. 

Limitations of Flashback Database

Based on the fact that Flashback Database works by reversing changes to the data files that exist at the moment when the DBA runs the command, it has the following limitations:

ñ Flashback Database can only reverse changes to a data file made by Oracle Database. It cannot be used to repair media failures, or to recover from accidental deletion of data files.

ñ The DBA cannot use Flashback Database to undo a shrink data file operation. Nevertheless, the DBA can take the shrunk file offline, flash back the rest of the database, and later restore and recover the shrunk data file.

ñ The DBA cannot use Flashback Database alone to retrieve a dropped data file. When the DBA flashes back a database to a time when a dropped data file existed in the database, only the data file entry is added to the control file. Thus, he must recover the dropped data file by using RMAN to fully restore and recover the data file.

ñ When the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. Consequently it is not possible to 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, e.g., an direct-path load, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. Then concurrency of Flashback and NOLOGGING operations should be avoided.

Types of Restore Points

Oracle Flashback technology can work with both normal and guaranteed restored points

Normal Restore Points

Creating a normal restore point assigns a restore point name to an SCN or specific point in time, as a bookmark.

The following commands support the use of normal restore points:

ñ The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN

ñ The FLASHBACK TABLE statement in SQL.

Guaranteed Restore Points

Similarly, a guaranteed restore point serves as an alias for an SCN in recovery operations. A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped. Thus, a guaranteed restore point is an alias for an SCN with any command that works with a normal restore point. A guaranteed restore point ensures that it is possible to use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point. Thus, if flashback logging is enabled, the DBA is able to rewind the database to any SCN in the continuum rather than to a single SCN only.

Guaranteed Restore Points versus Storage Snapshots

Today, guaranteed restore points offer a useful alternative to storage snapshots. Storage snapshots are often used to protect a database before risky operations such as large-scale database updates or application patches or upgrades, and to test a variety of daily business operations, in some cases, associated with Data Guard technology. Rather than creating a snapshot or duplicate database to test the operation, the DBA can create a guaranteed restore point on a primary or physical standby database. Then the DBA can perform the risky operation with the certainty that the required flashback logs are kept.

Guaranteed Restore Points and Fast Recovery Area Space Usage

In general, these are the set of governing the creation, retention, overwriting and deletion of flashback logs in the fast recovery area:

ñ If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.

ñ If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.

ñ If the database must create a flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.

ñ If the fast recovery area is full, then an archived redo log that is reclaimable according to the fast recovery area rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

According to fast recovery area rules, a file is reclaimable when one of the following criteria is true:

ñ The file is reported as obsolete and not needed by the flashback database. For example, the file is outside the DB_FLASHBACK_RETENTION_TARGET parameters. The file is backed up to tape.

ñ No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.

Logging for Guaranteed Restore Points Using Flashback Logging Disabled

Assume that a DBA can 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.

This method of logging has the following important consequences:

ñ FLASHBACK DATABASE can re-create the data file contents at the time of a guaranteed restore point by using the block images.

ñ For workloads that repeatedly modify the same data, disk space usage can be less than normal flashback logging. Less space is needed because each changed block is only logged once. Applications with low volume inserts may benefit from this disk space saving. This advantage is less likely for applications with high volume inserts or large batch inserts. The performance overhead of logging for a guaranteed restore point without flashback database logging enabled can also be lower.

Flashback Database Logging Using Explicit Guaranteed Restore Points

If the DBA enables Flashback Database and defines one or more guaranteed restore points, then the database performs normal flashback logging. Flashback logs are not deleted in response to space pressure if they are required to satisfy the guarantee. Indeed, Flashback logging can cause some performance overhead.

Prerequisites for Flashback Database and Guaranteed Restore Points

To ensure successful operation of Flashback Database and guaranteed restore points, the DBA must first set several key database options.

Flashback Database

Configure the following database settings before enabling Flashback Database:

ñ The database must be running in ARCHIVELOG mode, since archived redo logs are used in the Flashback Database operation.

ñ The DBA must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area.

ñ For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area has to be in a clustered file system or in ASM.

Guaranteed Restore Points

To attain successful guaranteed restore points usage, the database must meet the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater

Using Normal and Guaranteed Restore Points

I present next an array of various commands and monitoring capabilities, which can be used with normal and guaranteed restore points.

Creating Normal and Guaranteed Restore Points

In order to create normal or guaranteed restore points, use the CREATE RESTORE POINT SQL statement, providing a name for the restore point and specifying whether it is guaranteed or normal.

Sample SQL Commands

SQL> REM Creating a normal restore point

SQL> CREATE RESTORE POINT before_OrclFullBkp;

SQL> REM Creating a guarantee restore point

SQL> CREATE RESTORE POINT before_OrclFullBkp GUARANTEE FLASHBACK DATABASE;

SQL> Rem listing restore points

SQL> SELECT name,
            scn,

            time,

            database_incarnation#,

            guarantee_flashback_database,

            storage_size

       FROM v$restore_point;

SQL> SELECT * FROM rman.rc_restore_point;

Equivalently, the DBA can use the following RMAN sample commands.

RMAN> LIST RESTORE POINT before_OrclFullBkp;

RMAN> LIST RESTORE POINT ALL;

SQL> rem dropping a restore point

SQL> DROP RESTORE POINT before_OrclFullBkp;

Important Remarks

Normal restore points eventually age out of the control file, even if not explicitly dropped. The rules governing retention of restore points in the control file are:

ñ The most recent 2048 restore points are always kept in the control file, regardless of their age.

ñ Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined.

Normal restore points that do not meet either of these conditions may age out of the control file.

Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Using Flashback Database

I hereby highlight the basic commands for Flashback Database. It also lists guidelines to ensure optimal performance of Flashback Database.

Enabling Flashback Database

In order to enable Oracle Flashback Database, the DBA must follow these steps, namely:

1.   Ensure the database instance is open or mounted. If the instance is mounted, then the database must be shut down cleanly unless it is a physical standby database. I applicable, any other Oracle RAC instance can be in any mode.

2.   Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes, e.g., for five days, the command would be:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET= 7200;

By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day, i.e, 1440 minutes.

3.   Enable the Flashback Database feature for the whole database:

ALTER DATABASE FLASHBACK ON;

4.   Optionally, disable flashback logging for specific tablespaces.

By default, flashback logs are generated for all permanent tablespaces. The DBA can reduce some of the overhead by disabling flashback logging for specific tablespaces as shown below:

ALTER TABLESPACE tbs_adnencr FLASHBACK OFF;

Later the DBA can re-enable flashback logging for this tablespace:

ALTER TABLESPACE tbs_adnencr FLASHBACK ON;

Off course, when disabling Flashback Database for a tablespace, the DBA has to take its datafiles offline before executing the FLASHBACK DATABASE command.

Disabling Flashback Database Logging

On a database instances that is either in mount or open state, issue the following command:

ALTER DATABASE FLASHBACK OFF;

 Configurating a Better Environment for Optimal Flashback Database Performance

Maintaining flashback logs imposes comparatively limited overhead on an database instance. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.

To achieve good performance for large production databases with Flashback Database enabled, Oracle recommends for the DBA to perform the following actions:

ñ Use a fast file system for the fast recovery area, preferably without operating system file caching. ASM is highly recommended.

ñ Configure enough disk spindles for the file system that holds the fast recovery area.

ñ If the storage system used to hold the fast recovery area does not have nonvolatile RAM or is not SSS-based, then try to configure the file system on striped storage volumes using Oracle’s Stripe And Mirror Everything (SAME) approach to RAID technology, utilizing a small stripe size, e.g.,128KB.

ñ For large databases, set the initialization parameter LOG_BUFFER to at least 8 MB.

Monitoring the Outcome of Flashback Database in Terms of Performance

The DBA can use the Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning. AWR provides several data analysis methods for monitoring the Flashback Database workload. For instance, the DBA could compare AWR reports from before and after the Flashback Database was turned on.

Column Name	Column Description
Physical write I/O request	Number of write operations issued for writing data blocks
Physical read I/O request	Number of read operations issued for reading data blocks
Redo writes	Number of write operations issued for writing to the redo log
Flashback log writes	Number of write operations issued for writing to flashback logs
Flashback log write bytes	Total size in bytes of flashback database data written from this instance by the database.

In particular, the V$FLASHBACK_DATABASE_STAT view shows the bytes of flashback data logged

Because of the difference between sequential I/O and random I/O, a better indication of I/O overhead is the number of I/O operations issued for flashback logs. The V$SYSSTAT statistics shown below can inform the number of I/O operations that the instance has issued for various purposes.

Flashback Writer (RVWR) Behavior with I/O Errors

When flashback is enabled or when there are guaranteed restore points, the background process RVWR writes flashback data to flashback database logs in the fast recovery area. If RVWR encounters an I/O error, then the following behavior is expected:

ñ if there are any guaranteed restore points defined, then the instance fails once RVWR encounters I/O errors.

ñ If no guaranteed restore points are defined, then the instance remains unaffected when RVWR encounters I/O errors. The following cases are to be emphasized:

ñ On a primary database, Oracle Database automatically disables Flashback Database while the database is open. All existing transactions and queries proceed unaffected. This behavior is expected for both single-instance and Oracle RAC databases.

ñ On a physical or logical standby, RVWR appears to have stopped responding, retrying the I/O periodically. This may eventually cause the logical standby or the managed recovery of the physical standby to suspend. (Oracle Database does not cause the standby instance to fail because it does not want to cause the primary database to fail in maximum protection mode.) To resolve the issue, the DBA can issue either a SHUTDOWN ABORT or an ALTER DATABASE FLASHBACK OFF command.

Oracle Flashback Technology (Part IV)

PART IV:

Database Point-in-Time Recovery Using Oracle Flashback

With RMAN DBPITR the DBA has the ability to restore the database from backups before the target time for recovery, then uses incremental backups and redo to roll the database forward to a specific target time, but it is possible to recover to an SCN, time, log sequence number, or restore point, in particular.

Oracle recommends for DBAs to perform Flashback Database rather than database point-in-time recovery whenever possible. Media recovery with backups should be the last option in lieu of flashback technologies in order to undo the most recent changes. But I have personally tested that creating guaranteed restore points at the beginning of periodic full backups is a good policy to adopt.

Prerequisites to Database Point-in-Time Recovery

The prerequisites for database point-in-time recovery are as follows:

ñ The database must be running in ARCHIVELOG mode.

ñ All backups of all data files from before the target SCN for DBPITR must be available and so archived logs for the period between the SCN of the backups and the target SCN.

Performing Database Point-in-Time Recovery

The basic steps to DBPITR are now listed with the following assumptions, namely:

ñ The DBPITR is performed in the current database incarnation alone.

ñ The current control file is being used.

ñ The database is using the current server parameter file.

Otherwise, a need to set the database back to the appropriate incarnation, or restore the controlfile or spfile may be necessary.

Likewise, the DBA can avoid errors using the SET UNTIL command to set the target time at the beginning of the procedure, rather than specifying the UNTIL clause on the RESTORE and RECOVER commands individually.

To perform DBPITR:

1.   The DBA must determine the time, SCN, restore point, or log sequence that should end recovery.

Then, the DBA can use the Flashback Query features in order to identify when the logical corruption occurred and use the alert log to attempt to determine the time of the event from which to perform recover.

Likewise, the DBA can use a SQL query to determine the log sequence number that contains the target SCN and then recover using this log. For example, the DBA can run:

SELECT RECID,

       STAMP,

       THREAD#,

       SEQUENCE#,

       FIRST_CHANGE#

       FIRST_TIME,

       NEXT_CHANGE#

FROM   V$ARCHIVED_LOG

WHERE  RESETLOGS_CHANGE# =

       ( SELECT RESETLOGS_CHANGE#

         FROM   V$DATABASE_INCARNATION

         WHERE  UPPER(STATUS) = 'CURRENT');

RECID      STAMP      THREAD#    SEQUENCE#  FIRST_CHAN FIRST_TIM NEXT_CHANG

---------- ---------- ---------- ---------- ---------- --------- ----------

         1  347455711          1          1      31025 10-AUG-07      31031

         2  347455716          1          2      31031 10-AUG-07      31033

         3  347455720          1          3      31033 10-AUG-07      31036

For instance, the DBA finds out that a user accidentally dropped a tablespace at 10:02 PM., then he can recover to 10:00PM., just before the drop occurred. All changes to the database made after this time are automatically lost.

2.   When using a target time expression instead of a target SCN, the DBA makes sure the time format environment variables are appropriate before invoking RMAN.

The following are examples of specific Globalization Support settings useful to localization as well:

NLS_LANG = american_america.us7ascii

NLS_DATE_FORMAT=DD-MON-RRRR HH24:MI:SS

Exhibit 1. Setting the NLS_LANG environment variable.

3.   Then, the DBA connects to the target database using RMAN and the recovery catalog database, when in use. Then he brings the database to a mounted state:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

4.   Then DBA executes the following operations within a RUN block:

1.   Use SET UNTIL to specify the target time, restore point, SCN, or log sequence number for DBPITR. If specifying a time, it should use the date formats specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.

2.   When automatic channels are not configured, the DBA must manually allocate disk and tape channels required.

3.   Finally, the DBA should restore and recover the database.

The following example performs DBPITR on the target database until SCN 1000:

RUN

{

  SET UNTIL SCN 9717050810;   

  RESTORE DATABASE;

  RECOVER DATABASE;

}

As discussed in the following scenarios, the DBA may also use time expressions, restore points, or log sequence numbers to specify the SET UNTIL time:

SET UNTIL TIME 'Dec 17 2012 07:00:00';

SET UNTIL SEQUENCE 9717050716; 

SET UNTIL RESTORE POINT before_OrclFullBkp;

Indeed, when the operation completes without errors, then DBPITR has succeeded.

5.   Later, the DBA can open the database read-only using SQL*Plus and perform queries as needed to ensure that the effects of the logical corruption have been finally reversed.

So, the DBA should open the database in read-only mode as follows:

ALTER DATABASE OPEN READ ONLY;

6.   When satisfied with the results, the DBA can perform either of the following mutually exclusive actions:

1.   Open the database in read/write mode, thus abandoning all changes after the target SCN. In this case, the DBA must shut down the database, mount it, and then execute the following command:

ALTER DATABASE OPEN RESETLOGS;

2.   Export one or more objects from the database using Oracle Data Pump Export. Then the DBA can then recover the database to the current point in time and re-import the exported objects, thus allowing for these objects to return to their state before the undesired change without abandoning all other changes.

 Flashback and Database Point-in-Time Recovery Case Studies

Case I. Returning to an OPEN RESETLOGS Operation with Flashback Database

Rather than specifying a particular SCN or point in time for the FLASHBACK DATABASE command, however, the DBA can use FLASHBACK DATABASE TO BEFORE RESETLOGS.

To Reverse an OPEN RESETLOGS operation:

The DBA will perform the following actions, namely:

1.   Connect to the target database utilizing SQL*Plus and verify that the beginning of the flashback window is earlier than the time of the most recent OPEN RESETLOGS, by running the following queries:

SELECT RESETLOGS_CHANGE#

  FROM V$DATABASE;

SELECT OLDEST_FLASHBACK_SCN

  FROM V$FLASHBACK_DATABASE_LOG;

When V$DATABASE.RESETLOGS_CHANGE# is greater than V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN, then the DBA can use Flashback Database to reverse the OPEN RESETLOGS.

2.   The, the DBA will shut down the database, mount it, and recheck the flashback window. When the resetlogs SCN is still within the flashback window, then he will proceed to the next step.

3.   Then DBA will then connect to the target database utilizing RMAN and perform a flashback to the SCN just before the RESETLOGS, by using the following command:

FLASHBACK DATABASE TO BEFORE RESETLOGS;

As with other uses of FLASHBACK DATABASE, when the target SCN is before the beginning of the flashback database window, an error is returned and the database is not modified. Notwithstanding, if the command completes successfully, otherwise, then the database is left mounted and recovered to the most recent SCN before the OPENRESETLOGS in the previous incarnation.

4.   The DBA can now open the database read-only in SQL*Plus and perform queries as needed to ensure that the effects of the logical corruption have been reversed.

Open the database read-only as follows:

ALTER DATABASE OPEN READ ONLY;

5.   In order to make the database available for updates once more, the DBA will shut down the database, mount it, and then execute the following command:

ALTER DATABASE OPEN RESETLOGS;

Case II: Reversing an OPEN RESETLOGS Action on a Standby Databases utilizing Flashback Database

A DBA can use Flashback Database across OPEN RESETLOGS in order to perform the following functions in a Data Guard environment:

ñ Flashback to undo logical standby switchovers

In this scenario, the database reverts to its role (primary or standby) at the target time for the Flashback Database operation.

ñ Undo of a physical standby activation

The DBA may temporarily activate a physical standby database, use it for testing or reporting purposes, and then use Flashback Database to return it to its role as a physical standby. This technique can be use to implement Active Data Guard, among others, and in many instances to improve business processes, by repeatedly reutilizing a standby environment for testing, quality assurance or business continuity, in many self-improvement production support environments.

ñ Ongoing utilization of a standby database for testing

The use of Flashback Database means that there is no need to rely on the use of storage snapshots.

Rewinding the database to an SCN in an abandoned incarnation branch

In order to perform a reversal of a database to an SCN in a rather complex abandoned incarnation scenario, the DBA must perform the following tasks:

1.   Connect to the target database using SQL*Plus and verify that the flashback logs contain enough information to flash back to that particular SCN, by running the query:

SELECT OLDEST_FLASHBACK_SCN AS OLD_FBSC

FROM   V$FLASHBACK_DATABASE_LOG;

2.   Then, determine the target incarnation number for the Flashback Database operation, i.e., the incarnation key for the parent incarnation, by running the query:

SELECT PRIOR_INCARNATION#  AS PRIOR_ENCARNATION

FROM   V$DATABASE_INCARNATION

WHERE  UPPER(STATUS) = 'CURRENT';

3.   Later, use RMAN to connect to the target database.

4.   Then, shut down the database, and then mount it with the following options:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

5.   Afterwards, he can set the database incarnation to the parent incarnation, by running:

RESET DATABASE TO INCARNATION 1;

6.   Then, run the FLASHBACK DATABASE, specifying the target SCN.

FLASHBACK DATABASE TO SCN 9717050716;

7.   Can open the database in read-only mode utilizing SQL*Plus to perform queries as necessary to ensure that the logical corruption outcome has been reversed.

ALTER DATABASE OPEN READ ONLY;

8.   Finally, in order to make the database available for updates again, shut down the database, mount it, and then run the following command:

ALTER DATABASE OPEN RESETLOGS;