ORA_NLS (ORA_NLS33, ORA_NLS10) Environment Variables explained

ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained. [ID 77442.1]
		Modified 30-NOV-2010     Type BULLETIN     Status PUBLISHED

Applies to:

Oracle Server - Enterprise Edition - Version: 7.3.4.5 to 11.2.0.2 - Release: 7.3.4 to 11.2
Information in this document applies to any platform.

Purpose

To provide information on the environment variables ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) .

Scope and Application

Anyone configuring an Oracle RDBMS system.

ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.

When seeing ORA_NLSxx in this document it should be replaced with your version variable name.

For RDBMS 7.2.x version the variable is called ORA_NLS.
For version 7.3.x the variable is called ORA_NLS32.
For Oracle 8, 8i and 9 variable is called ORA_NLS33.
For Oracle 10g and11g the variable is called ORA_NLS10.

 

1) What is the use of this ORA_NLSxx parameter?

ORA_NLSxx is used to indicate where Oracle RDBMS/client software can locate the defintions of Charactersets (used in NLS_LANG or as NLS_CHARACTERSET/NLS_NCHAR_CHARACTERSET), NLS_SORT, NLS_LANGUAGE (or derived/related parameters) or NLS_TERRITORY (or derived/related parameters).
Those definitions are stored in .nlb files who can be found in the ORA_NLSxx directory.

For more information on how NLS parameters are set/derived please see The Priority of NLS Parameters Explained.

2) What is the correct setting for a certain Oracle RDBMS version?

If you are using version 7 then you *must* set the environment variable ORA_NLS32 to:

For recent versions (9i, 10g, 11g..) the software uses a default value when ORA_NLSxx is not defined.

Hence , when using 9i or above there is no need to explicit define the ORA_NLSxx parameter.
When not defined the software will simply use the default value, and this works fine.

If you do want to define this explicit then :
* when using a Oracle8, 8i or 9 version set the ORA_NLS33 environment variable to:

* when using a Oracle 10g or 11g version set the ORA_NLS10 environment variable to:

 

Explicitly defining ORA_NLSxx in 9i and above is only needed when using an non default directory location for the nls files.
This is mainly when using a custom characterset, nls_language , nls_sort or nls_territory definitions

Using ORA_NLSxx files from a different patch version is not supported in versions lower then 10g (= use the files that came with your $ORACLE_HOME).
In 10g and up we suggest to use nlb files of the same version whenever possible.
Using ORA_NLSxx files from a different base release version is not supported any version.

3) When creating a database:

For Oracle versions 8i and lower

When you try to create a database without setting ORA_NLSxx parameter, with a incorrect location or the files are not accessible to the OS user you will have the error:

If you are creating database upon installation of Oracle software, you need to add ORA_NLSxx in the .env file of the oracle software owner.

In 9i and above this most of the time indicates you have set ORA_NLSxx to a incorrect location or the files are not accessible to the OS user.
In 9i and above,there is no need to set ORA_NLSxx , unless there is need for an custom characterset, nls_language , nls_sort or nls_territory definition

The same error may also indicate other issues, please have a look at ORA-12701 When Creating a Database if you run into this error.

4) When the database already exists:

When database is already created and Oracle Net connections need to be established, ORA_NLSxx is used by the listener and client software to acces the nlb file to define date/time format masks and perform. characterset conversions.

For 8i and lower make sure that in the client's , database and listener *starting* (!) environment the ORA_NLSxx is set.
For 9i and above , or do not set this so the default is used, or when explicit defining the ORA_NLSxx make sure it's set to the correct location.
If you are using a client or starting a database/listener Oracle7 version, set the environment variable:

If you are using a client or starting a database/listener Oracle8, 8i or 9 version, set the environment variable:

If you are using a client or starting a database/listener Oracle10G version, set the environment variable:

 

 

5) Errors seen when ORA_NLSxx was not set correctly in the OS user environment when the listener was started:

Please note that we don't advise to use a version 9 listener for a version 8 database or a version 10 listener for a version 9 or 8 database seen there where many changes to the NLS layers between those versions.
Even patchset differences should be avoided when possible.

If ORA_NLSxx is not (8i and lower) or wrongly set or the nlb files can't be read by the OS user starting the listener you will typically see that a connection with NLS_LANG *not* set or set to AMERICAN_AMERICA.US7ASCII works.
But a connection with other territory and language like DUTCH_BELGIAN.WE8MSWIN1252 fails with ORA-12705 that is combined with ORA-604.
You will see this also when using a 817 listener for a 9i database.
A ORA-12705 combined with ORA-604 always points to a server problem, database and/or listener started with incorrect ORA_NLSxx definition or OS permission problems.

The solution is to set ORA_NLSxx correctly , check file/directory permissions and restart the listener (also the service on windows).
We recommend to use a 8i listener for a 8i db, a 9i listener for a 9i db etc...

If you really want to use a 9i listener for a 8i instance then define the correct ORA_NLS33 in the listener.ora like this:

Make sure you disable "automatic listener registration" for the 8i database if you use 1521 as listener port.

8i init.ora

where 1599 is a port number NOT used by the 9i listener, so that the 8i db is NOT able to find the 9i listener (this has no performance impact).
Disabling Automatic Registration of the Database with the Default Listener

However then do not forget to start the 9i listener with the 9i settings

 

6) Errors seen when ORA_NLSxx is not set correctly in the OS user environment when *starting* the database:

If ORA_NLSxx is not set (8i and lower) , set to an incorrect directory or the directory can't be read during the database startup the database will default and character set will not be recognized.
Normally the startup of the database itself will yield a error, this may not always be the case however.

An ORA-12705 combined with ORA-604 always points to a server problem like database and/or listener started with incorrect ORA_NLSxx definition or OS permission problems for the starting OS user.

This can result in:

a. Character set translation not occurring for clients using a character set different from the database's. This may result in corrupt data in certain applications as data may be lost and misinterpreted.

b. ORA-12705 (may be combined with ORA-604 errors).

c. "SQL*Loader-266: Unable to locate character set" error.

d. Messages being displayed in a language different from that expected.

e. ORA-3106 fatal two-task communication protocol error. ORA-600[12333]  which will have the same meaning as ORA-3106 in this case. At the client you may see a TNS-12571 or ORA-12571.

You can check which NLS setting are valid for this platform. by connecting to the database as a DBA and issuing the following command:

If this only returns US7ASCII then ORA_NLSxx was INCORRECTLY set during database startup.

To find out what character set you have, issue the following command connected as sys, system or DBA:

Look for the NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET (from version 8 onwards) parameter.

While ALL oracle provided characterset, nls_language , nls_sort or nls_territory definitions are ALWAYS installed, you may also want to check that the nlb files are there, and accessible on your system. To do that go to directory:

or from 10g onwards:

and issue the following command:

where we8mswin1252 is the NLS_CHARACTERSET used.

The solution is to set ORA_NLSxx correctly, check file/directory permissions and restart the instance (also the service on windows).

6) Errors seen when ORA_NLSxx is not set correctly in the CLIENT environment:

If you have only a ORA-12705 alone then the problem is purely on the client side.
ORA_NLSxx is incorrect defined or the NLS_LANG variable on the client side is not correct.
For example NLS_LANG=WE8MSWIN1252 (NLS_LANG=.WE8MSWIN1252 or NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 is correct)
-> see The Priority of NLS Parameters Explained.

Solution:

Unix: make sure you use the correct syntax for NLS env variables, remove them for testing if needed.
We DO recommend to set always ORA_NLSxx (espacially for 8i and lower), ORACLE_HOME and NLS_LANG correctly in a unix shell env.

Windows: make sure you use the correct syntax for NLS env variables, remove them for testing if needed.

On WINDOWS platforms there is normally no need to define ORA_NLSxx, they are set in the registry during installtion by the installer.

If you want however you can define this in the correct HOMEx entry in the registry,

The default directory for windows is

See <<73963.1>> Using multiple ORACLE HOMES on Windows platform.

You can however set it locally in a CMD/dos box session and start the executable from that prompt to test.

Generic: Do not forget to check for file/directory permission problems.

7) Other things to know about ORA_NLSxx / nlb files:

* In some circumstances you will see that Oracle recommends to set ORA_NLS10 to $ORACLE_HOME/nls/data/9idata instead of $ORACLE_HOME/nls/data
The why is documented in Language and Territory definitions changed in 10g vs 9i and lower

Please note that unless you have a good reason, we strongly recommend to have ORA_NLS10 set to $ORACLE_HOME/nls/data or not set at all.
Using $ORACLE_HOME/nls/data/9idata must be considered as a temporary workaround.

* In 9i and lower nlb files cannot be used on a OTHER platform. or a other version.

* From 10g onwards a *complete* set of nlb files is platform. generic.
This is only relevant when making/using customer charactersets, sorts, language or territory definitions using Locale Builder.
Locale Builder - Frequently Asked Questions

This means that you cannot generate and copy on (or a few) nlb files on one platform. and then copy them into a ORA_NLS10 directory on a other platform,
but you *can* generate nlb files on one platform. and then use a *complete* copy of the updated ORA_NLS10 directory on any other platform.
Note that there may (depending on used source/target platform) additional performance overhead (byte-swapping/compiler characterset conversion) incurred for general usage when deployed on a different platform.
So it's still beter to generate any custom definitions on each platform. if possible.
When using a custom, user defined _M sort please be aware of Custom multi-lingual sort [*_M sort] gives ORA-7445 or ORA-600

8) When Oracle Developer is installed:

Note1: ORA_NLSxx does not need to be set for Developer 6.0.

For Oracle version 8.x, if we connect from the client and use Developer version 1.6.1 then ORA_NLS33 needs to be set:

For other versions it should point to:

Note2: $ORACLE_HOME/ocommon/nls/admin/data2k subdirectory is created by Developer version 1.6.1 only. This is caused by the fact that this version of Developer uses two versions of NLSRTL library at once: version 3.2 for Oracle common RDBMS libraries (known as RSF on Windows) and version 3.3
for Forms processing. As these two NLSRTL versions needs different *.nlb files, two data directories are created: 'data' for 3.2 and 'datad2k' for 3.3.

NLSRTL 3.2 files are pointed to by the ORA_NLS32 variable, which defaults from ORACLE_HOME. NLSRTL 3.3 files are pointed by the ORA_NLS33 variable, which must be set explicitly to $ORACLE_HOME/ocommon/nls/admin/datad2k.

Note3:  When exporting from the Developer 1.6.1 environment, you might be getting an ORA-600 [23] [] error. If this is the case, try setting ORA_NLS33 to

instead of

This should resolve the internal ORA-600 [23][].

9) what is OI_NLS32?

OI_NLS32 is a parameter used by the Oracle Universal Installer and should NOT be altered.

10) What about using Instant Client ?

This is documented in the documentation set