Modify props$. Nls_characterset causes ORA-00900 to recover unexpectedly

Source: Internet
Author: User
Tags character set session id

Today a friend and I said that his database could not be Open,open process prompts ORA-00900 error, by analyzing alert log and props$ table, they found that they have modified an invalid Nls_characterset value, causing the database to not start correctly (exactly, Because the value in the database is invalid, when the database open, the control file is detected to specify the encoding and the value inconsistent, and then modify the control file encoding, after modification, the database to the Mount state to execute any statements are reported ORA-00900 error), through a number of tools to modify the Nls_ CharacterSet is the correct value for this fault resolution

Reproduce ORA-00900 failure

The code is as follows Copy Code
Sql> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition release 11.2.0.3.0-production
Pl/sql Release 11.2.0.3.0-production
CORE 11.2.0.3.0 Production
TNS for Linux:version 11.2.0.3.0-production
Nlsrtl Version 11.2.0.3.0-production

Sql> Select value$ from props$ where name= ' nls_characterset ';

value$
-------------------------------------------------------
Zhs16gbk

sql> Update props$ set value$= ' al16utf16 ' where name= ' nls_characterset ';

1 row updated.

Sql> commit;

Commit complete.

sql> ALTER DATABASE backup Controlfile to trace as '/tmp/ora11g.ctl ';

Database altered.

sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sql> Startup
ORACLE instance started.

Total System Global area 175775744 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database buffers 50331648 bytes
Redo buffers 6656000 bytes
Database mounted.
Ora-01092:oracle instance terminated. Disconnection forced
Ora-00604:error occurred at recursive SQL level 1
Ora-00900:invalid SQL Statement
Process id:5277
Session id:125 Serial Number:5

sql> startup Nomount;
ORACLE instance started.

Total System Global area 175775744 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database buffers 50331648 bytes
Redo buffers 6656000 bytes

Sql> ALTER DATABASE Mount;

Database altered.

sql> ALTER DATABASE open;
ALTER DATABASE Open
*
ERROR at line 1:
Ora-00900:invalid SQL Statement

Sql>select * from dual;
SELECT * FROM dual
*
ERROR at line 1:
Ora-00900:invalid SQL Statement

sql> Shutdown Abort
ORACLE instance shut down.

The first startup (open) procedure complains

The code is as follows Copy Code
smon:enabling TX Recovery
Updating character set in Controlfile to Al16utf16
Errors in FILE/U01/ORACLE/DIAG/RDBMS/ORA11G/ORA11G/TRACE/ORA11G_ORA_5277.TRC:
Ora-00604:error occurred at recursive SQL level%s
Ora-00900:invalid SQL Statementursive SQL level%s
Errors in FILE/U01/ORACLE/DIAG/RDBMS/ORA11G/ORA11G/TRACE/ORA11G_ORA_5277.TRC:
Ora-00604:error occurred at recursive SQL level%s
Ora-00900:invalid SQL Statementursive SQL level%s
Error 604 happened during DB Open, shutting down database
USER (ospid:5277): Terminating the instance due to error 604
Errors in FILE/U01/ORACLE/DIAG/RDBMS/ORA11G/ORA11G/TRACE/ORA11G_SMON_5243.TRC:
Ora-00604:error occurred at recursive SQL level%s
Ora-00900:invalid SQL Statementursive SQL level%s
Instance terminated by USER, PID = 5277
ORA-1092 signalled During:alter DATABASE OPEN ...
OPIODR aborting process unknown ospid (5277) as a result of ORA-1092
Sat May 18 00:44:27 2013
Ora-1092:opitsk aborting Process

It is obvious here that there is a (updating character set in Controlfile to Al16utf16), precisely because of this operation, the update control file encoding is an invalid encoding, resulting in the subsequent database mount (load control file) After that, you can't do anything else.

Solving ideas
Use Odu to find the block location, or query in the same version library
Modify the Nls_characterset value of props$ using Dul or bbed
Rebuild the control file (Noresetlogs way)

Processing process

The code is as follows Copy Code
sql> Shutdown Abort
ORACLE instance shut down.


Odu find the block position.
Dul or bbed Modify the block value
Rebuild the control file (Noresetlogs way)

The code is as follows Copy Code
sql> Startup
ORACLE instance started.
 
Total System Global area  175775744 bytes
Fixed size                   1343668 bytes
Variable size              117444428 bytes
Database buffers            50331648 bytes
Redo buffers                 6656000 bytes
Database mounted.
Ora-01113:file 1 Needs media recovery
Ora-01110:data file 1: '/u01/oracle/oradata/ora11g/system01.dbf '
&NB Sp
 
sql> recover database;
Media recovery complete.
Sql> ALTER DATABASE open;
 
Database altered.

Starting with Oracle 9i, modify the encoding of the database directly using the ALTER DATABASE character set Internal_use method instead of directly modifying the props$ base table, or modifying an encoded value that does not actually exist ( In this article, the database encoding is confused with the country code to make the fault occur)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.