Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)

xifenfei

1. prompt
   
2. prompt +----------------------------------------------------------------------------+
   
3. prompt |                   Oracle Database Recovery Check Result                    |
   
4. prompt |----------------------------------------------------------------------------+
   
5. prompt |  Copyright (c) 2010-2016 xifenfei. All rights reserved. (www.xifenfei.com) |
   
6. prompt +----------------------------------------------------------------------------+
   
7. prompt
   
8. prompt Please start the database to mount state.
   
9. prompt Note: Do not modify any inspection results
   
10. prompt Please start the database to mount state.
    
11. prompt Note: Do not modify any inspection results
    
12. prompt To send xifenfei_db_recover_YYYYMMDD.html to dba@xifenfei.com or QQ(107644445)
    
13. prompt Please refer to the use of the script:http://www.xifenfei.com/oracle_recovery_check
    
14. prompt
    
15. 
    
16. define reportHeader="<font size=+3 color=darkgreen><b>Oracle Database Recovery Check Result</b></font><hr>Copyright (c) 2010-2016 <a target=""_blank"" href=""http://www.xifenfei.com/oracle_recovery_check"">XIFENFEI</a>. All rights reserved.<p>"
    
17. 
    
18. 
    
19. 
    
20. set termout       off
    
21. set echo          off
    
22. set feedback      off
    
23. set verify        off
    
24. set wrap          on
    
25. set trimspool     on
    
26. set serveroutput  on
    
27. set escape        on
    
28. set pagesize 50000
    
29. set long     2000000000
    
30. set numw 16
    
31. col error format a30
    
32. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
    
33. set markup html on spool on preformat off entmap on -
    
34. head ' -
    
35.   <title>Oracle Database recovery check result</title> -
    
36.   <style type="text/css"> -
    
37.     body              {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
    
38.     p                 {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
    
39.     table,tr,td       {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
    
40.     th                {font:bold 10pt Arial,Helvetica,sans-serif; color:White; background:#0066cc; padding:0px 0px 0px 0px;} -
    
41.     h1                {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:#0066cc; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
    
42.     h2                {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
    
43.         a                 {font:10pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
    
44.   </style>' -
    
45. body   'BGCOLOR="#C0C0C0"' -
    
46. table  'WIDTH="90%" BORDER="1"'
    
47. 
    
48. define fileName=xifenfei_db_recover
    
49. COLUMN spool_time NEW_VALUE _spool_time NOPRINT
    
50. SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;
    
51. 
    
52. spool &FileName._&_spool_time..html
    
53. set markup html on entmap off
    
54. prompt &reportHeader
    
55. 
    
56. SET MARKUP HTML ON
    
57. set define ~
    
58. col CONTACT for a10
    
59. select 'Phone' "CONTACT",'13429648788' "INFORMATION"  from dual
    
60. union all
    
61. select
    
62. 'Q Q','107644445'
    
63. from dual
    
64. union all
    
65. select 'E-Mail','dba@xifenfei.com' from dual;
    
66. 
    
67. --current_date
    
68. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Execution Time</b></font><hr align="center" width="250"></center>
    
69. select sysdate as current_date from dual;
    
70. 
    
71. --version
    
72. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>DB Version</b></font><hr align="center" width="250"></center>
    
73. select * from v$version;
    
74. 
    
75. 
    
76. --instance
    
77. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Instance Information</b></font><hr align="center" width="250"></center>
    
78. select INSTANCE_NUMBER,INSTANCE_NAME,host_name,STATUS,STARTUP_TIME,THREAD# from Gv$instance;
    
79. 
    
80. 
    
81. --database
    
82. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Controlfile Information</b></font><hr align="center" width="300"></center>
    
83. select dbid, name,open_mode,
    
84.        created created,
    
85.        open_mode, log_mode,
    
86.        checkpoint_change# as checkpoint_change#,
    
87.        controlfile_type ctl_type,
    
88.        controlfile_created ctl_created,
    
89.        controlfile_change# as ctl_change#,
    
90.        controlfile_time ctl_time,
    
91.        resetlogs_change# as resetlogs_change#,
    
92.        resetlogs_time resetlogs_time
    
93. from v$database;
    
94. 
    
95. --scn
    
96. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SCN Information</b></font><hr align="center" width="300"></center>
    
97. SELECT to_char(tim,'yyyy-mm-dd hh24:mi:ss') tim,scn,chk16kscn Headroom_SCN,chk16kscn-scn more_scn,round((chk16kscn-scn)/24/3600/16/1024,3) Headroom, round((chk16kscn-scn)/1024/1024/1024,1) adjust_scn,round(chk16kscn/1024/1024/1024,1) ALL_scn
    
98. FROM  
    
99. (
    
100. select tim, scn,
     
101.   ((
     
102.   ((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
     
103.   ((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
     
104.   (((to_number(to_char(tim,'DD'))-1))*24*60*60) +
     
105.   (to_number(to_char(tim,'HH24'))*60*60) +
     
106.   (to_number(to_char(tim,'MI'))*60) +
     
107.   (to_number(to_char(tim,'SS')))
     
108.   ) * (16*1024)) chk16kscn
     
109.   from
     
110. (select sysdate tim,checkpoint_change# scn from v$database)
     
111. )
     
112. ORDER BY tim;
     
113. 
     
114. 
     
115. --parameter
     
116. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Non-default Parameter</b></font><hr align="center" width="300"></center>
     
117. SELECT
     
118.     p.name,i.instance_name ,p.value
     
119. FROM
     
120.     gv$parameter p
     
121.   , gv$instance  i
     
122. WHERE
     
123.     p.inst_id = i.inst_id
     
124. and  isdefault='FALSE'
     
125. ORDER BY
     
126.     p.name
     
127.   , i.instance_name;
     
128. 
     
129. --TABLESPACE
     
130. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace Information</b></font><hr align="center" width="300"></center>
     
131. select * from (
     
132. select a.ts#,a.name,sum(bytes)/1024/1024/1024 ts_size_g from v$datafile b,
     
133. v$tablespace a where a.ts#=b.ts# group by a.ts#,a.name
     
134. union all
     
135. select a.ts#,a.name||'@TEMPORARY',sum(bytes)/1024/1024/1024 from v$tempfile b,
     
136. v$tablespace a where a.ts#=b.ts# group by a.ts#,a.name
     
137. union all
     
138. select null, 'ALL_SIZE(G)',SUM(BYTES/1024/1024/1024)  FROM V$datafile) order by 1;
     
139. 
     
140. 
     
141. --v$datafile
     
142. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Datafile Information</b></font><hr align="center" width="300"></center>
     
143. select
     
144. ts#,
     
145. file#,
     
146. BYTES/1024/1024/1024 file_size_G,
     
147. status,
     
148. enabled,
     
149. CREATION_TIME,
     
150. checkpoint_change# "SCN",
     
151. last_change# "STOP_SCN",
     
152. offline_change#,
     
153. online_change#,
     
154. online_time,
     
155. name
     
156. from v$datafile
     
157. order by 1,2;
     
158. 
     
159. --v$datafile_header
     
160. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Datafile Header Information</b></font><hr align="center" width="350"></center>
     
161. 
     
162. select
     
163. ts#,
     
164. file#,
     
165. bytes/1024/1024/1024 file_size_G,
     
166. TABLESPACE_NAME,
     
167. status,
     
168. ERROR,
     
169. FORMAT,
     
170. recover,
     
171. FUZZY,
     
172. CREATION_TIME CREATE_TIME,
     
173. checkpoint_change# checkpoint_change,
     
174. RESETLOGS_CHANGE# "RESETLOGS_CHANGE",
     
175. resetlogs_time
     
176. from v$datafile_header
     
177. order by 1,2;
     
178. 
     
179. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Datafile and Datafile Header Information</b></font><hr align="center" width="350"></center>
     
180. 
     
181. SELECT status,checkpoint_change#,checkpoint_time,last_change#,count(*) ROW_NUM FROM v$datafile GROUP BY status, checkpoint_change#, checkpoint_time,last_change# ORDER BY status, checkpoint_change#, checkpoint_time;
     
182. 
     
183. SELECT status,
     
184. checkpoint_change#,checkpoint_time,FUZZY,RESETLOGS_CHANGE#,count(*) ROW_NUM
     
185. FROM v$datafile_header
     
186. GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy,RESETLOGS_CHANGE#
     
187. ORDER BY status, checkpoint_change#, checkpoint_time;
     
188. 
     
189. SELECT dd.FILE#,
     
190. dd.NAME,
     
191. dd.STATUS,
     
192. dd.checkpoint_change# dfile_chkp_change,
     
193. dh.checkpoint_change# dfile_hed_chkp_change,
     
194. dh.recover,
     
195. dh.fuzzy
     
196. FROM v$datafile dd,
     
197. v$datafile_header dh
     
198. WHERE dd.FILE#=dh.FILE#
     
199. AND dd.checkpoint_change#<>dh.checkpoint_change#;
     
200. 
     
201. 
     
202. --x$kcvfh
     
203. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>x$kcvfh Information</b></font><hr align="center" width="350"></center>
     
204. 
     
205. /*
     
206. fhsta
     
207. 64  normal rman fuzzy
     
208. 4   normal fuzzy
     
209. 8192 system good
     
210. 8196 system fuzzy
     
211. 0   normal good
     
212. 8256 system rman fuzzy
     
213. */
     
214. select
     
215. FHTSN  TS#,
     
216. HXFIL FILE#,
     
217. FHRFN RFILE#,
     
218. DECODE(HXONS,0,'OFFLINE','ONLINE') F_STATUS,
     
219. decode(HXERR, 0, NULL, 1,'FILE MISSING',2,'OFFLINE NORMAL', 3,'NOT VERIFIED', 4,'FILE NOT FOUND',5,'CANNOT OPEN FILE', 6,'CANNOT READ HEADER', 7,'CORRUPT HEADER',8,'WRONG FILE TYPE', 9,'WRONG DATABASE', 10,'WRONG FILE NUMBER',11,'WRONG FILE CREATE', 12,'WRONG FILE CREATE', 16,'DELAYED OPEN',14, 'WRONG RESETLOGS', 15,'OLD CONTROLFILE', 'UNKNOWN ERROR') F_ERROR,
     
220. FHSTA STAUTUS,
     
221. decode(hxifz, 0,'NO', 1,'YES', NULL) FUZZY,
     
222. FHCRS CRT_SCN,
     
223. FHCRT CRT_TIME,
     
224. FHSCN CPT_SCN,
     
225. FHTIM CPT_TIME,
     
226. FHRLS RESETLOGS_SCN,
     
227. FHTHR THREAD#,
     
228. FHRBA_SEQ SEQUENCE#
     
229. from x$kcvfh order by FHTSN,FHRFN;
     
230. 
     
231. SELECT fhthr thread,
     
232. fhrba_seq sequence,
     
233. fhscn scn,
     
234. fhsta status,
     
235. count(*) ROW_NUM
     
236. FROM x$kcvfh
     
237. group by fhthr,fhrba_seq,fhscn,fhsta;
     
238. 
     
239. select max(fhafs) "Minimum PITR SCN" from x$kcvfh;
     
240. 
     
241. --redo
     
242. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Redofile Information</b></font><hr align="center" width="300"></center>
     
243.   SELECT thread#,a.group#,
     
244.          a.sequence#,a.bytes/1024/1024 "SIZE(M)",
     
245.          first_change# "F_SCN",
     
246.          a.FIRST_TIME,
     
247.          a.ARCHIVED,
     
248.          a.status,
     
249.          MEMBER
     
250.     FROM v$log a, v$logfile b
     
251.    WHERE a.group# = B.GROUP#
     
252. ORDER BY thread#,a.sequence# DESC;
     
253. 
     
254. 
     
255. --incarnation
     
256. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Incarnation Information</b></font><hr align="center" width="350"></center>
     
257. select * from v$database_incarnation;
     
258. 
     
259. 
     
260. --v$backup begin backup
     
261. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Hot Backup Information</b></font><hr align="center" width="350"></center>
     
262. select file#,CHANGE# "SCN",
     
263. TIME "TIME" from v$backup;
     
264. 
     
265. --v$archived_log
     
266. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archivelog Information</b></font><hr align="center" width="300"></center>
     
267. col name for a50
     
268. select
     
269. thread#,sequence# sequence#,
     
270. FIRST_CHANGE# FIRST_CHANGE#,
     
271. FIRST_TIME FIRST_TIME,
     
272. NEXT_CHANGE# NEXT_CHANGE#,
     
273. NEXT_TIME NEXT_TIME,
     
274. name from (
     
275. select  rownum rn,a.* from
     
276. (
     
277. select
     
278. sequence#,thread#,
     
279. FIRST_CHANGE#,
     
280. FIRST_TIME,
     
281. NEXT_CHANGE#,
     
282. NEXT_TIME,
     
283. name from v$archived_log
     
284. where DELETED='NO'
     
285. order by NEXT_TIME desc
     
286. ) a
     
287. ) where rn<100;
     
288. select thread#,min(sequence#) min_seq,max(sequence#) max_seq, min(FIRST_TIME) min_first_tiime,max(NEXT_TIME) max_next_time,count(*) archive_count from v$archived_log where  DELETED='NO' group by thread#;
     
289. 
     
290. --$recovery_log v$recover_file
     
291. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>V$recovery_log and V$recover_file</b></font><hr align="center" width="500"></center>
     
292. select THREAD#,SEQUENCE# SEQUENCE#,
     
293. TIME "TIME"
     
294. from v$recovery_log;
     
295. 
     
296. select file#,online_status "STATUS",
     
297. change# "SCN",
     
298. time"TIME"
     
299. from v$recover_file;
     
300. 
     
301. SELECT a.recid,
     
302. a.thread#,
     
303. a.sequence#,
     
304. a.name,
     
305. a.first_change#,
     
306. a.NEXT_CHANGE#,
     
307. a.archived,
     
308. a.deleted,
     
309. a.completion_time
     
310. FROM v$archived_log a, v$recovery_log l
     
311. WHERE a.thread# = l.thread#
     
312. AND a.sequence# = l.sequence#;
     
313. 
     
314. --rman backup
     
315. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Rman Information</b></font><hr align="center" width="250"></center>
     
316.     SELECT A.RECID "BACKUP SET",
     
317.          A.SET_STAMP,
     
318.          DECODE (B.INCREMENTAL_LEVEL,
     
319.                  '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
     
320.                  1, 'Incr-1',
     
321.                  0, 'Incr-0',
     
322.                  B.INCREMENTAL_LEVEL)
     
323.             "Type LV",
     
324.          B.CONTROLFILE_INCLUDED "including CTL",
     
325.          DECODE (A.STATUS,
     
326.                  'A', 'AVAILABLE',
     
327.                  'D', 'DELETED',
     
328.                  'X', 'EXPIRED',
     
329.                  'ERROR')
     
330.             "STATUS",
     
331.          A.DEVICE_TYPE "Device Type",
     
332.          A.START_TIME "Start Time",
     
333.          A.COMPLETION_TIME "Completion Time",
     
334.          A.ELAPSED_SECONDS "Elapsed Seconds",
     
335.          A.TAG "Tag",
     
336.          A.HANDLE "Path"
     
337.     FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
     
338.    WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
     
339. ORDER BY A.COMPLETION_TIME DESC;
     
340. spool off
     
341. 
     
342. host echo Please check and upload "xifenfei_db_recover_YYYYMMDD.html" in current directory
     
343. exit;

复制代码

具体使用参考：Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)