我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
故障分析：ORA-00900 修改props$中字符集导致
某天在一个群里面看到有人在说ORA-00900的错误，google.baidu有大量关于ORA-00900的相关信息，其实就是一个update语句导致的，因为update语句并不会马上删除旧的值，所以处理起来相当的简单。下面是自己的测试
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw
1，数据库版本
www.htz.pw > select * from v$version;BANNER——————————————————————————–Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit ProductionPL/SQL Release 11.2.0.4.0 – ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 – ProductionNLSRTL Version 11.2.0.4.0 – Production
2，模拟现象
create table props$( name varchar2("M_IDEN") not null, /* property name */value$ varchar2("M_VCSZ"), /* property value */comment$ varchar2("M_VCSZ")) /* description of property *//www.htz.pw > @segment.sqlEnter value for owner: sysEnter value for segment_name: props$Enter value for tablespace_name:HEADEROWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS—————————— ——————– ————— ——————– ——————– ———- ———- ——-SYS.PROPS$ TABLE SYSTEM 1.800 0 8 1****************************** ———-Total: 0www.htz.pw > select value$ from props$ where name=’NLS_CHARACTERSET’;VALUE$————-ZHS16GBK1 row selected.www.htz.pw > update props$ set value$=’AL16UTF16′ where name=’NLS_CHARACTERSET’;1 row updated.www.htz.pw > commit;Commit complete.www.htz.pw > startupORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00604: error occurred at recursive SQL level 1ORA-00900: invalid SQL statementProcess ID: 31441Session ID: 1 Serial number: 5
在alert中可以看到有下面的日志信息
[31441] Successfully onlined Undo Tablespace 2.Undo initialization finished serial:0 start:9942364 end:9942404 diff:40 (0 seconds)Verifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryUpdating character set in controlfile to AL16UTF16Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_31441.trc:ORA-00604: error occurred at recursive SQL level %sORA-00900: invalid SQL statementursive SQL level %sErrors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_31441.trc:ORA-00604: error occurred at recursive SQL level %sORA-00900: invalid SQL statementursive SQL level %sError 604 happened during db open, shutting down databaseUSER (ospid: 31441): terminating the instance due to error 604Instance terminated by USER, pid = 31441ORA-1092 signalled during: ALTER DATABASE OPEN…opiodr aborting process unknown ospid (31441) as a result of ORA-1092Thu Jun 05 19:06:57 2014ORA-1092 : opitsk aborting processThu Jun 05 19:07:13 2014
在31441文件中可以看到下面的信息
*** 2014-06-05 19:06:56.914*** SESSION ID:(1.5) 2014-06-05 19:06:56.914*** CLIENT ID:() 2014-06-05 19:06:56.914*** SERVICE NAME:(SYS$USERS) 2014-06-05 19:06:56.914*** MODULE NAME:(sqlplus@orcl9i (TNS V1-V3)) 2014-06-05 19:06:56.914*** ACTION NAME:() 2014-06-05 19:06:56.914ORA-00604: ^@e^@r^@r^@o^@r^@ ^@o^@c^@c^@u^@r^@r^@e^@d^@ ^@a^@t^@ ^@r^@e^@c^@u^@r^@s^@i^@v^@e^@ ^@S^@Q^@L^@ ^@l^@e^@v^@e^@l^@ ^@%^@s^@ORA-00900: ^@i^@n^@v^@a^@l^@i^@d^@ ^@S^@Q^@L^@ ^@s^@t^@a^@t^@e^@m^@e^@n^@t^@u^@r^@s^@i^@v^@e^@ ^@S^@Q^@L^@ ^@l^@e^@v^@e^@l^@ ^@%^@s^@ORA-00604: ^@e^@r^@r^@o^@r^@ ^@o^@c^@c^@u^@r^@r^@e^@d^@ ^@a^@t^@ ^@r^@e^@c^@u^@r^@s^@i^@v^@e^@ ^@S^@Q^@L^@ ^@l^@e^@v^@e^@l^@ ^@%^@s^@ORA-00900: ^@i^@n^@v^@a^@l^@i^@d^@ ^@S^@Q^@L^@ ^@s^@t^@a^@t^@e^@m^@e^@n^@t^@u^@r^@s^@i^@v^@e^@ ^@S^@Q^@L^@ ^@l^@e^@v^@e^@l^@ ^@%^@s^@*** 2014-06-05 19:06:56.915USER (ospid: 31441): terminating the instance due to error 604
3，bbed处理
因为这里我们修改的列的长度是一致的，所以解决的方案有很多的，如，将列的值更改回原来的，将行指针，指向原来的值等方法
定位在块中那一行，可以使用dump块的方法，也可以直接使用find的方法，这里我直接使用的find的方法
[oracle@www.htz.pw trace]$bbedPassword:BBED: Release 2.0.0.0.0 – Limited Production on Thu Jun 5 19:11:25 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> set filename ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’;FILENAME /oracle/app/oracle/oradata/orcl1124/system01.dbfBBED> set block 801BLOCK# 801BBED> find /c NLS_CHARACTERSETFile: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)Block: 801 Offsets: 6001 to 6512 Dba:0x00000000————————————————————————4e4c535f 43484152 41435445 52534554 09414c31 36555446 31360d43 6861726163746572 20736574 2c00030a 44425449 4d455a4f 4e450530 303a3030 0c44422074696d65 207a6f6e 652c0002 174e4f5f 55534552 49445f56 45524946 4945525fBBED> fFile: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)Block: 801 Offsets: 7247 to 7758 Dba:0x00000000————————————————————————4e4c535f 43484152 41435445 52534554 085a4853 31364742 4b0d4368 6172616374657220 7365742c 0003164e 4c535f4e 554d4552 49435f43 48415241 4354455253022e2c 124e756d 65726963 20636861 72616374 6572732c 0003104e 4c535f49
当前值是6001这里这个
BBED> fBBED-00212: search string not foundBBED> p kdbrsb2 kdbr[0] @110 8048sb2 kdbr[1] @112 7767sb2 kdbr[2] @114 6290sb2 kdbr[3] @116 7836sb2 kdbr[4] @118 7696sb2 kdbr[5] @120 7675sb2 kdbr[6] @122 -1sb2 kdbr[7] @124 7576sb2 kdbr[8] @126 7509sb2 kdbr[9] @128 7439sb2 kdbr[10] @130 7385sb2 kdbr[11] @132 7351sb2 kdbr[12] @134 7316sb2 kdbr[13] @136 7283sb2 kdbr[14] @138 7242sb2 kdbr[15] @140 7194sb2 kdbr[16] @142 5905sb2 kdbr[17] @144 7109sb2 kdbr[18] @146 7068sb2 kdbr[19] @148 7024sb2 kdbr[20] @150 6983sb2 kdbr[21] @152 6937sb2 kdbr[22] @154 6870sb2 kdbr[23] @156 6803sb2 kdbr[24] @158 6716sb2 kdbr[25] @160 6672sb2 kdbr[26] @162 6638sb2 kdbr[27] @164 6588sb2 kdbr[28] @166 6534sb2 kdbr[29] @168 6478sb2 kdbr[30] @170 6413sb2 kdbr[31] @172 6365sb2 kdbr[32] @174 6240sb2 kdbr[33] @176 6166sb2 kdbr[34] @178 6042sb2 kdbr[35] @180 5982sb2 kdbr[36] @182 5949BBED> p ktbbhstruct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA)union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000062ub4 ktbbhod1 @24 0x00000062struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000ea5d7ub2 kscnwrp @32 0x0000sb2 ktbbhict @36 2ub1 ktbbhflg @38 0x03 (KTBFONFL)ub1 ktbbhfsl @39 0x00ub4 ktbbhfnx @40 0x00000000struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0004ub2 kxidslt @46 0x0000ub4 kxidsqn @48 0x0000029dstruct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00ac4ub2 kubaseq @56 0x00b7ub1 kubarec @58 0x09ub2 ktbitflg @60 0x8000 (KTBFCOM)union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0ub2 _ktbitwrp @62 0x0000ub4 ktbitbas @64 0x000ea5d6struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006ub2 kxidslt @70 0x0003ub4 kxidsqn @72 0x000005a1struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c0010cub2 kubaseq @80 0x01f5ub1 kubarec @82 0x01ub2 ktbitflg @84 0x2001 (KTBFUPB)union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0ub2 _ktbitwrp @86 0x0000ub4 ktbitbas @88 0x002ab1f8
由于是MSSM管理的表空间
FOR MSSMreal offset= kdbr[n] + 68 + (itls-1) *24
大概就是5905前面一行
BBED> x /rcc *kdbr[16]rowdata[0] @5997 ———-flag@5997: 0x2c (KDRHFL, KDRHFF, KDRHFH)lock@5998: 0x02cols@5999: 3col 0[16] @6000: NLS_CHARACTERSETcol 1[9] @6017: AL16UTF16col 2[13] @6027: Character set
所以这里是从6018开始存放的
www.htz.pw > select dump(‘ZHS16GBK’,16) from dual;DUMP(‘ZHS16GBK’,16)————————————-Typ=96 Len=8: 5a,48,53,31,36,47,42,4bBBED> modify /x 5a485331 offset 6018File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)Block: 801 Offsets: 6017 to 6025 Dba:0x00000000————————————————————————085a4853 36555446 31<32 bytes per line>BBED> modify /x 3647424b offset 6022File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)Block: 801 Offsets: 6021 to 6029 Dba:0x00000000————————————————————————31364742 31360d43 68<32 bytes per line>BBED> dump offset 6018 count 9File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)Block: 801 Offsets: 6018 to 6026 Dba:0x00000000————————————————————————5a485331 3647424b 36<32 bytes per line>BBED> sum applyCheck value for File 0, Block 801:current = 0xa257, required = 0xa257BBED> verifyDBVERIFY – Verification startingFILE = /oracle/app/oracle/oradata/orcl1124/system01.dbfBLOCK = 801DBVERIFY – Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531 not found; product=RDBMS; facility=BBED
下面是重建控制文件
www.htz.pw > startup ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.ORA-00911: invalid characterORACLE_BASE from environment = /oracle/app/oracleThu Jun 05 19:51:44 2014ALTER DATABASE MOUNTSuccessful mount of redo thread 1, with mount id 3338254288Database mounted in Exclusive ModeLost write protection disabledCompleted: ALTER DATABASE MOUNTwww.htz.pw > startup force nomount;ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytes
这里可以使用noresetlogs的方式，因为我们所有的文件都存在
www.htz.pw > CREATE CONTROLFILE REUSE DATABASE "ORCL1124" RESETLOGS NOARCHIVELOG2 MAXLOGFILES 163 MAXLOGMEMBERS 34 MAXDATAFILES 1005 MAXINSTANCES 86 MAXLOGHISTORY 2927 LOGFILE8 GROUP 1 ‘/oracle/app/oracle/oradata/orcl1124/redo01.log’ SIZE 50M BLOCKSIZE 512,9 GROUP 2 ‘/oracle/app/oracle/oradata/orcl1124/redo02.log’ SIZE 50M BLOCKSIZE 512,10 GROUP 3 ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ SIZE 50M BLOCKSIZE 51211 — STANDBY LOGFILE12 DATAFILE13 ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’,14 ‘/oracle/app/oracle/oradata/orcl1124/sysaux01.dbf’,15 ‘/oracle/app/oracle/oradata/orcl1124/undotbs01.dbf’,16 ‘/oracle/app/oracle/oradata/orcl1124/users01.dbf’,17 ‘/oracle/app/oracle/oradata/orcl1124/htz01.dbf’,18 ‘/oracle/app/oracle/oradata/orcl1124/undotbs02.dbf’19 CHARACTER SET ZHS16GBK20 ;Control file created.www.htz.pw > recover database using backup controlfile until cancel;ORA-00279: change 2798499 generated at 06/05/2014 19:06:56 needed for thread 1ORA-00289: suggestion :/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_1_%u_.arcORA-00280: change 2798499 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/oracle/app/oracle/oradata/orcl1124/redo03.logORA-00339: archived log does not contain any redoORA-00334: archived log: ‘/oracle/app/oracle/oradata/orcl1124/redo03.log’ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: ‘/oracle/app/oracle/oradata/orcl1124/system01.dbf’www.htz.pw > recover database using backup controlfile until cancel;ORA-00279: change 2798499 generated at 06/05/2014 19:06:56 needed for thread 1ORA-00289: suggestion :/oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_1_%u_.arcORA-00280: change 2798499 for thread 1 is in sequence #1Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/oracle/app/oracle/oradata/orcl1124/redo01.logLog applied.Media recovery complete.www.htz.pw > alter database open resetlogs;Database altered.
数据库已经成功打开
4，其它的一些测试
下面测试将值更它为其它的一些不正确的值，数据库仍能打开
www.htz.pw > update props$ set value$=’AL16U’ where name=’NLS_CHARACTERSET’;1 row updated.www.htz.pw > commit;Commit complete.www.htz.pw > startup forceORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.Database opened.
更改成其它的值的数据库还其它来了
www.htz.pw > update props$ set value$=” where name=’NLS_CHARACTERSET’;1 row updated.www.htz.pw > commit;Commit complete.www.htz.pw > startup forceORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.Database opened.
这里更改为空也起来，
www.htz.pw > update props$ set value$=’ZHS16GBK’ where name=’NLS_CHARACTERSET’;1 row updated.www.htz.pw > commit;Commit complete.www.htz.pw > startup force;ORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.Database opened.www.htz.pw > update props$ set value$=’ ‘ where name=’NLS_CHARACTERSET’;1 row updated.www.htz.pw > commit;Commit complete.www.htz.pw > startup forceORACLE instance started.Total System Global Area 379965440 bytesFixed Size 2253464 bytesVariable Size 171969896 bytesDatabase Buffers 201326592 bytesRedo Buffers 4415488 bytesDatabase mounted.Database opened.
其实还有很多其它的方法可以实现的。只要能达到目的，使用自己最熟悉的方法就可以了。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
