数据库运维相关脚本
1、采用shell脚本调用SQLPLUS执行SQL语句
#!/bin/bash
TAB=emp
sqlplus -s scott/tiger << EOF | awk 'NR != 1 && $2 ~ /S.*T/{print $2,$6}'
set head off
set feed off
select * from $TAB;
select * from dept;
exit;
EOF
2、批量禁用/启用触发器
SELECT 'alter trigger ' || TRIGGER_NAME || ' enable;' FROM USER_TRIGGERS;
SELECT 'alter trigger ' || TRIGGER_NAME || ' disable;' FROM USER_TRIGGERS;
3、查询索引占碎片比例
select name,del_lf_rows,lf_rows,
round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct
from index_stats
where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30
4、根据 sid 查 spid 或根据 spid 查 sid
select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal
from v$session s,v$process p
where s.paddr=p.addr and s.sid=SS or p.spid=BB
根据 sid 查sql 语句
select username,sql_text,machine,osuser from v$session a,v$sqltext_with_newlinesb
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
and a.sid=&sid order by piece
5、查询会话和进程消耗的平均内存大小
select round(sum(pga_used_mem)/1024/1024,0) total_used_M,
round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,
round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M,
round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M
from v$process
6、高CPU占用查询
select *from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text
from v$sql,dba_users
where user_id=PARSING_USER_ID
order by CPU_TIME/1000000 desc)where rownum <=3
7、一个巡检脚本
mkdir -p /home/oracle/script read -p "Enter Instance Name: " Name logfile=/home/oracle/script/db_log.log echo " ###### 2.1 Section #######" > $logfile export ORACLE_SID=$Name $ORACLE_HOME/bin/sqlplus -S / as sysdba >>$logfile <<END set heading off set feedback off; set pagesize 0; set linesize 120; set trimout on; set trimspool on; set verify off; select 'database_name: '||value from v\$parameter where name='db_name'; select 'instance_name: '||value from gv\$parameter where name='instance_name'; select 'RDBMS Relase: '||version from product_component_version where product like 'Oracle Database%'; select 'All Datafiles Total Size: '||round(sum(total_size)/1024/1024/1024,2)||'GB' from ( select sum(bytes) total_size from dba_data_files union all select sum(bytes) total_size from dba_temp_files ); select 'SGA_MAX_SIZE: '||value from v\$parameter where name='sga_max_size'; select 'SGA_TARGET: '||value from v\$parameter where name='sga_target'; select 'BLOCK_SIZE: '||value from v\$parameter where name='db_block_size'; select 'TABLESPACE TOTAL: '||count(*) from dba_tablespaces; select 'DataFiles: '||count(*) from dba_data_files; select 'Controlfiles: '||count(*) from v\$controlfile; select 'Every RedoLog Size: '||bytes/1024/1024||'MB' from v\$log where rownum=1; select 'Every RedoLog Group has Members: '||members from v\$log where rownum=1; select 'Archive Mode: '||log_mode from v\$database; select 'Max Process(History): '||max_utilization from v\$resource_limit where resource_name='processes'; exit; END echo "######## 5 Section ########" >>$logfile #echo -n "Enter All HostNames In RAC: " #read HostName gridpath=`ps -ef|grep crsd.bin|grep -v grep|awk '{print $8}'` for i in `${gridpath%/*}/olsnodes` do echo $i >>$logfile ssh $i df -h >>$logfile echo "" >>$logfile echo $i >>$logfile ssh $i cat /etc/sysctl.conf|grep -v ^#|grep -v grep|sort|uniq>>$logfile echo $i >>$logfile echo "HugePage Set " >>$logfile ssh $i grep -i hugepage /proc/meminfo >>$logfile echo "#### 3.1 ######" >>$logfile echo $i >>$logfile echo "Local Disk Size" >>$logfile ssh $i df -h|sed '1d'|awk 'sub("G","",$2){total+=$2}END{print total"G"}' >>$logfile echo $i >>$logfile echo "Network Card " >>$logfile ssh $i ethtool eth0|grep Speed >>$logfile echo $i >>$logfile echo "Phyical Memory Total" >>$logfile ssh $i grep MemTotal /proc/meminfo >>$logfile echo "" >>$logfile echo " Physical CPUS" >>$logfile ssh $i cat /proc/cpuinfo |grep "physical id"|uniq|wc -l >>$logfile echo "#### 3.1 over #####" >>$logfile echo $i >>$logfile echo "Vmstat stat " >>$logfile ssh $i vmstat 2 10 >>$logfile echo "" >>$logfile echo $i >>$logfile echo "ifconfig stat" >>$logfile ssh $i /sbin/ifconfig >>$logfile done echo "" >>$logfile echo "######### 6.2 Section #########" >>$logfile echo "Oracle user vairal" >>$logfile cat /home/oracle/.bash_profile >>$logfile echo "" >>$logfile echo "######### 6.3 Section #########" >>$logfile echo "Oracle Patch " >>$logfile $ORACLE_HOME/OPatch/opatch lspatches >>$logfile echo "######### 6.4 Section #########" >>$logfile echo ${gridpath%/*} >>$logfile cd ${gridpath%/*} >>$logfile echo "olsnodes stats" >>$logfile ./olsnodes >>$logfile echo "ocrcheck stats" >>$logfile ./ocrcheck >>$logfile echo "ocrconfig -showbackup stats" >>$logfile ./ocrconfig -showbackup >>$logfile echo "crsctl check crs stats" >>$logfile ./crsctl check crs >>$logfile echo "crsctl stat res -t " >>$logfile ./crsctl stat res -t >>$logfile echo "crsctl stat res -t -init ">>$logfile ./crsctl stat res -t -init >>$logfile echo "crsctl query css votedisk " >>$logfile ./crsctl query css votedisk >>$logfile echo "oifcfg getif ">>$logfile ./oifcfg getif >>$logfile echo "######## 6.7 Section ###########" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 120 set pagesize 0 col parameter for a40 col value for a10 prompt "Oracle install Options" select * from v\$option; exit; EOF echo "######### 6.8 Section #########" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 200 set pagesize 0 col name for a40 col value for a80 select inst_id,name,value from gv\$parameter where isdefault='FALSE'; prompt "######## 6.9 Section #######" select value$ from props\$ where name='NLS_CHARACTERSET'; exit; EOF echo "######### 6.13 Section ########" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 120 col name for a60 select name,status from v\$controlfile; exit; EOF echo "########## 6.14 Section ########" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 120 select group#,thread#,sequence#,bytes/1024/1024,members,archived,status from v\$log; prompt "####### 6.15 #### redo switch frequency" set linesize 200 col Day for a5 col H00 FOR '999' COL H01 FOR '999' COL H02 FOR '999' col H03 FOR '999' COL H04 FOR '999' COL H05 FOR '999' col H06 FOR '999' COL H07 FOR '999' COL H08 FOR '999' COL H09 FOR '999' COL H10 FOR '999' col H11 FOR '999' COL H12 FOR '999' COL H13 FOR '999' col H14 FOR '999' COL H15 FOR '999' COL H16 FOR '999' COL H17 FOR '999' COL H18 FOR '999' col H19 FOR '999' COL H20 FOR '999' COL H21 FOR '999' col H22 FOR '999' COL H23 FOR '999' SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, COUNT(*) TOTAL FROM v\$log_history a where first_time>=to_char(sysdate-11) GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC; exit; EOF echo "########### 6.16 Section #########" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 120 col name for a40 col value for a40 show parameter db_recovery; select inst_id,name,value from gv\$parameter where name like '%archive%' order by 1; exit; EOF echo "########### 6.17 Section ###########" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 150 col tablespace_name for a20 col file_name for a60 set pagesize 0 select tablespace_name,file_name,bytes/1024/1024,status,autoextensible from dba_data_files; exit; EOF echo "########## 6.18 Section ############" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 200 col tablespace_name for a20 set pagesize 0 select tablespace_name,block_size,initial_extent,next_extent,status,contents,force_logging,extent_management,allocation_type,SEGMENT_SPACE_MANAGEMENT,BIGFILE,COMPRESS_FOR from dba_tablespaces; prompt "####### tablespace_used % #######" SELECT total.tablespace_name, Round(total.MB, 2) AS Total_MB, Round(total.MB - free.MB, 2) AS Used_MB, Round(( 1 - free.MB / total.MB ) * 100, 2) || '%' AS Used_Pct FROM (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total WHERE free.tablespace_name = total.tablespace_name; prompt "######6.19 Temporary Tablespace Usage % ######" SET PAGESIZE 60 SET LINESIZE 100 col tablespace_name for a20 SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v\$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v\$tablespace B, v\$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total / prompt "######### 6.20 Section ###########" col segment_name for a30 set linesize 100 set pagesize 0 set heading on select status,sum(bytes)/1024/1024 from dba_undo_extents group by status; exit; EOF echo "######### 7 Section #############" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile prompt "Use default password " col username for a20 set linesize 100 select username from dba_users_with_defpwd where username in ('SYS','SYSTEM'); prompt "granted sysdba role " select username,sysdba from v\$pwfile_users; PROMPT "GRANTED DBA ROLE " col grantee for a20 col granted_role for a20 select grantee,granted_role from dba_role_privs where granted_role='DBA'; prompt "password management " set linesize 200 col profile for a20 col RESOURCE_NAME for a30 col limit for a15 select profile,resource_name,limit from dba_profiles where resource_name='PASSWORD_VERIFY_FUNCTION'; exit; EOF echo "######### 9.6 Section #############" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile set linesize 200 set pagesize 0 col resource_name for a30 col INITIAL_ALLOCATION for a20 col LIMIT_VALUE for a20 select * from v\$resource_limit; exit; EOF echo "######## 10.1 Section ############" >>$logfile sqlplus -S / as sysdba <<EOF >>$logfile col segment_name for a30 set linesize 150 col owner for a20 set pagesize 0 select owner,segment_name,sum(bytes)/1024 from dba_segments where (segment_name like '%TMP%' or segment_name like '%HIS%' or segment_name like '%BAK%' or segment_name like '%TEST%' or segment_name like '%201%' or segment_name like 'BIN%') and owner not in ('SYS','SYSTEM','SYSMAN','APEX_030200','MGMT_VIEW','DBSNMP') and segment_type like 'TABLE%' group by owner,segment_name; prompt "######## 10.2 ###### Redundant index" column redundant_index format a45 column sufficient_index format a45 select o1.name||'.'||n1.name redundant_index, o2.name||'.'||n2.name sufficient_index from sys.icol\$ ic1, sys.icol\$ ic2, sys.ind\$ i1, sys.obj\$ n1, sys.obj\$ n2, sys.user\$ o1, sys.user\$ o2 where ic1.pos# = 1 and ic2.bo# = ic1.bo# and ic2.obj# != ic1.obj# and ic2.pos# = 1 and ic2.intcol# = ic1.intcol# and i1.obj# = ic1.obj# and bitand(i1.property, 1) = 0 and ( select max(pos#) * (max(pos#) + 1) / 2 from sys.icol\$ where obj# = ic1.obj# ) = ( select sum(xc1.pos#) from sys.icol\$ xc1, sys.icol\$ xc2 where xc1.obj# = ic1.obj# and xc2.obj# = ic2.obj# and xc1.pos# = xc2.pos# and xc1.intcol# = xc2.intcol# ) and n1.obj# = ic1.obj# and n2.obj# = ic2.obj# and o1.user# = n1.owner# and o2.user# = n2.owner# and o1.name not in ('SYS','SYSTEM','APEX_030200','SYSMAN','MDSYS','ORDDATA','XDB') / PROMPT "####### 10.3 ####### Invalid object " col object_name for a30 set linesize 120 col owner for a15 col object_type for a30 set pagesize 0 select owner,object_name ,object_type from dba_objects where status='INVALID'; prompt "######### 10.4 ########UNUSABLE INDEX" select owner,index_name from dba_indexes where status='UNUSABLE'; prompt "######### 10.5 ###########Order by sql " set pagesize 0 select sql_id from v\$sqlarea where sql_text like '%order by %'; prompt "######### 10.6 ###########No bound variables are used for SQL" set numw 20 select FORCE_MATCHING_SIGNATURE, count(1) from v\$sql where FORCE_MATCHING_SIGNATURE > 0 and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE group by FORCE_MATCHING_SIGNATURE having count(1) > 100 order by 2; prompt "####### 10.7 ####### In SYSTEM/SYSAUX tablespace " col segment_name for a30 set linesize 200 col owner for a20 select owner,segment_name,segment_type,tablespace_name from dba_segments where tablespace_name in ('SYSTEM','SYSAUX') and owner not in ('SYS','SYSTEM','OUTLN','APEX_030200','SYSMAN','MDSYS','OLAPSYS','ORDDATA','ORDSYS','EXFSYS','XDB','CTXSYS','EXFSYS','WMSYS','DBSNMP'); prompt "######## 10.7 #####Table Fragment" col table_name for a30 select table_name,round(BLOCKS*8192/1024/1024,2) total_size_MB, round(num_rows*AVG_ROW_LEN/1024/1024,2) used_size_MB, round(((BLOCKS*8192/1024/1024)-(num_rows*AVG_ROW_LEN/1024/1024)),2) wasted_size_MB, round(round(((BLOCKS*8192/1024/1024)-(num_rows*AVG_ROW_LEN/1024/1024)),2)/ round(BLOCKS*8192/1024/1024,2),2)*100||'%' wasted_percent from dba_tables where owner not in ('SYS','SYSTEM','OUTLN') and round(BLOCKS*8192/1024/1024,2) <>0 and round(((BLOCKS*8192/1024/1024)-(num_rows*AVG_ROW_LEN/1024/1024)),2)>100 order by 2; PROMPT "##### 10.9 ###### TABLESPACE FRAGMENT " set linesize 150 column tablespace_name format a20 heading 'Tablespace' column sumb format 999,999,999 column extents format 9999 column bytes format 999,999,999,999 column largest format 999,999,999,999 column Tot_Size format 999,999 Heading 'Total| Size(Mb)' column Tot_Free format 999,999,999 heading 'Total Free(MB)' column Pct_Free format 999.99 heading '% Free' column Chunks_Free format 9999 heading 'No Of Ext.' column Max_Free format 999,999,999 heading 'Max Free(Kb)' set echo off PROMPT FREE SPACE AVAILABLE IN TABLESPACES select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free from ( select tablespace_name,0 tots,sum(bytes) sumb, max(bytes) largest,count(*) chunks from dba_free_space a group by tablespace_name union select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by pct_free; prompt "######### 10.10 parallel object #######" set pagesize 0 col owner for a15 col index_name for a30 col table_name for a30 prompt "parallel tables" select owner,table_name,degree from dba_indexes where ltrim(degree)>'1' order by 1; prompt "parallel index" select owner,index_name,degree from dba_indexes where ltrim(degree)>'1' order by 1; exit; EOF