[오라클]how to change the character set from WE8MSWIN1252 to AL32UTF8 in oracle rac 11g.

how to change the character set from WE8MSWIN1252 to AL32UTF8 in oracle rac 11g.

Steps to change the character set from WE8MSWIN1252 to AL32UTF8

1. Make the cluster database to false from any of the node.

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

2. Now shut down both the instance running on the nodes

SQL> SHUTDOWN IMMEDIATE;

3. Now start the database on any one node.

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8; 
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16; 
SQL> SHUTDOWN IMMEDIATE;

4. Now start the db on one node and change the cluster database parameter to true.

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=true scope=spfile;

SQL> Shut immediate;

5. Now start the database using srvctl services

$ Srvctl start database –d RAC.

Posted by SANDEEP DEVAGAM DBA a

Asian data in AL16UTF16 is more compact than UTF8 and save disk space and have less disk I/O with Asian data. But The maximum lengths for NCHAR and NVARCHAR2 are 1000 and 2000 characters, which is less than the lengths for NCHAR (2000) and NVARCHAR2 (4000) in UTF8.

When we need to use European data... UTF8 better...

Assume I created database with NATIONAL CHARACTER SET AL16UTF16. And need to use UTF8.
Anyway should backup before and Export/[Import] data about NCHAR and NVARCHAR2...
Because We Can not change NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists.

set lines 130
set pages 10
column PROPERTY_NAME format a35
column PROPERTY_VALUE format a35
column DESCRIPTION format a35
select * from database_properties where property_name='NLS_NCHAR_CHARACTERSET'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set

create table a (b nvarchar2(4000));
create table a (b nvarchar2(4000))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

create table a (b nvarchar2(2001));
create table a (b nvarchar2(2001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

create table a (b nvarchar2(2000));

Table created.

Begin Step by Step...

- SHUTDOWN IMMEDIATE
- STARTUP MOUNT
- ALTER SYSTEM ENABLE RESTRICTED SESSION

Perhaps should:
alter system set job_queue_processes=0
alter system set aq_tm_processes=0

- ALTER DATABASE OPEN
- ALTER DATABASE NATIONAL CHARACTER SET UTF8

If =>
ALTER DATABASE NATIONAL CHARACTER SET UTF8
*
ERROR at line 1:
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists

Check alert log and remove data from list (don't forget backup before), Example:

Mon Feb 09 11:52:45 2009
ALTER DATABASE NATIONAL CHARACTER SET UTF8
PM.PRINT_MEDIA (AD_FLTEXTN) - NCLOB populated
ORA-12717 signalled during: ALTER DATABASE NATIONAL CHARACTER SET UTF8....

remark:
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists
Cause: NCLOB, NCHAR or NVARCHAR2 data changed the representation to Unicode when converting to a multibyte character set and must be migrated.
Action: Remove NCLOB, NCHAR or NVARCHAR2 data as listed in the alert file. The above type data can be migrated by methods such as import/export.

- SHUTDOWN IMMEDIATE
- STARTUP

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set

create table a (b nvarchar2(4000));

Table created.