最近在看一些Oracle分享的时候，经常提到导出ASH的dump给另外的人来做分析，但我没有什么相关的操作，不知道是怎样的一个操作流程，于是上网看了各种博文。于是自己动手做实验做一次导出导入。
实验环境：Oracle 11.2.0.4+rhel 7.2
执行下面的语句对ASH信息做dump操作
|
1
|
sys@ORA11G>alter system
set
events
'immediate trace name ashdump level 10'
;
|
上面的语句中level的意思是dump出ASH buffer中最近n分钟的数据，这里以10分钟为例。
使用下面的语句打到对应的trace文件
|
1
2
3
4
5
|
sys@ORA11G>
SELECT
value
FROM
v$diag_info
WHERE
name
=
'Default Trace File'
;
VALUE
-------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_7556.trc
|
先简单查看一下trace文件中的内容：
从上面的两个图中可以看到trace文件中包含了三类信息：trace的基本信息、导入dump的方法和ash的实际数据，其实只要按照trace中的文件给出的方法就可以很容易的把dump数据导入到数据库中。
1、创建临时表
|
1
2
|
CREATE
TABLE
ashdump
AS
SELECT
*
FROM
SYS.WRH$_ACTIVE_SESSION_HISTORY
WHERE
rownum < 0；
|
2、生成sqlldr的control file
|
1
|
[oracle@rhel7 ash]$
sed
-n
'1,/^Step 2:/d;/^Step 3:/,$d;p'
ora11g_ora_7556.trc |
sed
'/^-/d'
> ashldr.ctl
|
3、使用sqlldr工具把trace文件中的数据导入到临时表中
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
[oracle@rhel7 ash]$ sqlldr zx
/zx
control=ashldr.ctl data=ora11g_ora_7556.trc errors=1000000
SQL*Loader: Release 11.2.0.4.0 - Production on Sat Sep 23 17:16:40 2017
Copyright (c) 1982, 2011, Oracle and
/or
its affiliates. All rights reserved.
Commit point reached - logical record count 19
Commit point reached - logical record count 37
Commit point reached - logical record count 55
Commit point reached - logical record count 73
Commit point reached - logical record count 91
Commit point reached - logical record count 109
Commit point reached - logical record count 127
Commit point reached - logical record count 145
Commit point reached - logical record count 163
Commit point reached - logical record count 181
Commit point reached - logical record count 199
Commit point reached - logical record count 217
Commit point reached - logical record count 235
Commit point reached - logical record count 253
Commit point reached - logical record count 271
Commit point reached - logical record count 289
Commit point reached - logical record count 307
Commit point reached - logical record count 325
Commit point reached - logical record count 343
Commit point reached - logical record count 361
Commit point reached - logical record count 379
Commit point reached - logical record count 397
Commit point reached - logical record count 415
Commit point reached - logical record count 433
Commit point reached - logical record count 451
Commit point reached - logical record count 469
Commit point reached - logical record count 487
Commit point reached - logical record count 505
Commit point reached - logical record count 523
Commit point reached - logical record count 541
Commit point reached - logical record count 559
Commit point reached - logical record count 577
Commit point reached - logical record count 595
Commit point reached - logical record count 613
Commit point reached - logical record count 631
Commit point reached - logical record count 649
Commit point reached - logical record count 651
zx@ORA11G>
select
count(*) from ashdump;
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/
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1968021，如需转载请自行联系原作者
