Getting Ready for Oracle Flashback DatabaseJuly 14, 2009 Use this article as a primer to setting up an instance’s configuration for making use of Oracle’s Flashback Database feature. Oracle’s flashback database feature is a great feature when you come to think of it. At any point in time, you can (with proper permissions, configuration, and administrative steps) recover a database to any point in time with a single command. Experienced and Guru type DBAs have been doing this for years through a series of well thought out steps that basically took a database through an incomplete recovery. Now, with Oracle’s flashback database feature, just about anyone can perform this highly surgical task of recovering a database to a point in time quickly. As with any feature, especially one with such power, there are going to be a handful of prerequisites that will be needed before attempting such a powerful task as incomplete recovery. With the non-Guru in mind, this article looks at configuring Oracle for Flashback Database using Enterprise Manager (RAC excluded): 1. Ensure that the database is running in ARCHIVELOG mode, as archived logs are a primary structure used in the reconstruction of data during the FLASHBACK DATABASE operation. The easiest way to confirm your database is in archive log mode is to issue the ARCHIVE LOG LIST command from SQL*Plus. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 374 Next log sequence to archive 383 Current log sequence 383 You can easily see that the database is in fact in ARCHIVELOG mode, automatic archival is ENABLED (very important), and that the archive destination is at USE_DB_RECOVERY_FILE_DEST. The use of the parameter USE_DB_REZCOVERY_FILE_DEST is actually a redirect for the DB_RECOVERY_FILE_DEST, which is the default location for the flash recovery area. This location on disk can be seen through the SHOW PARAMETER command. SQL> show parameter DB_RECOVERY_FILE_DEST NAME TYPE VALUE --------------------------- ----------- ------------------------------ db_recovery_file_dest string /oradata/db11FS/flash_recovery_area db_recovery_file_dest_size big integer 1G Now, if after issuing the ARCHIVE LOG LIST command, you find your database not to be in archive log mode (normally configured when you first created your database), you can easily configure ARCHIVELOG mode through Enterprise Manager. a. Click on the Availability tab. b. Click on Recovery Settings under Setup under the Backup/Recovery section. This will bring up the Recovery Settings page. c. Check the ARCHIVELOG mode checkbox within the Media Recovery section to place this database in ARCHIVELOG mode. d. Click the Apply button to save. e. Click the Yes button to restart the database when prompted to restart the database. f. Enter your Host and Database Credentials and then click the OK button on the Restart Database: Specify Host and Target Database Credentials page. g. Click Yes on the Restart Database:Confirmation page to restart the database. 2. Ensure that you have a flash recovery area defined and enabled since flashback logs can only be stored in a flash recovery area. From the ARCHIVE LOG LIST and SHOW PARAMETER for DB_RECOVERY_FILE_DEST above, you may or may not have had an actual flash recovery area defined. To enable a flash recovery area, four items must be configured / enabled.
a. Click on the Availability tab b. Click on Recovery Settings under Setup under the Backup/Recovery section. This will bring up the Recovery Settings page. c. Within the Media Recovery section, you can see that the last archiving location is set to USE_DB_RECOVERY_FILE_DEST—remembering that USE_DB_RECOVERY_FILE_DEST points to the DB_RECOVERY_FILE_DEST, which is the default location for the flash recovery area. d. Enter a Flash Recovery Area Location (DB_RECOVERY_FILE_DEST) within the Flash Recovery section. As a side note, the directory structure under the flash recovery area will look something like this:
[oracle@ludwig flash_recovery_area]$ tree
.
`-- DB11FS
|-- archivelog
| |-- 2009_06_09
| | |-- o1_mf_1_369_52xpslqx_.arc
| | |-- o1_mf_1_370_52xpwoyq_.arc
| | |-- o1_mf_1_371_52xq1bj2_.arc
| | |-- o1_mf_1_372_52xrfjb4_.arc
| | |-- o1_mf_1_373_52xt4d5x_.arc
| | |-- o1_mf_1_374_52xwd716_.arc
| | |-- o1_mf_1_375_52xz5zwq_.arc
| | |-- o1_mf_1_376_52y2fsjn_.arc
| | |-- o1_mf_1_377_52y32zlk_.arc
| | `-- o1_mf_1_378_52y35l4t_.arc
| |-- 2009_06_10
| | `-- o1_mf_1_379_52zzh914_.arc
| |-- 2009_06_17
| | `-- o1_mf_1_380_53l8b0dx_.arc
| |-- 2009_06_18
| | `-- o1_mf_1_381_53nd3w05_.arc
| |-- 2009_07_07
| | `-- o1_mf_1_382_5570wktf_.arc
| `-- 2009_07_08
| |-- o1_mf_1_383_55b45l82_.arc
| `-- o1_mf_1_384_55b5khpm_.arc
`-- onlinelog
e. You should also specify a Flash Recovery Area Size. f. Check the Enable Flashback Database checkbox to enable flashback logging. As another side note, much of the Oracle documentation mentions issuing the ALTER DATABASE FLASHBACK ON command to enable flashback for the entire database. Checking this box, through Enterprise Manager, is what will cause this command to be issued and can be seen in the alert log. ALTER DATABASE FLASHBACK ON db_recovery_file_dest_size of 1024 MB is 76.55% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Allocated 3981204 bytes in shared pool for flashback generation buffer Starting background process RVWR Wed Jul 08 16:53:27 2009 RVWR started with pid=17, OS id=10650 Flashback Database Enabled Turn database flashback on at SCN 2426421 Completed: ALTER DATABASE FLASHBACK ON g. Choose a Flashback Retention Time that makes sense for your database. h. Click the Apply button to save. i. Click the Yes button to restart the database when prompted to restart the database. j. Enter your Host and Database Credentials and then click the OK button on the Restart Database; Specify Host and Target Database Credentials page. k. Click Yes on the Restart Database:Confirmation page to restart the database. At this point, you can now perform backups, including archive logs, to the flash recovery area—making storage, recovery, and persistence of those files much easier. In subsequent articles I’ll begin to use this newly created flashback area to perform some flashback database operations and determine some ways in which to monitor and tune the process. Best of all, just as in this article, I’ll primarily be using Enterprise Manager with very few breakouts to SQL*Plus or the command line. Should be interesting. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
