CHANGE CHARACTER SET IN ORACLE 11G

Basically there are three methods by which you can change the characterset of a database and the method will vary a bit depending on the Oracle database version.

a) Conventional Export and Import
b) if the database is 8i or 9i via the ALTER DATABASE CHARACTERSET command
c) For 10g and upwards, we need to use the csalter.

Kindly note that whether we can use Export/Import method to change the characterset or not will depend on the particular output obtained after running csscan (Character Scanner Utility). So even if we are planning to use Export/Import method, we should install and run the csscan utility regardless. 

Current character set AR8ISO8859P6

New character set WE8IS08859P1

Step1: Check existing character

SQL> select * from nls_database_parameters;

PARAMETER VALUE
—————————— ————————————
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS.
NLS_CHARACTERSET AR8ISO8859P6
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

21 rows selected.

Step 2: Check whether csscan ustility is installed or not

[oracle@redhat1 admin]$CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11
:43 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: SYS AS SYSDBA

Password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CSS-00107: Character set migration utility schema not installed

So CSSCAN utility does not installed on your system, so we want to install the CSSCAN utility. Go to csminst.sql script

Step 3: Intsall csscan utility

SQL> @/app/oracle/product/11.2.0/dbhome_1/RDBMS/ADMIN/csminst.sql

Synonym created.
View created.
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Step 4: Run csscan to change character set

[oracle@redhat1 admin]$CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11:38
:37 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: SYS AS SYSDBA

Password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1

Current database character set is AR8ISO8859P6.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

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

. process 51 scanning EXFSYS.RLM$EVENTSTRUCT
. process 63 scanning EXFSYS.RLM$RULESETSTCODE
. process 59 scanning EXFSYS.RLM$RULESETPRIVS
. process 27 scanning EXFSYS.RLM$INCRRRSCHACT
. process 62 scanning EXFSYS.RLM$ORDERCLSALS
. process 21 scanning EXFSYS.RLM$RSPRIMEVENTS
. process 31 scanning EXFSYS.RLM$VALIDPRIVS
. process 10 scanning EXFSYS.RLM$DMLEVTTRIGS
. process 60 scanning EXFSYS.RLM$COLLGRPBYSPEC
. process 32 scanning EXFSYS.RLM$PRIMEVTTYPEMAP
. process 5 scanning EXFSYS.RLM$EQUALSPEC

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

[oracle@redhat1 admin]$

Step 5: STartup database into restricted mode

SQL> SHUTDOWN IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP RESTRICT

ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 301990352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.

Step 6: Run the csalter.plb script

SQL> @/app/oracle/product/11.2.0/dbhome_1/RDBMS/ADMIN/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 validity…
begin converting system objects

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

Step 7: Restart the database to reflect new character

SQL> SHUTDOWN IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.

 
SQL> STARTUP

ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 301990352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.

Step 8: Check the current character set.

SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET';

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

SQL>

And it’s done :)

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Oracle 11g Enterprise Manager is not able to connect to the database instance.

When you try to open EM you may see following message "Enterprise Manager is not able to connect to the database instance. The state of the components are listed below."

Authentication passed but it does not work.

If problem on authentication check sysman user`s account status.

SQL> select account_status from dba_users where username='SYSMAN';

Also it will helpful checking emctl`s log which locates under:

/u01/app/oracle/product/11.2.0/dbhome_1/localhost.localdomain_ulfetdb/sysman/log

After checking logs I decided to recreate dbcontrol using below command.

It will ask some parameters (some of them optional, just enter and skip)
Also to be patient, it may last several minutes...

[oracle@localhost ~]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Dec 26, 2012 10:19:51 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: ulfetdb
Database Control is already configured for the database ulfetdb
You have chosen to configure Database Control for managing the database ulfetdb
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: yes
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/dbhome_1 ]: listener
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/dbhome_1

Local hostname ................ localhost.localdomain
Listener ORACLE_HOME ................ listener
Listener port number ................ 1521
Database SID ................ ulfetdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Dec 26, 2012 10:21:01 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/ulfetdb/emca_2012_12_26_10_19_50.log.
Dec 26, 2012 10:21:02 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Dec 26, 2012 10:21:25 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Dec 26, 2012 10:28:38 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Dec 26, 2012 10:28:39 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Dec 26, 2012 10:43:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Dec 26, 2012 10:43:30 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Dec 26, 2012 10:45:19 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Dec 26, 2012 10:45:20 AM oracle.sysman.emcp.ParamsManager getLocalListener
WARNING: Error retrieving listener for localhost.localdomain
Dec 26, 2012 10:45:30 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Dec 26, 2012 10:45:30 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Dec 26, 2012 10:46:14 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Dec 26, 2012 10:46:14 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Dec 26, 2012 10:46:52 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Dec 26, 2012 10:46:52 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Dec 26, 2012 10:48:42 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Dec 26, 2012 10:48:42 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://localhost.localdomain:1158/em <<<<<<<<<<<
Dec 26, 2012 10:48:49 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/dbhome_1/localhost.localdomain_ulfetdb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Dec 26, 2012 10:48:49 AM

Let`s check

Alternatively you can directly drop repository and then create it again using below commands.

emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!