The Flashback Database feature allows you to perform an incomplete recovery back to a point in time in the past. Flashback Database uses information stored in flashback logs; it doesn’t rely on restoring database files (as do cold backup, hot backup, and RMAN).
Tip Flashback Database isn’t a substitute for a backup of your database. If you experience a media failure with a data file, you can’t use Flashback Database to flash back to before the failure. If a data file is damaged, you have to restore and recover, using a physical backup (hot, cold, or RMAN).
The Flashback Database feature may be desirable in situations in which you want to consistently reset your database back to a point in time in the past. For instance, you may periodically want to set a test or training database back to a known baseline. Or, you may be upgrading an application and, before making large-scale changes to the application database objects, mark the starting point. After the upgrade, if things don’t go well, you want the ability to quickly reset the database back to the point in time before the upgrade took place.
There are several prerequisites for Flashback Database:
- The database must be in archivelog mode.
- You must be using an FRA.
- The Flashback Database feature must be enabled.
See Chapter 2 for details on enabling archivelog mode and/or enabling an FRA. You can verify the status of these features using the following SQL*Plus statements:
SQL> archive log list;
SQL> show parameter db_recovery_file_dest;
To enable the Flashback Database feature, alter your database into flashback mode, as shown:
SQL> alter database flashback on; Note In Oracle 10g the database must be in mount mode to enable Flashback Database.
You can verify the flashback status, as follows:
SQL> select flashback_on from v$database;After you enable Flashback Database, you can view the flashback logs in your FRA with this query:
select name, log#, thread#, sequence#, bytes
from v$flashback_database_logfile;
The range of time in which you can flash back is determined by the DB_FLASHBACK_RETENTION_TARGET parameter. This specifies the upper limit, in minutes, of how far your database can be flashed back.
You can view the oldest SCN and time you can flash back your database to by running the following SQL:
select
oldest_flashback_scn
,to_char(oldest_flashback_time,'dd-mon-yy hh24:mi:ss')
from v$flashback_database_log;
If, for any reason, you need to disable Flashback Database, you can turn it off, as follows:
SQL> alter database flashback off;You can use either RMAN or SQL*Plus to flash back a database. You can specify a point in time in the past, using one of the following:
- SCN
- Timestamp
- Restore point
- Last RESETLOGS operation (works from RMAN only)
This example creates a restore point:
SQL> create restore point flash_1;Next, the application performs some testing, after which the database is flashed back to the restore point so that a new round of testing can begin:
SQL> shutdown immediate;
SQL> startup mount;
SQL> flashback database to restore point flash_1;
SQL> alter database open resetlogs;
At this point, your database should be transactionally consistent with how it was at the SCN associated with the restore point.
