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

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

1.环境准备

我们在Oracle11g中进行测试。

点击(此处)折叠或打开

1. SQL> 
   
2. SQL> select * from v$version;
   
3. 
   
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
    
11. 
    
12. SQL>

2.生成trace文件

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

点击(此处)折叠或打开

1. [oracle@hoegh ~]$ sqlplus / as sysdba
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 30 12:31:02 2015
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle. All rights reserved.
   
6. 
   
7. Connected to an idle instance.
   
8. 
   
9. SQL> 
   
10. SQL> startup
    
11. ORACLE instance started.
    
12. 
    
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;
    
23. 
    
24. Database altered.
    
25. 
    
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));
   
3. 
   
4. TRACEFILE
   
5. --------------------------------------------------------------------------------
   
6. /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc
   
7. 
   
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)
    
15. 
    
16. 
    
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
    
30. 
    
31. *** 2015-05-30 12:31:12.690
    
32. Started writing zeroblks thread 1 seq 7 blocks 143-150
    
33. 
    
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. ----------------------------------------------
    
52. 
    
53. *** 2015-05-30 12:31:12.692
    
54. KCRA: start recovery claims for 9 data blocks
    
55. 
    
56. *** 2015-05-30 12:31:12.703
    
57. KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0
    
58. 
    
59. *** 2015-05-30 12:31:12.704
    
60. Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
    
61. 
    
62. *** 2015-05-30 12:31:12.704
    
63. Completed redo application of 0.00MB
    
64. 
    
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
    
75. 
    
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 
    
81. 
    
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文件可以看出，提供了两个创建控制文件的脚本，分别针对两种不同的应用场景：NO RESETLOGS适用于当前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.添加临时表空间

为防止原帖关闭，进行转载

转载自：http://blog.itpub.net/30162081/viewspace-1678479/