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)

50,000 BLOG HITS

Thank you all Oracle lovers/users.........today is the day i need to celebrate that my blog has reached a 50K hits i thank once again all the oracle users whether it is useful or not useful to you but i keep on posting a new topics and issues what ever i face

Oracle Drop Database 11gR2 on LINUX

Dropping a database will remove all corresponding datafiles, redo log files and control files. The database must be mounted in exclusive and restricted mode.

Drop database command will not do any effect on archived log files and backups of the database. Drop database command will not delete the files on RAW disks.

If you created your database with DBCA, you can use the same DBCA tool to drop the database.

Example.

$export ORACLE_SID=DEVDB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 28 18:18:41 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>DROP DATABASE;

DROP DATABASE

*

ERROR at line 1:

ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

NOTE :  Drop database does not work when the database is opened in normal open mode

SQL > shut immediate

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 282352256 bytes

Fixed Size                  2132856 bytes

Variable Size            1644174848 bytes

Database Buffers         1140850688 bytes

Redo Buffers               36388864 bytes

Database mounted.

SQL> alter system enable restricted session;

System altered.

Or open the database using “startup mount Restrict” command

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Now, all the Control files, redo log files and Datafiles are automatically removed but you can still see the Parameter file and archivelog files.

Note:     If your database is running on windows you have to remove the registry entries manually.