重建控制文件.docx_咨信网zixin.com.cn

资源描述

范例一 Oracle提供两种方式备份控制文件: 1.生成可以重建控制文件的脚本 2.备份二进制的控制文件如何获得可以重建控制文件的脚本.在mount状态下获得trc文件 [sql] view plaincopy sql> alter database backup controlfile to trace as '/home/oracle/crontol_trace.trc'; [sql] view plaincopy 1. [oracle@hbgf oracle]$ more /home/oracle/crontol_trace.trc 2. -- The following are current System-scope REDO Log Archival related 3. -- parameters and can be included in the database initialization file. 4. -- 5. -- LOG_ARCHIVE_DEST='' 6. -- LOG_ARCHIVE_DUPLEX_DEST='' 7. -- 8. -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf 9. -- 10. -- DB_UNIQUE_NAME="uniguard" 11. -- 12. -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' 13. -- LOG_ARCHIVE_MAX_PROCESSES=2 14. -- STANDBY_FILE_MANAGEMENT=MANUAL 15. -- STANDBY_ARCHIVE_DEST=?/dbs/arch 16. -- FAL_CLIENT='' 17. -- FAL_SERVER='' 18. -- 19. -- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' 20. -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' 21. -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' 22. -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' 23. -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' 24. -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' 25. -- LOG_ARCHIVE_DEST_STATE_10=ENABLE-- 26. -- Below are two sets of SQL statements, each of which creates a new 27. -- control file and uses it to open the database. The first set opens 28. -- the database with the NORESETLOGS option and should be used only if 29. -- the current versions of all online logs are available. The second 30. -- set opens the database with the RESETLOGS option and should be used 31. -- if online logs are unavailable. 32. -- The appropriate set of statements can be copied from the trace into 33. -- a script file, edited as necessary, and executed when there is a 34. -- need to re-create the control file. 35. -- 36. -- Set #1. NORESETLOGS case 37. -- 38. -- The following commands will create a new control file and use it 39. -- to open the database. 40. -- Data used by Recovery Manager will be lost. 41. -- Additional logs may be required for media recovery of offline 42. -- Use this only if the current versions of all online logs are 43. -- available. 44. -- After mounting the created controlfile, the following SQL 45. -- statement will place the database in the appropriate 46. -- protection mode: 47. -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNT 48. CREATE CONTROLFILE REUSE DATABASE "UNIGUARD" NORESETLOGS NOARCHIVELOG 49. MAXLOGFILES 16 50. MAXLOGMEMBERS 3 51. MAXDATAFILES 100 52. MAXINSTANCES 8 53. MAXLOGHISTORY 4672 54. LOGFILE 55. GROUP 1 '/product/app/oracle/oradata/uniguard/redo01.log' SIZE 50M, 56. GROUP 2 '/product/app/oracle/oradata/uniguard/redo02.log' SIZE 50M, 57. GROUP 3 '/product/app/oracle/oradata/uniguard/redo03.log' SIZE 50M 58. -- STANDBY LOGFILEDATAFILE 59. '/product/app/oracle/oradata/uniguard/system01.dbf', 60. '/product/app/oracle/oradata/uniguard/undotbs01.dbf', 61. '/product/app/oracle/oradata/uniguard/sysaux01.dbf', 62. '/product/app/oracle/oradata/uniguard/users01.dbf', 63. '/product/app/oracle/oradata/uniguard/info01.dbf', 64. '/product/app/oracle/oradata/uniguard/index01.dbf', 65. '/product/app/oracle/oradata/uniguard/dev01.dbf', 66. '/product/app/oracle/oradata/uniguard/index02.dbf', 67. '/product/app/oracle/oradata/uniguard/dev02.dbf', 68. '/product/app/oracle/oradata/uniguard/dev03.dbf', 69. '/product/app/oracle/oradata/uniguard/dev04.dbf' 70. CHARACTER SET ZHS16GBK; 71. -- Commands to re-create incarnation table 72. -- Below log names MUST be changed to existing filenames on 73. -- disk. Any one log file from each branch can be used to 74. -- re-create incarnation records. 75. -- ALTER DATABASE REGISTER LOGFILE '/product/app/oracle/flash_recovery_area/UNIGUARD/archivelog/2012_04_11/o1_mf_1_1_%u_.arc'; 76. -- ALTER DATABASE REGISTER LOGFILE '/product/app/oracle/flash_recovery_area/UNIGUARD/archivelog/2012_04_11/o1_mf_1_1_%u_.arc'; 77. -- Recovery is required if any of the datafiles are restored backups, 78. -- or if the last shutdown was not normal or immediate. 79. RECOVER DATABASE 80. -- Database can now be opened normally. 81. ALTER DATABASE OPEN; 82. -- Commands to add tempfiles to temporary tablespaces. 83. -- Online tempfiles have complete space information. 84. -- Other tempfiles may require adjustment. 85. ALTER TABLESPACE TEMP ADD TEMPFILE '/product/app/oracle/oradata/uniguard/temp01.dbf' 86. SIZE 737148928 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; 87. -- End of tempfile additions. 88. -- 89. -- Set #2. RESETLOGS case 90. -- 91. -- The following commands will create a new control file and use it 编辑这个trace文件,我们就可以获得创建控制文件的sql. [html] view plaincopy 1. sql>STARTUP NOMOUNT 2. sql>CREATE CONTROLFILE REUSE DATABASE "UNIGUARD" NORESETLOGS NOARCHIVELOG 3. MAXLOGFILES 16 4. MAXLOGMEMBERS 3 5. MAXDATAFILES 100 6. MAXINSTANCES 8 7. MAXLOGHISTORY 4672 8. LOGFILE 9. GROUP 1 '/product/app/oracle/oradata/uniguard/redo01.log' SIZE 50M, 10. GROUP 2 '/product/app/oracle/oradata/uniguard/redo02.log' SIZE 50M, 11. GROUP 3 '/product/app/oracle/oradata/uniguard/redo03.log' SIZE 50M 12. -- STANDBY LOGFILE 13. DATAFILE 14. '/product/app/oracle/oradata/uniguard/system01.dbf', 15. '/product/app/oracle/oradata/uniguard/undotbs01.dbf', 16. '/product/app/oracle/oradata/uniguard/sysaux01.dbf', 17. '/product/app/oracle/oradata/uniguard/users01.dbf', 18. '/product/app/oracle/oradata/uniguard/info01.dbf', 19. '/index/oradata/uniguard/index01.dbf', 20. '/devdata/oradata/uniguard/index01.dbf', 21. '/devdata/oradata/uniguard/indexdata02.dbf', 22. '/devdata/oradata/uniguard/dev01.dbf' 23. CHARACTER SET ZHS16GBK 24. ; 25. sql>RECOVER DATABASE 26. sql>ALTER DATABASE OPEN; 27. sql>ALTER TABLESPACE TEMP ADD TEMPFILE '/product/app/oracle/oradata/uniguard/temp01.dbf' 28. SIZE 1244667904 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; 29. 以上给出生成创建控制文件脚本并重建控制文件的方法，但是具体恢复中遇到的问题可能需要具体对待,当得不到trace文件，可根据这个格式，查看数据文件，更改相关内容再重建.这种方法通常是在没有控制文件(二进制文件)备份的情况下所采用的，如果存在备份应该使用备份的控制文件尝试恢复. 注： [sql] view plaincopy SQL>alter database backup controlfile to '\home\oracle\backup\control.bkp'; 1. //备份二进制控制文件范例二重建控制文件 2012-06-12 21:37:11 分类： Oracle 环境: OS:Red Hat Linux As 5 DB:10.2.0.4 在全部控制文件丢失或损坏，而且没有备份的情况下,可以使用重建控制文件的办法打开数据库.以下模拟所有的控制文件丢失的情况下重建控制文件. 1.备份控制文件(数据库mount或是open状态) SQL> select status from v$instance; STATUS ------------ OPEN SQL>alter database backup controlfile to trace as '/u01/ftp/bak_controlfile'; 2.删除控制文件 [oracle@hxl oracl]$ rm control01.ctl rm: remove regular file `control01.ctl'? y [oracle@hxl oracl]$ rm control02.ctl rm: remove regular file `control02.ctl'? y [oracle@hxl oracl]$ rm control03.ctl rm: remove regular file `control03.ctl'? y 3.关闭数据库后尝试打开数据库 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 734003200 bytes Fixed Size 1221564 bytes Variable Size 218106948 bytes Database Buffers 511705088 bytes Redo Buffers 2969600 bytes ORA-00205: error in identifying control file, check alert log for more info 这个时候数据无法打开,以为我们已经删除了控制文件. 4.查看备份控制文件的内容 [oracle@hxl ftp]$ more bak_controlfile -- The following are current System-scope REDO Log Archival related -- parameters and can be included in the database initialization file. -- -- LOG_ARCHIVE_DEST='' -- LOG_ARCHIVE_DUPLEX_DEST='' -- -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf -- -- DB_UNIQUE_NAME="oracl" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_10=ENABLE -- -- Below are two sets of SQL statements, each of which creates a new -- control file and uses it to open the database. The first set opens -- the database with the NORESETLOGS option and should be used only if -- the current versions of all online logs are available. The second -- set opens the database with the RESETLOGS option and should be used -- if online logs are unavailable. -- The appropriate set of statements can be copied from the trace into -- a script file, edited as necessary, and executed when there is a -- need to re-create the control file. -- -- Set #1. NORESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- Additional logs may be required for media recovery of offline -- Use this only if the current versions of all online logs are -- available. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORACL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/oracl/system01.dbf', '/u01/app/oracle/oradata/oracl/undotbs01.dbf', '/u01/app/oracle/oradata/oracl/sysaux01.dbf', '/u01/app/oracle/oradata/oracl/users01.dbf' CHARACTER SET WE8ISO8859P1 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- All logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; -- Database can now be opened normally. ALTER DATABASE OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- -- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/oracl/system01.dbf', '/u01/app/oracle/oradata/oracl/undotbs01.dbf', '/u01/app/oracle/oradata/oracl/sysaux01.dbf', '/u01/app/oracle/oradata/oracl/users01.dbf' CHARACTER SET WE8ISO8859P1 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/oracl/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- 5.从备份控制文件中提取我们需要的部分,这里我们选择RESETLOGS,将如下内容保存文件为create_confile.sql CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/oracl/system01.dbf', '/u01/app/oracle/oradata/oracl/undotbs01.dbf', '/u01/app/oracle/oradata/oracl/sysaux01.dbf', '/u01/app/oracle/oradata/oracl/users01.dbf' CHARACTER SET WE8ISO8859P1 ; 6.执行create_confile.sql SQL>set sqlblanklines on -- 因为文件中有空行,需要将该选项打开,否则执行的时候报语法错误 SQL>@/u01/f

展开阅读全文