Introduction
Recently a request came in to investigate options to change the character set for a number of core databases. Goal would be to move from we8iso8859p15 to al32utf8. Below are some thoughts, things to remember if you are really heading in such direction.
A management summary of this article would be: Yes it is technically possible to move the character set of a database from we8iso8859p15 to al32utf8. But this can only be achieved after careful checking first and if checks show actions are needed then you will Need a team with application – datamodel knowledge look into the reports that will be generated during the inventory part of the scenario. With the knowledge gained from those reports they (application – datamodel) aware people will then have to fix issues if any in the Source database before running another analyses ( scan ) before doing a conversion. Oh and of course ( test test test test ) and have a backup in place before doing this on your live production boxes. Oracle software is trying to protect you by keeping a max of 7 days between analyses and conversion and of course that makes sense given the fact that the data of course will continue to grow during and after the first analyses.
Details
When setting up a new database there is of course a lot to be considered as in sizing of the database ( sizing during start and lets say after 12 Months ), the users and their privileges but there is one extra very important topic to think of well ( think of twice would even be better ) and that is the character set. Plain and simple if the Character-set is not chosen correctly there might be an issue converting the character set (if possible at all ) if you want to set it to the one desired at a later point and time. As a rule of thumb one could even say if the character set is wrong the data present in the database needs to be extracted (with tools like datapump), the database would need to be recreated in the new character-set after which the data can be added (inserted) again. Of course only to be done after testing such a scenario on a pre-production environment and only after careful analyses of the results in the reports to check the data before moving such scenario to the production environment.
There is one exception to the above scenario where you always will have to recreate the database to get to the desired (new) character-set. If the new character-set is a super set ( if it is holding the same characters ( and at the same location in the character table)) then a conversion will be possible. Of course still needed to do a proper analyses about the data present in the database before starting such conversion , plan a scenario in pre-production to do the conversion, do a lot of analyses of the results , run the same scenario ( run inventory do an impact analyses first on production, making a backup, do the conversion and do a lot of post checks ones that conversion has completed).
Before Oracle 12 two tools would need to be used to do the conversion. Csscan tool would do the inventory activity and present the results in three files about the consequences of such a conversion. If data would be lost you will have to manually fix such issues and rerun the csscan tool. Once that has been done the csalter tool would need to run in a restricted database ( only one instance up in case of a rac (all others need to be down) , only one session logged in as sys, only in a restricted database). Of course this will mean that the csalter tool will require a downtime of your environment .
In Oracle 12 csscan and csalter are no longer supported and you will have to look for the dmu tool.
Csscan and Csalter
Preparations:
### This Mos note has been used as a baseline for the csscan:
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) (Doc ID 745809.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)
Csscan Output Explained (Doc ID 444701.1)
As a best practice it was mentioned to alter the script after making a copy of it.
### In the existing f.e. 11G ORACLE_HOME: cp $ORACLE_HOME/rdbms/admin/csminst.sql $ORACLE_HOME/rdbms/admin/csminst.sql.old
### As best practice alter the script by altering the csmig user to an existing tablespace in the database with enough room. ( Hint: you might even consider to put the designated tablespace to autoextend till the analyses have completed).
alter user csmig default tablespace nomonitor quota unlimited on nomonitor;
### Then run the adapted script:
sqlplus /nolog conn / as sysdba set TERMOUT ON set ECHO ON spool csminst.log @?/rdbms/admin/csminst.sql
### Make sure you perform below changes to the user:
sqlplus /nolog conn / as sysdba; alter user csmig identified by ces10; alter user csmig account unlock ; grant create session to CSMIG; exit;
### All set for the analyses so on the Operating System level run:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH csscan SYSTEM FULL=y TOCHAR=AL32UTF8 ARRAY=2048000 PROCESS=32 CAPTURE=N LOG=$ORACLE_SID.log
### Important to know about csscan and csalter is that they are desupported in 12c.
For a scenario in 11G ( or before ) :
http://dbaonline.org/newsletter/Newsletter_200908.pdf
If your source database is big ( one of the environments where i had to run it was app 8TB). The csscan (11G env) will take time( in my case it ran for some 13 hours). If you like to keep an eye on it v$session_longops will be your friend:
sqlplus / as sysdba SET pages 1000 SELECT target, TO_CHAR(start_time,'HH24:MI:SS - DD-MM-YY'), time_remaining, sofar, totalwork, sid, serial#, opname FROM v$session_longops WHERE sid IN (SELECT sid FROM v$session WHERE upper(program) LIKE 'CSSCAN%') AND sofar < totalwork ORDER BY start_time /
DMU tool
### Dmu tool :
## Known issues with dmu:
### Requirements for DMU tool in Oracle (dmu can be used as of 11.2.0.3. and up)
### Needed to run this package in database you need 2 analyse.
@?/rdbms/admin/prvtdumi.plb
### Need to make sure you have a password file in place. First attempt failed, when trying to connect to the database in the dmu tool , kept getting invalid username or password … But after some time i realized that a password file might be mandatory. If a password file is not present this is the way to create password file :
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=manager entries=16 force=y
### If password file seems to be in place , check in the database with ( you should at least see sys out there then)
SQL> select * FROM v$pwfile_users;
### DMU tool will need a user with sysdba privileges .
### Before starting need to have set up environment.
export JAVA_HOME=$ORACLE_HOME/jdk $ORACLE_HOME/dmu/dmu.sh
As always, a pleasure to share thoughts and approaches. Happy reading,
Mathijs.
Mathijs Bruggink 