Oracle应急使用指南

2024年 6月 1日 79.2k 0

一、概述及目的

    为了有效且彻底地处理 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 - 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 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、收集相关日志信

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

Oracle应急使用指南-1Oracle应急使用指南-2
Oracle应急使用指南-3

五、常用事件(10046、10053)信息收集

Oracle应急使用指南-4
Oracle应急使用指南-5
Oracle应急使用指南-6

1、10046事件:

Oracle 10046事件主要用于追踪会话中执行的SQL情况,包括SQL的执行计划和等待事件。可以使用TKPROF,TRCA等工具来格式化TRC文件。

Oracle应急使用指南-7

2、10053 事件 Oracle 10053 事件主要用于分析优化器的行为和生成执行计划的依据,这有助于查找执行计划错误的原因,并发现表分析数据中存在的问题。

Oracle应急使用指南-8

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论