Oracle inspection script

Oracle inspection script

#!/bin/sh# Get HostnameHOSTNAME=`hostname`logfile=xunjian_`date +%y%m%d`.loghostinfodir=/home/oracle/xunjianif [[ ! -d $hostinfodir ]]thenmkdir -p $hostinfodirfidate >> $hostinfodir/$logfileecho " hostname " >> $hostinfodir/$logfilehostname >> $hostinfodir/$logfileecho " uname -a " >> $hostinfodir/$logfileuname -a >> $hostinfodir/$logfileecho " ulimit -a " >> $hostinfodir/$logfileulimit -a >> $hostinfodir/$logfileecho " cat /proc/cpuinfo " >> $hostinfodir/$logfilecat /proc/cpuinfo >> $hostinfodir/$logfileecho " cat /proc/meminfo " >> $hostinfodir/$logfilecat /proc/meminfo >> $hostinfodir/$logfileecho " vmstat 3 10 " >> $hostinfodir/$logfilevmstat 3 10 >> $hostinfodir/$logfileecho " df -ha " >> $hostinfodir/$logfiledf -ha >>$hostinfodir/$logfileecho " ifconfig -a " >> $hostinfodir/$logfileifconfig -a >> $hostinfodir/$logfileecho " env | grep ORACLE " >> $hostinfodir/$logfileenv | grep ORACLE >> $hostinfodir/$logfileecho " cat $ORACLE_HOME/network/admin/listener.ora " >> $hostinfodir/$logfilecat $ORACLE_HOME/network/admin/listener.ora >> $hostinfodir/$logfileecho " cat $ORACLE_HOME/network/admin/tnsnames.ora " >> $hostinfodir/$logfilecat $ORACLE_HOME/network/admin/tnsnames.ora >> $hostinfodir/$logfileecho "======osinfo end================"echo "=========oracleinfo begin========"cd $hostinfodirsqlplus /nolog << EOFconn / 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 sgaselect 'sga info:' infoterm from dual;select name,value from v\$parameter where namein('shared_pool_size','db_cache_size','db_block_buffers','log_buffer','large_pool_size','java_pool_size','pga_aggregate_target');--database nameselect 'database name:' infoterm from dual;select name from v\$database;--DB_BLOCK_SIZEselect 'DB_BLOCK_SIZE:' infoterm from dual;select value from v\$parameter where name in('db_block_size');--database versionselect '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 charactersetselect 'datafile characterset:' infoterm from dual;select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET';--archiveselect '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 a60select * 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 200col file_id for 9999999col file_name for a55col ts_name for a18col cur_mb for 99999col max_mb for 99999select file_id,tablespace_name ts_name,file_name,status, autoextensible,blocks/128 cur_mb, maxblocks/128 max_mbfrom 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_mbfrom dba_temp_files order by file_id;--tablespace info:select 'tablespace info:' infoterm from dual;set linesize 500 pagesize 200col tablespace_name format a20col contents for a9col ext_mgmt for a8col alloc_type for a9col ext_kb format 999999col ssm for a6col total_mb format a12col free_mb format a12col free_pct format a7select 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_pctfrom(select tablespace_name, to_char(sum(bytes)/1048576, '99999999.99') total_mbfrom 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_mbfrom dba_free_space group by tablespace_name) b,dba_tablespaces cwhere a.tablespace_name=b.tablespace_name(+) and c.tablespace_name=b.tablespace_nameorder 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_userswhere 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;exitEOFecho "=========oracleinfo end============"exit 0