The Flashback database allows you to maintain a backup version of changes in the database that enables you to recovery from user mistakes without the need for a full database recovery. In this section, we will describe the following:

1. Configuration of Flashback for existing database.
2. Configuration of Flashback to a new database.

Steps in implementing the Flashback for existing database

1. Defining the Initialization Parameters for Flashback area.

a. DB_RECOVERY_FILE_DEST_SIZE – defines the maximum size that the Flashback Area can grow to. NOTE: This parameter must be set before defining DB_RECOVERY_FILE_DEST.
b. DB_RECOVERY_FILE_DEST – this the directory storage area on disk that will hold all files related to backup and recovery, note this is for both flashback recovery and traditional RMAN recovery.

2. Specify initialization parameter by defining in PFILE or Altering the system: In this example we will alter the system.

SQL> alter system set db_recovery_file_dest_size=30G scope = both;
System altered.
SQL> alter system set db_recovery_file_dest=’/u03/oradata/orcl/flash_recovery_area’ scope = both;
System altered.
SQL>

3. Ensure that the parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are not set in the database. With Flashback you can only specify an LOG_ARCHIVE_DEST_n, by default LOG_ARCHIVE_DEST_10 will be set to your flash_recovery area.

SQL> show parameter log_archive

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=use_db_recovery_file_dest
log_archive_dest_10 string
log_archive_dest_2 string LOCATION=/u02/oradata/orcl/arc
hive
log_archive_dest_3 string LOCATION=/u05/oradata/orcl/arc
hive
log_archive_dest_4 string
log_archive_duplex_dest string

4. With the flash recovery area set we can startup our database in flashback mode. NOTE: The database must be mounted in exclusive mode, archiving enabled, and then flashback turned on before opening the database for use.

startup mount exclusive;
alter database archivelog;
alter database flashback on;
alter database open;

5. This completes the configuration of Flashback for existing database.

Configuration of Flashback on a new database

1. The following entries need to be placed in your pfile of new database.

DB_NAME=orcl
# set location for current datafiles:
DB_CREATE_FILE_DEST = ‘/u02/oradata/orcl’
# set location for control files and online redo logs:
DB_CREATE_ONLINE_LOG_DEST_1 = ‘/u03/oradata/orcl’
DB_CREATE_ONLINE_LOG_DEST_2 = ‘/u04/oradata/orcl’
# set flash recovery area location and size
DB_RECOVERY_FILE_DEST = ‘/u03/oradata/orcl/flash_recovery_area’
DB_RECOVERY_FILE_DEST_SIZE = 30G

2. Create your new database with DBCA or manually with scripts.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com