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

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)