RMAN恢复

Typical Media Recovery Scenarios

The steps you take during a database recovery depend on the extent of the recovery and which of the files (datafiles, control files, online and archived redo logs) are missing due to a media problem. The following sections take you through several common recovery scenarios using RMAN and user-managed recoveries.

Complete Recovery of a Whole Database

You may have to perform a complete recovery of the whole database when you lose several or all of your datafiles. Before you recover the database, you must restore the backup files. Then you need to apply all the available archived redo logs to the database. In the following sections, you'll learn how to do this with RMAN and with user-managed techniques.

Using RMAN for Whole Database Recovery

Assume that all the datafiles in your database are inaccessible due to a media malfunction. If you have all your archived redo logs, you can restore your backups and do a complete recovery without any loss of data.

To recover an entire database, first start the database but leave it in the mount position, as shown in Listing 16-1. Thus, the database is not open to users while you're restoring files and recovering the database. (You can open the database if you are performing a tablespace recovery.)

Listing 16-1: Using RMAN to Start the Database

---

C:\> RMAN TARGET / CATALOG RMAN/RMAN1@NICK
Recovery Manager: Release 11.1.0.6.0 - Production on Mon Mar 31 11:25:29 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database

RMAN> startup mount
Oracle instance started
database mounted
. . .
RMAN>

---

Next, you need to restore the datafiles that are lost. Because this is the recovery of an entire database, you ask RMAN to restore all the datafiles from backup sets. The command is very simple: RESTORE DATABASE. RMAN knows where the backed-up files are on disk, and it copies them to their original locations. By default, RMAN will direct the server session to restore backups to the default location, overwriting any previous files that are already there. If you wish, you can have RMAN copy files to new locations by using the SET NEWNAME command, as shown here:

RMAN> SET NEWNAME FOR DATAFILE '?/oradata/trgt/tools01.dbf' TO '/tmp/tools01.dbf';
RMAN> RESTORE DATAFILE '?/oradata/trgt/tools01.dbf';

Listing 16-2 shows the output of the RESTORE DATABASE command.

Listing 16-2: The RMAN RESTORE DATABASE Command

---

RMAN> RESTORE DATABASE;
Starting restore at 29-MAR-08
Using channel ORA_DISK_1
channel ORA_DISK_1: sid=50 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSTEM01.DBF
. . .
channel ORA_DISK_1: restore complete
Finished restore at 29-MAR-08
RMAN>

---

Once RMAN restores all the datafiles, you need to synchronize them using the archived redo logs. The RECOVER DATABASE command applies the archived logs to the restored files and synchronizes the SCNs for all the datafiles and the control file. Listing 16-3 shows the output of the RECOVER DATABASE command.

Listing 16-3: The RMAN RECOVER DATABASE Command

---

RMAN> RECOVER DATABASE;

Starting recover at 29-MAR-08
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 12 is already on disk as file
. . .
media recovery complete
Finished recover at 29-MAR-08
RMAN>

---

Tip

When you use RMAN, you don't have to restore the archived redo logs—RMAN automatically applies archived redo logs as necessary during the recovery process.

Finally, you need to bring the database online so users can access it once again:

RMAN> ALTER DATABASE OPEN;
Database opened;
RMAN>

Note that you can simplify the preceding steps for recovering the whole database by using the following script:

RMAN> RUN {
      shutdown immediate;
      startup mount;
      restore database;
      recover database;
      alter database open;
      }
RMAN>

As you can see, RMAN makes the recovery of a database a breeze. You don't have to specify the location of any of the files that you need to restore. RMAN knows where to get the files from by looking in the recovery catalog (or the control file).

Performing a Hot Restore with RMAN

In the previous example, I showed how you first had to restore the datafiles before recovering the database. By default, when you use the RESTORE command, RMAN restores a datafile from an image backup or from a backup set if an image copy isn't available. Either way, you have to wait for RMAN to copy the file to its original location.

However, you don't have to copy the file to the original location. When you need to perform a fast recovery, you can save the time it takes to restore the datafiles by using the image copies directly. You use the special SWITCH command to let Oracle know that you are actually using the image copy for the lost datafile. You can thus skip the restore step and directly head to the recovery stage.

The SWITCH command makes the control file point to the copy of the datafile as the current datafile. This is the same as using the SQL statement ALTER DATABASE RENAME FILE. Note that the filename at the operating system level remains unchanged.

Here's how you use the SWITCH command:

RMAN> SWITCH DATABASE TO COPY;

The preceding command will perform a hot restore of your database.

Tip

Use the SWITCH DATABASE rather than the RESTORE DATABASE command if your goal is to restore as quickly as possible.

User-Managed Whole Database Recovery

The user-managed complete database recovery process starts with the restoration of all lost or damaged datafiles from the backup. You then recover the database by using the RECOVER DATABASE command. Oracle will ask for the necessary archived log files and perform the recovery by applying them. It's easier to let Oracle apply the relevant archived log file than to attempt to do it yourself manually.

You can automate the application of the archived redo log files in two ways. Before you use the RECOVER DATABASE command, you can use the SET AUTORECOVERY ON command. The other way is to specify the AUTOMATIC keyword in the RECOVER command, as in RECOVER DATABASE AUTOMATIC.

The following is a summary of steps required for a complete recovery of your database:

1. Restore the datafiles from backup.

2. Start up the database in the mount mode:

   SQL> STARTUP MOUNT;
   
   

3. Use the RECOVER DATABASE command to start recovering the database. The AUTOMATIC keyword tells Oracle to automate the application of the archived redo logs. In this example, I'm assuming that you're placing the archived redo logs in the default location specified in the init.ora file or SPFILE.

   SQL> RECOVER AUTOMATIC DATABASE;
   

   If you've placed them in a different location, you'll have to supply the location to Oracle by using the LOGSOURCE parameter of the SET statement, or the RECOVER FROM parameter of the ALTER DATABASE statement. Here are examples of each method of specifying an alternative location for the archived redo log files:

   SQL> SET LOGSOURCE /new_directory;
   SQL> ALTER DATABASE RECOVER FROM '/new_directory';
   

4. Open the database once you're sure Oracle has completed media recovery:

   Media recovery complete.
   SQL> ALTER DATABASE OPEN;
   

Recovering a Tablespace

You need to perform a tablespace recovery when you lose one or more datafiles that belong to the tablespace and you don't have a mirrored copy of the files. The recovery may be open or closed, and it may be a full recovery or a point-in-time recovery, as explained at the beginning of this chapter. You can recover using either RMAN or user-managed techniques.

Using RMAN to Recover a Tablespace

Sometimes you may have to recover a tablespace or a set of tablespaces. You can use the RESTORE and RECOVER commands at the tablespace level for these situations. Since only a part of the database is affected, you don't have to shut down the database—you can leave it open instead. If you wish, you can shut down the database in the mount mode, if several tablespaces or a single very large tablespace is affected.

Here are the recovery steps:

1. Take the tablespace you're going to recover offline. The rest of the database will be functioning normally after you do this:

   RMAN> ALTER TABLESPACE sysaux OFFLINE;
   

2. Restore the tablespace using the RESTORE TABLESPACE command, as follows:

   RMAN> RESTORE TABLESPACE sysaux;
   
   Starting restore at 29-MAR-08
   using channel ORA_DISK_1
   . . .
   channel ORA_DISK_1: restore complete
   Finished restore at 29-MAR-08
   RMAN>
   
   

3. Recover the tablespace, as follows:

   RMAN> RECOVER TABLESPACE sysaux;
   
   Starting recover at 29-MAR-08
   using channel ORA_DISK_1
   starting media recovery
   archive log thread 1 sequence 12 is already on disk as file
   . . .
   media recovery complete
   Finished recover at 29-MAR-08
   RMAN>
   

4. Finally, bring the recovered tablespace online, as follows:

   RMAN> ALTER TABLESPACE sysaux ONLINE;
   

User-Managed Recovery of a Tablespace

Say your database is online, and one or more files belonging to it are damaged. If the database writer can't write to the damaged files, Oracle will take the files offline automatically. Otherwise, you must first take the tablespace offline. Then you need to restore the damaged datafiles and perform a recovery.

Here's a summary of the recovery process:

1. Take the affected tablespace offline:

   SQL> ALTER TABLESPACE sales01 OFFLINE IMMEDIATE;
   

2. Restore the damaged files:

   SQL> HOST cp /u01/app/oracle/backup/shan/sales_01.dbf
                /u01/app/oracle/oradata/shan/sales_01.dbf
   

3. Recover the offline tablespace:

   SQL> RECOVER TABLESPACE sales01;
   

4. Bring the tablespace you just recovered online:

   SQL> ALTER TABLESPACE sales01 ONLINE;
   

Recovering a Datafile

The procedures for recovering from the loss of a datafile depend on the type of tablespace the datafile belongs to. You can use the dynamic performance view V$RECOVER_FILE to determine the files you need to recover.

Let's see what happens when your instance encounters media errors, assuming you are operating in the archivelog mode. If your instance encounters a read error and can't read a datafile, you'll see an operating system error stating this fact, but the database will continue to operate. When the database tries writing the file header during a checkpoint, a write error will be issued. If the instance encounters a write error and can't write to a System or undo tablespace datafile, the instance will immediately shut down.

If the write error pertains to any other tablespace, the database will take that datafile offline—the other datafiles in the tablespace containing this datafile will remain online. Your job then is to restore and recover the affected datafile.

The following discussion deals with the loss of a datafile from a non-System tablespace.

Using RMAN to Recover a Datafile

The recovery process using RMAN is much simpler than the user-managed recovery technique. First of all, you don't need to tell RMAN where to get the backup file from—it identifies the correct file from its recovery catalog. All you have to do is tell RMAN to restore and recover the necessary datafile.

RMAN restores the datafile(s) first and then performs the necessary recovery on the datafile(s) using the archived redo logs. RMAN knows what archived logs to apply to the restored datafile(s).

Let's use a RUN block to perform our datafile recovery, as shown in Listing 16-4. Recovering a datafile is a two-step process: to recover a datafile, you must first restore it from the RMAN backup. The RESTORE DATAFILE command asks RMAN to restore the necessary datafile. The RECOVER DATAFILE command that follows tells RMAN to perform recovery on the restored datafile.

Listing 16-4: Recovering a Datafile Using RMAN

---

RMAN> RUN {
2> restore datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSAUX01.DBF';
3> recover datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSAUX01.DBF';
4> }

starting full resync of recovery catalog
full resync complete
Starting restore at 12-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: restore complete
Finished restore at 12-JUL-08
Starting recover at 12-JUL-08
using channel ORA_DISK_1
starting media recovery
. . .
media recovery complete
Finished recover at 12-JUL-08
starting full resync of recovery catalog
full resync complete
RMAN>

---

Note that behind the scenes, RMAN automatically applies any necessary archive logs without prompting from you during the recovery step.

User-Managed Recovery of a Datafile

If the database instance crashes or can't be started without an error, as the result of a missing or damaged datafile, the identity of the datafile is obvious. However, you can lose a datafile and continue to have an open database. You can use the following statement to find out which files may need a recovery:

SQL> SELECT file#, status, error, recover, tablespace_name, name
     FROM V$DATAFILE_HEADER
     WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

The various possibilities that can be shown in the output of the preceding query can be interpreted as follows:

• If the query results in "no rows selected," then none of the datafiles need recovery.

• If the ERROR column shows NULL, and the RECOVER column says YES, you can recover without having to restore a copy of the datafile.

• If the ERROR column is not NULL, there may be a media problem. Similarly, if the RECOVER column doesn't show the value NO, there may be a problem with the disk.

• In all the previous cases, first check whether the problem is temporary and can be fixed without replacing the media. If the problem isn't temporary, you'll have to perform media recovery.

• A NULL value in the RECOVER column indicates a hardware error.

You can also use the following query of the V$RECOVER_FILE view to find out the file number, status, and other error information for datafiles:

SQL> SELECT file#, error, online_status, change#, time
     FROM V$RECOVER_FILE;

To recover from the loss of a datafile while the database is open, you must first take the affected tablespace offline. You must then restore the datafile from a backup and recover the tablespace. Here's a summary of the commands you need to use:

SQL> ALTER TABLESPACE sales01 OFFLINE IMMEDIATE;

SQL> HOST cp /test01/app/oracle/backup/sales01.dbf
    /test01/app/oracle/oradata/finance/sales01.dbf;

SQL> RECOVER TABLESPACE sales01;

SQL> ALTER TABLESPACE sales01 ONLINE;

The ALTER TABLESPACE OFFLINE and ONLINE commands ensure that users don't access the tablespace during the recovery process.

Incomplete Recovery

The previous examples dealt with complete-recovery scenarios. The database or the tablespace, as the case may be, are fully recovered, and there's no loss of data. You use incomplete recovery in situations where you want to recover to a previous point in time, perhaps because you made a data entry error or because an online redo log was lost. After recovery, you end up with a database that's not current to the latest point in time, but it is consistent. In the following sections, you'll see how to perform incomplete recovery using RMAN and user-managed recovery procedures.

Using RMAN for Incomplete Recovery

You can perform three types of incomplete recovery using RMAN, provided you are running your database in the archivelog mode. You can specify a time, SCN, or log sequence number with the SET UNTIL command before using the RESTORE and RECOVER commands. Your choice of recovery type depends on the problem that prompts the incomplete recovery.

• Time-based recovery: In this type of recovery, RMAN restores and recovers all files in the database up to a point in time. This is helpful if you know that a problem, such as the accidental dropping of a table, occurred at a certain point in time. You use the SET UNTIL command to perform a time-based recovery, as in this example:

  SET UNTIL TIME 'Mar 21 2005 06:00:00'
  

• Change-based SCN: You can perform the recovery up to a specific SCN if you know it. You use the keywords SET UNTIL SCN to specify that files up to that SCN be used. Here is an example:

  SET UNTIL SCN 1000
  
  

• Log sequence-based recovery: You can recover until a particular log sequence number. RMAN selects the files to recover up to but not including the specified sequence number. You use the SET UNTIL SEQUENCE command for a log sequence-based recovery:

  SET UNTIL SEQUENCE 9923
  

Let's look at an example of a time-based recovery within the current incarnation of the database. Assume that table test was accidentally dropped right before 6 p.m. Listing 16-5 shows the time-based recovery process.

Listing 16-5: A Time-Based Incomplete Recovery Using RMAN

---

RMAN> STARTUP MOUNT
RMAN> RUN
2> {set until time 'Jun 30 2008 18:00:00';
3> restore database;
4> recover database;
5> }
executing command: SET until clause
restoring datafile 00024 to /test02/app/oracle/oradata/temp_01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/test01/app/oracle/oradata/backup
/2ddp387s_1_1 tag=null params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 30-JUN-08
Starting recover at 30-JUN-08
using channel ORA_DISK_1
starting media recovery
media recovery complete
Finished recover at 30-JUN-08
RMAN> ALTER DATABASE OPEN RESETLOGS;
Database opened.
RMAN>

---

Note

For a point-in-time recovery to succeed, you must have backups of all datafiles from before the target point in time (or SCN). You must also have all archived redo logs for the period between the SCN of the backups and the target SCN.

In Listing 16-5, the database is first mounted but not opened. RMAN is asked to restore the database (meaning that it is asked to get the backed-up datafiles that are necessary for this restore). It then is asked to recover the database. RMAN knows which archived redo logs are needed based on the information about backups stored in its recovery catalog. RMAN applies the archived redo logs and finishes the recovery process. You can then open the database with the ALTER DATABASE OPEN RESETLOGS command. This is a point-in-time recovery, and you need to make sure that the database doesn't apply the old redo logs by mistake. You ensure this by resetting or reinitializing the redo log files.

Here's the entire script for performing a tablespace PITR using RMAN:

RMAN> RUN {
Allocate channel s1 type 'sbt_tape';
Allocate channel s2 type 'sbt_tape';
Set until time '28-JUL-08 06:00:00';
Restore database;

Recover database;
Sql "alter database open reset logs";
Release channel s1;
Release channel s2;
}

Once you query the database and verify that you have recovered it to the previous point in time, you can open the database using the following command, which will undo all changes after the point in time you've recovered to:

RMAN> ALTER DATABASE OPEN RESETLOGS;

The previous command will archive all online redo logs, reset the log sequence numbers, and give the online redo logs a new time stamp and SCN. You thus eliminate the possibility of corrupting your datafiles by mistakenly applying older redo logs.

If you want to use a specific log sequence number instead of a point in time, you modify the script by replacing the SET UNTIL TIME line with the following:

RMAN> SET UNTIL SEQUENCE 1234;

---

What is RESETLOGS?

Note that after you perform any kind of incomplete recovery, the logs are always reset. Essentially, the RESETLOGS option reinitializes the redo log files, erasing all the redo information they currently have, and resets the log sequence number to 1. To apply any archived redo logs to a datafile, the SCNs and time stamps in the database files have to match the SCNs and time stamps in the headers of the archived redo log files, and when you perform a RESETLOGS operation, the datafiles are stamped with new SCN and time stamp information, making it impossible for the older archived redo logs to be applied to them by mistake.

The RESETLOGS option is used under these circumstances:

• When you use a backup control file to recover

• When you perform an incomplete recovery, rather than a complete recovery

• When you recover using a control file created with the RESETLOGS option

If you were to do the incomplete recovery using an SCN, the SET UNTIL command would be modified as SET UNTIL SCN nnnn. If you were to use an archived log sequence number, the command would be SET UNTIL LOGSEQ=nnnn THREAD=nnnn, where LOGSEQ is the log you want to recover to.

---

Here's a short script that shows how to perform incomplete recovery using RMAN, where you specify an SCN:

RMAN> RUN
{
 ALLOCATE CHANNEL ch1 TYPE sbt;
 RESTORE DATABASE;
 RECOVER DATABASE UNTIL SCN 1000; # recovers through SCN 999
 ALTER DATABASE OPEN RESETLOGS;
}

Recovery Through Current and Ancestor Database Incarnations

Anytime you use the OPEN RESETLOGS command, the incarnation of the database changes and a new incarnation begins. The previous incarnation is termed an ancestor incarnation, and the latest is the current incarnation. RMAN can recover through multiple incarnations of a database. For example, if you have backups from an older incarnation of the database, you can use them to recover your current database incarnation, but you must specify that the backups are coming from a previous incarnation.

The Simplified Recovery Through Resetlogs feature lets you use archived redo logs from an earlier incarnation of the database. The default format for the LOG_ARCHIVE_FORMAT initialization parameter now includes a %r component, which stands for the RESETLOGS identifier. For example, on a UNIX/ Linux system, your archived redo logs will use the format log%t_%s_%r.arc. The variable t stands for the thread number, and the variable s is the log sequence number. The V$LOG_HISTORY view has two columns, RESETLOGS_CHANGE# and RESETLOGS_TIME, that indicate the database incarnation of the archived redo logs.

The point-in-time recovery example in the previous section dealt with recovery using the current incarnation of the database. Let's look at incomplete database recovery using a parent incarnation of the database. Suppose you want to specify an SCN that isn't in the current incarnation, but is in an ancestor incarnation. There are two requirements for this type of a point-in-time recovery:

• You must reset the current incarnation of the database back to the incarnation to which your target SCN belongs.

• You must use the control file from the older incarnation that contains the target SCN.

To perform point-in-time recovery to the older incarnation, use the following steps:

1. Find out the incarnation key for the incarnation that was current at the time you want to recover your database to. You can find it in the incarnation key column of the output of RMAN's LIST INCARNATION command. Let's say our incarnation key value for this example is 2.

2. Start the database in the following way:

   RMAN> STARTUP FORCE NOMOUNT;
   

3. Reset the current incarnation to the incarnation that was current at the point in time that you want to recover to:

   RMAN> RESET DATABASE TO INCARNATION 2;
   

4. Restore the old control file from a backup and mount the database with the following commands:

   RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
   RMAN> ALTER DATABASE MOUNT;
   

5. Restore and recover the database until the point in time or the SCN:

   RMAN> RESTORE DATABASE;
   RMAN> RECOVER DATABASE UNTIL SCN 1000;
   

6. Open the database after resetting the online log files:

   RMAN> ALTER DATABASE OPEN RESETLOGS;
   

Oracle calls the preceding type of recovery Simplified Recovery Through Resetlogs. This feature comes in handy when you perform a point-in-time recovery or a recovery using a backup control file and use the RESETLOGS option to open the database. In these cases, you can still use the backup from before the RESETLOGS operation.

User-Managed Incomplete Recovery

You have looked at how to use RMAN for incomplete recovery; let's look now at how to do it manually. Assume that your database is open and you have decided that you have to perform an incomplete recovery—you want to take the database back to a previous point. All changes since then are gone, whether you want it that way (because of user error, for example) or you're forced to do so (such as when you don't have all the archived redo logs needed for up-to-date recovery). Here's a brief summary of the steps you must take to perform an incomplete recovery:

1. Shut down the database immediately:

   SQL> SHUTDOWN ABORT;
   

2. Restore all the datafiles and make sure all of them are online.

3. Choose one of the following three commands to recover the datafiles, depending upon your situation:

   • Cancel-based recovery: Here, you let Oracle apply the archived redo logs until you cancel the recovery process. You could use this method, for example, when there is a gap in your archived redo logs. Here is the command you would use:

     SQL> RECOVER DATABASE UNTIL CANCEL;
     

   • Time-based recovery: You have to specify the point in time to which you want the database to be recovered. Here is an example:

     SQL> RECOVER DATABASE UNTIL TIME '2005-06-30:12:00:00';
     

     Or, if you're using a backed-up control file, you should use the following command instead of the preceding one:

     SQL> RECOVER DATABASE UNTIL TIME
     '2005-06-30:12:00:00' USING BACKUP CONTROLFILE;
     

   • Change-based recovery: In the change-based method, you need to find out what SCN you want to go back to, and specify it in the command:

     SQL> RECOVER DATABASE UNTIL CHANGE 27845;
     

4. No matter which of the three methods you use to perform your recovery, you must issue the following command when the recovery is complete, because this is an incomplete recovery:

   SQL> ALTER DATABASE OPEN RESETLOGS;
   

Recovering from the Loss of Control Files

Your instance will shut down immediately if one or all of the control files are inaccessible. Here are two possible scenarios:

• If even a single copy of the duplexed control file is lost, your instance will crash immediately. You then simply copy a duplexed control file to the same location as the lost or damaged control file. If you can't place it in the same location, update your parameter file (use the CONTROL_FILES parameter) to indicate the new location. If you can't replace the lost control file for some reason, just edit the initialization parameter file so it doesn't refer to the lost control file any longer. You can successfully start your instance now.

• If you've lost all your control files, you must restore a backup control file or create a new one. If you restore the control file from backup, you must perform media recovery of the whole database and then perform an OPEN RESETLOGS operation.

The following sections show how to recover from a situation where all your control files are lost. You'll have to recover using a backed-up control file.

Using RMAN to Recover from Control-File Loss

In this section, we'll simulate a control-file loss by deleting both the control files. Make sure you have a backup of the database, including the control files, before you do this.

Once you have deleted your control files, follow these steps:

1. Shut down the database and try to start it up. The instance will start and try to mount the database, but when it doesn't find the control files, the database fails to mount:

   RMAN> SHUTDOWN IMMEDIATE;
   database closed
   database dismounted
   Oracle instance shut down
   RMAN>
   RMAN> STARTUP
   Oracle instance started
   RMAN-00571:
   RMAN-00569: ERROR MESSAGE STACK FOLLOWS
   RMAN-00571:
   RMAN-03002: failure of startup command at 07/11/2008 17:18:05
   ORA-00205: error in identifying controlfile, check alert log for more info
   RMAN>
   

   You can avoid the preceding error messages by using the alternative command STARTUP NOMOUNT:

   RMAN> SHUTDOWN IMMEDIATE;
   database closed
   database dismounted
   Oracle instance shut down
   RMAN>
   RMAN> STARTUP NOMOUNT;
   connected to target database (not started)
   Oracle instance started
   . . .
   RMAN>
   

2. Issue the RESTORE CONTROLFILE command so RMAN can copy the control file backups to their default locations specified in the init.ora file:

   RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
   Starting restore at 14-JUL-08
   allocated channel: ORA_DISK_1
   . . .
   output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\CONTROL03.CTL
   Finished restore at 14-JUL-08
   RMAN>
   
   

3. After the restore is over, mount the database:

   RMAN> ALTER DATABASE MOUNT;
   database mounted
   RMAN>
   

4. Recover the database as shown in Listing 16-6.

   Listing 16-6: Using RMAN to Recover from the Loss of Control Files

   ---

   RMAN> RECOVER DATABASE;
   Starting recover at 14-JUL-08
   Starting implicit crosscheck backup at 14-JUL-08
   Crosschecked 5 objects
   Finished implicit crosscheck backup at 14-JUL-08
   Starting implicit crosscheck copy at 14-JUL-08
   Finished implicit crosscheck copy at 14-JUL-08
   searching for all files in the recovery area
   cataloging files...
   cataloging done
   starting media recovery
   media recovery complete
   Finished recover at 14-JUL-08
   RMAN>
   

   ---

Because RMAN restores the control files from its backups, you have to open the database with the RESETLOGS option:

RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>

User-Managed Recovery from Control-File Loss

If you've lost all your control files, you can create a brand-new control file by using the CREATE CONTROLFILE command. Listing 16-7 shows a typical control file creation statement derived using the output of the ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement. Here's the SQL statement that will get you the output necessary to run the CREATE CONTROLFILE statement later on:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL>

Listing 16-7: Recovering Lost Control Files with User-Managed Techniques

---

Dump file c:\oracle\product\10.1.0\admin\NICK\udump\NICK_ora_2452.trc
Sun Jul 10 16:35:47 2008
ORACLE Version 11.1.0.0.0 - Production vsnsta=0
The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NICK" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 2
    MAXDATAFILES 200
    MAXINSTANCES 1
    MAXLOGHISTORY 454
LOGFILE
  GROUP 1 'C:\ORACLE\PROD\11.1.0\ORADATA\NICK\REDO01.LOG' SIZE 100M,
  GROUP 2 'C:\ORACLE\PROD\11.1.0\ORADATA\NICK\REDO02.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  'C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSTEM01.DBF',
  'C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\UNDOTBS01.DBF',
  'C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSAUX01.DBF'
CHARACTER SET US7ASCII;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\11.1.0\
FLASH_RECOVERY_AREA\NICK\ARCHIVELOG\
2008_07_10\O1_MF_1_1_%U_.ARC';
-- ALTER DATABASE REGISTER LOGFILE 'C:\ORACLE\PRODUCT\11.1.0\
FLASH_RECOVERY_AREA\NICK\ARCHIVELOG\
2008_07_10\O1_MF_1_1_%U_.ARC';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- No tempfile entries found to add.

---

Tip

Even if you don't have a control file backup, you can easily create a new control file provided you have a complete list of all the datafiles and the redo log files that are part of the database.

After you issue the ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement, you can get a trace file as shown in Listing 16-7 from your trace directory, usually the udump directory.

As you can see, you can make up your own CREATE CONTROLFILE statement, with the catch being that you need to have an accurate record of all the component files of your database. Let's take a closer look at the control file creation script.

The script first starts up the database in nomount mode. Obviously, if you don't have the control files, you can't mount the database. The next line, which includes the CREATE CONTROLFILE statement, is the most critical one in the script. If you have all your redo log files intact, you have to specify the NORESETLOGS option so that Oracle can reuse the redo logs. Alternatively, if your redo logs are lost or damaged, you need to specify RESETLOGS in the CREATE CONTROLFILE statement. Oracle will create new redo files in this case, or if they exist, Oracle will reinitialize them, essentially creating a new set of redo log files. The REUSE parameter asks Oracle to overwrite any of the old control files if they exist in their default locations.

Listing 16-8 shows how to use the CREATE CONTROLFILE statement in Listing 16-7.

Listing 16-8: Creating New Control Files

---

SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size                  282576 bytes
Variable Size             83886080 bytes
Database Buffers          33554432 bytes
Redo Buffers                532480 bytes
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "NICK" NORESETLOGS ARCHIVELOG
. . .
Control file created.
SQL>
SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER SYSTEM ARCHIVE LOG ALL;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>

---

Recovering a Datafile Without a Backup

Suppose you add a new datafile and users consequently create some objects in it. Before you back up your database over the weekend, the new file is damaged and you need to recover the data. The archived redo logs since the last backup will contain the information regarding the lost file and will enable you to recover the data. The following sections illustrate the procedures involved.

Using RMAN to Recover a File Without a Backup

Suppose you first notice the damaged file when you access the lost or damaged file and get the following error:

SQL> CREATE TABLE x (name varchar2 (30));
create table x (name varchar2 (30))
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/test02/app/oracle/oradata/finance1/test01.dbf'

Here are the steps you would follow to fix the problem:

1. Take the affected datafile offline:

   RMAN> SQL "alter database datafile
      2> ''/test01/app/oracle/oradata/remorse/sales_01.dbf'' offline";
         sql statement: alter database datafile
         ''/test01/app/oracle/oradata/remorse/sales_01.dbf'' offline
   RMAN>
   
   

2. Create a new datafile with the same name as the damaged offline datafile:

   RMAN> sql "alter database create datafile
      2> ''/test02/app/oracle/oradata/remorse/sales01.dbf'' ";
         sql statement: alter database create datafile
         ''/test02/app/oracle/oradata/remorse/sales01.dbf"
   RMAN>
   

3. Recover the new datafile. RMAN will retrieve data from the archived redo logs, so the new datafile is identical to the one that was lost:

   RMAN> RECOVER DATAFILE '/test01/app/oracle/oradata/remorse/sales_01.dbf';
   Starting recover at 30-JUN-08
   using channel ORA_DISK_1
   using channel ORA_DISK_2
   using channel ORA_DISK_3
   using channel ORA_DISK_4
   starting media recovery
   media recovery complete
   Finished recover at 30-JUN-08
   RMAN>
   

4. Bring the new datafile online:

   RMAN> SQL "alter database datafile
      2> ''/test02/app/oracle/oradata/finance1/test01.dbf'' online";
         sql statement: alter database datafile
         ''/test02/app/oracle/oradata/remorse/sales01.dbf'' online
   RMAN> EXIT
   

User-Managed Recovery of a File Without a Backup

The manual procedure for recovering a file without prior backups is very straightforward, again assuming you have all the archived redo logs available. You first create a new file with the same name as the lost file, and then you use the archived logs (if necessary) to recover the data that was in that file.