oracle 巡检脚本

浏览数：31 / 时间：2015年06月12日

#!/bin/sh

# Get Hostname
HOSTNAME=`hostname`

logfile=xunjian_`date +%y%m%d`.log
hostinfodir=/home/oracle/xunjian

if [[ ! -d $hostinfodir ]]
then
mkdir -p $hostinfodir
fi

date >> $hostinfodir/$logfile

echo " hostname " >> $hostinfodir/$logfile

hostname >> $hostinfodir/$logfile

echo " uname -a " >> $hostinfodir/$logfile

uname -a >> $hostinfodir/$logfile

echo " ulimit -a " >> $hostinfodir/$logfile

ulimit -a >> $hostinfodir/$logfile

echo " cat /proc/cpuinfo " >> $hostinfodir/$logfile

cat /proc/cpuinfo >> $hostinfodir/$logfile

echo " cat /proc/meminfo " >> $hostinfodir/$logfile

cat /proc/meminfo >> $hostinfodir/$logfile

echo " vmstat 3 10 " >> $hostinfodir/$logfile

vmstat 3 10 >> $hostinfodir/$logfile

echo " df -ha " >> $hostinfodir/$logfile

df -ha >>$hostinfodir/$logfile

echo " ifconfig -a " >> $hostinfodir/$logfile

ifconfig -a >> $hostinfodir/$logfile

echo " env | grep ORACLE " >> $hostinfodir/$logfile

env | grep ORACLE >> $hostinfodir/$logfile

echo " cat $ORACLE_HOME/network/admin/listener.ora " >> $hostinfodir/$logfile

cat $ORACLE_HOME/network/admin/listener.ora >> $hostinfodir/$logfile

echo " cat $ORACLE_HOME/network/admin/tnsnames.ora " >> $hostinfodir/$logfile

cat $ORACLE_HOME/network/admin/tnsnames.ora >> $hostinfodir/$logfile

echo "======osinfo end================"

echo "=========oracleinfo begin========"

cd $hostinfodir
sqlplus /nolog << EOF
conn / as sysdba;
SET TERMOUT OFF;
set echo off;
spool oracle.txt;
set linesize 200;
col name format a20;
col value format a30;
--log file size mb:
select ‘log file size unit mb:‘ infoterm from dual;
select round(bytes/(1024*1024),2) total_g from v\$log where rownum<2;
--log file total number:
select ‘log file total number:‘ infoterm from dual;
select count(*) from v\$log;
--log file number in one group:
select ‘log file number in one group:‘ infoterm from dual;
select num from (select count(*) num from v\$logfile group by GROUP#) where rownum<2;
--sga info:
show sga
select ‘sga info:‘ infoterm from dual;
select name,value from v\$parameter where name
in(‘shared_pool_size‘,‘db_cache_size‘,‘db_block_buffers‘,‘log_buffer‘,‘large_pool_size‘,‘java_pool_size‘,‘pga_aggregate_target‘);
--database name
select ‘database name:‘ infoterm from dual;
select name from v\$database;
--DB_BLOCK_SIZE
select ‘DB_BLOCK_SIZE:‘ infoterm from dual;
select value from v\$parameter where name in(‘db_block_size‘);
--database version
select ‘database version:‘ infoterm from dual;
select version from v\$instance;
--datafile size;
select ‘datafile size:‘ infoterm from dual;
select round((sum(bytes)/1024/1024/1024)) total_g from dba_data_files;
--datafile characterset
select ‘datafile characterset:‘ infoterm from dual;
select value from nls_database_parameters where parameter = ‘NLS_CHARACTERSET‘;
--archive
select ‘archive:‘ infoterm from dual;
select log_mode from v\$database;
archive log list;
--datafile number:
select ‘datafile number:‘ infoterm from dual;
select count(*) from dba_data_files;
--tablespace number:
select ‘tablespace number:‘ infoterm from dual;
select count(*) from dba_tablespaces;
--controlfile number:
select ‘controlfile number:‘ infoterm from dual;
select count(*) from v\$controlfile;
--controlfile info:
select ‘controlfile info:‘ infoterm from dual;
col NAME format a60
select * from v\$controlfile;
--logfile info:
select ‘logfile info:‘ infoterm from dual;
col member format a50;
select * from v\$logfile order by GROUP#;
select * from v\$log order by GROUP#;
--block_size info:
select ‘block_size info:‘ infoterm from dual;
select distinct block_size from dba_tablespaces;
--datafile info:
select ‘datafile info:‘ infoterm from dual;
set linesize 500 pagesize 200
col file_id for 9999999
col file_name for a55
col ts_name for a18
col cur_mb for 99999
col max_mb for 99999
select file_id,tablespace_name ts_name,file_name,status, autoextensible,
blocks/128 cur_mb, maxblocks/128 max_mb
from dba_data_files order by file_id;
--tempfile info:
select ‘tempfile info:‘ infoterm from dual;
select file_id,tablespace_name ts_name,file_name,status, autoextensible,
blocks/128 cur_mb, maxblocks/128 max_mb
from dba_temp_files order by file_id;
--tablespace info:
select ‘tablespace info:‘ infoterm from dual;
set linesize 500 pagesize 200
col tablespace_name format a20
col contents for a9
col ext_mgmt for a8
col alloc_type for a9
col ext_kb format 999999
col ssm for a6
col total_mb format a12
col free_mb format a12
col free_pct format a7
select c.tablespace_name, c.contents, c.extent_management ext_mgmt,
c.allocation_type alloc_type, c.initial_extent/1024 ext_kb,
c.segment_space_management SSM, a.total_mb, b.free_mb,
to_char(100*b.free_mb/a.total_mb, ‘999.99‘) free_pct
from
(select tablespace_name, to_char(sum(bytes)/1048576, ‘99999999.99‘) total_mb
from dba_data_files group by tablespace_name) a,
(select tablespace_name, to_char(sum(bytes)/1048576, ‘99999999.99‘) free_mb,
count(*) free_exts, to_char(max(bytes)/1048576, ‘99999999‘) max_mb,
to_char(min(bytes)/1048576, ‘99999999‘) min_mb
from dba_free_space group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name=b.tablespace_name(+) and c.tablespace_name=b.tablespace_name
order by free_pct, tablespace_name;
--system tablespace info:
select ‘system tablespace info:‘ infoterm from dual;
select owner,count(*) from dba_segments where tablespace_name=‘SYSTEM‘ group by owner;
select username, default_tablespace, temporary_tablespace from dba_users
where default_tablespace=‘SYSTEM‘ or temporary_tablespace=‘SYSTEM‘;
--invalid object info:
select ‘invalid object info:‘ infoterm from dual;
select count(*),owner from dba_objects where status=‘INVALID‘ group by owner;
select ‘invalid index info:‘ infoterm from dual;
select owner,index_name from dba_indexes where status=‘UNUSABLE‘;
--auth info:
select ‘auth info:‘ infoterm from dual;
select * from v\$pwfile_users;
col grantee for a15;
col granted_role for a15;
col admin_option for a20;
col default_role for a20;
select * from dba_role_privs where granted_role=‘DBA‘;
spool off;
exit
EOF