오라클 패치 후 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
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
15 | windows용 오라클 온라인 백업 스크립트 | 조인상 | 2010.07.01 | 20143 |
14 | 온라인 백업방법 begin~end backup | 조인상 | 2010.05.12 | 20682 |
13 | ORA-04063: package body "EXFSYS.DBMS_EXPFIL_EXP" has errors | 조인상 | 2012.01.04 | 20766 |
12 | 일반유저에게 kill session 권한 주기 | ischo | 2019.08.12 | 22324 |
11 | datafile, redolog, controlfile 위치 변경하기 | 조인상 | 2012.08.02 | 24625 |
10 | 오라클에 접속하는 최대 인스턴스개수(세션개수)를 늘리는 방법 | 조인상 | 2010.05.12 | 24667 |
9 | 오라클 11g - ADR/ADRCI : 새로운 alertlog 위치 | 조인상 | 2011.05.17 | 29720 |
8 | exp실행시 에러. ORA-06512 SYS.DBMS_METADATA_INT | 조인상 | 2010.05.12 | 32658 |
7 | 오라클 패치후 각 컴퍼넌트들 버전 확인 쿼리 | 조인상 | 2010.10.05 | 46338 |
6 | 성능분석을 위한 v$sysstat, v$sesstat, v$system_event 조회 | 조인상 | 2011.09.27 | 50449 |
5 | 히든 파라미터 확인하는 쿼리 | 조인상 | 2010.05.12 | 62461 |
4 | ORA-28040 : No matching authentication protocol | 조인상 | 2014.11.13 | 67285 |
3 | 오라클 온라인백업 스크립트 (ksh 용) [1] | 조인상 | 2010.05.12 | 100773 |
2 | centOS 5.2 에 오라클 10g 설치 | 조인상 | 2010.05.12 | 109931 |
1 | exp error - ORA-19206: Invalid value for query or REF CURSOR parameter | 조인상 | 2010.05.12 | 156647 |
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.