sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-00600 4194产生原因和解决办法

时间:2016-10-24 20:49   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映测试数据库在启动时报错ORA-00600 4194,分析原因为undo异常导致。

1,数据库版本

oracleplus.net>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,查看undo$的定义信息

create table undo$ /* undo segment table */
( us# number not null, /* undo segment number */
name varchar2(“M_IDEN”) not null, /* name of this undo segment */
user# number not null, /* owner: 0 = SYS(PRIVATE), 1 = PUBLIC */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
scnbas number, /* highest commit time in rollback segment */
scnwrp number, /* scnbas – scn base, scnwrp – scn wrap */
xactsqn number, /* highest transaction sequence number */
undosqn number, /* highest undo block sequence number */
inst# number, /* parallel server instance that owns the segment */
status$ number not null, /* segment status (see KTS.H): */
/* 1 = INVALID, 2 = AVAILABLE, 3 = IN USE, 4 = OFFLINE, 5 = NEED RECOVERY,
* 6 = PARTLY AVAILABLE (contains in-doubt txs)
*/
ts# number, /* tablespace number */
ugrp# number, /* The undo group it belongs to */
keep number,
optimal number,
flags number,
spare1 number,
spare2 number,
spare3 number,
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date
)


CREATE TABLE UNDO$
(
“US#” NUMBER NOT NULL,
“NAME” VARCHAR2 (30) NOT NULL,
“USER#” NUMBER NOT NULL,
“FILE#” NUMBER NOT NULL,
“BLOCK#” NUMBER NOT NULL,
“SCNBAS” NUMBER,
“SCNWRP” NUMBER,
“XACTSQN” NUMBER,
“UNDOSQN” NUMBER,
“INST#” NUMBER,
“STATUS$” NUMBER NOT NULL,
“TS#” NUMBER,
“UGRP#” NUMBER,
“KEEP” NUMBER,
“OPTIMAL” NUMBER,
“FLAGS” NUMBER,
“SPARE1” NUMBER,
“SPARE2” NUMBER,
“SPARE3” NUMBER,
“SPARE4” VARCHAR2 (1000),
“SPARE5” VARCHAR2 (1000),
“SPARE6” DATE
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 64 K
NEXT 1024 K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
OBJNO 15
EXTENTS ( FILE 1 BLOCK 224 ));

标注为绿色部分的信息是我们需要使用的

3,dump控制文件

oracleplus.net>oradebug setmypid
Statement processed.
oracleplus.net>oradebug dump controlf 4;
Statement processed.
oracleplus.net>oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_13293.trc

以SYSTEM来查找

TABLESPACE #0 SYSTEM: recno=1
First datafile link=1 Tablespace Flag=0
Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00
Tablespace PITR last completion scn: 0x0000.00000000 01/01/1988 00:00:00

以tablespace 0来查找
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max = 100, section in-use = 5,
last-recid= 225, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
name #8: /oracle/app/oracle/oradata/orcl1124/system01.dbf
creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1


tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:322 scn: 0x0000.001bca2a 05/14/2014 23:21:31
Stop scn: 0xffff.ffffffff 05/14/2014 23:20:23
Creation Checkpointed at scn: 0x0000.00000007 08/24/2013 11:37:33
thread:0 rba:(0x0.0.0)

下面是undo的信息

TABLESPACE #2 UNDOTBS1: recno=3
First datafile link=3 Tablespace Flag=0
Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00
Tablespace PITR last completion scn: 0x0000.00000000 01/01/1988 00:00:00

UNDO表空间的TS#=2

4,bbed来查看UNDO段的名字

BBED> set block 225
BLOCK# 225

BBED> map
File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)
Block: 225 Dba:0x00000000
————————————————————
KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

struct ktbbh, 48 bytes @20

struct kdbh, 14 bytes @68

struct kdbt[1], 4 bytes @82

sb2 kdbr[21] @86

ub1 freespace[4075] @128

ub1 rowdata[3985] @4203

ub4 tailchk @8188


BBED> p kdbr
sb2 kdbr[0] @86 8078
sb2 kdbr[1] @88 5083
sb2 kdbr[2] @90 5015
sb2 kdbr[3] @92 4947
sb2 kdbr[4] @94 4270
sb2 kdbr[5] @96 4812
sb2 kdbr[6] @98 4135
sb2 kdbr[7] @100 4676
sb2 kdbr[8] @102 4609
sb2 kdbr[9] @104 4541
sb2 kdbr[10] @106 4472
sb2 kdbr[11] @108 5877
sb2 kdbr[12] @110 5814
sb2 kdbr[13] @112 5748
sb2 kdbr[14] @114 5682
sb2 kdbr[15] @116 5616
sb2 kdbr[16] @118 5550
sb2 kdbr[17] @120 5484
sb2 kdbr[18] @122 5418
sb2 kdbr[19] @124 5352
sb2 kdbr[20] @126 5286


x /rnc *kdbr[0]
x /rnc *kdbr[1]
x /rnc *kdbr[2]
x /rnc *kdbr[3]
x /rnc *kdbr[4]
x /rnc *kdbr[5]
x /rnc *kdbr[6]
x /rnc *kdbr[7]
x /rnc *kdbr[8]
x /rnc *kdbr[9]
x /rnc *kdbr[10]
x /rnc *kdbr[11]
x /rnc *kdbr[12]
x /rnc *kdbr[13]
x /rnc *kdbr[14]
x /rnc *kdbr[15]
x /rnc *kdbr[16]
x /rnc *kdbr[17]
x /rnc *kdbr[18]
x /rnc *kdbr[19]
x /rnc *kdbr[20]

将结果输出到文件。
以UE打开,可以得到下面的结果,或者 grep就可以,这里还需要注意的是表空间的

col 1[20] @5157: _SYSSMU1_3724004606$
col 1[20] @5089: _SYSSMU2_2996391332$
col 1[20] @5021: _SYSSMU3_1723003836$
col 1[20] @4344: _SYSSMU4_1254879796$
col 1[20] @4209: _SYSSMU6_1263032392$
col 1[20] @4750: _SYSSMU7_2070203016$
col 1[20] @4615: _SYSSMU9_1650507775$
col 1[21] @4546: _SYSSMU10_1197734989$
col 1[20] @5951: _SYSSMU11_894599432$
col 1[21] @5888: _SYSSMU12_1573055333$
col 1[21] @5822: _SYSSMU13_3860906822$
col 1[21] @5756: _SYSSMU14_3319140121$
col 1[21] @5690: _SYSSMU15_1436577151$
col 1[21] @5624: _SYSSMU16_1689093467$
col 1[21] @5558: _SYSSMU17_1049158485$
col 1[21] @5492: _SYSSMU18_1557221903$
col 1[21] @5426: _SYSSMU19_2284825117$
col 1[21] @5360: _SYSSMU20_2312497597$

5,使用strings的方法

这种方法最简单,但是不能区别UNDO段的表空间,并且会将块中所有的UNDO段的段名显示,包括已经被删除的UNDO段
[oracle@www.htz.pw sql]$dd if=/oracle/app/oracle/oradata/orcl1124/system01.dbf of=/soft/test.dbf skip=224 count=8 bs=8192
8+0 records in
8+0 records out

[oracle@www.htz.pw soft]$strings test.dbf |grep SYSSM|sort -u
_SYSSMU10_1197734989$
_SYSSMU10_3470984480$
_SYSSMU11_894599432$
_SYSSMU12_1573055333$
_SYSSMU1_2603659607$
_SYSSMU13_3860906822$
_SYSSMU1_3724004606$
_SYSSMU14_3319140121$
_SYSSMU15_1436577151$
_SYSSMU16_1689093467$
_SYSSMU17_1049158485$
_SYSSMU18_1557221903$
_SYSSMU19_2284825117$
_SYSSMU20_2312497597$
_SYSSMU2_2996391332$
_SYSSMU2_73114111$
_SYSSMU3_1723003836$
_SYSSMU3_596277271$
_SYSSMU4_1254879796$
_SYSSMU4_2523322691$
_SYSSMU5_4008018903$
_SYSSMU5_898567397$
_SYSSMU6_1263032392$
_SYSSMU6_4235600416$
_SYSSMU7_2070203016$
_SYSSMU7_2271882308$
_SYSSMU8_517538920$
_SYSSMU8_854328387$
_SYSSMU9_1650507775$
_SYSSMU9_508477954$

6,使用第三方抽数据工具

这里使用的ODU来测试
[oracle@www.htz.pw odu]$cat control.txt
#ts fno rfno filename block_size is_big_file header_offset blocks
0 0 0 /oracle/app/oracle/oradata/orcl1124/system01.dbf
这里我只写了SYSTEM表空间,因为UNDO$在SYSTEM表空间中

[oracle@www.htz.pw odu]$./odu

ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 208
TABLE OBJ$ file_no: 1 block_no: 240
CLUSTER C_OBJ# file_no: 1 block_no: 144
CLUSTER C_OBJ# file_no: 1 block_no: 144
found IND$’s obj# 19
found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found TABPART$’s obj# 591
found TABPART$’s dataobj#:591,ts#:0,file#:1,block#:4000,tab#:0
found INDPART$’s obj# 596
found INDPART$’s dataobj#:596,ts#:0,file#:1,block#:4040,tab#:0
found TABSUBPART$’s obj# 603
found TABSUBPART$’s dataobj#:603,ts#:0,file#:1,block#:4096,tab#:0
found INDSUBPART$’s obj# 608
found INDSUBPART$’s dataobj#:608,ts#:0,file#:1,block#:4136,tab#:0
found IND$’s obj# 19
found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3
found LOB$’s obj# 80
found LOB$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6
found LOBFRAG$’s obj# 624
found LOBFRAG$’s dataobj#:624,ts#:0,file#:1,block#:4264,tab#:0


ODU> unload table sys.undo$

Unloading table: UNDO$,object ID: 15
Unloading segment,storage(Obj#=15 DataObj#=15 TS#=0 File#=1 Block#=224 Cluster=0)
21 rows unloaded


[oracle@www.htz.pw odu]$cd data
[oracle@www.htz.pw data]$cat SYS_UNDO$.txt
0|SYSTEM|0|1|128|0|0|0|0|0|3|0|||||0
1|_SYSSMU1_3724004606$|1|3|128|1774525|0|862|222|0|3|2|||||2
2|_SYSSMU2_2996391332$|1|3|144|1774529|0|1083|212|0|3|2|||||2
3|_SYSSMU3_1723003836$|1|3|160|1774527|0|1077|217|0|3|2|||||2
4|_SYSSMU4_1254879796$|1|3|176|1774531|0|928|300|0|3|2|||||2
5|_SYSSMU5_898567397$|1|3|192|1774515|0|1075|229|0|3|2|||||2
6|_SYSSMU6_1263032392$|1|3|208|1774519|0|1262|286|0|3|2|||||2
7|_SYSSMU7_2070203016$|1|3|224|1774517|0|868|179|0|3|2|||||2
8|_SYSSMU8_517538920$|1|3|240|1774523|0|1105|335|0|3|2|||||2
9|_SYSSMU9_1650507775$|1|3|256|1774521|0|1088|402|0|3|2|||||2
10|_SYSSMU10_1197734989$|1|3|272|1774533|0|866|238|0|3|2|||||2
11|_SYSSMU11_894599432$|1|5|128|923330|0|2|1|0|1|5|||||2
12|_SYSSMU12_1573055333$|1|5|144|0|0|1|1|0|1|5|||||2
13|_SYSSMU13_3860906822$|1|5|160|923661|0|2|1|0|1|5|||||2
14|_SYSSMU14_3319140121$|1|5|176|923323|0|2|1|0|1|5|||||2
15|_SYSSMU15_1436577151$|1|5|192|923332|0|2|1|0|1|5|||||2
16|_SYSSMU16_1689093467$|1|5|208|923314|0|2|1|0|1|5|||||2
17|_SYSSMU17_1049158485$|1|5|224|923296|0|2|1|0|1|5|||||2
18|_SYSSMU18_1557221903$|1|5|240|923320|0|2|1|0|1|5|||||2
19|_SYSSMU19_2284825117$|1|5|256|923294|0|2|1|0|1|5|||||2
20|_SYSSMU20_2312497597$|1|5|272|923262|0|2|1|0|1|5|||||2

在DUMP的控制文件中我们能看到下面的信息
TABLESPACE #2 UNDOTBS1: recno=3
First datafile link=3 Tablespace Flag=0
Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00
Tablespace PITR last completion scn: 0x0000.00000000 01/01/1988 00:00:00

UNDO表空间的TS#=2


[oracle@www.htz.pw data]$awk -F\| ‘{ print $2,$12}’ SYS_UNDO$.txt
SYSTEM 0
_SYSSMU1_3724004606$ 2
_SYSSMU2_2996391332$ 2
_SYSSMU3_1723003836$ 2
_SYSSMU4_1254879796$ 2
_SYSSMU5_898567397$ 2
_SYSSMUOracleо6_1263032392$ 2
_SYSSMU7_2070203016$ 2
_SYSSMU8_517538920$ 2
_SYSSMU9_1650507775$ 2
_SYSSMU10_1197734989$ 2
_SYSSMU11_894599432$ 5
_SYSSMU12_1573055333$ 5
_SYSSMU13_3860906822$ 5
_SYSSMU14_3319140121$ 5
_SYSSMU15_1436577151$ 5
_SYSSMU16_1689093467$ 5
_SYSSMU17_1049158485$ 5
_SYSSMU18_1557221903$ 5
_SYSSMU19_2284825117$ 5
_SYSSMU20_2312497597$ 5
本文固定链接: http://www.htz.pw/2014/05/15/%e6%95%b0%e6%8d%ae%e5%ba%93%e4%b8%8d%e8%83%bdopen%e4%b8%8b%e6%9f%a5%e7%9c%8bundo%e6%ae%b5%e7%9a%84%e5%90%8d%e5%ad%97.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-00600 4194产生原因和解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1055.html

Oracle研究中心

关键词:

ORA-00600

如何查看Oralce undo段的名字

Oracle报错ORA-00600 4194

Oracle数据库不能open下查看undo段的名字