4 Replies Latest reply on Jan 9, 2019 4:51 AM by Sergiusz Wolicki-Oracle

    cannot execute command after changing NLS_CHARSET

    Damon K

      Hi all!

       

      This is my first posting since I registered oracle community.

       

      Actually, I am working as junior IT developer and therefore I  do not have lots of knowledge about IT technology.

       

      I faced serious problem by changing NLS_CHARSET on my development server.

       

      I was trying to preprocess dataset before doing analysis but I needed to change character set because of dataset's characteristic.

       

      So I changed NLS_CHARSET with updating sql query on server and restart related services to apply.

       

      Since then oracle server is not working. I tried to export dump file to protect data but not worked as well.

       

      In conclusion, Every commands are not executed so I cannot change NLS_CHARACTSET anymore.

       

      I googled to find out but I could not and the below list is what I did by googling.

       

           1. Re-start server and services - worked but not resolved

       

           2. unmount database - worked but not resolved

       

           3. recover database - not worked(ORA-00911: invalid character)

       

           4. open database - not worked(ORA-00911: invalid character)

       

           5. edit registry key - worked but not resolved

       

           6. Explore ORACLE_HOME directory to find config file which has NLS_CHARACTERSET information

       

      What can I do without re-install oracle server? Hopefully, I want to edit the config If config file is existed.

       

      If not existed, please let me know how to backup my database without using expdp command.

       

      Thanks for reading and I am waiting for your valued feedback.

       

      Have a good day!

        • 1. Re: cannot execute command after changing NLS_CHARSET
          top.gun

          1) Why did you need to change NLS_CHARSET ?

          2) How did you change it?

          3) Where is your backup of the database?

          • 2. Re: cannot execute command after changing NLS_CHARSET
            Damon K

            Thanks for your reply

             

            1) Why did you need to change NLS_CHARSET ?

                 I was trying to preprocess dataset before doing analysis but I needed to change character set because of dataset's characteristic.

             

            2) How did you change it?

                 For example, I executed SQL command like this

                      UPDATE sys.props$

                      SET value$ = 'UTF8'

                      WHERE name = 'NLS_CHARACTERSET';

             

                      UPDATE sys.props$

                      SET value$ = 'UTF8'

                      WHERE name = 'NLS_NCHAR_CHARACTERSET';

             

            3) Where is your backup of the database?

                Actually, I do not know where backup is.

            • 3. Re: cannot execute command after changing NLS_CHARSET
              Markus Flechtner

              Hi,

               

              changing the characterset by updating PROPS$ is not supported.

              You have to use the tools csscan/csalter (up to Oracle 11.2, desupported with 11.2) or DMU (from 10.2 or higher).

              Please see https://www.oracle.com/technetwork/database/database-technologies/globalization/dmu/downloads/index.html for more information on DMU.

               

               

              Regards

              Markus

              • 4. Re: cannot execute command after changing NLS_CHARSET
                Sergiusz Wolicki-Oracle

                As a junior IT developer, you have just learned a valuable lesson that you should never run DML on any Oracle-maintained table, especially one owned by SYS, unless the DML is explicitly documented in Oracle documentation or you get a corresponding advice from Oracle Support.

                 

                Your chances to repair the database are close to zero. The character set information is stored in a database table, which is in one of the data blocks of the SYSTEM tablespace. It is not in a separate configuration file. Data blocks have special internal format, so you cannot just write some text to them. Trying to locate the place and modify it back to the original value without corrupting the internal format would be a pretty time-consuming process for Oracle Support.

                 

                Seeing ORA-00911, I suspect you used some non-ASCII (Korean?) characters for some object names in the database and now the database cannot process these names as it cannot interpret the non-ASCII codes as UTF8. Because you cannot open the database, you cannot rename those objects either.

                 

                (By the way, the right character set for Unicode UTF-8 is AL32UTF8. UTF8 is deprecated.)

                 

                Thanks,
                Sergiusz