Flashback Database


Oracle Flashback Database can be used to undo the following:

  • Changes that have resulted in logical data corruption

  • Changes that are a result of user error

Unlike standard point-in-time recovery methods, the time required to invoke Flashback Database for a specific time in the past is directly proportional to the number of changes made, not on the size of the database.


Flashback Database Architecture

If you have experienced physical corruption or media failure in your database, then you must use traditional recovery methods.


Flashback Database utilizes a new type of log file called flashback logs. These files are created in the flash recovery area by the Oracle server and contain data block images for changed blocks, which are recorded at a timed interval. When invoked by the Flashback Database operation, these data block images are used to quickly back out any changes to the database that you specify with the FLASHBACK DATABASE command. You do not have to create or manage the flashback logs, nor do you have to explicitly size the flashback logs; you must, however, decide how much space is allocated to the flash recovery area, as well as manage it. Depending on the activity of your database, the flashback logs could accumulate very quickly within your flash recovery area; for this reason, having the right flash recovery area size is critical. Because the flashback logs must be created in the flash recovery area, you must have a flash recovery area initialized prior to running Flashback Database.

Flashback Database also utilizes a new background process called recover writer (RVWR). When Flashback Database is enabled, RVWR is responsible for writing flashback data to the flashback logs. RVWR is very similar to the log writer, LGWR, however RVWR only writes to the flashback logs.

Possible Flashback Error

Specifying DB_RECOVERY_FILE_DEST without also specifying the DB_RECOVERY_FILE_DEST_SIZE parameter is not allowed. You will receive an ORA-19801 and/or ORA-19802 error if you do. DB_RECOVERY_FILE_DEST_SIZE must be set before setting DB_RECOVERY_FILE_DEST.


Following are the initialization parameters that are needed to configure Flashback Database and the flash recovery area:

  • DB_RECOVERY_FILE_DEST (dynamic). This parameter specifies the physical location for the flash recovery area. The RVWR background process will create and write the flashback logs in this location. The specified location can be defined as a directory, file system, or ASM disk group. Flash recovery area cannot be stored on a raw file system. There is no default setting.

    Possible Error When Deleting Flash-back Logs

    Because the flashback logs are stored in the flash recovery area, you should use RMAN to back up and manage the retention policy for space requirements. If someone happened to manually remove a flashback log from the flash recovery area, the database will continue to function normally as long as the delete file was not the current flashback log. However once you shut down your database and try to start up the instance, you will receive ORA-38701 and ORA-27041 unable to open file errors. To fix the problem, clear out the flash recovery area by mounting the database, turn off flash recovery, and then turn on flash recovery. After you do this you can open your database again. If you remove the current flashback log or kill the RVWR process, however, the instance will crash and you will have to follow the aforementioned re-initialization steps.


  • DB_RECOVERY_FILE_DEST_SIZE (dynamic). This parameter specifies the amount of total space used in the flash recovery area. The size you set depends on the activity of your database. If you do not allocate enough space, you will limit your flashback time table. There is no default setting.

  • DB_FLASHBACK_RETENTION_TARGET (dynamic). This parameter specifies the limit (in minutes) of flashback data that Oracle should retain. This limit is contingent upon sufficient space within the flash recovery area. The default setting is 1,440 minutes (24 hours).

When running Oracle RAC, each instance must have all three parameters set with the same setting.

Configuring Flashback Database

A clean shutdown via SHUTDOWN NORMAL, IMMEDIATE, or trANSACTIONAL is mandatory before starting the initial configuration of Flashback Database


Before you can start enjoying the benefits of Flashback Database, you must make sure you follow the necessary steps to properly configure your instance to use flashback logging.

1.

Start your instance in MOUNT EXCLUSIVE mode. The database must be mounted but not open.

 SQL> startup mount; SQL> select status from v$instance; STATUS ------- MOUNTED 

2.

If it's not already enabled, enable archivelog mode:

 SQL> alter database archivelog; SQL> select log_mode from v$database; LOG_MODE ------- ARCHIVELOG 

3.

Configure the flash recovery area by setting the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters:

 SQL> alter system set db_recovery_file_dest_size = 4G; SQL> alter system set db_recovery_file_dest = 'C:\oracle\10.1.0\flash_recovery_area'; 

4.

Set the flashback retention target by using the DB_FLASHBACK_RETENTION_TARGET parameter:

 SQL> alter system set db_flashback_retention_target = 1440; 

5.

Enable flashback logging. (Note: The database must be in mount exclusive mode and in archivelog mode to issue this command; otherwise you will receive an error message.)

 SQL> alter database flashback on; 

6.

Verify your Flashback Database settings:

 SQL> show parameter recovery NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------------- db_recovery_file_dest                string      C:\oracle\10.1.0\flash_recovery_area db_recovery_file_dest_size           big integer 4G SQL> sho parameter db_flashback NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target        integer     1440 SQL> select flashback_on, current_scn from v$database; FLA CURRENT_SCN --- ----------- YES           0 

7.

Optionally, disable flashback logging for any nonessential tablespaces:

 SQL> alter tablespace stroupe flashback off; 

8.

Open the database:

 SQL> alter database open; 

Managing Flashback Database

Now that you have properly configured Flashback Database, let us focus on the available techniques and methods to manage and monitor its capabilities. Once Flashback Database is up and running, you will need to make sure that you have enough space to store the flashback logs based on your current workload as well as to monitor the overhead associated with the flashback logging.

When Flashback Database is enabled, Oracle starts tracking the amount of flashback logging that is occurring and stores it in the view V$FLASHBACK_DATABASE_LOG. DBAs will want to query this view throughout the day to monitor the flashback retention target and the flashback recovery size that was previously set.

The column values for V$FLASHBACK_DATABASE_LOG are as follows:

  • OLDEST_FLASHBACK_SCN. Indicates the lowest system change number (SCN) in the flashback data.

  • OLDEST_FLASHBACK_TIME. Indicates the time of the lowest SCN in the flashback data (oldest time you can flashback the database to, given the size of your flash recovery area).

  • RETENTION_TARGET. Indicates the target retention time (in minutes).

  • FLASHBACK_SIZE. Indicates the current size of the flashback data (in bytes).

  • ESTIMATED_FLASHBACK_SIZE. Indicates the estimated size of flashback data needed for the current target retention.

You can actually use V$FLASHBACK_DATABASE_LOG to estimate the amount of flashback space required based on your current workload:

[View full width]

SQL> select * from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE -------------------- ------------------ ---------------- -------------- ------------------------ 685607 21:33:42 10-MAR-05 1440 36175872 25165824

The estimate is based on the workload since the instance was started or during the most recent time interval equal to the flashback retention target, whichever is shorter. Depending on your result from V$FLASHBACK_DATABASE_LOG, you should adjust the database retention target as needed.

Unlike redo log files, flashback logs are not archived.


To monitor the overhead with flashback logging, Oracle stores all logging activity statistics in the V$FLASHBACK_DATABASE_STAT view. Using this view, you can review the estimated flashback size based on previous workloads (usually within the last 24 hours). Each row represents data based over a one-hour window. Along with flashback logging, V$FLASHBACK_DATABASE_STAT also displays redo and actual database data generated over the same time frame.

[View full width]

SQL> select * from v$flashback_database_stat BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE ------------------ ------------------ -------------- ---------- ---------- ------------------------ 22:56:35 11-MAR-05 23:20:22 11-MAR-05 3293184 4284416 2502144 0 21:56:26 11-MAR-05 22:56:35 11-MAR-05 4374528 7086080 2852352 25288704 20:56:16 11-MAR-05 21:56:26 11-MAR-05 4202496 7028736 2916352 23568384 19:56:06 11-MAR-05 20:56:16 11-MAR-05 4046848 6717440 2916352 21897216 18:55:56 11-MAR-05 19:56:06 11-MAR-05 4300800 6799360 2755584 20201472 17:55:46 11-MAR-05 18:55:56 11-MAR-05 4046848 6668288 2836480 18309120 16:55:39 11-MAR-05 17:55:46 11-MAR-05 1990656 4562944 1176064 16465920 ...

You can use V$FLASHBACK_DATABASE_STAT to determine how the rate of generation of flashback data has changed over a 24-hour period. Based on this information, you may need to adjust the retention time or the overall size of the flash recovery area.

Flashback Database in Action

The best way to truly understand Flashback Database is to see the technology in action. You can use any of the following tools to flashback the database to a point in time or a specific SCN:

  • FLASHBACK DATABASE SQL command

  • FLASHBACK DATABASE RMAN command

  • Enterprise Manager 10g Recovery wizard

As discussed previously, the database must be in a mounted state. When Flashback Database is invoked, the server uses the flashback logs to back out of all modifications during the specified time frame. In addition, redo log entries may be applied to ensure consistency. After the flashback operation has been completed, you can open the database in read-only mode to verify that you have corrected the problem. If not, you can continue the flashback operation or perform additional recovery to roll the database forward to a different point in time.

Following is a brief example of using the Flashback Database command via SQL:

1.

Verify your Flashback Database settings (if you haven't already, make sure you properly configure Flashback Database by following the steps listed in the section titled "Configuring Flashback Database"):

 SQL> select flashback_on, current_scn from v$database; FLA CURRENT_SCN --- ----------- YES           0 

2.

Capture the current SCN number from the database:

 SQL> select current_scn from v$database; CURRENT_SCN -----------      703327 

3.

We introduce logical errors to our example:

 SQL> select count(*) from emp;   COUNT(*) ----------        107 SQL> truncate table emp; Table truncated. SQL> select count(*) from emp;   COUNT(*) ----------          0 SQL> drop table dept; Table dropped. SQL> desc dept ERROR: ORA-04043: object dept does not exist 

4.

Perform a clean shutdown and mount the database:

 SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area  171966464 bytes Fixed Size                   787988 bytes Variable Size             145750508 bytes Database Buffers           25165824 bytes Redo Buffers                 262144 bytes Database mounted. 

5.

Issue the FLASHBACK DATABASE SQL command:

 SQL> flashback database to scn 703327; Flashback complete. 

You can also use the FLASHBACK DATABASE SQL command using a timestamp:

 SQL> flashback database to timestamp(SYSDATE  1/24); Flashback complete. 

6.

Open the database in read-only mode and verify that you corrected the problem:

 SQL> alter database open read only; Database altered. SQL> conn stroupe/gwu Connected. SQL> select count(*) from emp;   COUNT(*) ----------        107 SQL> select count(*) from dept;   COUNT(*) ----------         27 

7.

Perform a clean shutdown and open the database with the RESETLOGS command:

 SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  171966464 bytes Fixed Size                   787988 bytes Variable Size             145750508 bytes Database Buffers           25165824 bytes Redo Buffers                 262144 bytes Database mounted. SQL> alter database open resetlogs; Database altered. 

Along with the SQL, you can also use the FLASHBACK DATABASE command via Oracle RMAN. The syntax is very similar to what you can use with SQL. Using the V$FLASHBACK_DATABASE_LOG view, you can retrieve the OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME (same as before) and use the following RMAN commands to invoke Flashback Database:

SCN-based FLASHBACK DATABASE command:

 RMAN> FLASHBACK DATABASE TO SCN=703327; 

Time-based FLASHBACK DATABASE command:

 RMAN> FLASHBACK DATABASE TO TIME = TO_DATE('2005-03-12 01:00:00', 'YYYY-MM-DD HH24:MI:SS'); 

As noted previously, you can use the Flashback Database feature with SQL, RMAN, and Enterprise Manager via the Recovery wizard. Following is a small example of using Flashback Database via 10g OEMa.k.a., Database Control:

1.

From the Maintenance tab, click Perform Recovery.

2.

On the Perform Recovery Page: Type page, shown in Figure 17.1, choose Whole Database from the Object Type drop-down list and then choose your recovery type. If your database is open, you will be notified that the database needs to be in mount stage. Here, you will need to enter the O/S login credentials so OEM can restart your instance and place it in the mount stage.

Figure 17.1. The Enterprise Manager 10g Perform Recovery home page.


3.

Recovery wizard shuts down your database and restarts it in the mount stage. Click the Refresh button, as shown in Figure 17.2, when the database has been placed in mount stage.

Figure 17.2. Shutting down the database with the Enterprise Manager 10g Recovery wizard.


4.

After the database is placed in mount stage, the Recovery wizard will prompt you with several options for recovery, as shown in Figure 17.3. You can either recover by date, SCN, or sequence. Recovery wizard will also provide you with the oldest SCN and time to which you can flashback.

Figure 17.3. Specifying the point in time recovery options using the 10g Recovery wizard.


5.

After you have chosen the best recovery time, the Recovery wizard will prompt you for the recovery methodeither flashback or traditional point-in-time recovery (see Figure 17.4).

Figure 17.4. Specifying Flashback options using the 10g Recovery wizard.


6.

Review your selections for the recovery operation, as shown in Figure 17.5. Here, because OEM will use RMAN to perform the recovery, you can also choose to edit the RMAM script that OEM will use if you wish. Once you click Submit, the job will be processed via the scheduler. When complete, OEM will provide you a success message along with the output from the RMAN script that was used (see Figure 17.6).

Figure 17.5. 10g Recovery wizard Summary page.


Figure 17.6. Successful results using 10g Recovery wizard.


Using Flashback Database with Data Guard

You must have Flashback Database enabled on both the primary and the standby database to utilize its features in a Data Guard environment.


As mentioned in Chapter 16, "Utilizing 10g Physical and Logical Standby with Data Guard," Oracle 10g provides the ability to fully utilize the benefits of Flashback Database in your Data Guard environment. In earlier releases of Oracle, your old primary database was removed from the Data Guard environment after a failover operation occurred. The only way to reinstantiate the old primary database back into the Data Guard environment involved restoring a previous backup of the new primary and then resynchronizing any missing archive logs. This process was very cumbersome, especially if you had a large database. Now, with 10g, you can use the Flashback Database feature to convert the old primary database into a new standby database without performing this time-consuming reinstantiation process.

After a failover has occurred, you can perform the following steps to utilize the Flashback Database feature for your new standby database.

In a physical standby configuration, do the following:

1.

On the new primary database, capture the SCN at the time of the database role reversal with the following query:

 SQL> select standby_became_primary_scn from v$database; 

2.

After the old primary database site becomes available, start the old primary database in the mount stage.

3.

Use the FLASHBACK DATABASE command with the SCN number you determined in step 1 on the old primary database:

 SQL> flashback database to scn <scn>; 

4.

Disable the Flashback Database feature on the old primary database:

 SQL> alter database flashback database off; 

5.

Create a new standby control file on the old primary database and then issue the shutdown command:

 SQL> alter database create standby controlfile as 'C:\oracle\physical_std.ctl'; 

6.

Replace the old primary control file with the new standby control file from step 5 and start the old primary database in the mount stage.

7.

On the new standby (old primary), enable the Flashback Database feature:

 SQL> alter database flashback database on; 

8.

On the new primary database (old standby), enable log transport services to the new standby database (old primary) and manually archive a new log:

 SQL> alter system archive log current; 

9.

On the new standby database (old primary), start the managed recovery service:

 SQL> alter database recover managed standby database disconnect; 

In a logical standby configuration, do the following:

1.

On the new primary database, capture the SCN at the time of the database role reversal with the following query:

 SQL> select value from dba_logstdby_parameters where name = 'END_PRIMARY_SCN'; 

2.

After the old primary database site becomes available, start the old primary database in the mount stage.

3.

Use the FLASHBACK DATABASE command with the SCN number you determined in step 1 on the old primary database:

 SQL> flashback database to scn <scn>; 

4.

Use the security_clause (GUARD) to protect data in the database from being changed:

 SQL> alter database guard all; 

5.

Open the database with the RESETLOGS command:

 SQL> alter database open resetlogs; 

6.

On the new primary database, find the redo log file where the data dictionary dump begins:

 SQL> select name from v$archive_log where (sequence# =         (select max(sequence# from v$archived_log            where dictionary_begin= 'YES' and standby_dest = 'NO')); 

7.

Copy the redo log file identified in step 6 to the standby site and register it in the new standby database:

 SQL> alter database register logical logfile <logfile>; 

8.

Start the SQL Apply services on the new logical standby database:

 SQL> alter database start logical standby apply initial; 

Along with the failover operation, the Flashback Database feature can also be used when a RESETLOGS command has been issued in your Data Guard environment. Previously, anytime you issued a RESETLOGS command to your primary database, your standby database became invalidated and had to be re-created. Perform the following steps to avoid having to rebuild your standby database after a RESETLOGS command has been issued.

In a physical standby configuration, do the following:

1.

On the primary database, capture the SCN that was used before the RESETLOGS command was issued:

 SQL> select (resetlogs_change# - 2) from v$database; 

2.

On the standby database, capture the current SCN number:

 SQL> select current_scn from v$database; 

3.

Issue the FLASHBACK DATABASE command on the standby database to the SCN number you ascertained in step 1:

 SQL> flashback standby database to scn <before RESETLOGS scn>; 

4.

Restart the managed recovery services on the standby database:

 SQL> alter database recover managed standby database disconnect; 

In a logical standby configuration, do the following:

1.

On the primary database, capture the SCN that was used before the RESETLOGS command was issued:

SQL Apply After RESETLOGS

It is possible that the SQL Apply service does not halt when the RESETLOGS command is issued on the primary database. If the logical standby's SCN is far behind the primary's SCN, then the SQL Apply service can interpret the OPEN RESETLOGS command without stopping. Only use the following steps below if you have made sure that your SQL Apply process has halted on the logical standby.


 SQL> select (resetlogs_change# - 2) from v$database; 

2.

On the standby database, capture the current SCN number:

 SQL> select current_scn from v$database; 

3.

Issue the FLASHBACK DATABASE command on the standby database to the SCN number you ascertained in step 1:

 SQL> flashback standby database to scn <before RESETLOGS scn>; 

4.

Restart SQL Apply on the standby database:

 SQL> alter database start logical standby apply immediate; 

Troubleshooting Flashback Database

Although the Flashback Database feature does offer significant benefits to your 10g environment, it is important to understand the limitations that exist with Oracle 10g Release 1 as well as some common errors you may receive when using its syntax.

Currently in Oracle 10g Release 1, you cannot use the Flashback Database feature if:

  • Any condition exists where you must use media recovery.

  • The control file has been restored or re-created.

  • Any data file has been dropped or shrunk.

  • A tablespace has been dropped.

Flashback Database supports all datatypes in the database, but all data files must remain online during the time in which Flashback Database is enabled. Dropped data files will not be recovered with Flashback Database. After the flashback operation is completed, you can restore and recover any dropped data files to the same point in time as the rest of your database if needed. If you have a data file that has been resized (shrunk) during the flashback span, you should take the file offline before issuing the FLASHBACK DATABASE command. After the flashback operation is completed, you can restore and recover any resized data files to the same point in time if needed.

Flashback Database Using Automatic Extension

You can use the Flashback Database feature with data files that you have configured for automatic extension. Only files that have been shrunk, not expanded, lose support for Flashback Database.


It is possible that you could receive failures during your Flashback Database operation. In normal operation, there are two types of errors that can occur when there is not enough flashback data to process the command:

  • ORA-38729: Not enough flashback database log data to do FLASHBACK . This error indicates that your time or SCN did not have enough flashback data to process the command. You must change the time or SCN to an appropriate time that would remain valid in your flash recovery area.

  • ORA-38753: Cannot flashback data file #; no flashback log data . This error indicates that these specific files did not have enough flashback data to process the command. You can take these files offline and continue the flashback operation if you like. If you do, the offline files can be recovered to the same point in time by using traditional point-in-time recovery methods.

If you do receive any of these errors, you should consider increasing your flashback retention period to a higher value. Because these errors are happening while your database is mounted, you can simply reissue the FLASHBACK DATABASE command after you take appropriate measures if needed. If you even experience an instance crash during a Flashback Database operation, simply restart and mount your database and reissue the FLASHBACK DATABASE command to resume the recovery operation.



    Oracle Database 10g Insider Solutions
    SUSE LINUX Enterprise Server 9 Administrators Handbook
    ISBN: 672327910
    EAN: 2147483647
    Year: 2006
    Pages: 214

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net