How to check Globalization Supports for Oracle Database on UIM server

book

Article ID: 131441

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

This kb article describes how to check Oracle Globalization Supports on UIM server






 

Environment

Release:
Component: UIMUMP

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.

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