oracle自动化巡检脚本.docx

#!/bin/bash# NAME# report_oracle_inspection.sh 2013-12-18# DESCRIPTION# collecting the DB info# NOTES# sh report_oracle_inspection.sh# MODIFIED (yyyy-mm-dd)# zhangheli 2010-08-11 初步改成用shell执行# zhangheli 2011-07-02 增加temp表空间情况查询 echo Instance Health Dataecho =echo The current database is $ORACLE_SIDecho The current running processes for $ORACLE_SID areecho =ps -ef|grep $ORACLE_SIDsqlplus -S /nolog EOF connect / as sysdbaset feedback offset heading offselect 00.instance information from dual;select = from dual;set linesize 1000set pagesize 1000set heading onselect * from v$instance;set heading offselect 01:database created date and archive type from dual;select = from dual;set heading on Select Created, Log_Mode, Log_Mode From V$Database;set heading offselect 1.ulimit oracle from dual;select = from dual;!ulimit -aset heading offselect 3.installed production option from dual;select = from dual;set linesize 1000set pagesize 1000set heading onselect * from v$option;set heading offselect 4.used production option from dual;select = from dual;set linesize 1000set pagesize 1000col COMP_NAME for a40set heading onselect COMP_ID, COMP_NAME, VERSION,STATUS from dba_registry;set heading offselect 5.spfile from dual;select = from dual;show parameter spfileset heading offselect 6.not default parameter from dual;select = from dual;col name for a40col value for a40set heading onselect name,value from v$parameter where isdefault=FALSE;set heading offselect 7.control file from dual;select = from dual;show parameter control_filesset heading offselect 8.backup control file from dual;select = from dual;alter database backup controlfile to trace;set heading offselect 9.log file from dual;select = from dual;set linesize 1000set pagesize 1000set heading onselect group#,thread#,bytes/1024/1024 size_MB , members, archived,status from v$Log;set heading offselect 10.log file from dual;col MEMBER for a40select = from dual;set heading onselect * From v$logfile order by 1;set heading offselect 11.Archive log from dual;select = from dual;Archive log list select 12.data file from dual;select = from dual;set heading onselect count(*),sum(bytes)/1024/1024/1024 |G max_G from v$datafile;SELECT trunc(sum(sum_m-sum_free_m)/1024,2)|G used_GFROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_fileswhere tablespace_name not like UNDO% GROUP BY tablespace_name) df,(SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_free_mFROM dba_free_space GROUP BY tablespace_name ) fswhere df.tablespace_name=fs.tablespace_name;set heading offselect 13.data file location from dual;select = from dual;set heading onselect t1.TABLESPACE_NAME,t1.FILE_ID, t1.bytes/1024/1024 SIZE_MB,t1.AUTOEXTENSIBLE AUT,t2.status,t1.FILE_NAMEfrom dba_data_files t1,v$datafile t2where t1.file_id=t2.file#;set heading offselect 13-1.temp data file from dual;select = from dual;set heading onselect FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 byte_MB,status,AUTOEXTENSIBLE from sys.dba_temp_files;set heading offselect 13-2.temp tablespace from dual;select = from dual;set heading oncol file_name for a30col byte_MB for a20col cached_MB for a20SELECT d.file_name, v.status, TO_CHAR(d.bytes / 1024 / 1024), 99999990.000) byte_MB, TO_CHAR(NVL(t.bytes_cached, 0) / 1024 / 1024, 99999990.000) cached_MB, d.autoextensible, d.increment_by, d.maxblocks FROM sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v WHERE (t.file_id (+)= d.file_id) AND (d.tablespace_name = TEMP) AND (d.file_id = v.file#);set heading offselect 14.system tablespace from dual;select = from dual;set heading onselect owner,segment_type,segment_name from dba_segments where owner not in(SYS,SYSTEM,MDSYS,ORDSYS,OUTLN,WMSYS) and tablespace_name=SYSTEM order by 1;exitEOFora_version=sqlplus -S / as sysdba EOFset head offselect version from v$instance;exit;EOFecho $ora_versionif echo $ora_version|awk -F. print $1 -ne 8 thensqlplus -S /nolog EOFconn / as sysdbaset linesize 1000set pagesize 1000set heading offselect 15.tablespace fragmentation and free from dual;select = from dual;col TABLESPACE_NAME for a30col FREE_PCT for a20set heading onSELECT df.TABLESPACE_NAME,FILES, extent_management ,sum_m as TOTAL_SIZE,-sum(largest) as MAXFREE_MB, sum_free_m as FREE_MB,to_char(100*sum_free_m/sum_m, 999.99) AS FREE_PCT-,sum(blocks) as FREE_EXTENTSFROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df,(SELECT tablespace_name,-max(bytes)/1024/1024 largest,sum(bytes)/1024/1024 AS sum_free_m -,count(blocks) as blocksFROM dba_free_space GROUP BY tablespace_name ) fs,(select tablespace_name,extent_management from dba_tablespaces) tswhere df.tablespace_name=fs.tablespace_name and fs.tablespace_name=ts.tablespace_name; exit;EOFelsesqlplus -S /nolog EOFconn / as sysdbaset linesize 1000set pagesize 1000select 16.tablespace fragmentation and free (8i) from dual;select = from dual;col TABLESPACE_NAME for a30col FREE_PCT for a20set heading onSELECT df.TABLESPACE_NAME,FILES, sum_m as TOTAL_SIZE,-sum(largest) as MAXFREE_MB, sum_free_m as FREE_MB,to_char(100*sum_free_m/sum_m, 999.99) AS FREE_PCT-,sum(blocks) as FREE_EXTENTSFROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df,(SELECT tablespace_name,-max(bytes)/1024/1024 largest,sum(bytes)/1024/1024 AS sum_free_m -,count(blocks) as blocksFROM dba_free_space GROUP BY tablespace_name ) fs,(select tablespace_name from dba_tablespaces) tswhere df.tablespace_name=fs.tablespace_name and fs.tablespace_name=ts.tablespace_name; exit;EOFfisqlplus -S /nolog 4;set heading offselect 21.dba role from dual;select = from dual;set heading onselect grantee,granted_role from dba_role_privs where granted_role=DBA;set heading offselect 22.sysdba role from dual;select = from dual;set heading onSELECT * FROM v$pwfile_users order by username;set head offselect 2-performance from dual;select = from dual;select 2-1.buffer cache hit ratio:(Higher than 80% is ok, high value does not alwasy mean good performance) from dual;select = from dual;set head onselect (1 - (sum(decode(name, physical reads, value, 0) /(sum(decode(name, db block gets, value, 0) +sum(decode(name, consistent gets, value, 0) * 100 Hit Ratio from v$sysstat;set head offselect 2-2.data dictionary hit ratio:should 98% from dual;select = from dual;set head onselect (1 - (sum(getmisses) / sum(gets) * 100 Hit Ratiofrom v$rowcache;set head offselect 2-3.library cache hit ratio:(Should be kept over 90%, otherwise there mighe be too much reparse) from dual;select = from dual;set head onselect sum(pins) / (sum(pins) + sum(reloads) * 100 Hit Ratiofrom v$librarycache;set head offselect 2-4.menory sort ratio:should 98% from dual;select = from dual;set head onselect a.value Disk Sorts,b.value Memory Sorts,round(100 * b.value) /decode(a.value + b.value), 0, 1, (a.value + b.value),2) Pct Memory Sortsfrom v$sysstat a, v$sysstat bwhere = sorts (disk)and = sorts (memory);set head offselect 2-5.memory top 10 sql read ratio:should 5% from dual;select = from dual;set head onselect sum(pct_bufgets)from (select rank() over(order by buffer_gets desc) as rank_bufgets,to_char(100 * ratio_to_report(buffer_gets) over(), 999.99) pct_bufgetsfrom v$sqlarea)where rank_bufgets 11;set heading offselect from dual;select 2-6.Top 10 Wait Event (Time unit:Hundreths of a second, IO operations should be common wait event) from dual;select = from dual;set heading oncolumn event format a30select * from (select event,total_waits,time_waited, average_wait from v$system_event where event not like SQL*Net% and event not like %ipc% order by total_waits desc) where rownum x - 1 loopdbms_output.put_line( | substr(text1, x, 65);x := x + 66;end loop;end loop;end;/set head offselect 2-8.IO information from dual;select = from dual;set head onSelect phyrds,phywrts, from v$datafile d,v$filestat f where f.file#=d.file# order by ;set head offselect 2-9.full table scan from dual;select = from dual;set head onSelect name,value value1 from v$sysstat where name like %table scan%;set head offselect 3-1.sys and system security from dual;select = from dual;select username User(s) with Default Password!,ACCOUNT_STATUSfrom dba_userswhere password in(E066D214D5421CCC, - dbsnmp24ABAB8B06281B4C, - ctxsys72979A94BAD2AF80, - mdsysC252E8FA117AF049, - odmA7A32CD03D3CE8D5, - odm_mtr88A2B2C183431F00, - ordplugins7EFA02EC7EA6B86F, - ordsys4A3BA55E08595C81, - outlnF894844C34402B67, - scott3F9FBD883D787341, - wk_proxy79DF7A1BD138CF11, - wk_sys7C9BA362F8314299, - wmsys88D8364765FCE6AF, - xdbF9DA8977092B7B81, - tracesvr9300C0977D7DC75E, - oas_publicA97282CE3D94E29E, - websysAC9700FD3F1410EB, - lbacsysE7B5D92911C831E1, - rmanAC98877DE1297365, - perfstat66F4EF5650C20355, - exfsys84B8CBCA4D477FA3, - si_informtn_schemaD4C5016086B2DC6A, - sysD4DF7931AB130E37) - system;exitEOFcd $ORACLE_HOME/network/admin/echo 3-2.listener configureecho listener.ora=cat listener*.orasleep 2; echo sqlnet.ora=cat sqlnet*.orasleep 2; echo tnsnames.ora=cat tnsnames*.orasleep 2; echo 3-3.controlfile dump=ora_dump=sqlplus -S / as sysdba EOFset head offselect valuefrom v$parameterwhere name=user_dump_dest;exit;EOFcd $ora_dumpls -lt|head -n 2|tail -n 1|awk print $9|xargs catsleep 2;echo 3-4.Alert Log ORA- Warning Error=ora_background_dump=sqlplus -S / as sysdba EOFset head offselect valuefrom v$parameterwhere name=background_dump_dest;exit;EOFcd $ora_background_dumptail -10000 alert_$ORACLE_SID.log|grep ORA-sleep 2;echo 3-5.Alert Log size=ls -l alert_$ORACLE_SID.logecho 3-6.listener.log size=lsnrctl status|grep listener.log|awk print $4|xargs ls -lecho 3-7.crontab info=crontab -lecho 3-8.Alert Log tail 20000 nums=tail -20000 alert_$ORACLE_SID.logSYSTEM=uname -sexport SYSTEMecho 4.machine information=if $SYSTEM = Linux ; thenecho -host name-hostnameecho echo -id-idecho echo - Current uptime,users and load averages -uptimeecho echo -CPU number-cat /proc/cpuinfosleep 1;echo echo -memory info-cat /proc/meminfosleep 1;echo echo -disk info-df -ksleep 1;echo echo -kernel parameter-cat /etc/sysctl.confsleep 1;echo echo -os lever-lsb_release -asleep 1;echo echo -product type-dmidecode |grep Productsleep 1;echo echo -CPU memory usage-vmstat 5 5sleep 1;echo echo -top info-top -d 1 -n 20sleep 5;top -d 1 -n 20 sleep 5;top -d 1 -n 20 sleep 5;top -d 1 -n 20 sleep 5;top -d 1 -n 20