Flashback Database

---

The most revolutionary Flashback Technology may also be the one that gets used the least often. Flashback Database provides the ability to quickly rewind the entire database to a previous point in time. This operation has as its end result that which you would get from doing point-in-time recovery using RMAN or user-managed recovery. However, Flashback Database does not require the restore of all of the database's datafiles from the most recent backup, followed by a roll-forward using all the archivelogs that have accumulated since that backup. By avoiding these costly operations, Flashback Database can perform a point-in-time recovery in a fraction of the time typically required for such an operation.

Flashback Database works by incrementally recording all blocks that have changed at a timed interval. These flashback 'checkpoints' then provide the points to which the database can be 'rewound.' After rolling back to the flashback checkpoint, archivelogs can be used to then roll forward to the exact time or SCN specified by the FLASHBACK DATABASE command. So, the operation uses new technology as well as the old standby, the archivelogs, to provide a fast way to perform point-in-time recovery.

Typically, there is less archival to be applied after a flashback checkpoint than must be applied to the last backup (typically taken every night, vs. every few minutes for flashback logs), so the recovery stage of flashback is very quick.

Flashback Logs

Flashback Database implements a new type of log, called the flashback log, which is generated by the database at regular intervals and accumulates in the flashback recovery area (FRA) you created in Chapter 8. You must have an FRA for Flashback Database; the logs cannot be created anywhere else. The flashback log contains a copied image of every block that has been changed since the last flashback log was generated. These blocks can then be reinstated into the database when a FLASHBACK DATABASE command is issued in order to rewind the database back to its state at the time specified in the FLASHBACK command.

Because entire blocks are being dumped to the flashback logs, they can accumulate very quickly in extremely active databases. Setting an appropriately sized FRA is crucial to the success of meeting your Flashback Database needs. In addition, you can manually turn off flashback logging for certain tablespaces that could be manually re-created after a Flashback Database operation, and thereby decrease the amount of logging that occurs.

alter tablespace ws_app_idx flashback off; 

You can turn it back on at any time, but it is worth noting that you cannot rewind backward through a flashback logging gap for the tablespace you turned off.

alter tablespace sales_idx flashback on; 

Any tablespace that has flashback logging turned off for any period of time within the FLASHBACK DATABASE command would need to be offlined prior to performing the Flashback Database.

Flashback Retention Target

The lifespan of flashback logs correlates directly to how far back in time you would like to have the Flashback Database option. By default, the flashback logs will be kept long enough so that you can always flashback 24 hours from the current time. If this is too long or too short of a time, you can change it with an init parameter:

alter system set db_flashback_retention_target=720;

The value is specified in minutes (720 would be 12 hours).

HA Workshop: Configure for Flashback Database

Workshop Notes

This workshop will walk you through the primary steps required to configure the database initially for using flashback logging for Flashback Database operations.

Step 1.  Shut down the database and start up mount. The database must be mounted but not open.

SQL> select status from v$instance; 

In addition, check to make sure the database is in archivelog mode. Archivelog mode is required for Flashback Database.

SQL> archive log list; Database log mode               Archive Mode Automatic archival              Enabled Archive destination             USE_DB_RECOVERY_FILE_DEST Oldest online log sequence      62 Next log sequence to archive    64 Current log sequence            64

Step 2.  Set the flashback retention target to your desired value. We will use 12 hours as the window.

alter system set db_flashback_retention_target=720 SCOPE=BOTH SID='*';

Step 3.  Set the values for db_recovery_file_dest and db_recovery_file_dest_size (flash recovery area parameters). Note that if you have already set these for your RMAN backup strategy as outlined in Chapter 8, you should review the parameters now. Flashback logs increase FRA usage significantly. It would behoove you to at least double the given size of the FRA.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2335825920 SCOPE=BOTH SID='*'; SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/fra/' SCOPE=BOTH SID='*';

Step 4.  Turn flashback logging on. This is done in the same fashion as turning archivelog on-with an ALTER DATABASE command when the database is mounted but not open.

alter database flashback on;

Step 5.  Turn flashback logging off for any tablespaces that you deem do not require it.

alter tablespace sales_idx flashback off;

Step 6.  Open the database.

alter database open; 

Flashback Database: Tuning and Tweaking

So, you've determined that Flashback Database provides you with a fallback position you desire for your database, and you have determined how far back you want your fallback position to be. You've set your db_flashback_retention_target. Now, the question comes up: how do I know if I have enough space in my flashback recovery area to handle the volume of flashback logs being generated? And, for that matter, how much flashback logging is occuring?

Using V$FLASHBACK_DATABASE_LOG

One thing at a time. First, Oracle provides built-in analysis for you to use in determining if you need to increase the size of your flashback recovery area (FRA). After you enable flashback logging, Oracle begins to keep track of the amount of flashback logging that is occuring, and stores it in the view V$FLASHBACK_DATABASE_LOG. This view actually provides an estimate for the total flashback size:

select estimated_flashback_size from v$flashback _database_log;

Note that this view gives the size for flashback logs, not for all users in the FRA, so you will need to add this value to whatever size you need for archivelogs and RMAN backups. This estimated value only gets better with age, meaning that as the database runs through its day-to-day (and then month-to-month) operations, Oracle can provide a better estimate of the size. So, it is a good idea to check back in with this estimator to find out if you still have the right specifications in place.

V$FLASHBACK_DATABASE_LOG also provides you with the actual oldest time that you can flashback the database to, given the current size of the FRA and the currently available flashback logs. You can use this as another indicator of space issues in the FRA.

select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

Using V$FLASHBACK_DATABASE_STAT

Oracle has built a monitoring view so that you can keep your trained HADBA eye on flashback logging activity. V$FLASHBACK_DATABASE_STAT provides you with information on flashback data generated over the course of a period of time (typically, a one-hour window extending back from sysdate). In addition to showing how much flashback logging occurred, this view posts the redo generated and the actual database data generated over the same period.

select * from v$flashback_database_stat; <output>

HA Workshop: Perform Flashback Database

Workshop Notes

It's time to give it a test drive. We are going to introduce a fault that cannot be handled by any of the other less intrusive forms of flashback: the table truncate. Because the truncate does not produce any redo, we cannot do a Flashback Table. So we are forced to do a flashback of the entire database.

Step 1.  First, get the current SCN from the database. Because we are simply testing, we can prepare for the test by getting the current SCN prior to putting the fault into the database.

SQL> select current_scn from v$database; CURRENT_SCN ---------------- 885524

Step 2.  Introduce the fault.

SQL> truncate table woodscrew; Table truncated.

Step 3.  Shut down the database, and then remount. The database must be mounted and not open for flashback.

SQL> connect / as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount;

Step 4.  Issue the FLASHBACK command.

SQL> flashback database to scn 885524; Flashback complete.

Step 5.  Open the database read-only to confirm that the table has been flashed back to the appropriate SCN.

SQL> alter database open read only;  Database altered.   SQL> connect sales/sales;  Connected.   SQL> select count(*) from woodscrew;     COUNT(*) ----------         12

Step 6. Open the database with resetlogs.

SQL> connect / as sysdba  SQL> shutdown immediate;  SQL> startup mount;   SQL> alter database open resetlogs; 

Flashback Database: Opportunity for Different Uses

One of the most interesting things about Flashback Database is that the full set of uses won't be apparent to everyone until after it has been around a while. Flashback Database is quite literally a rewind button for the database. You can flashback to a certain point, and open the database read-only to have a look. If you haven't gone back far enough, you can do another flashback. Then, you can also use the database's existing 'fast-forward' button-database recovery using archivelogs-to move forward, if you have flashed back too far into the past. So, when trying to come to terms with a user-induced trauma, you can move back and forth in the database until you isolate the perfect moment to open the database.

Flashback Database as a Historical Data Extraction Tool

Take the preceeding HA Workshop, 'Perform Flashback Database.' At Step 6, after we opened the database read-only and confirmed that the Woodscrew table was back, we opened the database with resetlogs. This would be required in most cases where we simply needed to get the database back up and operational in as little time as possible. All data generated after the flasback time would be sacrificed on the database altar.

But, there is another option. If you can live without the lost table until you can schedule emergency downtime (at end of business day, for instance), then you might be able to get the Woodscrew table back and not be forced to lose data from any other tables in the database. To do this, you would flashback until the appropriate time and then open the database read-only. Then, instead of opening resetlogs, you would use export to do a table-level dump of the Woodscrew table. After the export completed, you would only have to issue a RECOVER DATABASE command, and Oracle would fast forward to the time of the clean shutdown, and you could open the database and be back in business. There would have to be an outage of the entire system for the duration of the export, but it can be a decent trade-off to get data from a lost table and still retain the rest of the database data up to the current time.

Flashback Database for Standby Database Reinstantiation

Another intriguing possibility for Flashback Database comes in relation to the standby database function of your Data Guard implementation. The standby database can also be flashed to a previous time. Therefore, if you failover to the standby to overcome a logical failure, you can flashback the standby if the failure has already been propagated from the primary database.

Perhaps more compelling, Flashback Database can be used for standby database reinstantiation. When a failover occurs from the primary to the standby, in the past you have been forced to use backups to restore a copy of the new primary back to the old primary and make it a standby, and then fail back over. Now, you can instead use Flashback Database on the old primary to move it back to the exact SCN at which the standby database was at prior to activation. Then you set up the flow of archivelogs from the new primary to the old primary and give it a new standby controlfile, and you are back in business in minutes.

---