Oracle应急使用指南

DBA巫师 2024-04-02

435

点击上方蓝字关注我们

一、概述及目的

为了有效且彻底地处理 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 事件主要用于分析优化器的行为和生成执行计划的依据，这有助于查找执行计划错误的原因，并发现表分析数据中存在的问题。

扫描下方二维码或添加作者微信，回复“加群”即可开启你的数据库和IT学习之旅。加入我们，你将获得不仅仅是知识，多位业内数据库ACE大佬镇场+免费GPT4资源+Oracle MOS免费服务，很多志同道合的小伙伴，欢迎加群一起探讨、一起学习、一起进步！

往期推荐

Oracle的SQL调化健康检查脚本介绍

项目管理实战一：Oracle到达梦的迁移经验分享

如何选择合适的数据库产品与服务

中外数据库的差异究竟在什么地方

摆脱Oracle 错误码困扰，免费公益查询MOS

文章转载自DBA巫师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle应急使用指南

评论