Monday, October 15, 2012

Changing Default CHARACTER SET of the database



Here my goal is to change the default character set from WE8ISO8859P1 to AL32UTF8 on  LINUX
Remember, this was tested on my DEVELOPMENT standalone database (refer Oracle Doc before doing on PROD).

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL> SELECT SUM (a.log_space + b.data_space + c.tempspace) "Total_DB_Size (G)"
   FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space  FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space  FROM v$log) a,
  (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace FROM dba_temp_files) c;

Total_DB_Size (G)
-----------------
            11.29


Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.


SQL> select name from v$database;

NAME
---------
TEST


SQL > select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------
WE8ISO8859P1

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

VALUE
----------------------------------------
AL16UTF16


SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1

NLS_NCHAR_CHARACTERSET
AL16UTF16
16UTF16



SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

SQL> exit
$
$ expdp directory=TEST dumpfile=FULL_TEST_10112012.dmp logfile=FULL_TEST_10112012.log full=y

Full Database backup is taken, 

SQL> 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) order by CHARACTERSET;

CHARACTERSET                             TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16                                NCHAR
AL16UTF16                                NCLOB
AL16UTF16                                NVARCHAR2
WE8ISO8859P1                             CHAR
WE8ISO8859P1                             CLOB
WE8ISO8859P1                             VARCHAR2

6 rows selected.


Oracle note suggests that if the character set conversion has happened between  a 7/8 bit character set like WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc,

then there will be data loss for clob columns which display the old character set.
So it is best to take a full back of the database, preferably using the tradional export utility.


SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


sql> shut immediate

SQL> startup Restrict

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
 Database altered.

after changing the character set bounced the database

SQL> 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) order by CHARACTERSET;


CHARACTERSET                             TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16                                NCHAR
AL16UTF16                                NCLOB
AL16UTF16                                NVARCHAR2
AL32UTF8                                 CHAR
AL32UTF8                                 CLOB
AL32UTF8                                 VARCHAR2

6 rows selected.


SQL> select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8


SQL>  select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL>  SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16


SQL>  select * from v$nls_parameters where parameter like '%CHARACTERSET';

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16


NOTE:   we can use new GUI based DMU (Database Migration Assistant for Unicode) tool to convert the NLS_CHARACTERSET of an existing database to AL32UTF8 or UTF8

How to Migrate a WE8ISO8859P1 DB to AL32UTF8 using DMU 1.2 - an example (Doc ID 1546507.1)



9 comments:

harshal said...

Very good and detailed..Thanks!

gabo said...

Very helpful, Thank you

Syed Eazas said...

Its nice document. Appreciate :)

oguri said...

Doc ID 260192.1

In 10g and 11g you NEED to use Csalter (or the DMU tool).
In 12c you NEED to use the DMU tool.
Do NOT use "Alter database character set" in 10g, 11g or 12c to go to AL32UTF8 or UTF8.
Using "Alter database character set" to go to UTF8 or AL32UTF8 is NOT supported in 10g, 11g or 12c and WILL corrupt at least (!) Data Dictionary objects and most likely also User data.

Asaduzzaman said...

Nice

Unknown said...

Thanks for sharing this. It helps me.

Unknown said...

Hi chandrashekar
I have two questions:
Q1: After I change the character set, I must impdp all tables that contains at least onE CLOB column? or all schemas that contains at least one CLOB column?
The steps will be: a)alter database open; b)impdp ??
Q2: You say exp tool (traditional export), but I saw you used expdp. Why?
thanks

nikkisa889 said...

It’s onerous to search out knowledgeable folks on this topic, however you sound like you recognize what you’re talking about! Thanks online gambling casino

Lexman said...

Excellent !

Post a Comment

Auto Scroll Stop Scroll