Oracle 11g重建控制檔案——如何獲取建立控制檔案指令碼

Hoegh發表於2015-05-30
Oracle指令碼
      控制檔案對於資料庫來說是非常重要，在進行資料恢復時通常是必不可少的；丟失控制檔案並不是致命的，但是會使恢復變得困難很多。因此，dba除了多路儲存控制檔案外，還需要備份控制檔案。
     備份控制檔案也有多種辦法，例如cp複製、rman自動備份等，今天給大家介紹如何獲取建立控制檔案的指令碼，這樣資料庫一旦出現意外，即使找不到可用的備份控制檔案，也能透過重建控制檔案來啟動資料庫。
     Oracle提供瞭如下命令來實現這個功能：
      alter database backup controlfile to trace;

1.環境準備

我們在Oracle11g中進行測試。

點選(此處)摺疊或開啟

  1. SQL> 
  2. SQL> select * from v$version;

  4. BANNER
  5. --------------------------------------------------------------------------------
  6. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
  7. PL/SQL Release 11.2.0.3.0 - Production
  8. CORE 11.2.0.3.0 Production
  9. TNS for Linux: Version 11.2.0.3.0 - Production
  10. NLSRTL Version 11.2.0.3.0 - Production

  12. SQL>

2.生成trace檔案

開啟資料庫，執行‘alter database backup controlfile to trace;’語句來生成trace檔案。

點選(此處)摺疊或開啟

  1. [oracle@hoegh ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 30 12:31:02 2015

  5. Copyright (c) 1982, 2011, Oracle. All rights reserved.

  7. Connected to an idle instance.

  9. SQL>
  10. SQL> startup
  11. ORACLE instance started.

  13. Total System Global Area 941600768 bytes
  14. Fixed Size 1348860 bytes
  15. Variable Size 515902212 bytes
  16. Database Buffers 419430400 bytes
  17. Redo Buffers 4919296 bytes
  18. Database mounted.
  19. Database opened.
  20. SQL>
  21. SQL>
  22. SQL> alter database backup controlfile to trace;

  24. Database altered.

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

  4. TRACEFILE
  5. --------------------------------------------------------------------------------
  6. /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc

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


  17. *** 2015-05-30 12:31:12.588
  18. *** SESSION ID:(125.5) 2015-05-30 12:31:12.588
  19. *** CLIENT ID:() 2015-05-30 12:31:12.588
  20. *** SERVICE NAME:() 2015-05-30 12:31:12.588
  21. *** MODULE NAME:(sqlplus@hoegh (TNS V1-V3)) 2015-05-30 12:31:12.588
  22. *** ACTION NAME:() 2015-05-30 12:31:12.588
  23.  
  24. Successfully allocated 2 recovery slaves
  25. Using 66 overflow buffers per recovery slave
  26. Thread 1 checkpoint: logseq 7, block 2, scn 898139
  27.   cache-low rba: logseq 7, block 118
  28.     on-disk rba: logseq 7, block 143, scn 898639
  29.   start recovery at logseq 7, block 118, scn 0

  31. *** 2015-05-30 12:31:12.690
  32. Started writing zeroblks thread 1 seq 7 blocks 143-150

  34. *** 2015-05-30 12:31:12.690
  35. Completed writing zeroblks thread 1 seq 7
  36. ==== Redo read statistics for thread 1 ====
  37. Total physical reads (from disk and memory): 4096Kb
  38. -- Redo read_disk statistics --
  39. Read rate (ASYNC): 12Kb in 0.08s => 0.15 Mb/sec
  40. Longest record: 1Kb, moves: 0/51 (0%)
  41. Change moves: 2/35 (5%), moved: 0Mb
  42. Longest LWN: 4Kb, moves: 0/9 (0%), moved: 0Mb
  43. Last redo scn: 0x0000.000db64e (898638)
  44. ----------------------------------------------
  45. ----- Recovery Hash Table Statistics ---------
  46. Hash table buckets = 262144
  47. Longest hash chain = 1
  48. Average hash chain = 9/9 = 1.0
  49. Max compares per lookup = 1
  50. Avg compares per lookup = 29/75 = 0.4
  51. ----------------------------------------------

  53. *** 2015-05-30 12:31:12.692
  54. KCRA: start recovery claims for 9 data blocks

  56. *** 2015-05-30 12:31:12.703
  57. KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0

  59. *** 2015-05-30 12:31:12.704
  60. Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

  62. *** 2015-05-30 12:31:12.704
  63. Completed redo application of 0.00MB

  65. *** 2015-05-30 12:31:12.709
  66. Completed recovery checkpoint
  67. ----- Recovery Hash Table Statistics ---------
  68. Hash table buckets = 262144
  69. Longest hash chain = 1
  70. Average hash chain = 9/9 = 1.0
  71. Max compares per lookup = 1
  72. Avg compares per lookup = 22/38 = 0.6
  73. ----------------------------------------------
  74. Recovery sets nab of thread 1 seq 7 to 143 with 8 zeroblks

  76. *** 2015-05-30 12:31:13.044
  77. kwqmnich: current time:: 4: 31: 12: 0
  78. kwqmnich: instance no 0 repartition flag 1
  79. kwqmnich: initialized job cache structure
  80. kwqinfy: Call kwqrNondurSubInstTsk

  82. *** 2015-05-30 12:31:30.897
  83. -- The following are current System-scope REDO Log Archival related
  84. -- parameters and can be included in the database initialization file.
  85. --
  86. -- LOG_ARCHIVE_DEST=\'\'
  87. -- LOG_ARCHIVE_DUPLEX_DEST=\'\'
  88. --
  89. -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
  90. --
  91. -- DB_UNIQUE_NAME=\"HOEGH\"
  92. --
  93. -- LOG_ARCHIVE_CONFIG=\'SEND, RECEIVE, NODG_CONFIG\'
  94. -- LOG_ARCHIVE_MAX_PROCESSES=4
  95. -- STANDBY_FILE_MANAGEMENT=MANUAL
  96. -- STANDBY_ARCHIVE_DEST=?/dbs/arch
  97. -- FAL_CLIENT=\'\'
  98. -- FAL_SERVER=\'\'
  99. --
  100. -- LOG_ARCHIVE_DEST_1=\'LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch\'
  101. -- LOG_ARCHIVE_DEST_1=\'MANDATORY NOREOPEN NODELAY\'
  102. -- LOG_ARCHIVE_DEST_1=\'ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC\'
  103. -- LOG_ARCHIVE_DEST_1=\'NOREGISTER NOALTERNATE NODEPENDENCY\'
  104. -- LOG_ARCHIVE_DEST_1=\'NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME\'
  105. -- LOG_ARCHIVE_DEST_1=\'VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)\'
  106. -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
  107. --
  108. -- Below are two sets of SQL statements, each of which creates a new
  109. -- control file and uses it to open the database. The first set opens
  110. -- the database with the NORESETLOGS option and should be used only if
  111. -- the current versions of all online logs are available. The second
  112. -- set opens the database with the RESETLOGS option and should be used
  113. -- if online logs are unavailable.
  114. -- The appropriate set of statements can be copied from the trace into
  115. -- a script file, edited as necessary, and executed when there is a
  116. -- need to re-create the control file.
  117. --
  118. -- Set #1. NORESETLOGS case
  119. --
  120. -- The following commands will create a new control file and use it
  121. -- to open the database.
  122. -- Data used by Recovery Manager will be lost.
  123. -- Additional logs may be required for media recovery of offline
  124. -- Use this only if the current versions of all online logs are
  125. -- available.
  126. -- After mounting the created controlfile, the following SQL
  127. -- statement will place the database in the appropriate
  128. -- protection mode:
  129. -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
  130. STARTUP NOMOUNT
  131. CREATE CONTROLFILE REUSE DATABASE \"HOEGH\" NORESETLOGS NOARCHIVELOG
  132.     MAXLOGFILES 16
  133.     MAXLOGMEMBERS 3
  134.     MAXDATAFILES 100
  135.     MAXINSTANCES 8
  136.     MAXLOGHISTORY 292
  137. LOGFILE
  138.   GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M BLOCKSIZE 512,
  139.   GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M BLOCKSIZE 512,
  140.   GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M BLOCKSIZE 512
  141. -- STANDBY LOGFILE
  142. DATAFILE
  143.   \'/u01/app/oracle/oradata/HOEGH/system01.dbf\',
  144.   \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\',
  145.   \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\',
  146.   \'/u01/app/oracle/oradata/HOEGH/users01.dbf\',
  147.   \'/u01/app/oracle/oradata/HOEGH/example01.dbf\'
  148. CHARACTER SET AL32UTF8
  149. ;
  150. -- Commands to re-create incarnation table
  151. -- Below log names MUST be changed to existing filenames on
  152. -- disk. Any one log file from each branch can be used to
  153. -- re-create incarnation records.
  154. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\';
  155. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\';
  156. -- Recovery is required if any of the datafiles are restored backups,
  157. -- or if the last shutdown was not normal or immediate.
  158. RECOVER DATABASE
  159. -- Database can now be opened normally.
  160. ALTER DATABASE OPEN;
  161. -- Commands to add tempfiles to temporary tablespaces.
  162. -- Online tempfiles have complete space information.
  163. -- Other tempfiles may require adjustment.
  164. ALTER TABLESPACE TEMP ADD TEMPFILE \'/u01/app/oracle/oradata/HOEGH/temp01.dbf\'
  165.      SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
  166. -- End of tempfile additions.
  167. --
  168. -- Set #2. RESETLOGS case
  169. --
  170. -- The following commands will create a new control file and use it
  171. -- to open the database.
  172. -- Data used by Recovery Manager will be lost.
  173. -- The contents of online logs will be lost and all backups will
  174. -- be invalidated. Use this only if online logs are damaged.
  175. -- After mounting the created controlfile, the following SQL
  176. -- statement will place the database in the appropriate
  177. -- protection mode:
  178. -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
  179. STARTUP NOMOUNT
  180. CREATE CONTROLFILE REUSE DATABASE \"HOEGH\" RESETLOGS NOARCHIVELOG
  181.     MAXLOGFILES 16
  182.     MAXLOGMEMBERS 3
  183.     MAXDATAFILES 100
  184.     MAXINSTANCES 8
  185.     MAXLOGHISTORY 292
  186. LOGFILE
  187.   GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M BLOCKSIZE 512,
  188.   GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M BLOCKSIZE 512,
  189.   GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M BLOCKSIZE 512
  190. -- STANDBY LOGFILE
  191. DATAFILE
  192.   \'/u01/app/oracle/oradata/HOEGH/system01.dbf\',
  193.   \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\',
  194.   \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\',
  195.   \'/u01/app/oracle/oradata/HOEGH/users01.dbf\',
  196.   \'/u01/app/oracle/oradata/HOEGH/example01.dbf\'
  197. CHARACTER SET AL32UTF8
  198. ;
  199. -- Commands to re-create incarnation table
  200. -- Below log names MUST be changed to existing filenames on
  201. -- disk. Any one log file from each branch can be used to
  202. -- re-create incarnation records.
  203. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\';
  204. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\';
  205. -- Recovery is required if any of the datafiles are restored backups,
  206. -- or if the last shutdown was not normal or immediate.
  207. RECOVER DATABASE USING BACKUP CONTROLFILE
  208. -- Database can now be opened zeroing the online logs.
  209. ALTER DATABASE OPEN RESETLOGS;
  210. -- Commands to add tempfiles to temporary tablespaces.
  211. -- Online tempfiles have complete space information.
  212. -- Other tempfiles may require adjustment.
  213. ALTER TABLESPACE TEMP ADD TEMPFILE \'/u01/app/oracle/oradata/HOEGH/temp01.dbf\'
  214.      SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
  215. -- 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 --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30162081/viewspace-1678479/，如需轉載，請註明出處，否則將追究法律責任。

相關文章