ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转载:ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) [ID 77442.1]

转载:ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) [ID 77442.1]

Linux操作系统 作者:burphy 时间:2016-01-29 14:39:13 0 删除 编辑
转载:ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained. [ID 77442.1]
http://space.itpub.net/35489/spacelist-blog

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.

Please be aware that your ORACLE_HOME variable should *also* be set correctly.


 

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 seeNote 241047.1The 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:

ORA_NLS32 = $ORACLE_HOME/ocommon/nls/admin/data


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

for ORA_NLS33 this is $ORACLE_HOME/ocommon/nls/admin/data
for ORA_NLS10 this is $ORACLE_HOME/nls/data


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:

ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data

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

ORA_NLS10 = $ORACLE_HOME/nls/data


 

There is no need to define ORA_NLS32 or ORA_NLS when using oracle 8 or higher.
There is no need to define ORA_NLS33, ORA_NLS32 or ORA_NLS when using oracle 10g or higher.


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:

ORA-12701: "CREATE DATABASE character set is not known"

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 atNote:1058400.6ORA-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:

ORA_NLS32 = $ORACLE_HOME/ocommon/nls/admin/data


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

ORA_NLS33 = $ORACLE_HOME/ocommon/nls/admin/data


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

ORA_NLS10 = $ORACLE_HOME/nls/data



 

There is no need to define ORA_NLS32 or ORA_NLS when using oracle 8 or higher.
There is no need to define ORA_NLS33, ORA_NLS32 or ORA_NLS when using oracle 10g or higher.


 

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 wit 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:

(SID_DESC =
(GLOBAL_DBNAME = o8i.oracle.com)
(ORACLE_HOME = /disk1/oracle8i)

(ENVS='ORACLE_HOME=/disk1/oracle8i,ORA_NLS33=%ORACLE_HOME%/ocommon/nls/admin/data,LD_LIBRARY_PATH=/disk1/oracle8i/lib')
(SID_NAME = o8i)
)


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

8i init.ora

local_listener="(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname) (PORT=1599)))"


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).
Note:130574.1Disabling Automatic Registration of the Database with the Default Listener

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

ORACLE_HOME=/disk1/oracle9i
ORA_NLS33=%ORACLE_HOME%/ocommon/nls/admin/data


 

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:

SELECT * FROM V$NLS_VALID_VALUES;


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:

SELECT * FROM NLS_DATABASE_PARAMETERS;


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:

$ORACLE_HOME/ocommon/nls/admin/data

or from 10g onwards:

$ORACLE_HOME/nls/data

and issue the following command:

strings * | grep -i we8mswin1252


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)
-> seeNote:241047.1The 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,

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE for version 7
or
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx for version 8 and 9
or
HKEY_LOCAL_MACHINE\SOFTWARE\KEY_home_name for version 10 and up


The default directory for windows is


for 8.0: ORA_NLS33=%ORACLE_HOME%\NLSRTL33\DATA

for 8i and 9i: ORA_NLS33=%ORACLE_HOME%\ocommon\nls\ADMIN\DATA

for 10g: ORA_NLS10=%ORACLE_HOME%\nls\DATA


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

Please do NOT define ORA_NLSxx as (system) ENVIRONMENT variable on windows.
Please do NOT define ORACLE_HOME and/or ORACLE_BASE as (system) ENVIRONMENT variable on windows as this will invalidate the working of ORA_NLSxx.


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 inNote:292942.1Language 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.
Note:227339.1Locale 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 ofNote:603260.1Custom 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:

$ORACLE_HOME/ocommon/nls/admin/datad2k

For other versions it should point to:

$ORACLE_HOME/ocommon/nls/admin


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

$ORACLE_HOME/ocommon/nls/admin/data

instead of

$ORACLE_HOME/ocommon/nls/admin/datad2k

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 documentedin the documentation set

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25542870/viewspace-1983969/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-06-08

  • 博文量
    98
  • 访问量
    137296