8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

Flashback Database in Oracle Database 10g

The FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery, also known as a point-in-time recovery (PITR). In order to flashback the database you must have SYSDBA privilege and the flash recovery area must have been prepared in advance.

Related articles.

Enable Flashback Database

The FLASHBACK_ON column of the V$DATABASE view shows the current status of flashback database.

If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.

CONN / AS SYSDBA
ALTER SYSTEM SET log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Flashback must be enabled before any flashback operations are performed. In Oracle 11gR2, this process was simplified, allowing flashback database to be enabled/disabled without the need for a database restart. As a result, you could just issue the following.

CONN / AS SYSDBA
ALTER DATABASE FLASHBACK ON;
--ALTER DATABASE FLASHBACK OFF;

For 10g and 11gR1 releases, the database needs to be restarted to achieve this, as follows.

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;

Flashback Database

With flashback enabled the database can be switched back to a previous point-in-time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created.

Create a test table.

CONN test/test

CREATE TABLE flashback_database_test (
  id  NUMBER(10)
);

Flashback the database to 5 minutes previous, to before the test table was created.

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;

Check that the test table is gone.

CONN test/test

DESC flashback_database_test

Some other variations of the flashback database command include the following.

FLASHBACK DATABASE TO TIMESTAMP {my_date};
FLASHBACK DATABASE TO BEFORE TIMESTAMP {my_date};
FLASHBACK DATABASE TO SCN {my_scn};
FLASHBACK DATABASE TO BEFORE SCN {my_scn};

The window of time that is available for flashback is determined by the DB_FLASHBACK_RETENTION_TARGET parameter. The maximum flashback can be determined by querying the V$FLASHBACK_DATABASE_LOG view. It is only possible to flashback to a point in time after flashback was enabled on the database and since the last RESETLOGS command.

Restore Points

In addition to using SCNs and timestamps, we can create restore points and guaranteed restore points. A restore point is just a text alias representing a SCN. A guaranteed restore point means the database doesn't delete any flashback logs between that point and the current time. You should delete all unnecessary guaranteed restore points.

CREATE RESTORE POINT before_changes;
CREATE RESTORE POINT before_changes GUARANTEE FLASHBACK DATABASE;

Existing restore points can be displayed using the V$RESTORE_POINT view. To flashback to this restore point, we would issue the following command.

FLASHBACK DATABASE TO RESTORE POINT before_changes;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.