|
Category :
Database
Resources -> Backup and Recovery
DB Version
:
Oracle 8 / 8i / 9i
OS Details :
Sun Solaris9
There
are multiples ways to recovering a dropped table. You could do a
tablespace point in time recovery or use options available through
RMAN. This article explains the steps involved in recovering a dropped
table using the standard backup and recovery procedures. The
following conditions should be met for us to be able to recover the
table:
- The database should be in
archivelog mode.
- A valid offline or hot database backup should exist along with the
archivelog files.
Steps:
(1) On the production
database, connect as sysdba and create a controlfile trace. For
example:
SQL> connect / as sysdba
SQL> alter database backup controlfile to trace
resetlogs;
A trace file will be generated in the user_dump_dest directory. Rename
this trace file to prodcontrol.sql and save it.
(2) Create a directory on the Test or Development machine. The
restored database will be located in this directory. In this
example the directory is called /oradata/restore. The only stipulation
is that there is enough space to easily accommodate the restored
database and any archivelogs required for recovery.
(3) Restore the backup of system tablespace datafiles, all
rollback segment datafiles, all datafiles in which the table to be
recovered is stored and archivelog files to the /oradata/restore
directory.
(4) Copy the init.ora file (e.g initPRODDB.ora) from the
production database to the /oradata/restore directory on the Test or
development machine. This assumes that all database parameters are
specified in only the init.ora file.
(5) Rename the initPRODDB.ora to initTEMPDB.ora on the Test /
development machine.
(6) Edit the initTEMPDB.ora and make the following
modifications:
a. control_files = /oradata/restore/cntrlTEMP.dbf
b. archivelogs:
log_archive_destination
log_archive_format
c. %dump_dest parameters should be changed to point to
suitable directories
d. Disable remote login passwordfile.
(7) Ensure that the shell environment is set correctly. At the
very least, the following environment variables must be defined:
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMPDB)
(8) Connect as a privileged user. For example:
SQL> connect / as sysdba
(9) Start the instance, after setting ORACLE_SID to TEMPDB :
SQL> startup nomount
(10) The prodcontrol.sql file (created in Step 1) contains the
syntax necessary to
create a new controlfile. Copy this script to the Test / development
machine and edit it to save only the text between (and including)
'CREATE CONTROLFILE' and it's terminating semi-colon. Modify the
following sections:
a. LOGFILE. Edit this section to reflect the names of the
online logs to
be created for the TEMPDB instance. If the
PRODDB instance has a large
number of large online log members, it is
advisable to specify a
reduced number of smaller log members. You
MUST however specify at
least two online log groups.
b. DATAFILE. Edit this section to reflect the names of the
restored
datafiles only.
c. Miscellaneous:
- Remove the REUSE keyword
- Optionally change the ARCHIVELOG keyword
to NOARCHIVELOG (so the
TEMP database will not operate
in archivelog mode)
An example of the completed prodcontrol.sql script might be:
CREATE CONTROLFILE DATABASE "PRODDB" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 200
MAXINSTANCES 1
MAXLOGHISTORY 400
LOGFILE
GROUP 1 '/oradata/restore/logs1PRODDB.dbf'
SIZE 10M,
GROUP 2 '/oradata/restore/logs2PRODDB.dbf'
SIZE 10M
DATAFILE
'/RESTORE/sysPRODDB.dbf',
'/RESTORE/rbsPRODDB.dbf',
'/RESTORE/usersPRODDB.dbf';
Then execute prodcontrol.sql script to create control file(s).
(11) Recover the database. The database is to be recovered to a
time before the table was dropped. There are two options here:
a. Time-based incomplete recovery. Database recovery is
stopped at the
specified date and time.
b. Cancel-based incomplete recovery. Database recovery is
stopped on
an archivelog boundary i.e. the granularity
of cancel-based recovery
is the archivelog.
Example of time-based recovery:
SQL> recover database until time
'2005-10-10:10:00:00' using backup controlfile
In the above example, apply archivelogs as requested.
Recovery will stop
automatically at 10:00 on October 10th, 2005.
Example of cancel-based recovery:
SQL> recover database until cancel using
backup controlfile
As soon as you have applied the last desired archivelog, type
CANCEL
to stop recovery.
(12) Open the database with the RESETLOGS option:
SQL> alter database open resetlogs;
(13) Export the necessary table(s).
(14) Once the necessary tables have been exported, the TEMPDB
instance can be shutdown and all associated files removed. It is
worthwhile verifying that the table has all the required rpws before
removing the TEMPDB instance.
|
