[20190531]ORA-600 kokasgi1故障模擬與恢復.txt

--//昨天看連結:半天看到的還是惜分飛網站的連結.

--//既然他提到sys和system被人重新命名,解決相對容易,透過測試說明問題.千萬不要在生產系統做這樣的測試.

--//再次提醒大家,下載oracle介質要選擇官方網站並且要做md5sum,不對堅決不要使用!!一些工具下載也要注意檢查裡面的一些登入執行的sql指令碼.

--//另外千萬注意不要使用別人的電腦登入生產系統.以免口令外洩.

--//參考連結:http://blog.itpub.net/267265/viewspace-1085037/=>[20140221]login.sql與系統安全.txt

1.環境:

SYS@book> @ ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.模擬:

SYS@book> select rowid ,name from user$ where name like 'SYS%';

ROWID NAME

------------------ ----------------------------------------

AAAAAKAABAAAADRAAB SYS

AAAAAKAABAAAADUAAL SYSMAN

AAAAAKAABAAAADRAAG SYSTEM

SYS@book> @ rowid AAAAAKAABAAAADRAAB

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

10 1 209 1 0x4000D1 1,209 alter system dump datafile 1 block 209 ;

SYS@book> @ rowid AAAAAKAABAAAADRAAG

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

10 1 209 6 0x4000D1 1,209 alter system dump datafile 1 block 209 ;

SYS@book> update user$ set name=name||'DW' where name in( 'SYS','SYSTEM');

2 rows updated.

SYS@book> commit ;

Commit complete.

SYS@book> select rowid ,name from user$ where name like 'SYS%';

ROWID NAME

------------------ ----------------------------------------

AAAAAKAABAAAADRAAB SYSDW

AAAAAKAABAAAADUAAL SYSMAN

AAAAAKAABAAAADRAAG SYSTEMDW

SYS@book> shutdown immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@book> startup

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Process ID: 55795

Session ID: 295 Serial number: 3

--//alert.log記錄如下:

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55795.trc (incident=2920815):

ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []

Incident details in: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_2920815/book_ora_55795_i2920815.trc

Incremental checkpoint up to RBA [0x49dc.94c1.0], current log tail at RBA [0x49dc.94ff.0]

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55795.trc:

ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []

Errors in file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_55795.trc:

ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []

Error 600 happened during db open, shutting down database

USER (ospid: 55795): terminating the instance due to error 600

Instance terminated by USER, pid = 55795

ORA-1092 signalled during: ALTER DATABASE OPEN...

opiodr aborting process unknown ospid (55795) as a result of ORA-1092

Fri May 31 09:41:55 2019

ORA-1092 : opitsk aborting process

3.分析:

SYS@book> @ 10046on 12

old 1: alter session set events '10046 trace name context forever, level &1'

new 1: alter session set events '10046 trace name context forever, level 12'

Session altered.

SYS@book> alter database open ;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []

Process ID: 55895

Session ID: 295 Serial number: 3

SYS@book> @ 10046off

ERROR:

ORA-03114: not connected to ORACLE

--//檢查跟蹤檔案發現:

=====================

PARSING IN CURSOR #140302340540856 len=189 dep=1 uid=0 oct=3 lid=0 tim=1559267635846185 hv=186852205 ad='7d1df0b8' sqlid='2tkw12w5k68vd'

select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2 from user$ where name=:1

END OF STMT

PARSE #140302340540856:c=0,e=269,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1559267635846184

BINDS #140302340540856:

Bind#0

oacdty=01 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00

oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0

kxsbbbfp=7f9aaf2b4568 bln=32 avl=03 flg=05

value="SYS"

EXEC #140302340540856:c=1000,e=472,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1457651150,tim=1559267635846719

FETCH #140302340540856:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=1457651150,tim=1559267635846747

STAT #140302340540856 id=1 cnt=0 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=1 pr=0 pw=0 time=9 us)'

STAT #140302340540856 id=2 cnt=0 pid=1 pos=1 obj=46 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=8 us)'

CLOSE #140302340540856:c=0,e=4,dep=1,type=0,tim=1559267635846793

Incident 2922415 created, dump file: /u01/app/oracle/diag/rdbms/book/book/incident/incdir_2922415/book_ora_55895_i2922415.trc

ORA-00600: internal error code, arguments: [kokasgi1], [], [], [], [], [], [], [], [], [], [], []

*** 2019-05-31 09:53:56.717

USER (ospid: 55895): terminating the instance due to error 600

--//往前看可以發現如下:

=====================

PARSING IN CURSOR #140302340501536 len=189 dep=2 uid=0 oct=3 lid=0 tim=1559267635703833 hv=186852205 ad='7d1df0b8' sqlid='2tkw12w5k68vd'

select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2 from user$ where name=:1

END OF STMT

PARSE #140302340501536:c=0,e=633,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1559267635703831

BINDS #140302340501536:

Bind#0

oacdty=01 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00

oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0

kxsbbbfp=7f9aaf2aabd0 bln=32 avl=05 flg=05

value="SYSDW"

EXEC #140302340501536:c=999,e=1015,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=1457651150,tim=1559267635704985

WAIT #140302340501536: nam='db file sequential read' ela= 11 file#=1 block#=417 blocks=1 obj#=46 tim=1559267635705054

FETCH #140302340501536:c=0,e=83,p=1,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=1457651150,tim=1559267635705108

STAT #140302340501536 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=1 pw=0 time=76 us)'

STAT #140302340501536 id=2 cnt=1 pid=1 pos=1 obj=46 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=1 pw=0 time=67 us)'

CLOSE #140302340501536:c=0,e=7,dep=2,type=0,tim=1559267635705213

=====================

--//dba = 1,417 是索引I_USER1.也就是修復要禁用這個索引.

4.bbed觀察:

BBED> set dba 1,417

DBA 0x004001a1 (4194721 1,417)

BBED> find /c SYSDW TOP

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 417 Offsets: 5864 to 5927 Dba:0x004001a1

------------------------------------------------------------------------------------------------------------------------------------------------

53595344 57000200 4000d100 06085359 5354454d 44570000 004000d2 00100448 49533500 00004000 d2000f0b 4a415641 5f444550 4c4f5900 00004000 d500010a

<64 bytes per line>

BBED> x /rc *kd_off[87]

rowdata[4] @5855

----------

flag@5855: 0x00 (NONE)

lock@5856: 0x02

keydata[6]: 0x00 0x40 0x00 0xd1 0x00 0x01

data key:

col 0[5] @5864: SYSDW

--//rowid = 0x00 0x40 0x00 0xd1 0x00 0x01 ,dba 0x004000d1=4194513

--//4194513= alter system dump datafile 1 block 209

BBED> set dba 1,209

DBA 0x004000d1 (4194513 1,209)

BBED> p kdbt

struct kdbt[0], 4 bytes @106

sb2 kdbtoffs @106 0

sb2 kdbtnrow @108 21

struct kdbt[1], 4 bytes @110

sb2 kdbtoffs @110 21

sb2 kdbtnrow @112 22

--//SYS.user$是CLUSTER TABLE中的一個表.

BBED> x /rcncnnttttncnnnnnccnnncct *kdbr[22]

rowdata[173] @3074

------------

flag@3074: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@3075: 0x02

cols@3076: 22

ckix@3077: 1

col 0[5] @3078: SYSDW

col 1[2] @3084: 1

col 2[16] @3087: 8A8F025737A9097A

col 3[1] @3104: 0

col 4[2] @3106: 3

col 5[7] @3109: 2013-08-24 11:37:40

col 6[7] @3117: 2017-02-15 08:35:57

col 7[7] @3125: 2013-08-24 12:07:04

col 8[7] @3133: 2013-08-24 12:07:04

col 9[1] @3141: 0

col 10[0] @3143: *NULL*

col 11[2] @3144: 1

col 12[0] @3147: *NULL*

col 13[0] @3148: *NULL*

col 14[1] @3149: 0

col 15[1] @3151: 0

col 16[22] @3153: DEFAULT_CONSUMER_GROUP

col 17[0] @3176: *NULL*

col 18[1] @3177: 0

col 19[0] @3179: *NULL*

col 20[0] @3180: *NULL*

col 21[62] @3181: S:757313FFE2E6EF9A6335E271A6AC31A5183587AB5A50C8C99611A375F95E

BBED> x /rcncnnttttncnnnnnccnnncct *kdbr[27]

rowdata[0] @2901

----------

flag@2901: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@2902: 0x02

cols@2903: 22

ckix@2904: 5

col 0[8] @2905: SYSTEMDW

col 1[2] @2914: 1

col 2[16] @2917: 2D594E86F93B17A1

col 3[1] @2934: 0

col 4[2] @2936: 3

col 5[7] @2939: 2013-08-24 11:37:40

col 6[7] @2947: 2019-02-28 09:14:04

col 7[7] @2955: 2016-08-24 11:45:42

col 8[7] @2963: 2013-08-24 12:07:04

col 9[1] @2971: 0

col 10[0] @2973: *NULL*

col 11[2] @2974: 1

col 12[0] @2977: *NULL*

col 13[0] @2978: *NULL*

col 14[1] @2979: 0

col 15[1] @2981: 0

col 16[22] @2983: DEFAULT_CONSUMER_GROUP

col 17[0] @3006: *NULL*

col 18[1] @3007: 0

col 19[0] @3009: *NULL*

col 20[0] @3010: *NULL*

col 21[62] @3011: S:71B503269929F92E4BC275D2B9EBAB760C5C6D3A6BC4BD408F5EB6A0CF63

--//找到原來的位置.

BBED> set offset 7000

OFFSET 7000

BBED> find /c SYS curr

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 209 Offsets: 7413 to 7476 Dba:0x004000d1

------------------------------------------------------------------------------------------------------------------------------------------------

53595354 454d02c1 02104434 44463739 33314142 31333045 33370180 02c10407 78710818 0c262907 78710818 0c2629ff ff0180ff 02c102ff ff018001 80164445

<64 bytes per line>

BBED> dump /v

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 209 Offsets: 7413 to 7476 Dba:0x004000d1

-----------------------------------------------------------------------------------------------------------

53595354 454d02c1 02104434 44463739 33314142 31333045 33370180 02c10407 l SYSTEM....D4DF7931AB130E37......

78710818 0c262907 78710818 0c2629ff ff0180ff 02c102ff ff018001 80164445 l xq...&).xq...&)...............DE

<32 bytes per line>

--//7413-5 = 7408

BBED> set offset +6

OFFSET 7419

BBED> find /c SYS

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 209 Offsets: 7948 to 8011 Dba:0x004000d1

------------------------------------------------------------------------------------------------------------------------------------------------

53595302 c1021044 34433530 31363038 36423244 43364101 8002c104 07787108 180c2629 07787108 180c2629 ffff0180 ff02c102 ffff0180 01801644 45464155

<64 bytes per line>

BBED> dump /v

File: /mnt/ramdisk/book/system01.dbf (1)

Block: 209 Offsets: 7948 to 8011 Dba:0x004000d1

-----------------------------------------------------------------------------------------------------------

53595302 c1021044 34433530 31363038 36423244 43364101 8002c104 07787108 l SYS....D4C5016086B2DC6A......xq.

180c2629 07787108 180c2629 ffff0180 ff02c102 ffff0180 01801644 45464155 l ..&).xq...&)...............DEFAU

<32 bytes per line>

--//7948-5 = 7943

BBED> x /rcncnnttttncnnnnnccnnncct offset 7408

rowdata[4507] @7408

-------------

flag@7408: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@7409: 0x02

cols@7410: 22

ckix@7411: 5

col 0[6] @7412: SYSTEM

col 1[2] @7419: 1

col 2[16] @7422: D4DF7931AB130E37

col 3[1] @7439: 0

col 4[2] @7441: 3

col 5[7] @7444: 2013-08-24 11:37:40

col 6[7] @7452: 2013-08-24 11:37:40

col 7[0] @7460: *NULL*

col 8[0] @7461: *NULL*

col 9[1] @7462: 0

col 10[0] @7464: *NULL*

col 11[2] @7465: 1

col 12[0] @7468: *NULL*

col 13[0] @7469: *NULL*

col 14[1] @7470: 0

col 15[1] @7472: 0

col 16[22] @7474: DEFAULT_CONSUMER_GROUP

col 17[0] @7497: *NULL*

col 18[1] @7498: 0

col 19[0] @7500: *NULL*

col 20[0] @7501: *NULL*

col 21[62] @7502: S:5E9BBAFD8CE1E16065CAF66B09741B79CC45E9EBAAB3F99F34BF5FCE5445

BBED> x /rcncnnttttncnnnnnccnnncct offset 7943

rowdata[5042] @7943

-------------

flag@7943: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@7944: 0x02

cols@7945: 22

ckix@7946: 1

col 0[3] @7947: SYS

col 1[2] @7951: 1

col 2[16] @7954: D4C5016086B2DC6A

col 3[1] @7971: 0

col 4[2] @7973: 3

col 5[7] @7976: 2013-08-24 11:37:40

col 6[7] @7984: 2013-08-24 11:37:40

col 7[0] @7992: *NULL*

col 8[0] @7993: *NULL*

col 9[1] @7994: 0

col 10[0] @7996: *NULL*

col 11[2] @7997: 1

col 12[0] @8000: *NULL*

col 13[0] @8001: *NULL*

col 14[1] @8002: 0

col 15[1] @8004: 0

col 16[22] @8006: DEFAULT_CONSUMER_GROUP

col 17[0] @8029: *NULL*

col 18[1] @8030: 0

col 19[0] @8032: *NULL*

col 20[0] @8033: *NULL*

col 21[62] @8034: S:52F9294AFEF93815CFC0D8F13BA88B935944095E6CD1101ECE75290A383E

--//可以發現2條記錄的偏移在7408,7943.kdbr記錄的是相對偏移從kdbh位置開始,要減去92.

BBED> p kdbh

struct kdbh, 14 bytes @92

ub1 kdbhflag @92 0x00 (NONE)

sb1 kdbhntab @93 2

sb2 kdbhnrow @94 43

sb2 kdbhfrre @96 -1

sb2 kdbhfsbo @98 108

sb2 kdbhfseo @100 2809

sb2 kdbhavsp @102 5516

sb2 kdbhtosp @104 5516

--//7408 - 92 = 7316 , name='SYSTEM'的記錄偏移

--//7943 - 92 = 7851 , name='SYS'的記錄偏移

BBED> assign kdbr[22]=7851

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

sb2 kdbr[0] @158 7851

BBED> assign kdbr[27]=7316

sb2 kdbr[0] @168 7316

BBED> x /rcncnnttttncnnnnnccnnncct *kdbr[27]

rowdata[4507] @7408

-------------

flag@7408: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@7409: 0x02

cols@7410: 22

ckix@7411: 5

col 0[6] @7412: SYSTEM

col 1[2] @7419: 1

col 2[16] @7422: D4DF7931AB130E37

col 3[1] @7439: 0

col 4[2] @7441: 3

col 5[7] @7444: 2013-08-24 11:37:40

col 6[7] @7452: 2013-08-24 11:37:40

col 7[0] @7460: *NULL*

col 8[0] @7461: *NULL*

col 9[1] @7462: 0

col 10[0] @7464: *NULL*

col 11[2] @7465: 1

col 12[0] @7468: *NULL*

col 13[0] @7469: *NULL*

col 14[1] @7470: 0

col 15[1] @7472: 0

col 16[22] @7474: DEFAULT_CONSUMER_GROUP

col 17[0] @7497: *NULL*

col 18[1] @7498: 0

col 19[0] @7500: *NULL*

col 20[0] @7501: *NULL*

col 21[62] @7502: S:5E9BBAFD8CE1E16065CAF66B09741B79CC45E9EBAAB3F99F34BF5FCE5445

BBED> x /rcncnnttttncnnnnnccnnncct *kdbr[22]

rowdata[5042] @7943

-------------

flag@7943: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@7944: 0x02

cols@7945: 22

ckix@7946: 1

col 0[3] @7947: SYS

col 1[2] @7951: 1

col 2[16] @7954: D4C5016086B2DC6A

col 3[1] @7971: 0

col 4[2] @7973: 3

col 5[7] @7976: 2013-08-24 11:37:40

col 6[7] @7984: 2013-08-24 11:37:40

col 7[0] @7992: *NULL*

col 8[0] @7993: *NULL*

col 9[1] @7994: 0

col 10[0] @7996: *NULL*

col 11[2] @7997: 1

col 12[0] @8000: *NULL*

col 13[0] @8001: *NULL*

col 14[1] @8002: 0

col 15[1] @8004: 0

col 16[22] @8006: DEFAULT_CONSUMER_GROUP

col 17[0] @8029: *NULL*

col 18[1] @8030: 0

col 19[0] @8032: *NULL*

col 20[0] @8033: *NULL*

col 21[62] @8034: S:52F9294AFEF93815CFC0D8F13BA88B935944095E6CD1101ECE75290A383E

--OK,修改正確!!

BBED> sum apply

Check value for File 1, Block 209:

current = 0xb8d7, required = 0xb8d7

--//必須禁用索引,從別的機器獲得如下資訊.注意版本一定要一致.

SYS@bookdg> select rowid x,a.* from bootstrap$ a where sql_text like '%I_USER%';

X LINE# OBJ# SQL_TEXT

------------------------------ ------------ ------------ ------------------------------------------------------------

AAAAA7AABAAAAIKAAM 11 11 CREATE INDEX I_USER# ON CLUSTER C_USER# PCTFREE 10 INITRANS

2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS

1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 11 EXTENTS (FILE

1 BLOCK 216))

AAAAA7AABAAAAIKAAT 46 46 CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE 10 INITRA

NS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTEN

TS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 46 EXTENTS (F

ILE 1 BLOCK 416))

AAAAA7AABAAAAIKAAU 47 47 CREATE UNIQUE INDEX I_USER2 ON USER$(USER#,TYPE#,SPARE1,SPAR

E2) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64

K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE

0 OBJNO 47 EXTENTS (FILE 1 BLOCK 424))

SYS@bookdg> @ rowid AAAAA7AABAAAAIKAAT

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------

59 1 522 19 0x40020A 1,522 alter system dump datafile 1 block 522 ;

--//繼續修改:

BBED> set dba 1,522

DBA 0x0040020a (4194826 1,522)

BBED> x /rnnc *kdbr[19]

rowdata[228] @1754

------------

flag@1754: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1755: 0x01

cols@1756: 3

col 0[2] @1757: 46

col 1[2] @1760: 46

col 2[197] @1763: CREATE UNIQUE INDEX I_USER1 ON USER$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS

2147483645 PCTINCREASE 0 OBJNO 46 EXTENTS (FILE 1 BLOCK 416))

--//修改標識flag=0x3c 表示刪除.

BBED> assign offset 1754=0x3c;

ub1 rowdata[0] @1754 0x3c

BBED> sum apply

Check value for File 1, Block 522:

current = 0x1ef8, required = 0x1ef8

4.測試:

SYS@book> startup

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

Database opened.

SYS@book> connect system/oracle

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-00607: Internal error occurred while making a change to a data block

ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [209], [6110], [], [], [], [], [], [], [], []

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

--//必須修復該塊.正好前一陣子做tab$刪除恢復時,別人提醒引數_db_always_check_system_ts=false,可以跳過檢查.測試看看,

SYS@book> alter system set "_db_always_check_system_ts"=false ;

System altered.

SYS@book> startup mount

ORACLE instance started.

Total System Global Area 643084288 bytes

Fixed Size 2255872 bytes

Variable Size 205521920 bytes

Database Buffers 427819008 bytes

Redo Buffers 7487488 bytes

Database mounted.

SYS@book> alter database open ;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 2

ORA-01578: ORACLE data block corrupted (file # 1, block # 209)

ORA-01110: data file 1: '/mnt/ramdisk/book/system01.dbf'

Process ID: 56447

Session ID: 295 Serial number: 3

--//昏,已經標誌壞塊.

BBED> set dba 1,209

DBA 0x004000d1 (4194513 1,209)

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/system01.dbf

BLOCK = 209

Block Checking: DBA = 4194513, Block Type = KTB-managed data block

Found block already marked corrupted

BBED> p kcbh.seq_kcbh

ub1 seq_kcbh @14 0xff

BBED> p tailchk

ub4 tailchk @8188 0x000006ff

BBED> assign tailchk=0x00000601

ub4 tailchk @8188 0x00000601

BBED> sum apply

Check value for File 1, Block 209:

current = 0x016d, required = 0x016d

BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/system01.dbf

BLOCK = 209

Block Checking: DBA = 4194513, Block Type = KTB-managed data block

data header at 0x1414e5c

kdbchk: the amount of space used is not equal to block size

used=2548 fsc=0 avsp=5516 dtl=8096

Block 209 failed with check code 6110

--//繼續看看引數_db_always_check_system_ts=false是否有效.

SYS@book> connect system/oracle

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SYS@book> connect system/manager

Connected.

--//昏,修復有問題,指向最早的位置,不過應該不影響使用.

SYSTEM@book> @ bbvi 1 209

BVI_COMMAND

----------------------------------------------------------------------------------------------------

bvi -b 1712128 -s 8192 /mnt/ramdisk/book/system01.dbf

xxd -c16 -g 2 -s 1712128 -l 8192 /mnt/ramdisk/book/system01.dbf

dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=209 count=1 of=1_209.dd conv=notrunc 2>/dev/null

od -j 1712128 -N 8192 -t x1 -v /mnt/ramdisk/book/system01.dbf

hexdump -s 1712128 -n 8192 -C -v /mnt/ramdisk/book/system01.dbf

alter system dump datafile '/mnt/ramdisk/book/system01.dbf' block 209;

alter session set events 'immediate trace name set_tsn_p1 level 1';

alter session set events 'immediate trace name buffer level 4194513';

9 rows selected.

$ dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=209 count=1 of=1_209.dd conv=notrunc 2>/dev/null

$ strings -td -3 1_209.dd | grep SYS

2276 SYSTEM

2433 SYSTEM

2591 SYSTEM

2748 SYSTEM

2906 SYSTEMDW

3079 SYSDW

3249 SYSTEM

3420 SYSTEM

3592 SYSTEM

3763 SYSTEM

3935 SYSTEM

4106 SYSTEM

4278 SYSTEM

4449 SYSTEM

4621 SYSTEM

4792 SYSTEM

4964 SYSTEM

5135 SYS

5303 SYSTEM

5475 SYS

5644 GATHER_SYSTEM_STATISTICS

7413 SYSTEM

7948 SYS

BBED> x /rcncnnttttncnnnnnccnnncct offset 5298

rowdata[3027] @5298

-------------

flag@5298: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@5299: 0x02

cols@5300: 22

ckix@5301: 5

col 0[6] @5302: SYSTEM

col 1[2] @5309: 1

col 2[16] @5312: 2D594E86F93B17A1

col 3[1] @5329: 0

col 4[2] @5331: 3

col 5[7] @5334: 2013-08-24 11:37:40

col 6[7] @5342: 2015-11-24 09:13:26

col 7[7] @5350: 2013-08-24 12:07:04

col 8[7] @5358: 2013-08-24 12:07:04

col 9[1] @5366: 0

col 10[0] @5368: *NULL*

col 11[2] @5369: 1

col 12[0] @5372: *NULL*

col 13[0] @5373: *NULL*

col 14[2] @5374: 9

~~~~~~~~~~~~~~~~~~~~

col 15[1] @5377: 0

col 16[22] @5379: DEFAULT_CONSUMER_GROUP

col 17[0] @5402: *NULL*

col 18[1] @5403: 0

col 19[0] @5405: *NULL*

col 20[0] @5406: *NULL*

col 21[62] @5407: S:574F64CF334589628C7754569B8C88C0A2000C907997A2E079AD97B2B0E3

BBED> x /rcncnnttttncnnnnnccnnncct offset 4959

rowdata[2688] @4959

-------------

flag@4959: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)

lock@4960: 0x00

cols@4961: 22

ckix@4962: 5

col 0[6] @4963: SYSTEM

col 1[2] @4970: 1

col 2[16] @4973: 2D594E86F93B17A1

col 3[1] @4990: 0

col 4[2] @4992: 3

col 5[7] @4995: 2013-08-24 11:37:40

col 6[7] @5003: 2015-11-24 09:13:26

col 7[7] @5011: 2013-08-24 12:07:04

col 8[7] @5019: 2013-08-24 12:07:04

col 9[1] @5027: 0

col 10[0] @5029: *NULL*

col 11[2] @5030: 1

col 12[0] @5033: *NULL*

col 13[0] @5034: *NULL*

col 14[1] @5035: 0

~~~~~~~~~~~~~~~~~~~~~

col 15[1] @5037: 0

col 16[22] @5039: DEFAULT_CONSUMER_GROUP

col 17[0] @5062: *NULL*

col 18[1] @5063: 0

col 19[0] @5065: *NULL*

col 20[0] @5066: *NULL*

col 21[62] @5067: S:574F64CF334589628C7754569B8C88C0A2000C907997A2E079AD97B2B0E3

--//一些塊沒有恢復,不想做了,對應索引也沒有修正,下午繼續看看.

[20190531]ORA-600 kokasgi1故障模擬與恢復.txt

相關文章