我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ,欢迎关注收藏,也欢迎大家转载,但是请在文章开始地方标注文章出处,谢谢!
由于博客中有大量代码,通过页面浏览效果更佳。
Oracle故障分析:数据库不能open下查看undo段的名字
下面的测试来至于今天群里面一个朋友,open数据库的时候遇到了ORA-00600 4194错误,这个错误比较常见,并且处理方法也很简单。但是在修改参数的时候,不知道怎么去查看UNDO段的名字。下面简单的测试一把
欢迎大家加入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 ProductionPL/SQL Release 11.2.0.4.0 – ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 – ProductionNLSRTL 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 10PCTUSED 40INITRANS 1MAXTRANS 255STORAGE (INITIAL 64 KNEXT 1024 KMINEXTENTS 1MAXEXTENTS 2147483645PCTINCREASE 0OBJNO 15EXTENTS ( FILE 1 BLOCK 224 ));
标注为绿色部分的信息是我们需要使用的
3,dump控制文件
www.htz.pw > oradebug setmypidStatement processed.www.htz.pw > oradebug dump controlf 4;Statement processed.www.htz.pw > oradebug tracefile_name;/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_13293.trc以SYSTEM来查找TABLESPACE #0 SYSTEM: recno=1First datafile link=1 Tablespace Flag=0Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00Tablespace 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.dbfcreation size=0 block size=8192 status=0xe head=8 tail=8 dup=1 tablespace 0, index=1 krfil=1 prev_file=0unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00Checkpoint cnt:322 scn: 0x0000.001bca2a 05/14/2014 23:21:31Stop scn: 0xffff.ffffffff 05/14/2014 23:20:23Creation Checkpointed at scn: 0x0000.00000007 08/24/2013 11:37:33thread:0 rba:(0x0.0.0)下面是undo的信息TABLESPACE #2 UNDOTBS1: recno=3First datafile link=3 Tablespace Flag=0Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00Tablespace PITR last completion scn: 0x0000.00000000 01/01/1988 00:00:00
UNDO表空间的TS#=2
4,bbed来查看UNDO段的名字
BBED> set block 225BLOCK# 225BBED> mapFile: /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 kdbrsb2 kdbr[0] @86 8078sb2 kdbr[1] @88 5083sb2 kdbr[2] @90 5015sb2 kdbr[3] @92 4947sb2 kdbr[4] @94 4270sb2 kdbr[5] @96 4812sb2 kdbr[6] @98 4135sb2 kdbr[7] @100 4676sb2 kdbr[8] @102 4609sb2 kdbr[9] @104 4541sb2 kdbr[10] @106 4472sb2 kdbr[11] @108 5877sb2 kdbr[12] @110 5814sb2 kdbr[13] @112 5748sb2 kdbr[14] @114 5682sb2 kdbr[15] @116 5616sb2 kdbr[16] @118 5550sb2 kdbr[17] @120 5484sb2 kdbr[18] @122 5418sb2 kdbr[19] @124 5352sb2 kdbr[20] @126 5286x /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=81928+0 records in8+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 blocks0 0 0 /oracle/app/oracle/oradata/orcl1124/system01.dbf
这里我只写了SYSTEM表空间,因为UNDO$在SYSTEM表空间中
[oracle@www.htz.pw odu]$./oduODU> unload dictCLUSTER C_USER# file_no: 1 block_no: 208TABLE OBJ$ file_no: 1 block_no: 240CLUSTER C_OBJ# file_no: 1 block_no: 144CLUSTER C_OBJ# file_no: 1 block_no: 144found IND$’s obj# 19found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3found TABPART$’s obj# 591found TABPART$’s dataobj#:591,ts#:0,file#:1,block#:4000,tab#:0found INDPART$’s obj# 596found INDPART$’s dataobj#:596,ts#:0,file#:1,block#:4040,tab#:0found TABSUBPART$’s obj# 603found TABSUBPART$’s dataobj#:603,ts#:0,file#:1,block#:4096,tab#:0found INDSUBPART$’s obj# 608found INDSUBPART$’s dataobj#:608,ts#:0,file#:1,block#:4136,tab#:0found IND$’s obj# 19found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3found LOB$’s obj# 80found LOB$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6found LOBFRAG$’s obj# 624found LOBFRAG$’s dataobj#:624,ts#:0,file#:1,block#:4264,tab#:0ODU> unload table sys.undo$Unloading table: UNDO$,object ID: 15Unloading 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$.txt0|SYSTEM|0|1|128|0|0|0|0|0|3|0|||||01|_SYSSMU1_3724004606$|1|3|128|1774525|0|862|222|0|3|2|||||22|_SYSSMU2_2996391332$|1|3|144|1774529|0|1083|212|0|3|2|||||23|_SYSSMU3_1723003836$|1|3|160|1774527|0|1077|217|0|3|2|||||24|_SYSSMU4_1254879796$|1|3|176|1774531|0|928|300|0|3|2|||||25|_SYSSMU5_898567397$|1|3|192|1774515|0|1075|229|0|3|2|||||26|_SYSSMU6_1263032392$|1|3|208|1774519|0|1262|286|0|3|2|||||27|_SYSSMU7_2070203016$|1|3|224|1774517|0|868|179|0|3|2|||||28|_SYSSMU8_517538920$|1|3|240|1774523|0|1105|335|0|3|2|||||29|_SYSSMU9_1650507775$|1|3|256|1774521|0|1088|402|0|3|2|||||210|_SYSSMU10_1197734989$|1|3|272|1774533|0|866|238|0|3|2|||||211|_SYSSMU11_894599432$|1|5|128|923330|0|2|1|0|1|5|||||212|_SYSSMU12_1573055333$|1|5|144|0|0|1|1|0|1|5|||||213|_SYSSMU13_3860906822$|1|5|160|923661|0|2|1|0|1|5|||||214|_SYSSMU14_3319140121$|1|5|176|923323|0|2|1|0|1|5|||||215|_SYSSMU15_1436577151$|1|5|192|923332|0|2|1|0|1|5|||||216|_SYSSMU16_1689093467$|1|5|208|923314|0|2|1|0|1|5|||||217|_SYSSMU17_1049158485$|1|5|224|923296|0|2|1|0|1|5|||||218|_SYSSMU18_1557221903$|1|5|240|923320|0|2|1|0|1|5|||||219|_SYSSMU19_2284825117$|1|5|256|923294|0|2|1|0|1|5|||||220|_SYSSMU20_2312497597$|1|5|272|923262|0|2|1|0|1|5|||||2
在DUMP的控制文件中我们能看到下面的信息
TABLESPACE #2 UNDOTBS1: recno=3First datafile link=3 Tablespace Flag=0Tablespace PITR mode start scn: 0x0000.00000000 01/01/1988 00:00:00Tablespace 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$.txtSYSTEM 0_SYSSMU1_3724004606$ 2_SYSSMU2_2996391332$ 2_SYSSMU3_1723003836$ 2_SYSSMU4_1254879796$ 2_SYSSMU5_898567397$ 2_SYSSMU6_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
------------------作者介绍-----------------------
姓名:黄廷忠
现就职:Oracle中国高级服务团队
曾就职:OceanBase、云和恩墨、东方龙马等
电话、微信、QQ:18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)