Configuring Flashback Database through SQL

In order to configure the Flashback Database feature, we need to step through a series of operations, as follows:

1. Check that our database is in the archive log mode by either querying the  $DATABASE view, or by simply issuing the following command:

SQL> ARCHIVE LOG LIST

The preceding output reveals that the database is indeed running in the archive log mode. If it isn’t, we can turn archive logging on with the ALTER DATABASE statement shown in the following code, after first shutting down the database and starting it up initially in the mount mode:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN

2. Set up a flash recovery area, as we learn in the FLASH RECOVERY AREA CHAPTER

3. Set the DB_FLASHBACK_RETENTION_TARGET initialization parameter to specify how far back you can flashback your database. The following code sets the Flashback target to 1 day (1,440 minutes):

SQL> SHOW PARAMETER RETENTION

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

4. Shut down and restart the database in the mount exclusive mode. If we are using a single instance, a simple MOUNT command can be used:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

5. Enable the Flashback Database feature:

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

6. Use the ALTER DATABASE OPEN command to open the database and then confirm that the Flashback Database feature is enabled, by querying the V$DATABASE view:

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;