Oracle 11g重建控制文件——如何获取创建控制文件脚本

控制文件对于数据库来说是非常重要,在进行数据恢复时通常是必不可少的;丢失控制文件并不是致命的,但是会使恢复变得困难很多。因此,dba除了多路保存控制文件外,还需要备份控制文件。
     备份控制文件也有多种办法,例如cp拷贝、rman自动备份等,今天给大家介绍如何获取创建控制文件的脚本,这样数据库一旦出现意外,即使找不到可用的备份控制文件,也能通过重建控制文件来启动数据库。
     Oracle提供了如下命令来实现这个功能:
      alter database backup controlfile to trace;

1.环境准备

我们在Oracle11g中进行测试。

点击(此处)折叠或打开

  1. SQL>
  2. SQL> select * from v$version;
  3. BANNER
  4. ——————————————————————————–
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
  6. PL/SQL Release 11.2.0.3.0 – Production
  7. CORE 11.2.0.3.0 Production
  8. TNS for Linux: Version 11.2.0.3.0 – Production
  9. NLSRTL Version 11.2.0.3.0 – Production
  10. SQL>

2.生成trace文件

打开数据库,执行‘alter database backup controlfile to trace;’语句来生成trace文件。

点击(此处)折叠或打开

  1. [oracle@hoegh ~]$ sqlplus / as sysdba
  2. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 30 12:31:02 2015
  3. Copyright (c) 1982, 2011, Oracle. All rights reserved.
  4. Connected to an idle instance.
  5. SQL>
  6. SQL> startup
  7. ORACLE instance started.
  8. Total System Global Area 941600768 bytes
  9. Fixed Size 1348860 bytes
  10. Variable Size 515902212 bytes
  11. Database Buffers 419430400 bytes
  12. Redo Buffers 4919296 bytes
  13. Database mounted.
  14. Database opened.
  15. SQL>
  16. SQL>
  17. SQL> alter database backup controlfile to trace;
  18. Database altered.
  19. SQL>


3.查找trace文件路径

trace文件的路径可以通过一个多表查询来获得,其中涉及v$processv、$sessionv和$mystat等三个动态视图。

点击(此处)折叠或打开

  1. SQL>
  2. SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
  3. TRACEFILE
  4. ——————————————————————————–
  5. /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc
  6. SQL>

4.查看trace文件

通过cat命令查看trace文件,内容如下:

点击(此处)折叠或打开

  1. Trace file /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  4. ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
  5. System name: Linux
  6. Node name: hoegh
  7. Release: 2.6.18-164.el5PAE
  8. Version: #1 SMP Thu Sep 3 02:28:20 EDT 2009
  9. Machine: i686
  10. VM name: VMWare Version: 6
  11. Instance name: HOEGH
  12. Redo thread mounted by this instance: 1
  13. Oracle process number: 19
  14. Unix process pid: 4831, image: oracle@hoegh (TNS V1-V3)
  15. *** 2015-05-30 12:31:12.588
  16. *** SESSION ID:(125.5) 2015-05-30 12:31:12.588
  17. *** CLIENT ID:() 2015-05-30 12:31:12.588
  18. *** SERVICE NAME:() 2015-05-30 12:31:12.588
  19. *** MODULE NAME:(sqlplus@hoegh (TNS V1-V3)) 2015-05-30 12:31:12.588
  20. *** ACTION NAME:() 2015-05-30 12:31:12.588
  21. Successfully allocated 2 recovery slaves
  22. Using 66 overflow buffers per recovery slave
  23. Thread 1 checkpoint: logseq 7, block 2, scn 898139
  24.   cache-low rba: logseq 7, block 118
  25.     on-disk rba: logseq 7, block 143, scn 898639
  26.   start recovery at logseq 7, block 118, scn 0
  27. *** 2015-05-30 12:31:12.690
  28. Started writing zeroblks thread 1 seq 7 blocks 143-150
  29. *** 2015-05-30 12:31:12.690
  30. Completed writing zeroblks thread 1 seq 7
  31. ==== Redo read statistics for thread 1 ====
  32. Total physical reads (from disk and memory): 4096Kb
  33. — Redo read_disk statistics —
  34. Read rate (ASYNC): 12Kb in 0.08s => 0.15 Mb/sec
  35. Longest record: 1Kb, moves: 0/51 (0%)
  36. Change moves: 2/35 (5%), moved: 0Mb
  37. Longest LWN: 4Kb, moves: 0/9 (0%), moved: 0Mb
  38. Last redo scn: 0x0000.000db64e (898638)
  39. ———————————————-
  40. —– Recovery Hash Table Statistics ———
  41. Hash table buckets = 262144
  42. Longest hash chain = 1
  43. Average hash chain = 9/9 = 1.0
  44. Max compares per lookup = 1
  45. Avg compares per lookup = 29/75 = 0.4
  46. ———————————————-
  47. *** 2015-05-30 12:31:12.692
  48. KCRA: start recovery claims for 9 data blocks
  49. *** 2015-05-30 12:31:12.703
  50. KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0
  51. *** 2015-05-30 12:31:12.704
  52. Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
  53. *** 2015-05-30 12:31:12.704
  54. Completed redo application of 0.00MB
  55. *** 2015-05-30 12:31:12.709
  56. Completed recovery checkpoint
  57. —– Recovery Hash Table Statistics ———
  58. Hash table buckets = 262144
  59. Longest hash chain = 1
  60. Average hash chain = 9/9 = 1.0
  61. Max compares per lookup = 1
  62. Avg compares per lookup = 22/38 = 0.6
  63. ———————————————-
  64. Recovery sets nab of thread 1 seq 7 to 143 with 8 zeroblks
  65. *** 2015-05-30 12:31:13.044
  66. kwqmnich: current time:: 4: 31: 12: 0
  67. kwqmnich: instance no 0 repartition flag 1
  68. kwqmnich: initialized job cache structure
  69. kwqinfy: Call kwqrNondurSubInstTsk
  70. *** 2015-05-30 12:31:30.897
  71. — The following are current System-scope REDO Log Archival related
  72. — parameters and can be included in the database initialization file.
  73. — LOG_ARCHIVE_DEST=\’\’
  74. — LOG_ARCHIVE_DUPLEX_DEST=\’\’
  75. — LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
  76. — DB_UNIQUE_NAME=\”HOEGH\”
  77. — LOG_ARCHIVE_CONFIG=\’SEND, RECEIVE, NODG_CONFIG\’
  78. — LOG_ARCHIVE_MAX_PROCESSES=4
  79. — STANDBY_FILE_MANAGEMENT=MANUAL
  80. — STANDBY_ARCHIVE_DEST=?/dbs/arch
  81. — FAL_CLIENT=\’\’
  82. — FAL_SERVER=\’\’
  83. — LOG_ARCHIVE_DEST_1=\’LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch\’
  84. — LOG_ARCHIVE_DEST_1=\’MANDATORY NOREOPEN NODELAY\’
  85. — LOG_ARCHIVE_DEST_1=\’ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC\’
  86. — LOG_ARCHIVE_DEST_1=\’NOREGISTER NOALTERNATE NODEPENDENCY\’
  87. — LOG_ARCHIVE_DEST_1=\’NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME\’
  88. — LOG_ARCHIVE_DEST_1=\’VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)\’
  89. — LOG_ARCHIVE_DEST_STATE_1=ENABLE
  90. — Below are two sets of SQL statements, each of which creates a new
  91. — control file and uses it to open the database. The first set opens
  92. — the database with the NORESETLOGS option and should be used only if
  93. — the current versions of all online logs are available. The second
  94. — set opens the database with the RESETLOGS option and should be used
  95. — if online logs are unavailable.
  96. — The appropriate set of statements can be copied from the trace into
  97. — a script file, edited as necessary, and executed when there is a
  98. — need to re-create the control file.
  99. — Set #1. NORESETLOGS case
  100. — The following commands will create a new control file and use it
  101. — to open the database.
  102. — Data used by Recovery Manager will be lost.
  103. — Additional logs may be required for media recovery of offline
  104. — Use this only if the current versions of all online logs are
  105. — available.
  106. — After mounting the created controlfile, the following SQL
  107. — statement will place the database in the appropriate
  108. — protection mode:
  109. — ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
  110. STARTUP NOMOUNT
  111. CREATE CONTROLFILE REUSE DATABASE \”HOEGH\” NORESETLOGS NOARCHIVELOG
  112.     MAXLOGFILES 16
  113.     MAXLOGMEMBERS 3
  114.     MAXDATAFILES 100
  115.     MAXINSTANCES 8
  116.     MAXLOGHISTORY 292
  117. LOGFILE
  118.   GROUP 1 \’/u01/app/oracle/oradata/HOEGH/redo01.log\’ SIZE 50M BLOCKSIZE 512,
  119.   GROUP 2 \’/u01/app/oracle/oradata/HOEGH/redo02.log\’ SIZE 50M BLOCKSIZE 512,
  120.   GROUP 3 \’/u01/app/oracle/oradata/HOEGH/redo03.log\’ SIZE 50M BLOCKSIZE 512
  121. — STANDBY LOGFILE
  122. DATAFILE
  123.   \’/u01/app/oracle/oradata/HOEGH/system01.dbf\’,
  124.   \’/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\’,
  125.   \’/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\’,
  126.   \’/u01/app/oracle/oradata/HOEGH/users01.dbf\’,
  127.   \’/u01/app/oracle/oradata/HOEGH/example01.dbf\’
  128. CHARACTER SET AL32UTF8
  129. ;
  130. — Commands to re-create incarnation table
  131. — Below log names MUST be changed to existing filenames on
  132. — disk. Any one log file from each branch can be used to
  133. — re-create incarnation records.
  134. — ALTER DATABASE REGISTER LOGFILE \’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\’;
  135. — ALTER DATABASE REGISTER LOGFILE \’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\’;
  136. — Recovery is required if any of the datafiles are restored backups,
  137. — or if the last shutdown was not normal or immediate.
  138. RECOVER DATABASE
  139. — Database can now be opened normally.
  140. ALTER DATABASE OPEN;
  141. — Commands to add tempfiles to temporary tablespaces.
  142. — Online tempfiles have complete space information.
  143. — Other tempfiles may require adjustment.
  144. ALTER TABLESPACE TEMP ADD TEMPFILE \’/u01/app/oracle/oradata/HOEGH/temp01.dbf\’
  145.      SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
  146. — End of tempfile additions.
  147. — Set #2. RESETLOGS case
  148. — The following commands will create a new control file and use it
  149. — to open the database.
  150. — Data used by Recovery Manager will be lost.
  151. — The contents of online logs will be lost and all backups will
  152. — be invalidated. Use this only if online logs are damaged.
  153. — After mounting the created controlfile, the following SQL
  154. — statement will place the database in the appropriate
  155. — protection mode:
  156. — ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
  157. STARTUP NOMOUNT
  158. CREATE CONTROLFILE REUSE DATABASE \”HOEGH\” RESETLOGS NOARCHIVELOG
  159.     MAXLOGFILES 16
  160.     MAXLOGMEMBERS 3
  161.     MAXDATAFILES 100
  162.     MAXINSTANCES 8
  163.     MAXLOGHISTORY 292
  164. LOGFILE
  165.   GROUP 1 \’/u01/app/oracle/oradata/HOEGH/redo01.log\’ SIZE 50M BLOCKSIZE 512,
  166.   GROUP 2 \’/u01/app/oracle/oradata/HOEGH/redo02.log\’ SIZE 50M BLOCKSIZE 512,
  167.   GROUP 3 \’/u01/app/oracle/oradata/HOEGH/redo03.log\’ SIZE 50M BLOCKSIZE 512
  168. — STANDBY LOGFILE
  169. DATAFILE
  170.   \’/u01/app/oracle/oradata/HOEGH/system01.dbf\’,
  171.   \’/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\’,
  172.   \’/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\’,
  173.   \’/u01/app/oracle/oradata/HOEGH/users01.dbf\’,
  174.   \’/u01/app/oracle/oradata/HOEGH/example01.dbf\’
  175. CHARACTER SET AL32UTF8
  176. ;
  177. — Commands to re-create incarnation table
  178. — Below log names MUST be changed to existing filenames on
  179. — disk. Any one log file from each branch can be used to
  180. — re-create incarnation records.
  181. — ALTER DATABASE REGISTER LOGFILE \’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\’;
  182. — ALTER DATABASE REGISTER LOGFILE \’/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\’;
  183. — Recovery is required if any of the datafiles are restored backups,
  184. — or if the last shutdown was not normal or immediate.
  185. RECOVER DATABASE USING BACKUP CONTROLFILE
  186. — Database can now be opened zeroing the online logs.
  187. ALTER DATABASE OPEN RESETLOGS;
  188. — Commands to add tempfiles to temporary tablespaces.
  189. — Online tempfiles have complete space information.
  190. — Other tempfiles may require adjustment.
  191. ALTER TABLESPACE TEMP ADD TEMPFILE \’/u01/app/oracle/oradata/HOEGH/temp01.dbf\’
  192.      SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
  193. — End of tempfile additions.


5.NORESETLOGS VS RESETLOGS

从trace文件可以看出,提供了两个创建控制文件的脚本,分别针对两种不同的应用场景:NORESETLOGS适用于当前redo log可用,而RESETLOGS适用于当前redo log不可用。

NORESETLOGS重建步骤

1.执行脚本,重建控制文件(CREATE CONTROLFILE REUSE DATABASE “HOEGH” NORESETLOGS NOARCHIVELOG……)
2.执行介质恢复RECOVER DATABASE
3.打开数据库ALTER DATABASE OPEN;
4.添加临时表空间

RESETLOGS重建步骤

1.执行脚本,重建控制文件(CREATE CONTROLFILE REUSE DATABASE “HOEGH” RESETLOGS NOARCHIVELOG……)
2.执行介质恢复RECOVER DATABASE USING BACKUP CONTROLFILE
3.打开数据库ALTER DATABASE OPEN RESETLOGS;
4.添加临时表空间


hoegh
15.05.30
— The End —

Leave a Reply

Your email address will not be published. Required fields are marked *