An instance is a set of processes through which the Oracle database talks to shared memory and the shared memory that Oracle allocates for its use. Often, there is more than one instance on a single system. When an Oracle instance is opened, the database connected to it becomes available.

On a Unix/Linux system, an instance can be identified by a set of processes with the pattern ora_ function_SID, where function is a string indicating which Oracle function the process applies to and SID is the instance name. On Windows, each Oracle SID has its own service named OracleService SID, where SID is the instance name.

On a Unix/Linux system, an instance is automatically started with the dbstart script and shut down with the dbshut script. You can use these scripts or SQL*Plus commands to manually stop and start Oracle. In Unix or Linux, you have to place this script into the appropriate directory and add the appropriate symbolic links to have it run automatically after a reboot. To automatically start and stop instances in Windows, you should enable automatic start in the Control Panel for the appropriate OracleService. (If you cannot find OracleService SID in the Control Panel, you can create it using the oradim utility.) You then need to tell Oracle to automatically start the database when the service is started. You can do this by right-clicking on the SID in the Administrative Assistant; choosing Startup/Shutdown Options; choosing the Oracle Instance tab; and selecting “Start up instance when service is started,” “Shut down instance when service is stopped,” or both.

The database is what most people think about when they are using Oracle. That’s because the database contains the data! A database is a collection of files that contain tables, indexes, and other important database objects. Unless you’re using Oracle Real Application Clusters (RAC), there is a one-to-one relationship between instances and databases. Without Oracle RAC, a database connects to only one instance, and an instance mounts only one database. RACs run multiple instances (most likely on multiple servers) that share a single database.

That is why Oracle DBAs often use the two terms interchangeably. Technically, though, the instance is a set of processes through which the database talks to Oracle’s shared memory segments whereas the database is the collection of files that contain the data.

A table is a collection of related rows that all have the same attributes. There are three types of tables in Oracle: relational, object, and XML.

A database index is analogous to an index in a book: it allows Oracle to find data quickly. Again, an Oracle index is the same as anyone else’s index, and it presents no unique backup requirements. An index is a derived object; it is created based on the attributes in another table so that it can be recreated during a restore, and it’s usually faster to recreate an index than to restore it. Oracle has several types of indexes, including normal, bitmap, partitioned, function-based, and domain indexes.

Oracle 8 has special datatypes called BLOB, CLOB, and BFILE for storing large objects such as text or graphics. The BLOB and CLOB datatypes present no special backup requirements because they are stored within the database itself. (A BLOB typically contains image files, and a CLOB normally contains text data.) However, the BFILE datatype stores only a pointer inside the database to a file that actually resides somewhere in a filesystem on the database server. This aspect requires some special attention during backups. See the section “LOB space” in Chapter 15 for more information.

An object is any type of database object, such as a table or an index; it’s really a generic term rather than an Oracle-specific term. Unfortunately, Oracle also uses the term “object” to refer to reusable components created by object-oriented SQL programming. Here, it is used simply as a generic way to refer to any type of table, index, or other entity within Oracle.

A row is a collection of related attributes, such as all the information about a specific customer. Oracle also may refer to this as a record.

An attribute is any specific value (also known as a “column” or “field”) within a row.

A block is the smallest piece of data that can be moved within the database. Oracle allows a custom block size for each instance; the size can range from 2 K to 32 K, and each tablespace (defined later) can have its own block size. A block is what is referred to as a page in other RDBMSs.

An extent is a collection of contiguous Oracle blocks that are treated as one unit. The size of an extent varies based on a combination of factors, the most important of which is the tablespace storage allocation method defined at tablespace creation.

A segment is the collection of extents dedicated to a database object. (An object is a table, index, etc.) Depending on the type of object, extents may be allocated or taken away to meet the storage needs of a given table. Oracle8 had the rollback segment, but this has been replaced in later versions with undo segments, which are stored in an undo tablespace. (See the later sections “Tablespace” and “Undo tablespace.”)

An Oracle datafile can be stored on either a raw (disk device) or cooked (filesystem) file. Once they are created, the syntax to work with raw and cooked datafiles is the same. However, if rman is not used to back up the Oracle datafiles, backup scripts do have to take the type of datafile into account. If the backup script is going to support datafiles on raw partitions, it will need to use dd or some other command that can back up a raw partition. Using cp or tar will not work because they support only filesystem files.

Each Oracle datafile contains a special header block that holds that datafile’s system change number (SCN). Each transaction is assigned an SCN, the SCN in each datafile is updated every time a change is made to that datafile, and the control file keeps track of the current SCN. When an instance is started, the current SCN is checked against the SCN markers in each datafile. (See the section “Control file ” later in this chapter.)

The tablespace is the virtual area into which a DBA creates tables and other objects. It consists of one or more datafiles and is created by the create tablespace tablespace_name datafile device command. A tablespace may contain several tables. The space that each object (e.g., table) occupies within that tablespace is a segment (see the earlier definition of segment).

As of 10g, every Oracle database has at least three tablespaces: system, sysaux, and undo. The system tablespace stores the data dictionary, PL/SQL programs, view definitions, and other types of instance-wide information. The sysaux tablespace stores non-system-related tables and indexes that traditionally were placed in the system tablespace. Its name implies that it is the auxiliary system tablespace. The undo tablespace contains the undo segments, which replaced rollback segments. When it comes to backup and recovery, the main difference between these tablespaces and the rest of the tablespaces is that they must be recovered offline. That is because the instance cannot be brought online without these tablespaces. Other tablespaces can be recovered after the instance has been brought online.

A table or index can be divided into chunks called partitions and spread across multiple tablespaces for performance and availability reasons. As long as you are backing up all tablespaces, partitioned tables do not present any challenges to backup and recovery.

The control file is a database (of sorts) that keeps track of the status of all the files that comprise a given database and maintains rman backup metadata. It knows about all tablespaces, datafiles, archive logs, and redo logs within the database. It also knows the current state of each of these files by tracking each object’s SCN. Every transaction is assigned an SCN, and every time a change is made, the SCN gets updated both in the control file and in each datafile. (See the earlier section “Datafile.”) That way, when the instance is starting up, the control file has a record of what SCN the file should be at, and it checks that against the SCN that the file has. This is how it “notices” that a file is older than the control file and in need of media recovery. Also, if an older control file is put in place, Oracle will see that the SCN of the datafiles is higher than those that it has recorded in the control file. That’s when Oracle displays the datafile is more recent than the controlfile error.

If you’re performing user-managed backups, control files should be backed up using both the backup controlfile to filename and backup controlfile to trace commands. If you’re using rman, you can use the backup controlfile command, or you can have rman automatically back up the control file every time you run a backup by setting the controlfile autobackup parameter to on. You should also issue the backup controlfile to trace command within sqlplus. Additionally, it’s a good practice to copy the results of this command to a known location to make it easy to find during recovery. There is a scenario that we will cover in the “Recovering Oracle” section where this trace output will come in quite handy. Restoring control files is a bit tricky. The mechanics of restoring control files are covered later in the section “Recovering Oracle.”

It is best to avoid having to restore or rebuild a control file. The best way to do this is to mirror your control files within Oracle via a function Oracle calls multiplexing. This allows you to create two or three copies of the control file, each of which is written to every time the control file is updated.

Multiplexing/mirroring is slightly different from disk-level mirroring and offers an additional level of protection. With disk-level mirroring, you have one control file that’s mirrored to multiple disks via RAID 1. This protects you from disk failure, but not human error. If someone accidentally deleted the control file, it would instantly be deleted on each disk it was mirrored on. Storing multiplexed/mirrored control files on separate disks protects you from disk failure as well as accidental deletion. If a single control file gets deleted or corrupted, the instance becomes inoperable. However, the deleted/corrupted control file can easily be restored via one of the multiplexed copies, and the instance can be returned to proper operation.

A transaction is any activity by a user or a DBA that changes one or more attributes in an Oracle database. (A set of commands that ends with a commit statement is treated as one transaction.) Logically, a transaction modifies one or more attributes, but what eventually occurs physically is a modification to one or more blocks within the Oracle database.

Undo data is now used for three purposes, the first of which is to undo an aborted transaction or a transaction that was not yet committed when the database crashed. Undo information also provides a consistent read for long-running queries. (See the section “ACID Compliance” in Chapter 15.) Finally, undo information can be used by Oracle’s flashback feature, which allows you to manually undo transactions. For these reasons, it’s important to keep undo information as long as possible.

Prior to 9i, undo was managed using rollback segments. The biggest challenge with rollback segments was trying to figure out how large they should be. If you made them too large, you wasted space. If you made them too small, you would get the dreaded ORA-01555 snapshot too old error, indicating that a long-running query was not able to obtain the consistent read it needed. If you had a very active database with a lot of long transactions or long running queries, this could prove to be a real problem.

Oracle 9i introduced automatic undo management, or AUM. You can continue to use manual undo management using rollback segments, but once you understand the value of AUM, it’s hard to understand why you wouldn’t use it. When configuring an Oracle database, you now specify an undo tablespace. Oracle automatically creates undo segments in this tablespace instead of requiring you to manually create rollback segments. AUM eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten. Oracle strongly recommends that you use undo tablespaces to manage undo rather than rollback segments.

At first, it’s hard to differentiate between a tablespace dedicated to rollback segments and a tablespace dedicated to undo segments. Perhaps an explanation of how Oracle manages the automatic deletion of older undo data will help.

You can specify a minimum amount of time that undo information is to be kept using the undo_retention parameter. When Oracle needs space for more undo data, Oracle does its best to honor that value by deleting the oldest expired undo data first. However, if the undo tablespace is out of space, Oracle may begin deleting unexpired undo information, which unfortunately can result in the same snapshot too old error that you could experience with rollback segments. However, if you enable autoextend on the undo tablespace, the tablespace automatically extends to the maximum size you have specified. This automatic space management is what makes AUM so popular with those who have used it.

A checkpoint is the point at which all changed data that is kept in memory is flushed to disk. In Oracle, a DBA can force a checkpoint with the alter system checkpoint command, but a checkpoint also is done automatically every time the database switches to a different online redo log group.

The components that create different backup and recovery-related files (e.g., rman, alter database backup controlfile, alter system switch logfile) have no knowledge of each other or of the space available in the filesystem in which they’re storing data.

One of the big reasons to upgrade to Oracle 10g is its flash recovery area, which solves this problem by automating management of backup-related files. Choose a location on disk and an upper limit for storage space, and set a retention policy that governs how long backup files are needed for recovery, and the database automatically manages the storage used for backups, archived redo logs, and other recovery-related files for your database within that space. To create a flash recovery area, specify a value for the db_recovery_file_dest and db_recovery_file_size parameters in your startup file. To have archive logs sent to the flash recovery area, specify location = use_db_recovery_file_dest as the value for one of your log_archive_dest_ n parameters.

If the undo tablespace or rollback segments contain the information to roll back a transaction, the redo log contains the information to “roll forward” transactions. Every time that Oracle needs to change a block on disk, it records the change vector in the redo log; that is, it records how it changed the block, not the value it changed it to. A mathematical explanation may be helpful here. Suppose that you had a variable with a value of 100 and added 1 to it. To record the change vector, you would record +1; to record the changed value, you would record 101. This is how Oracle records information to the redo logs during normal operation. As explained in the later section “Debunking Hot-Backup Myths,” it switches to recording the changed value when a tablespace is in hot-backup mode.

In times of recovery, the redo log is used to redo (or replay) transactions that have occurred since the last checkpoint or since the backup that is being used for a restore. Oracle can be configured to use both online redo logs and offline (archived) redo logs.

Originally, the online redo logs were a few (typically three) files to which Oracle wrote the logs of each transaction. The problem with this approach is that the log to which Oracle was currently writing always contained the only copy of the most recent transaction logs. If the disk that this log was stored on were to crash, Oracle would not be able to recover up to the point of failure. This is why, in addition to multiplexing/mirroring the control file, Oracle also supports multiplexing/mirroring redo logs as well. Instead of using individual redo logs, you can now write redo information to a log group. A log group is a set of one or more files that are written to simultaneously by Oracle—essentially a mirror for the redo logs. Believe it or not, you’re only required to have one member of each log group. Obviously, if you don’t have multiple members of the log group, though, you’re not helping yourself much. The usual practice is to put three members of each log group on three separate disks—often the same disks that you’re multiplexing your control files to. The separate files within a log group are referred to as members. Each log group is treated as a single logfile, and all transaction records are simultaneously written to all disks within the currently active log group.

Now, instead of three or more separate files, any one of which could render the database useless if damaged, there are three or more separate log groups of multiplexed/mirrored files. If each log group is assigned more than one member, every transaction is being recorded in more than one place. After a crash, Oracle can read any one of these members to perform crash recovery.

Oracle writes to the log groups in a cyclical fashion. It writes to one log group until that log group is full. It then performs a log switch and starts writing to the next log group. As soon as this happens, the log group that was just filled is then copied to an archived redo logfile, if running in archivelogmode and automatic archiving is enabled. If archivelogmode is not enabled, this file is not copied and is simply overwritten the next time that Oracle needs to write to that log. If automatic archiving is not enabled, the instance hangs the next time Oracle needs to write to an unarchived redo log.

For nonenterprise customers, each of the online redo logs is copied to the filename pattern specified by the value in one or more log_archive_dest_ n parameters in the parameter file, followed by an incremented string specified by the log_archive_format parameter in the parameter file. For example, assume that log_archive_dest_0 is set to /archivelogs/arch and log_archive_format is set to %s .log, where %s is Oracle’s variable for the current sequence number. If the current sequence number is 293, a listing of the archivelogs directory might show the following:

# cd /archivelogs/arch
# ls -l arch*
arch291.log
arch292.log
arch293.log

The log_archive_dest_ n parameter is an enhanced version of the log_archive_dest parameter. Where log_archive_dest could be set to only one destination, you can have multiple log_archive_dest_ n parameters, each of which can be set to a directory or to the flash recovery area by specifying db_recovery_file_dest. (See the section “Flash recovery area” earlier in this chapter.)

Depending on how much activity a database has, the archive log destination directory may have hundreds of files over time. If you send archive logs directly to a directory, Oracle does not manage this area; however, if you’re sending archive logs to the flash recovery area, Oracle manages the space for you. If you’re managing the space, a cron job must be set up to clean up archive log destinations. As long as these files are being backed up to some kind of backup media, they can be removed after a few days. However, the more logs there are on disk, the better off the database will be because it may sometimes be necessary to restore from a backup that is not the most current. (For example, this could happen if the current backup volume is damaged.) If all the archive logs since the time the old backup was taken are online, it’s a breeze. If they aren’t, they have to be restored as well. That can create an available-space problem for many environments. This is why I recommend having enough space to store enough archive logs to span two backup cycles. For example, if the system does a full database backup once a night, there should be enough space to have at least two days’ worth of redo logs online. If it backs up once a week, there should be enough storage for two weeks’ worth of transaction logs. (This is yet another reason for backing up every night.)

In summary, the online redo logs are usually three or more log groups that Oracle cycles through to write the current transaction log data. A log group is a set of one or more logs that Oracle treats as one redo log. Log groups should have more than one member; more than one member means there is little chance for data corruption in case of disk failure. Once Oracle fills up one online redo log group, it copies that redo log to the archive log destination as a separate file with a sequence number contained in the filename. It makes this copy only if you are running in archivelog mode and automatic archiving is enabled.

There are a number of initialization parameters in Oracle that are important to know during recovery, so it’s vital to learn where those parameters are stored both in and outside of the database.

Historically, these initialization parameters were stored in a text file named init<SID>.ora. You could change most of the parameters inside the database, but if you wanted them to survive a reboot, you also had to make the changes in the init<SID>.ora file.

Oracle 9i introduced the concept of a server parameter file, or spfile for short. An spfile is a binary file named spfile<SID>.ora that is usually stored in the ORACLE_HOME/dbs directory. It cannot be edited, but it can be directly controlled by Oracle, allowing dynamic configuration changes to be written to the spfile automatically so that they will survive a reboot. For those who are used to init<SID>.ora files, an spfile can be a bit of an adjustment because there’s nothing for you to edit. There are actually a few ways to change initialization parameters.

If the database is running, simply set the appropriate parameter via the alter system set parameter_name = value command. This action immediately changes the parameter in the running database and also stores it in the spfile. This is certainly easier than the old method of changing it in the database and the init.ora file. Some parameters, such as log_archive_start, cannot be changed this way. You must change them in the spfile and then restart.

If you want to change a parameter in the spfile without using the database to do it, you need to create a text version of the file, edit it, then import the text file into a new spfile. The following SQL command creates a pfile called pfilename from the current spfile:

SQL> create pfile='pfilename' from spfile

Once you’ve edited pfilename and made the appropriate changes, you can make a new spfile from the pfile using this SQL command:

SQL> create spfile from pfile='pfilename'

Oracle distinguishes between a restore and a recovery. A restore is meant in the traditional sense of the term; it returns the datafile to the way it looked when you last backed it up by reading that backup from tape or disk. You then initiate a recovery, which applies redo against that datafile to bring it up to the current point in time. This is also referred to as media recovery.

Yes, you can use rman without a recovery catalog. In fact, looking at the release notes shows that Oracle continues to enhance what you can do without a catalog. If you don’t use a catalog, rman data is stored in the control file. Storing your recovery information inside the thing you want to recover is never a good idea. It would be much better to have a centralized recovery catalog that contains all the recovery information for all instances. It’s also a good idea if this catalog is on a different server than any of the databases it is tracking.

It’s also much easier to create the catalog now by just using the create catalog command in rman. Large environments might want to use more than one rman catalog and back up each of those catalogs using an rman instance that’s connected to the other catalog. You might also consider performing a hot or cold backup of the rman catalog by some method that doesn’t use rman.

Each persistent parameter that you create is one less parameter that you have to specify on the command line. The ultimate goal here is that you would simply be able to tell rman to backup database, and everything else would come from these parameters. The parameters that can be set persistently can be viewed by using the show all command in an rman session. It will list all of the parameters and show you which of them you have customized, and which of them are still set to the default values. Here is the example shown in Oracle’s documentation:

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default

Using persistent parameters greatly simplifies your rman scripts. To create or modify these parameters, simply connect to rman and issue commands like those shown here:

RMAN> configure backup optimization on;
RMAN> configure device type disk backup type to compressed backupset;
RMAN>configure controlfile autobackup on;

You can create rman scripts to back up an entire database, a tablespace, a datafile, a control file, or an archive log. These scripts can be stored in the filesystem, or they can be stored in the rman catalog, allowing all instances using that catalog to use the scripts. Once stored there, they can be executed by connecting to the catalog and issuing the rman command run script_name.

For space reasons, we’ll cover only two very basic rman scripts that perform a full (level 0) and incremental (level 1) backup of the entire instance as well as switching logfiles and backing up any archived redo logs that were created during the backup. A full or level 0 backup backs up every byte of the database, whereas an incremental or level 1 backup backs up only those bytes that have changed since the last backup. This script assumes that you have controlfile_autobackup set to on.

Here is the rman command file used to perform a full backup. It creates a full (level 0) backup of the database to the default device. The plus archivelog option causes it to switch logfiles (using archivelog current) before the backup, back up any archived logs not backed up (if backup optimization is set to on), then back up the database, followed by another logfile switch and a backup of any redo logs that were created during the backup:

connect target userid/passwd@oracle_sid;
connect catalog userid/passwd@rman_sid;
backup incremental level 0 plus archivelog;

Substitute the appropriate values for userid, password, and the SIDs for the target and catalog databases. (The target database is the one to be backed up.)

This is an incremental companion to the full script shown previously. It performs an incremental backup of those bytes that have changed in the database since the level 0 backup was taken:

connect target userid /passwd@oracle_sid;
connect catalog userid /passwd@rman_sid;
backup incremental level 1 plus archivelog;

It’s possible that there is no known copy of the current control file. If this is the case and you weren’t using your control file to store rman backup history, it’s probably best to attempt to use the create controlfile script. If you have rman backup history in your control file, it’s probably best to actually restore your control file from backup.

To use the create controlfile script, proceed to Steps 4 through 7. To restore your control file from backup, proceed to Step 8.

If that’s not possible or probable, try the following procedure. First, make backups of all the control files. Then, one at a time, try copying every version of each control file to all the other locations—excluding the one that Oracle has already complained about because it’s obviously damaged.

For example, assume there are three control files: /a/control1.ctl, /b/control2.ctl, and /c/control3.ctl. The alert log says that the /c/control3.ctl is damaged, and because /a/control1.ctl and /b/control2.ctl have different modification times, there’s no way to know which one is good. Try the following steps:

First, make backup copies of all the files:

$ cp /a/control1.ctl /a/control1.ctl.sav
$ cp /b/control2.ctl /b/control2.ctl.sav
$cp /c/control3.ctl /c/control3.ctl.sav

Or, for a Windows system, run the following commands:

C: copy C:CONTROL01.CTL C:CONTROL01.SAV
C: copy D:CONTROL02.CTL D:CONTROL02.SAV 
C:copy E:CONTROL03.CTL E:CONTROL03.SAV

Second, try copying one file to all locations. No sense in copying from control3.ctl because it’s obviously damaged. Try starting with control1.ctl:

$ cp /a/control1.ctl /b/control2.ctl
$cp /a/control1.ctl /c/control3.ctl

Or, for a Windows system, run the following commands:

C: copy C:CONTROL01.CTL D:CONTROL02.CTL
C:copy C:CONTROL01.CTL E:CONTROL03.CTL

Now attempt a startup mount:

SQL> startup mount
Sun Jul 23 18:53:19 PDT 2006 alter database mount exclusive
Sun Jul 23 18:53:19 PDT 2006 ORA-00202: controlfile: '/a/control3.ctl'
ORA-27037: unable to obtain file status

This error says that the file that was copied to all locations is also damaged. Now try the second file, control2.ctl. This time we have to copy from backup since we overwrote the original with the last step.

$ cp /b/control2.ctl.sav /a/control1.ctl
$cp /b/control2.ctl.sav /a/control3.ctl

Or, for a Windows system, run the following commands:

C: copy D:CONTROL02.SAV C:CONTROL01.CTL
C:copy D:CONTROL02.SAV E:CONTROL03.CTL

Now attempt to do a startup mount:

SQL> startup mount;
ORACLE instance started.
Total System Global Area            5130648 bytes
Fixed Size                     44924 bytes
Variable Size                  4151836 bytes
Database Buffers                 409600 bytes
Redo Buffers                   524288 bytes
Database mounted.

It appears that control2.ctl was a good copy of the control file.

The very first step in this process is to find and restore the most recent backup of the control file.

If you’re using rman with a catalog and you’re backing up the control file explicitly, you can use the restore controlfile command:

RMAN> restore controlfile

If you’ve set controlfile autobackup to on, connect to rman, and issue the restore controlfile from autobackup command:

RMAN> restore controlfile from autobackup

If you’re performing user-managed backups, you need to issue the backup controlfile to filename command in sqlplus. Locate the backed up control file, and copy it to all of the locations and filenames shown when running a select value from v$parameter where name like "control_files".

Again, this backup control file should be more recent than the most recent database file in the instance. If this isn’t the case, Oracle will complain.

To find out if the control file is valid and has been copied to all the correct locations, attempt to start up the database with the mount option. (This is the same command from Step 1.) If you’re running rman, you can simply issue this command after your restore controlfile command:

RMAN> startup mount

If you’re performing user-managed backups, run the following command on the mounted, closed database:

SQL> startup mount;

Once this step has been completed, proceed to Step 9.

If you’re using rman, this section is easy. Just issue recover database as your next command. This single command handles recovering the database even if you’re using a backup control file.

RMAN> recover database;

Here’s an area where rman really shines. If recovery requires applying archived redo logfiles that have been backed up, rman automatically restores them from the appropriate location, applies them, then deletes them. The entire process occurs automatically, regardless of where the archived redo logs have to come from.

If you did not use a backup control file, you just need to open the database:

RMAN> alter database open;

If you did use a backup control file, you need to open the database with the resetlogs option:

RMAN> alter database open resetlogs;

Since recovering the database with a backup control file requires the alter database open resetlogs option, it never hurts to try recovering the database normally first:

SQL> recover database;

If the backup control file option is required, Oracle will complain:

ORA-00283: Recover session cancelled due to errors
...
ORA-01207: file is more recent than controlfile - old controlfile

If Oracle complains, you need to recover the database using the backup controlfile option. Attempt to recover the database using the following command on the mounted, closed database:

SQL> recover database using backup controlfile

If it works, the output will look something like Example 16-3.

ORA-00279: change 38666 generated at 03/14/06 21:19:05 needed for thread 1
ORA-00289: suggestion : /db/Oracle/admin/crash/arch/arch.log1_494.dbf
ORA-00280: change 38666 for thread 1 is in sequence #494

Oracle requests all archived redo logs since the time of the oldest restored datafile. For example, if the backup that was used to restore the datafiles was from three days ago, Oracle needs all archived redo logs created since then. Also, the first logfile that it asks for is the oldest logfile that it wants.

If you’re performing user-managed backups, and you need older archived redo logs, you have to restore them yourself. If you have to find and apply the logs yourself, the most efficient way to roll through the archived redo logs is to have all of them sitting uncompressed in the directory that Oracle suggests as the location of the first file. (This, of course, requires much more storage than the rman method.) If this is the case, simply enter auto at the prompt. Otherwise, specify alternate locations, or press enter as it asks for each one, giving time to compress or remove the files that it no longer needs.

If it is able to do so, Oracle automatically rolls through all the archived redo logs and the online redo log. It then says Media recovery complete.

However, once Oracle rolls through all the archived redo logs, it may prompt for the online redo log. It does this by prompting for an archived redo log with a sequence number that is higher than the most recent archived redo log available. This means that it is looking for the online redo log. Try answering its prompt with the names of the online redo logfiles that you have. Unfortunately, as soon as you give it a name it doesn’t like, it makes you start the recover database using backup controlfile command again.

For example, suppose that you have the following three online redo logs:

/oracle/data/redolog01.dbf
/oracle/data/redolog02.dbf
/oracle/data/redolog03.dbf

When you are prompting for an archived redo log that has a higher sequence number than the highest numbered archived redo log that you have, answer the prompt with one of these files (e.g., /oracle/data/redolog01.dbf ). If the file that you give it does not contain the sequence number it is looking for, you will see a message like the following:

ORA-00310: archived log contains sequence 2; sequence 3 required
ORA-00334: archive log: '/oracle/data/redolog01.dbf'

Oracle cancels the database recovery, requiring you to start it over. Once you get to the same prompt again, respond with a different filename, such as /oracle/data/redolog02.dbf. If it contains the recovery thread it is looking for, it responds with a message like the following:

Log applied.
Media recovery complete.

If, after trying all the online redo logs, Oracle still asks for a log that you do not have, simply enter cancel.

Once the media recovery is complete, the next step is to open the database. As mentioned earlier, when recovering the database using a backup control file, it must be opened with the resetlogs option. Do this by entering the following SQL command against the mounted, closed database:

SQL> alter database open resetlogs;

Later versions of Oracle allow you to create a control file with a resetlogs option. If this was done, you can simply open the database and do not need the resetlogs option here.

If you’re running a version of Oracle prior to 10g, you must take a backup immediately after recovering the database with the resetlogs option! Even if you’re running 10g, it would be good to take a backup as soon as you can. While 10g databases can recover through a resetlogs incident, you will have to redo the restore and recovery you just did.

It is best if it is a cold backup after shutting down the database, but a hot backup would be better than nothing. Just remember that if you allow the database to become operational, and something happens before you get it backed up again, there is a risk that:

If you’re using rman, and the attempt to open the database did not work, you have a big choice to make. You can continue to follow this procedure to determine what’s wrong and fix things one at a time, or you can issue two commands and be done. If the latter idea sounds better, try these two commands:

RMAN> restore database ;
RMAN>recover database ;

If you’re performing user-managed backups, or you’d like to perform the rest of these steps manually with rman, you now need to find out why the database wouldn’t open. The output varies depending on the condition. Here is a listing of what some of those conditions may be, accompanied by what the error might look like when that condition occurs:

A damaged datafile is actually very easy to recover from. This is a good thing because this occurs more often than any other problem. Remember that there is only one copy of each datafile, unlike online redo logs and control files that can be multiplexed/mirrored. So, statistically speaking, it’s easier to lose one datafile than to lose all multiplexed/mirrored copies of a log group or all multiplexed/mirrored copies of the control file.

Oracle also can recover parts of the database while other parts of the database are brought online. Unfortunately, this helps only if a partially functioning database is of any use to the users in your environment. Therefore, a database that is completely worthless unless all tables are available will not benefit from the partial online restore feature. However, if the users can use one part of the database while the damaged files are being recovered, this feature may help to save face by allowing at least partial functionality during an outage. (Because doing an online (partial) restore actually makes more work for you, you should seriously investigate whether this option will help if you use it—before you actually have to use it. That way, when you need to do a large restore, that question will already be answered.)

There are three types of datafiles as far as recovery is concerned:

The final type of datafile is a file contained within a required tablespace (system , sysaux, or undo tablespaces in 10g, and system in previous versions). This datafile cannot be recovered with the database online because the database cannot be brought online without it.

If all members of a log group are damaged, there is great potential for data loss. The entire database may have to be restored, depending on the status of the log group that was damaged and the results of some attempts at fixing it. This may seem like a broken record, but this is why multiplexing/mirroring the log groups is so important.

If the error refers to a damaged log group, one option is to proceed directly to Step 17. However, to verify that nothing else is wrong, you should probably read the rest of this step and proceed to the next one.

If any datafiles belonging to required tablespaces are damaged, then you have to restore and recover them offline. The database cannot be opened without them.

If the error refers to a required tablespace, one option is to proceed directly to Step 11. However, to verify that nothing else is wrong, you should probably read the rest of this step and proceed to the next one.

Since Oracle has to open the datafiles that contain this rollback segment before it can verify that the rollback segment is available, this error will not occur unless a datafile has been taken offline. If Oracle encounters a damaged datafile (whether or not it contains a rollback segment), it will complain about that datafile and abort the attempt to open the database.

Remember that a rollback segment is a special part of a tablespace that stores rollback information. Rollback information is needed in order to undo (or roll back) an uncommitted transaction. Since a crashed database almost always contains uncommitted transactions, recovering a database with a damaged rollback segment is a little tricky. As previously mentioned, a damaged datafile may be taken offline, but Oracle will not open the database without the rollback segment.

If the error indicates that there is a damaged rollback segment, proceed to Step 18.

Remember that Oracle will stop attempting to open the database as soon as it encounters an error with one file. This means, of course, that there could be other damaged files. If there is at least one damaged datafile, now is a good time to see whether other files are damaged. Detailed instructions on how to do that are provided in Step 5.

Once you know the names of all the damaged files, you can recover them as described next.

If any datafiles are restored from backup, the sqlplus or rman recover command is needed. These commands use the archived and online redo logs to redo any transactions that have occurred since the time that the backup of a datafile was taken. You can recover a complete database, a tablespace, or a datafile by issuing the commands recover database, recover tablespace tablespace_name, or recover datafile data_file_name, respectively.

With rman or user-managed backups, you can simply issue the command recover database. Both automatically figure out what needs to have media recovery applied to it and perform the appropriate recovery. The difference between rman and user-managed backups comes when you need an archived log that is available only on backup. If rman needs to obtain any archived redo logs from backup, it automates restoring them and deleting them as necessary. If you’re performing user-managed backups, you need to perform this step yourself.

You can also apply media recovery to an individual datafile by issuing the SQL commands below against a mounted, closed database:

SQL> recover datafile '/db/Oracle/a/oradata/crash/datafile01.dbf'

This command allows the restore of an older version of a datafile and uses redo to roll it forward to the point of failure. For example, if you took a backup of a datafile on Wednesday night, and that datafile was damaged on Thursday evening, you would restore that datafile from Wednesday night’s backup. Of course many transactions would have occurred since Wednesday night, making changes to the datafiles that you restored. Running the command recover [database|tablespace|datafile] reapplies those transactions to the restored datafile, rolling them forward to Thursday evening.

This recovery can work in a number of ways. After receiving the recover command, Oracle prompts for the name and location of the first archived redo log that it needs. If that log and all logs that have been made since that log are online, uncompressed, and in their original location, enter the word auto. This tells Oracle to assume that all files that it needs are online. It therefore can automatically roll through each log.

In order to do this, all files that Oracle will need must be online. First, get the name of the oldest file, because that is the first file it will need. That filename is displayed immediately after issuing the recover command:

ORA-00279: change 18499 generated at 02/21/06 11:49:56 needed for thread 1
ORA-00289: suggestion : /db/Oracle/admin/crash/arch/arch.log1_481.dbf
ORA-00280: change 18499 for thread 1 is in sequence #481
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

In the preceding example, the first file that Oracle needs is /db/Oracle/admin/crash/arch/arch.log1_481.dbf. Make sure that this file is online and not compressed or deleted. If it is deleted, restore it from backup. If it is compressed, uncompress it and any archived redo logfiles in that directory that are newer than it, because Oracle may need all of them to complete the media recovery. If you’re not using rman, it might be necessary to delete some of the older archived redo logs to make enough room for the files that need to be uncompressed. Once all archived redo logs that are newer than the one requested by Oracle have been restored and uncompressed, enter auto at the Specify log prompt. rman can be configured to restore only one redo log at a time from tape, apply that redo log, then delete it, removing the space issue. It can also be configured to keep a certain amount on disk using the maxsize parameter.

If there isn’t enough space for all of the archived redo logs to be uncompressed, a little creativity may be required. Uncompress as many as possible, and then press Enter each time it suggests the next file. (Pressing Enter tells Oracle that the file that it is suggesting is available. If it finds that it is not available, it prompts for the same file again.) Once it has finished with one archive log, compress that log, and uncompress a newer log, because it will be needed shortly. (Obviously, a second window is required, and a third window wouldn’t hurt!)

At some point, it may ask for an archived redo log that is not available. This could mean that some of the archived redo logs or online redo logs are damaged. If the file cannot be located or restored, enter cancel.

More details on media recovery are available in Oracle’s documentation.

If any of the damaged datafiles is a member of any required tablespaces, proceed to Step 12. If none of them is a member of any required tablespaces, proceed to Step 13.

Once the names of the datafiles that need to be restored are determined, restore them from the latest available backup. If you’re performing user-managed backups, you have to decide which files to restore and where to restore them from. If you’re using rman, you only need to issue the following command:

RMAN> restore database ;

Once they are restored, recovery within Oracle can be accomplished in three different ways. These ways vary greatly in complexity and flexibility. Examine the following three media recovery methods and choose whichever one is best for you.

If there are a small number of datafiles to recover, this may be the easiest option. As each file is restored, issue the recover datafile command against it, and then bring it online. The following commands work in rman or sqlplus:

recover datafile 'datafile_name' ;
alter database datafile 'datafile_name' online ;

The downside to this method is that media recovery may take a while for each datafile. If recovering multiple datafiles within a single tablespace, this is probably wasting time.

This is the hardest of the methods, but it may work faster than the previous method if there are several damaged datafiles within a tablespace. If forced to leave the partially functional database open while recovering the damaged datafiles, and there are several of them to recover, this is probably the best option.

First, find the names of all datafiles and the tablespace to which they belong. Since the database is now open, this can be done in one step, as demonstrated in Example 16-4.

SQL> select file_name, tablespace_name from dba_data_files;
Statement processed.
FILE_NAME
 TABLESPACE_NAME
--------------------------------------------------------------------------------
 ------------------------------
/db/oracle/a/oradata/crash/users01.dbf
 USERS
/db/oracle/a/oradata/crash/tools01.dbf
 TOOLS
/db/oracle/a/oradata/crash/temp01.dbf
 TEMP
/db/oracle/a/oradata/crash/rbs01.dbf
 RBS
/db/oracle/a/oradata/crash/system01.dbf
 SYSTEM
/db/oracle/a/oradata/crash/test01.dbf
 TEST

Once all of the datafiles are restored and the names of all the tablespaces that contain these datafiles have been determined, issue the recover tablespace command against each of those tablespaces. Before doing so, however, each tablespace must be taken offline, as shown in Example 16-5. The commands in Example 16-5 work in rman or sqlplus.

alter tablespace tablespace_name1 offline;
recover tablespace tablespace_name1 ;
alter tablespace tablespace_name1 online;
alter tablespace tablespace_name2 offline;
recover tablespace tablespace_name2 ;alter tablespace tablespace_name2 online;

It’s obvious that this method is quite involved! It’s not pretty, and it’s not easy, but it allows recovery of multiple tablespaces while the instance continues to operate. If a partially functioning database is of any value to the users, this method may be their best friend.

This is actually the easiest method, but it requires that the database be shut down to perform it. After restoring all the database files that were taken offline, close the database, and issue the recover database command.

Once all the database files are restored, issue the commands shown in Example 16-6. These commands will work in rman or sqlplus.

shutdown immediate ;
startup mount ;
recover database ;alter database open ;

To make sure that all tablespaces and datafiles have been returned to their proper status, run the commands shown in Example 16-7.

SQL> select name, status from v$datafile
NAME
 STATUS
--------------------------------------------------------------------------------
 -------
/db/oracle/a/oradata/crash/system01.dbf  SYSTEM
/db/oracle/a/oradata/crash/rbs01.dbf     ONLINE
/db/oracle/a/oradata/crash/temp01.dbf    ONLINE
/db/oracle/a/oradata/crash/tools01.dbf   ONLINE
/db/oracle/a/oradata/crash/users01.dbf   ONLINE
/db/oracle/a/oradata/crash/test01.dbf    ONLINE
6 rows selected.
SQL> select member, status from v$logfile
/oracle/product/10.2.0/oradata/orcl/redo03.log
/oracle/product/10.2.0/oradata/orcl/redo02.log
/oracle/product/10.2.0/oradata/orcl/redo01.log
SQL> select name, status from v$controlfile;
/oracle/product/10.2.0/oradata/orcl/control01.ctl
/oracle/product/10.2.0/oradata/orcl/control02.ctl
/oracle/product/10.2.0/oradata/orcl/control03.ctl

Example 16-7 shows that all datafiles, control files, and logfiles are in good condition. (In the case of the logfiles and control files, no status is good status.)