首页 > Linux操作系统 > Linux操作系统 > Recovering a Dropped Table [ID 96197.1]

Recovering a Dropped Table [ID 96197.1]

原创 Linux操作系统 作者:ygzhou518 时间:2012-02-25 21:59:45 0 删除 编辑

Recovering a Dropped Table from a Full Database Backup using manual backup and recovery procedures [ID 96197.1]

  修改时间 03-JAN-2012     类型 TROUBLESHOOTING     状态 PUBLISHED  


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.

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.

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 accommodate 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). RELATED DOCUMENTS ----------------- Oracle7 Server Administrator's Guide Oracle8 Backup and Recovery Guide 

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

上一篇: xargs
请登录后发表评论 登录


  • 博文量
  • 访问量