请选择 进入手机版 | 继续访问电脑版

ORACLE SOS

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 1732|回复: 0

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

[复制链接]

95

主题

266

帖子

1695

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1695
发表于 2018-7-12 12:14:16 | 显示全部楼层 |阅读模式
  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. 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>"



  16. set termout       off
  17. set echo          off
  18. set feedback      off
  19. set verify        off
  20. set wrap          on
  21. set trimspool     on
  22. set serveroutput  on
  23. set escape        on
  24. set pagesize 50000
  25. set long     2000000000
  26. set numw 16
  27. col error format a30
  28. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
  29. set markup html on spool on preformat off entmap on -
  30. head ' -
  31.   <title>Oracle Database recovery check result</title> -
  32.   <style type="text/css"> -
  33.     body              {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  34.     p                 {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
  35.     table,tr,td       {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
  36.     th                {font:bold 10pt Arial,Helvetica,sans-serif; color:White; background:#0066cc; padding:0px 0px 0px 0px;} -
  37.     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;} -
  38.     h2                {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
  39.         a                 {font:10pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
  40.   </style>' -
  41. body   'BGCOLOR="#C0C0C0"' -
  42. table  'WIDTH="90%" BORDER="1"'

  43. define fileName=xifenfei_db_recover
  44. COLUMN spool_time NEW_VALUE _spool_time NOPRINT
  45. SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;

  46. spool &FileName._&_spool_time..html
  47. set markup html on entmap off
  48. prompt &reportHeader

  49. SET MARKUP HTML ON
  50. set define ~
  51. col CONTACT for a10
  52. select 'Phone' "CONTACT",'13429648788' "INFORMATION"  from dual
  53. union all
  54. select
  55. 'Q Q','107644445'
  56. from dual
  57. union all
  58. select 'E-Mail','dba@xifenfei.com' from dual;

  59. --current_date
  60. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Execution Time</b></font><hr align="center" width="250"></center>
  61. select sysdate as current_date from dual;

  62. --version
  63. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>DB Version</b></font><hr align="center" width="250"></center>
  64. select * from v$version;


  65. --instance
  66. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Instance Information</b></font><hr align="center" width="250"></center>
  67. select INSTANCE_NUMBER,INSTANCE_NAME,host_name,STATUS,STARTUP_TIME,THREAD# from Gv$instance;


  68. --database
  69. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Controlfile Information</b></font><hr align="center" width="300"></center>
  70. select dbid, name,open_mode,
  71.        created created,
  72.        open_mode, log_mode,
  73.        checkpoint_change# as checkpoint_change#,
  74.        controlfile_type ctl_type,
  75.        controlfile_created ctl_created,
  76.        controlfile_change# as ctl_change#,
  77.        controlfile_time ctl_time,
  78.        resetlogs_change# as resetlogs_change#,
  79.        resetlogs_time resetlogs_time
  80. from v$database;

  81. --scn
  82. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>SCN Information</b></font><hr align="center" width="300"></center>
  83. 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
  84. FROM  
  85. (
  86. select tim, scn,
  87.   ((
  88.   ((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
  89.   ((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
  90.   (((to_number(to_char(tim,'DD'))-1))*24*60*60) +
  91.   (to_number(to_char(tim,'HH24'))*60*60) +
  92.   (to_number(to_char(tim,'MI'))*60) +
  93.   (to_number(to_char(tim,'SS')))
  94.   ) * (16*1024)) chk16kscn
  95.   from
  96. (select sysdate tim,checkpoint_change# scn from v$database)
  97. )
  98. ORDER BY tim;


  99. --parameter
  100. 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>
  101. SELECT
  102.     p.name,i.instance_name ,p.value
  103. FROM
  104.     gv$parameter p
  105.   , gv$instance  i
  106. WHERE
  107.     p.inst_id = i.inst_id
  108. and  isdefault='FALSE'
  109. ORDER BY
  110.     p.name
  111.   , i.instance_name;

  112. --TABLESPACE
  113. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Tablespace Information</b></font><hr align="center" width="300"></center>
  114. select * from (
  115. select a.ts#,a.name,sum(bytes)/1024/1024/1024 ts_size_g from v$datafile b,
  116. v$tablespace a where a.ts#=b.ts# group by a.ts#,a.name
  117. union all
  118. select a.ts#,a.name||'@TEMPORARY',sum(bytes)/1024/1024/1024 from v$tempfile b,
  119. v$tablespace a where a.ts#=b.ts# group by a.ts#,a.name
  120. union all
  121. select null, 'ALL_SIZE(G)',SUM(BYTES/1024/1024/1024)  FROM V$datafile) order by 1;


  122. --v$datafile
  123. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Datafile Information</b></font><hr align="center" width="300"></center>
  124. select
  125. ts#,
  126. file#,
  127. BYTES/1024/1024/1024 file_size_G,
  128. status,
  129. enabled,
  130. CREATION_TIME,
  131. checkpoint_change# "SCN",
  132. last_change# "STOP_SCN",
  133. offline_change#,
  134. online_change#,
  135. online_time,
  136. name
  137. from v$datafile
  138. order by 1,2;

  139. --v$datafile_header
  140. 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>

  141. select
  142. ts#,
  143. file#,
  144. bytes/1024/1024/1024 file_size_G,
  145. TABLESPACE_NAME,
  146. status,
  147. ERROR,
  148. FORMAT,
  149. recover,
  150. FUZZY,
  151. CREATION_TIME CREATE_TIME,
  152. checkpoint_change# checkpoint_change,
  153. RESETLOGS_CHANGE# "RESETLOGS_CHANGE",
  154. resetlogs_time
  155. from v$datafile_header
  156. order by 1,2;

  157. 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>

  158. 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;

  159. SELECT status,
  160. checkpoint_change#,checkpoint_time,FUZZY,RESETLOGS_CHANGE#,count(*) ROW_NUM
  161. FROM v$datafile_header
  162. GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy,RESETLOGS_CHANGE#
  163. ORDER BY status, checkpoint_change#, checkpoint_time;

  164. SELECT dd.FILE#,
  165. dd.NAME,
  166. dd.STATUS,
  167. dd.checkpoint_change# dfile_chkp_change,
  168. dh.checkpoint_change# dfile_hed_chkp_change,
  169. dh.recover,
  170. dh.fuzzy
  171. FROM v$datafile dd,
  172. v$datafile_header dh
  173. WHERE dd.FILE#=dh.FILE#
  174. AND dd.checkpoint_change#<>dh.checkpoint_change#;


  175. --x$kcvfh
  176. 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>

  177. /*
  178. fhsta
  179. 64  normal rman fuzzy
  180. 4   normal fuzzy
  181. 8192 system good
  182. 8196 system fuzzy
  183. 0   normal good
  184. 8256 system rman fuzzy
  185. */
  186. select
  187. FHTSN  TS#,
  188. HXFIL FILE#,
  189. FHRFN RFILE#,
  190. DECODE(HXONS,0,'OFFLINE','ONLINE') F_STATUS,
  191. 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,
  192. FHSTA STAUTUS,
  193. decode(hxifz, 0,'NO', 1,'YES', NULL) FUZZY,
  194. FHCRS CRT_SCN,
  195. FHCRT CRT_TIME,
  196. FHSCN CPT_SCN,
  197. FHTIM CPT_TIME,
  198. FHRLS RESETLOGS_SCN,
  199. FHTHR THREAD#,
  200. FHRBA_SEQ SEQUENCE#
  201. from x$kcvfh order by FHTSN,FHRFN;

  202. SELECT fhthr thread,
  203. fhrba_seq sequence,
  204. fhscn scn,
  205. fhsta status,
  206. count(*) ROW_NUM
  207. FROM x$kcvfh
  208. group by fhthr,fhrba_seq,fhscn,fhsta;

  209. select max(fhafs) "Minimum PITR SCN" from x$kcvfh;

  210. --redo
  211. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Redofile Information</b></font><hr align="center" width="300"></center>
  212.   SELECT thread#,a.group#,
  213.          a.sequence#,a.bytes/1024/1024 "SIZE(M)",
  214.          first_change# "F_SCN",
  215.          a.FIRST_TIME,
  216.          a.ARCHIVED,
  217.          a.status,
  218.          MEMBER
  219.     FROM v$log a, v$logfile b
  220.    WHERE a.group# = B.GROUP#
  221. ORDER BY thread#,a.sequence# DESC;


  222. --incarnation
  223. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Incarnation Information</b></font><hr align="center" width="350"></center>
  224. select * from v$database_incarnation;


  225. --v$backup begin backup
  226. 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>
  227. select file#,CHANGE# "SCN",
  228. TIME "TIME" from v$backup;

  229. --v$archived_log
  230. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Archivelog Information</b></font><hr align="center" width="300"></center>
  231. col name for a50
  232. select
  233. thread#,sequence# sequence#,
  234. FIRST_CHANGE# FIRST_CHANGE#,
  235. FIRST_TIME FIRST_TIME,
  236. NEXT_CHANGE# NEXT_CHANGE#,
  237. NEXT_TIME NEXT_TIME,
  238. name from (
  239. select  rownum rn,a.* from
  240. (
  241. select
  242. sequence#,thread#,
  243. FIRST_CHANGE#,
  244. FIRST_TIME,
  245. NEXT_CHANGE#,
  246. NEXT_TIME,
  247. name from v$archived_log
  248. where DELETED='NO'
  249. order by NEXT_TIME desc
  250. ) a
  251. ) where rn<100;
  252. 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#;

  253. --$recovery_log v$recover_file
  254. 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>
  255. select THREAD#,SEQUENCE# SEQUENCE#,
  256. TIME "TIME"
  257. from v$recovery_log;

  258. select file#,online_status "STATUS",
  259. change# "SCN",
  260. time"TIME"
  261. from v$recover_file;

  262. SELECT a.recid,
  263. a.thread#,
  264. a.sequence#,
  265. a.name,
  266. a.first_change#,
  267. a.NEXT_CHANGE#,
  268. a.archived,
  269. a.deleted,
  270. a.completion_time
  271. FROM v$archived_log a, v$recovery_log l
  272. WHERE a.thread# = l.thread#
  273. AND a.sequence# = l.sequence#;

  274. --rman backup
  275. prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Rman Information</b></font><hr align="center" width="250"></center>
  276.     SELECT A.RECID "BACKUP SET",
  277.          A.SET_STAMP,
  278.          DECODE (B.INCREMENTAL_LEVEL,
  279.                  '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
  280.                  1, 'Incr-1',
  281.                  0, 'Incr-0',
  282.                  B.INCREMENTAL_LEVEL)
  283.             "Type LV",
  284.          B.CONTROLFILE_INCLUDED "including CTL",
  285.          DECODE (A.STATUS,
  286.                  'A', 'AVAILABLE',
  287.                  'D', 'DELETED',
  288.                  'X', 'EXPIRED',
  289.                  'ERROR')
  290.             "STATUS",
  291.          A.DEVICE_TYPE "Device Type",
  292.          A.START_TIME "Start Time",
  293.          A.COMPLETION_TIME "Completion Time",
  294.          A.ELAPSED_SECONDS "Elapsed Seconds",
  295.          A.TAG "Tag",
  296.          A.HANDLE "Path"
  297.     FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
  298.    WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
  299. ORDER BY A.COMPLETION_TIME DESC;
  300. spool off

  301. host echo Please check and upload "xifenfei_db_recover_YYYYMMDD.html" in current directory
  302. exit;
复制代码




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

Q Q:107644445
Tel:13429648788
Email:dba@xifenfei.com
个人Blog(惜分飞)
提供专业ORACLE技术支持(数据恢复,安装实施,升级迁移,备份容灾,故障诊断,系统优化等)
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|ORACLE SOS 技术论坛

GMT+8, 2021-9-26 21:16 , Processed in 0.016841 second(s), 20 queries .

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表