ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" has error

1 使用exp導出數據，發(fā)現報錯

EXP導出報錯:

exporting cluster definitions

EXP-00056: ORACLE error 4063 encountered

ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" has errors

EXP-00000: Export terminated unsuccessfully

2 解決方法：

1 使用 rman 做數據庫全備

一致性備份 （ 適用于 NOARCHIVELOG 和 ARCHIVELOG 模式 ）

  c:\rman target sys/oracle@TEST nocatalog

  RMAN>shutdown immediate

  RMAN>startup mount

  RMAN>backup database; 如果備份到別的目錄 backup database format ='d:\backup\%d_%s.dbf' ;

  RMAN>sql 'alter system archive log current '

2 查看組件信息

col comp_id for a15

col version for a15

col comp_name for a30

SQL> select comp_id,comp_name,version,status from dba_registry;

3 查看用戶(hù)對象信息

SELECT status, object_id, object_type, owner||'.'||object_name     "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID'    ORDER BY 4,2;

4 查看當前系統 XDB 默認的表空間及用戶(hù)的密碼

    select username,password,default_tablespace,temporary_tablespace from dba_users where username like '%XDB%';

5 檢查環(huán)境變量 ：

Before installing or upgrading XDB,make sure the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable isset correctly.  

That is, the first directory referenced should be $ORACLE_HOME/lib.  This environment variable is used to resolve thelocation of the shared library "libxdb.so".

6 確保 XDB 有執行 execute permissionson the DBMS_LOB and UTL_FILE  packages 的權限

XDB must have execute permissionson the DBMS_LOB and UTL_FILE  packages.  XDB automatically has theseprivileges because they are granted to PUBLIC by default.  

If theseprivileges have been revoked from PUBLIC for security reasons, errors will be reportedduring the installation / upgrade of XDB and many XDB objects will becomeinvalid,

making the component itself invalid.  Therefore, grant executeprivileges on these packages back to PUBLIC before installing / upgrading XDB

or if you do not wish for PUBLIC to have these permissions, after the install /upgrade, grant execute permissions on these packages directly to XDB

and run$ORACLE_HOME/rdbms/admin/utlrp.sql to recompile the invalid objects.

SQL> DESC DBMS_LOB

SQL> DESC UTL_FILE

SQL> GRANT EXECUTE ON DBMS_LOB TO XDB;

SQL> GRANT EXECUTE ON UTL_FILE TO XDB;

7 檢查以下幾項

7.1 對 Oracle10.2 來(lái)說(shuō)， XDK 組件是需要的

7.2 XDB 表空間最少要 200M

7.3 確保 SHARED_POOL_SIZE 和  JAVA_POOL_SIZE 至少

(4)    Prior to Oracle 10.2, a validinstallation of XDK is also required

(5)    Allocate at least 200 MB forthe XDB repository tablespace datafile.

(6)    Ensure that the SHARED_POOL_SIZE and JAVA_POOL_SIZE is set to at least 150 MB.

5 關(guān)閉數據庫

6 啟動(dòng)數據庫刪除 XDB

SQL> startup

SQL> spool xdb_removal521.log

SQL> set echo on;

SQL> @?/rdbms/admin/catnoqm.sql

SQL> spool off;

7 檢查 XDB 用戶(hù)的信息是否刪除干凈

8 關(guān)閉數據庫

9 啟動(dòng)數據庫重新安裝 XDB

SQL> startup;

SQL> spool xdb_install521.log

SQL> set echo on;

SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs>

SQL> @?/rdbms/admin/catxdbj.sql

SQL> @?/rdbms/admin/utlrp.sql

SQL> spool off

10 查詢(xún) XDB 組件及用戶(hù)對象是否有無(wú)效對象。

SQL> col comp_id for a15

SQL> col version for a15

SQL> col comp_name for a30

SQL> select comp_id,comp_name,version,status from dba_registry;

SELECT status, object_id, object_type, owner||'.'||object_name     "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID'    ORDER BY 4,2;

11 ．  Reload ORDIM 組件

在前面提到 Oracle Multimedia （ ORDIM ）組件需要使用 XDB 組件，在我們重建 XDB 組件之后需要 Reload 一些 ORDIM 組件。

Oracle 10g 版本

The XDB schemais also used by other products like interMedia and Spatial to register theirXML Schemas. When XDB has been deinstalled and reinstalled for whatever reasonthe XML Schemas for these products will have to be reinstalled as well.

--XDB Schema 被其他的組件所用 ， 比如 interMedia （ 11g 中叫 Multimedia ） 和 Spatial 組件 ， 他們注冊自己的信息在 XML Schemas 中。   當 XDB 被刪除或者重建時(shí)，其他組件在 XML 中的信息也被卸載，這就導致其他組件的無(wú)效。

-- Register XML Schemas for the Rules Manager component (part of CATPROC)

-- 在 XMLSchemas 中注冊 Rules Manager 組件信息 ：

  connect / as sysdba

  alter session set current_schema  = EXFSYS;   

  @?/rdbms/admin/rulpbs.sql

-- Register XML Schemas for the interMedia product

-- 在 XMLSchemas 中注冊 interMedia 組件信息 ：

 connect / as sysdba  

  alter session set current_schema=ORDSYS;  

  @?/ord/im/admin/imxreg.sql

 -- Register XML Schemas for the Locator/Spatial product

-- 在 XMLSchemas 中注冊 Locator/Spatial 組件信息

 connect / as sysdba  

  alter session set current_schema=MDSYS;  

  @?/md/admin/sdogmlsc.sql

  @?/md/admin/sdoepsgx.sql

  @?/md/admin/sdogrxml.sql  -- not to be run for a Locatorinstallation

12 重建 XDB 組件后，還有無(wú)效的 XDB 對象

解決方法有兩種：

（ 1 ）方法一

SQL> CONN / AS SYSDBA (Connect asSYSDBA)

SQL> DESC DBMS_LOB

SQL> DESC UTL_FILE

SQL> GRANT EXECUTE ON DBMS_LOB TO XDB;

SQL> GRANT EXECUTE ON UTL_FILE TO XDB;

SQL> @?/rdbms/admin/utlrp.sql  -- Run this afew times

SQL> SELECT * FROM DBA_ERRORS;

SQL> SELECT COMP_ID, COMP_NAME, STATUS FROM DBA_REGISTRY;

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS FROM DBA_OBJECTS WHEREOWNER = 'XDB' AND STATUS != 'VALID';

（ 2 ） 方法二 ：

a) Grant following privileges to XDBuser

SQL> GRANTEXECUTE ON DBMS_LOB TO XDB;

SQL> GRANT EXECUTE ON UTL_FILE TO XDB;

b) Reload the XDB Component (usingxdbrelod.sql).

       - Refer Master Note for Oracle XMLDatabase (XDB) Installation (Doc ID 1292089.1)

13 EXP 導出驗證