Oracle应急使用指南

数据运维 2024-06-01 法医手机阅读

一、概述及目的

为了有效且彻底地处理 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 18col terminal for a15col program for a15col module for a20select a.sid,a.serial#,a.sql_id,a.sql_child_number,a.status,a.TERMINAL,a.PROGRAM,a.modulefrom v$session a ,v$process b where a.paddr=b.addr and b.spid=&os_spid;

3、查看该 SQL 等待事件

set linesize 180col username for a15col event for a35select a.USERNAME,a.status,a.BLOCKING_SESSION_STATUS,a.BLOCKING_INSTANCE,a.BLOCKING_SESSION,a.event,a.SECONDS_IN_WAIT from v$session a where a.SID=&SID anda.SERIAL#=&SERIAL;

4、查看该 SQL 执行计划

set linesize 180set pagesize 150select * from table(dbms_xplan.display_cursor(‘&sql_id’,&sql_child_number));

5、对比该 SQL 历史执行计划

set linesize 180set pagesize 150select * from table(dbms_xplan.display_awr('&sql_id'));

6、指定的SQL语句执行计划

declarev_clob clob;v_sql_id varchar2(13);v_plan_hash_value number;v_fixed varchar2(3);v_enabled varchar2(3);beginv_sql_id := '&sql_id';v_plan_hash_value := to_number('&new_plan_hash_value');v_fixed := '&fixed';v_enabled := '&enabled';select sql_fulltextinto v_clobfrom v$sqlwhere 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 180set pagesize 150select signature,sql_handle,plan_name,origin,enabled,accepted,fixed,autopurgefrom dba_sql_plan_baselineswhere 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, --会话序号 NUMBERC.SERIAL#, --会话序号 NUMBERC.STATUS, --状态 VARCHAR2A.USER_NAME, --数据用户C.PROGRAM, --当前运行程序C.TERMINAL, --终端名称C.CLIENT_INFO AS IPADDRESS, -- IP 地址, 需要建 logon on database 触发器获取 IPB.DISK_READS, --读硬盘次数 NUMBERB.EXECUTIONS, --执行次数 NUMBERB.ROWS_PROCESSED, --通过语法分析的sql返回的数据总行数 NUMBERROUND(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, --sqlB.SQL_FULLTEXT,B.FIRST_LOAD_TIME --创建的时间FROM V$OPEN_CURSOR AJOIN V$SQLAREA B ON A.ADDRESS = B.ADDRESSJOIN V$SESSION C ON A.SID = C.SIDWHERE C.PROGRAM != 'PLSQLDEV.EXE'ORDER BY A.USER_NAME;

9、查看被锁对象信息

set linesize 180col username for a15col lock_level for a15col owner for a10col object_name for a15col object_type for a15col machine for a15col program for a20col osuser for a15col terminal for a10SELECT 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.osuserFROM v$session s,v$lock l,dba_objects oWHERE l.sid = s.sidAND l.id1 = o.object_id(+)AND s.username is NOT NULLand owner='&OWNER';

10、查看会话阻塞信息

set linesize 180col username for a15col event for a35select a.sid,a.serial#,a.USERNAME,a.status,a.BLOCKING_SESSION_STATUS,a.BLOCKING_INSTA NCE,a.BLOCKING_SESSION,a.event,a.SECONDS_IN_WAITfrom v$session awhere 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 - oracleless $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 sysdbasys@ORCL> @?/rdbms/admin/awrrpt.sql

10、收集 ADDM 信息

sqlplus / as sysdbasys@ORCL> @?/rdbms/admin/addmrpt.sql

11、收集 ASH 信息

sqlplus / as sysdbasys@ORCL> @?/rdbms/admin/ashrpt.sql

四、数据库夯机故障诊断

最好记录整个操作过程（匹配时间），其日志信息将会占用大量的物理磁盘（与收集级别和进程数量有关）。

一般处理步骤：1查看 ALERT LOG 中是否有报错2查看相关会话信息3通过 V$SESSION_WAIT 查看等待事件4做 ASH 报告5做 AWR 报告6检查长时间执行的 SQL7通过 HANGANALYZE 分析查看是否有 HANG 住现象8查看操作系统资源

1、收集系统状态

$ date$ sqlplus -prelim / as sysdbaSQL> 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 sysdbaSQL> 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 sysdbaSQL> 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、收集相关日志信

从第三、四章获取的所有日志