Tags

A customer recently attempted to change the characterset of their Oracle RAC database on their own, after receiving some questionable advice, and ended up somehow deleting the database from their cluster. While working on recovering the database, it occurred to me that to most DBAs, working with charactersets and ‘NLS_’ parameters is pretty much a dry subject, which they would prefer to not do.
Prior versions of the Oracle database made it mandatory to re-create the database if there were any changes required to the default characterset. But we have (since at least Oracle 9i) the CSSCAN utility that will help us (in many situations) make this transition. If you’re like me, the first thing you would think of when someone talks of an utility/tool to administer the database is ‘That’s nice, but does it work with RAC?’
So for our test environment, we will be using a 10g Release 2 RAC database (with 2 nodes) running on Oracle Linux 5, with ASM for the storage. Let’s start by looking at the current settings for the characterset.
From the alert log:
Thu Jan 28 20:07:10 2010
Database Characterset is AL32UTF8
Opening with internal Resource Manager plan

So now we know what is being used, lets change the characterset to WE8ISO8859P1. Before beginning, make sure:
i. There is a full backup of the database taken available
ii. There is a backup of the current spfile (especially for RAC databases)

Now we can start the CSSCAN utility, and start scanning the database. From the command line, run the following to start it as the SYS user, and specify a full database scan, with the intended characterset(WE8ISO8859P1). Additional options available can be seen by running ‘csscan –help’

If this is the first time you are running the csscan utility, you may see the following error:

CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.

In order to resolve this, you need to run the csminst.sql script (located in $ORACLE_HOME/rdbms/admin) as the SYS user, which will create the objects required by CSSCAN.

$ csscan \”sys/syspassword as sysdba\” full=y tochar= WE8ISO8859P1

You will then be able to specify a size for the array fetch buffer size (in bytes), as well as the degree of parallelism for the operation. Depending on the resources available on the server, higher values could be used for your setup.

Sample output:
Character Set Scanner v2.1 : Release 10.2.0.3.0 – Production on Thu Jan 28 11:27:56 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 4
Enumerating tables to scan…
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.TYPE$[AAAAC1AABAAAAURAAA]
. process 4 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
. process 3 scanning SYS.METHOD$[AAAAC1AABAAAAURAAA]
——– some output truncated for brevity
Creating Database Scan Summary Report…
Creating Individual Exception Report…
Scanner terminated successfully.

Once the CSSCAN utility has completed, you can review the results in the current directory (scan.txt file), and more importantly any exceptions that might have occurred (scan.err file). Some of the useful information found in these reports include:
i. Any data truncation that might occur, on application data as well as the data dictionary
ii. Loss of data on both application tables and the data dictionary
iii. Indexes that need to be rebuilt after the characterset conversion
iv. Rows in tables that contain data that will be affected by the conversion

By reviewing these files, you will be able to better decide on whether or not to proceed with the method outlined here. If there are a significant number of objects that will be affected negatively, this approach is not advisable. Luckily, in my environment there was no significant data loss or truncation, so it’s safe to proceed.

The next part will involve using the CSALTER.PLB package (again, found in $ORACLE_HOME/rdbms/admin) to actually carry out the alteration of the characterset. The intended characterset will be the same as used when generating the CSSCAN report earlier. Also, it’s worth noting that CSALTER will need to be run within 7 days of running CSSCAN, or the results will not be valid, and CSSCAN will have to be re-run.
* For RAC databases only – The instance must not be started in clustered mode, and all other RAC instances must be shutdown when running the CSALTER package.

First, ensure the ‘job_queue_processes’ and ‘aq_tm_processes’ parameters are both set to 0, to avoid any scheduled jobs running during the characterset migration.

Next, startup the database in restricted mode:
SQL> shutdown immediate;
SQL> startup restrict

Now, we can run the CSALTER package itself:
SQL> ?/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 validility…
begin converting system objects
1 row in table SYS.SQL$TEXT is converted
2 rows in table SYS.WRI$_ADV_OBJECTS are converted
38 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
6395 rows in table SYS.WRH$_SQL_PLAN are converted
19 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
935 rows in table SYS.WRH$_SQLTEXT are converted
80 rows in table SYS.METASTYLESHEET are converted
86 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
1 row in table SYS.RULE$ is converted
PL/SQL procedure successfully completed.
Alter the database character set…
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

Once the procedure completes, you can now restart the database, and verify if the characterset changes have been applied. Additionally, you can revert the ‘job_queue_processes’ and ‘aq_tm_processes’ initialization parameters to their original values.
* For RAC database only, you will also need to change the CLUSTER_DATABASE parameter again to enable all the RAC instances to be started.

Depending on what changes you have made (default database characterset, or NLS characterset) you will be able to verify the current database characterset from either the initialization parameters or the alert log. In this case, from the alert log:

Thu Jan 28 12:21:04 2010
Database Characterset is WE8ISO8859P1

By Niradj Selvam,
Oracle 10g, 11g OCP, 10g RAC Expert