Modifying props $. NLS_CHARACTERSET results in ORA-00900 exception recovery

This article to introduce you to modify props $. NLS_CHARACTERSET caused by ORA-00900 exception recovery, need to know friends can refer.

Today a friend and I said his database can not open, open process prompts ORA-00900 error, by analyzing the alert Log and props $ table, they found that they modified an invalid NLS_CHARACTERSET value, as a result, the database cannot be started normally (to be accurate, because the value in the database is invalid, when the database is open, the encoding specified by the control file is detected to be inconsistent with the value, then modify the control file encoding, after the modification, the database to the mount State to execute any statements are reported ORA-00900 error), through some tools to modify the NLS_CHARACTERSET to the correct value of the fault solution

Reproduce ORA-00900 faults

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 The Session ID is 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.

Error reported during the first startup (open) Process

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

Here we can see that there is one (Updating character set in controlfile to AL16UTF16). It is precisely because of this operation that the update control file encoding is an invalid code, as a result, after the database is mounted (the control file is loaded), no other operations can be performed.

Solution
Use odu to locate the block or query the block location in the same version Library
Use dul or bbed to modify the NLS_CHARACTERSET value of props $
Recreate the control file (noresetlogs Mode)

Handling process

The Code is as follows:	Copy code
SQL> shutdown abort ORACLE instance shut down.

Location of block found by odu
Modify the block Value of dul or bbed
Recreate the control file (noresetlogs Mode)
 

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' SQL> recover database; Media recovery complete. SQL> alter database open; Database altered.

From oracle 9i, you can modify the database encoding directly by using alter database character set internal_use instead of directly modifying the props $ base table, you cannot modify a code value that does not actually exist. (In this article, the database code is confused with the country code to cause this fault)