Enabling and Disabling Flashback in 19c Database

Enabling and Disabling Flashback in 19c Database

Flashback in Oracle 19c is a feature that allows you to view and recover data at a specific point in time, providing a way to undo changes made to the database. It enables you to perform various operations such as querying historical data, recovering dropped tables, and reverting database changes.

To enable Flashback in an Oracle 19c database, you need to ensure the following pre-requisites

• Database is running in ARCHIVELOG mode. Flashback requires the database to be in ARCHIVELOG mode to store and retrieve archived redo logs.

Error which we will get when enabling in non-archive mode.

ALTER DATABASE FLASHBACK ON

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38707: Media recovery is not enabled.

• Also sufficient undo tablespace should be available to support Flashback operations. Make sure that the undo tablespace has enough free space.
• Set DB parameters

Set the db_recovery_file_dest_size parameter

alter system set db_recovery_file_dest_size=20G scope=both sid='*';

Set db_recovery_file_dest parameter.

alter system set db_recovery_file_dest='+RECO' scope=both sid='*';

Once the prerequisites are met, We can enable Flashback by following these steps:

In 19c we don't need to bring down database to enable or disable

Enable Flashback

1) Connect to the Oracle database using SQL*Plus with a user with SYSDBA privileges.

2) Check the current Flashback status by running the following query:

SELECT flashback_on FROM v$database;

This will indicate whether Flashback is currently enabled or disabled.

3) To enable Flashback, execute the following command:

ALTER DATABASE FLASHBACK ON;

Enabling Flashback allows you to use Flashback features for data recovery and querying historical data.

To verify that Flashback is enabled, you can run the same query as in step 2 to check the updated status.

Disable Flashback

Execute Below command

ALTER DATABASE FLASHBACK OFF;

Flashback on a running transaction has the following implications:

• Any ongoing transaction will not be affected by enabling Flashback. The transaction will continue to execute normally. But make sure that the transaction is completed while doing flashback else command will be stuck.
• Flashback operates on committed data, so the ongoing transaction will not be rolled back or affected by Flashback queries or operations.

To enable Flashback in a Data Guard environment:

• Follow the same steps mentioned above on both the primary and standby databases. Ensure that both databases meet the prerequisites for Flashback.
• Doing Flashback on the primary database will automatically do it on the standby database.

To enable Flashback in a RAC (Real Application Clusters) environment:

• Connect to any one node of the RAC cluster and execute the same steps mentioned above to enable Flashback.
• Flashback operations can be performed on individual nodes using Flashback Query, Flashback Table, etc.
• Flashback Database can be performed on the entire RAC cluster to restore the entire cluster to a previous point in time.
• It's important to note that enabling Flashback consumes some disk space for storing flashback logs and requires proper management of undo tablespaces to ensure sufficient space availability.

If you like please follow and comment