Flashback Database Best Practices & Performance (Doc ID 565535.1)

Last updated on JUNE 13, 2023

Applies to:

Purpose

Some application profiles may require special tuning or additional considerations when enabling flashback database or using guaranteed restore points.

This note describes

• flashback database benefits,
• performance observations,
• configuration best practices
• operational best practices,
• performance tuning for specific application workloads

Scope

This note is intended for database administrators who are using or considering using flashback database or guaranteed restore points.

Flashback Database Feature and its Benefits

Flashback database can be used to quickly flashback a primary or standby database to a point in time as opposed to the traditional point-in-time recovery. Flashback database feature can provide the following benefits

1. Point-in-time recovery – to quickly rewind the primary or standby database to point-in-time by using restore points or normal flashback database functionality. This functionality can be used for 1) repair from failed batch updates that has modified a significant portion of the database,  2) repair from a crashed database where the current online redo log is corrupted or irreparable or 3) repair from a planned maintenance event (e.g. application change that changes the database) that goes awry.
2. Data Guard Fast-start failover - to quickly reinstate the primary database as a standby database.
3. Manual primary database reinstate- if Data Guard is being used without the fast-start failover feature and a Data Guard failover is necessary, then flashback database can be used to manually reinstate the failed primary database.  Reinstatement of the failed primary database via flashback can complete in minutes regardless of database size compared to much more extensive work and time required for database reinstantiation performed across the network.  Using flashback database to reinstate a failed primary database after a failover is documented in the Data Guard Administration and Concepts guide.
4. Data Guard Snapshot Standby - A snapshot standby database is a fully updatable standby database.  It receives and archives redo data from a primary database but does not apply it.  An implicit guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.
5. To flashback a failed database upgrade - Additional to having a backup of the database, it is recommended to create a Flashback Guaranteed Restore Point (GRP).  As long as the database COMPATIBLE parameter is not changed after creating the GRP, the database can easily be flashed back after a (failed) upgrade.  The AutoUpgrade Tool will also offer an option to create a GRP before proceeding with the upgrade.  Flashing back to a GRP will back out all changes in the database made after the creation  of the GRP.
6. Rolling Database Upgrades with Physical Standby Databases using Transient Logical Standby or DBMS_ROLLING - As part of the database rolling upgrade when a physical standby is temporarily converted to a logical standby, a guaranteed restore point is taken on the primary database prior to the upgrade process.  This is so that the database can be flashed back and converted back to a physical standby after the upgrade.  For details on this see the MAA paper, Database Rolling Upgrade using Data Guard and Automated Database Upgrades using Oracle Active Data Guard and DBMS_ROLLING at our website www.oracle.com/goto/maa
7. RMAN - flashback database is integrated with Oracle Recovery Manager and used implicitly in recovery and explicitly if desired.  One example of RMAN implicit use of flashback database is when block media recovery is performed.
8. Restoring Test and Training Databases - Another common use of flashback database is for flashing back a test or training database to the same starting point after each test run or training session.
9. Flashback PDB - Starting in Oracle 12.2 and higher, flashback pluggable database (PDB) can rewind a PDB without affecting other PDBs. You can also create PDB restore points.

For more information about flashback database and restore points, refer to the Backup and Recovery User's Guide Using Flashback Database and Restore Points section.

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.