This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Mar 8, 2013 5:12 PM by Sergiusz Wolicki (Oracle) RSS

Change character set - problems

642068 Newbie
Currently Being Moderated
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
    881508 Journeyer
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Dear, Wolicki! How can I change it back? What I need to do?

    Best regards, Debuger
  • 12. Re: Change character set - problems
    642068 Newbie
    Currently Being Moderated
    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
    881508 Journeyer
    Currently Being Moderated
    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) Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points