Flashback database

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.
To ensure successful operation of Flashback Database and guaranteed restore points, you must first set several key database options.

Flashback Database

Configure the following database settings before enabling Flashback Database:

    Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.

    You 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 must be in a clustered file system or in ASM.

Prerequisites

You must have the SYSDBA system privilege.

A flash recovery area must have been prepared for the database.

The database must have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON statement unless you are flashing the database back to a guaranteed restore point.

The database must be mounted but not open.

Limitations&Notes of Flashback Database

Flashback Database can only undo 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.
You cannot use Flashback Database to undo a shrink data file operation. However, you can take the shrunken file offline, flash back the rest of the database, and then later restore and recover the shrunken data file.
You cannot use Flashback Database alone to retrieve a dropped data file. If you flash 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. You can only recover the dropped data file by using RMAN to fully restore and recover the data file.
If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
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.
If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation.
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.

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 when RVWR encounters I/O errors.
If no guaranteed restore points are defined, then the instance remains unaffected when RVWR encounters I/O errors. Note the following cases:
- 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, you can issue either a SHUTDOWN ABORT or an ALTER DATABASE FLASHBACK OFF command.

Enabling Flashback Database

The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

To enable flashback logging

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 RAC instances can be in any mode.

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
# By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).

Enable the Flashback Database feature for the whole database:

ALTER DATABASE FLASHBACK ON;

Monitor logging in the Flashback Database logs

  # Monitor logging in the Flashback Database logs:
SQL> select begin_time, flashback_data,
db_data, redo_data, ESTIMATED_FLASHBACK_SIZE
from   v$flashback_database_stat;

  # Monitor the Flashback Database retention target:
SQL> select * from   v$flashback_database_log;

Disabling Flashback Database Logging

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

ALTER DATABASE FLASHBACK OFF;

Creating Normal and Guaranteed Restore Points

To create a restore point:

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).
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;

List RESTORE POINT

LIST RESTORE POINT ALL;

To see a list of all currently defined restore points (normal and guaranteed), use the V$RESTORE_POINT control file view with the following query:

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

Drop RESTORE POINT

SQL> DROP RESTORE POINT before_app_upgrade;

Flashback Example

The following is the step-by-step process of Flashback of RAC database to a Restore Point.

Set the environment to correct instance

ps -ef | grep pmon
. oraenv

Get restore point name and make note of it from sqlplus

SQL> select name, host_name, status from gv$instance;
SQL> select log_mode,flashback_on from v$database;
SQL> select name,time from v$restore_point;

Stop the database and put it in mount state using srvctl

srvctl status database -d MYTESTDB
srvctl stop database -d MYTESTDB
srvctl status database -d MYTESTDB
srvctl start instance -d MYTESTDB -i MYTESTDB1 -o mount

Flashback to restore point using sqlplus

SQL> select instance_name,status from gv$instance;

SQL> flashback database to restore point REST_POINT;

SQL> alter database open resetlogs;

Database altered.

Stop and start the database and make sure all instances are up, using srvctl

srvctl stop database -d MYTESTDB
srvctl start database -d MYTESTDB
srvctl status database -d MYTESTDB

désert/Oracle/FRA