오라클 패치 후 export가 안된다!
2010.05.12 08:21
원문 : http://www.ischo.net -- 조인상 // 시스템 엔지니어
Writer : http://www.ischo.net -- ischo // System Engineer in Replubic Of Korea
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
상황 : 오라클 8.0 및 오라클 8i 에서 exp 한 dump파일을
오라클 9i 9.2.0.4 (release 2 설치 후 패치셋 3 적용) 데이터베이스에
imp 시킨 후....
데이터베이스 정기백업을 위해서 exp를 실행한 후 다음과 같은 메시지를
보이며 exp가 정지한다.
( 문제 1 )
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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 KO16KSC5601 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
( 문제 1 해결책 )
위 문제는 오라클 하위버전 덤프를 상위버전에 imp 시켰을 경우에
발생하며 $ORACLE_HOME/rdbms/admin/catexp.sql 을
구동시키면 해결된다.
root# su - oracle
oracle$ sqlplus '/ as sysdba'
sql> start rdbms/admin/catexp.sql
( 문제 2 )
위와 같은 상황에서
exp 실행시 다음과 같은 에러를 보이며 exp가 정지한다.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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 KO16KSC5601 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
위 문제는 패치셋3 적용후에 발생하며
$ORACLE_HOME/rdbms/admin/catpatch.sql을 실행시키면 해결된다.
root# su - oracle
oracle$ sqlplus '/ as sysdba'
sql> start rdbms/admin/catpatch.sql
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
상황 : 오라클 8.0 및 오라클 8i 에서 exp 한 dump파일을
오라클 9i 9.2.0.4 (release 2 설치 후 패치셋 3 적용) 데이터베이스에
imp 시킨 후....
데이터베이스 정기백업을 위해서 exp를 실행한 후 다음과 같은 메시지를
보이며 exp가 정지한다.
( 문제 1 )
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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 KO16KSC5601 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
( 문제 1 해결책 )
위 문제는 오라클 하위버전 덤프를 상위버전에 imp 시켰을 경우에
발생하며 $ORACLE_HOME/rdbms/admin/catexp.sql 을
구동시키면 해결된다.
root# su - oracle
oracle$ sqlplus '/ as sysdba'
sql> start rdbms/admin/catexp.sql
( 문제 2 )
위와 같은 상황에서
exp 실행시 다음과 같은 에러를 보이며 exp가 정지한다.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit 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 KO16KSC5601 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
위 문제는 패치셋3 적용후에 발생하며
$ORACLE_HOME/rdbms/admin/catpatch.sql을 실행시키면 해결된다.
root# su - oracle
oracle$ sqlplus '/ as sysdba'
sql> start rdbms/admin/catpatch.sql
Re: 관련 metalink 포럼답변
본문 : http://www.ischo.net -- 조인상 //시스템 엔지니어
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Oracle Server-Enterprise and Standard Edition/DBA Administration Technical Forum
Displayed below are the messages of the selected thread.
Thread Status: Closed
From: Karen OGrady 20-Feb-03 21:28
Subject: Full export gives error EXP-00056
RDBMS Version: 9.2.0.1
Operating System and Version: Solaris 5.8
Error Number (if applicable): 00056
Product (i.e. SQL*Loader, Import, etc.): Export
Product Version: 9.2.0.2
Full export gives error EXP-00056
When I try to do a full export on one of my databases I get:
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ASP_TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ASP_TEST
About to export ASP_TEST's objects ...
. exporting database links
. exporting sequence numbers
. 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-00000: Export terminated unsuccessfully
I had a recent problem with the temp tablespace on this database. I created a new temp, moved everyone to it. Dropped the bad tablespace and recreated it, then moved users back. I don't know if that has anything to do with this issue but since this showed up right after (or at the same time) I thought I should include the info.
I can export tables by user successfully. Any suggestions?
Karen
--------------------------------------------------------------------------------
From: Oracle, Gina Thunder 25-Feb-03 15:04
Subject: Re : Full export gives error EXP-00056
Hi Karen,
You have two versions listed 9.2.0.1 & 9.2.0.2. Was catpatch.sql run when you applied 9.2.0.2? Bug 2653887 (closed as not a bug) reports catpatch.sql resolving a similar issue.
Gina
Oracle Support Services
--------------------------------------------------------------------------------
From: Karen OGrady 25-Feb-03 18:41
Subject: Re : Full export gives error EXP-00056
Thanks. That fixed it.
Karen
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.