你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle 导出 ASH的dump信息

[日期:2017-11-20] 来源:Linux社区  作者:hbxztc [字体: ]

最近在看一些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文件中的内容:


从上面的两个图中可以看到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

数据导入成功后就可以根据具体的问题现在做近一步的分析。
其实导出和导入的步骤非常简单,Oracle直接给出了步骤,难的是拿到数据后如何使用这些数据进行近一步的分析,从而找出问题的根本原因。
如下分析每个sampletime的会话数量

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-11/148748.htm

linux
相关资讯       Oracle dump  Oracle 导出 ASH的dump 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款