ORACLE问题处理的脚本是什么
这篇文章主要讲解了“ORACLE问题处理的脚本是什么”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“ORACLE问题处理的脚本是什么”吧!
创新互联主要从事做网站、成都做网站、网页设计、企业做网站、公司建网站等业务。立足成都服务海城,10余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:028-86922220
查看操作系统负载
登上数据库服务器后,第一个就是通过系统命令确认下CPU、内存、I/O是否异常,每个系统的命令不一样,常见的有top、topas、vmstat、iostat
查看等待事件
--wait_event col event for a45 SELECT inst_id,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAITWHERE event NOT IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client', 'gcs remote message') AND event NOT LIKE '%idle%' AND event NOT LIKE '%Idle%' AND event NOT LIKE '%Streams AQ%'GROUP BY inst_id,EVENTORDER BY 1,5 desc;
根据等待事件查会话
--session_by_event SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6;
查询某个会话详情
--session_by_sid SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj,s.username, s.machine, module,blocking_session b_sess,logon_time FROM v$session s, v$process p WHERE sid = '&sid' AND s.paddr = p.addr;
查询对象信息
--obj_info col OBJECT_NAME for a30 select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid;
查询SQL语句信息
--sql_text select sql_id,SQL_fullTEXT from v$sqlarea --DBA_HIST_SQLTEXT where (sql_id='&sqlid' or hash_value=to_number('&hashvale') ) and rownum<2;
查询会话阻塞情况
--blocking_sess select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session;
查询数据库的锁
--lockset linesize 180 col username for a15 col owner for a15 col OBJECT_NAME for a30 col SPID for a10 --查询某个会话的锁 select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid; --查询TM、TX锁 select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9; --查询数据库中的锁 select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min, s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3;
保留现场证据
--systemstate dump sqlplus -prelim / as sysdba oradebug setmypid oradebug unlimit; oradebug dump systemstate 266; --wait for 1 min oradebug dump systemstate 266; --wait for 1 min oradebug dump systemstate 266; oradebug tracefile_name; --hanganalyze oradebug setmypid oradebug unlimit; oradebug dump hanganalyze 3 --wait for 1 min oradebug dump hanganalyze 3 --wait for 1 min oradebug dump hanganalyze 3 oradebug tracefile_name
杀会话
--kill_sess set line 199 col event format a35 --杀某个SID会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sid='&sid' AND s.paddr = p.addr order by 1; --根据SQL_ID杀会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE sql_id='&sql_id' AND s.paddr = p.addr order by 1; --根据等待事件杀会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE event='&event' AND s.paddr = p.addr order by 1; --根据用户杀会话 SELECT /*+ rule */ sid, s.serial#, 'kill -9 '||spid, event, blocking_session b_sess FROM v$session s, v$process p WHERE username='&username' AND s.paddr = p.addr order by 1; --kill所有LOCAL=NO进程 ps -ef|grep LOCAL=NO|grep $ORACLE_SID|grep -v grep|awk '{print $2}' | xargs kill -9
感谢各位的阅读,以上就是“ORACLE问题处理的脚本是什么”的内容了,经过本文的学习后,相信大家对ORACLE问题处理的脚本是什么这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是创新互联,小编将为大家推送更多相关知识点的文章,欢迎关注!
分享文章:ORACLE问题处理的脚本是什么
本文链接:http://pwwzsj.com/article/pcgpsp.html