最近由于某银行数据库巡检工作，需要把数据库的巡检以报告体现出来，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<<EOF
rman target / <<EOF
list backup of database summary;
quit
EOF
