Operating System - HP-UX
1748107 Members
4929 Online
108758 Solutions
New Discussion юеВ

Re: 2 Ora Instances with Different CharacterSets?

 
SOLVED
Go to solution

2 Ora Instances with Different CharacterSets?

Hello,

I know its possible on ORacle to have multiple instanaces running. But what i would like to know is that, is it possible for these different instances to have different charactersets?.

As a limitation of hardware i need to install 2 products with different charactersets using a single oracle installation. So was wondering if this is possible and more so supported. If yes then could you let me know the steps or probably a doc explaining the steps.

Thanks and Regards
Sumedha
10 REPLIES 10
Indira Aramandla
Honored Contributor

Re: 2 Ora Instances with Different CharacterSets?

Hi Sumedha,

Database character set and how is it set :-

The database character set is specified when the database is created and is stored in the data dictionary table, sys.props$. It is used to determine which characters can be stored in the database. It is also used to determine the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

Client character set how is it set?

The client character set used for Oracle should be equivalent to the character set supported for your terminal. This character set should also be equivalent to or a subset of the character set used for your database so that every character input through the terminal has a matching character to map to in the database.

It is set by the environment variable NLS_LANG. This has the format:
LANGUAGE_TERRITORY.CHARACTERSET and is set as a local environment variable in Unix

From Oracle 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values:
UTF8 or AL16UTF16.

Indira A
Never give up, Keep Trying

Re: 2 Ora Instances with Different CharacterSets?

Hello Indira,

So do you think the Database character set can be different for the 2 instances if the ORACLE_HOME value is the same for both instances.

Thanks
Sumedha
Indira Aramandla
Honored Contributor

Re: 2 Ora Instances with Different CharacterSets?

Hi Sumedha,

Yes you can have different character sets for different databases as it is specified when the database is created and is stored in the data dictionary table of the database.



Indira A
Never give up, Keep Trying

Re: 2 Ora Instances with Different CharacterSets?

Hi,

So i guess its just to add the 2 Charactersets details in the data dictionary table, sys.props$. to use different sets? Am i right here?.

Regards,
Sumedha
Indira Aramandla
Honored Contributor
Solution

Re: 2 Ora Instances with Different CharacterSets?

Hi Sumedha,

I ment that there can co-exist two databases with different charactersets on the same server.

Characterset for a database is defined when the database is initially created. i.e if you are using the dbassist to create the database it will prompt you to select the characterset. or if you are using the script to create the database you specify the characterset.
eg: -
CREATE DATABASE "rprd"
maxdatafiles 2048
maxinstances 8
maxlogfiles 64
character set US7ASCII
national character set US7ASCII

After this when the database is created this characterset is stored in the data dictionary table, sys.props$.

For an existing database changing character set takes a very long time and uses lots of rollback space. Changing the character set is done by either export / import or by using the Alter database character set commnad. Remember that changing the database character set only changes the way characters are stored on the database. This should NOT have any effect on the clients and you should not have to change the NLS_LANG for example. The NLS_LANG on the clients should simply reflect the setup of that machine.

I hope this is clear now.


Indira A
Never give up, Keep Trying
Indira Aramandla
Honored Contributor

Re: 2 Ora Instances with Different CharacterSets?

Sumedha,

Attached is a document about changing character set for an existing database from metalink.


Indira A
Never give up, Keep Trying

Re: 2 Ora Instances with Different CharacterSets?

Hello Indira,

Yes i get it now. Thx for the descriptive explantion. Appreciate it.

Regards
Sumedha
Jean-Luc Oudart
Honored Contributor

Re: 2 Ora Instances with Different CharacterSets?

Sumedha,

to the excellent post from Indira I will add this document.

Regards
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: 2 Ora Instances with Different CharacterSets?

I also like to point to these Metlink documents :

Note:227330.1 Character Sets & Conversion - Frequently Asked Questions

Note:60134.1 Globalization (NLS) - Frequently Asked Questions

Regards
Jean-Luc
fiat lux