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 all file locations for 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 - 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 if necessary.

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';". 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.

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 the controlfile and all the datafiles are in the desired positions, the database recovery can begin with commands similar to those below (user-entered commands are in bold) 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:

alter database open read only;

To open the database for read/write (the only option available on version 7), enter the following:

alter database open resetlogs;

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.