How to change the national character set (NLS_NCHAR_CHARACTERSET) of Oracle database to UTF8?
This tutorial contains information about the database character set and the national character set.The database character set and the national character set of an Oracle database are independent of each other. This tutorial describes how to change the national character set (NLS_NCHAR_CHARACTERSET) of your Oracle database to UTF8.
Prerequisite for changing the national character set
Oracle Releases 9.2.0, 10.1.0, 10.2.0, and 11.2.0.
Recommendation
We recommend that you change the national character set to UTF8 during a database upgrade regardless of whether or not an SAP J2EE system (Java stack) with Unicode data is installed in the database.
Setting the national character set
If you operate an SAP system with Java Stack (SAP J2EE server), you must ensure that the national character set of the Oracle database is correctly set.
This tutorial is relevant for the following installations:
For a retroactive SAP add-in installation of a SAP J2EE server on an existing SAP non-Unicode system (Add-In installation).
For an installation of an SAP J2EE server as a further MCOD component on a non-Unicode database of an existing SAP non-Unicode MCOD system.
(MCOD J2EE installation)
The role of the national character set in SAP systems.
In an SAP Unicode system, both the database character set and the national character set of the Oracle database are set to UTF8 and are therefore already set correctly for an add-in installation of an SAP J2EE server.
In (non-Unicode) SAP systems that were installed with Oracle 9i or lower, the national character set of the Oracle database is generally not set to UTF8, but either to AL16UTF16 (default on Oracle 9i) or to another value.
However, to install and run an SAP J2EE server, UTF8 is required as a national character set in the database. Before retroactively installing the SAP J2EE server on this type of SAP system, you must therefore use UTF8 as the national character set. The change procedure described in this note is supported from Oracle Release 9i (9.2.0).
How to save Unicode data in a non-Unicode system.
The SAP J2EE application (Java stack) saves its data in the database in the separate database schema SAP<SAPSID>DB, whereby NCHAR, NVARCHAR2, and NCLOB are used exclusively as data types for character data. These data types store their data in the national character set. The ABAP-based SAP application (ABAP stack) does not use these data types. This way, the data of the SAP J2EE application and the data of the ABAP SAP application are separated, not only by a separate database schema, but also by the use of different data types. With this architecture, it is possible to operate a Unicode-based SAP J2EE application (with a UTF8 character set) and a non-Unicode-based SAP application (with a WE8DEC character set) on the same database at the same time, without having to install a Unicode-only database.
To avoid misunderstandings, note the following important points once again:
- The terms 'national character set' and 'database character set' used in this note refer only to character sets in the Oracle database. They do NOT refer to character sets in the SAP application (SAP codepages). The circumstances described in this note do not require ANY changes to the SAP codepage.
- The procedure described in this note does NOT change the database character set.
- The environment variable NLS_LANG also remains unchanged if the national character set is changed to UTF8.
General information about the national character set
The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB. In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.
Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import). Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g).
Possible values for the national character set for Oracle 9i Release 2 are as follows:
- AL16UTF16 (Oracle default setting)
- UTF8 (defined SAP standard)
The national character set is therefore only intended and suitable for storing Unicode data.
A standard SAP system only uses table columns of the types CHAR, VARCHAR2 and CLOB (the national character set is therefore irrelevant). The SAP J2EE application server, on the other hand, stores Unicode data in table columns of the types NCHAR, NVARCHAR2 and NCLOB.
If an SAP J2EE server is subsequently installed in this type of standard SAP system (add-in installation or MCOD J2EE installation), you must ensure that the national character set is set to UTF8.
Generally, this does not apply to SAP systems that have been installed with Oracle 8i or lower, since with these, the national character set is set to the preset value AL16UTF16 as a result of the upgrade. In this case, the national character set must be changed to UTF8 as described below.
The national character set can only be changed if no data exists as yet in this character set (that is, in columns of the types NCHAR, NVARCHAR2, NCLOB). Accordingly, no application data is converted or changed in this case. Only the relevant setting in the Oracle Dictionary is modified. Therefore, you do not have to first perform a database export.
CAUTION: We recommend that you perform a full database backup before changing the national character set.
Preliminary check
You can use the following SQL command to determine the national character set that is currently set.
SQL> select property_value from database_properties
where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';
The following command is equivalent to this:
SQL> select value from v$nls_parameters
where upper(parameter) = 'NLS_NCHAR_CHARACTERSET';
If the result is "UTF8", the national character set is already correctly set and you do not have to do anything else.
If not, continue as follows:
Determine whether the database contains NCHAR data:
SQL> select owner, table_name, column_name
from dba_tab_columns
where (data_type = 'NCHAR' or
data_type = 'NVARCHAR2' or
data_type = 'NCLOB') and
owner != 'SYS' and
owner != 'SYSTEM';
(NCHAR data in the Oracle Dictionary, that is owner = SYS or owner = SYSTEM, is not important and is therefore disregarded.)
If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data. This should not be the case in a standard SAP system. If the NCHAR data type columns contain data, we recommend that you contact SAP Support.
Changing the national character set
You require SYSDBA authorization to change the national character set.
Changing the national character set means changing an Oracle Dictionary entry, but no data is changed. Therefore, the process is very fast.
Procedure:
$sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE = MEMORY;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE = MEMORY;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
Note: The two database parameters JOB_QUEUE_PROCESSES and AQ_TM_PROCESSES should not be set in SPFILE (see Note 830576), therefore SCOPE=MEMORY. If they were inadvertently set in SPFILE, you can delete them from SPFILE again using:
SQL> ALTER SYSTEM RESET JOB_QUEUE_PROCESSES SCOPE=SPFILE SID='*';
SQL> ALTER SYSTEM RESET AQ_TM_PROCESSES SCOPE=SPFILE SID='*';
Otherwise, the system will display the following warning in the alert log:
"WARNING: aq_tm_processes is set to 0. System operation might be adversely affected."
If the system issues the message:
ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER SET
when NCLOB data exists, this means that there is still some data in the NCLOB, NCHAR or NVARCHAR2 columns. In this case, in the message for this error in the alert log you will find details on the table/column that caused this message.
Repeating the J2EE installation with a corrected national character set
After a termination of the J2EE deployment caused by the wrong national character set, proceed as follows:
1. Save the database
Perform a full database backup.
2. Delete the J2EE schema
Manually delete the relevant J2EE schema from the database. However, you should execute this step only if the schema was created exclusively for use in the J2EE environment and does not contain any other relevant data. Otherwise, contact SAP Support by opening a customer message with the BC-DB-ORA-SYS component.
3. Changing the national character set
Change the national character set from UTF16 to UTF8 in accordance with the procedure described above. If you encounter any problems, contact SAP Support under the component BC-DB-ORA.
4. Restart the installation.
Restart the J2EE installation after you delete the schema. The installed components do not have to be uninstalled.