How to check Globalization Supports for Oracle Database on UIM server
Article ID: 131441
Updated On:
Products
DX Unified Infrastructure Management (Nimsoft / UIM)
Issue/Introduction
This kb article describes how to check Oracle Globalization Supports on UIM server
Environment
UIM 20.x
Resolution
-- To check the NLS settings.
Run below query in a sqlplus session on UIM server
COL "PARAMETER" format a20
COL "SESSION" format a15
COL "DATABASE" format a15
COL "INSTANCE" format a15
SELECT * FROM
(SELECT 'SESSION' SCOPE,nsp.* FROM nls_session_parameters nsp
UNION
SELECT 'DATABASE' SCOPE,ndp.* FROM nls_database_parameters ndp
UNION
SELECT 'INSTANCE' SCOPE,nip.* FROM nls_instance_parameters nip
) a
PIVOT (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
IN ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));
This returns the NLS parameter values at the session, instance and DB level. Some of the UIM components may modify NLS parameter values using a logon trigger but won't modify all the values.
The NLS_CHARACTER_SET value at the Database level is the default DB character set specified when creating the database and this cannot be overridden by any means in a client session.
UIM components should not have an assumption on the character set used. However, after installing Oracle client and before installing UIM, make sure to specify a compatible character set that allows for conversion like ZHS16GBK (Simplified Chinese) with AL32UTF8 if server and client are in different character sets. Make the same language on both ends makes Oracle transfer and receive data blindly without any conversion.
-- To determine the character set.
The NLS_LANG environment variable controls default date, monetary, and numeric format and determines the language used for Oracle messages, sorting, day names, and month names if these are not set in the session. It also specifies the character set used by the client application so that Oracle can do the proper conversion.
1. Check existing NLS_LANG setting
Check NLS_LANG on the Linux platform:
SQL> HOST ECHO $NLS_LANG
On the Windows platform:
SQL> HOST ECHO %NLS_LANG%
It is possible that NLS_LANG is set in Windows registry by Client installer according to types of installation:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name> for Oracle Client installation
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE for Oracle Instant Client installation
If the result contains %NLS_LANG%, this indicates that NLS_LANG is not set on the UIM server. This is possible like for example Oracle instant client basic is just unzipped.
2. If otherwise NLS_LANG not set
The character set is US7ASCII.
3. The NLS_LANG setting should reflect the code page/locale of the client
This should be done by the Oracle Client Installer, for example, character set 'WE8MSWIN1252' is for locale uses Western European language, character set 'ZHS16GBK ' is for Simplified Chinese.
Windows code pages can be found in the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage. Default code page can be found in value 'ACP' and 'OEMCP', which are determined by System Locale.
Locale information of a Unix/Linux server can be found using:
#locale -a
The current local on the server can be found:
#locale
If you plan to set a non-default character set, consult local DBA first.
Run below query in a sqlplus session on UIM server
COL "PARAMETER" format a20
COL "SESSION" format a15
COL "DATABASE" format a15
COL "INSTANCE" format a15
SELECT * FROM
(SELECT 'SESSION' SCOPE,nsp.* FROM nls_session_parameters nsp
UNION
SELECT 'DATABASE' SCOPE,ndp.* FROM nls_database_parameters ndp
UNION
SELECT 'INSTANCE' SCOPE,nip.* FROM nls_instance_parameters nip
) a
PIVOT (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
IN ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));
This returns the NLS parameter values at the session, instance and DB level. Some of the UIM components may modify NLS parameter values using a logon trigger but won't modify all the values.
The NLS_CHARACTER_SET value at the Database level is the default DB character set specified when creating the database and this cannot be overridden by any means in a client session.
UIM components should not have an assumption on the character set used. However, after installing Oracle client and before installing UIM, make sure to specify a compatible character set that allows for conversion like ZHS16GBK (Simplified Chinese) with AL32UTF8 if server and client are in different character sets. Make the same language on both ends makes Oracle transfer and receive data blindly without any conversion.
-- To determine the character set.
The NLS_LANG environment variable controls default date, monetary, and numeric format and determines the language used for Oracle messages, sorting, day names, and month names if these are not set in the session. It also specifies the character set used by the client application so that Oracle can do the proper conversion.
1. Check existing NLS_LANG setting
Check NLS_LANG on the Linux platform:
SQL> HOST ECHO $NLS_LANG
On the Windows platform:
SQL> HOST ECHO %NLS_LANG%
It is possible that NLS_LANG is set in Windows registry by Client installer according to types of installation:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<oracle_home_name> for Oracle Client installation
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE for Oracle Instant Client installation
If the result contains %NLS_LANG%, this indicates that NLS_LANG is not set on the UIM server. This is possible like for example Oracle instant client basic is just unzipped.
2. If otherwise NLS_LANG not set
The character set is US7ASCII.
3. The NLS_LANG setting should reflect the code page/locale of the client
This should be done by the Oracle Client Installer, for example, character set 'WE8MSWIN1252' is for locale uses Western European language, character set 'ZHS16GBK ' is for Simplified Chinese.
Windows code pages can be found in the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NLS\CodePage. Default code page can be found in value 'ACP' and 'OEMCP', which are determined by System Locale.
Locale information of a Unix/Linux server can be found using:
#locale -a
The current local on the server can be found:
#locale
If you plan to set a non-default character set, consult local DBA first.
Additional Information
NLS parameter priorities (high to low):
1 Explicitly set in SQL functions
2 Set by an ALTER SESSION statement
3 Set as an environment variable
4 Specified in the initialization parameter file
For more information on NLS parameters: Setting Up a Globalization Support Environment
Windows code page information: Code Page Identifiers
1 Explicitly set in SQL functions
2 Set by an ALTER SESSION statement
3 Set as an environment variable
4 Specified in the initialization parameter file
For more information on NLS parameters: Setting Up a Globalization Support Environment
Windows code page information: Code Page Identifiers
Feedback
Yes
No
Powered by
