#!/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 echo "=========oracleinfo end============" exit 0