美创科技技术社区

注册

 

发新话题 回复该主题

expdp trace和10046功能的使用 [复制链接]

1#

介绍一篇metalink的文档,介绍expdp 的trace功能 ID 286496.1

下面这个实验使用trace和10046配合使用
1.建一个了一个用户,和一个aa表
SQL> select * from maxiajun.aa;

ID NAME
———- ——————–
1 maxiajun
2 bb

2.在我们进行expdp导出的时候,Oracle会对其加一个row share锁,假设如果有一个会话在这之前对该表加了exclusive锁,那么expdp就会hang住.之所以要加row share锁,是希望在导这个表的时候,表的结构不会发生改变,或者被删除等等.
SQL>lock table aa in exclusive mode;  –模拟hang住的情形

3.模拟导出
[oracle@DG2 ~]$ expdp maxiajun/oracle directory=dump dumpfile=test.dmp logfile=test.log trace=480300

Export: Release 10.2.0.1.0 – Production on Thursday, 27 September, 2012 15:15:20

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “MAXIAJUN”.”SYS_EXPORT_SCHEMA_02″:  maxiajun/******** directory=dump dumpfile=test.dmp logfile=test.log trace=480300
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported “MAXIAJUN”.”SYS_EXPORT_SCHEMA_01″           117.6 KB    1051 rows

4.观察expdp的trace文件
上面expdp开始hang住
[root@DG2 bdump]#tail -f /oracle/admin/hzmc/bdump/hzmc_dw03_29345.trc
KUPW: 15:15:48.584: 1: in procedure BUILD_OBJECT_STRINGS
KUPW: 15:15:48.584: 1: in procedure MOVE_DATA_USING_DATA_API UNLOADing process_order 2 TABLE_DATA:”MAXIAJUN”.”AA”
KUPW: 15:15:48.586: 1: KUPD$DATA.OPEN called
KUPW: 15:15:48.587: 1: KUPD$DATA.OPEN returned
KUPW: 15:15:48.587: 1: KUPD$DATA.SET_PARAMETER called
KUPW: 15:15:48.587: 1: KUPD$DATA.SET_PARAMETER returned
KUPW: 15:15:48.588: 1: KUPD$DATA.START_JOB called
……

5.用10046时间跟踪
获取expdp 进程的pid
SQL> select program FROM v$session;

PROGRAM
————————————————
[url=]oracle@DG2[/url] (DM02)
[url=]oracle@DG2[/url] (q000)
[url=]ude@DG2[/url] (TNS V1-V3)
[url=]sqlplus@DG2[/url] (TNS V1-V3)
[url=]oracle@DG2[/url] (DW03)
[url=]sqlplus@DG2[/url] (TNS V1-V3)
[url=]oracle@DG2[/url] (DW01)
[url=]oracle@DG2[/url] (q001)
[url=]oracle@DG2[/url] (DM00)
[url=]oracle@DG2[/url] (QMNC)
[url=]oracle@DG2[/url] (ARC1)

SQL> SELECT p.spid,p.addr,p.pid,s.sid,SUBSTR(s.username,1,15) “USERNAME”,
2  SUBSTR(s.program,1,15) “PROGRAM”
3  FROM v$process p,v$session s
4  WHERE s.paddr=p.addr
5  AND addr=(SELECT paddr FROM v$session WHERE UPPER(program) LIKE ‘%DW03%’);

SPID         ADDR            PID        SID USERNAME                       PROGRAM
———— ——– ———- ———- —————————— ——————————
29345        29E1F6C4         27        140 MAXIAJUN                       [url=]oracle@DG2[/url] (DW03)

或者

set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”, s.program, s.sid,
s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;

SQL> oradebug setospid 29345
Oracle pid: 27, Unix process pid: 29345, image: [url=]oracle@DG2[/url] (DW03)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/hzmc/bdump/hzmc_dw03_29345.trc

查看跟踪文件
SQL>tail -f /oracle/admin/hzmc/bdump/hzmc_dw03_29345.trc
Received ORADEBUG command ‘unlimit’ from process Unix process pid: 29153, image:
Received ORADEBUG command ‘tracefile_name’ from process Unix process pid: 29153, image:
*** 2012-09-27 15:24:44.292
Received ORADEBUG command ‘Event 10046 trace name context forever, level 12′ from process Unix process pid: 29153, image:
WAIT #53: nam=’enq: TM – contention’ ela= 2974913 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119808880588
WAIT #53: nam=’enq: TM – contention’ ela= 3019428 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119811900543
WAIT #53: nam=’enq: TM – contention’ ela= 2989870 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119814890580
WAIT #53: nam=’enq: TM – contention’ ela= 2982338 name|mode=1414332418 object #=51703 table/partition=0 obj#=-1 tim=1317119817873007

解决锁
SQL> select s.sid,s.serial#,s.username,s.logon_time from v$session s,v$locked_object l where s.sid=l.session_id;

SID    SERIAL# USERNAME                       LOGON_TIM
———- ———- —————————— ———
138        538 SYS                            27-SEP-12
143        204 MAXIAJUN                       27-SEP-12
140       1242 MAXIAJUN                       27-SEP-12

SQL>  select sid,addr,id1,id2,lmode,request from v$lock where sid in (138,143,140)  order by sid;

SID ADDR            ID1        ID2      LMODE    REQUEST
———- ——– ———- ———- ———- ———-
138 288F3A90      51703          0          6          0
140 29FB9F10       7102          1          3          0
140 288F3BE8      51703          0          0          2
143 288F3B3C      51703          0          0          2
143 29FB9E58       7102          1          3          0

SQL>  select owner,object_naME,object_id,object_type from dba_objects where object_id in (51703,7102);

OWNER                          OBJECT_NAME                     OBJECT_ID OBJECT_TYPE
—————————— —————————— ———- ——————-
SYS                            KU$NOEXP_TAB                         7102 TABLE
MAXIAJUN                       AA                                  51703 TABLE

分享 转发
TOP
发新话题 回复该主题