이번에 고객사에서 연락이 왔다. " 서버 상태가 좋지 않으니 서버를 새로 구성해야 겠습니다. "
나는 그리하여 기존에 납품나간 서버를 분석(?) 하고 있었다.
데이터를 최종적으로 백업 받으려고 EXPORT를 진행 한 순간 로그에 에러가 쌓이면서 백업이 Export 가 되지 않아 에러 내용을 보는순간 캐릭터 셋 문제인것을 확인 했습니다.
당시 Export시 화면
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
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
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
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
원인 : 캐릭터셋(문자셋) 중복 으로 인해 발생한 문제
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);
조회결과
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
KO16MSWIN949 CLOB
US7ASCII CLOB
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
KO16MSWIN949 CHAR
US7ASCII VARCHAR2
KO16MSWIN949 VARCHAR2
AL16UTF16 NCHAR
US7ASCII CHAR
9 rows selected.
이 서버의 경우는 KO16MSWIN949 , US7ASCII 두 캐릭터셋이 중복되어 발생한 문제였습니다.
TYPES_USED_IN 이 두가지 이상 중복된경우 문제가 발생합니다.
KO16MSWIN949 CLOB
US7ASCII CLOB
KO16MSWIN949 CHAR
US7ASCII CHAR
KO16MSWIN949 VARCHAR2
US7ASCII VARCHAR2
KO16MSWIN949 , US7ASCII 이두가지 에서 문제가 발생한것 입니다.
해결방법
로그인>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 17 23:39:12 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 6831239168 bytes
Fixed Size 2188728 bytes
Variable Size 3976202824 bytes
Database Buffers 2835349504 bytes
Redo Buffers 17498112 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;
System altered.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> col value new_value charset
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------
US7ASCII
SQL> col value new_value ncharset
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
VALUE
----------------------------------------
AL16UTF16
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE & CHARSET;
old 1: ALTER DATABASE CHARACTER SET INTERNAL_USE & CHARSET
new 1: ALTER DATABASE CHARACTER SET INTERNAL_USE US7ASCII
Database altered.
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE & NCHARSET;
old 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE & NCHARSET
new 1: ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16
Database altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 6831239168 bytes
Fixed Size 2188728 bytes
Variable Size 3976202824 bytes
Database Buffers 2835349504 bytes
Redo Buffers 17498112 bytes
Database mounted.
Database opened.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 6831239168 bytes
Fixed Size 2188728 bytes
Variable Size 3976202824 bytes
Database Buffers 2835349504 bytes
Redo Buffers 17498112 bytes
Database mounted.
Database opened.
완료시 정상 캐릭터셋
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
US7ASCII CLOB
AL16UTF16 NCLOB
US7ASCII VARCHAR2
AL16UTF16 NVARCHAR2
US7ASCII CHAR
AL16UTF16 NCHAR
6 rows selected.
저는 문제없이 해결이 완료 되었으나 이 작업을 시도중 에러가 생기실수 있습니다.
에러에 대해서는 차후에 포스팅 하겠습니다.
