oracle表空间监控shell,8个DBA最常用的监控Oracle数据库的常用shell脚本

4. 清除旧的归档文件

以下的脚本将会在log文件达到90%容量的时候清空旧的归档文件：

$ df -k | grep arch

Filesystem kbytes used avail capacity Mounted on

/dev/vx/dsk/proddg/archive 71123968 30210248 40594232 43% /u08/archive

#######################################################################

## clean_arch.ksh ##

#######################################################################

#!/bin/ksh

df -k | grep arch > dfk.result

archive_filesystem=`awk -F" " '{ print $6 }' dfk.result`

archive_capacity=`awk -F" " '{ print $5 }' dfk.result`

if [ $archive_capacity > 90% ]

then

echo "Filesystem ${archive_filesystem} is ${archive_capacity} filled"

# try one of the following option depend on your need

find $archive_filesystem -type f -mtime +2 -exec rm -r {} ;

tar

rman

fi

5. 分析表和索引(以得到更好的性能)

以下我将展示假如传送参数到一个脚本中：

####################################################################

## analyze_table.sh ##

####################################################################

#!/bin/ksh

# input parameter: 1: passWord # 2: SID

if (($#<1)) then echo "Please enter 'Oracle' user password as the first parameter !" exit 0

fi

if (($#<2)) then echo "Please enter instance name as the second parameter!" exit 0

fi

要传入参数以执行该脚本，输入：

$ analyze_table.sh manager oradb1

脚本的第一部分产生了一个analyze.sql文件，里面包含了分析表用的语句。脚本的第二部分分析全部的表：

#################################################################

## analyze_table.sh ##

#################################################################

sqlplus -s '/ as sysdba' <

set heading off

set feed off

set pagesize 200

set linesize 100

spool analyze_table.sql

select 'ANALYZE TABLE ' || owner || '.' || segment_name ||

' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'

from dba_segments

where segment_type = 'TABLE'

and owner not in ('SYS', 'SYSTEM');

spool off

exit

EOF

sqlplus -s '/ as sysdba' <

@./analyze_table.sql

exit

EOF

以下是analyze.sql的一个例子：

$ cat analyze.sql

ANALYZE TABLE HIRWIN.JANUSAGE_SUMMARY ESTIMATE STATISTICS SAMPLE 10 PERCENT;

ANALYZE TABLE HIRWIN.JANUSER_PROFILE ESTIMATE STATISTICS SAMPLE 10 PERCENT;

ANALYZE TABLE APPSSYS.HIST_SYSTEM_ACTIVITY ESTIMATE STATISTICS SAMPLE 10 PERCENT;

ANALYZE TABLE HTOMEH.QUEST_IM_VERSION ESTIMATE STATISTICS SAMPLE 10 PERCENT;

ANALYZE TABLE JSTENZEL.HIST_SYS_ACT_0615 ESTIMATE STATISTICS SAMPLE 10 PERCENT;

6. 检查表空间的使用

以下的脚本检测表空间的使用。假如表空间只剩下10%，它将会发送一个警告email。

#####################################################################

## ck_tbsp.sh ##

#####################################################################

#!/bin/ksh

EDITOR=vi; export EDITOR

ORACLE_SID=PPRD10; export ORACLE_SID

ORACLE_BASE=/data/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/10.2.0; export ORACLE_HOME

LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH

TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN

NLS_LANG=american; export NLS_LANG

NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT

ORATAB=/var/opt/oracle/oratab;export ORATAB

PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATH

DBALIST="www.linuxidc.com,www.linuxidc.net";export DBALIST

sqlplus -s '/ as sysdba' <

set feed off

set linesize 100

set pagesize 200

column "USED (MB)" format a10

column "FREE (MB)" format a10

column "TOTAL (MB)" format a10

column PER_FREE format a10

spool tablespace.alert

SELECT F.TABLESPACE_NAME,

TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",

TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",

TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",

TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE

FROM (

SELECT TABLESPACE_NAME,

ROUND (SUM (BLOCKS*(SELECT VALUE/1024

FROM V\$PARAMETER

WHERE NAME = 'db_block_size')/1024)

) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME

) F,

(

SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES/1048576)) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME

) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80;

spool off

exit

EOF

if [ `cat tablespace.alert|wc -l` -gt 0 ]

then

cat tablespace.alert > tablespace.tmp

mailx -s "TABLESPACE  ALERT  for  PPRD10" $DBALIST < tablespace.tmp

fi

警告email输出的例子如下：

TABLESPACE_NAME                USED (MB)  FREE (MB)  TOTAL (MB) PER_FREE

------------------------------ ---------- ---------- ---------- ----------

SYSTEM                              519        401        920     44 %

MILLDATA                            559        441      1,000     44 %

SYSAUX                              331        609        940     65 %

MILLREPORTS                         146        254        400     64 %