当前位置: 首页 > BACKUP & RESTORE, ORA > 正文

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

3bbed处理

因为这里我们修改的列的长度是一致的,所以解决的方案有很多的,如,将列的值更改回原来的,将行指针,指向原来的值等方法

 

定位在块中那一行,可以使用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.

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

本文固定链接: http://www.htz.pw/2014/06/10/ora-00900-%e4%bf%ae%e6%94%b9props%e4%b8%ad%e5%ad%97%e7%ac%a6%e9%9b%86%e5%af%bc%e8%87%b4.html | 认真就输

该日志由 huangtingzhong 于2014年06月10日发表在 BACKUP & RESTORE, ORA 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORA-00900 修改props$中字符集导致 | 认真就输
关键字: ,