Oracle Database Recovery

This document describes the procedure to perform a production "Point In Time Recovery" of an Oracle 7 (or higher) database.

To begin, hopefully the backup set will contain a text file with a name similar to "ora_#####.trc" - this file is produced by the ALTER DATABASE BACKUP CONTROLFILE TO TRACE; SQL statement. This text file specifies the full paths of all required files to recover the database.

In addition to a number of other statements in this file, a CREATE CONTROLFILE will be found. This SQL statement will have two main sections - one describing redo logfile groups, and one describing datafiles. The datafile section is of most interest.

Below is an example of the file:

CREATE CONTROLFILE REUSE DATABASE "PRDQRY" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 1
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 (
    '/pkg/prdqry/oracle/data/redo1.log',
    '/pkg/prdqry/data/redo1.log'
  ) SIZE 64M,
...
DATAFILE
  '/pkg/prdqry/data/system_d01.dat',
  '/pkg/prdqry/data/rollback_segs_d01.dat',
  '/pkg/prdqry/data/temp_d01.dbf',
  '/pkg/prdqry/data/users_d01.dat',
...
  '/pkg/prdqry/data/rollback_segs_d02.dat',
  '/pkg/prdqry/data/index16m-f.dat',
  '/pkg/prdqry/data/index16m-g.dat'
;

All of the datafiles must be present in the specified location for a complete recovery (an incomplete recovery is described below). If the datafiles cannot be placed in the path described in the tracefile, SQL must be issued to specify a new location (described below). Do not place any files in the locations described for redo logfile groups. The files from the backup media may be compressed; if so, a decompression tool must be used to return them to their original state. gzip, bzip2, and 7-zip have been used for compression; the first two methods are usually included in most Linux systems, and 7-zip is available from 7-zip.org (or the EPEL repository) - please note that 7-zip only works on Linux and Windows, so the files must be decompressed either on Linux or Windows with 7-zip, then transferred to HP-UX or other legacy UNIX platforms if necessary.

The database (SID) can be renamed by modifying parameters to the CREATE CONTROLFILE. Renaming the database will always require a resetlogs. For example, the following SQL is used to clone a production database to development with SET to change the name, the required RESETLOGS, and NOARCHIVELOG to disable the redolog recording for recovery (unnecessary with a development database):

CREATE CONTROLFILE REUSE SET DATABASE "LIMSDEV" RESETLOGS NOARCHIVELOG

If an incomplete recovery is to be done (perhaps of a single table), the irrelevant datafiles can be skipped. Please note that the datafiles are always required when they compose the system tablespace, tablespaces involving rollback segments or "undo" (version 9 rollback), and the tablespace for the desired table (and optionally tablespaces involving indexes). The tablespace holding a table or index can be found by querying the TABLESPACE_NAME column of the DBA_TABLES and DBA_INDEXES dictionary views. The datafiles composing a tablespace can be found in DBA_DATA_FILES.

When the datafiles are in place, a controlfile must also be put into position. The backup controlfile is usually included in the backup set under the name CONTROL.BAK; it is created with SQL of the form ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/CONTROL.BAK';. RMAN is also known to to create controlfile backups along with datafiles for the SYSTEM tablespace (check any compressed SYSTEM datafiles for an included control file). The location of the controlfile is specified in another Oracle configuration file - the init.ora, or a file included from there. The init.ora can be found at the following full path: $ORACLE_HOME/dbs/init$ORACLE_SID.ora (where ORACLE_SID and ORACLE_HOME are the full name of the database and its home directory; in this example, prdqry is the SID and /pkg/prdqry/oracle is the HOME). Within the init.ora (or a file it includes) there is a configuration directive of the following form:

control_files           = (/pkg/prdqry/data/control01.ctl,
                           /pkg/prdqry/oracle/data/control02.ctl)

The backup controlfile must be renamed and copied to each of the locations specified in the init.ora.

The controlfile for a standby database might also be included, normally named STANDBY.CTL. The benefit of the standby controlfile (as opposed to a backup controlfile) is that new tablespace datafiles can be dynamically recreated during the recovery, and need not be present as long as the standby controlfile was created before the new datafiles appeared.

Also, if you are on Oracle 9 or higher, a binary "stored parameter file" might be in use instead of the init.ora. Since it is more convenient to work with an init.ora in a recovery situation, convert the stored parameter file to an init.ora with CREATE PFILE='/tmp/initSID.ora' FROM SPFILE='/path/to/stored/parameter/file.ora';.

When using the standby controlfile, the init.ora paramter *.STANDBY_FILE_MANAGEMENT=auto must be present for dynamic datafile recreation.

When the controlfile, all the datafiles, and a parameter file are in the desired positions, the database recovery can begin with commands similar to those below (user-entered commands are in red) which are typically issued by the "oracle" userid in UNIX:

hx0012:oracle> svrmgrl

Oracle Server Manager Release 2.3.4.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.4.1 - Production
With the distributed option
PL/SQL Release 2.3.4.4.1 - Production

SVRMGR> connect internal
Connected.
SVRMGR> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SVRMGR> startup mount force
ORACLE instance started.
Total System Global Area      54923768 bytes
Fixed Size                       38984 bytes
Variable Size                 15301040 bytes
Database Buffers              39321600 bytes
Redo Buffers                    262144 bytes
Database mounted.
SVRMGR> recover database until time '2008-05-12:19:00:00' using backup controlfile;

Notes:

If any files are not in the proper positions, an error will be issued indicating what is missing. If you are performing an incomplete recovery, these files can be removed from the recovery session by issuing SQL of the following form:

alter database datafile '/path/to/unneeded/file.dat' offline drop;

Once again, be sure not to drop datafiles composing the system tablespace, any tablespace involving rollback/undo segments, or the datafiles holding your desired table (and optionally its indexes). Also, the controlfile trace, mentioned above, can be edited to produce the list of datafiles to be dropped.

If you have placed any of the datafiles in a new location, the controlfile must be updated with SQL of the following form:

alter database rename file '/old/path/oldfile.dat' to '/new/path/newfile.dat';

After you have successfully entered your RECOVER DATABASE SQL command and the recovery finds all needed datafiles, you will be asked to supply archived redo with a prompt similar to the one below:

ORA-00279: change 6749127112286 generated at 07/03/2008 07:05:37 needed for thread 1
ORA-00289: suggestion : /pkg/prdcrm3/oracle/prdcrm3-arch/T0001S0000162906.ARC
ORA-00280: change 6749127112286 for thread 1 is in sequence #162906
Specify log: {=suggested | filename | AUTO | CANCEL}

To recover the database, every archived redo logfile generated from the beginning of the backup (the earliest backed up file) to the recovery point in time must be provided. Also note that you cannot use datafile backups from the "future" - the times that all datafile backups were created must be before the selected recover database point in time.

When you have all archived logfiles in place, enter "AUTO" to begin the recovery.

When the recovery is complete, the sequence will terminate like so:

ORA-00279: change 6749127396055 generated at 07/03/2008 07:16:31 needed for thread 1
ORA-00289: suggestion : /pkg/prdcrm3/oracle/prdcrm3-arch/T0001S0000162907.ARC
ORA-00280: change 6749127396055 for thread 1 is in sequence #162907
Log applied.
Media recovery complete.

At this point, the database can be opened. If you are working with a version 8i or higher database, it can be opened in READ ONLY mode, examined, then closed for further recovery. This option is not available on Oracle 7.

Here is an example of a non-invasive READ ONLY call to open the database on version 8i (this command works with either a backup or a standby controlfile):

alter database open read only;

To open the database for read/write with a backup controlfile, enter the following:

alter database open resetlogs;

To open the database for read/write with a standby controlfile, enter the following:

alter database activate standby database;

When the above option is executed, all of the redo logfile groups (mentioned above in the controlfile trace) will be erased and initialized. If any of the locations are inaccessible, the open will fail with an error message (soft links are an easy solution when mount points differ on a separate recovery machine, and the previous ALTER DATABASE RENAME FILE also works). If the command is successful, the previous version of the database will be available for full read/write access.