EXP-00008: ORACLE 오류 6552가 발생했습니다
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
EXP-00000: 엑스포트가 실패로 끝났습니다
이런 문제는 어떻게 해야 해결할 수 있을지요?
경험해 보시거나 아시는 분께서는 조언 좀 부탁드립니다.
A. 우선 ORA-06552 로 게시판 검색을 하시면 06년 9월 초 질문 사항에 대해서
제가 답변한 것이 있습니다. 그에 대한 해결방법을 보시지요. 그런데
character set 문제에 대한 처리과정에서 위험한 것이 있습니다.
character set을 잘못 변경 했을 경우에는 도저히 DB가 OPEN하지 않는 경우가
많습니다. 그러니 꼭 metalink를 통해서 한국 오라클의 공식적인 지원이 필요합니다.
Export: Release 9.2.0.1.0 - Production on Tue Oct 31 01:04:43 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in KO16KSC5601 character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user KIUP
. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 6552 encountered
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
EXP-00000: Export terminated unsuccessfully
9206다른 서버에서 export받은 파일을 import할때는 이상없이 잘됩니다.
백업없는 DBA는 용답이 되지 않으므로 많은 성원 부탁드립니다.
Re: caused by a "mixup" in the character set of the database.
아래를 알려주십시오.
ORA-06553: PLS-553: character set name is not recognized
으로 보아서는 character set문제로 보입니다.
1. DB에서
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM
NLS_DATABASE_PARAMETERS WHERE ARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
2. DB에서
select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);
3. OS에서
echo $NLS_LANG
글 수정:
민천사 (민연홍)
관련사항
Causes
======
This error may be caused by a "mixup" in the character set of the database.
Correcting the problem
======================
This problem was likely caused when the database character set was changed
incorrectly in the past, and some locations where the character set is stored
were updated and others were not.
In order to correct this problem we can simply run a script that correctly
changes the character set in all the locations to the current database character
set:
Re: caused by a "mixup" in the character set of the database. 게시일: 2006. 10. 30 오후 10:52 YeonHong.Min님의 질문에 답변
Re: caused by a "mixup" in the character set of the database. 게시일: 2006. 10. 30 오후 11:57 ghkim님의 질문에 답변
AL32UTF8 VARCHAR2
US7ASCII VARCHAR2
UTF8 VARCHAR2
이 부분은 문제가 있습니다. 정상이라면 VARCHAR2가
하나의 CHARACTERSET을 가지고 있습니다.
character set이 어떻게 되는지요? UTF8 인가요?
이 사항은 오라클에 공식적으로 요청을 해서 처리해야할 문제로
보입니다. 함부로 character set을 변경했다가는 위험하기 때문
입니다.
우선 이 사항에 대한 note자료를 보면 아래와 같습니다.
아래에서도 해결방법을 알려주기는 하나 공식적으로 오라클에
요청해서 진행하는 편이 좋을 것 같습니다.
만약 그러한 경우가 되지 않을 경우에는 offline backup을 2 copy
해놓고 아래와 같이 작업을 합니다.
note 286964.1
PLS-553 when calling or compiling pl/sql objects
(obviously the actual character sets can differ)
Here you can see that there are 2 different character sets returned for VARCHAR2
data, which indicates a mixup in the database character set setup, which is the
cause for this PLS-553 error.
<처리방법>
Correcting the problem
======================
This problem was likely caused when the database character set was changed
incorrectly in the past, and some locations where the character set is stored
were updated and others were not.
In order to correct this problem we can simply run a script that correctly
changes the character set in all the locations to the current database character
set:
a) Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all.
SQL>show parameter parallel_server
b) Run the next script in SQLPLUS connected "as sysdba"
Please make sure you have a valid backup to fall back on before you run this script!
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
c) Restore the parallel_server parameter in INIT.ORA, if necessary.
This script doesn't change anything for the data that is already stored, but it
re-enforces database character set to be known in all places where it should be
stored.
After running this you can run the query under "Verification of the cause" once
more, and now there should just 2 sets of character sets, 1 for N-types and 1
for normal types. When that is indeed the case the PLS error will be solved.
Further actions
===============
It would be useful to do a healthcheck on this database because it has been
running with this incorrectly converted character set. The following note
describes a "character set health check" for the database:
Note 225938.1 Database Character Set Healthcheck
Further reading
===============
Note 257736.1 Changing the Database Character Set - an extended overview
Note 124721.1 Migrating an Applications Installation to a New Character Set
For further NLS / Globalization information you may start here:
Note 267942.1 - Globalization Technology (NLS) Knowledge Browser Product Page
Note 60134.1 Globalization (NLS) - Frequently Asked Questions
ghkim님의 질문에 답변 
댓글을 달아 주세요
진짜 너무 친절한 설명 고맙습니다. 덕분에 해결했습니다. ^^