19 Advanced User-Managed Recovery Scenarios

Recovering After the Loss of Datafiles: Scenarios

If a media failure affects datafiles, then the recovery procedure depends on:

• The archiving mode of the database: ARCHIVELOG or NOARCHIVELOG

• The type of media failure

• The files affected by the media failure

Recovering Through an Added Datafile with a Backup Control File: Scenario

If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.

For example, suppose the following sequence of events occurs:

You may see the following error when applying the CREATE TABLESPACE redo data:

ORA-00283: recovery session canceled due to errors 
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 11: '/oracle/oradata/trgt/test02.dbf'
ORA-01110: data file 10: '/oracle/oradata/trgt/test01.dbf'

To recover through an ADD DATAFILE operation, use the following procedure:

1. View the files added by selecting from V$DATAFILE. For example:

   SELECT FILE#,NAME 
   FROM V$DATAFILE;
   
   FILE#           NAME
   --------------- ----------------------
   1               /oracle/oradata/trgt/system01.dbf
   .
   .
   .
   10               /oracle/oradata/trgt/UNNAMED00001
   11               /oracle/oradata/trgt/UNNAMED00002
   

   If multiple unnamed files exist, then determine which unnamed file corresponds to which datafile by using one of these methods:

   • Open the alert_SID.log, which contains messages about the original file location for each unnamed file.

2. Derive the original file location of each unnamed file from the error message and V$DATAFILE: each unnamed file corresponds to the file in the error message with the same file number.

   Issue the ALTER DATABASE RENAME FILE statement to rename the datafiles. For example, enter:

   ALTER DATABASE RENAME FILE '/db/UNNAMED00001' TO
                              '/oracle/oradata/trgt/test01.dbf';
   ALTER DATABASE RENAME FILE '/db/UNNAMED00002' TO
                              '/oracle/oradata/trgt/test02.dbf';
   

   Continue recovery by issuing the previous recovery statement. For example:

   RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
   

Re-Creating Datafiles When Backups Are Unavailable: Scenario

If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:

• All archived log files written after the creation of the original datafile are available

• The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

  Note:

  You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo is not available.

To re-create a datafile for recovery:

Recovering Through RESETLOGS with Created Control File: Scenario

You can recover backups through an OPEN RESETLOGS so long as:

• You have a current, backup, or created control file that knows about the prior incarnations

• You have all available archived redo logs

If you need to re-create the control file, the trace file generated by ALTER DATABASE BACKUP CONTROLFILE TO TRACE will contain the necessary commands to re-construct the complete incarnation history. The V$DATABASE_INCARNATION view displays the RESETLOGS history known to the control file, while the V$LOG_HISTORY view displays the archived log history.

It is possible for the incarnation history to be incomplete in the in re-created control file. For example, archived logs necessary for recovery may be missing. In this case, it is possible to create incarnation records explicitly with the ALTER DATABASE REGISTER LOGFILE statement.

In the following example, you register four logs that are necessary for recovery but are not recorded in the re-created control file, and then recover the database:

ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_42343523.arc';
ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_34546466.arc';
ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_23435466.arc';
ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_12343533.arc';
RECOVER AUTOMATIC DATABASE;

Recovering NOLOGGING Tables and Indexes: Scenario

You can create tables and indexes with the CREATE TABLE AS SELECT statement. You can also specify that the database create them with the NOLOGGING option. When you create a table or index as NOLOGGING, the database does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even if you are running in ARCHIVELOG mode.

Be aware that when you perform media recovery, and some tables or indexes are created normally whereas others are created with the NOLOGGING option, the NOLOGGING objects are marked logically corrupt by the RECOVER operation. Any attempt to access the unrecoverable objects returns an ORA-01578 error message. Drop the NOLOGGING objects and re-create them if needed.

Because it is possible to create a table with the NOLOGGING option and then create an index with the LOGGING option on that table, the index is not marked as logically corrupt after you perform media recovery. The table was unrecoverable (and thus marked as corrupt after recovery), however, so the index points to corrupt blocks. The index must be dropped, and the table and index must be re-created if necessary.

Recovering Read-Only Tablespaces with a Backup Control File: Scenario

If you have a read-only tablespace on read-only or slow media, then you may encounter errors or poor performance when recovering with the USING BACKUP CONTROLFILE option. This situation occurs when the backup control file indicates that a tablespace was read/write when the control file was backed up. In this case, media recovery may attempt to write to the files. For read-only media, the database issues an error saying that it cannot write to the files. For slow media, such as a hierarchical storage system backed up by tapes, performance may suffer.

To avoid these recovery problems, use current control files rather than backups to recover the database. If you need to use a backup control file, then you can also avoid this problem if the read-only tablespace has not suffered a media failure.

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Media Recovery of Transportable Tablespaces: Scenario

The transportable tablespace feature of Oracle allows a user to transport a set of tablespaces from one database to another. Transporting a tablespace into a database is like creating a tablespace with preloaded data. Using this feature is often an advantage because:

• It is faster than using the Export or SQL*Loader utilities because it involves only copying datafiles and integrating metadata

• You can use it to move index data, hence avoiding the necessity of rebuilding indexes

Like normal tablespaces, transportable tablespaces are recoverable. However, while you can recover normal tablespaces without a backup, you must have a version of the transported datafiles in order to recover a transported tablespace.

To recover a transportable tablespace, use the following procedure:

You may see the error ORA-01244 when recovering through a transportable tablespace operation just as when recovering through a CREATE TABLESPACE operation. In this case, rename the unnamed files to the correct locations using the procedure in "Recovering Through an Added Datafile with a Backup Control File: Scenario".

Recovering After the Loss of Online Redo Log Files: Scenarios

If a media failure has affected the online redo logs of a database, then the appropriate recovery procedure depends on the following:

• The configuration of the online redo log: mirrored or non-mirrored

• The type of media failure: temporary or permanent

• The types of online redo log files affected by the media failure: current, active, unarchived, or inactive

Table 19-1 displays V$LOG status information that can be crucial in a recovery situation involving online redo logs.

Recovering After the Loss of Archived Redo Log Files: Scenario

If the database is operating in ARCHIVELOG mode, and if the only copy of an archived redo log file is damaged, then the damaged file does not affect the present operation of the database. The following situations can arise, however, depending on when the redo log was written and when you backed up the datafile.

Recovering from a Dropped Table: Scenario

One not-uncommon error is the accidental dropping of a table from your database. In general, the fastest and simplest solution is to use the flashback drop feature, described inOracle Database Backup and Recovery Basics, to reverse the dropping of the table. However, if for some reason, such as flashback drop being disabled or the table having been dropped with the PURGE option, you cannot use flashback table, you can create a copy of the database, perform point-in-time recovery of that copy to a time before the table was dropped, export the dropped table using an Oracle export utility, and re-import it into your primary database using an Oracle import utility.

In this scenario, assume that you do not have the flashback database functionality enabled, so FLASHBACK DATABASE is not an option, but you do have physical backups of the database.

To recover a table that has been accidentally dropped, use the following procedure:

Performing Media Recovery in a Distributed Environment: Scenario

How you perform media recovery depends on whether your database participates in a distributed database system. The Oracle distributed database architecture is autonomous. Therefore, depending on the type of recovery operation selected for a single damaged database, you may have to coordinate recovery operations globally among all databases in the distributed system.

Table 19-2, "Recovery Operations in a Distributed Database Environment" summarizes different types of recovery operations and whether coordination among nodes of a distributed database system is required.

Dropping a Database with SQL*Plus

You may need to remove a database, that is, the database files that form the database, from the operating system. For example, this scenario can occur when you create a test database and then no longer have a use for it. The SQL*Plus command DROP DATABASE can perform this function.