I. RECOVERY ON A TEST MACHINE:
이 부분이 다른 서버에서 복구하는 것이구요.
II. RECOVERY ON A PRODUCTION MACHINE:
이 부분이 동일 서버에서 인스턴스 하나 띄워서 복구하는 것입니다.
문서 보시고 하면 됩니다.
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"
I. RECOVERY ON A TEST MACHINE:
------------------------------
The following steps are to be used when performing recovery on a TEST machine,
the assumption being that there is no instance with the ORACLE_SID "PROD"
or "TEMP" already running on the TEST machine.
1. On the PROD database, connect as a privileged user and create an ASCII
dump of the controlfile. For example:
SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;
A trace file will be generated in the user_dump_dest destination. Rename
this trace file to ccf.sql and save it.
2. Create a directory on the TEST machine. The restored database will be
located in this directory. In this example the directory is called
/RESTORE. The only stipulation is that there is enough space to easily
accomodate the restored database and any archivelogs required for recovery.
If filesystem space is at a premium, the archivelogs may be optionally
restored to /RESTORE as required by recovery, and removed after they have
been applied.
3. Restore the backup to the /RESTORE directory.
4. Optionally, restore all archivelogs required for recovery to the /RESTORE
directory.
5. Copy the init.ora file (initPROD.ora) from the PROD database to the /RESTORE
directory on the TEST machine. This assumes that all database parameters are
specified in only the init.ora file. If an include files (ifile) is
referenced, also copy this file to /RESTORE.
6. Rename the initPROD.ora to initTEMP.ora on the TEST machine.
7. Edit the initTEMP.ora and make the following modifications:
a. control_files = /RESTORE/cntrlTEMP.dbf
b. if applicable, change any ifile references to point to the copied
include file
c. log_archive% parameters should be changed to reflect the restored
archivelogs:
log_archive_destination
log_archive_format
For example:
log_archive_destination=/RESTORE
log_archive_format=arch_%s.dbf
d. %dump_dest parameters should be changed to point to suitable directories
e. If audit is on, turn it off.
8. 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 TEMP)
9. Connect as a privileged user. For example:
SVRMGR> connect / as sysdba
10. Start the instance, specifying TEMP's init.ora:
SVRMGR> startup nomount pfile=/RESTORE/initTEMP.ora
11. The ccf.sql file (created in Step 1) contains the syntax necessary to
create a new controlfile. Copy this script to the TEST 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 TEMP instance. If the PROD 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 ccf.sql script might be:
CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 20
MAXINSTANCES 1
MAXLOGHISTORY 337
LOGFILE
GROUP 1 '/RESTORE/log1PROD.dbf' SIZE 1M,
GROUP 2 '/RESTORE/log2PROD.dbf' SIZE 1M
DATAFILE
'/RESTORE/sys1PROD.dbf',
'/RESTORE/rbs1RBS.dbf',
'/RESTORE/users1PROD.dbf'
;
Then execute ccf.sql script to create control file(s).
12. 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 cance-based recovery
is the archivelog.
Example of time-based recovery:
SVRMGR> recover database until time '2000-01-10:12:00:00' using backup controlfile
In the above example, apply archivelogs as requested. Recovery will stop
automatically at 12:00 on Januaru 10th, 2000.
Example of cancel-based recovery:
SVRMGR> recover database until cancel using backup controlfile
As soon as you have applied the last desired archivelog, type CANCEL
to stop recovery.
13. Open the database with the RESETLOGS option:
SVRMGR> alter database open resetlogs;
14. Export the table(s).
15. Import the table(s) into the PROD database.
16. Once the necessary tables have been imported, the TEMP instance can be
shutdown and all associated files removed. It is worthwhile verifying that
the import has completed successfully before removing the TEMP instance.
II. RECOVERY ON A PRODUCTION MACHINE:
-------------------------------------
If a TEST machine is not available for performing recovery, the PROD machine
can be used. It is important to exercise extreme caution when doing this. The
restored database will be operate under the TEST instance as before. During
this procedure the restored database's name is changed in order to avoid
problems in acquisition of the mount lock.
1. Take a FULL backup of the database running against the PROD instance
before performing any recovery.
2. While connected to the PROD instance, create an ASCII dump of the
controlfile:
SVRMGR> connect / as sysdba
SVRMGR> alter database backup controlfile to trace resetlogs;
3. Create a /RESTORE directory as before (Step 2 above).
4. Restore the backup (and optionally the archivelogs) to the /RESTORE
directory (Steps 3 and 4 above).
5. Create the initTEMP.ora file (Steps 5, 6, 7 above). In addition to the
changes already made, modify the db_name parameter, for example:
db_name=TEMP
6. Ensure that the shell environment is set correctly (Step 8 above):
a. ORACLE_HOME
b. ORACLE_SID (should be set to TEMP)
7. Start the TEMP instance (Steps 9, 10 above). It is critical to ensure
that the correct pfile is used to start the instance.
8. Modify the ccf.sql file (Step 11 above). It is critical to ensure that
the DATAFILE and LOGFILE names reference the backup location and NOT the
PROD instance database's files. In addition to the changes already made,
modify the first line to set the new database name, for example:
from: CREATE CONTROLFILE DATABASE "PROD" RESETLOGS NOARCHIVELOG
to: CREATE CONTROLFILE SET DATABASE "TEMP" RESETLOGS NOARCHIVELOG
9. Recover the database (Step 12 above).
10. Open the database (Step 13 above).
11. Export the table(s).
12. Import the tables(s).
13. Cleanup the TEMP instance (Step 16 above).
