Nov 22, 2023
Flashback Database- Files

The FLASHBACK DATABASE command was introduced to speed up the otherwise slow process of point-in-time database recovery. It can be used in place of a full database restore and a rolling forward using archive logs, and it is primarily designed to speed up the recovery from an “accident.”

For example, let’s take a look at what a DBA might do to recover from an accidentally dropped schema, in which the right schema was dropped, just in the wrong database (it was meant to be dropped in the test environment). The DBA immediately recognizes the mistake they have made and shuts down the database right away. Now what?

Prior to the FLASHBACK DATABASE capability, what would probably happen is this:

\ 1.\ The DBA would shut down the database.

\ 2.\ The DBA would restore the last full backup of the database from tape (typically), generally a long process. Typically, this would be initiated with RMAN via RESTORE DATABASE UNTIL .

\ 3.\ The DBA would restore all archive redo logs generated since the backup that were not available on the system.

\ 4.\ Using the archive redo logs (and possibly information in the online redo logs), the DBA would roll the database forward and stop rolling forward at a point in time just before the erroneous DROP USER command. Steps 3 and 4 in this list would typically be initiated with RMAN via RECOVER DATABASE UNTIL .

\ 5.\ The database would be opened with the RESETLOGS option.

This was a nontrivial process with many steps and would generally consume a large piece of time (time when no one could access the database, of course). The causes of a point-in-time recovery like this are many: an upgrade script gone awry, an upgrade gone bad, an inadvertent command issued by someone with the privilege to issue it (a mistake, probably the most frequent cause), or some process introducing data integrity issues into a large database (again, an accident; maybe it was run twice instead of just once, or maybe it had a bug). Whatever the reason, the net effect was a large period of downtime.

The steps to recover, assuming you configured the Flashback Database capability, would be as follows:

\ 1.\ The DBA shuts down the database.

\ 2.\ The DBA startup-mounts the database and issues the Flashback Database command, using either an SCN (the Oracle internal clock), a restore point (which is a pointer to an SCN), or a timestamp (wall clock time), which would be accurate to within a couple of seconds.

\ 3.\ The DBA opens the database with resetlogs.

When flashing back, you can flashback either the entire container database (including all pluggable databases) or just a particular pluggable database. When flashing back the pluggable database, you would use restore points that you created for the entire database or for a specific pluggable database. Flashing back a pluggable database does not require that you open the database with resetlogs, but it does require that you issue the RECOVER DATABASE command while connected to the pluggable database you flashed back.

To use the flashback feature, your database must be in archivelog mode, and you must have a Fast Recovery Area (FRA) setup (because the flashback logs are stored in the FRA). To use normal restore points, you must enable flashback logging in the database. Guaranteed restore points do not require flashback logging in the database.

To view the flashback logging status, run the following query:

SQL> select flashback_on from v$database;
FLASHBACK_ON

NO

To enable flashback logging, do as follows in the root container:

$ sqlplus / as sysdba
SQL> alter database flashback on;

The final point here is that you need to set up the flashback capability before you ever need to use it. It is not something you can enable after the damage is done; you must make a conscious decision to use it, whether you have it on continuously or whether you use it to set restore points.

More Details

Leave a Reply

Your email address will not be published. Required fields are marked *