ORACLE 数据库巡检报告脚本

最近由于某银行数据库巡检工作，需要把数据库的巡检以报告体现出来，ORACLE数据库有AWR报告。那我是不是可以搞个脚本。

以巡检报告方式体现出来，一则可以拿出来可以唬人，二则还是有点实际用途，可以会大大提高效率。good idea,想到就干。先按原来要求来版简单的

后继改进。上脚本，喜欢的小伙伴拿去，不谢！

****************************************************************

# made by brain zhang

# products made by brain zhang is competitive products

****************************************************************

SET MARKUP HTML ON SPOOL ON pre off entmap off

SET ECHO OFF

SET TERMOUT OFF

SET TRIMOUT OFF

set feedback off

set heading on

set linesize 200

set pagesize 10000

col tablespace_name format a15

col total_space format a10

col free_space format a10

col used_space format a10

col used_rate format 99.99

column dbid new_value spool_dbid

column inst_num new_value spool_inst_num

select dbid from v$database where rownum = 1;

select instance_number as inst_num from v$instance where rownum = 1;

column spoolfile_name new_value spoolfile

select 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_name from v$instance where rownum=1)

||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi')||'_static' as spoolfile_name from dual;

spool &&spoolfile..html

set line 140 pages 9000;

col action_time for a30;

col action for a10;

col namespace for a15;

col version for a20;

col comments for a30;

prompt system info check

!/sbin/ip addr

!hostname

!df -h

!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep ora-

!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep err

!tail -10000 $ORACLE_BASE/admin/$ORACLE_SID/bdump/al*|grep fail

prompt 1.database version and patch check

select action_time,action, namespace,version,comments from dba_registry_history;

prompt 2.database id check

select dbid from v$database;

prompt 3. database force logging、SUPPLEMENTAL_LOG_DATA_MIN、FLASHBACK_ON check

col FORCE_LOGGING for a3;

col SUPPLEMENTAL_LOG_DATA_MIN for a10;

col SUPPLEMENTAL_LOG_DATA_PK for a3;

col SUPPLEMENTAL_LOG_DATA_UI for a3;

col SUPPLEMENTAL_LOG_DATA_FK for a3

col SUPPLEMENTAL_LOG_DATA_ALL for a3;

col FLASHBACK_ON for a15;

select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK

,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL

,FLASHBACK_ON

from v$database;

prompt 4. database SESSIONS_CURRENT、SESSIONS_HIGHWATER

select INST_ID,SESSIONS_CURRENT,SESSIONS_HIGHWATER from gv$license;

prompt 5. database profiles

col limit for a30;

select * from dba_profiles order by 1;

prompt 6. database language

select userenv('language') from dual;

prompt 7. database instance status

col INSTANCE_NAME for a20;

col host_name for a20;

select inst_id,instance_number,instance_name,host_name,status

from gv$instance;

prompt 8. database sum size

select sum(bytes)/1024/1024/1024 as GB from dba_segments;

prompt 9. database controlfile

COL NAME FOR A50;

select * from v$controlfile;

prompt 10. database logfile

select THREAD#,GROUP#,SEQUENCE#, BYTES/1024/1024,status,FIRST_TIME from v$log;

col member for a50;

select * from v$logfile;

prompt 11. database archive

archive log list;

prompt 12. database tablespace check

col file_name for a50;

col tablespace_name for a20;

select file_id,tablespace_name,file_name,bytes/1024/1024,status,AUTOEXTENSIBLE,MAXBYTES/1024/1024

from dba_data_files;

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",

ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

UNION ALL --if have tempfile

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,

USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",

NVL(FREE_SPACE,0) "FREE_SPACE(M)"

FROM

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D,

(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

prompt 13. database backup

!sh rman_back.sh

spool off

exit;

#check rman back scripts

cat >rman_back.sh

rman target /

list backup of database summary;

quit

EOF