Oracle 导出 ASH的dump信息

最近在看一些Oracle分享的时候，经常提到导出ASH的dump给另外的人来做分析，但我没有什么相关的操作，不知道是怎样的一个操作流程，于是上网看了各种博文。于是自己动手做实验做一次导出导入。

实验环境：Oracle 11.2.0.4+rhel 7.2

执行下面的语句对ASH信息做dump操作

sys@ORA11G>altersystemsetevents'immediatetracenameashdumplevel10';

上面的语句中level的意思是dump出ASH buffer中最近n分钟的数据，这里以10分钟为例。

使用下面的语句打到对应的trace文件

sys@ORA11G>SELECTvalueFROMv$diag_infoWHEREname='DefaultTraceFile';

VALUE
-------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_7556.trc

先简单查看一下trace文件中的内容：

从上面的两个图中可以看到trace文件中包含了三类信息：trace的基本信息、导入dump的方法和ash的实际数据，其实只要按照trace中的文件给出的方法就可以很容易的把dump数据导入到数据库中。

1、创建临时表

CREATETABLEashdumpAS
SELECT*FROMSYS.WRH$_ACTIVE_SESSION_HISTORYWHERErownum<0；

2、生成sqlldr的control file

[oracle@rhel7ash]$sed-n'1,/^Step2:/d;/^Step3:/,$d;p'ora11g_ora_7556.trc|sed'/^-/d'>ashldr.ctl

3、使用sqlldr工具把trace文件中的数据导入到临时表中

[oracle@rhel7ash]$sqlldrzx/zxcontrol=ashldr.ctldata=ora11g_ora_7556.trcerrors=1000000

SQL*Loader:Release11.2.0.4.0-ProductiononSatSep2317:16:402017

Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.

Commitpointreached-logicalrecordcount19
Commitpointreached-logicalrecordcount37
Commitpointreached-logicalrecordcount55
Commitpointreached-logicalrecordcount73
Commitpointreached-logicalrecordcount91
Commitpointreached-logicalrecordcount109
Commitpointreached-logicalrecordcount127
Commitpointreached-logicalrecordcount145
Commitpointreached-logicalrecordcount163
Commitpointreached-logicalrecordcount181
Commitpointreached-logicalrecordcount199
Commitpointreached-logicalrecordcount217
Commitpointreached-logicalrecordcount235
Commitpointreached-logicalrecordcount253
Commitpointreached-logicalrecordcount271
Commitpointreached-logicalrecordcount289
Commitpointreached-logicalrecordcount307
Commitpointreached-logicalrecordcount325
Commitpointreached-logicalrecordcount343
Commitpointreached-logicalrecordcount361
Commitpointreached-logicalrecordcount379
Commitpointreached-logicalrecordcount397
Commitpointreached-logicalrecordcount415
Commitpointreached-logicalrecordcount433
Commitpointreached-logicalrecordcount451
Commitpointreached-logicalrecordcount469
Commitpointreached-logicalrecordcount487
Commitpointreached-logicalrecordcount505
Commitpointreached-logicalrecordcount523
Commitpointreached-logicalrecordcount541
Commitpointreached-logicalrecordcount559
Commitpointreached-logicalrecordcount577
Commitpointreached-logicalrecordcount595
Commitpointreached-logicalrecordcount613
Commitpointreached-logicalrecordcount631
Commitpointreached-logicalrecordcount649
Commitpointreached-logicalrecordcount651


zx@ORA11G>selectcount(*)fromashdump;

COUNT(*)
----------
650

数据导入成功后就可以根据具体的问题现在做近一步的分析。

其实导出和导入的步骤非常简单，Oracle直接给出了步骤，难的是拿到数据后如何使用这些数据进行近一步的分析，从而找出问题的根本原因。

如下分析每个sampletime的会话数量

参考：http://www.eygle.com/archives/2009/08/howto_dump_ashinfo.html

https://antognini.ch/2017/07/offline-analysis-of-ash-data-with-ashdump/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!