RE: Restoring tables/tablespaces

From: Steven Monaghan <MonaghaS_at_mscdirect.com>
Date: Mon, 25 Sep 2000 12:13:59 -0400
Message-Id: <10630.117757@fatcity.com>

Just pulled this down from Metalink today to add to my library:

Doc ID: Note:96197.1
Subject: Recovering a Dropped Table from a Full Database Backup Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 17-JAN-2000
Last Revision Date: 29-FEB-2000
Language: USAENG

PURPOSE

This bulletin outlines the steps to perform recovery to restore a dropped table without recovering the entire database. The bulletin assumes the reader is familiar with Oracle's recovery procedures which are documented in the Oracle documentation set.

SCOPE & APPLICATION

This bulletin discusses dropped table recovery using traditional (Oracle7) backup and recovery procedures. These can equally be applied to Oracle8. It does NOT discuss tablespace point in time recovery (TSPITR) or the Recovery Manager (RMAN) duplicate database feature.

The examples in this bulletin are UNIX-based, but can be easily applied to other platforms with little modification.

RECOVERING A DROPPED TABLE FROM A FULL DATABASE BACKUP

TERMINOLOGY

PROD machine - Host computer on which the production database runs. Also

denotes the ORACLE_SID of the production instance. TEST machine - Host computer, physically distinct from the machine on which the

               production database runs.
TEMP         - The ORACLE_SID of the instance used to access the restored
               database.

The backup - The set of backed up database structures (see REQUIREMENTS) used

               as the basis for recovering the dropped table. Once restored,
               this set of structures is referred to as the partially
restored
               database.

REQUIREMENTS

The following data structures must be available from the backup:
- All system tablespace datafiles

All rollback segment datafiles
All datafiles in which the table to recovered is stored
Archive logs - see Note (a) Notes: (a) If the restored datafiles are part of a hot backup, or you wish to roll them forward, the required archivelogs must be available.

In an ideal world the partially restored database will be made available for recovery on a TEST machine. However this may not always be possible, and it may be necessary to restore to the same machine on which the 'source' (PROD) database resides. The latter is NOT RECOMMENDED as a user error in the
recovery process can corrupt the production database.

If the datafiles comprising the partially restored database were in hot backup mode at the time the table was dropped, it is necessary to restore a previous backup. The reason for this is that the hot backup must be rolled forward past the 'end backup' markers (the time that the tablespaces were taken
out of hot backup mode) before the database can be opened. Failure to recover
the restored datafiles past their end backup markers will result in the following error when an attempt is made to open the database:

ORA-1195 "online backup of file %s needs more recovery to be consistent" Received on Mon Sep 25 2000 - 11:13:59 CDT