Daily Archives: 2012 年 01 月 16 日

EXP-00091: Exporting questionable statistics.

Posted on 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 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 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