오라클 클론 디비로 복구 하기

I. RECOVERY ON A TEST MACHINE:
이 부분이 다른 서버에서 복구하는 것이구요.

II. RECOVERY ON A PRODUCTION MACHINE:
이 부분이 동일 서버에서 인스턴스 하나 띄워서 복구하는 것입니다.
문서 보시고 하면 됩니다.

[출처] 클론 디비로 복구하기 질문 (ProDBA) |작성자 가을바람

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).

[출처] 클론 디비로 복구하기 질문 (ProDBA) |작성자 가을바람