首页 > 代码库 > 数据库不能open下查看undo段的名字

数据库不能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 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 ));

 

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

3dump控制文件

www.htz.pw > oradebug setmypid

Statement 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=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

4bbed来查看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

_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