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

Writer : http://www.ischo.net -- ischo // System Engineer in Replubic Of Korea

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

 

본문 : 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>
번호 제목 글쓴이 날짜 조회 수
75 10g ORA-32004 : obsolete and/or deprecated parameter(s) specified 조인상 2010.05.12 10739
74 centOS 5.2 에 오라클 10g 설치 조인상 2010.05.12 109915
73 히든 파라미터 확인하는 쿼리 조인상 2010.05.12 62419
72 오라클 복구 시나리오 조인상 2010.05.12 16648
71 골든 5.7 버전 데모 file 조인상 2010.05.12 7846
70 오라클 온라인백업 스크립트 (ksh 용) [1] file 조인상 2010.05.12 100758
69 [9i feature] DB_CACHE_ADVICE / V$DB_CACHE_ADVICE 에 대해서 조인상 2010.05.12 8068
68 exp error - ORA-19206: Invalid value for query or REF CURSOR parameter 조인상 2010.05.12 156614
» exp실행시 에러. ORA-06512 SYS.DBMS_METADATA_INT 조인상 2010.05.12 32633
66 OPN 가입시의 장점 file 조인상 2010.05.12 6705
65 오라클에 접속하는 최대 인스턴스개수(세션개수)를 늘리는 방법 조인상 2010.05.12 24616
64 Red Hat Enterprise Linux AS release 3 (Taroon) 기반에서 오라클 9.2.0 설치하기 조인상 2010.05.12 8063
63 Unix 에서 Raw Device 사용법 조인상 2010.05.12 14090
62 오라클 10g OCP 라이센스 업그레이드 정보 조인상 2010.05.12 7500
61 .net 과 오라클 연동에 대하여 조인상 2010.05.12 10321
60 [장애처리] 메모리 풀로 인한 OS hang-up 조인상 2010.05.12 9761
59 [설치장애기] 64비트 windows 2003에 오라클 10.2.0.1 32비트 설치시 ORA-12564에러 조인상 2010.05.12 16499
58 Redhat Linux 8.0 에 Oracle 9i install 조인상 2010.05.12 7601
57 레드햇리눅스9에 오라클9i설치중 hang 조인상 2010.05.12 8774
56 OS의 파일사이즈가 2GB로 제한될때 split으로 나누기 조인상 2010.05.12 6174
서버에 요청 중입니다. 잠시만 기다려 주십시오...