Wednesday, November 19, 2008

Enabling Flashback Database

To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here.

1. Start SQL*Plus and ensure that the database is mounted, but not open. For example:

SQL> SELECT STATUS FROM V$INSTANCE;

2. By default the flashback retention target is set to one day (1440 minutes). If you wish, you can change the retention target. For example, if you want to retain enough flashback logs to be able to perform a 72 hour flashback, set the retention target to 4320 minutes (3 days x 24 hours/day x 60 minutes/hour):

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320;

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

SQL> ALTER DATABASE FLASHBACK ON;

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE test1 FLASHBACK OFF;


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

SQL> ALTER TABLESPACE test1 FLASHBACK ON;


Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command:

SQL> ALTER DATABASE FLASHBACK OFF;

You can enable Flashback Database not only on a primary database, but also on a standby database. Enabling Flashback Database on a standby database allows one to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment.
Post a Comment

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information"

Autoconfig post 12C upgrade from 11g failed with "ORA-01804: failure to initialize timezone information" Error DB Autoconfig ...