故障分析：ORA-00900 修改props$中字符集导致
我们的文章会在微信公众号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 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 */
/
www.htz.pw > @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
www.htz.pw > select value$ from props$ where name=’NLS_CHARACTERSET’;
VALUE$
————-
ZHS16GBK
1 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 > 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开始存放的
www.htz.pw > 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
下面是重建控制文件
www.htz.pw > 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
www.htz.pw > 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的方式，因为我们所有的文件都存在
www.htz.pw > 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.
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 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’
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 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.
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 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.
更改成其它的值的数据库还其它来了
www.htz.pw > update props$ set value$=” 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 bytes
Fixed Size 2253464 bytes
Variable Size 171969896 bytes
Database Buffers 201326592 bytes
Redo Buffers 4415488 bytes
Database 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 bytes
Fixed Size 2253464 bytes
Variable Size 171969896 bytes
Database Buffers 201326592 bytes
Redo Buffers 4415488 bytes
Database 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 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.
其实还有很多其它的方法可以实现的。只要能达到目的，使用自己最熟悉的方法就可以了。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号