ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ASH的DUMP

ASH的DUMP

原创 Linux操作系统 作者:westzq1984 时间:2009-04-10 13:31:56 0 删除 编辑

v$active_session_history对于诊断系统HANG是有帮助的,但是系统HANG住后是无法查询的,而且,最后的最重要的这部分数据是在内存中的(内存中保存最近的30分钟的数据)
可以使用ASHDUMP来获得这部分内存中的数据

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 10;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/SOURCE/udump/source_ora_3585.trc

相关的文档:Doc ID:  243132.1 Doc ID:  555303.1
metalink上提供的是个sqlldr脚本,可以生成一个外部表的脚本用用

CREATE TABLE external_active_sess_history
(
  "DBID" NUMBER,
  "INSTANCE_NUMBER" NUMBER,
  "SAMPLE_ID" NUMBER,
  "SAMPLE_TIME" VARCHAR(255),
  "SESSION_ID" NUMBER,
  "SESSION_SERIAL#" NUMBER,
  "USER_ID" NUMBER,
  "SQL_ID" VARCHAR2(13),
  "SQL_CHILD_NUMBER" NUMBER,
  "SQL_PLAN_HASH_VALUE" NUMBER,
  "SERVICE_HASH" NUMBER,
  "SESSION_TYPE" VARCHAR2(10),
  "SQL_OPCODE" NUMBER,
  "PLSQL_ENTRY_OBJECT_ID" NUMBER,
  "PLSQL_ENTRY_SUBPROGRAM_ID" NUMBER,
  "PLSQL_OBJECT_ID" NUMBER,
  "PLSQL_SUBPROGRAM_ID" NUMBER,
  "BLOCKING_SESSION" NUMBER,
  "BLOCKING_SESSION_SERIAL#" NUMBER,
  "QC_SESSION_ID" NUMBER,
  "QC_INSTANCE_ID" NUMBER,
  "XID" RAW(8),
  "CURRENT_OBJ#" NUMBER,
  "CURRENT_FILE#" NUMBER,
  "CURRENT_BLOCK#" NUMBER,
  "EVENT_ID" NUMBER,
  "SEQ#" NUMBER,
  "P1" NUMBER,
  "P2" NUMBER,
  "P3" NUMBER,
  "WAIT_TIME" NUMBER,
  "TIME_WAITED" NUMBER,
  "FORCE_MATCHING_SIGNATURE" NUMBER,
  "PROGRAM" VARCHAR2(64),
  "MODULE" VARCHAR2(48),
  "ACTION" VARCHAR2(32),
  "CLIENT_ID" VARCHAR2(64)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DUMP_DIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    READSIZE 1048576   nobadfile nologfile nodiscardfile
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
 "DBID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "INSTANCE_NUMBER" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SAMPLE_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SAMPLE_TIME" CHAR(255)  TERMINATED BY "," ENCLOSED BY '"' AND '"'  DATE_FORMAT TIMESTAMP MASK "MM-DD-YYYYHH24:MI:SSXFF",
 "SESSION_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SESSION_SERIAL#" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "USER_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SQL_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SQL_CHILD_NUMBER" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SQL_PLAN_HASH_VALUE" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SERVICE_HASH" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SESSION_TYPE" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SQL_OPCODE" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "PLSQL_ENTRY_OBJECT_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "PLSQL_ENTRY_SUBPROGRAM_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "PLSQL_OBJECT_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "PLSQL_SUBPROGRAM_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "BLOCKING_SESSION" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "BLOCKING_SESSION_SERIAL#" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "QC_SESSION_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "QC_INSTANCE_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "XID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "CURRENT_OBJ#" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "CURRENT_FILE#" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "CURRENT_BLOCK#" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "EVENT_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "SEQ#" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "P1" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "P2" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "P3" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "WAIT_TIME" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "TIME_WAITED" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "FORCE_MATCHING_SIGNATURE" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "PROGRAM" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "MODULE" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "ACTION" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 "CLIENT_ID" CHAR(255)  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location ('source_ora_3585.trc')
)REJECT LIMIT UNLIMITED
/

DUMP出来的是没有event_name的,需要用event_id去v$event_name中找名称,主要event_id和name的关系可能应版本的不同的不同,主要使用正确的v$event_name

如果使用sqlload装入,控制文件为?/rdbms/demo/ashldr.ctl

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-586993/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    960541