Oracle NLS_LANG 변경하기

[Oracle] 서버 인코딩(문자셋) 변경하기

August 19th, 2008

개발용으로 받은 오라클 dmp파일의 인코딩이 KO16KSC5601로 되어 있고, imp할 대상 서버의 인코딩은 WE8ISO8859P15 .. 웩 -ㅠ-
하루종일 뒤진결과 다음과 같이 간단하게 변경이 가능하다 (-_-)b 굳ㅋ

1. 오라클 데이터베이스 관리자로 접속하여 NLS_CHARACTERSET, NCHAR의 CHARACTERSET에 한국어를 지원하도록 파라미터의 속성값을 KO16KSC5601로 변경

[문자셋 변경]
SQL> update sys.props$ set value$='KO16KSC5601' where name='NLS_CHARACTERSET';
1 row updated.

SQL> update sys.props$ set value$='KO16KSC5601' where name='NLS_NCHAR_CHARACTERSET';
1 row updated.

[언어셋 변경]
SQL> update sys.props$ set value$='AMERICAN_AMERICA.KO16KSC5601' where name='NLS_LANGUAGE';
1 row updated.

[변경사항 저장 및 데이터베이스 재연동]
SQL> commit;
Commit complete.

[오라클 재시작]
SQL> shutdown
Database closed.
Database dismounted.
Oracle instance shut down.

SQL>startup
ORACLE instance started.
Total System Global Area 235999352 bytes

Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

[변경사항 확인]
SQL> select * from v$nls_parameters;

2. 문자셋과 마찬가지로 오라클 데이터베이스 관리자로 접속하여 NLS_LANGUAGE 파라미터의 속성값을 AMERICAN_AMERICA.KO16KSC5601로 변경
오라클을 설치할 때 지정해 주었던 .bash_profile 파일에서 Oracle 언어 환경변수를 다음과 같이 변경해 준다.

export NLS_LANG=AMERICAN_AMERICA.KO16KSC5601

For more information see:

• Character Set Migration
• Character Set Scanner Utilities

접기

Character Set Migration using CSSCAN and CSALTER

This article presents a simple example of migrating the default character set of a database using the CSSCAN and CSALTER character set scanner utilities provided by Oracle. The basic steps involved in character set conversion are listed below:

• Backup
• CLUSTER_DATABASE=FALSE (*RAC Only*)
• SHUTDOWN IMMEDIATE
• STARTUP
• CSSCAN
• SHUTDOWN IMMEDIATE
• STARTUP RESTRICT
• CSALTER
• CLUSTER_DATABASE=TRUE (*RAC Only*)
• SHUTDOWN IMMEDIATE
• STARTUP

Performing a backup before starting the character set conversion is very important. If the conversion fails part way through, you must restore from a backup before re-attempting the conversion.

With the backup complete, the instance must be restarted. In the case of RAC databases, the CLUSTER_DATABASE parameter should be set to FALSE, then all RAC instances stoped and only one restarted.

ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; -- RAC Only

SHUTDOWN IMMEDIATE;

STARTUP;

Once the database is open, the CSSCAN utility is run. This example command below would perform a scan of the whole database.

CSSCAN \"sys/password@db10g AS SYSDBA\" FULL=Y

If the character set migration utility schema is not installed on your database, you will get the following error.

C:\>CSSCAN \"sys/password@db10g AS SYSDBA\" FULL=Y



Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue Dec 5 06:54:23 2006



Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

CSS-00107: Character set migration utility schema not installed



Scanner terminated unsuccessfully.



C:\>

The character set migration utility schema is installed by running the "$ORACLE_HOME/rdbms/admin/csminst.sql" script in SQL*Plus as the SYS user. Once the schema is present, the character set scanner should work normally.

In the following example, the current database character set is "WE8MSWIN1252", which we will convert to "WE8ISO8859P15" using 32 scan processes.

C:\>CSSCAN \"sys/password@db10g AS SYSDBA\" FULL=Y



Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue Dec 5 07:00:36 2006



Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options



Current database character set is WE8MSWIN1252.



Enter new database character set name: > WE8ISO8859P15



Enter array fetch buffer size: 1024000 >



Enter number of scan processes to utilize(1..32): 1 > 32



Enumerating tables to scan...



. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]

. process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]

.

.

. process 14 scanning SYS.WRH$_SERVICE_WAIT_CLASS[AAANZRAADAAAJKBAAA]

. process 22 scanning SYS.WRH$_WAITSTAT[AAANUxAADAAAHzJAAA]



Creating Database Scan Summary Report...



Creating Individual Exception Report...



Scanner terminated successfully.



C:\>

Once the scan has completed successfully, the database should be opened in restricted mode so you can run the "$ORACLE_HOME/rdbms/admin/csalter.plb" script as the SYS user. The example below shows a successful conversion.

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP RESTRICT

ORACLE instance started.



Total System Global Area  289406976 bytes

Fixed Size                  1248600 bytes

Variable Size              83886760 bytes

Database Buffers          201326592 bytes

Redo Buffers                2945024 bytes

Database mounted.

Database opened.

SQL> @@csalter.plb



0 rows created.





Function created.





Function created.





Procedure created.



This script will update the content of the Oracle Data Dictionary.

Please ensure you have a full backup before initiating this procedure.

Would you like to proceed (Y/N)?Y

old   6:     if (UPPER('&conf') <> 'Y') then

new   6:     if (UPPER('Y') <> 'Y') then

Checking data validility...

begin converting system objects



PL/SQL procedure successfully completed.



Alter the database character set...

CSALTER operation completed, please restart database



PL/SQL procedure successfully completed.





0 rows deleted.





Function dropped.





Function dropped.





Procedure dropped.



SQL>

If there are possible conversion problems, the process will report the problem and clean itself up without performing the conversion. Here are a couple of the messages I got when trying this process.

# When I tried to convert WE8MSWIN1252 -> AL32UTF8.

Checking data validility...

Unrecognized convertible date found in scanner result



# When I tried to run the CSALTER script without a SHUTDOWN-STARTUP RESTRICT.

Checking data validility...

Sorry only one session is allowed to run this script

Once the conversion is complete, you must restart the instance. In the case of RAC databases, the CLUSTER_DATABASE parameter should be set back to TRUE.

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE; -- RAC Only

SHUTDOWN IMMEDIATE;

STARTUP;

For more information see:

• Character Set Migration
• Character Set Scanner Utilities

Hope this helps. Regards Tim...