Multiple database on same machine - NLS_LANG problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Multiple database on same machine - NLS_LANG problem

  1. #1
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22

    Multiple database on same machine - NLS_LANG problem

    Hi,

    I have 2 databases on the same machine which has Oracle 10G 10.2.0.4 installed. Both databases have different NLS_LANG. Every time when I try to use one database the other one can't be used since NLS_LANG is not correct.

    Is there a way to specify NLS_LANG for each database in order to avoid changing this parameter in registry.

    Regards

  2. #2
    Join Date
    Dec 2001
    Posts
    320
    You use NLS_LANG to let Oracle know what characterset the OS is using. This is important so that Oracle would do characterset conversion from client characterset to the database characterset.

    The question here would be why do you have two different NLS_LANG setting on the same server?
    Anyone can hold the helm when the sea is calm.

  3. #3
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by hany View Post
    You use NLS_LANG to let Oracle know what characterset the OS is using. This is important so that Oracle would do characterset conversion from client characterset to the database characterset.

    The question here would be why do you have two different NLS_LANG setting on the same server?
    I have two databases that use different NLS_LANG and ceach time when I switch from one database to another I have to change NLS_LANG parameter in registry.

  4. #4
    Join Date
    Dec 2001
    Posts
    320
    Quote Originally Posted by crysu View Post
    I have two databases that use different NLS_LANG and ceach time when I switch from one database to another I have to change NLS_LANG parameter in registry.
    Please re-read my post. NLS_LANG tells Oracle what the OS characterset is. It's not the other way around, you do not specify the database characterset through NLS_LANG.

    You can't have two OS charactersets at the same time, can you?

    Please review Note 158577.1 on metalink. It explains NLS_LANG very well.
    Anyone can hold the helm when the sea is calm.

  5. #5
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by hany View Post
    Please re-read my post. NLS_LANG tells Oracle what the OS characterset is. It's not the other way around, you do not specify the database characterset through NLS_LANG.

    You can't have two OS charactersets at the same time, can you?

    Please review Note 158577.1 on metalink. It explains NLS_LANG very well.

    Seems that you didn't understood my problem (or I don't understand you). I have two databases on the same machine DB1 and DB2. When I use DB1 I have to set NLS_LANG to WE8MSWIN1252... when I use DB2 I have to set NLS_LANG to AL32UTF8... I wonder if there is a parameter to specify for each database what NLS_LAG to use... I understood from you that I can't have two NLS_LANG at the same time, but I wonder if there is another parameter that specify NLS_LANG for each database not for all.

    Regards

  6. #6
    Join Date
    May 2010
    Posts
    2
    When I use DB1 I have to set NLS_LANG to WE8MSWIN1252... when I use DB2 I have to set NLS_LANG to AL32UTF8...
    I think this is the point you have to clear... Why do you have to use different NLS_LANG settings for the different DBs? You should be using a unique NLS_LANG for both, depending on your operating system charset, and it should work fine.

    What happens if you use the same NLS_LANG setting for both, that makes you change in for each DB?

  7. #7
    Join Date
    Jan 2007
    Location
    Dayton, OH
    Posts
    15
    In unix, here is what gets loaded when I type in a SID:
    >DPMCDEV
    export ORACLE_HOME=/u16/oracle/product/10.2.0
    export ORACLE_BASE=/u16/oracle/product/10.2.0
    export LD_LIBRARY_PATH=/u16/oracle/product/10.2.0/lib
    export OB=/u16/oracle/product
    export OH=/u16/oracle/product/10.2.0
    export ORACLE_ADMIN=/u01/app/oracle/local/admin/DPMCDEV
    export OA=/u01/app/oracle/local/admin/DPMCDEV
    export ORACLE_SID=DPMCDEV
    export TNS_ADMIN=/u16/oracle/product/10.2.0/network/admin
    export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
    export ORA_NLS33=$ORACLE_HOME/nls/data
    export NLS_DATE_FORMAT=DD-MON-RR
    export NLS_DATE_LANGUAGE=AMERICAN
    export NLS_NUMERIC_CHARACTERS=".,"
    umask 022
    export SHLIB_PATH=$ORACLE_HOME/lib

    When I change db's it's unique settings are exported. You have to fully set the environment each time you change from one instance to another.

    Jim

  8. #8
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by jignacio View Post
    I think this is the point you have to clear... Why do you have to use different NLS_LANG settings for the different DBs? You should be using a unique NLS_LANG for both, depending on your operating system charset, and it should work fine.

    What happens if you use the same NLS_LANG setting for both, that makes you change in for each DB?
    it's not my decision, this is what client want and I can't do anything

    Quote Originally Posted by ridges54 View Post
    In unix, here is what gets loaded when I type in a SID:
    >DPMCDEV
    export ORACLE_HOME=/u16/oracle/product/10.2.0
    export ORACLE_BASE=/u16/oracle/product/10.2.0
    export LD_LIBRARY_PATH=/u16/oracle/product/10.2.0/lib
    export OB=/u16/oracle/product
    export OH=/u16/oracle/product/10.2.0
    export ORACLE_ADMIN=/u01/app/oracle/local/admin/DPMCDEV
    export OA=/u01/app/oracle/local/admin/DPMCDEV
    export ORACLE_SID=DPMCDEV
    export TNS_ADMIN=/u16/oracle/product/10.2.0/network/admin
    export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
    export ORA_NLS33=$ORACLE_HOME/nls/data
    export NLS_DATE_FORMAT=DD-MON-RR
    export NLS_DATE_LANGUAGE=AMERICAN
    export NLS_NUMERIC_CHARACTERS=".,"
    umask 022
    export SHLIB_PATH=$ORACLE_HOME/lib

    When I change db's it's unique settings are exported. You have to fully set the environment each time you change from one instance to another.

    Jim
    you say that every time when you change database NLS_LANG change automatically ?

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by crysu View Post
    you say that every time when you change database NLS_LANG change automatically ?
    No. Process that set environmentals when you change instances e.g. proper ORACLE_HOME, proper ORACLE_SID, etc - has to set ALL environmentals as they are needed to access targetted instance.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width