ORA-00900 修改props$中字符集导致-CSDN博客

那天在一个群里面看到有人在说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

************* !!! 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.

其实还有很多其它的方法可以实现的。只要能达到目的，使用自己最熟悉的方法就可以了。

本文转自7343696 51CTO博客，原文链接：http://blog.51cto.com/luoping/1424394，如需转载请自行联系原作者