下面是一个朋友的数据库，通过exp全库导出的时候报下面的错误
1，版本与平台介绍
|
d:\wendang\SkyDrive\rs2\sql>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期一 8月 18 15:19:36 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> host uname -a windows32 luoping 2.6.1 7601 i686-pc Intel unknown MinGW
SQL> select * from v$version where rownum<3;
BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production PL/SQL Release 11.2.0.3.0 – Production |
2，错误信息
|
d:\wendang\SkyDrive\rs2\sql>exp system/oracle full=y file=e:\full.dmp indexes=n constraints=n
Export: Release 11.2.0.3.0 – Production on 星期一 8月 18 12:54:19 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 注: 将不导出表的索引 注: 将不导出表的约束条件
即将导出整个数据库… . 正在导出表空间定义 . 正在导出概要文件 . 正在导出用户定义 . 正在导出角色 . 正在导出资源成本 . 正在导出回退段定义 . 正在导出数据库链接 . 正在导出序号 . 正在导出目录别名 . 正在导出上下文名称空间 . 正在导出外部函数库名 . 导出 PUBLIC 类型同义词 . 正在导出专用类型同义词 . 正在导出对象类型定义 . 正在导出系统过程对象和操作 . 正在导出 pre-schema 过程对象和操作 . 正在导出簇定义 EXP-00056: 遇到 ORACLE 错误 4063 ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" 有错误 EXP-00056: 遇到 ORACLE 错误 4063 ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" 有错误 EXP-00000: 导出终止失败 |
这里看到遇到视图不存在。
3，处理过程
3.1 10046trace过程编译过程
|
SQL> oradebug setmypid 已处理的语句 SQL> oradebug event 10046 trace name context forever,level 12; 已处理的语句 SQL> oradebug tracefile_name; D:\APP\LUOPING\diag\rdbms\orcl\orcl\trace\orcl_ora_5824.trc SQL> alter VIEW SYS.KU$_XMLSCHEMA_VIEW compile;
警告: 更改的视图带有编译错误。 |
3.2 查看无效对象
|
MEDADM RC PROCEDURE INVALID Invalid Obj PUBLIC APEX_ADMIN SYNONYM INVALID Invalid Obj PUBLIC EQUALS_PATH SYNONYM INVALID Invalid Obj PUBLIC F SYNONYM INVALID Invalid Obj
OBJECT_OWNER OBJECT_NAME TYPE STATUS PROB ————— —————————————- ——————- ——- ———– PUBLIC HTMLDB_ADMIN SYNONYM INVALID Invalid Obj PUBLIC PATH_VIEW SYNONYM INVALID Invalid Obj PUBLIC RESOURCE_VIEW SYNONYM INVALID Invalid Obj PUBLIC UNDER_PATH SYNONYM INVALID Invalid Obj XDB XDBHI_IDXTYP INDEXTYPE INVALID Invalid Obj XDB EQUALS_PATH OPERATOR INVALID Invalid Obj XDB UNDER_PATH OPERATOR INVALID Invalid Obj XDB DBMS_RESCONFIG PACKAGE BODY INVALID Invalid Obj XDB XDB_PV_TRIG TRIGGER INVALID Invalid Obj XDB XDB_RV_TRIG TRIGGER INVALID Invalid Obj XDB PATH_VIEW VIEW INVALID Invalid Obj XDB RESOURCE_VIEW VIEW INVALID Invalid Obj XDB DBMS_XMLDOM_ICD PACKAGE VALID Miss Pkg Body XDB XDB_PVTRIG_PKG PACKAGE VALID Miss Pkg Body |
这里可以看到xdb有很多无效的对象，估计是xdb的问题。
3.3手动重新运行xdb一些视图的创建
|
Rem NAME Rem catmetx.sql – Metadata API: Real definitions for XDB object views. SQL> @?/rdbms/admin/catmetx.sql
SQL> alter package xdb.xdb_funcimpl compile;
程序包已变更。
SQL> alter index xdb.xdbhi_idx rebuild; alter index xdb.xdbhi_idx rebuild * 第 1 行出现错误: ORA-01418: 指定的索引不存在
SQL> SQL> — view for xmlschemas SQL> — this view is used for direct use of MDAPI; not for datapump SQL> create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t 2 with object identifier (schemaoid) as 3 select ‘1’,’0′, 4 u.user#, u.name, x.schema_url, x.schema_id, 5 (case when x.local=’YES’ then 1 else 0 end 6 + case when x.binary=’YES’ then 2 else 0 end), 7 xlvl.lvl, 8 value(s).getClobVal(), 9 xdb.dbms_xdbutil_int.XMLSchemaStripUsername(XMLTYPE( 10 value(s).getClobVal()), 11 u.name) — stripped 12 from sys.user$ u, sys.dba_xml_schemas x, xdb.xdb$schema s, 13 sys.dba_xmlschema_level_view xlvl 14 where x.owner=u.name and xlvl.schema_oid = x.schema_id and 15 s.sys_nc_oid$ = x.schema_id and 16 (SYS_CONTEXT(‘USERENV’,’CURRENT_USERID’) IN (u.user#, 0) OR 17 EXISTS ( SELECT * FROM session_roles 18 WHERE role=’SELECT_CATALOG_ROLE’ )) 19 /
警告: 创建的视图带有编译错误。
SQL> grant select on sys.ku$_xmlschema_view to public 2 / grant select on sys.ku$_xmlschema_view to public * 第 1 行出现错误: ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" 有错误
SQL> — ku$_table_xmlschema_view is used to find the xmlschemas directly referenced SQL> — for xmltype columns/tables and dependent schemas referenced. SQL> — elclude hidden xmlschemas (32768 set in xdb$schema flags) SQL> create or replace view ku$_table_xmlschema_view as 2 select opq.obj# tabobj_num, opq.schemaoid schemaoid, opq.schemaoid par_oid 3 from sys.opqtype$ opq 4 UNION 5 select opq.obj# tabobj_num, sd.dep_schema_oid schemaoid, opq.schemaoid par_oid 6 from sys.opqtype$ opq, dba_xml_schema_dependency sd 7 start with 8 sd.schema_oid=opq.schemaoid and opq.type=1 and opq.schemaoid is not null 9 connect by nocycle 10 prior sd.dep_schema_oid=sd.schema_oid and 11 prior opq.schemaoid=opq.schemaoid and opq.type=1 12 /
视图已创建。
SQL> grant select on ku$_table_xmlschema_view to select_catalog_role 2 /
授权成功。 |
刚运行就开始报了一些错误。不过这里报错都很简单，就是一些索引，视图不存在。
3.4 手动创建视图与索引
|
SQL> CREATE INDEX XDB.XDBHI_IDX ON XDB.XDB$RESOURCE 2 (SYS_MAKEXML(‘8758D485E6004793E034080020B242C6’,734,"XMLEXTRA","XMLDATA")) 3 INDEXTYPE IS XDB.XDBHI_IDXTYP 4 NOPARALLEL;
索引已创建。
SQL> CREATE OR REPLACE FORCE VIEW SYS.DBA_XMLSCHEMA_LEVEL_VIEW_DUP 2 ( 3 SCHEMA_URL, 4 SCHEMA_OWNER, 5 SCHEMA_OID, 6 LVL, 7 IN_CYCLE 8 ) 9 AS 10 SELECT schema_url, 11 schema_owner, 12 schema_oid, 13 MAX (LEVEL) + 1, 14 CONNECT_BY_ISCYCLE 15 FROM DBA_XML_SCHEMA_DEPENDENCY 16 CONNECT BY NOCYCLE PRIOR schema_url = dep_schema_url 17 AND PRIOR schema_owner = dep_schema_owner 18 AND PRIOR schema_oid = dep_schema_oid 19 GROUP BY schema_url, 20 schema_owner, 21 schema_oid, 22 CONNECT_BY_ISCYCLE 23 UNION 24 SELECT x.xmldata.schema_url, 25 x.xmldata.schema_owner, 26 x.sys_nc_oid$, 27 1, 28 0 29 FROM xdb.xdb$schema x 30 WHERE x.xmldata.includes IS NULL AND x.xmldata.imports IS NULL;
视图已创建。
SQL> GRANT SELECT ON SYS.DBA_XMLSCHEMA_LEVEL_VIEW_DUP TO SELECT_CATALOG_ROLE;
授权成功。
SQL> CREATE OR REPLACE FORCE VIEW SYS.DBA_XMLSCHEMA_LEVEL_VIEW 2 ( 3 SCHEMA_URL, 4 SCHEMA_OWNER, 5 SCHEMA_OID, 6 LVL 7 ) 8 AS 9 SELECT l.schema_url, 10 l.schema_owner, 11 l.schema_oid, 12 l.lvl 13 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP l 14 WHERE NOT EXISTS 15 (SELECT 1 16 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP 17 WHERE schema_url = l.schema_url 18 AND schema_owner = l.schema_owner 19 AND schema_oid = l.schema_oid 20 AND in_cycle = 1) 21 UNION 22 SELECT l.schema_url, 23 l.schema_owner, 24 l.schema_oid, 25 0 26 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP l 27 WHERE EXISTS 28 (SELECT 1 29 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP 30 WHERE schema_url = l.schema_url 31 AND schema_owner = l.schema_owner 32 AND schema_oid = l.schema_oid 33 AND in_cycle = 1);
视图已创建。
SQL> grant select on sys.ku$_xmlschema_view to public 2 ;
授权成功。
SQL> CREATE OR REPLACE PUBLIC SYNONYM DBA_XMLSCHEMA_LEVEL_VIEW FOR SYS.DBA_XMLSCHEMA_LEVEL_VIEW;
同义词已创建。
SQL> GRANT SELECT ON SYS.DBA_XMLSCHEMA_LEVEL_VIEW TO PUBLIC;
授权成功。
再次运行catmetx.sql脚本，无任何报错 |
4 故障处理后结果
|
d:\wendang\SkyDrive\rs2\sql>exp system/oracle full=y file=e:\full.dmp indexes=n constraints=n
Export: Release 11.2.0.3.0 – Production on 星期一 8月 18 14:03:15 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 注: 将不导出表的索引 注: 将不导出表的约束条件
即将导出整个数据库… . 正在导出表空间定义 . 正在导出概要文件 . 正在导出用户定义 . 正在导出角色 . 正在导出资源成本 . 正在导出回退段定义 . 正在导出数据库链接 . 正在导出序号 . 正在导出目录别名 . 正在导出上下文名称空间 . 正在导出外部函数库名 . 导出 PUBLIC 类型同义词 . 正在导出专用类型同义词 . 正在导出对象类型定义 . 正在导出系统过程对象和操作 . 正在导出 pre-schema 过程对象和操作 . 正在导出簇定义 . 即将导出 SYSTEM 的表通过常规路径… 没有报错了
这里已经看到开始导system用户的表，没有再出现之前的故障 |
EXP-00056 ORA-04063处理过程：等您坐沙发呢！