more xt.sh
ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1;export ORACLE_HOME
ORACLE_OWNER=oracle;export ORACLE_OWNER
ORACLE_SID=whx;export ORACLE_SID
ORACLE_BASE=/opt/ora10g;export ORACLE_BASE
LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin:$LD_LIBRARY_PATH;export PATH
NLS_LANG=AMERICAN_AMERICA.ZHS16CGB231280;export NLS_LANG
FILE_NAME=/home/oracle/report`date +"%Y%m%d%H"`.lst
sqlplus "sys/a123456 as sysdba" <
exit;
spool off;
EOF
echo "########################### linux###############################################" >>$FILE_NAME
echo "##### listener status ##########################################################" >>$FILE_NAME
lsnrctl status >>$FILE_NAME
echo "##### system model######################################################uname -a" >>$FILE_NAME
uname -a >>$FILE_NAME
cat /etc/redhat-release >>$FILE_NAME
echo "##### linux"
echo "##### hostname#########################################################hostname">>$FILE_NAME
hostname >>$FILE_NAME
echo "##### oracle emergency################################alert_whx.log|ora-err-fail" >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i ora- >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i err- >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i fail- >>$FILE_NAME
echo "##### db_recovery_file_dest_size used###############################alert_whx.log" >>$FILE_NAME
cat /opt/ora10g/admin/whx/bdump/alert_whx.log |grep -i db_recovery_file_dest_size >>$FILE_NAME
echo "##### system space##########################################################df -k" >>$FILE_NAME
df -k >>$FILE_NAME
echo "##### runtime##############################################################uptime" >>$FILE_NAME
uptime >>$FILE_NAME
echo "##### system user# #################################################cat/etc/passwd" >>$FILE_NAME
tail -10 /etc/passwd >>$FILE_NAME
echo "##### disk I/O ##########################################################sar 3 4" >>$FILE_NAME
sar 3 4 >>$FILE_NAME
echo "##### system log in###########################################################who" >>$FILE_NAME
who >>$FILE_NAME
echo "##### scheduled tast ##################################################crontab -l" >>$FILE_NAME
crontab -l >>$FILE_NAME
echo "##### cpu status#######################################################vmstat 2 4" >>$FILE_NAME
vmstat 2 4 >>$FILE_NAME
echo "##### free status ###########################################################free" >>$FILE_NAME
free >>$FILE_NAME
echo "##### disk status######################################################iostat 2 3" >>$FILE_NAME
iostat 2 3 >iostat23
head -10 iostat23 >>$FILE_NAME
echo "##### memory status###########################################################top" >>$FILE_NAME
top -b -n 1 -d 1 >top10
head -10 top10 >>$FILE_NAME
echo "##### system user group ###########################################cat /etc/group" >>$FILE_NAME
tail -10 /etc/group >>$FILE_NAME
echo "##### route ##########################################################netstat -rn" >>$FILE_NAME
netstat -rn >>$FILE_NAME
echo "##### THE END ##############################################" >>$FILE_NAME
其中数据库脚本b.sql如下
more b.sql
column today new_value today_file
select to_char(sysdate,'yyyymmddhh24') as today from dual;
spool report&today_file
prompt ##### select * from v$version##################################################
select * from v$version;
---set heading on
prompt ##### select status from v$instance ###########################################
select status from v$instance;
prompt ##### tablespace surplus proportion ###########################################
select a.tablespace_name, substr((f.free_space / a.total) * 100,1,6) free
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) free_space
from dba_free_space a
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
order by to_number(free);
prompt ##### temp tablespace ##########################################################
select b.bytes/a.bytes*100 ||'%' from
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a ,
( select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) b;
prompt ##### invalid objects###########################################################
SELECT owner , object_name, object_type,status ,LAST_DDL_TIME FROM dba_objects WHERE status like 'INVALID';
prompt ##### database user status #####################################################
col username for a15
col account_status for a20
col default_tablespace for a10
col temporary_tablespace for a10
select a.username,
a.account_status,
a.default_tablespace,
a.temporary_tablespace
from dba_users a, dba_objects b
where a.username = b.owner(+) and a.account_status='OPEN'
group by a.username,
a.account_status,
a.default_tablespace,
a.temporary_tablespace;
prompt ###### controlfile status ####################################################
col name for a45
col status for a4
col block_size for 999999
select status,name,block_size from v$controlfile;
prompt ##### logfile status #########################################################
col status for a7
col type for a8
col member for a45
col is_recovery_dest_file for a5
select status,type,member,is_recovery_dest_file from v$logfile;
prompt ##### database dbid,log_mode#################################################
col name for a10
Select dbid,name,created,current_scn,log_mode From V$Database;
prompt ##### database constraint####################################################
Select constraint_type,constraint_name,table_name, status From dba_constraints Where status<>'ENABLED' AND owner not in ('SYSTEM','O
LAPSYS');
prompt ##### disabled trigger#######################################################
SELECT owner ,trigger_name,status FROM dba_triggers where status = 'DISABLED' and owner not in ('SYS','EXFSYS') AND TABLE_OWNER='SYS
';
prompt ##### disabled index ########################################################
SELECT owner ,index_name,status FROM dba_indexes where status = 'UNUSABLE';
prompt ##### temp tablespace used G ################################################
select sum(blocks)*8192/1024/1024/1024 from v$sort_usage;
prompt ##### SGA ###################################################################
select * from
(SELECT 1 - (phy.value-phyd.value)/( (cur.value + con.value-phyd.value)) "cache hit>90"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy,v$sysstat phyd
WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
AND phy.name = 'physical reads' and phyd.NAME = 'physical reads direct' ),
(select sum(pinhits)/sum(pins) "sql library>95" from v$librarycache) ,
(select 1-SUM(GETMISSES)/SUM(GETS) "rowcache>85" from V$ROWCACHE);
prompt ##### tablespace sum########################################################
select nvl(t.owner,'total:') "USER",
sum(bytes)/1024/1024/1024 "percentage"
from dba_segments t
group by rollup(t.owner)
order by 2 ;
prompt ##### memory consumption ###################################################
col sql_txt for a45
col buffer/exes for 99999999
col executions for 999999
select * from (
select trunc(t.BUFFER_GETS/t.EXECUTIONS) "buffer/exes",
t.EXECUTIONS ,t.SQL_TEXT
from v$sql t where t.EXECUTIONS > 0 order by 1 desc )
where rownum <=2;
prompt ##### table don't have index###############################################
col owner for a7
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
select owner, segment_name, segment_type, tablespace_name FROM dba_segments t WHERE NOT EXISTS ( SELECT 'x' FROM dba_indexes i
WHERE t.owner = i.table_owner AND t.segment_name = i.table_name) AND t.segment_type IN ('TABLE', 'TABLE PARTITION') AND t.owner
='WHX' ORDER BY 2 DESC;
prompt ##### lock table##########################################################
col username for a10
col sid for 9999999
col serial for 99999
col logon_time for a15
select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logo
n_time;
spool off
exit;
-----注意
在crontab 中，不要用more 最好用cat ，因为more会产生mail