SET MARKUP HTML ON SPOOL ON pre off entmap off;

set feedback off;

set termout off;

set echo off;

set verify off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

spool /tmp/check_oracle.html

prompt <center>version infomation<hr align="center" width="250"></center>

select BANNER "version_info" from v$version;

prompt <center>PLATFORM infomation<hr align="center" width="250"></center>

select * from dba_registry_database;

prompt <center>Controlfile Information<hr align="center" width="250"></center>

select dbid, name,

to_char(created,'YYYY-MM-DD') created,

open_mode, log_mode,

to_char(checkpoint_change#) as checkpoint_change#,

controlfile_type ctl_type,

to_char(controlfile_created,'YYYY-MM-DD') ctl_created,

to_char(controlfile_change#) as ctl_change#,

to_char(controlfile_time,'YYYY-MM-DD') ctl_time,

resetlogs_change# as resetlogs_change#,

to_char(resetlogs_time,'YYYY-MM-DD') resetlogs_time,

FLASHBACK_ON

from v$database;

/*

fhsta

64 normal rman fuzzy

4 normal fuzzy

8192 system good

8196 system fuzzy

0 normal good

8256 system rman fuzzy

*/

prompt <center>datafile scn info<hr align="center" width="250"></center>

select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

prompt <center>instance Information<hr align="center" width="250"></center>

select instance_name, host_name, status, archiver, database_status, instance_role, active_state from v$instance;

prompt <center>parameter Information<hr align="center" width="250"></center>

SELECT

p.name,i.instance_name ,p.value

FROM

gv$parameter p

, gv$instance i

WHERE

p.inst_id = i.inst_id

and isdefault='FALSE'

ORDER BY

p.name

, i.instance_name;

prompt <center>important parameter<hr align="center" width="250"></center>

select v.name,v.VALUE,v.TYPE,v.NUM from v$parameter v where v.name like '%process%' or v.name like '%session%' or v.name like '%sga%' or v.name like '%pga%' or v.name like '%memory%' or v.name like '%cursor%' order by v.NAME;

prompt <center>database Information<hr align="center" width="250"></center>

select protection_mode, protection_level, remote_archive, database_role, dataguard_broker,guard_status from v$database;

prompt <center>log mode<hr align="center" width="250"></center>

select log_mode from v$database;

col force_logging for a15;

col supplemental_log_data_min for a25;

col supplemental_log_data_pk for a25;

col supplemental_log_data_ui for a25;

col supplemental_log_data_fk for a25;

col supplemental_log_data_all for a25;

prompt <center>log Information<hr align="center" width="250"></center>

select force_logging,supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui,

supplemental_log_data_fk, supplemental_log_data_all

from v$database;

prompt <center>property Information<hr align="center" width="250"></center>

select * from database_properties;

--v$datafile_header

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

select ts#,file#,TABLESPACE_NAME,status,ERROR,FORMAT,recover,FUZZY,

to_char(CREATION_TIME,'YYYY-MM-dd') CREATE_TIME,

to_char(checkpoint_change#) "SCN",

RESETLOGS_CHANGE# "RESETLOGS SCN"

from v$datafile_header

order by 1,2;

col archived for a10;

prompt <center>redo Information<hr align="center" width="350"></center>

select a.inst_id,a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,to_char(a.first_time,'YYYY-MM-dd') first_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.inst_id,a.sequence#,a.members,a.archived,a.status,first_time order by group#;

--v$recover_file

prompt <center>V$recover_file Information<hr align="center" width="350"></center>

select file#,online_status "STATUS",

change# "SCN",

time"TIME"

from v$recover_file;

prompt <center>Archived Information<hr align="center" width="350"></center>

SELECT

to_char(first_time,'YYYY-MM-DD') day,

to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",

to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",

to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",

to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",

to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",

to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",

to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",

to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",

to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",

to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",

to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",

to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",

to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",

to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",

to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",

to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",

to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",

to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",

to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",

to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",

to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",

to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",

to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",

to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"

from

v$log_history

GROUP by

to_char(first_time,'YYYY-MM-DD') order by day desc;

prompt <center>Tablespace Usage<hr align="center" width="350"></center>

select a.tablespace_name,

round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,

round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,

round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,

round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_Free,

100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_used,

round(maxbytes/1048576,2) Max,

round(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) / round(maxbytes/1048576,2) * 100,2) || '%' "USED_MAX%"

from ( select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

from dba_data_files f

group by tablespace_name) a,

( select f.tablespace_name,

sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name (+)

union all

select h.tablespace_name,

round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,

round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,

round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,

round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' Pct_Free,

100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' pct_used,

round(sum(f.maxbytes) / 1048576, 2) max,

round(round(sum(nvl(p.bytes_used, 0))/ 1048576, 2)/round(sum(f.maxbytes) / 1048576, 2) * 100,2)||'%' "USED_MAX%"

from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f

where p.file_id(+) = h.file_id

and p.tablespace_name(+) = h.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = h.tablespace_name

group by h.tablespace_name

ORDER BY 1;

--temp_info

prompt <center>Temp Infomation<hr align="center" width="350"></center>

SELECT S.sid || ',' || S.serial# sid_serial,

S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used,

T.tablespace,

T.sqladdr address,

Q.hash_value,T.SQL_ID,

Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address(+)

AND T.tablespace = TBS.tablespace_name

ORDER BY mb_used desc;

select r.INSTANCE_NUM,r.TABLESPACE_NAME,segment_name,r.STATUS, v.rssize/1024/1024 mb

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;

--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",

to_char(A.START_TIME,'YYYY-MM-DD') "Start Time",

to_char(A.COMPLETION_TIME,'YYYY-MM-DD') "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 '<div style="display:none">'>>/tmp/check_oracle.html

host echo '---- Starting uname -a -----'

host echo ''

host echo '------------------------------ uname -a ------------------------------'>>/tmp/check_oracle.html

host uname -a >>/tmp/check_oracle.html

host echo '----Starting Collect PATCH Information----'

host echo ''

host echo '------------------------------ ORACLE PATCH ------------------------------'>>/tmp/check_oracle.html

host $ORACLE_HOME/OPatch/opatch lsinventory >>/tmp/check_oracle.html

host echo '----Starting Collect ALERT LOG Information----'

host echo ''

host echo '------------------------------ ORACLE ALERT ------------------------------'>>/tmp/check_oracle.html

set pages 0

set markup html off

spool /tmp/chk_ora_trace_name.lst

select value from v$parameter where name='background_dump_dest';

spool off

host alert_dir=`cat /tmp/chk_ora_trace_name.lst|grep -v SQL`;current_path=`pwd`;cd $alert_dir;tail -1000 "alert_$ORACLE_SID.log" >>/tmp/check_oracle.html

host rm -rf /tmp/chk_ora_trace_name.lst

host echo '------------------------------ ORACLE DUMP------------------------------'>>/tmp/check_oracle.html

spool /tmp/chk_ora_dump_name.lst

select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,(select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,(select value from v$parameter where name = 'user_dump_dest') d;

spool off

host echo '----Starting DUMP file_hdrs Information----'

host echo ''

host dump_name=`cat /tmp/chk_ora_dump_name.lst|grep -v SQL|grep .trc`;echo '-------------------dump file_hdrs-------------------'>>$dump_name

ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 3';

host echo '----Starting DUMP controlf Information----'

host echo ''

host dump_name=`cat /tmp/chk_ora_dump_name.lst|grep -v SQL|grep .trc`;echo '-------------------dump controlf-------------------'>>$dump_name

ALTER SESSION SET EVENTS 'immediate trace name controlf level 3';

host echo '----Starting DUMP redohdr Information----'

host echo ''

host dump_name=`cat /tmp/chk_ora_dump_name.lst|grep -v SQL|grep .trc`;echo '-------------------dump redohdr-------------------'>>$dump_name

ALTER SESSION SET EVENTS 'immediate trace name redohdr level 3';

host dump_name=`cat /tmp/chk_ora_dump_name.lst|grep -v SQL|grep .trc`;cat $dump_name >>/tmp/check_oracle.html

host rm -rf /tmp/chk_ora_dump_name.lst

host echo '</ div>'>>/tmp/check_oracle.html

host echo '-----Oracle Database Recovery Check END-----'

host pwd

host ls -l /tmp/check_oracle.html

host echo ''

exit;

注：指令碼大部分內容借鑑惜分飛大神的指令碼，根據個人的需求修改補充

資料庫的常規檢查指令碼

相關文章