10 Detailed Recovery Steps

10.3.3.1 Physical Standby Failover Using SQL*Plus

1. Check for archive gaps.

   SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# 
   FROM V$ARCHIVE_GAP;
   

2. Shut down other standby instances.

3. Finish recovery.

   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
   
   

4. Check the database state. Query switchover status readiness by executing the following statement:

   SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   
   

5. Convert the physical standby database to the production role.

   ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
   
   

6. Restart instance.

10.3.3.2 Logical Standby Failover Using SQL*Plus

1. Stop the current SQL Apply session.

   ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   
   

2. If there are additional logs to be registered, (for example, you can get to the primary database or you are using LGWR to the standby destination), then register the log files.

   ALTER DATABASE REGISTER LOGICAL LOGFILE 'file_name';
   
   

3. Start the SQL Apply session using the NODELAY and FINISH clauses.

   ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY FINISH;
   
   

4. Activate the logical standby database.

   ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;
   
   

Failover has completed, and the new production database is available to process transactions.

10.4.3.1 Physical Standby Switchover Using SQL*Plus

1. Shut down all production and standby instances except one for each site.

2. Stop active sessions on remaining active production instance

   To identify active sessions, execute the following query:

   SELECT SID, PROCESS, PROGRAM 
   FROM V$SESSION 
   WHERE TYPE = 'USER' 
   AND SID <> (SELECT DISTINCT SID FROM V$MYSTAT);
   
   

3. Check that the switchover status on the production database is 'TO STANDBY'.

   SELECT SWITCHOVER_STATUS FROM V$DATABASE;
   
   

4. Switch over the current production database to the standby database.

   ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY [WITH SESSION SHUTDOWN];
   
   

5. Start the new standby database.

   STARTUP MOUNT;
   RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
   
   

6. Convert the former standby database to a production database.

   ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WITH SESSION SHUTDOWN];
   
   

7. Restart all instances.

10.4.3.2 Logical Standby Switchover Using SQL*Plus

1. Prepare the production database to become the logical standby database.

   ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
   
   

2. Prepare the logical standby database to become the production database.

   Following this step, logs start to ship in both directions, although the current production database does not process the logs coming from the current logical standby database.

   ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
   
   

3. Commit the production database to become the logical standby database.

   This is the phase where current transactions on the production database are cancelled. All DML-related cursors are invalidated, preventing new records from being applied. The end of redo (EOR) marker is recorded in the online redo log and then shipped (immediately if using real-time apply) to the logical standby database and registered.

   ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
   
   

4. Commit the logical standby database to become the production database.

   ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
   
   

5. Start the logical standby apply engine on the new logical standby database.

   If real-time apply is required, execute the following statement:

   ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
   
   

   Otherwise execute the following statement:

   ALTER DATABASE START LOGICAL STANDBY APPLY;
   

10.5.1.1 Automatic Instance Recovery for Failed Instances

Instance failure occurs when software or hardware problems disable an instance. After instance failure, Oracle automatically uses the online redo log file to perform database recovery as described in this section.

10.5.1.2 Automatic Service Relocation

Service reliability is achieved by configuring and failing over among redundant instances. More instances are enabled to provide a service than would otherwise be needed. If a hardware failure occurs and adversely affects a RAC database instance, then RAC automatically moves any services on that instance to another available instance. Then Cluster Ready Services (CRS) attempts to restart the failed nodes and instances.

An installation can specify the "preferred" and "available" configuration for each service. This configuration describes the preferred way to run the system, and is used when the service first starts up. For example, the ERP service runs on instance1 and instance2, and the HR service runs on instance3 when the system first starts. instance2 is available to run HR in the event of a failure or planned outage, and instance3 and instance4 are available to run ERP. The service configuration can be designed several ways.

RAC recognizes when a failure affects a service and automatically fails over the service and redistributes the clients across the surviving instance supporting the service. In parallel, CRS attempts to restart and integrate the failed instances and dependent resources back into the system. Notification of failures occur at various levels including notifying external parties through Enterprise Manager and callouts, recording the fault for tracking, event logging, and interrupting applications. Notification occurs from a surviving fault domain when the failed domain is out of service. The location and number of fault domains serving a service is transparent to the applications. Auto restart and recovery are automatic, including all the subsystems, not just database.

10.5.2.1 Disabling CRS-Managed Resources

When an outage occurs, RAC automatically restarts essential components. Components that are eligible for automatic restart include instances, listeners, and the database as well as several subcomponents. Some scheduled administrative tasks require that you prevent components from automatically restarting. To perform scheduled maintenance that requires a CRS-managed component to be down during the operation, the resource must be disabled to prevent CRS from trying to automatically restart the component. For example, to take a node and all of its instances and services offline for maintenance purposes, disable the instance and its services using either Enterprise Manager or SRVCTL, and then perform the required maintenance. Otherwise, if the node fails and then restarts, then CRS attempts to restart the instance during the administrative operation.

10.5.2.2 Planned Service Relocation

For a scheduled outage that requires an instance, node, or other component to be isolated, RAC provides the ability to relocate, disable, and enable services. Relocation migrates the service to another instance. The sessions can also be relocated. These interfaces also allow services, instances and databases to be selectively disabled while a repair, change, or upgrade is made and re-enabled after the change is complete. This ensures that the service is not started at the instance being repaired because of a dependency or a start operation on the service. The service is disabled on the instance at the beginning of the planned outage. It is then enabled at the end of the maintenance outage.

For example, to relocate the SALES service from instance1 to instance3 in order to perform scheduled maintenance on node1,the tasks can be performed using Enterprise Manager or SRVCTL commands. The following shows how to use SRVCTL commands:

1. Relocate the SALES service to instance3.

   srvctl relocate service -d PROD -s SALES -i instance1 -t instance3
   
   

2. Disable the SALES service on instance1 to prevent it from being relocated to instance1 while maintenance is performed.

   srvctl disable service -d PROD -s SALES -i instance1
   
   

3. Stop instance1.

   srvctl stop instance -d PROD -i instance1
   
   

4. Perform the scheduled maintenance.

5. Start instance1.

   srvctl start instance -D PROD -i instance1
   
   

6. Re-enable the SALES service on instance1.

   srvctl enable service -d PROD -s SALES -i instance1
   
   

7. If desired, relocate the SALES service running on instance3 back to instance1.

   srvctl relocate service -d PROD -s SALES -i instance3 -t instance1
   

10.6.1.1 Step 1: Ensure That the Chosen Standby Instance is Mounted

From the targeted standby instance, run the following query.

SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

10.6.1.2 Step 2: Verify Oracle Net Connection to the Chosen Standby Host

1. Ensure that the standby listener is started.

   % lsnrctl status listener_name
   
   

2. Validate the Oracle Net alias from all the production hosts in the cluster.

   % tnsping standby_database_connection_service_name
   
   

If the connection cannot be made, then consult Oracle Net Services Administrator's Guide for further troubleshooting.

10.6.1.3 Step 3: Start Recovery on the Chosen Standby Instance

Use the following statements for a physical standby database:

RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Use the following statements for a logical standby database:

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

10.6.1.4 Step 4: Copy Archived Redo Logs to the New Apply Host

Optionally, copy the archived redo logs to the new apply host.

The copy is not necessary for a physical standby database. For a physical standby database, when the managed recovery process detects an archive gap, it requests the production archived redo logs to be resent automatically.

For a logical standby database, unapplied archive file names that have already been registered are not resent automatically to the logical standby database. These archived redo logs must be sent manaully to the same directory structure in the new apply host. You can identify the registered unapplied archived redo logs by executing a statement similar to the following:

SELECT LL.FILE_NAME, LL.THREAD#, LL.SEQUENCE#, LL.FIRST_CHANGE#, LL.NEXT_CHANGE#, LP.APPLIED_SCN, LP.READ_SCN
  FROM DBA_LOGSTDBY_LOG LL, DBA_LOGSTDBY_PROGRESS LP
  WHERE LEAST(LP.APPLIED_SCN, LP.READ_SCN) <= LL.NEXT_CHANGE#;

Compare the results of the statement to the contents of the STANDBY_ARCHIVE_DEST directory.

10.6.1.5 Step 5: Verify the New Configuration

1. Verify that archived redo logs are being sent to the new apply host.

   Query V$ARCHIVE_STATUS and V$ARCHIVED_DEST_STATUS.

   SELECT NAME_SPACE, STATUS, TARGET, LOG_SEQUENCE ,
   TYPE,PROCESS, REGISTER , ERROR FROM V$ARCHIVE_DEST
   WHERE STATUS!='INACTIVE';
   
   SELECT * FROM V$ARCHIVE_DEST_STATUS WHERE STATUS!='INACTIVE';
   
   

2. Verify that the managed recovery or logical apply on the new apply host is progressing.

   Issue the following queries to ensure that the sequence number is advancing over time.

   Use the following statements for a physical standby database:

   SELECT MAX(SEQUENCE#), THREAD# FROM V$LOG_HISTORY GROUP BY THREAD#;
   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, CLIENT_PROCESS FROM V$MANAGED_ STANDBY;
   
   

   Use the following statements for a logical standby database:

   SELECT MAX(SEQUENCE#), THREAD# FROM DBA_LOGSTDBY_LOG GROUP BY THREAD#;
   SELECT APPLIED_SCN FROM DBA_LOGSTDBY_PROGRESS;
   

10.7.1.1 Detecting Datafile Block Corruption

A data fault is detected when it is recognized by the user, administrator, RMAN backup, or application because it has affected the availability of the application. For example:

• A single corrupt data block in a user table that cannot be read by the application because of a bad spot of the physical disk

• A database that automatically shuts down because of the invalid blocks of a datafile in the SYSTEM tablespace caused by a failing disk controller

Regularly monitor application logs (which may be distributed across the data server, middle-tier and the client machines), the alert log, and Oracle trace files for errors such as ORA-1578 and ORA-1110

ORA-01578: ORACLE data block corrupted (file # 4, block # 26)
ORA-01110: data file 4: '/u01/oradata/objrs/obj_corr.dbf'

10.7.1.2 Recovering From Datafile Block Corruption

After you have identified datafile block corruption, follow these steps:

1. Determine the Extent of the Corruption Problem

2. Replace or Move Away From Faulty Hardware

3. Determine Which Objects Are Affected

4. Decide Which Recovery Method to Use

ORA-01578: ORACLE data block corrupted (file # 22, block # 12698)
ORA-01110: data file 22: '/oradata/SALES/users01.dbf'

SELECT tablespace_name, partition_name, segment_type,
   owner, segment_name
FROM dba_extents 
WHERE file_id = fid
  AND bid BETWEEN block_id AND block_id + blocks - 1;

SQL> select tablespace_name, partition_name, segment_type,
  2  owner, segment_name from dba_extents 
  3  where file_id=4 and 11 between block_id and block_id + blocks -1;

TABLESPACE_NAME   PARTITION_NAME     SEGMENT_TYPE    OWNER   SEGMENT_NAME
---------------   --------------     ------------    -----   ------------
USERS                                TABLE           SCOTT   EMP

10.7.2.1 Determine the Extent of the Media Failure

Use the following methods to determine the extent of the media failure:

• Gather details from error messages

  Gather the file number and file name from the error messages reported. Typical error messages are ORA-1157, ORA-1110 and ORA-1115, ORA-1110.

  For example, from the following error message:

ORA-01115: IO error reading block from file 22 (block # 12698)
    ORA-01110: data file 22: '/oradata/SALES/users01.dbf'

• Check log files for additional error messages

  Record additional error messages that appear in the system logs, volume manager logs, alert log, Oracle trace files, or application logs. Note that log files may be distributed across the data server, middle tier, and the client machines.

10.7.2.2 Replace or Move Away From Faulty Hardware

If there is a hardware fault or a suspect component, then it is sensible to either repair the problem or make disk space available on a separate disk subsystem before proceeding with a recovery option.

If there are multiple errors, if there are operating system-level errors against the affected file, or if the errors are transient and keep moving about, then there is little point in proceeding until the underlying problem has been addressed or space is available on alternative disks. Ask your hardware vendor to verify system integrity.

10.7.2.3 Decide Which Recovery Action to Take

The appropriate recovery action depends on what type of file is affected by the media failure. Table 10-5 shows the type of file and the appropriate recovery.

10.7.3.1 Use RMAN Datafile Media Recovery

Datafile media recovery recovers an entire datafile or set of datafiles for a database by using the RMAN RECOVER command. When a large or unknown number of data blocks are marked media-corrupt and require media recovery, or when an entire file is lost, the affected datafiles must be restored and recovered.

Use RMAN file media recovery when the following conditions are true:

• The number of blocks requiring recovery is large or unknown

• Block media recovery is not available (for example, if incomplete recovery is required, or if only incremental backups are available for the datafile requiring recovery)

  
  

  See Also: "Advanced User-Managed Recovery Scenarios" in Oracle Database Backup and Recovery Advanced User's Guide

10.7.3.2 Use RMAN Block Media Recovery

Block media recovery (BMR) recovers one or a set of data blocks marked "media corrupt" within a datafile by using the RMAN BLOCKRECOVER command. When a small number of data blocks are marked media corrupt and require media recovery, you can selectively restore and recover damaged blocks rather than whole datafiles. This results in lower mean time to recovery (MTTR) because only blocks that need recovery are restored and only necessary corrupt blocks undergo recovery. Block media recovery minimizes redo application time and avoids I/O overhead during recovery. It also enables affected datafiles to remain online during recovery of the corrupt blocks. The corrupt blocks, however, remain unavailable until they are completely recovered.

Use block media recovery when:

• A small number of blocks require media recovery and the blocks that need recovery are known. If a significant portion of the datafile is corrupt, or if the amount of corruption is unknown, then a different recovery method should be used.

• Blocks are marked corrupt (verified with the RMAN BACKUP VALIDATE command) and only when complete recovery is required.

Block media recovery cannot be used to recover from the following:

• User error or software bugs that cause logical corruption where the data blocks are intact. See "Recovering from User Error with Flashback Technology" for additional details for this type of recovery.

• Changes caused by corrupt redo data. Block media recovery requires that all available redo data be applied to the blocks being recovered.

The following are useful practices when using block media recovery:

• The flash recovery area should have a retention policy such that backups of all datafiles are retained on disk longer than the frequency of use of an Oracle tool that detects block corruption. For example, if RMAN BACKUP VALIDATE is run once a week, then the flash recovery area should have a retention policy greater than one week. This ensures that corrupt blocks can be recovered quickly using block media recovery with disk-based backups.

• Even if archived redo logs have missing redo data, block media recovery can still work if the block has been renewed since the data file backup used for the restoration of the block, or if there are no changes for the specific block in the missing redo data.

• If RMAN block validation has been run proactively, then the V$DATABASE_BLOCK_CORRUPTION view has a list of blocks validated as corrupt by RMAN. RMAN can be instructed to recover all corrupt blocks listed in V$DATABASE_BLOCK_CORRUPTION using block media recovery.

  
  

  See Also: Oracle Database Backup and Recovery Advanced User's Guide

10.7.3.3 Re-Create Objects Manually

Some database objects, such as small look-up tables or indexes, can be recovered quickly by manually re-creating the object instead of doing media recovery.

Use manual object re-creation when:

• You need to re-create a small index because of media corruption. Creating an index online enables the base object to be used concurrently.

• You need to re-create a look-up table or when the scripts to re-create the table are readily available. Dropping and re-creating the table may be the fastest option.

10.7.3.4 Use Data Guard to Recover From Data Failure

Failover is the operation of taking the production database offline on one site and bringing one of the standby databases online as the new production database. A database switchover is a planned transition in which a standby database and a production database switch roles.

Use Data Guard switchover or failover for data failure when:

• The database is down or when the database is up but the application is unavailable because of data failure, and the time to restore and recover locally is long or unknown.

• The business cost of a switchover or failover and re-creating the standby database is less than the cost of the expected downtime or reduced capacity while local recovery steps are taken.

• The proper decision to recover locally is unclear or too complex, and the data failure is affecting the availability of the application.

  
  

  See Also: "Database Failover" and "Database Switchover"

10.8.1.1 Flashback Query

Flashback Query, a feature introduced in the Oracle9i Database, enables an administrator or user to query any data at some point in time in the past. This powerful feature can be used to view and reconstruct data that may have been deleted or changed by accident. For example:

SELECT * FROM EMPLOYEES 
       AS OF TIMESTAMP 
       TO_DATE('28-Aug-03 14:00','DD-Mon-YY HH24:MI')
 WHERE ...

This partial statement displays rows from the EMPLOYEES table starting from 2 p.m. on August 28, 2003. Developers can use this feature to build self-service error correction into their applications, empowering end users to undo and correct their errors without delay, rather than burdening administrators to perform this task. Flashback Query is very simple to manage, because the database automatically keeps the necessary information to reconstruct data for a configurable time into the past.

10.8.1.2 Flashback Version Query

Flashback Version Query provides a way to view changes made to the database at the row level. It is an extension to SQL and enables the retrieval of all the different versions of a row across a specified time interval. For example:

SELECT * FROM EMPLOYEES
       VERSIONS BETWEEN TIMESTAMP
       TO_DATE('28-Aug-03 14:00','dd-Mon-YY hh24:mi') AND
       TO_DATE('28-Aug-03 15:00','dd-Mon-YY hh24:mi')
WHERE ...

This statement displays each version of the row, each entry changed by a different transaction, between 2 and 3 p.m. today. A DBA can use this to pinpoint when and how data is changed and trace it back to the user, application, or transaction. This enables the DBA to track down the source of a logical corruption in the database and correct it. It also enables application developers to debug their code.

10.8.1.3 Flashback Transaction Query

Flashback Transaction Query provides a way to view changes made to the database at the transaction level. It is an extension to SQL that enables you to see all changes made by a transaction. For example:

SELECT UNDO_SQL
FOMR DBA_TRANSACTION_QUERY 
WHERE XID = '000200030000002D';

This statement shows all of the changes that resulted from this transaction. In addition, compensating SQL statements are returned and can be used to undo changes made to all rows by this transaction. Using a precision tool like this, the DBA and application developer can precisely diagnose and correct logical problems in the database or application.

10.8.1.4 Example: Using Flashback Technology to Investigate Salary Discrepancy

Consider a human resources (HR) example involving the SCOTT schema. the HR manager reports to the DBA that there is a potential discrepancy in Ward's salary. Sometime before 9:00 a.m., Ward's salary was increased to $1875. The HR manager is uncertain how this occurred and wishes to know when the employee's salary was increased. In addition, he has instructed his staff to reset the salary to the previous level of $1250, and this was completed around 9:15 a.m.

The following steps show how to approach the problem.

1. Assess the problem.

   Fortunately, the HR manager has provided information about the time when the change occurred. We can query the information as it was at 9:00 a.m. with Flashback Query.

   SELECT EMPNO, ENAME, SAL
   FROM EMP
   AS OF TIMESTAMP TO_DATE('03-SEP-03 09:00','dd-Mon-yy hh24:mi')
   WHERE ENAME = 'WARD';
   
    EMPNO     ENAME             SAL
   ---------- ---------- ----------
         7521 WARD             1875
   
   

   We can confirm we have the correct employee by the fact that Ward's salary was $1875 at 09:00 a.m. Rather than using Ward's name, we can now use the employee number for subsequent investigation.

2. Query past rows or versions of the data to acquire transaction information.

   Although it is possible to restrict the row version information to a specific date or SCN range, we decide to query all the row information that we have available for the employee WARD using Flashback Version Query.

   SELECT EMPNO, ENAME, SAL, VERSIONS_STARTTIME, VERSIONS_ENDTIME
   FROM EMP
   VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
   WHERE EMPNO = 7521
   ORDER BY NVL(VERSIONS_STARTSCN,1);
   
       EMPNO ENAME             SAL VERSIONS_STARTTIME     VERSIONS_ENDTIME
   -------- ---------- ---------- ---------------------- ----------------------
        7521 WARD             1250 03-SEP-03 08.48.43 AM  03-SEP-03 08.54.49 AM
        7521 WARD             1875 03-SEP-03 08.54.49 AM  03-SEP-03 09.10.09 AM
        7521 WARD             1250 03-SEP-03 09.10.09 AM
   
   

   We can see that WARD's salary was increased from $1250 to $1875 at 08:54:49 the same morning and was subsequently reset to $1250 at approximately 09:10:09.

   Also, we can modify the query to determine the transaction information for each of the changes effecting WARD using a similar Flashback Version Query. This time we use the VERSIONS_XID pseudocolumn.

   SELECT EMPNO, ENAME, SAL, VERSIONS_XID
   FROM EMP
   VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
   WHERE EMPNO = 7521
   ORDER BY NVL(VERSIONS_STARTSCN,1);
   
        EMPNO ENAME             SAL VERSIONS_XID
   ---------- ---------- ---------- ----------------
         7521 WARD             1250 0006000800000086
         7521 WARD             1875 0009000500000089
         7521 WARD             1250 000800050000008B
   
   

3. Query the erroneous transaction and the scope of its impact.

   With the transaction information (VERSIONS_XID pseudocolumn), we can now query the database to determine the scope of the transaction, using Flashback Transaction Query.

   SELECT UNDO_SQL
   FROM FLASHBACK_TRANSACTION_QUERY
   WHERE XID = HEXTORAW('0009000500000089');
   
   UNDO_SQL                                                                    
       ----------------------------------------------------------------------------
   update "SCOTT"."EMP" set "SAL" = '950' where ROWID = 'AAACV4AAFAAAAKtAAL';      
   update "SCOTT"."EMP" set "SAL" = '1500' where ROWID = 'AAACV4AAFAAAAKtAAJ';     
   update "SCOTT"."EMP" set "SAL" = '2850' where ROWID = 'AAACV4AAFAAAAKtAAF';      
   update "SCOTT"."EMP" set "SAL" = '1250' where ROWID = 'AAACV4AAFAAAAKtAAE';    
   update "SCOTT"."EMP" set "SAL" = '1600' where ROWID = 'AAACV4AAFAAAAKtAAB';     
                                                                                   
       6 rows selected.
   
   

   We can see that WARD's salary was not the only change that occurred in the transaction. The information that was changed for the other four employees at the same time as WARD can now be passed back to the HR manager for review.

4. Determine if the corrective statements should be executed.

   If the HR manager decides that the corrective changes suggested by the UNDO_SQL column are correct, then the DBA can execute these statements individually.

10.8.2.1 Flashback Table

Flashback Table provides the DBA the ability to recover a table, or a set of tables, to a specified point in time quickly and easily. In many cases, Flashback Table alleviates the need to perform more complicated point in time recovery operations. For example:

FLASHBACK TABLE orders, order_items 
      TO TIMESTAMP 
      TO_DATE('29-AUG-03 14.00.00','dd-Mon-yy hh24:mi:ss');

This statement rewinds any updates to the ORDERS and ORDER_ITEMS tables that have been done between the current time and specified timestamp in the past. Flashback Table performs this operation online and in place, and it maintains referential integrity constraints between the tables.

10.8.2.2 Flashback Drop

Dropping or deleting database objects by accident is a common mistake. Users soon realize their mistake, but by then it is too late and there has been no way to easily recover the dropped tables and its indexes, constraints, and triggers. Objects once dropped were dropped forever. Loss of very important tables or other objects (like indexes, partitions or clusters) required DBAs to perform a point-in-time recovery, which can be time-consuming and lead to loss of recent transactions.

Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle places it in a recycle bin. Objects in the recycle bin remain there until the user decides to permanently remove them or until space limitations begin to occur on the tablespace containing the table. The recycle bin is a virtual container where all dropped objects reside. Users can look in the recycle bin and undrop the dropped table and its dependent objects. For example, the employees table and all its dependent objects would be undropped by the following statement:

FLASHBACK TABLE employees TO BEFORE DROP;

10.8.3.1 Flashback Database

To bring an Oracle database to a previous point in time, the traditional method is point-in-time recovery. However, point-in-time recovery can take hours or even days, since it requires the whole database to be restored from backup and recovered to the point in time just before the error was introduced into the database. With the size of databases constantly growing, it will take hours or even days just to restore the whole database.

Flashback Database is a new strategy for doing point-in-time recovery. It quickly rewinds an Oracle database to a previous time to correct any problems caused by logical data corruption or user error. Flashback logs are used to capture old versions of changed blocks. One way to think of it is as a continuous backup or storage snapshot. When recovery needs to be performed the flashback logs are quickly replayed to restore the database to a point in time before the error and just the changed blocks are restored. It is extremely fast and reduces recovery time from hours to minutes. In addition, it is easy to use. A database can be recovered to 2:05 p.m. by issuing a single statement. Before the database can be recovered, all instances of the database must be shut down and one of the instances subsequently mounted. The following is an example of a FLASHBACK DATABASE statement.

FLASHBACK DATABASE TO TIMESTAMP TIMESTAMP'2002-11-05 14:00:00';

No restoration from tape, no lengthy downtime, and no complicated recovery procedures are required to use it. You can also use Flashback Database and then open the database in read-only mode and examine its contents. If you determine that you flashed back too far or not far enough, then you can reissue the FLASHBACK DATABASE statement or continue recovery to a later time to find the proper point in time before the database was damaged. Flashback Database works with a production database, a physical standby database, and a logical standby database.

These steps are recommended for using Flashback Database:

1. Determine the time or the SCN to which to flash back the database.

2. Verify that there is sufficient flashback log information.

   SELECT OLDEST_FLASHBACK_SCN, 
      TO_CHAR(OLDEST_FLASHBACK_TIME, 'mon-dd-yyyy HH:MI:SS') 
      FROM V$FLASHBACK_DATABASE_LOG;
   
   

3. Flash back the database to a specific time or SCN. (The database must be mounted to perform a Flashback Database.)

   FLASHBACK DATABASE TO SCN scn;
   
   

   or

   FLASHBACK DATABASE TO TIMESTAMP TO_DATE date;
   
   

4. Open the database in read-only mode to verify that it is in the correct state.

   ALTER DATABASE OPEN READ ONLY;
   
   

   If more flashback data is required, then issue another FLASHBACK DATABASE statement. (The database must be mounted to perform a Flashback Database.)

   If you want to move forward in time, issue a statement similar to the following:

   RECOVER DATABASE UNTIL [TIME | CHANGE] date | scn;
   
   

5. Open the database.

   
   

   Caution: After you open the database, you will not be able to flash back to an SCN before the resetlogs SCN, so ensure that the database is in the correct state before issuing the ALTER DATABASE OPEN RESETLOGS statement.

   ALTER DATABASE OPEN RESETLOGS;
   
   

Other considerations when using Flashback Database are as follows:

• If there are not sufficient flashback logs, then use Data Guard if available or restore from backups.

• After flashing back a database, any dependent database such as a standby database needs to be flashed back. See Chapter 11, "Restoring Fault Tolerance".

10.8.3.2 Using Flashback Database to Repair a Dropped Tablespace

Flashback Database does not automatically fix this problem, but it can be used to dramatically reduce the downtime. You can flash back the production database to a point before the tablespace was dropped and then restore a backup of the corresponding datafiles from the affected tablespace and recover to a time before the tablespace was dropped.

Follow these recommended steps to use Flashback Database to repair a dropped tablespace:

1. Determine the SCN or time you dropped the tablespace.

2. Flash back the database to a time before the tablespace was dropped. You can use a statement similar to the following:

   FLASHBACK DATABASE TO BEFORE SCN drop_scn;
   
   

3. Restore, rename, and bring datafiles online.

   1. Restore only the datafiles from the affected tablespace from a backup.

   2. Rename the unnamed files to the backup files.

   ALTER DATABASE RENAME FILE '.../UNNAMED00005' to 'restored_file';
   
   

4. Bring the datafiles online.

   ALTER DATABASE DATAFILE 'name' ONLINE; 
            
   

5. Query and recover the database.

   SELECT CHECKPOINT_CHANGE# FROM V$DATAFILE_HEADER WHERE FILE#=1;
   RECOVER DATABASE UNTIL CHANGE checkpoint_change#;
   
   

6. Open the database.

   ALTER DATABASE OPEN RESETLOGS;