[分享]对Oracle EXP/IMP 工具进行Trace的方法_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2771 | 回复: 0

主题： [分享]对Oracle EXP/IMP 工具进行Trace的方法

lei.wang

注册用户

等级：上尉
经验：767
发帖：21
精华：0
注册：1970-1-1
状态：离线
发送短消息息给lei.wang

加好友发送短消息息给lei.wang

发消息

发表于：

2015-3-24 18:03:56 | [全部帖] [楼主帖]

楼主

前言

在对Oracle数据库进行逻辑导入导出时，经常会使用到exp/imp逻辑导入导出工具；

本文演示当oracle exp/imp 出现问题时，如何进行相关的trace debug操作

步骤1

打开命令窗口（Linux shell & Windows cmd），运行exp 用户名/密码

$ exp system/manager

步骤2

另开一个窗口进行运行下面命令获得相关进程号：

[10:46:12oracle@dvd db_1]$sqlplus system/oracle
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 29 10:46:42 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the OLAP and Data Mining options
system@SUN> col program for a30
system@SUN> select sid,program from v$session where username = 'SYSTEM';
SID PROGRAM
---------- ------------------------------
95 sqlplus@dvd (TNS V1-V3)
112 exp@dvd (TNS V1-V3)
system@SUN> select s.sid, p.pid, p.spid from v$session s, v$process p where s.paddr = p.addr and s.sid =112;
SID        PID SPID
---------- ---------- ------------------------------------------------
112         19 12277

步骤3

以sysdba身份登陆数据库对指定进程进行trace debug

9i之前版本如下操作

$ svrmgrl <enter>
svrmgr> connect internal
svrmgr> oradebug setospid 12277
svrmgr> oradebug Event 10046 trace name context forever, level 12;

9i及更高版本如下操作

[10:56:22oracle@dvd db_1]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 29 10:56:32 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the OLAP and Data Mining options
sys@SUN> oradebug setospid 12277
Oracle pid: 19, Unix process pid: 12277, image: oracle@dvd (TNS V1-V3)
sys@SUN> oradebug event 10046 trace name context forever ,level 12;
Statement processed.

步骤4

切换回exp窗口继续进行exp后续操作

步骤5

关闭所开启的trace，并提交分析所生成的trace文件

sys@SUN> oradebug event 10046 trace name context off;
ORA-00072: process "Unix process pid: 12791, image: oracle@dvd (W000)" is not active
在user_dump_file_dest参数所指定的目录下查找所生成的trace文件，注意文件以 <ORACLE_SID>_ora_<OSPID>.trc格式命名。

trace文件实例：

[11:06:20oracle@dvd trace]$pwd
/home/oracle/diag/rdbms/sun/sun/trace
[11:06:21oracle@dvd trace]$
[11:06:22oracle@dvd trace]$head -50 sun_ora_12277.trc
*** SERVICE NAME:(SYS$USERS) 2012-10-29 10:57:26.973
*** MODULE NAME:(exp@dvd (TNS V1-V3)) 2012-10-29 10:57:26.973
*** ACTION NAME:() 2012-10-29 10:57:26.973
Received ORADEBUG command (#1) 'event 10046 trace name context forever ,level 12' from process 'Unix process pid: 12398, image: <none>'
*** 2012-10-29 10:57:27.034
Finished processing ORADEBUG command (#1) 'event 10046 trace name context forever ,level 12'
*** 2012-10-29 11:01:05.098
WAIT #0: nam='SQL*Net message from client' ela= 873598029 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1351479665098707
CLOSE #2:c=0,e=39,dep=0,type=0,tim=1351479665098960
WAIT #5: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1351479665099039
*** 2012-10-29 11:01:12.759
WAIT #5: nam='SQL*Net message from client' ela= 7660611 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1351479672759694
WAIT #2: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1351479672759894
WAIT #2: nam='SQL*Net message from client' ela= 50 driver id=1650815232 #bytes=1 p3=0 obj#=383 tim=1351479672759988
=====================
PARSING IN CURSOR #6 len=37 dep=1 uid=0 oct=3 lid=0 tim=1351479672762142 hv=1398610540 ad='589d4b34' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #6:c=1000,e=1229,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1351479672762140
。。。。。。。。。。