本文共 13292 字，大约阅读时间需要 44 分钟。
那天在一个群里面看到有人在说ORA-00900的错误，google.baidu有大量关于ORA-00900的相关信息，其实就是一个update语句导致的，因为update语句并不会马上删除旧的值，所以处理起来相当的简单。下面是自己的测试
欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到
1，数据库版本
| > select * from v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL 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 */ / > @segment.sql Enter value for owner: sys Enter value for segment_name: props$ Enter value for tablespace_name: HEADER OWNER:SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME FILE_BLOCK SIZE(M) BLOCKS EXTENTS ------------------------------ -------------------- --------------- -------------------- -------------------- ---------- ---------- ------- SYS.PROPS$ TABLE SYSTEM 1.800 0 8 1 ****************************** ---------- Total: 0 > select value$ from props$ where name='NLS_CHARACTERSET'; VALUE$ ------------- ZHS16GBK 1 row selected. > update props$ set value$='AL16UTF16' where name='NLS_CHARACTERSET'; 1 row updated. > commit; Commit complete. > startup ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 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: 31441 Session 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 completed SMON: enabling tx recovery Updating character set in controlfile to AL16UTF16 Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_31441.trc: ORA-00604: error occurred at recursive SQL level %s ORA-00900: invalid SQL statementursive SQL level %s Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_31441.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: 31441): terminating the instance due to error 604 Instance terminated by USER, pid = 31441 ORA-1092 signalled during: ALTER DATABASE OPEN... opiodr aborting process unknown ospid (31441) as a result of ORA-1092 Thu Jun 05 19:06:57 2014 ORA-1092 : opitsk aborting process Thu 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.914
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^@
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.915 USER (ospid: 31441): terminating the instance due to error 604 |
3，bbed处理
因为这里我们修改的列的长度是一致的，所以解决的方案有很多的，如，将列的值更改回原来的，将行指针，指向原来的值等方法
定位在块中那一行，可以使用dump块的方法，也可以直接使用find的方法，这里我直接使用的find的方法
| [oracle@www.htz.pw trace]$bbed Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 5 19:11:25 2014
Copyright (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.dbf
BBED> set block 801 BLOCK# 801
BBED> find /c NLS_CHARACTERSET File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0) Block: 801 Offsets: 6001 to 6512 Dba:0x00000000 ------------------------------------------------------------------------ 4e4c535f 43484152 41435445 52534554 09414c31 36555446 31360d43 68617261 63746572 20736574 2c00030a 44425449 4d455a4f 4e450530 303a3030 0c444220 74696d65 207a6f6e 652c0002 174e4f5f 55534552 49445f56 45524946 4945525f BBED> f File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0) Block: 801 Offsets: 7247 to 7758 Dba:0x00000000 ------------------------------------------------------------------------ 4e4c535f 43484152 41435445 52534554 085a4853 31364742 4b0d4368 61726163 74657220 7365742c 0003164e 4c535f4e 554d4552 49435f43 48415241 43544552 53022e2c 124e756d 65726963 20636861 72616374 6572732c 0003104e 4c535f49 当前值是6001这里这个 BBED> f BBED-00212: search string not found
BBED> p kdbr sb2 kdbr[0] @110 8048 sb2 kdbr[1] @112 7767 sb2 kdbr[2] @114 6290 sb2 kdbr[3] @116 7836 sb2 kdbr[4] @118 7696 sb2 kdbr[5] @120 7675 sb2 kdbr[6] @122 -1 sb2 kdbr[7] @124 7576 sb2 kdbr[8] @126 7509 sb2 kdbr[9] @128 7439 sb2 kdbr[10] @130 7385 sb2 kdbr[11] @132 7351 sb2 kdbr[12] @134 7316 sb2 kdbr[13] @136 7283 sb2 kdbr[14] @138 7242 sb2 kdbr[15] @140 7194 sb2 kdbr[16] @142 5905 sb2 kdbr[17] @144 7109 sb2 kdbr[18] @146 7068 sb2 kdbr[19] @148 7024 sb2 kdbr[20] @150 6983 sb2 kdbr[21] @152 6937 sb2 kdbr[22] @154 6870 sb2 kdbr[23] @156 6803 sb2 kdbr[24] @158 6716 sb2 kdbr[25] @160 6672 sb2 kdbr[26] @162 6638 sb2 kdbr[27] @164 6588 sb2 kdbr[28] @166 6534 sb2 kdbr[29] @168 6478 sb2 kdbr[30] @170 6413 sb2 kdbr[31] @172 6365 sb2 kdbr[32] @174 6240 sb2 kdbr[33] @176 6166 sb2 kdbr[34] @178 6042 sb2 kdbr[35] @180 5982 sb2 kdbr[36] @182 5949
BBED> p ktbbh struct ktbbh, 72 bytes @20 ub1 ktbbhtyp @20 0x01 (KDDBTDATA) union ktbbhsid, 4 bytes @24 ub4 ktbbhsg1 @24 0x00000062 ub4 ktbbhod1 @24 0x00000062 struct ktbbhcsc, 8 bytes @28 ub4 kscnbas @28 0x000ea5d7 ub2 kscnwrp @32 0x0000 sb2 ktbbhict @36 2 ub1 ktbbhflg @38 0x03 (KTBFONFL) ub1 ktbbhfsl @39 0x00 ub4 ktbbhfnx @40 0x00000000 struct ktbbhitl[0], 24 bytes @44 struct ktbitxid, 8 bytes @44 ub2 kxidusn @44 0x0004 ub2 kxidslt @46 0x0000 ub4 kxidsqn @48 0x0000029d struct ktbituba, 8 bytes @52 ub4 kubadba @52 0x00c00ac4 ub2 kubaseq @56 0x00b7 ub1 kubarec @58 0x09 ub2 ktbitflg @60 0x8000 (KTBFCOM) union _ktbitun, 2 bytes @62 sb2 _ktbitfsc @62 0 ub2 _ktbitwrp @62 0x0000 ub4 ktbitbas @64 0x000ea5d6 struct ktbbhitl[1], 24 bytes @68 struct ktbitxid, 8 bytes @68 ub2 kxidusn @68 0x0006 ub2 kxidslt @70 0x0003 ub4 kxidsqn @72 0x000005a1 struct ktbituba, 8 bytes @76 ub4 kubadba @76 0x00c0010c ub2 kubaseq @80 0x01f5 ub1 kubarec @82 0x01 ub2 ktbitflg @84 0x2001 (KTBFUPB) union _ktbitun, 2 bytes @86 sb2 _ktbitfsc @86 0 ub2 _ktbitwrp @86 0x0000 ub4 ktbitbas @88 0x002ab1f8 由于是MSSM管理的表空间 FOR MSSM real offset= kdbr[n] + 68 + (itls-1) *24 大概就是5905前面一行
BBED> x /rcc *kdbr[16] rowdata[0] @5997 ---------- flag@5997: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5998: 0x02 cols@5999: 3
col 0[16] @6000: NLS_CHARACTERSET col 1[9] @6017: AL16UTF16 col 2[13] @6027: Character set
所以这里是从6018开始存放的
> select dump('ZHS16GBK',16) from dual;
DUMP('ZHS16GBK',16) ------------------------------------- Typ=96 Len=8: 5a,48,53,31,36,47,42,4b
BBED> modify /x 5a485331 offset 6018 File: /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 6022 File: /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 9 File: /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 apply Check value for File 0, Block 801: current = 0xa257, required = 0xa257
BBED> verify DBVERIFY - Verification starting FILE = /oracle/app/oracle/oradata/orcl1124/system01.dbf BLOCK = 801
DBVERIFY - Verification complete
Total Blocks Examined : 1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
下面是重建控制文件 > startup ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. ORA-00911: invalid character
ORACLE_BASE from environment = /oracle/app/oracle Thu Jun 05 19:51:44 2014 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 3338254288 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT
> startup force nomount; ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes 这里可以使用noresetlogs的方式，因为我们所有的文件都存在 > CREATE CONTROLFILE REUSE DATABASE "ORCL1124" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 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 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/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 ZHS16GBK 20 ;
Control file created.
> recover database using backup controlfile until cancel; ORA-00279: change 2798499 generated at 06/05/2014 19:06:56 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_1_% u_.arc ORA-00280: change 2798499 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/orcl1124/redo03.log ORA-00339: archived log does not contain any redo ORA-00334: archived log: '/oracle/app/oracle/oradata/orcl1124/redo03.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/app/oracle/oradata/orcl1124/system01.dbf'
> recover database using backup controlfile until cancel; ORA-00279: change 2798499 generated at 06/05/2014 19:06:56 needed for thread 1 ORA-00289: suggestion : /oracle/app/oracle/fast_recovery_area/ORCL1124/archivelog/2014_06_05/o1_mf_1_1_% u_.arc ORA-00280: change 2798499 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oracle/app/oracle/oradata/orcl1124/redo01.log Log applied. Media recovery complete.
> alter database open resetlogs;
Database altered.
数据库已经成功打开 |
4，其它的一些测试
下面测试将值更它为其它的一些不正确的值，数据库仍能打开
| > update props$ set value$='AL16U' where name='NLS_CHARACTERSET';
1 row updated.
> commit;
Commit complete.
> startup force ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. Database opened. 更改成其它的值的数据库还其它来了
> update props$ set value$='' where name='NLS_CHARACTERSET';
1 row updated.
> commit;
Commit complete.
> startup force ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. Database opened. 这里更改为空也起来，
> update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';
1 row updated.
> commit;
Commit complete.
> startup force; ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. Database opened.
> update props$ set value$=' ' where name='NLS_CHARACTERSET';
1 row updated.
> commit;
Commit complete.
> startup force ORACLE instance started.
Total System Global Area 379965440 bytes Fixed Size 2253464 bytes Variable Size 171969896 bytes Database Buffers 201326592 bytes Redo Buffers 4415488 bytes Database mounted. Database opened. |
其实还有很多其它的方法可以实现的。只要能达到目的，使用自己最熟悉的方法就可以了。
本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/1424394，如需转载请自行联系原作者