Using Flashback Database and Restore Points

This chapter explains Flashback Database and restore points. It discusses configuring, monitoring, and maintaining these features as part of an overall data protection strategy.

This chapter contains the following topics:

Note: Detailed information on recovery scenarios that use Flashback Database and normal and guaranteed restore points can be found in Performing Flashback and Database Point-in-Time Recovery.

Overview of Flashback Database, Restore Points and Guaranteed Restore Points

Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.

These features provide a more efficient alternative to point-in-time recovery and does not require a backup of the database to be restored first. The effects are similar to database point-in-time recovery (DBPITR). Flashback Database and restore points are not only effective in traditional database recovery situations but can also be useful during database upgrades, application deployments and testing scenarios when test databases must be quickly created and re-created. Flashback Database also provides an efficient alternative to rebuilding a failed primary database after a Data Guard failover.

Restore points provide capabilities related to Flashback Database and other media recovery operations. In particular, a guaranteed restore point created at a system change number (SCN) ensures that you can use Flashback Database to rewind the database to this SCN. You can use restore points and Flashback Database independently or together.

Flashback Database is accessible through both RMAN and SQL as FLASHBACK DATABASE . You can use either language to quickly recover the database from logical data corruption or user errors. The following examples return the database to a specified SCN or restore point:

FLASHBACK DATABASE TO RESTORE POINT before_upgrade;
FLASHBACK DATABASE TO SCN 202381;

See Also: Oracle Data Guard Concepts and Administration

About Flashback Database

Flashback Database is similar to conventional point-in-time recovery in its effects. It enables you to return a database to its state at a time in the recent past. Flashback Database is much faster than point-in-time recovery because it does not require restoring data files from backup and requires applying fewer changes from the archived redo logs.

You can use Flashback Database to reverse most unwanted changes to a database if the data files are intact. You can return a database to its state in a previous incarnation, and undo the effects of an ALTER DATABASE OPEN RESETLOGS statement. “Rewinding a Database with Flashback Database” explains how to use the FLASHBACK DATABASE command to reverse database changes.

Flashback Database uses its own logging mechanism, creating flashback logs and storing them in the fast recovery area. You can only use Flashback Database if flashback logs are available. To take advantage of this feature, you must set up your database in advance to create flashback logs.

To enable Flashback Database, you configure a fast recovery area and set a flashback retention target. This retention target specifies how far back you can rewind a 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. These block images can later be reused to reconstruct the data file contents for any moment at which logs were captured.

When you use Flashback Database to rewind a database to a past target time, the command determines which blocks changed after the target time and restores them from the flashback logs. The database restores the version of each block that is immediately before the target time. The database then uses redo logs to reapply changes that were made after these blocks were written to the flashback logs.

Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. For example, if the flashback retention target is 1 week, then you must ensure that online and archived redo logs that contain all changes for the past week are accessible. In practice, redo logs are typically needed much longer than the flashback retention target to support point-in-time recovery.

About 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.

You cannot back up flashback logs to locations outside the fast recovery area. To increase the likelihood that enough flashback logs are retained to meet the flashback database window, you can increase the space in your fast recovery area (see “Table 5-3”).

If the fast recovery area is not large enough to hold the flashback logs and files such as archived redo logs and other backups needed for the retention policy, then the database may delete flashback logs from the earliest SCNs forward to make room for other files. Consequently, the flashback database window can be shorter than the flashback retention target, depending on the size of the fast recovery area, other backups that must be retained, and how much flashback logging data is needed. The flashback retention target is a target, not a guarantee that Flashback Database is available.

If you cannot use FLASHBACK DATABASE because the flashback database window is not long enough, then usually you can use database point-in-time recovery (DBPITR) to achieve a similar result. Guaranteed restore points are the only way to ensure that you can use Flashback Database to return to a specific point in time or guarantee the size of the flashback window.

Note: Some database operations, such as dropping a tablespace or shrinking a data file, cannot be reversed with Flashback Database. See “Limitations of Flashback Database” for details.

See Also:

Limitations of Flashback Database

Because Flashback Database works by undoing changes to the data files that exist at the moment when you run the command, it has certain limitations.

Following are the limitations of Flashback Database:

About Normal Restore Points

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

Thus, a restore point functions as a bookmark or alias for this SCN. Before performing any operation that you may have to reverse, you can create a normal restore point. The control file stores the name of the restore point and the SCN.

If you use flashback features or point-in-time recovery, then you can use the name of the restore point instead of a time or SCN. The following commands support this use of restore points:

Creating a normal restore point eliminates manually recording an SCN in advance or determine the correct SCN after the fact by using features such as Flashback Query.

Normal restore points are lightweight. The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

See Also: Oracle Database Development Guide to learn how to use Flashback Query

About Guaranteed Restore Points

Like a normal restore point, 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.

In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point. Except as noted, the information about where and how to use normal restore points applies to guaranteed restore points as well.

A guaranteed restore point ensures that you can 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, you can rewind the database to any SCN in the continuum rather than to a single SCN only.

Note: If flashback logging is disabled, then you cannot FLASHBACK DATABASE directly to SCNs between the guaranteed restore points and the current time. You can, however, flashback to the guaranteed restore point first and then recover to SCN’s between the guaranteed restore point and current time.

If the recovery area has enough disk space to store the needed logs, then you can use a guaranteed restore point to rewind a whole database to a known good state days or weeks ago. As with Flashback Database, even the effects of NOLOGGING operations like direct load inserts can be reversed with guaranteed restore points.

Note: Limitations that apply to Flashback Database also apply to guaranteed restore points. For example, shrinking a data file or dropping a tablespace can prevent flashing back the affected data files to the guaranteed restore point. See “Limitations of Flashback Database” for details. In addition, when there are guaranteed restore points in the database, the database compatibility parameter cannot be set to a higher database version. An attempt to do so results in an error. This restriction exists because flashback database is currently unable to reverse the effects of increasing the database version with the compatibility initialization parameter.

Guaranteed Restore Points versus Storage Snapshots

In practice, guaranteed restore points provide 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. Rather than creating a snapshot or duplicate database to test the operation, you can create a guaranteed restore point on a primary or physical standby database. You can then perform the risky operation with the certainty that the required flashback logs are retained.

Overview of Restore Points in a Multitenant Environment

You can create both normal and guaranteed restore points in a multitenant environment.

The basic concepts of restore points for databases are also applicable to restore points in a multitenant environment. You can create the following types of restore points in a multitenant environment:

See Also:

About CDB Restore Points

A CDB restore point serves as an alias for an SCN or a point in time in a multitenant container database (CDB). It can be a normal restore point or a guaranteed restore point.

CDB restore points are similar to restore points in a non-CDB, except that you need to connect to the root as a common user with the SYSDBA or SYSBACKUP privilege to create them. You can create CDB restore points starting with Oracle Database 12c Release 1 (12.1). CDB restore points are accessible to every pluggable database (PDB) within the CDB. However, a CDB restore point does not reflect the PDB sub-incarnation of any of its PDBs.

CDB restore points are useful in the following scenarios:

See Also:

About Restore Points in PDBs

You can create normal and guaranteed restore points in a pluggable database (PDB). PDB restore points are accessible only to the PDB in which they are defined.

PDB Restore Points

A PDB restore point is a bookmark to a point in time or an SCN in a particular pluggable database (PDB). It pertains only to the PDB for which it is created and is only usable for operations on that PDB. A PDB restore point represents the PDB sub-incarnation of the point in time at which it was created.

PDB restore points can be normal restore points or guaranteed restore points. A guaranteed PDB restore point guarantees that you can perform a flashback operation for the PDB to this restore point.

A PDB restore point can be used to perform Flashback Database operations or point-in-time recovery only for the PDB in which it was created.

Note: Creating a guaranteed PDB restore point requires careful consideration because such a restore point can prevent required flashback logs in the multitenant container database (CDB) from being reused. This can potentially impact CDB functioning because the fast recovery area could run out of space.

Clean PDB Restore Points

A clean PDB restore point is a PDB restore point that is created when the PDB is closed and when there are no outstanding transactions for that PDB. Clean PDB restore points are only applicable to CDBs that use shared undo.

Clean PDB restore points can be normal or guaranteed restore points. Use the CREATE CLEAN RESTORE POINT command to explicitly create a clean PDB restore point. For a CDB that uses shared undo, if a PDB is closed and it has no outstanding transactions, any PDB restore point created is marked as a clean PDB restore point.

If you anticipate that you may need to rewind a PDB to a particular point in time, for example, to a state just before an application upgrade, then it is recommended that you create a clean PDB guaranteed restore point.

For CDBs that use shared undo, a Flashback Database operation to a clean PDB restore point is faster than a Flashback Database operation to an SCN or other restore points that are not clean PDB restore points. This is because RMAN does not need to restore any backups while performing a flashback operation to a clean PDB restore point.

See Also:

About the Namespace for PDB Restore Points

Each pluggable database (PDB) has its own namespace for restore points. Therefore, you can define a PDB restore point with the same name in more than one PDB.

In a multitenant environment, when you use a restore point name in a PDB or for a PDB operation, the name is first interpreted as a PDB restore point for the concerned PDB. If a PDB restore point with the specified name is not found, then it is interpreted as a CDB restore point.

See Also: About Restore Points in PDBs

About Logging for Flashback Database and Guaranteed Restore Points

Logging for Flashback Database and guaranteed restore points involves capturing images of data file blocks before changes are applied. The FLASHBACK DATABASE command can use these images to return the data files to their previous state.

The chief differences between normal flashback logging and logging for guaranteed restore points are related to when blocks are logged and whether the logs can be deleted in response to space pressure in the fast recovery area. These differences affect space usage for logs and database performance.

Your recoverability goals partially determine whether to enable logging for flashback database, or use guaranteed restore points, or both. The implications in performance and in space usage for these features, separately and when used together, also factor into your decision.

Guaranteed Restore Points and Fast Recovery Area Space Usage

Certain rules govern the usage of space in the fast recovery area.

When you create a guaranteed restore point, with or without enabling full flashback database logging, you must monitor the space available in your fast recovery area. “Managing Space for Flashback Logs in the Fast Recovery Area” explains how to monitor fast recovery area disk space usage.

The following rules govern creating, retaining, overwriting and deleting of flashback logs in the fast recovery area:

Caution: If no files are eligible for deletion from the fast recovery area because of the requirements imposed by your retention policy and the guaranteed restore point, then the database performs as if it has encountered a disk full condition. In many circumstances, this causes your database to halt. See “Responding to a Full Fast Recovery Area”.

About Logging for Guaranteed Restore Points with Flashback Logging Disabled

Assume that you 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 when 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 or a subsequent flashback database operation has restored the original contents of the block. When you use Flashback Database to restore a database multiple times to the same restore point, it is common practise to drop and recreate the guaranteed restore point each time. This deletes the old flashback logs and also ensures that the space quota for the fast recovery area is not exceeded.

This method of logging has the following important consequences:

Assume that your primary goal is the ability to return your database to the time at which the guaranteed restore point was created. In this case, it is usually more efficient to turn off flashback logging and use only guaranteed restore points. For example, suppose that you are performing an application upgrade on a database host over a weekend. You could create a guaranteed restore point at the start of the upgrade. If the upgrade fails, then reverse the changes with the FLASHBACK DATABASE command.

About Logging for Flashback Database with Guaranteed Restore Points Defined

If you enable Flashback Database and define one or more guaranteed restore points, then the database performs normal flashback logging.

In this case, the recovery area retains the flashback logs required to flash back to any arbitrary time between the present and the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure if they are required to satisfy the guarantee.

Flashback logging causes some performance overhead. Depending upon the pattern of activity on your database, it can also cause significant space pressure in the fast recovery area. Thus, you should monitor space used in the fast recovery area.

Prerequisites for Flashback Database and Restore Points

To ensure successful operation of Flashback Database and guaranteed restore points, you must first set some key database options.

Flashback Database

Configure the following database settings before enabling Flashback Database:

Guaranteed Restore Points

To use guaranteed restore points, the database must satisfy the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater.

Note: There are no special prerequisites to set before using normal restore points.

Restore Points in PDBs

To create restore points in a pluggable database (PDB), the COMPATIBLE initialization parameter must be set to 12.2.0 or higher.

Using Normal and Guaranteed Restore Points

You can create, monitor, and drop both normal and guaranteed restore points.

This section contains the following topics:

Creating Normal and Guaranteed Restore Points in non-CDBs

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 to be a guaranteed restore point or a normal one (the default).

To create a restore point:

  1. Connect SQL*Plus to a target database.

    See Also: Oracle Database Administrator’s Guide for using SQL*Plus to connect to a database

  2. Ensure that the database is open or mounted. If the database is mounted, then it must have been shut down cleanly (unless it is a physical standby database).

  3. Run the CREATE RESTORE POINT statement.

    The following example shows how to create a normal restore point in SQL*Plus:

    SQL> CREATE RESTORE POINT before_upgrade;

    This example shows how to create a guaranteed restore point:

    SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

See Also:

Creating CDB Restore Points

The CREATE RESTORE POINT SQL command enables you to create normal and guaranteed restore points in a multitenant container database (CDB).

To create a CDB restore point:

  1. Ensure that the prerequisites described in Prerequisites for Flashback Database and Restore Points are satisfied.

  2. Connect SQL*Plus to the root as a common user with the SYSDBA or SYSBACKUP privilege.

  3. Ensure that the CDB is open or mounted. If the CDB is mounted, then it must have been shut down cleanly (unless it is a physical standby database).

  4. Run the CREATE RESTORE POINT statement to create a CDB restore point.

    The following command creates a normal CDB restore point:

    SQL> CREATE RESTORE POINT cdb_before_upgrade;

    The following command creates a guaranteed CDB restore point:

    SQL> CREATE RESTORE POINT cdb_grp_before_upgrade GUARANTEE FLASHBACK DATABASE;

See Also:

Creating PDB Restore Points

You use the CREATE RESTORE POINT SQL statement to create restore points in a pluggable database (PDB).

The restore point can be a normal PDB restore point, guaranteed PDB restore point, or clean PDB restore point. You can create PDB restore points either when connected to the PDB or to the root.

When a PDB uses shared undo, you can create a clean restore point only if the PDB does not have any outstanding transactions.

To create a PDB restore point when connected to the PDB:

  1. Ensure that the prerequisites described in Prerequisites for Flashback Database and Restore Points are met.

  2. Connect SQL*Plus to the PDB as a common user or local user with the SYSDBA or SYSBACKUP privilege.

  3. If you are creating a clean PDB restore point in a CDB that uses shared undo, then the PDB must be closed.

    The following command displays the state of the PDB:

    SQL> SELECT name, open_mode FROM V$PDBs;

    Use the following command to close the PDB:

    SQL> ALTER PLUGGABLE DATABASE CLOSE;

  4. If the multitenant container database (CDB) is in mounted state, then it must have been shut down consistently (unless it is a physical standby database).

  5. Set the current container to the PDB.

    The following command sets to current container to the PDB my_pdb:

    SQL> ALTER SESSION SET CONTAINER=my_pdb;

  6. Create a PDB restore point by using the CREATE RESTORE POINT command.

    The following command creates a normal PDB restore point:

    SQL> CREATE RESTORE POINT before_patching;

    The following command creates a guaranteed PDB restore point:

    SQL> CREATE RESTORE POINT before_upgrade GUARENTEE FLASHBACK DATABASE;

    The following command explicitly creates a clean PDB restore point. If a clean restore point cannot be created, then an error is returned.

    SQL> CREATE CLEAN RESTORE POINT before_patching;

To create a PDB restore point when connected to the CDB:

  1. Ensure that the prerequisites described in Prerequisites for Flashback Database and Restore Points are met.

  2. Connect SQL*Plus to the root as a common user with the SYSDBA or SYSBACKUP privilege.

  3. If you are creating a clean PDB restore point in a CDB that uses shared undo, then the PDB must be closed.

    The following command closes the PDB my_pdb:

    SQL> ALTER PLUGGABLE DATABASE my_pdb CLOSE;

  4. The CDB that contains the PDB can be open or mounted. If the CDB is mounted, then it must have been shut down consistently (unless it is a physical standby database).

    The following commands place the CDB in a mounted state:

    SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

  5. Set the current container to the root.

    SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

  6. Create a PDB restore point by using the CREATE RESTORE POINT command with the FOR PLUGGABLE DATABASE clause.

    The following command creates a normal PDB restore point:

    SQL> CREATE RESTORE POINT mypdb_before_patching FOR PLUGGABLE DATABASE my_pdb;

    The following command creates a guaranteed PDB restore point:

    SQL> CREATE RESTORE POINT mypdb_grp_before_upgrade FOR PLUGGABLE DATABASE my_pdb GUARANTEE FLASHBACK DATABASE;

    The following command explicitly creates a clean PDB restore point (when the PDB is closed and has no pending transactions). If the restore point cannot be created, then an error is displayed.

    SQL> CREATE CLEAN RESTORE POINT mypdb_crp_before_patching FOR PLUGGABLE DATABASE my_pdb;

See Also:

Listing Restore Points Using the LIST Command

Use the LIST command to list either a specific restore point or all restore points known to the RMAN repository.

The variations of the LIST command are as follows:

LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;

RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. The following example shows sample output:

RMAN> LIST RESTORE POINT ALL;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
341859           28-APR-15            28-APR-15 NORMAL_RS
343690           28-APR-15 GUARANTEED 28-APR-15 GUARANTEED_RS

Note: The LIST command does not display details such as the PDB incarnation number and whether a restore point is a PDB restore point. To view additional details about restore points in a multitenant environment, see Listing Restore Points Using the V$RESTORE_POINT View.

See Also: “Rewinding a Database with Flashback Database”

Listing Restore Points Using the V$RESTORE_POINT View

You can use the V$RESTORE_POINT control file view to obtain information about all currently-defined restore points (normal and guaranteed), including CDB restore points and PDB restore points.

The V$RESTORE_POINT view contains additional information about restore points in a multitenant environment that is not displayed by the LIST RESTORE POINT command. This includes details such as the incarnation of the pluggable database (PDB) in which a PDB restore point was created and whether a restore point is a PDB restore point or clean PDB restore point.

The following steps display information about PDB restore points for all PDBs in the CDB:

  1. Connect SQL*Plus to the target database. If the target database is a multitenant container database (CDB), then connect to the root.

    • To view restore points for all PDBs, you must connect to the root as a common use with the SYSDBA or SYSBACKUP privilege.

    • To view the restore points in a particular PDB, you can connect to that PDB as a common user or local user with the SYSDBA or SYSBACKUP privilege.

  2. Query the V$RESTORE_POINT view to display information about restore points.

Example 7-1 Displaying Restore Points in a Multitenant Environment

The following query displays details about all restore points in a multitenant environment (query output formatted to fit in the page):

SELECT name, guarantee_flashback_database, pdb_restore_point, clean_pdb_restore_point, pdb_incarnation#, storage_sizeFROM v$restore_point;
NAME                         GUARANTEE_  PDB_RESTORE_POINT  CLEAN_PDB_RESTORE_POINT  STORAGE_SIZE
--------                     ----------  ----------------   -----------------------  ------------
CDB_GRP_BEFORE_PATCH            YES         NO                  NO                     84586
PDB_GRP_BEFORE_UPGRADE_TEMP     YES         YES                 NO                      4562
CDB_RP1                         NO          NO                  NO                         0
PDB1_BEFORE_PATCHING            NO          YES                 NO                         0
MYPDB_CLEAN_GRP_BEFORE_UPGRADE  NO          YES                 YES                        0

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the approximate number of bytes of disk space in the fast recovery area that is tied up retaining logs required to guarantee FLASHBACK DATABASE to that restore point.

See Also:

Dropping Restore Points

When you are satisfied that you do not need an existing restore point, or when you want to create a restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement.

For example:

SQL> DROP RESTORE POINT before_app_upgrade;
Restore point dropped.

The same statement is used to drop both normal and guaranteed restore points.

Note:

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:

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.

See also: Oracle Database SQL Language Reference for reference information about the SQL DROP RESTORE POINT statement

Using Flashback Database

To use flashback logging for a target database, you must enable Flashback Database. Certain guidelines can be followed to ensure optimal performance of Flashback Database.

This section contains the following topics:

Enabling Flashback Database

Use the ALTER DATABASE command to enable Flashback Database

To enable flashback logging:

  1. Configure the recovery area as described in “Enabling the Fast Recovery Area”.

  2. 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. Other Oracle Real Application Clusters (Oracle RAC) instances can be in any mode.

  3. Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

    ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 days

    By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).

    Note: This setting must be persistent across database startup and shutdown.

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

    ALTER DATABASE FLASHBACK ON;

  5. Optionally, disable flashback logging for specific tablespaces.

    By default, flashback logs are generated for all permanent tablespaces. You can reduce overhead by disabling flashback logging for specific tablespaces as in the following example:

    ALTER TABLESPACE tbs_3 FLASHBACK OFF;

    You can re-enable flashback logging for a tablespace later with this command:

    ALTER TABLESPACE tbs_3 FLASHBACK ON;

    If you disable Flashback Database for a tablespace, then you must take its data files offline before running FLASHBACK DATABASE.

When you enable Flashback Database while the database is open, there is a very small chance the command may not be able to obtain the memory it needs. If the command fails because of that reason, retry the command after a while or retry after a shutdown and restart of the instance.

When you enable Flashback Database on a physical standby database, you can flash back a standby database. Flashback Database of standby databases has some applications in the Data Guard environment.

See Also: Oracle Data Guard Concepts and Administration for details about standby databases

Disabling Flashback Database Logging

Use the ALTER DATABASE command to disable Flashback Database.

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

ALTER DATABASE FLASHBACK OFF;

Configuring the Environment for Optimal Flashback Database Performance

Maintaining flashback logs imposes comparatively limited overhead on a 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 the following:

The overhead of logging for Flashback Database depends on the mixture of reads and writes in the database workload. When you have a write-intensive workload, the Flashback Database logging overhead is high since it must log all those database changes. Queries do not change data and thus do not contribute to logging activity for Flashback Database.

Monitoring the Effect of Flashback Database on Performance

Several data analysis methods are available to monitor the Flashback Database workload on your system.

Table 7-1 V$SYSSTAT Statistics

Column Name Column Meaning
Physical write I/O request The number of write operations issued for writing data blocks
Physical read I/O request The number of read operations issued for reading data blocks
Redo writes The number of write operations issued for writing to the redo log
Flashback log writes The 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

See Also:

About 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: