1 2 Previous Next 19 Replies Latest reply: Mar 8, 2013 7:12 PM by Sergiusz Wolicki-Oracle RSS

    Change character set - problems

    642068
      Hi! I have Oracle 10 g on UNIX server!

      I changed characterset to:

      update SYS.props$
      set value$ = 'BLT8MSWIN1257'
      where name = 'NLS_CHARACTERSET'

      I shut down oracle instance and started it again and then I have problem. When I try to create table using SQLDEveloper then I have error: http://my.jetscreenshot.com/2677/20110914-wk6t-114kb.

      How to resolve my problem? How can I get back my pervious characterset and set to that I need? If You need more info then please let me know!

      Best regards, Debuger!

      Edited by: Debuger on Sep 14, 2011 7:11 AM
        • 1. Re: Change character set - problems
          Nadeem M
          Updating SYS.props$ for changing the characterset is not the right approach. You should never update props$ table (unless Oracle support suggests). In 10g you should use the csalter command to change the characterset. So not sure if this can be solved.

          Could you please provide the output of following query.

          select * from nls_database_parameters

          Also provide the following

          5 digit database version, OS and its version and last successful database startup entries for alert log.
          • 2. Re: Change character set - problems
            JustinCave
            1) That is very much not the way to change the database character set. If memory serves, it was valid back in the Oracle 7 days but it is highly unsupported today. You should never, ever update data dictionary tables unless specifically told to do that by Oracle Support. Do you have a backup of the database from before you made this change? If so, can you restore the database from this backup?

            2) There is a chapter in the Globalization Support Guide on changing the database character set. You really want to read through that carefully to understand how to change the database character set. Depending on the current character set, you may need to create a new database and do an export & import to move some or all of your tables.

            Justin
            • 3. Re: Change character set - problems
              Srini Chavali-Oracle
              Pl see MOS Doc 225912.1 (Changing the Database Character Set ( NLS_CHARACTERSET )). Hope you have a good backup of your database.

              Srini
              • 4. Re: Change character set - problems
                642068
                Hi, Nadeem! I will try to give all information You need to help:

                Database version: 10.2.0.1.0
                Server/OS: IBM AIX 5.3 Unix
                Allert log file is too long, I share it there: http://www.failiem.lv/down.php?i=gdrnhuq&n=ALERT_LOG_ENTRIES.txt

                PARAMETER VALUE
                ------------------------------ ----------------------------------------
                NLS_LANGUAGE AMERICAN
                NLS_TERRITORY AMERICA
                NLS_CURRENCY $
                NLS_ISO_CURRENCY AMERICA
                NLS_NUMERIC_CHARACTERS .,
                NLS_CHARACTERSET AL32UTF8
                NLS_CALENDAR GREGORIAN
                NLS_DATE_FORMAT DD-MON-RR
                NLS_DATE_LANGUAGE AMERICAN
                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_NCHAR_CHARACTERSET AL16UTF16
                NLS_RDBMS_VERSION 10.2.0.1.0

                The last NLS_CHARACTERSET AL32UTF8 is because I tried to get back and tried vary charactersets. I use characterset before on other machines and all was good. There was windows and do not have problems.

                Can You please give example how to use csalter command to set correct characterset to LATVIAN. And what I need to do to get my db back, if I have no backups?

                Please HELP me!

                Best regards, Debuger!
                • 5. Re: Change character set - problems
                  642068
                  The only way to get my db back is backup it? No other solutions?

                  Best regards, Debuger!
                  • 6. Re: Change character set - problems
                    Srini Chavali-Oracle
                    Updating SYS.prop$ is not supported and will corrupt your database. The only way to recover is from good backups.

                    Srini
                    • 7. Re: Change character set - problems
                      642068
                      If SYS.prop$ is not supported to update why there was no warrnigs when I done it? I need to recover full DB? And after that, how to change CHARACTERSET in correct way? Please give me an example.

                      Bestregards, Debuger!

                      Edited by: Debuger on Sep 14, 2011 8:55 AM
                      • 8. Re: Change character set - problems
                        Srini Chavali-Oracle
                        Pl see the MOS Document I posted above (and the link Justin also posted) on the needed steps to change the characterset of a database. What are the versions of the OS and database ? How big is the database ? The easiest method might be expdp/impdp.

                        HTH
                        Srini
                        • 9. Re: Change character set - problems
                          642068
                          Database version: 10.2.0.1.0
                          Server/OS: IBM AIX 5.3 Unix.

                          I need to do export of users, then reinstall Oracle DB and then do import, am I right? And can You please give me sql to change my characterset to latvian?

                          Best regards, Debuger!
                          • 10. Re: Change character set - problems
                            Sergiusz Wolicki-Oracle
                            If the only thing you messed up was changing the character set in SYS.PROPS$ , then you can update it back to the original value (what was it?), commit, and restart the database. This should make it consistent again. Then, use the provided support notes to do the conversion.


                            -- Sergiusz
                            • 11. Re: Change character set - problems
                              642068
                              Dear, Wolicki! How can I change it back? What I need to do?

                              Best regards, Debuger
                              • 12. Re: Change character set - problems
                                642068
                                How to know what was the orginal value? Where I can find it. I do not remember the orginal! :(

                                Best regards, Debuger!
                                • 13. Re: Change character set - problems
                                  Nadeem M
                                  Check the alert log entries of the instance before the change. During the startup you will find that charcterset of the database is logged in alert log as follows.
                                  Mon Sep 12 05:21:41 2011
                                  Database Characterset is WE8MSWIN1252
                                  • 14. Re: Change character set - problems
                                    Sergiusz Wolicki-Oracle
                                    Shut down the database and do a backup of whatever you currently have.

                                    Then, try this to learn the previous character set (connect as SYSDBA):
                                    SELECT DISTINCT NLS_CHARSET_NAME(charsetid) FROM sys.col$ WHERE type#=1 AND charsetform=1;
                                    Then, run the same update as previously, but with the retrieved original character set:
                                    UPDATE sys.props$ SET value$='<copy/paste result of the query here>' WHERE name='NLS_CHARACTERSET';
                                    Then, double-check that you pasted the value correctly, without any leading or trailing blanks, and issue COMMIT. Then, restart the database.

                                    Let me know what was the original character set.


                                    -- Sergiusz
                                    1 2 Previous Next