4 Replies Latest reply: Nov 16, 2012 1:55 AM by moslee RSS

    Character Sets during upgrade from 9i to 11g

    moslee
      Scenario
      =====
      Oracle 9i Enterprise Edition (9.2.0.8)
      Windows Server 2003 (32 bit)
      --- to ---
      Oracle 11g Enterprise Edition (11.2.0.3)
      Windows Server 2008 Standard R2 (64 bit)



      Hi Experts

      I am doing an upgrade from 9i (32bit) to 11g (64bit) and I was trying to understand the Character Sets section from the following Oracle doc. http://docs.oracle.com/cd/E11882_01/server.112/e10897/install.htm#BABCABAC

      I did a SELECT * FROM NLS_DATABASE_PARAMETERS; Below is the result... I hope to keep my current character sets in the new 11gR2, will this be possible? Am I risking applications error if I change my character sets, especially from 32bit to 64bit? Thanks for all your inputs.



      PARAMETER VALUE
      ------------------------------ ----------------------------------------
      NLS_NCHAR_CHARACTERSET AL16UTF16
      NLS_CSMIG_SCHEMA_VERSION 2
      NLS_LANGUAGE AMERICAN
      NLS_TERRITORY AMERICA
      NLS_CURRENCY $
      NLS_ISO_CURRENCY AMERICA
      NLS_NUMERIC_CHARACTERS .,
      NLS_CHARACTERSET US7ASCII
      NLS_CALENDAR GREGORIAN
      NLS_DATE_FORMAT DD-MON-RR
      NLS_DATE_LANGUAGE AMERICAN

      PARAMETER VALUE
      ------------------------------ ----------------------------------------
      NLS_SORT BINARY
      NLS_TIME_FORMAT HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
      NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY $
      NLS_COMP BINARY
      NLS_LENGTH_SEMANTICS BYTE
      NLS_NCHAR_CONV_EXCP FALSE
      NLS_RDBMS_VERSION 9.2.0.8.0

      21 rows selected.

      Edited by: 940854 on Oct 29, 2012 11:36 PM
        • 1. Re: Character Sets during upgrade from 9i to 11g
          Srini Chavali-Oracle
          Pl indicate how you are planning to upgrade. If you going to directly upgrade in-place using DBUA or migration scripts, then the characterset will not change as part of the upgrade. The characterset can be changed if you are creating a new empty 11.2.0.3 database and then performing an upgrade via export/import.

          Oracle recommends using the AL32UTF8 characterset (http://docs.oracle.com/cd/E11882_01/server.112/e25494/create001.htm#ADMIN13343), but if your application does not expect to ever use any characters that are not included in US7ASCII, you can continue to use this characterset.

          HTH
          Srini
          • 2. Re: Character Sets during upgrade from 9i to 11g
            moslee
            Hi Srini

            Yes, I am creating a new empty 11.2.0.3 database and then performing an upgrade via export/import... Next as far as I know, my application team is supporting inhouse only and they are expecting low or zero impact to their inhouse apps when I do the upgrade/migration to new database... On top of that, they seems happy with what they are doing now and cannot be bother with CHARACTERSET or whatsoever. With this and your input, I will continue to use US7ASCII as my characterset.

            A trival question, may I know what NLS_CHARACTERSET US7ASCII and NLS_NCHAR_CHARACTERSET AL16UTF16 mean and in what way are they different?

            Thanks for your answer.. It helps..
            • 3. Re: Character Sets during upgrade from 9i to 11g
              Srini Chavali-Oracle
              Is there a reason you are not using DBUA for the upgrade ?

              Pl see these MOS Docs for an explanation of NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET

              Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]
              The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g [ID 276914.1]

              HTH
              Srini
              • 4. Re: Character Sets during upgrade from 9i to 11g
                moslee
                Hi Srini

                I'm not using DBUA because the new 11g will be on a separate new server.. Somewhere in Oracle doc, i saw:

                Databases must be on the same system (in-place upgrade where the new version of Oracle Database is installed on the same server as the existing version).


                Thanks for the MOS docs..