一、概述及目的
为了有效且彻底地处理 Oracle 数据库故障,并在紧急情况下捕捉故障的现场情况以便未来跟踪解决故障,特此编制了此文档以辅助现场 Oracle 工程师收集相关故障信息及排查问题。 这份指导旨在帮助工程师在 Oracle 数据库发生故障时的处理,以避免未彻底解决问题后丢失重要数据,从而给之后的根本解决带来困难。
二、SQL 故障
1、定位系统 CPU 使用高的 oracle 进程号
--Linux:
ps -eo pid,user,cpu,command --sort=-%cpu | grep ora_
--AIX:
ps aux | grep ora_ | sort -nrk 3,3 或者nmon
2、 查询oracle 进程执行内容
set linesize 18
col terminal for a15
col program for a15
col module for a20
select a.sid,a.serial#,a.sql_id,a.sql_child_number,a.status,a.TERMINAL,a.PROGRAM,a.module
from v$session a ,v$process b where a.paddr=b.addr and b.spid=&os_spid;
3、查看该 SQL 等待事件
set linesize 180
col username for a15
col event for a35
select a.USERNAME,a.status,a.BLOCKING_SESSION_STATUS,a.BLOCKING_INSTANCE,a.BLOC
KING_SESSION,a.event,a.SECONDS_IN_WAIT from v$session a where a.SID=&SID and
a.SERIAL#=&SERIAL;
4、查看该 SQL 执行计划
set linesize 180
set pagesize 150
select * from table(dbms_xplan.display_cursor(‘&sql_id’,&sql_child_number));
5、 对比该 SQL 历史执行计划
set linesize 180
set pagesize 150
select * from table(dbms_xplan.display_awr('&sql_id'));
6、指定的SQL语句执行计划
declare
v_clob clob;
v_sql_id varchar2(13);
v_plan_hash_value number;
v_fixed varchar2(3);
v_enabled varchar2(3);
begin
v_sql_id := '&sql_id';
v_plan_hash_value := to_number('&new_plan_hash_value');
v_fixed := '&fixed';
v_enabled := '&enabled';
select sql_fulltext
into v_clob
from v$sql
where sql_id = '&sql_id'
and child_number = 0;
dbms_output.put_line(v_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => v_sql_id,
plan_hash_value => v_plan_hash_value,
sql_text => v_clob,
fixed => v_fixed,
enabled => v_enabled));
end;
/
7、查看 SQL 的base line(基线)信息
set linesize 180
set pagesize 150
select signature,
sql_handle,
plan_name,
origin,
enabled,
accepted,
fixed,
autopurge
from dba_sql_plan_baselines
where sql_text like 'select object_name from test where owner=%';
8、查看高消耗的SQL语句
这个SQL查询是从Oracle数据库中检索关于当前打开的游标、执行的SQL语句和会话信息的详细数据。它主要关联了三个动态性能视图:V$OPEN_CURSOR、V$SQLAREA、和V$SESSION。
SELECT C.SID, --会话序号 NUMBER
C.SERIAL#, --会话序号 NUMBER
C.STATUS, --状态 VARCHAR2
A.USER_NAME, --数据用户
C.PROGRAM, --当前运行程序
C.TERMINAL, --终端名称
C.CLIENT_INFO AS IPADDRESS, -- IP 地址, 需要建 logon on database 触发器获取 IP
B.DISK_READS, --读硬盘次数 NUMBER
B.EXECUTIONS, --执行次数 NUMBER
B.ROWS_PROCESSED, --通过语法分析的sql返回的数据总行数 NUMBER
ROUND(B.CPU_TIME / 1000000, 2) AS CPU_RUNTIME, --CPU 时间,单位微秒
ROUND(B.CPU_TIME / CASE WHEN B.EXECUTIONS = 0 THEN 1 ELSE B.EXECUTIONS END / 1000000, 3) AS EVERY_CPUTIME, --单次执行时间秒
ROUND(B.ROWS_PROCESSED / CASE WHEN B.EXECUTIONS = 0 THEN 1 ELSE B.EXECUTIONS END, 0) AS EVERY_ROWS, --每次返回数据行数
ROUND(B.DISK_READS / CASE WHEN B.EXECUTIONS = 0 THEN 1 ELSE B.EXECUTIONS END, 0) AS EVERY_DISKREADS, --每次读磁盘次数
ROUND((COALESCE(B.BUFFER_GETS, 1) - B.DISK_READS) / COALESCE(B.BUFFER_GETS, 1), 2) AS HIT_RATIO, --命中率
B.SORTS, --排序次数
B.SQL_TEXT, --sql
B.SQL_FULLTEXT,
B.FIRST_LOAD_TIME --创建的时间
FROM V$OPEN_CURSOR A
JOIN V$SQLAREA B ON A.ADDRESS = B.ADDRESS
JOIN V$SESSION C ON A.SID = C.SID
WHERE C.PROGRAM != 'PLSQLDEV.EXE'
ORDER BY A.USER_NAME;
9、查看被锁对象信息
set linesize 180
col username for a15
col lock_level for a15
col owner for a10
col object_name for a15
col object_type for a15
col machine for a15
col program for a20
col osuser for a15
col terminal for a10
SELECT s.username,
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.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
and owner='&OWNER';
10、查看会话阻塞信息
set linesize 180
col username for a15
col event for a35
select a.sid,
a.serial#,
a.USERNAME,
a.status,
a.BLOCKING_SESSION_STATUS,
a.BLOCKING_INSTA NCE,
a.BLOCKING_SESSION,
a.event,
a.SECONDS_IN_WAIT
from v$session a
where a.BLOCKING_SESSION_STATUS = 'VALID';
11、解除SQL锁(上一步10查询出的SID和SERIAL#)
alter system kill session 'SID,SERIAL#';
三、数据库故障查询
1、查看RAC集群服务状态
su - grid 、
$GRID_HOME/bin/crsctl stat res -t
2、查看RAC集群 alert 日志
su - grid
less $GRID_HOME/log/$(hostname | tr [A-Z] [a-z])/alert$(hostname | tr [A-Z] [a-z]).log
3、查看RAC集群 crsd 日志
su - grid
less $GRID_HOME/log/$(hostname | tr [A-Z] [a-z])/crsd/crsd.log
4、查看RAC集群 cssd 日志
su - grid
less $GRID_HOME/log/$(hostname | tr [A-Z] [a-z])/cssd/ocssd.log
5、查看ASM 日志
su – grid
less ${ORACLE_BASE}/diag/rdbms/$(echo ${ORACLE_SID} | tr -d [0-9] | tr [A-Z] [a-z])/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log
6、查看alert 日志
su - oracle
less $ORACLE_BASE/diag/rdbms/$(echo $ORACLE_SID | tr -d '0-9' | tr 'A-Z' 'a-z')/$ORACLE_SID/trace/alert_$ORACLE_SID.log
7、使用Oracle的OSWatcher工具
OSWatcher Black Box(OSWbb)是Oracle提供的一个工具,用于自动收集关于操作系统层面性能的数据,包括CPU、内存、磁盘I/O和网络等指标。它适用于帮助诊断系统性能问题。OSWbb通过定期执行系统性能命令并将输出保存到文件中,使得用户可以分析过去一段时间内的系统性能。
下载地址:
8、手工执行生成快照
exec dbms_workload_repository.create_snapshot();
--5 分钟后再执行
exec dbms_workload_repository.create_snapshot();
9、收集 AWR 信息
sqlplus / as sysdba
sys@ORCL> @?/rdbms/admin/awrrpt.sql
10、收集 ADDM 信息
sqlplus / as sysdba
sys@ORCL> @?/rdbms/admin/addmrpt.sql
11、收集 ASH 信息
sqlplus / as sysdba
sys@ORCL> @?/rdbms/admin/ashrpt.sql
四、数据库夯机故障诊断
最好记录整个操作过程(匹配时间),其日志信息将会占用大量的物理磁盘(与收集级别和进程数量有关)。
一般处理步骤:
1查看 ALERT LOG 中是否有报错
2查看相关会话信息
3通过 V$SESSION_WAIT 查看等待事件
4做 ASH 报告
5做 AWR 报告
6检查长时间执行的 SQL
7通过 HANGANALYZE 分析查看是否有 HANG 住现象
8查看操作系统资源
1、收集系统状态
$ date
$ sqlplus -prelim / as sysdba
SQL> oradebug setmypid;
SQL> oradebug unlimit;
SQL> oradebug -g all dump systemstat 266;
SQL> oradebug -g all dump systemstat 266;
SQL> oradebug tracefile_name;
SQL> oradebug close_trace;
2、收集夯机分析信息
$ date
$ sqlplus -prelim / as sysdba
SQL> oradebug set mypid;
SQL> oradebug unlimit;
SQL> oradebug -g all dump hanganalyze 10;
SQL> oradebug -g all dump hanganalyze 10;
SQL> oradebug tracefile_name;
SQL> oradebug close_trace;
3、收集堆栈错误信息
$ date
$ sqlplus -prelim / as sysdba
SQL> oradebug setmypid;
SQL> oradebug unlimit;
SQL> oradebug -g all errorstatck 3;
SQL> oradebug -g all errorstatck 3;
SQL> oradebug tracefile_name;
SQL> oradebug close_trace;
4、收集相关日志信
从第三、四章获取的所有日志
五、常用事件(10046、10053)信息收集
1、10046事件:
Oracle 10046事件主要用于追踪会话中执行的SQL情况,包括SQL的执行计划和等待事件。可以使用TKPROF,TRCA等工具来格式化TRC文件。
2、10053 事件 Oracle 10053 事件主要用于分析优化器的行为和生成执行计划的依据,这有助于查找执行计划错误的原因,并发现表分析数据中存在的问题。