ORACLE SOS-SQL 脚本-Oracle 技术资料-Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)

xifenfei 发表于 2018-7-12 12:14:16

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

prompt
prompt +----------------------------------------------------------------------------+
prompt | Oracle Database Recovery Check Result |
prompt |----------------------------------------------------------------------------+
prompt |Copyright (c) 2010-2016 xifenfei. All rights reserved. (www.xifenfei.com) |
prompt +----------------------------------------------------------------------------+
prompt
prompt Please start the database to mount state.
prompt Note: Do not modify any inspection results
prompt Please start the database to mount state.
prompt Note: Do not modify any inspection results
prompt To send xifenfei_db_recover_YYYYMMDD.html to dba@xifenfei.com or QQ(107644445)
prompt Please refer to the use of the script:http://www.xifenfei.com/oracle_recovery_check
prompt

define reportHeader="Oracle Database Recovery Check Result<hr>Copyright (c) 2010-2016 <a target=""_blank"" href=""http://www.xifenfei.com/oracle_recovery_check"">XIFENFEI</a>. All rights reserved."

set termout off
set echo off
set feedback off
set verify off
set wrap on
set trimspool on
set serveroutputon
set escape on
set pagesize 50000
set long 2000000000
set numw 16
col error format a30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set markup html on spool on preformat off entmap on -
head ' -
<title>Oracle Database recovery check result</title> -
<style type="text/css"> -
body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} -
table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
th {font:bold 10pt Arial,Helvetica,sans-serif; color:White; background:#0066cc; padding:0px 0px 0px 0px;} -
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;} -
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
a {font:10pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
</style>' -
body 'BGCOLOR="#C0C0C0"' -
table'WIDTH="90%" BORDER="1"'

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

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

SET MARKUP HTML ON
set define ~
col CONTACT for a10
select 'Phone' "CONTACT",'13429648788' "INFORMATION"from dual
union all
select
'Q Q','107644445'
from dual
union all
select 'E-Mail','dba@xifenfei.com' from dual;

--current_date
prompt <center>Execution Time<hr align="center" width="250"></center>
select sysdate as current_date from dual;

--version
prompt <center>DB Version<hr align="center" width="250"></center>
select * from v$version;

--instance
prompt <center>Instance Information<hr align="center" width="250"></center>
select INSTANCE_NUMBER,INSTANCE_NAME,host_name,STATUS,STARTUP_TIME,THREAD# from Gv$instance;

--database
prompt <center>Controlfile Information<hr align="center" width="300"></center>
select dbid, name,open_mode,
 created created,
 open_mode, log_mode,
 checkpoint_change# as checkpoint_change#,
 controlfile_type ctl_type,
 controlfile_created ctl_created,
 controlfile_change# as ctl_change#,
 controlfile_time ctl_time,
 resetlogs_change# as resetlogs_change#,
 resetlogs_time resetlogs_time
from v$database;

--scn
prompt <center>SCN Information<hr align="center" width="300"></center>
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
FROM
(
select tim, scn,
((
((to_number(to_char(tim,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(tim,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(tim,'DD'))-1))*24*60*60) +
(to_number(to_char(tim,'HH24'))*60*60) +
(to_number(to_char(tim,'MI'))*60) +
(to_number(to_char(tim,'SS')))
) * (16*1024)) chk16kscn
from
(select sysdate tim,checkpoint_change# scn from v$database)
)
ORDER BY tim;

--parameter
prompt <center>Non-default Parameter<hr align="center" width="300"></center>
SELECT
p.name,i.instance_name ,p.value
FROM
gv$parameter p
, gv$instancei
WHERE
p.inst_id = i.inst_id
andisdefault='FALSE'
ORDER BY
p.name
, i.instance_name;

--TABLESPACE
prompt <center>Tablespace Information<hr align="center" width="300"></center>
select * from (
select a.ts#,a.name,sum(bytes)/1024/1024/1024 ts_size_g from v$datafile b,
v$tablespace a where a.ts#=b.ts# group by a.ts#,a.name
union all
select a.ts#,a.name||'@TEMPORARY',sum(bytes)/1024/1024/1024 from v$tempfile b,
v$tablespace a where a.ts#=b.ts# group by a.ts#,a.name
union all
select null, 'ALL_SIZE(G)',SUM(BYTES/1024/1024/1024)FROM V$datafile) order by 1;

--v$datafile
prompt <center>Datafile Information<hr align="center" width="300"></center>
select
ts#,
file#,
BYTES/1024/1024/1024 file_size_G,
status,
enabled,
CREATION_TIME,
checkpoint_change# "SCN",
last_change# "STOP_SCN",
offline_change#,
online_change#,
online_time,
name
from v$datafile
order by 1,2;

--v$datafile_header
prompt <center>Datafile Header Information<hr align="center" width="350"></center>

select
ts#,
file#,
bytes/1024/1024/1024 file_size_G,
TABLESPACE_NAME,
status,
ERROR,
FORMAT,
recover,
FUZZY,
CREATION_TIME CREATE_TIME,
checkpoint_change# checkpoint_change,
RESETLOGS_CHANGE# "RESETLOGS_CHANGE",
resetlogs_time
from v$datafile_header
order by 1,2;

prompt <center>Datafile and Datafile Header Information<hr align="center" width="350"></center>

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;

SELECT status,
checkpoint_change#,checkpoint_time,FUZZY,RESETLOGS_CHANGE#,count(*) ROW_NUM
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy,RESETLOGS_CHANGE#
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
dd.checkpoint_change# dfile_chkp_change,
dh.checkpoint_change# dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#
AND dd.checkpoint_change#<>dh.checkpoint_change#;

--x$kcvfh
prompt <center>x$kcvfh Information<hr align="center" width="350"></center>

/*
fhsta
64normal rman fuzzy
4 normal fuzzy
8192 system good
8196 system fuzzy
0 normal good
8256 system rman fuzzy
*/
select
FHTSNTS#,
HXFIL FILE#,
FHRFN RFILE#,
DECODE(HXONS,0,'OFFLINE','ONLINE') F_STATUS,
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,
FHSTA STAUTUS,
decode(hxifz, 0,'NO', 1,'YES', NULL) FUZZY,
FHCRS CRT_SCN,
FHCRT CRT_TIME,
FHSCN CPT_SCN,
FHTIM CPT_TIME,
FHRLS RESETLOGS_SCN,
FHTHR THREAD#,
FHRBA_SEQ SEQUENCE#
from x$kcvfh order by FHTSN,FHRFN;

SELECT fhthr thread,
fhrba_seq sequence,
fhscn scn,
fhsta status,
count(*) ROW_NUM
FROM x$kcvfh
group by fhthr,fhrba_seq,fhscn,fhsta;

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

--redo
prompt <center>Redofile Information<hr align="center" width="300"></center>
SELECT thread#,a.group#,
 a.sequence#,a.bytes/1024/1024 "SIZE(M)",
 first_change# "F_SCN",
 a.FIRST_TIME,
 a.ARCHIVED,
 a.status,
 MEMBER
FROM v$log a, v$logfile b
WHERE a.group# = B.GROUP#
ORDER BY thread#,a.sequence# DESC;

--incarnation
prompt <center>Incarnation Information<hr align="center" width="350"></center>
select * from v$database_incarnation;

--v$backup begin backup
prompt <center>Hot Backup Information<hr align="center" width="350"></center>
select file#,CHANGE# "SCN",
TIME "TIME" from v$backup;

--v$archived_log
prompt <center>Archivelog Information<hr align="center" width="300"></center>
col name for a50
select
thread#,sequence# sequence#,
FIRST_CHANGE# FIRST_CHANGE#,
FIRST_TIME FIRST_TIME,
NEXT_CHANGE# NEXT_CHANGE#,
NEXT_TIME NEXT_TIME,
name from (
selectrownum rn,a.* from
(
select
sequence#,thread#,
FIRST_CHANGE#,
FIRST_TIME,
NEXT_CHANGE#,
NEXT_TIME,
name from v$archived_log
where DELETED='NO'
order by NEXT_TIME desc
) a
) where rn<100;
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 whereDELETED='NO' group by thread#;

--$recovery_log v$recover_file
prompt <center>V$recovery_log and V$recover_file<hr align="center" width="500"></center>
select THREAD#,SEQUENCE# SEQUENCE#,
TIME "TIME"
from v$recovery_log;

select file#,online_status "STATUS",
change# "SCN",
time"TIME"
from v$recover_file;

SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.first_change#,
a.NEXT_CHANGE#,
a.archived,
a.deleted,
a.completion_time
FROM v$archived_log a, v$recovery_log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;

--rman backup
prompt <center>Rman Information<hr align="center" width="250"></center>
SELECT A.RECID "BACKUP SET",
 A.SET_STAMP,
 DECODE (B.INCREMENTAL_LEVEL,
 '', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
 1, 'Incr-1',
 0, 'Incr-0',
 B.INCREMENTAL_LEVEL)
 "Type LV",
 B.CONTROLFILE_INCLUDED "including CTL",
 DECODE (A.STATUS,
 'A', 'AVAILABLE',
 'D', 'DELETED',
 'X', 'EXPIRED',
 'ERROR')
 "STATUS",
 A.DEVICE_TYPE "Device Type",
 A.START_TIME "Start Time",
 A.COMPLETION_TIME "Completion Time",
 A.ELAPSED_SECONDS "Elapsed Seconds",
 A.TAG "Tag",
 A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;
spool off

host echo Please check and upload "xifenfei_db_recover_YYYYMMDD.html" in current directory
exit;

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

页: [1]

ORACLE SOS's Archiver

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