본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어

+++++++++++++++++++++++++++++++++++++++++++++++++++++++


1. 기존에 사용중이던 9.2.0.5 오라클 디비에 새로운 BMS라는
instance를 추가하고 나서 exp 백업을 시도하던 중...

다음과 같은 에러가 발생

[SDCNDB:BMS:/onnaraexp/exp] exp system/system@BMS file=test.dmp log=test.log full=y

Export: Release 9.2.0.5.0 - Production on Thu Dec 18 09:58:04 2008

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


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

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully






2. 위문제를 metalink에서 검색

제목: PLS-201 and ORA-06512 at "XDB.DBMS_XDBUTIL_INT" while Exporting Database
  문서 ID: NOTE:243133.1 유형:  PROBLEM
  마지막 개정 날짜:  07-JUL-2004 상태:  PUBLISHED


Problem Description
-------------------
An export from an Oracle9i database fails with:

...
. exporting triggers
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 26:
PLS-00201: identifier 'XDB.DBMS_XDBUTIL_INT' must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 26:
PLS-00201: identifier 'XDB.DBMS_XDBUTIL_INT' must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully


You have verified the following:

1. The package DBMS_XDBUTIL_INT object does not exist:

  SQL> SELECT status,object_id,object_type,SUBSTR(owner||'.'||object_name,
      1, 40) "OWNER.OBJECT" FROM dba_objects
      WHERE object_name = 'DBMS_XDBUTIL_INT';

  no rows selected

2. The error messages during export are identical as mentioned above.
  For the error ORA-1422 (exact fetch returns more than requested number
  of rows) in XDB.DBMS_XDBUTIL_INT (line 52), refer to:
  - Note 221178.1 "ORA-01422 ORA-06512: at "XDB.DBMS_XDBUTIL_INT" while
              exporting full database"
  - Note 256548.1 "Export of Database with XDB Throws ORA-1422 Error"


Solution Description
--------------------
There are three possible solutions:

1. If you are *NOT* having any schema's in the database that use the
  XML functionality, remove the XDB schema and the XDB tablespace by
  following the steps in the article mentioned below.  The XDB schema
  and the XML functionality can be re-installed at a later time if
  actually needed.

  Note 243554.1 "How to Deinstall and Reinstall XML Database (XDB)"

  WARNING: DO NOT DE-INSTALL XDB IF THERE ARE REGISTERED SCHEMA'S OR
        IF XML DATABASE ITEMS HAVE BEEN CREATED !

2. If you are using the XML functionality, and you have recently applied
  a patch-set, check whether the data dictionary has been upgraded
  accordingly.

  SQL> SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
      version, SUBSTR(comp_name,1,30) comp_name
      FROM dba_registry ORDER BY 1;

  If the version of the components CATALOG, CATPROC, XDB (among others)
  is lower than the patch-set version that you have applied, re-run the
  script catpatch.sql after a startup migrate. See the readme file of
  the patch-set for all these mandatory post-installation instructions.

3. If you are using the XML functionality, and the data dictionary
  components have the correct version and are VALID or LOADED,
  then re-create the package XDB.DBMS_XDBUTIL_INT with (example with
  UNIX syntax):

  SQL> connect sys/<password> AS SYSDBA
  SQL> @?/rdbms/admin/prvtxdb.plb
  SQL> exit


Explanation
-----------
The PLS-201 indicates that the export utility is expecting to find the
packge DBMS_XDBUTIL_INT.  This package together with its package body
should exist in the XDB schema and should be valid.






3. 위와 같은 이유였다.  즉, 새로 추가한 DB에도 catpatch.sql 을 돌려줘야 했다.

SQL> SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
      version, SUBSTR(comp_name,1,30) comp_name
      FROM dba_registry ORDER BY 1;

스크립트를 돌리면 다음과 같이 패치가 미적용된 레지스트리 목록이 나온다.

COMP_ID                STATUS    VERSION
------------------------------ ----------- --------------------
COMP_NAME
------------------------------------------------------------
AMD                  INVALID    9.2.0.1.0
OLAP Catalog

APS                  LOADED    9.2.0.1.0
OLAP Analytic Workspace

CATALOG                INVALID    9.2.0.5.0
Oracle9i Catalog Views


COMP_ID                STATUS    VERSION
------------------------------ ----------- --------------------
COMP_NAME
------------------------------------------------------------
CATJAVA                VALID    9.2.0.1.0
Oracle9i Java Packages

CATPROC                VALID    9.2.0.5.0
Oracle9i Packages and Types

CONTEXT                VALID    9.2.0.1.0
Oracle Text


COMP_ID                STATUS    VERSION
------------------------------ ----------- --------------------
COMP_NAME
------------------------------------------------------------
JAVAVM                VALID    9.2.0.1.0
JServer JAVA Virtual Machine

ODM                  LOADED    9.2.0.1.0
Oracle Data Mining

ORDIM                  LOADED    9.2.0.1.0
Oracle interMedia


COMP_ID                STATUS    VERSION
------------------------------ ----------- --------------------
COMP_NAME
------------------------------------------------------------
OWM                  VALID    9.2.0.1.0
Oracle Workspace Manager

SDO                  LOADED    9.2.0.1.0
Spatial

WK                    VALID    9.2.0.1.0
Oracle Ultra Search


COMP_ID                STATUS    VERSION
------------------------------ ----------- --------------------
COMP_NAME
------------------------------------------------------------
XDB                  INVALID    9.2.0.1.0
Oracle XML Database

XML                  VALID    9.2.0.2.0
Oracle XDK for Java

XOQ                  LOADED    9.2.0.1.0
Oracle OLAP API


15 개의 행이 선택되었습니다.

SQL>