oracle巡检脚本

合集下载

oracle日常维护巡检工作汇总

oracle 日常维护工作内容(1). 每天对ORACLE数据库的运行状态,日志文件,备份情况,数据库的空间使用情况,系统资源的使用情况进行检查,发现并解决问题。

(2). 每周对数据库对象的空间扩展情况,数据的增长情况进行监控,对数据库做健康检查,对数据库对象的状态做检查。

====================每天工作====================1.(1).确认所有的INSTANCE状态正常.登陆到所有数据库或例程,检测ORACLE后台进程:$ps –-ef|grep ora(2). 检查文件系统的使用（剩余空间）。

如果文件系统的剩余空间小于20%，需删除不用的文件以释放空间。

对于业务交易生产系统和数据库日志归档目录,当空间不足,请急时清理.$df -h(3). 检查日志文件和trace文件记录alert和trace文件中的错误.连接到数据库服务器cd 到bdump目录,通常是$ORACLE_BASE/admin/<SID>/bdump‘tail’命令来查看alert_<SID>.log文件如果发现任何新的ORA- 错误,记录并解决(4). 检查数据文件的状态记录状态不是“online”的数据文件，并做恢复。

Select file_name from dba_data_files where status=’OFFLINE’(5). 检查表空间的使用情况SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_nameFROM dba_free_spaceGROUP BY tablespace_name;SELECT a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes * 100) / a.bytes "% USED ",(c.bytes * 100) / a.bytes "% FREE "FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free cWHERE a.tablespace_name = b.tablespace_nameAND a.tablespace_name = c.tablespace_name;(6). 检查剩余表空间SELECT tablespace_name, SUM (blocks) AS free_blk,TRUNC (SUM (BYTES) / (1024 * 1024)) AS free_m,MAX (BYTES) / (1024) AS big_chunk_k, COUNT (*) AS num_chunks FROM dba_free_spaceGROUP BY tablespace_name;(7). 检查数据库性能，记录数据库的cpu使用、IO、buffer命中率等等使用vmstat,iostat,glance,top等命令====================每周工作====================(1). 监控数据库对象的空间扩展情况根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施alter tablespace 表空间名add datafile '/u1/oradata/userdata_002.ora' size *m;(2). 检查无效的数据库对象SELECT owner, object_name, object_typeFROM dba_objectsWHERE status = 'invalid';(3). 检查不起作用的约束SELECT owner, constraint_name, table_name, constraint_type, statusFROM dba_constraintsWHERE status = 'DISABLED' AND constraint_type = 'P' ;(4). 检查无效的triggerSELECT owner, trigger_name, table_name, statusFROM dba_triggersWHERE status = 'DISABLED';====================每月的工作====================(1). 检查表空间碎片根据本月每周的检查分析数据库碎片情况,找到相应的解决方法(2). 寻找数据库性能调整的机会比较每天对数据库性能的监控报告,确定是否有必要对数据库性能进行调整(3). 数据库性能调整如有必要,进行性能调整(4). 提出下一步空间管理计划根据每周的监控,提出空间管理的改进方法============================================================================|| ★★★Oracle DBA 日常管理★★★||============================================================================目的：这篇文档有很详细的资料记录着对一个甚至更多的ORACLE 数据库每天的，每月的，每年的运行的状态的结果及检查的结果，在文档的附录中你将会看到所有检查，修改的SQL和PL/SQL 代码。

Oracle巡检内容

Oracle巡检内容1. Oracle进程检查Ps –ef |grep ora2. CRS进程检查$ps –df |grep d.bin应有：crsd.bin ocssd.bin evmd.bincrsctl check crscrs_stat –tcrs_stat –lsasm进程检查：ps –ef|grep asmORACLE_SID=+ASM1select group_number,name,state,total_mb,free_mb from v$asm_diskgroup;select * from v$asm_disk;检查数据库状态：srvctl status database –d dbname3. Oracle数据库实例状态检查SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time, status, archiver,database_status FROM gv$instance;4. Oracle数据库打开状态检查SELECT inst_id, dbid, NAME, TO_CHAR (created, 'yyyy-mm-dd hh24:mi:ss') created, log_mode, TO_CHAR (version_time, 'yyyy-mm-dd hh24:mi:ss') version_time,open_modeFROM gv$database;5. 数据库表空间使用情况检查;5.1 表空间的空间使用情况SELECT df.tablespace_name, COUNT (*) datafile_count,ROUND (SUM (df.BYTES) / 1048576) size_mb,ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used, ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_freeFROM dba_data_files df,(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id =free.file_id(+)GROUP BY df.tablespace_name ORDER BY 8;5.2 表空间可用性检查select tablespace_name,status from dba_tablespaces;5.3 临时表空间使用情况和性能检查SELECT tablespace_name, extent_management, block_size, initial_extent,next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE CONTENTS = 'TEMPORARY';SELECT username, default_tablespace, temporary_tablespace FROM dba_users;期望结果：根据现场实际情况，应用用户的缺省表空间不能为system，临时表空间必须为temp。

oracle自动化巡检脚本

#!/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 Data'echo '================================================'echo 'The current database is $ORACLE_SID'echo 'The current running processes for $ORACLE_SID are'echo '================================================'ps -ef|grep $ORACLE_SIDsqlplus -S /nolog <<EOFconnect / 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 onSelect Created, Log_Mode, Log_Mode From V\$Database;set heading offselect '1.ulimit oracle' from dual;select '================================================' from dual; !ulimit -aselect '3.installed production option' from dual;select '================================================' from dual; set linesize 1000set pagesize 1000set heading onselect * from v\$option;set heading offselect 'ed 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 1000select 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 listselect '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_files where 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/1024SIZE_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/1024byte_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.maxblocksFROM sys.dba_temp_files d, v\$temp_extent_pool t, v\$tempfile vWHERE (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 notin('SYS','SYSTEM','MDSYS','ORDSYS','OUTLN','WMSYS') andtablespace_name='SYSTEM' order by 1;exitEOFora_version=`sqlplus -S '/ as sysdba' <<EOFset head offselect version from v\\\$instance;exit;EOF`echo $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 asTOTAL_SIZE,--sum(largest) as "MAXFREE_MB",sum_free_m as "FREE_MB",to_char(100*sum_free_m/sum_m, '999.99') ASFREE_PCT--,sum(blocks) as "FREE_EXTENTS"FROM ( 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,(selecttablespace_name,extent_management from dba_tablespaces) tswhere df.tablespace_name=fs.tablespace_name andfs.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') ASFREE_PCT--,sum(blocks) as "FREE_EXTENTS"FROM ( 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 andfs.tablespace_name=ts.tablespace_name;exit;EOFfisqlplus -S /nolog <<EOFconn / as sysdbaset linesize 1000set pagesize 1000set heading offselect '17.object list' from dual;select '================================================' from dual;col OBJECT_TYPE for a20set heading onselect owner,replace(object_type,' ','_') as OBJECT_TYPE,count(*) from dba_objects whereowner not in ('SYS','SYSTEM') group by owner,object_type order by owner,object_type;set heading offselect '18.invalid objects' from dual;select '================================================' from dual;col OBJECT_NAME for a40col OBJECT_TYPE for a20set heading onselect OWNER,OBJECT_NAME,replace(OBJECT_TYPE,' ','_') asOBJECT_TYPE,STATUS,TIMESTAMP from dba_objects where status='INVALID';set heading offselect '19.dblinks' from dual;select '================================================' from dual;col DB_LINK for a40col OWNER for a10col HOST for a20set heading onselect * from dba_db_links;set heading offselect '20.indexes' from dual;select '================================================' from dual;set heading onselect * From dba_indexes where BLEVEL>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 Ratio"from 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 Ratio"from 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 Sorts"from 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 fromv\$system_event whereevent not like 'SQL*Net%' and event not like '%ipc%' order by total_waits desc) whererownum<11;set head offselect '2-7.memory top 10 sql' from dual;select '================================================' from dual;set head onset serveroutput on size 1000000declaretop10 number;text1 varchar2(4000);x number;len1 number;cursor c1 isselect buffer_gets, substr(sql_text, 1, 4000)from v\$sqlareaorder by buffer_gets desc;begindbms_output.put_line('------------' || ' ' || '-------------------');open c1;for i in 1 .. 10 loopfetch c1 into top10, text1;dbms_output.put_line('------------top sql No.' ||i||'-------------------');dbms_output.put_line(rpad(to_char(top10), 9) );len1 := length(text1);x := 1;while len1 > 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 wheref.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', -- dbsnmp'24ABAB8B06281B4C', -- ctxsys'72979A94BAD2AF80', -- mdsys'C252E8FA117AF049', -- odm'A7A32CD03D3CE8D5', -- odm_mtr'88A2B2C183431F00', -- ordplugins'7EFA02EC7EA6B86F', -- ordsys'4A3BA55E08595C81', -- outln'F894844C34402B67', -- scott'3F9FBD883D787341', -- wk_proxy'79DF7A1BD138CF11', -- wk_sys'7C9BA362F8314299', -- wmsys'88D8364765FCE6AF', -- xdb'F9DA8977092B7B81', -- tracesvr'9300C0977D7DC75E', -- oas_public'A97282CE3D94E29E', -- websys'AC9700FD3F1410EB', -- lbacsys'E7B5D92911C831E1', -- rman'AC98877DE1297365', -- perfstat'66F4EF5650C20355', -- exfsys'84B8CBCA4D477FA3', -- si_informtn_schema'D4C5016086B2DC6A', -- sys'D4DF7931AB130E37') -- system;exitEOFcd $ORACLE_HOME/network/admin/echo '3-2.listener configure'echo '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;EOF`cd $ora_dumpls -lt|head -n 2|tail -n 1|awk '{print $9}'|xargs catsleep 2;echo '3-4.Alert Log ORA- WarningError============================================'ora_background_dump=`sqlplus -S '/ as sysdba' <<EOFset head offselect valuefrom v\\\$parameterwhere name='background_dump_dest';exit;EOF`cd $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 20000nums============================================'tail -20000 alert_$ORACLE_SID.logSYSTEM=`uname -s`export 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 20sleep 5;top -d 1 -n 20sleep 5;top -d 1 -n 20sleep 5;top -d 1 -n 20elif [ $SYSTEM = "SunOS" ] ; thenecho "----------------host name----------------" hostnameecho ""echo "----------------id----------------"idecho "----------------CPU,memory number----------------" /usr/platform/sun4u/sbin/prtdiag -vecho "----------------os lever----------------"cat /etc/releaseecho "----------------Kernel parameter----------------" /usr/sbin/sysdef |grep SHM/usr/sbin/sysdef |grep SEMcat /etc/systemecho "----------------disk info----------------"df -kifconfig -asleep 1;elif [ $SYSTEM = "AIX" ] ; thenecho "----------------host name----------------" hostnameecho ""echo "----------------id----------------"idecho ""echo "----------------machine plat----------------" uname -Mecho ""echo "----------------CPU,memory number----------------" prtconfsleep 2;echo ""echo "----------------disk info----------------"df -kecho ""echo "----------------os lever----------------"oslevel -recho ""echo "----------------kernel parameter----------------" lsattr -El sys0echo ""echo "----------------HACMP----------------"lslpp -l |grep clusterecho ""echo "----------------network parameter----------------" no -aecho ""echo "----------------CPU memory usage----------------" vmstat 5 5sleep 1;echo ""echo "----------------IP info----------------"ifconfig -asleep 1;echo ""echo "----------------view cluster----------------"lssrc -g clustersleep 1;echo ""lsvgsleep 1;echo ""elif [ $SYSTEM = "HP-UX" ] ; thenecho "----------------host name----------------" hostnameecho ""echo "----------------id----------------"idecho ""echo "----------------machine plat----------------" modelecho ""echo "----------------CPU,memory number----------------" machinfosleep 2;echo ""echo "----------------disk info----------------"bdfecho ""echo "----------------os lever----------------"oslevel -recho ""echo "----------------HACMP----------------"lslpp -l |grep clusterecho ""echo "----------------network parameter----------------" no -aecho ""echo "----------------CPU memory usage----------------" vmstat 5 5sleep 1;sar -du 5 5echo ""echo "----------------IP info----------------"ifconfig -aelseecho "What?"fi。

Oracle小型机日常巡检

Oracle小型机日常巡检Oracle小型机日常巡检企业的业务数据库系统是IT运维的重中之重，为使数据库长期稳定的运行，需要相关人员对数据库进行每日巡检和记录，下面对数据库日常巡检工作做一个全面详细的计划：一、小型机日常巡检：1. 检查小型机硬件健康状态1.1 显示内核启用的是32位还是64位# bootinfo -K641.2 显示硬件32位还是64位：# bootinfo -y641.3 显示以KB为单位的实际内存：# bootinfo -r325058561.4 显示系统上的硬盘数量# lspvhdisk0 00c7c505bc0669c5 rootvg activehdisk1 00c7c50592cdd77a rootvg activehdisk2 00cb9934c0a92e73 datavg activehdisk3 00c7c505ce5e6688 datavg active1.5 查看硬盘hdisk1的详细信息：# lspv hdisk1PHYSICAL VOLUME: hdisk1 VOLUME GROUP: rootvgPV IDENTIFIER: 00c7c50592cdd77a VG IDENTIFIER 00c7c50500004c0000000129bc06773fPV STATE: activeSTALE PARTITIONS: 0 ALLOCATABLE: yesPP SIZE: 512 megabyte(s) LOGICAL VOLUMES: 14TOTAL PPs: 558 (285696 megabytes) VG DESCRIPTORS: 2FREE PPs: 224 (114688 megabytes) HOT SPARE: noUSED PPs: 334 (171008 megabytes) MAX REQUEST: 1 megabyteFREE DISTRIBUTION: 01..00..00..111..112USED DISTRIBUTION: 111..112..111..00..00MIRROR POOL: None# smitty fs# smitty lvm1.6 查看处理器数量：# lscfg | grep proc+ proc0 Processor+ proc2 Processor+ proc4 Processor+ proc6 Processor1.7 查看一个CPU的详细信息：# lsattr -El proc0frequency 4204000000 Processor Speed Falsesmt_enabled true Processor SMT enabled Falsesmt_threads 2 Processor SMT threads Falsestate enable Processor state Falsetype PowerPC_POWER6 Processor type False#1.8 查看系统硬件资源列表：#lscfg1.9 查看芯片类型：# uname -ppowerpc1.10 查看操作系统版本号：oslevel1.11 显示系统名称：# uname -sAIX1.12 显示节点名称：# uname -nDL-DB-021.13 显示uname的很多信息（系统名称、节点名称、版本、计算机ID）：# uname -aAIX DL-DB-02 1 6 00C7C5054C001.14 显示系统型号：# uname -MIBM,8204-E8A1.15 显示操作系统版本：# uname -v61.16 显示运行系统的硬件的计算机ID编号：# uname-m00C7C5054C001.17 显示系统ID编号：# uname -uIBM,02067C5051.18 显示AIX的主要版本、次要版本和维护级：# oslevel -r6100-04# lslpp -h bos.rteFileset Level Action Status Date Time----------------------------------------------------------------------------Path: /usr/lib/objreposbos.rte6.1.4.0 COMMIT COMPLETE 07/10/10 19:07:31Path: /etc/objreposbos.rte6.1.4.0 COMMIT COMPLETE 07/10/10 19:07:31#1.19 查看磁盘使用情况（参数k表示以k为单位，m表示以M为单位）：# df -kFilesystem 1024-blocks Free %Used Iused %Iused Mounted on/dev/hd4 5242880 5039512 4% 14271 2% //dev/hd2 11534336 5382688 54% 52471 5% /usr/dev/hd9var 5242880 4544720 14% 7487 1% /var/dev/hd3 10485760 10397956 1% 4002 1% /tmp/dev/fwdump 1048576 1046932 1% 13 1% /var/adm/ras/platform/dev/hd1 5242880 5241708 1% 8 1% /home/dev/hd11admin 524288 523848 1% 5 1% /admin/proc - - - - - /proc/dev/hd10opt 10485760 5696856 46% 10713 1% /opt/dev/livedump 524288 523880 1% 4 1% /var/adm/ras/livedump/dev/oradmpbak 10485760 4488028 58% 28042 3% /orainstbak1/dev/oraclebak 62914560 9605248 85% 33 1% /oradatabak1 /dev/oradata 367001600 321016968 13% 33 1% /oradata/dev/orainst 20971520 14943512 29% 28707 1% /orainst1.20 查看文件大小# du -s tmp166552 tmp2. 检查系统报错信息2.1 显示简短报错信息# errpt | moreTIMESTAMP: MMDDHHMMYY (月日时分年）T（类型）: P 永久; T 临时; U 未知（永久性的错误应引起重视）C（分类）: H 硬件; S 软件; O 用户; U未知2.2 列出所有硬件出错信息：# errpt -d H2.3 列出所有软件出错信息：# errpt -d S2.4 查看具体某个ID的报错信息：# errpt -aj D666A8C7 > aaa.txtD666A8C7是简短报错信息中的ID号。

oracle健康检查(巡检)手册

性能检查
数据库性能
检查数据库的整体性能，包括响应时间、吞吐量和资源利用率等。
查询性能
检查特定查询的性能，包括执行计划、索引和查询优化等方面。
锁和争用
检查数据库中的锁和争用情况，以发现潜在的性能瓶颈和问题。
03 Oracle数据库巡检方法
手动巡检方法
数据库日志检查
检查Oracle数据库的日志文件，包括警告日志、跟踪文件等，以发现潜在的问题和错误。
远程巡检方法
远程监控
01
通过远程监控工具，实时监控Oracle数据库的运行状态和性能
指标。
远程诊断
02
通过远程诊断工具，远程连接到数据库服务器，对数据库进行
故障排除和诊断。
远程备份与恢复
03
通过远程备份与恢复工具，远程备份和恢复Oracle数据库的数
据和日志文件。
04 Oracle数据库巡检结果分析
Oracle Enterprise Manager (OEM)： OEM是一个集成的平台，用于自动监控、诊断和优化Oracle数据库的性能。
Automatic Database Diagnostic Monitor (ADDM)：ADDM是一个自动化的性能诊断工具，可以自动发现和解决性能问题。
在此添加您的文本16字
内存优化
在此添加您的文本16字
调整内存参数：根据数据库的实际需求，合理配置内存参数，如SGA和PGA的大小。
在此添加您的文本16字
内存泄漏检测：定期检查内存使用情况，发现内存泄漏并及时处理。
软件优化建议
调整初始化参数
根据数据库的性能需求，调整初始化参数，如打开表的数量、共享池的大小等。
巡检结果数据收集

数据库巡检

1 日常巡检1.1 数据库巡检为了保证oracle数据库稳定，高效的运行，每个季度初需要对oracle数据库进行健康检查。

以确定数据库是否存在故障及性能问题。

对于异常状况，上报，进一步诊断、分析，及时解决。

巡检工作包括以下细则：●ALERT文件(alertSID.log)是否出现错误信息●top10等待事件●数据库大小●表空间使用情况●内存配置●三个Top10 SQL●内存命中率●归档方式及备份情况1.1.1 巡检脚本1.1.1.1 AlertSID.log文件位置：1.1.1.2 归档方式及备份情况（1）查看是否为归档方式：（2）说明该数据库备份情况，是否有备份策略。

1.1.1.3 top10等待事件：◆不同的版本，事件的多少不同✧Oracle9iOracle10g1.1.1.4 数据库大小：1.1.1.5 表空间使用情况：1.1.2 Top10segment◆查找系统数据量最大的10个段1.1.2.1 内存配置✧oracle9i：✧Oracle10g:1.1.2.2 三个Top10 SQL1.1.2.3 命中率1.1.2.4 死锁死锁查询：SELECT /*+ rule */ername,decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type,s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuserFROM v$session s, v$lock l, dba_objects oWHERE l.sid = s.sidAND l.id1 = o.object_id(+)AND ername is NOT NULL解锁：杀死该session：alter system kill session 'sid,serial#'。

数据库巡检方案

Oracle数据库日常检查A. 查看所有的实例及其后台进程是否正常确认所有的instance工作正常，登陆到所有的数据库或instance上，检测oracle后台进程$env | grep SIDORACLE_SID=UWNMS3B．检查文件系统的使用情况如果文件系统的剩余空间小于10％，则需要删除不必要的文件以释放空间。

$df -hFilesystem size used avail capacity Mounted on/dev/md/dsk/d0 20G 17G 3.1G 85% //proc 0K 0K 0K 0% /procmnttab 0K 0K 0K 0% /etc/mnttabfd 0K 0K 0K 0% /dev/fdswap 85G 192K 85G 1% /var/rundmpfs 85G 0K 85G 0% /dev/vx/dmpdmpfs 85G 0K 85G 0% /dev/vx/rdmpswap 85G 213M 85G 1% /tmp/dev/vx/dsk/data10dg/Ora_File_Vol01394G 292G 98G 75% /data05/dev/vx/dsk/data1dg/vola0131443G 156G 283G 36% /archivelogAIX:$df –g or df –kHP-UX$bdf or df –k or df -h注意：需要特别关注根目录，数据库软件和数据库备份所在目录的剩余空间情况！备注：数据库运行日志的及时清除1). 可清除bdump,cdump,udump下的相关日志$ cd $ORACLE_BASE/admin/db_name/bdump$ ls -ltotal 174-rwxrwxrwx 1 oracle dba 59047 Jul 30 22:02 alert_UWNMS1.log-rwxrwxrwx 1 oracle dba 1000 Jul 14 22:00 uwnms1_j000_18128.trc -rw-r----- 1 oracle dba 1000 Jul 22 22:00 uwnms1_j001_5369.trc -rwxrwxrwx 1 oracle dba 695 Jul 14 19:12 uwnms1_lgwr_18100.trc -rwxrwxrwx 1 oracle dba 2668 Jul 30 22:02 uwnms1_lgwr_19661.trc -rwxrwxrwx 1 oracle dba 983 Jul 14 17:36 uwnms1_lgwr_7816.trc -rwxrwxrwx 1 oracle dba 955 Jul 14 19:11 uwnms1_lgwr_7883.trc -rwxrwxrwx 1 oracle dba 803 Jul 14 17:31 uwnms1_p000_7714.trc -rwxrwxrwx 1 oracle dba 801 Jul 14 17:31 uwnms1_p001_7716.trc $ cd ../cdump$ ls -ltotal 4drwxr-x--- 2 oracle dba 512 Jul 25 14:12 core_18095drwxr-x--- 2 oracle dba 512 Jul 25 19:17 core_25934$ cd ../udump$ ls -ltotal 20042-rw-r----- 1 oracle dba 505 Jul 16 16:33 uwnms1_ora_14771.trc-rw-r----- 1 oracle dba 4516169 Jul 25 14:12 uwnms1_ora_18095.trc-rwxrwxrwx 1 oracle dba 644 Jul 14 19:12 uwnms1_ora_18119.trc -rw-r----- 1 oracle dba 505 Jul 30 15:11 uwnms1_ora_18820.trc-rwxrwxrwx 1 oracle dba 774 Jul 15 10:23 uwnms1_ora_19573.trc -rwxrwxrwx 1 oracle dba 587 Jul 15 10:23 uwnms1_ora_19645.trc -rwxrwxrwx 1 oracle dba 644 Jul 15 10:23 uwnms1_ora_19680.trc -rw-r----- 1 oracle dba 720942 Jul 15 16:28 uwnms1_ora_24759.trc-rw-r----- 1 oracle dba 4951562 Jul 25 19:17 uwnms1_ora_25934.trc-rw-r----- 1 oracle dba 505 Jul 15 17:21 uwnms1_ora_27326.trc-rw-r----- 1 oracle dba 503 Jul 30 16:54 uwnms1_ora_6612.trc-rwxrwxrwx 1 oracle dba 585 Jul 14 17:12 uwnms1_ora_7523.trc-rwxrwxrwx 1 oracle dba 767 Jul 14 17:30 uwnms1_ora_7566.trc 2). 可清除oracle的监听日志$ cd $ORACLE_HOME/network/log$ ls -ltotal 533072-rwxrwxrwx 1 oracle dba 272507851 Jul 31 11:28 listener.log-rw-r--r-- 1 oracle dba 257876 Jul 31 08:48 sqlnet.log$ cp /dev/null listener.logC．查找警告日志文件1. 联接每一个操作管理系统2. 使用‘TELNET’或是可比较程序3. 对每一个管理实例，经常的执行$ORACLE_BASE/<SID>/bdump 操作，并使其能回退到控制数据库的SID。

Oracle数据库教程 —— sqlserver 巡检脚本

Oracle数据库教程—— sqlserver 巡检脚本--1.查看数据库版本信息select @@version--2.查看所有数据库名称及大小exec sp_helpdb--3.查看数据库所在机器的操作系统参数exec master..xp_msver--4.查看数据库启动的参数exec sp_configure--5.查看数据库启动时间select convert(varchar(30),login_time,120)from master..sysprocesses where spid=1--6.查看数据库服务器名select 'Server Name:'+ltrim(@@servername)--7.查看数据库实例名select 'Instance:'+ltrim(@@servicename)--8.数据库的磁盘空间呢使用信息exec sp_spaceused--9.日志文件大小及使用情况dbcc sqlperf(logspace)--10.表的磁盘空间使用信息exec sp_spaceused 'tablename'--11.获取磁盘读写情况select@@total_read [读取磁盘次数],@@total_write [写入磁盘次数],@@total_errors [磁盘写入错误数],getdate() [当前时间]--12.获取I/O工作情况select @@io_busy [自上次启动的I/O操作毫秒数],@@timeticks [每个时钟周期对应的微秒数],@@io_busy*@@timeticks [I/O操作毫秒数],getdate() [当前时间]--13.查看CPU活动及工作情况select@@cpu_busy [自上次启动CPU的工作时间毫秒数],@@timeticks [每个时钟周期对应的微秒数],@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],getdate() [当前时间]--14.检查锁与等待exec sp_lock--15.检测死锁和阻塞declare @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter intcreate table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 print @@ERRORinsert into #tmp_lock_who(spid,bl) select 0 ,blockedfrom (select * from sysprocesses where blocked>0 ) awhere not exists(select * from (select * from sysprocesseswhere blocked>0 ) bwhere a.blocked=spid)union select spid,blocked from sysprocesses where blocked>0IF @@ERROR<>0 print @@ERROR-- 找到临时表的记录数select @intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF @@ERROR<>0 print @@ERRORif @intCountProperties=0select '现在没有阻塞和死锁信息' as message-- 循环开始while @intCounter <= @intCountPropertiesbegin-- 取第一条记录select @spid = spid,@bl = blfrom #tmp_lock_who where Id = @intCounterbeginif @spid =0select '引起数据库死锁的是: '+ CAST(@bl AS V ARCHAR(10))+ '进程号,其执行的SQL语法如下'elseselect '进程号SPID：'+ CAST(@spid AS V ARCHAR(10))+ '被'+ '进程号SPID：'+ CAST(@bl AS V ARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl )end-- 循环指针下移set @intCounter = @intCounter + 1end/--16.用户和进程信息exec sp_whoexec sp_who2--17.活动用户和进程的信息exec sp_who 'active'--19.查看所有数据库用户登录信息exec sp_helplogins--20.查看所有数据库用户所属的角色信息exec sp_helpsrvrolemember--21.查看链接服务器exec sp_helplinkedsrvlogin--22.查看远端数据库用户登录信息exec sp_helpremotelogin--23.获取网络数据包统计信息select@@pack_received [输入数据包数量],@@pack_sent [输出数据包数量],@@packet_errors [错误包数量],getdate() [当前时间]--24.检查数据库中的所有对象的分配和机构完整性是否存在错误dbcc checkdb--25.查询文件组和文件selectdf.[name],df.physical_name,df.[size],df.growth,f.[name][filegroup],f.is_defaultfrom sys.database_files df join sys.filegroups fon df.data_space_id = f.data_space_id--26.查看数据库中所有表的条数select as tablename ,a.rowcnt as datacountfrom sysindexes a ,sysobjects bwhere a.id = b.idand a.indid < 2and objectproperty(b.id, 'IsMSShipped') = 0--27.得到最耗时的前10条T-SQL语句;with maco as(select top 10plan_handle,sum(total_worker_time) as total_worker_time ,sum(execution_count) as execution_count ,count(1) as sql_countfrom sys.dm_exec_query_stats group by plan_handleorder by sum(total_worker_time) desc)select t.text ,a.total_worker_time ,a.execution_count ,a.sql_countfrom maco across apply sys.dm_exec_sql_text(plan_handle) t--28. 查看SQL Server的实际内存占用select * from sysperfinfo where counter_name like '%Memory%'--29.显示所有数据库的日志空间信息dbcc sqlperf(logspace)--30.收缩数据库dbcc shrinkdatabase(databaseName)更多文章可见：公司官网：。

oracle巡检手册

Oracle巡检手册第一部分数据库状态监控首先检查oracle的log，在sqlplus中：show parameter background_dump_dest;select * from v$diag_info;可以得到日志路径1:检查oracle 监听lsnrtcl statusPs –ef|grep ora2:检查oracle初始化参数Select * from v$parameter;3:检查oracle实例状态Select instance_name,version,status,database_status from v$instance;select inst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,'yyyy-mm-dd hh24:mi:ss')startup_time,status,archiver,database_status FROM gv$instance;4:检查后台进程状态：select name,Description From v$BGPROCESS Where Paddr<>'00'5:查看系统全局区SGA信息select * from v$sga;6: 查看SGA各部分占用内存情况：select * from v$sgastat;select request_misses,request_failures from v$shared_pool_reserved;比较好的状态：REQUEST_MISSES REQUEST_FAILURES为0或者接近0REQUEST_MISSES REQUEST_FAILURES-------------- ----------------007：查看系统SCN号select (select dbms_flashback.get_system_change_number from dual)scn,current_scn,scn_to_timestamp(current_scn)from v$database;8：检查数据库状态：select name,log_mode,open_mode,platform_name from v$database;select inst_id,dbid,name,to_char(created,'yyyy-mm-dd hh24:mi:ss')created,log_mode,to_char(version_time,'yyyy-mm-ddhh24:mi:ss')version_time,open_mode from gv$database;第二部分：数据库空间监控检查表空间使用率select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percentfrom (select tablespace_name, sum(bytes) totalfrom dba_free_spacegroup by tablespace_name) A,(select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) Bwhere A.tablespace_name = B.tablespace_name;检查system表空间内的内容select distinct (owner)from dba_tableswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM'unionselect distinct (owner)from dba_indexeswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM';输出：no rows selected分析：如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。

oracle巡检脚本

1) 数据库session连接数select count(*) from v$session;2) 数据库的并发数select count(*) from v$session where status='ACTIVE';3) 是否存在死锁set linesize 200column oracle_username for a16column os_user_name for a12column object_name for a30SELECT l.xidusn,l.object_id,l.oracle_username,l.os_user_name,l.proc ess,l.session_id,s.serial#, l.locked_mode,o.object_name FROM v$locked_object l,dba_objects o,v$session s where l.object_id = o.object_id and s.sid =l.session_id;selectername||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_textfrom v$locked_object t1,v$session t2,v$sqltext t3where t1.session_id=t2.sidand t2.sql_address=t3.addressorder by t2.logon_time;4) 是否有enqueue等待select eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat wheretotal_wait#>0;5) 是否有大量长事务set linesize 200column name for a16column username for a10select,b.xacts,c.sid,c.serial#,ername,d.sql_tex tfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere n=nand n=e.XIDUSNand c.taddr=e.addrand c.sql_address=d.ADDRESSand c.sql_hashvalue=d.hash_valueorder by ,c.sid,d.piece;6)表空间使用率set linesize 150column file_name format a65column tablespace_name format a20select f.tablespace_nametablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,round(f.sumbytes/1024/1024/1024,2) free_g,round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percentfrom (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,(select tablespace_name,sum(bytes) sumbytes fromdba_data_files group by tablespace_name) dwhere f.tablespace_name= d.tablespace_nameorder by d.tablespace_name;临时文件：set linesize 200column file_name format a55column tablespace_name format a20selecta.tablespace_name,a.file_name,round(a.bytes/(1024*1 024*1024),2) total_g,round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g, round((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g, round(((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_gfrom dba_temp_files a,dba_free_space bwhere a.file_id = b.file_id(+)group by a.tablespace_name,a.file_name,a.bytesorder by a.tablespace_name;selecta.tablespace_name,a.file_name,round(a.bytes/(1024*1 024*1024),2) total_g,round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g, round((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g, round(((a.bytes/(1024*1024*1024) -sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_gfrom dba_temp_files a,dba_free_space bwhere a.file_id = b.file_id(+)group by a.tablespace_name,a.file_name,a.bytesorder by a.tablespace_name;归档的生成频率:set linesize 120column begin_time for a26column end_time for a26select a.recid,to_char(a.first_time,'yyyy-mm-ddhh24:mi:ss') begin_time,b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round((b.first_time - a.first_time)*24*60,2) minutes from v$log_history a,v$log_history bwhere b.recid = a.recid+1;sql读磁盘的频率:select ername,b.disk_reads,b.executions,round((b.disk_reads/decode(b.executions,0,1,b.execu tions)),2) disk_read_ratio,b.sql_textfrom dba_users a,v$sqlarea bwhere er_id = b.parsing_user_idand disk_reads > 5000;Datafile I/O:col tbs for a12;col name for a46;select c.tablespace_nametbs,,a.phyblkrd+a.phyblkwrtTotal,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrtfrom v$filestat a,v$datafile b,dba_data_files c where b.file# = a.file#and b.file# = c.file_idorder by tablespace_name,a.file#;Disk I/Oselect substr(,1,13)disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds, 0,1,a.phyblkrd))/100) avg_rd_time,((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_timefrom v$filestat a,v$datafile b,dba_data_files c where b.file# = a.file#and b.file# = c.file_idorder by disk,c.tablespace_name,a.file#;select ername,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_textfrom dba_users a,v$sqlarea bwhere er_id = b.parsing_user_idand b.buffer_gets > 5000000;col index_name for a16;col table_name for a18;col column_name for a18;selectindex_name,table_name,column_name,column_position from user_ind_columnswhere table_name = '&tbs';大事务：select sid,serial#,to_char(start_time,'yyyy-mm-ddhh24:mi:ss')start_time,sofar,totalwork,(sofar/decode(totalwork, 0,1,totalwork))*100 ratio,message fromv$session_longopswhere message like '%RMAN%';select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss')start_time,sofar,totalwork,(sofar/decode(totalwork, 0,1,totalwork))*100 ratio,message fromv$session_longopswhere sofar <> totalwork;where (sofar/totalwork)*100 < 100;索引检查：set linesize 200;column index_name for a15;column index_type for a10;column table_name for a15;column tablespace_name for a16;selectindex_name,index_type,table_name,tablespace_name from user_indexeswhere table_name ='&t';set linesize 200;column index_name for a26;column table_name for a26;column column_name for a22;column column_position for 999;column tablespace_name for a16;selecttable_name,index_name,column_name,column_position from user_ind_columns where table_name = '&tab'; selecttable_name,index_name,column_name,column_position from user_ind_columns where index_name = '&ind'; selecttable_name,index_name,index_type,status,TABLESPACE_ NAME from user_indexes where table_name = '&tab'; selecttable_name,index_name,index_type,status,TABLESPACE_ NAME from user_indexes where index_name = '&ind'; set linesize 200;column index_name for a20;column table_name for a20;select index_name,index_type,table_name,partitioned from user_indexes where index_name = '&ind';等待事件：set linesize 200column username for a12column program for a30column event for a28column p1text for a15column p1 for 999,999,999,999,999select ername,s.program,sw.event,sw.p1text,sw.p1 from v$session s,v$session_wait swwhere s.sid=sw.sid and s.status='ACTIVE'order by sw.p1;select event,p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_waitorder by event;where event = 'buffer busy waits';selectowner,segment_name,segment_type,file_id,block_id from dba_extentswhere file_id = &P1 and &P2 between block_id and block_id + blocks -1;column event for a35;column p1text for a40;select sid,event,p1,p1text from v$session_wait order by event;查询相关SQL：set linesize 200set pagesize 1000column username for a8column program for a36selects.sid,s.serial#,ername,s.program,st.sql_text from v$session s,v$sqltext stwhere s.sql_hashvalue=st.hash_value ands.status='ACTIVE'order by s.sid,st.piece;select pid,spid from v$process p,v$session s where s.sid=&sid and p.addr = s.paddr;selects.sid,s.serial#,ername,s.program,st.sql_text from v$session s,v$sqltext st,v$process ps where s.sql_hashvalue=st.hash_valueand ps.spid=&sid and s.paddr=ps.addrorder by s.sid,st.piece;select sql_text from v$sqltextwhere hash_value in (select sql_hash_value fromv$sessionwhere paddr in (select addr from v$processwhere spid=&sid))order by piece;select sql_text from v$sqltextwhere address in (select sql_address from v$session where paddr in (select addr from v$processwhere spid=&sid))order by piece;select sql_text from v$sqltextwhere hash_value in (select sql_hash_value fromv$session where sid=&sid)order by piece;select sql_text from v$sqltextwhere address in (select sql_address from v$session where sid=&sid)order by piece;selectps.addr,ps.pid,ps.spid,ername,ps.program,s.sid ,ername,s.programfrom v$process ps,v$session swhere ps.spid=&pidand s.paddr=ps.addr;selects.sid,s.serial#,ername,s.program,st.sql_text from v$session s,v$sqltext st,v$process pswhere s.sql_hashvalue=st.hash_valueand ps.spid='29863' and s.paddr=ps.addrorder by s.sid,st.piece;column username for a12column program for a20select ername,s.program,s.osuser,statusfrom v$session swhere s.status='ACTIVE';query undotbs used percent:set linesize 300;selecttablespace_name,segment_name,status,count(*),round( sum(bytes)/1024/1024,2) used_M from dba_undo_extents group by tablespace_name,segment_name,status;set linesize 300column username for a10;column program for a25;selectername,s.program,status,p.spid,st.sql_text from v$session s,v$process p,v$sqltext st wheres.status='ACTIVE' and p.addr=s.paddr andst.hashvalue=s.sql_hash_value order bys.sid,st.piece;selectsnap_id,dbid,instance_number,to_char(snap_time,'yyy y-mm-dd hh24:mi:ss') snap_time from stats$snapshot order by INSTANCE_NUMBER,SNAP_ID,SNAP_TIME;set linesize 120;column what form a30;select job,log_user,what,instance from dba_jobs; set linesize 120;column owner for a12;column segment_name for a24;column segment_type for a18;selectowner,segment_name,segment_type,file_id,block_idfrom dba_extentswhere file_id=&file and &block between block_id and block_id + blocks - 1;select file_id,file_name from dba_data_files where file_id = &file_id;ANALYZE TABLE ICS_ODS_CUST_ICS_CURpartition(ICS_ODS_CUST_ICS_CUR_PART_1)VALIDATE STRUCTURE CASCADE;ANALYZE TABLE ODSDATA.&object VALIDATE STRUCTURE CASCADE INTO INVALID_ROWS;analyze index SYS_C00311764 validate structure cascade;column owner for a12;column segment_name for a26;column segment_type for a16;column tablespace_name for a20;column bytes for 999,999,999,999;selectowner,segment_name,segment_type,tablespace_name,byt es,blocks,buffer_pool from dba_segmentswhere segment_name='&seg'order by bytes desc;selectsegment_name,segment_type,tablespace_name,partition _name,bytes from user_segmentswhere segment_name='ODSV_REC_FILE'and segment_name in (select distinct table_name from user_part_col_statistics wheretable_name='ODSV_REC_FILE')order by bytes desc;col object_name for a26;select object_name,object_type,status,temporary from user_objectswhere object_name = '&o';set linesize 180break on hash_value skip 1 dupcol child_number format 999 heading 'CHILD'col operation format a82col cost format 999999col Kbytes format 999999col object format a25select hash_value,child_number,lpad(' ', 2 * depth) || operation || ' ' || options ||decode(id, 0, substr(optimizer, 1, 6) || ' Cost=' || to_char(cost)) operation,object_name object,cost,cardinality,round(bytes / 1024) kbytesfrom v$sql_planwhere hashvalue=&hash_value/*in(select a.sql_hash_valuefrom v$session a, v$session_wait bwhere a.sid = b.sid and b.event = 'db file scattered read')*/order by hash_value, child_number, id;。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

oracle巡检脚本

oracle日常维护巡检工作汇总

Oracle巡检内容

oracle自动化巡检脚本

Oracle小型机日常巡检

oracle健康检查(巡检)手册

数据库巡检

数据库巡检方案

Oracle数据库教程 —— sqlserver 巡检脚本

oracle巡检 手册

oracle巡检脚本

oracle巡检手册