EXP-00091: Exporting questionable statistics.

Posted on 2012 年 01 月 16 日 by 惜分飞

一.出现EXP-00091错误

[oracle@localhost tmp]$ exp "'/ as sysdba'" tables=testxuchao.TAB_XN_PY_DICT \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 16:02:37 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TESTXUCHAO
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

当环境变量和数据库的不一致，在导出统计信息时候，会报EXP-00091错误。

二.查看NLS相关参数

[oracle@localhost tmp]$ echo $NLS_LANG
AMERICAN

SQL>  col value for a30
SQL>  select parameter,value from nls_database_parameters 
   2  where parameter like '%CHARACTERSET%'; 

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

三.解决方法
1.设置NLS_LANG为NLS_CHARACTERSET

[oracle@localhost tmp]export NLS_LANG=american_america.ZHS16GBK
[oracle@localhost tmp]$ exp "'/ as sysdba'" tables=testxuchao.TAB_XN_PY_DICT \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 16:08:10 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to TESTXUCHAO
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
Export terminated successfully without warnings.

2.使用statistics=none

[oracle@localhost tmp]$ exp "'/ as sysdba'" tables=testxuchao.TAB_XN_PY_DICT \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log  statistics=none

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 16:08:53 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TESTXUCHAO
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
Export terminated successfully without warnings.

PLS-00302: component ‘SET_NO_OUTLINES’ must be declared

Posted on 2012 年 01 月 16 日 by 惜分飞

今天接到测试报告，他的客户端不能导出数据库
1.报错如下

Export started on 2012-1-16 15:30:05
D:\oracle\product\10.2.0\client_3\BIN\exp.exe parfile=C:\WINDOWS\Tmp\plsexp.par
EXP-00056: 遇到 ORACLE 错误 6550
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: 导出终止失败
Export finished on 2012-1-16 15:30:07

2.服务器上导出

[oracle@localhost tmp]$ exp "'/ as sysdba'" owner=testxuchao \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 15:36:38 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TESTXUCHAO 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TESTXUCHAO 
About to export TESTXUCHAO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TESTXUCHAO's tables via Conventional Path ...
. . exporting table                    COMPANY_CON          0 rows exported
. . exporting table       SMP_VDD_OPERATIONS_TABLE          0 rows exported
. . exporting table        TAB_XN_NXT_CONSULTATION          0 rows exported
EXP-00091: Exporting questionable statistics.
……………………
EXP-00091: Exporting questionable statistics.
. . exporting table                      TAB_XN_PY          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

服务器上执行exp成功了，我很自然的说了句，是客户端的问题，然后和测试说，可以端重新装下再试试看(因为他装了两个客户端，我是怀疑变量等原因导致)。当他装好后，然后和我说，还是不行，我开始怀疑是版本的问题，然后对比了下今天他的报错，和数据库版本，发现果真是两边版本的问题(数据库服务器版本:9.2.0.4，客户端版本:10.2.0.1)，解决问题，让他装一个低版本的客户端，再装个高版本的客户端，然后在根据导出数据的版本，选择不同版本的exp。

3.失误总结
自己的自以为是让他忙碌了几个小时，深表歉意。人家把你当做专家，你就应该对人家负责任。用认真的心态去帮别人解决问题，不要以为自己就要离开，用种不负责任的态度去处理事情，更不要以为这些东西，自己已经很懂了，不认真对待。

跳过obj$坏块方法

Posted on 2012 年 01 月 16 日 by 惜分飞

1.确定obj$坏块存在

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select /*+full(obj$)*/ count(*) from obj$;
select /*+full(obj$)*/ count(*) from obj$
                                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 95369)
ORA-01110: data file 1: '/opt/oracle/oradata/chf/system01.dbf'

2.使用dbms_repair跳过坏块

SQL> exec dbms_repair.skip_corrupt_blocks('SYS','OBJ$');
BEGIN dbms_repair.skip_corrupt_blocks('SYS','OBJ$'); END;

*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
ORA-06512: at "SYS.DBMS_REPAIR", line 419
ORA-06512: at line 1

--ORA-00701原因
SQL> set pages 100
SQL> SELECT * FROM BOOTSTRAP$ WHERE SQL_TEXT LIKE '%OBJ$%';

     LINE#       OBJ#
---------- ----------
SQL_TEXT
--------------------------------------------------------------------------------
        18      90724
CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT N
ULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(
30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME"
DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" VAR
CHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3
" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 16384 NEXT 106496 MINEX
TENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 90724 EXTENTS (FILE 1 BLOCK 94
505))
--obj$是BOOTSTRAP$中对象，不能被修改
--这里打破一个传奇:一般人都说BOOTSTRAP$中对象都是object_id是非常小靠前。
--但是我这个从10g升级过来的库，obj$的object_id为90724

3.使用event跳过坏块

pfile中添加
event="10231 trace name context forever, level 10"

SQL> startup pfile='/tmp/pfile_new' 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.
SQL> select /*+full(obj$)*/ count(*) from obj$;

  COUNT(*)
----------
     74503

obj$坏块情况下exp导出单个表解决方案

Posted on 2012 年 01 月 15 日 by 惜分飞

在前面一篇(obj$坏块exp不能执行原因探讨)已经研究了在obj$出现坏块的情况下，导致exp导出单个表不能成功的原因，这篇给出解决方案
1.重新创建exu81javt视图

SQL> CREATE OR REPLACE view exu81javt (objid) AS
  2      SELECT  obj#
  3          FROM    sys.obj$
  4          WHERE   name = 'oracle/aurora/rdbms/DbmsJava' AND
  5                  type# = 29 AND
  6                  owner# = 0 AND
  7                  status = 1
  8   /    

View created.

SQL> GRANT SELECT ON sys.exu81javt TO PUBLIC;

Grant succeeded.

SQL>  set autot  trace
SQL> SELECT COUNT(*)      FROM   SYS.EXU81JAVT;


Execution Plan
----------------------------------------------------------
Plan hash value: 2521745379

---------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |     1 |    35 |     4   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |        |     1 |    35 |            |      |

|*  2 |   TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    35 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | I_OBJ2 |     1 |       |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATUS"=1)
   3 - access("OWNER#"=0 AND "NAME"='oracle/aurora/rdbms/DbmsJava' AND
              "TYPE#"=29)
       filter("TYPE#"=29)

通过对这个视图的重新创建，是的原来需要对obj$表全表扫描，改为走I_OBJ2索引，从而避免了部分坏块导致的exp异常。

2.测试exp导出单表

[oracle@node1 tmp]$ exp "'/ as sysdba'" tables=chf.t_undo 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 23:39:12 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                         T_UNDO       1636 rows exported
Export terminated successfully without warnings.

测试证明修改了exu81javt视图后，exp导出单个表成功

3.生成导出脚本

SELECT 'exp "''' || '/ as sysdba''" tables=' || U.NAME || '.' || O.NAME ||
       ' file=' || '&PATH' || U.NAME || '_' || O.NAME || '.dmp log=' ||
       '&PATH' || U.NAME || '_' || O.NAME ||
       '.log buffer=1024000  COMPRESS =N STATISTICS =NONE'
  FROM TAB$ T, OBJ$ O, USER$ U
 WHERE O.TYPE# = 2
   AND T.OBJ# = O.OBJ#
   AND U.USER# = O.OWNER#
   AND u.name IN('CHF');

Execution Plan
----------------------------------------------------------
Plan hash value: 3095026863

-----------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                 |         |    31 |  1829 |    32   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |         |    33 |  1782 |    31   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| USER$   |     1 |    17 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | I_USER1 |     1 |       |     0   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | I_OBJ2  |    33 |  1221 |    30   (0)| 00:00:01 |

|   6 |   TABLE ACCESS CLUSTER        | TAB$    |     1 |     5 |     1   (0)| 00:00:01 |

|*  7 |    INDEX UNIQUE SCAN          | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

4.补充说明
1)并非所有的obj$坏块都可以通过该方法，使得exp导出单个表正常
2)在系统确实无救，有不想使用dul/odu的情况下，可以尝试这种方法抢救数据。

obj$坏块exp不能执行原因探讨

Posted on 2012 年 01 月 15 日 by 惜分飞

上篇(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
1

<strong>三.阅读trace文件</strong>
因为是obj$对象出现坏块，导致exp不能执行，如果是使用了obj$表的index，那么不会每次都报错，而我测试了多次都报错，所以怀疑是对obj$表进行全表扫描导致该错误发生，而使得exp不能继续下去。所以这次查找trace文件，重点是关注obj$表的全表扫描操作，经过耐心查找，终于发现了一个对obj$全表扫描的操作
1
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$有坏块的情况下，不能正常执行