联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
上篇(obj$坏块exp/expdp导出不能执行)，验证了在obj$有坏块的情况下，不能执行exp/expdp操作，这篇是说明是什么原因导致在obj$有坏块的情况下exp不能正常执行
一.启动数据库级别会话跟踪
[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 15 11:37:07 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create pfile='/tmp/pfile' from spfile; File created. -------------------------------------------------- 在pfile中添加 event='10046 trace name context forever,level 12' -------------------------------------------------- SQL> startup pfile='/tmp/pfile' force ORACLE instance started. Total System Global Area 622149632 bytes Fixed Size 2230912 bytes Variable Size 398460288 bytes Database Buffers 213909504 bytes Redo Buffers 7548928 bytes Database mounted. Database opened.
二.执行单表导出，找到trace文件
[oracle@node1 trace]$ exp "'/ as sysdba'" tables=chf.t1 file=/tmp/xifenfei.dmp \ > log=/tmp/xifenfei.log INDEXES =n COMPRESS =n CONSISTENT =n GRANTS =n \ > STATISTICS =none TRIGGERS =n CONSTRAINTS =n Export: Release 11.2.0.3.0 - Production on Sun Jan 15 11:48:50 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing option Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set Note: grants on tables/views/sequences/roles will not be exported Note: indexes on tables will not be exported Note: constraints on tables will not be exported About to export specified tables via Conventional Path ... Current user changed to CHF . . exporting table T1 --另外会话观察 Tasks: 241 total, 1 running, 240 sleeping, 0 stopped, 0 zombie Cpu(s): 8.9%us, 1.2%sy, 0.0%ni, 85.1%id, 4.8%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8165060k total, 7168288k used, 996772k free, 266028k buffers Swap: 8289500k total, 168k used, 8289332k free, 4653408k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4829 oracle 18 0 69812 12m 9144 S 51.1 0.2 0:03.64 exp tables=chf.t1 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log INDEXES =n COMPRESS 4830 oracle 18 0 829m 62m 58m D 27.9 0.8 0:03.85 oraclechf (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) [oracle@node1 trace]$ ll |grep 4830 -rw-r----- 1 oracle oinstall 14101447 01-15 11:49 chf_ora_4830.trc -rw-r----- 1 oracle oinstall 75398 01-15 11:49 chf_ora_4830.trm <strong>三.阅读trace文件</strong> 因为是obj$对象出现坏块，导致exp不能执行，如果是使用了obj$表的index，那么不会每次都报错，而我测试了多次都报错，所以怀疑是对obj$表进行全表扫描导致该错误发生，而使得exp不能继续下去。所以这次查找trace文件，重点是关注obj$表的全表扫描操作，经过耐心查找，终于发现了一个对obj$全表扫描的操作 PARSING IN CURSOR #46986932266584 len=41 dep=0 uid=0 oct=3 lid=0 tim=1326599330636591 hv=2311813821 ad='7be773c8' sqlid='ftx7dd64wqypx' SELECT COUNT(*) FROM SYS.EXU81JAVT END OF STMT PARSE #46986932266584:c=2999,e=2938,p=5,cr=23,cu=0,mis=1,r=0,dep=0,og=1,plh=23986678,tim=1326599330636590 WAIT #46986932266584: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636682 WAIT #46986932266584: nam='SQL*Net message from client' ela= 42 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636738 EXEC #46986932266584:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=23986678,tim=1326599330636788 WAIT #46986932266584: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636810 WAIT #46986932266584: nam='SQL*Net message from client' ela= 91 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330636913 WAIT #46986932266584: nam='SQL*Net message to client' ela= 9 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668126 FETCH #46986932266584:c=30995,e=31256,p=0,cr=989,cu=0,mis=0,r=1,dep=0,og=1,plh=23986678,tim=1326599330668198 STAT #46986932266584 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=989 pr=0 pw=0 time=31173 us)' STAT #46986932266584 id=2 cnt=1 pid=1 pos=1 obj=90724 op='TABLE ACCESS FULL OBJ$ (cr=989 pr=0 pw=0 time=31156 us cost=220 size=18270 card=522)' WAIT #46986932266584: nam='SQL*Net message from client' ela= 76 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668403 CLOSE #46986932266584:c=0,e=10,dep=0,type=0,tim=1326599330668452 WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668481 WAIT #0: nam='SQL*Net message from client' ela= 113 driver id=1650815232 #bytes=1 p3=0 obj#=91 tim=1326599330668606
四.对EXU81JAVT对象深究
SQL> select object_type from dba_objects where object_name='EXU81JAVT';
OBJECT_TYPE
-------------------
VIEW
SQL> set long 1000
SQL> select TEXT from dba_views where view_name='EXU81JAVT';
TEXT
------------------------------------------------------
SELECT obj#
FROM sys.obj$
WHERE name LIKE '%DbmsJava' AND
type# = 29 AND
owner# = 0 AND
status = 1
SQL> SELECT obj#
2 FROM sys.obj$
3 WHERE name LIKE '%DbmsJava' AND
4 type# = 29 AND
5 owner# = 0 AND
6 status = 1 ;
OBJ#
----------
17671
SQL> select name from obj$ where obj#=17671;
NAME
------------------------------
oracle/aurora/rdbms/DbmsJava
现在稳定已经定位到，是因为exp判断是否使用了java，是去找”/oracle/aurora/rdbms/DbmsJava”.这个对象的，如果java enabled，那么它就会使用dbms_java做一些转换，实际上oracle是查找视图exu81javt来确定DbmsJava的。
这里的EXU81JAVT是查询obj$而是通过name LIKE ‘%DbmsJava’,导致index不能正常使用，从而使得obj$全表扫描，而obj$有坏块，从而使得exp在obj$有坏块的情况下，不能正常执行