Oracle 导出 ASH的dump信息

215次阅读

共计 2839 个字符，预计需要花费 8 分钟才能阅读完成。

最近在看一些 Oracle 分享的时候，经常提到导出 ASH 的 dump 给另外的人来做分析，但我没有什么相关的操作，不知道是怎样的一个操作流程，于是上网看了各种博文。于是自己动手做实验做一次导出导入。
实验环境：Oracle 11.2.0.4+rhel 7.2
执行下面的语句对 ASH 信息做 dump 操作
sys@ORA11G>alter system set events ‘immediate trace name ashdump level 10’;

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

使用下面的语句打到对应的 trace 文件
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 文件中的内容：

Oracle 导出 ASH 的 dump 信息

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

1、创建临时表
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0；

2、生成 sqlldr 的 control file
[oracle@rhel7 ash]$ sed -n ‘1,/^Step 2:/d;/^Step 3:/,$d;p’ ora11g_ora_7556.trc |sed ‘/^-/d’ > ashldr.ctl

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

[oracle@rhel7 ash]$ sqlldr linuxidc/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

linuxidc@ORA11G>select count(*) from ashdump;

COUNT(*)
———-
650