You are a new oracle dba and you want to know the best way about how to recover your database in case your user accidentally deleted some rows from a table or multiple tables or if you had a database crash 1 hour before and you want to recover your database in the fastest way as possible.
This article explains in detail and indepth about the FLASHBACK DATABASE functionality which will answer all your above questions.
Remember that you can only use this functionality if this feature has already been set up for your database either by you or your senior database administrator.
this is one of the best articles i found in the internet regarding this feature.
Flashback Query
Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.
Prerequisites
Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments. The maximum time period that can be flashbacked to is defined using the UNDO_RETENTION parameter in the init.ora file. Alternatively, this parameter can be set using:
ALTER SYSTEM SET UNDO_RETENTION = <seconds>;
Using Flashback Query
Flashback Query is enabled and disabled using the DBMS_FLASHBACK package. The point in time of the flashback can be specified using the SCN or the actual time:
EXECUTE Dbms_Flashback.Enable_At_System_Change_Number(123);
EXECUTE Dbms_Flashback.Enable_At_Time('28-AUG-01 11:00:00');
Once you’ve finished performing all your read-only operations you can turn off flashback query using:
EXECUTE Dbms_Flashback.Disable;
The flashback query is turned off when the session ends if there is no explicit call to the disable procedure. The current system change number can be returned using the Get_System_Change_Number function.
Example Data Recovery
Assuming all data was accidentally deleted from the EMPLOYEES table at 9:05AM we could recover it using:
DECLARE
CURSOR c_emp IS
SELECT *
FROM employees;
v_row c_emp%ROWTYPE;
BEGIN
Dbms_Flashback.Enable_At_Time('28-AUG-01 09:00:00');
OPEN c_emp;
Dbms_Flashback.Disable;
LOOP
FETCH c_emp INTO v_row;
EXIT WHEN c_emp%NOTFOUND;
INSERT INTO employees VALUES
(v_row.employee_id, v_row.first_name,
v_row.last_name, v_row.email,
v_row.phone_number, v_row.hire_date,
v_row.job_id, v_row.salary,
v_row.commission_pct, v_row.manager_id,
v_row.department_id, v_row.dn);
END LOOP;
CLOSE c_emp;
COMMIT;
END;
/
Notice that the Flashback Query session is disabled after the cursor is created so that the DML operations can be performed to recover the data.
Restrictions
- The server must be configured to use Automatic Undo Management.
- No DDL or DML can be issued when using Flashback Query.
- Flashback Query does not reverse DDL operations such as
DROP, only DML alterations to the data. - Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback data.
Hope this helps. Regards Tim…
10g flashback recovery
/*
|| Oracle 10g RMAN Listing 2
||
|| Contains examples of new Oracle 10g FlashBack Recovery Area and
|| Flashback Database features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g’s
|| FlashBack Recovery Area and Flashback Database features and should be
|| carefully proofread before executing it against any existing Oracle database || to insure that no potential damage can occur.
||
*/
—–
— Listing 2.1: Setting up the Flash Recovery Area – closed database
—–
— Entries to add to database’s INIT.ORA:
###########################################
# Flashback Backup and Recovery settings
###########################################
db_recovery_file_dest_size = 2G # See article for suggested sizing guidelines
db_recovery_file_dest = ‘c:\oracle\fbrdata\zdcdb’ # Should be a separate area of disk
db_flashback_retention_target = 2880 # Will hold two days (2880 minutes) worth of Flashback
# Activate this to transmit an extra copy of archived redo logs to Flash Recovery Area
log_archive_dest_2 = ‘location=use_db_recovery_file_dest’
log_archive_dest_state_2 = enable
—–
— Listing 2.2: Setting up the Flash Recovery Area – open database
—–
— Be sure to set DB_FILE_RECOVERY_DEST_SIZE first …
ALTER SYSTEM SET db_file_recovery_dest_size = ‘5G’ SCOPE=BOTH SID=’*’;
— … and then set DB_FILE_RECOVERY_DEST and DB_FLASHBACK_RETENTION_TARGET
ALTER SYSTEM SET db_file_recovery_dest = ‘c:\oracle\fbrdata\zdcdb’ SCOPE=BOTH SID=’*’;
ALTER SYSTEM SET db_flashback_retention_target = 2880;
—–
— Listing 2.3: Flash Recovery status queries
—–
— What Flashback options are currently enabled for this database?
TTITLE ‘Flashback Options Currently Enabled:’
COL name FORMAT A32 HEADING ‘Parameter’
COL value FORMAT A32 HEADING ‘Setting’
SELECT
name
,value
FROM v$parameter
WHERE NAME LIKE ‘%flash%’ OR NAME LIKE ‘%recovery%’
ORDER BY NAME;
— What’s the status of the Flash Recovery Area?
TTITLE ‘Flash Recovery Area Status’
COL name FORMAT A32 HEADING ‘File Name’
COL spc_lmt_mb FORMAT 9999.99 HEADING ‘Space|Limit|(MB)’
COL spc_usd_mb FORMAT 9999.99 HEADING ‘Space|Used|(MB)’
COL spc_rcl_mb FORMAT 9999.99 HEADING ‘Reclm|Space|(MB)’
COL number_of_files FORMAT 99999 HEADING ‘Files’
SELECT
name
,space_limit /(1024*1024) spc_lmt_mb
,space_used /(1024*1024) spc_usd_mb
,space_reclaimable /(1024*1024) spc_rcl_mb
,number_of_files
FROM v$recovery_file_dest;
— Is Flashback Database currently activated for this database?
TTITLE ‘Is Flashback Database Enabled?’
COL name FORMAT A12 HEADING ‘Database’
COL current_scn FORMAT 9999999 HEADING ‘Current|SCN #’
COL flashback_on FORMAT A8 HEADING ‘Flash|Back On?’
SELECT
name
,current_scn
,flashback_on
FROM v$database;
— What’s the earliest point to which this database can be flashed back?
TTITLE ‘Flashback Database Limits’
COL oldest_flashback_scn FORMAT 999999999 HEADING ‘Oldest|Flashback|SCN #’
COL oldest_flashback_time FORMAT A20 HEADING ‘Oldest|Flashback|Time’
COL retention_target FORMAT 999999999 HEADING ‘Oldest|Flashback|SCN #’
COL flashback_size FORMAT 999999999 HEADING ‘Oldest|Flashback|Size’
COL estimated_flashback_size FORMAT 999999999 HEADING ‘Estimated|Flashback|Size’
SELECT
oldest_flashback_scn
,oldest_flashback_time
,retention_target
,flashback_size
,estimated_flashback_size
FROM v$flashback_database_log;
—–
— Listing 2.4: Configuring RMAN to use Flash Recovery Area
—–
RUN {
# Configure RMAN specifically to use Flash Recovery Area features
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
}
—–
— Listing 2.5: RMAN Daily Backup Scheme Using Image Copies
—–
RUN {
###############################################################################
# RMAN Script: DailyImageCopyBackup.rcv
# Creates a daily image copy of all datafiles and Level 1 incremental backups
# for use by the daily image copies
###############################################################################
# Roll forward any available changes to image copy files
# from the previous set of incremental Level 1 backups
RECOVER
COPY OF DATABASE
WITH TAG ‘img_cpy_upd’;
# Create incremental level 1 backup of all datafiles in the database
# for roll-forward application against image copies
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG ‘img_cpy_upd’
DATABASE;
}
—–
— Listing 2.6: Results of First Daily Backup
—–
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
——- —- – ————— ———- ————— —-
41 1 A 07-DEC-04 2119100 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_SYSTEM_0VDM2NP9_.DBF
1 1 A 20-NOV-04 2006057 20-NOV-04 C:\RMANBKUP
43 2 A 07-DEC-04 2119143 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_UNDOTBS1_0VDM6MRV_.DBF
48 3 A 07-DEC-04 2119180 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_DRSYS_0VDM9OP2_.DBF
44 4 A 07-DEC-04 2119156 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_EXAMPLE_0VDM7S0X_.DBF
46 5 A 07-DEC-04 2119173 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_INDX_0VDM94ON_.DBF
50 6 A 07-DEC-04 2119186 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_TOOLS_0VDMB270_.DBF
47 7 A 07-DEC-04 2119176 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_USERS_0VDM9F8W_.DBF
45 8 A 07-DEC-04 2119166 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_XDB_0VDM8N66_.DBF
51 9 A 07-DEC-04 2119189 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT1_0VDMB6CL_.DBF
49 10 A 07-DEC-04 2119184 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT3_0VDM9Y6J_.DBF
53 11 A 07-DEC-04 2119193 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT2_0VDMBGJN_.DBF
52 12 A 07-DEC-04 2119191 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT4_0VDMBBGW_.DBF
42 13 A 07-DEC-04 2119127 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_SYSAUX_0VDM53DD_.DBF
List of Archived Log Copies
Key Thrd Seq S Low Time Name
——- —- ——- – ——— —-
148 1 203 A 05-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002030010493846599.ARC
149 1 204 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002040010493846599.ARC
150 1 205 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002050010493846599.ARC
151 1 206 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002060010493846599.ARC
152 1 207 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002070010493846599.ARC
153 1 208 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002080010493846599.ARC
154 1 209 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002090010493846599.ARC
155 1 209 A 06-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_06\O1_MF_1_209_0V9Q1HHJ_.ARC
160 1 210 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002100010493846599.ARC
161 1 210 A 06-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_08\O1_MF_1_210_0VH53GGG_.ARC
156 1 210 A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002100010493846599.ARC
157 1 210 A 06-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_07\O1_MF_1_210_0VDOMOGQ_.ARC
162 1 211 A 07-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002110010493846599.ARC
163 1 211 A 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_08\O1_MF_1_211_0VH53NS2_.ARC
158 1 211 A 07-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002110010493846599.ARC
159 1 211 A 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_07\O1_MF_1_211_0VDOPPDT_.ARC
164 1 212 A 07-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002120010493846599.ARC
165 1 212 A 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_08\O1_MF_1_212_0VH53V2V_.ARC
—–
— Listing 2.7: Flashback Log Query
—–
— What Flashback Logs are available?
TTITLE ‘Current Flashback Logs Available’
COL log# FORMAT 9999 HEADING ‘FLB|Log#’
COL bytes FORMAT 99999999 HEADING ‘Flshbck|Log Size’
COL first_change# FORMAT 99999999 HEADING ‘Flshbck|SCN #’
COL first_time FORMAT A24 HEADING ‘Flashback Start Time’
SELECT
LOG#
,bytes
,first_change#
,first_time
FROM v$flashback_database_logfile;
Synopsis. Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article – part two of a series – explores one of the most intriguing new features of Oracle 10g: Flashback Backup and Recovery.
The previous article in this series explored the myriad enhancements to Recovery Manager (RMAN) that Oracle 10g has added to an Oracle DBA’s tool belt when constructing a well-planned backup and recovery strategy.
Oracle 9i provided the capability to "flash back" to a prior view of the database via queries performed against specific logical entities. For example, if a user had accidentally added, modified or deleted a large number of rows erroneously, it was now possible to view the state of logical entity just before the operation had taken place. This capability was limited, of course, by the amount of UNDO data retained in the database’s UNDO segments and bounded by the time frame specified by the UNDO_RETENTION initialization parameter. Oracle 10g expands these logical flashback capabilities significantly, and I will cover them in detail in the next article in this series.
However, when a DBA needed to return an entire database back to a prior state to recover from a serious logical error – for example, when multiple erroneous transactions within the same logical unit of work have affected the contents of several database tables – a logical option was to perform an incomplete database recovery. Since an incomplete recovery requires that all datafiles are first restored from the latest backup, and then a careful "roll forward" recovery through the appropriate archived and online redo logs until the appropriate point in time was reached, the database would be unavailable until this process was completed.
With the addition of Flashback Database, Oracle 10g has significantly improved the availability of a database while it’s restored and recovered to the desired point in time. These new features, however, do take some additional effort to plan for and set up, so let’s start at the beginning: configuring the Flash Recovery Area.
Enabling The Flash Recovery Area
Before any Flash Backup and Recovery activity can take place, the Flash Recovery Area must be set up. The Flash Recovery Area is a specific area of disk storage that is set aside exclusively for retention of backup components such as datafile image copies, archived redo logs, and control file autobackup copies. These features include:
Unified Backup Files Storage. All backup components can be stored in one consolidated spot. The Flash Recovery Area is managed via Oracle Managed Files (OMF), and it can utilize disk resources managed by Oracle Automated Storage Management (ASM). In addition, the Flash Recovery Area can be configured for use by multiple database instances if so desired.
Automated Disk-Based Backup and Recovery. Once the Flash Recovery Area is configured, all backup components (datafile image copies, archived redo logs, and so on) are managed automatically by Oracle.
Automatic Deletion of Backup Components. Once backup components have been successfully created, RMAN can be configured to automatically clean up files that are no longer needed (thus reducing risk of insufficient disk space for backups).
Disk Cache for Tape Copies. Finally, if your disaster recovery plan involves backing up to alternate media, the Flash Recovery Area can act as a disk cache area for those backup components that are eventually copied to tape.
Flashback Logs. The Flash Recovery Area is also used to store and manage flashback logs, which are used during Flashback Backup operations to quickly restore a database to a prior desired state.
Sizing the Flash Recovery Area. Oracle recommends that the Flash Recovery Area should be sized large enough to include all files required for backup and recovery. However, if insufficient disk space is available, Oracle recommends that it be sized at least large enough to contain any archived redo logs that have not yet been backed up to alternate media.
Table 1 below shows the minimum and recommended sizes for the Flash Recovery Area based on the sizes of these database files in my current Oracle 10g evaluation database:
| Table 1. Sizing The Flash Recovery Area | |
| Database Element | Estimated Size (MB) |
| Image copies of all datafiles | 1200 |
| Incremental backups | 256 |
| Online Redo Logs | 48 |
| Archived Redo Logs retained for backup to tape | 96 |
| Control Files | 6 |
| Control File Autobackups | 6 |
| Flash Recovery Logs | 96 |
| Recommended Size: | 1708 |
| Minimum Size: | 96 |
Based on these estimates, I will dedicate 2GB of available disk space so I can demonstrate a complete implementation of the Flash Recovery Area.
Setting Up the Flash Recovery Area. Activation of the Flash Recovery Area specifying values for two additional initialization parameters:
- DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. Note that Oracle recommends setting this value first.
- DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flashback Recovery Area will be stored. Oracle recommends that this be a separate location from the database’s datafiles, control files, and redo logs. Also, note that if the database is using Oracle’s new Automatic Storage Management (ASM) feature, then the shared disk area that ASM manages can be targeted for the Flashback Recovery Area.
Activating the Flash Recovery Area. It is obviously preferable to set up the Flash Recovery Area when a database is being set up for the first time, as all that needs to be done is to make the changes to the database’s initialization parameters. However, if the Flash Recovery Area is being set up for an existing database, all that’s required to do is issue the appropriate ALTER SYSTEM commands.
Listing 2.1 shows the changes I have made to the database’s initialization parameter file, including an example of how to insure that an additional copy of the database’s archived redo logs is created in the Flash Recovery area.
Listing 2.2 shows the commands to issue to set up the Flash Recovery Area when the database is already open before flashback logging has been activated.
Oracle 10g Availability Enhancements, Part 2: Flashback Database
By Jim Czuprynski
Enabling Flashback Database
As its name implies, Flashback Database offers the capability to quickly "flash" a database back to its prior state as of a specified point in time. Oracle does this by retaining a copy of any modified database blocks in flashback logs in the Flash Recovery Area. A new flashback log is written to the Flash Recovery Area on a regular basis (usually hourly, even if nothing has changed in the database), and these logs are typically smaller in size than an archived redo log. Flashback logs have a file extension of .FLB.
When a Flashback Database request is received, Oracle then reconstructs the state of the database just prior to the point in time requested using the contents of the appropriate flashback logs. Then the database’s archived redo logs are used to fill in the remaining gaps between the last backup of the datafile and the point in time desired for recovery.
The beauty of this approach is that no datafiles need to be restored from backups; further, only the few changes required to fill in the gaps are automatically applied from archived redo logs. This means that recovery is much quicker than traditional incomplete recovery methods, with much higher database availability.
It is worth noting the few prerequisites that must be met before a database may utilize Flashback Database features:
- The database must have flashback logging enabled, and therefore a Flash Recovery Area must have been configured. (For a RAC environment, the Flash Recovery Area must also be stored in either ASM or in a clustered file system.)
- Since archived redo logs are used to "fill in the gaps" during Flashback Database recovery, the database must be running in ARCHIVELOG mode.
Activating Flashback Database. Once the Flash Recovery Area has been configured, the next step is to enable Flashback Database by issuing the ALTER DATABASE FLASHBACK ON; command while the database is in MOUNT EXCLUSIVE mode, similar to activating a database in ARCHIVELOG mode.
Setting the Flashback Retention Target. Once Flashback Database has been enabled, the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines exactly how far a database can be flashed back. The default value is 1440 minutes (one full day), but this can be modified to suit the needs of your database. For purposes of illustration, I have set my demonstration database’s setting to 2880 minutes (two full days).
Deactivating Flashback Database. Likewise, issuing the ALTER DATABASE FLASHBACK OFF; command deactivates Flashback Backup and Recovery. Just as in the activation process, note that this command must be issued while the database is in MOUNT EXCLUSIVE mode.
See Listing 2.3 for queries that display the status of the Flash Recovery Area, status of the related initialization parameters, and whether the database has been successfully configured for flashback.
Storing Backups In Flash Recovery Area
Now that I have enabled the Flash Recovery Area and enabled flashback logging, I can next turn my attention to preparing the database to use flashback logs during a Flashback Database recovery operation.
Listing 2.4 lists the RMAN commands I will need to issue to configure the database for Flash Recovery Area and Flashback Database use. Notice that I have not CONFIGUREd a FORMAT directive for the RMAN channels used to create database backups; for these examples, I am going to let RMAN place all backup components directly in the Flash Recovery Area.
Listing 2.5 implements Oracle’s recommended daily RMAN backup scheme using datafile image copies and incrementally-updated backups. (See the previous article in this series for a full discussion of this technique.)
Finally, Listing 2.6 shows the abbreviated results of the first cycle’s run of this backup scheme. Note that Oracle uses OMF naming standards for each backup component file – in this example, datafiles, the "extra copy" of the archived redo logs, and control file autobackups – stored in the Flash Recovery Area.
Flashback Database: An Example
Now that I have enabled flashback logging and have created sufficient backup components that are being managed in the Flash Recovery Area, it is time to demonstrate a Flashback Database operation.
Let’s assume a worst-case scenario: One of my junior developers has been enthusiastically experimenting with logical units of work on what he thought was his personal development database, but instead mistakenly applied a transaction against the production database. He has just accidentally deleted several thousand entries in the SH.SALES and SH.COSTS tables – just in time to endanger our end-of-quarter sales reporting schedule, of course! Here is the DML statements issued, along with the number of records removed:
DELETE FROM sh.sales
WHERE prod_id BETWEEN 20 AND 80;
10455 rows deleted
Executed in 89.408 seconds
DELETE FROM sh.costs
WHERE prod_id BETWEEN 20 AND 80;
6728 rows deleted
Executed in 18.086 seconds
COMMIT;
Commit complete
Executed in 0.881 seconds
Flashback Database to the rescue! Since I know the approximate date and time that this transaction was committed to the database, I will issue an appropriate FLASHBACK DATABASE command from within an RMAN session to return the database to that approximate point in time. Here is a more complete listing of the FLASHBACK DATABASE command set:
FLASHBACK [DEVICE TYPE = <device type>] DATABASE
TO [BEFORE] SCN = <scn>
TO [BEFORE] SEQUENCE = <sequence> [THREAD = <thread id>]
TO [BEFORE] TIME = '<date_string>'
Note that I can return the database to any prior point in time based on a specific System Change Number (SCN), a specific redo log sequence number (SEQUENCE), or to a specific date and time (TIME). If I specify the BEFORE directive, I am telling RMAN to flash the database back to the point in time just prior to the specified SCN, redo log, or time, whereas if the BEFORE directive is not specified, the database will be flashed back to the specified SCN, redo log, or time as of that specified point in time, i.e., inclusively.
First, I queried my database’s Flashback Logs to determine which ones are available, found the log just prior to the user error and decided to flash back the database based on that log’s starting SCN. Listing 2.7 contains the query I ran against V$FLASHBACK_DATABASE_LOGFILE to obtain this information.
Just as I would do during a normal point-in-time incomplete recovery, I then shut down the database by issuing the SHUTDOWN IMMEDIATE command, and then restarted the database and brought it into MOUNT mode via the STARTUP MOUNT command. Instead of having to perform a restoration of datafiles as in a normal incomplete recovery, I instead simply issue the appropriate FLASHBACK DATABASE command to take the database back to the SCN I desired.
Once the flashback is completed, I could have continued to roll forward additional changes from the archived redo logs available; however, I simply chose to open the database at this point in time via the ALTER DATABASE OPEN RESETLOGS; command. Here are the actual results from the RMAN session:
C:>rman nocatalog target sys/@zdcdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ZDCDB (DBID=1863541959)
using target database controlfile instead of recovery catalog
RMAN> FLASHBACK DEVICE TYPE = DISK DATABASE TO SCN = 2127725;
Starting flashback at 08-DEC-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK
starting media recovery
media recovery complete
Finished flashback at 08-DEC-04
RMAN> alter database open resetlogs;
database opened
To see what is really going on during the flashback and recovery process, I have also included a portion of the database’s alert log. Note that Oracle automatically cleaned up after itself: Since they are of no use any longer after the RESETLOGS operation, Oracle even deleted the outmoded Flashback Logs from the Flashback Recovery Area.
Conclusion
Oracle 10g’s Flash Recovery Area simplifies the storage and handling of backup components and flashback logs, and the new Flashback Database features provide any Oracle DBA with a much improved, faster option for incomplete database recovery. The next article in this series will delve into the details of using Oracle 10g’s expanded Logical Flashback features, including some intriguing capabilities for recovering from logical errors at a much more granular level than Flashback Database provides.
References and Additional Reading
While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:
B10734-01 Oracle Database Backup and Recovery Advanced User’s Guide
B10735-01 Oracle Database Backup and Recovery Basics
B10750-01 Oracle Database New Features Guide
B10770-01 Oracle Database Recovery Manager Refererence
