查看: 1994|回复: 0

[原创] ORA-01555 Duration=0 sec ORA-00600: [2663] 问题处理

[复制链接]
论坛徽章:
21
娜美
日期:2017-06-26 15:18:15火眼金睛
日期:2018-04-30 22:00:00目光如炬
日期:2018-07-29 22:00:00火眼金睛
日期:2018-08-31 22:00:00目光如炬
日期:2018-09-02 22:00:00目光如炬
日期:2018-09-16 22:00:01火眼金睛
日期:2018-09-30 22:00:00目光如炬
日期:2018-10-14 22:00:00火眼金睛
日期:2018-11-30 22:00:01目光如炬
日期:2018-04-29 22:00:00
发表于 2018-7-23 10:21 | 显示全部楼层 |阅读模式
本帖最后由 sunyunyi 于 2018-7-23 14:27 编辑

作者简介:
----------------------------------------------------
@ 孙显鹏,海天起点oracle技术专家,十年从业经验
@ 精通oracle内部原理,擅长调优和解决疑难问题
@ 致力于帮助客户解决生产中的问题,提高生产效率。
@ 爱好:书法,周易,中医。微信:sunyunyi_sun
@ 易曰:精义入神,以致用也!
@ 调优乃燮理阴阳何其难也!
-----------------------------------------------------

问题简述

客户那里出现的由于oracle bug 问题在DG切换时数据块ITL中scn 异常导致访问数据块报错:
ORA-01555 caused by SQL statement below (SQL ID: fmqj0z5ynrqkd, Query Duration=0 sec, SCN: 0x059c.67ff1b2c):
ora-01555 快照过旧:回滚段15(名称 "_SYSSMU15_3567263981$") 过小
ORA-00600: 内部错误代码, 参数: [2663], [1436], [1734882023], [1436], [2895035744], [], [], [], [], [], [], []

希望基于该问题让大家明白oracle 事务层和undo的关系,所有做了下面测试:
首先我们回忆数据块事务层ITL相关信息:
FSC ---快速清除
SCN ---延迟清除
flag:
---- = transaction is active, or committedpending cleanout
C--- = transaction has been committed andlocks cleaned out
-B-- = this undo record contains the undofor this ITL entry
--U- = transaction committed (maybe longago); SCN is an upper bound
---T = transaction was still active atblock cleanout SCN

模拟测试
说明:该测试涉及块清除,ITL和undo事务管理以及BBED如何修改块中内容

环境:--oracle事务管理从9i开始没什么变化
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
create tablespace data datafile '/u01/oradata/data01.dbf' size 4g;
conn wolf/wolf
create table tb_logic ( id number) tablespace data
/
insert into tb_logic values (1)
/
首先需要明白oracle的块清除
快速块清除:
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from tb_logic;
ID
----------
2
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 8 block 134;
System altered.
Block dump from disk:
buffer tsn: 9 rdba: 0x02000086 (8/134)
scn: 0x6ef868 seq: 0x01 flg: 0x04 tail: 0xf8680601
frmt: 0x02 chkval: 0xd461 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006ef868 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.01b.0000150e 0x01800a29.0368.20 C--- 0 scn 0x00000000006eea4d
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 C-U- 0 scn 0x00000000006ef4f3
bdba: 0x02000086

SQL> update tb_logic set id=1 where id=2;
1 row updated.
SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 8 block 134;
System altered.

Block dump from disk:
buffer tsn: 9 rdba: 0x02000086 (8/134)
scn: 0x6eff67 seq: 0x02 flg: 0x06 tail: 0xff670602 --SCN号0x6eff67
frmt: 0x02 chkval: 0xe3ef type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006ef868 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0027.004.000014eb 0x01800b24.0301.18 --U- 1 fsc 0x0000.006eff67 --快速清除
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 C-U- 0 scn 0x00000000006ef4f3
bdba: 0x02000086
注意 ITL 1的lck标志还是1
行的SCN也可以这样查询:
SQL> select to_char(ora_rowscn,'xxxxxxxxxxxxxxxxxxxxxx') from tb_logic;
TO_CHAR(ORA_ROWSCN,'XXXXXXXXXXXXXXXXXXXXXX')
---------------------------------------------------------------------
6eff67

延迟块清除:
truncate table tb_logic
insert /*+ append */ into tb_logic values (1)
/
commit;
update tb_logic set id=2 where id=1;
Block dump from disk:
buffer tsn: 9 rdba: 0x02000086 (8/134)
scn: 0x6e9234 seq: 0x02 flg: 0x04 tail: 0x92340602
frmt: 0x02 chkval: 0xbfaf type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9234 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02000086
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 8 block 134;
System altered.
Block dump from disk:
buffer tsn: 9 rdba: 0x02000086 (8/134)
scn: 0x6eef44 seq: 0x01 flg: 0x04 tail: 0xef440601
frmt: 0x02 chkval: 0x9595 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006eef00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.01b.0000150e 0x01800a29.0368.20 C--- 0 scn 0x00000000006eea4d ---/append/ 延时清除
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 ---- 1 fsc 0x0000.00000000
bdba: 0x02000086
SQL> commit;
Commit complete.
SQL> alter system dump datafile 8 block 134;
System altered.
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006eef00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.01b.0000150e 0x01800a29.0368.20 C--- 0 scn 0x00000000006eea4d
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 ---- 1 fsc 0x0000.00000000

select to_number('0026','xxxxxx') from dual;
TO_NUMBER('0026','XXXXXX')
--------------------------
38
select * from v$rollname where USN=38
USN NAME CON_ID
---------- ------------------------------------------------------------------------------------------ ----------
38 _SYSSMU38_4225799204$ 0

select SEGMENT_NAME,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='_SYSSMU38_4225799204$';
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------- --------------- ----------- ------------
_SYSSMU38_4225799204$ UNDOTBS2 6 272

alter system dump datafile 6 block 272;
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.02c3.11 ext: 0x2 spc: 0x17ec
uba: 0x00000000.02c3.35 ext: 0x2 spc: 0x658
uba: 0x00000000.02c3.35 ext: 0x2 spc: 0x728
uba: 0x00000000.02ba.17 ext: 0x2 spc: 0x1528
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x1b 10 0x80 0x14af 0x0002 0x00000000006eeef1 0x01800ceb 0x0000.000.00000000 0x00000001 0x00000000 0
0x1c 9 0x00 0x14af 0x0013 0x00000000006eede2 0x01800cea 0x0000.000.00000000 0x00000001 0x00000000 1531236572
0x1b 是活动的,因为undo segment 还在buffer中,强制检查点,写入磁盘:SCN=0x00000000006eeef1
alter system checkpoint;
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x1a 9 0x00 0x14a8 0x0014 0x00000000006eee14 0x01800cea 0x0000.000.00000000 0x00000001 0x00000000 1531236572
0x1b 9 0x00 0x14af 0xffff 0x00000000006ef3d2 0x01800ceb 0x0000.000.00000000 0x00000001 0x00000000 1531237247
0x1b SLOT 已经提交,说明进行了延迟块清除,下面的数据块ITL信息没有发生改变。SCN=0x00000000006ef3d2

Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006eef00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.01b.0000150e 0x01800a29.0368.20 C--- 0 scn 0x00000000006eea4d
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 ---- 1 fsc 0x0000.00000000
bdba: 0x02000086
data_block_dump,data header at 0x7fd14c2b1064

select * from tb_logic;
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006eef00 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.01b.0000150e 0x01800a29.0368.20 C--- 0 scn 0x00000000006eea4d
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 ---- 1 fsc 0x0000.00000000 --没有改变,因为数据块没有写入磁盘
bdba: 0x02000086
data_block_dump,data header at 0x7fd14c2b1064

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 8 block 134;
System altered.
*** 2018-07-10T23:51:28.180550+08:00
Start dump data blocks tsn: 9 file#:8 minblk 134 maxblk 134
Block dump from cache:
Dump of buffer cache at level 4 for pdb=0 tsn=9 rdba=33554566
Block dump from disk:
buffer tsn: 9 rdba: 0x02000086 (8/134)
scn: 0x6ef868 seq: 0x01 flg: 0x04 tail: 0xf8680601 ---scn: 0x6ef868 基础SCN号
frmt: 0x02 chkval: 0xd461 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x02000086
Object id on Block? Y
seg/obj: 0x15238 csc: 0x00000000006ef868 itc: 2 flg: E typ: 1 - DATA -- csc: 0x00000000006ef868 块清除时的SCN
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0024.01b.0000150e 0x01800a29.0368.20 C--- 0 scn 0x00000000006eea4d
0x02 0x0026.01b.000014af 0x01800ceb.02c3.12 C-U- 0 scn 0x00000000006ef4f3 --发生了延迟块清除,事务提交的SCN号
注意:
SCN=0x00000000006ef3d2 --这是undo段头 0x1b solt 记录的提交时的SCN
scn 0x00000000006ef4f3 --这是数据块ITL延迟块清除后记录的SCN,这个SCN比上面的SCN大,C-U-标志中的U代表取最大的SCN,有个计算公式

下面我们修改数据块SCN/FSC值,模拟客户遇到的问题:(这里可以明白事务和undo的关系–关于这个更详细讲解可以关注我的视频讲解)
修改SCN/FSC:
alter system checkpoint;
select
dbms_rowid.rowid_relative_fno(rowid) ralative_fno,
dbms_rowid.rowid_block_number(rowid) block_number
from TB_LOGIC where id=1;
RALATIVE_FNO BLOCK_NUMBER
------------ ------------
8 133
TRACE_FILE
----------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7638.trc
alter system dump datafile 8 block 133;
trace file:
Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9234 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
commit;
alter system checkpoint;
alter system dump datafile 8 block 133;
Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9234 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02000085
alter system flush buffer_cache;
Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9234 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a --U- 1 fsc 0x0000.006e9355 ---快速块清除
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02000085
bbed:

select file#||' '||name||' '||bytes from v$datafile;
1 +DATA/ORCL/DATAFILE/system.491.973940703 1178599424
2 +DATA/ORCL/DATAFILE/sysaux.498.973940721 1251999744
3 +DATA/ORCL/DATAFILE/undotbs1.497.973940735 168820736
4 +DATA/ORCL/DATAFILE/users.495.973940759 5242880
5 +DATA/ORCL/DATAFILE/test.493.975509441 1590689792
6 +DATA/ORCL/DATAFILE/undotbs2.426.978082105 3818913792
7 +DATA/ORCL/DATAFILE/bftbs.412.979816939 52428800
8 /u01/oradata/data01.dbf 4294967296
vi listfile.log
8 /u01/oradata/data01.dbf 4294967296

BBED> set filename '/u01/oradata/data01.dbf' blocksize 8192 mode edit listfile '/home/oracle/listfile.log'
FILENAME /u01/oradata/data01.dbf
BLOCKSIZE 8192
MODE Edit
LISTFILE /home/oracle/listfile.log

BBED> set dba 8,133
DBA 0x02000085 (33554565 8,133)
BBED> p ktbbh
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00015229
ub4 ktbbhod1 @24 0x00015229
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x006e9234 ---csc scn
ub2 kscnwrp @32 0x8000
ub2 kscnwrp2 @34 0x0000
sb2 ktbbhict @36 2
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x02000080
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x0020
ub2 kxidslt @46 0x0003
ub4 kxidsqn @48 0x000014e7
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x018005c2
ub2 kubaseq @56 0x02cb
ub1 kubarec @58 0x0a
ub2 ktbitflg @60 0x2001 (KTBFUPB)
union _ktbitun, 2 bytes @62
sb2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x006e9355 -----ITL中的SCN
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
sb2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000

BBED> d offset 64 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 64 to 71 Dba:0x02000085
------------------------------------------------------------------------
55936e00 00000000
<32 bytes per line>
BBED> m /x ffff offset 64
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 64 to 71 Dba:0x02000085
------------------------------------------------------------------------
ffff6e00 00000000
<32 bytes per line>
BBED> d offset 66 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 66 to 73 Dba:0x02000085
------------------------------------------------------------------------
6e000000 00000000
<32 bytes per line>
BBED> m /x edff offset 66
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 66 to 73 Dba:0x02000085
------------------------------------------------------------------------
edff0000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 8, Block 133:
current = 0x371f, required = 0x371f
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133

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

SQL> select * from tb_logic;
ID
----------
1
---没有报错,因为undo segment solt 存在所以不会报错!!

Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9234 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a --U- 1 fsc 0x0000.ffedffff
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02000085
SQL> update tb_logic set id=1 where id=1;
1 row updated.
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 8 block 133;
System altered.
Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9f92 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a C-U- 0 scn 0x00000000006e9989 ---oracle 修改了该SCN为正常的SCN。
0x02 0x001f.002.00001539 0x01801000.02a6.1f ---- 1 fsc 0x0000.00000000
bdba: 0x02000085

我们下面修改数据块后,改变UNDO为手动,让所有undo segment offline,然后select 看看效果:
SQL> commit;
Commit complete.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 8 block 133;
System altered.
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9f92 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a C-U- 0 scn 0x00000000006e9989
0x02 0x001f.002.00001539 0x01801000.02a6.1f ---- 1 fsc 0x0000.00000000
bdba: 0x02000085
select to_number('001f','xxxxxx') from dual;
TO_NUMBER('001F','XXXXXX')
--------------------------
31
select * from v$rollname where USN=31
USN NAME CON_ID
---------- ------------------------------------------------------------------------------------------ ----------
31 _SYSSMU31_3745581560$ ---31号段 0

select SEGMENT_NAME,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='_SYSSMU31_3745581560$';
SEGMENT_NAME TABLESPACE_NAM HEADER_FILE HEADER_BLOCK
------------------------------ -------------- ----------- ------------
_SYSSMU31_3745581560$ UNDOTBS2 6 160

set lines 1200 pages 1200
col segment_name for a25
col tablespace_name for a15
col status for a15
select tablespace_name,segment_name,status,initial_extent,next_extent,max_extents
from dba_rollback_segs where segment_name='_SYSSMU31_3745581560$'

TABLESPACE_NAME SEGMENT_NAME STATUS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
--------------- ------------------------- --------------- -------------- ----------- -----------
UNDOTBS2 _SYSSMU31_3745581560$ ONLINE 131072 65536 32765
因为有活动事务所以不能offline,那怎么办,我们暂时将undo 改为手动方式,看看效果


BBED> set filename '/u01/oradata/data01.dbf' blocksize 8192 mode edit listfile '/home/oracle/listfile.log'
FILENAME /u01/oradata/data01.dbf
BLOCKSIZE 8192
MODE Edit
LISTFILE /home/oracle/listfile.log
BBED> set dba 8,133
DBA 0x02000085 (33554565 8,133)
BBED> d offset 88 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 88 to 95 Dba:0x02000085
------------------------------------------------------------------------
00000000 00000000
<32 bytes per line>
BBED> m /x ffff offset 88
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 88 to 95 Dba:0x02000085
------------------------------------------------------------------------
ffff0000 00000000
<32 bytes per line>
BBED>
BBED> d offset 90 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 90 to 97 Dba:0x02000085
------------------------------------------------------------------------
00000000 00000000
<32 bytes per line>
BBED> m /x ddff offset 90
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 90 to 97 Dba:0x02000085
------------------------------------------------------------------------
ddff0000 00000000
<32 bytes per line>
BBED> d offset 88 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 88 to 95 Dba:0x02000085
------------------------------------------------------------------------
ffffddff 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 8, Block 133:
current = 0x2473, required = 0x2473
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133

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

SQL> alter system dump datafile 8 block 133;
System altered.
Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006e9f92 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a C-U- 0 scn 0x00000000006e9989
0x02 0x001f.002.00001539 0x01801000.02a6.1f ---- 1 fsc 0x0000.ffddffff --已经修改了
bdba: 0x02000085

SQL> create pfile='/home/oracle/initsun.ora' from spfile;
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/oracle/initsun.ora'
ORACLE instance started.
Total System Global Area 1577058304 bytes
Fixed Size 8621136 bytes
Variable Size 503317424 bytes
Database Buffers 1056964608 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> sho parameter undo;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_optimizer_undo_cost_change string 12.2.0.1
temp_undo_enabled boolean FALSE
undo_management string MANUAL
undo_retention integer 10
undo_tablespace string UNDOTBS2


set lines 1200 pages 1200
col segment_name for a25
col tablespace_name for a15
col status for a15
select tablespace_name,segment_name,status,initial_extent,next_extent,max_extents
from dba_rollback_segs where segment_name='_SYSSMU31_3745581560$'

TABLESPACE_NAME SEGMENT_NAME STATUS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
--------------- ------------------------- --------------- -------------- ----------- -----------
UNDOTBS2 _SYSSMU31_3745581560$ OFFLINE 131072 65536 32765

SQL> select * from tb_logic;
ID
----------
1 --还能查出数据
SQL> !pwd
/home/oracle/lswits
SQL> @trace_file
TRACE_FILE
----------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16521.trc
SQL> alter system dump datafile 8 block 133
2 /
System altered.
SQL> update tb_logic set id=2 where id=1;
update tb_logic set id=2 where id=1
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'DATA'


BBED> d offset 88 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 88 to 95 Dba:0x02000085
------------------------------------------------------------------------
ffffddff 00000000
<32 bytes per line>
BBED>
BBED> m /x 0000 offset 88
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 88 to 95 Dba:0x02000085
------------------------------------------------------------------------
0000ddff 00000000
<32 bytes per line>
BBED>
BBED> m /x 0000 offset 90
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 90 to 97 Dba:0x02000085
------------------------------------------------------------------------
00000000 00000000
<32 bytes per line>
BBED>
BBED> d offset 88 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 88 to 95 Dba:0x02000085
------------------------------------------------------------------------
00000000 00000000
<32 bytes per line>
BBED> sum apply;
Check value for File 8, Block 133:
current = 0x2451, required = 0x2451
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133

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

shut
startup pfile='/home/oracle/initsun.ora'
select * from tb_logic;

shut immediate
startup
Block header dump: 0x02000085
Object id on Block? Y
seg/obj: 0x15229 csc: 0x00000000006ea7e3 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000080 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0020.003.000014e7 0x018005c2.02cb.0a C-U- 0 scn 0x00000000006e9989
0x02 0x001f.002.00001539 0x01801000.02a6.1f C-U- 0 scn 0x00000000006ea592 --oracle 还是修改了,从undo$
bdba: 0x02000085

select SCNBAS from undo$ where name='_SYSSMU31_3745581560$'
SCNBAS
----------
7251346
SQL> select to_char(7251346,'xxxxxxxxx') from dual;
TO_CHAR(7251346,'XXXXXXXXX')
------------------------------
6ea592 --这个就是0x00000000006ea592了,也就是undo segment offline oracle认为事务已经提交,undo segment不存在从undo$取 SCNBAS 值修改ITL SCN。

使用spfile 启动数据库
TABLESPACE_NAME SEGMENT_NAME STATUS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
--------------- ------------------------- --------------- -------------- ----------- -----------
UNDOTBS2 _SYSSMU31_3745581560$ ONLINE 131072 65536 32765
SQL> conn wolf/wolf
Connected.
SQL>
SQL> select * from tb_logic;
ID
----------
1

修改数据块的base SCN:
SQL> update tb_logic set id=2 where id=1;
1 row updated.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> @trace_file;
TRACE_FILE
----------------------------------------------------------------------------------------------------
/u02/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22855.trc
SQL> alter system dump datafile 8 block 133;
System altered.


Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001e.000.000014d3 0x018004d9.032b.12 ---- 1 fsc 0x0000.00000000
0x02 0x001f.002.00001539 0x01801000.02a6.1f C-U- 0 scn 0x00000000006ea592
bdba: 0x02000085

BBED> set filename '/u01/oradata/data01.dbf' blocksize 8192 mode edit listfile '/home/oracle/listfile.log'
FILENAME /u01/oradata/data01.dbf
BLOCKSIZE 8192
MODE Edit
LISTFILE /home/oracle/listfile.log

BBED> d offset 8 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 15 Dba:0x02000085
------------------------------------------------------------------------
a9bd6e00 00000104
<32 bytes per line>
BBED> m/x ffff offset 8;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 15 Dba:0x02000085
------------------------------------------------------------------------
ffff6e00 00000104
<32 bytes per line>
BBED> d offset 10 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 10 to 17 Dba:0x02000085
------------------------------------------------------------------------
6e000000 0104d122
<32 bytes per line>
BBED> m/x fdff offset 10
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 10 to 17 Dba:0x02000085
------------------------------------------------------------------------
fdff0000 0104d122
<32 bytes per line>
BBED> d offset 8 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 15 Dba:0x02000085
------------------------------------------------------------------------
fffffdff 00000104
<32 bytes per line>
BBED> sum apply;
Check value for File 8, Block 133:
current = 0x9f14, required = 0x9f14
BBED> verify;
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133
Block 133 is corrupt
Corrupt block relative dba: 0x02000085 (file 0, block 133)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x02000085
last change scn: 0x0000.0000.fffdffff seq: 0x1 flg: 0x04
spare3: 0x0
consistency value in tail: 0xbda90601
check value in block header: 0x9f14
computed block checksum: 0x0

DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED

SQL> select * from tb_logic;
select * from tb_logic
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 133)
ORA-01110: data file 8: '/u01/oradata/data01.dbf'

修改回去
BBED> d offset 8 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 15 Dba:0x02000085
------------------------------------------------------------------------
6e00fdff 00000104
<32 bytes per line>
BBED> m/x a9bd offset 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 15 Dba:0x02000085
------------------------------------------------------------------------
a9bdfdff 00000104
<32 bytes per line>
BBED> m/x 6e00 offset 10
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 10 to 17 Dba:0x02000085
------------------------------------------------------------------------
6e000000 01048560
<32 bytes per line>
BBED> sum apply
Check value for File 8, Block 133:
current = 0x22d1, required = 0x22d1
BBED> verfy
BBED-00200: invalid keyword (verfy)

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133

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

BBED> d offset 8 count 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 15 Dba:0x02000085
------------------------------------------------------------------------
a9bd6e00 00000104
<32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub2 wrp2_kcbh @2 0x0000
ub4 rdba_kcbh @4 0x02000085
ub4 bas_kcbh @8 0x006ebda9
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x22d1
ub2 spare3_kcbh @18 0x0000
BBED> exit

SQL> select * from tb_logic;
select * from tb_logic
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 133)
ORA-01110: data file 8: '/u01/oradata/data01.dbf'

SQL> alter system flush buffer_cache;
System altered.
SQL> select * from tb_logic;
ID
----------
2

上面修改的偏移量过多
BBED> set filename '/u01/oradata/data01.dbf' blocksize 8192 mode edit listfile '/home/oracle/listfile.log'
FILENAME /u01/oradata/data01.dbf
BLOCKSIZE 8192
MODE Edit
LISTFILE /home/oracle/listfile.log
BBED>
BBED> set dba 8 133
BBED-00205: illegal or out of range DBA (File 0, Block 8)

BBED> set dba 8,133
DBA 0x02000085 (33554565 8,133)

BBED> d offset 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 519 Dba:0x02000085
------------------------------------------------------------------------
96c06e00 00000104 3a7a0000 01000000 29520100 96c06e00 00800000 02003200
80000002 1e000000 d3140000 d9048001 2b031200 00800000 f1be6e00 1f000200
39150000 00108001 a6021f00 00a00000 92a56e00 00000000 00000000 00010100
<32 bytes per line>
BBED> d offset 10
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 10 to 521 Dba:0x02000085
------------------------------------------------------------------------
6e000000 01043a7a 00000100 00002952 010096c0 6e000080 00000200 32008000
00021e00 0000d314 0000d904 80012b03 12000080 0000f1be 6e001f00 02003915
<32 bytes per line>
BBED> m/x af00 offset 10
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 10 to 521 Dba:0x02000085
------------------------------------------------------------------------
af000000 01043a7a 00000100 00002952 010096c0 6e000080 00000200 32008000
00021e00 0000d314 0000d904 80012b03 12000080 0000f1be 6e001f00 02003915
<32 bytes per line>
BBED> d offset 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 519 Dba:0x02000085
------------------------------------------------------------------------
96c0af00 00000104 3a7a0000 01000000 29520100 96c06e00 00800000 02003200
80000002 1e000000 d3140000 d9048001 2b031200 00800000 f1be6e00 1f000200

<32 bytes per line>
BBED> sum apply;
Check value for File 8, Block 133:
current = 0x7afb, required = 0x7afb
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133

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
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from tb_logic;
select * from tb_logic
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbzibmlt_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
修改回去:

BBED> m/x 6e00 offset 10
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 10 to 521 Dba:0x02000085
------------------------------------------------------------------------
6e000000 0104fb7a 00000100 00002952 010096c0 6e000080 00000200 32008000
00021e00 0000d314 0000d904 80012b03 12000080 0000f1be 6e001f00 02003915

<32 bytes per line>
BBED> d offset 8
File: /u01/oradata/data01.dbf (8)
Block: 133 Offsets: 8 to 519 Dba:0x02000085
------------------------------------------------------------------------
96c06e00 00000104 fb7a0000 01000000 29520100 96c06e00 00800000 02003200
80000002 1e000000 d3140000 d9048001 2b031200 00800000 f1be6e00 1f000200

<32 bytes per line>
BBED> sum apply;
Check value for File 8, Block 133:
current = 0x7a3a, required = 0x7a3a
BBED> veriry
BBED-00200: invalid keyword (veriry)

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oradata/data01.dbf
BLOCK = 133

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
另外我这里只是测试了修改数据块中事务层的ITL信息,该报错是索引块ITL scn存在问题,原理都是一致的。

现场问题
由于客户DG主库磁盘故障导致DG切换后报错。
业务执行如下SQL 报错,且其他SQL同样报错
select * from c_pay;

ORA-01555 caused by SQL statement below (SQL ID: fmqj0z5ynrqkd, Query Duration=0 sec, SCN: 0x059c.67ff1b2c):
ora-01555 快照过旧:回滚段15(名称 "_SYSSMU15_3567263981$") 过小
alter 日志报错:
ORA-01555 caused by SQL statement below (SQL ID: cvg12jatc4nq0, Query Duration=0 sec, SCN: 0x059c.676597e7):
ORA-00600: 内部错误代码, 参数: [2663], [1436], [1734882023], [1436], [2895035744], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [2663], [1436], [1735023650], [1436], [3663213624], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [2663], [1436], [1735127458], [1436], [2431473195], [], [], [], [], [], [], []

注意ORA-01555 Query Duration=0 sec ,0秒表示特殊问题,不是undo retention太小,需要特殊处理。

处理步骤

1:分析表和索引:
analyze table <table name found in the error message> validate structure cascade
上面脚本执行报错表示存在坏块,需要重建对应表的的索引,如果不能确定表的那个索引,将该表的全部索引重建。
2:查询索引建立脚本SQL:
prompt Type like TABLE,INDEX,SEQUENCE,SYNONYM,
set long 100000000
set pages 20000
select dbms_metadata.get_ddl('&Type','&Name','&User') from dual
/
示例:
依据提示输入:
Type= INDEX
Name=索引名
User=用户名
保存输出信息到txt文件
3:删除索引:
drop index 索引名
4:重建索引:
依据上面select dbms_metadata.get_ddl('&Type','&Name','&User') from dual 结果信息重建索引,不要改动脚本内容!

5:主库执行SQL:
analyze table <table name found in the error message> validate structure cascade
确定问题是否解决

6:问题依旧存在,禁用然后启用主键
alter table <table name> disable primary key;
alter table <table name> enable primary key;
7:问题依旧存在,那就需要尝试重建UNDO--联系DBA

create undo tablespace undotbs02 'path/undotbs02.dbf' size 30g;
/
alter system set undo_tablespace=undotbs02
/
select tablespace_name,status,count(*) from dba_rollback_segs group by tablespace_name,status
/
select n.usn,s.segment_name,s.status from dba_rollback_segs s,v$rollname n where s.segment_name=n.name and s.status not in ('OFFLINE')
/
Drop tablespace <tablespace_name> including contents and datafiles; ---先不要执行

由于客户对该主库涉及的表重建了一个索引后由于业务不能停其他索引不能重建,另外不只是该表存在问题,客户担心该问题存在普遍现象,想通过一个方案一次性解决问题,
所以我们尝试了重建undo表空间:
通过上面的测试我们发现
可以通过重建UNDO,此时oracle 发现原来的UNDO 已经offline了,就会依据UNDO$给ITL一个offline前的最大SCN值,那么在此
访问就不会报错了,这个原理很隐晦。

我们查询UNDO 段信息:
15 号 段 段名为 _SYSSMU15_3567263891$ 报错.
SYS@claimdb>select us#,name,SCNBAS from sys.undo$
2 /
US# NAME SCNBAS
---------- ------------------------------ ----------
0 SYSTEM 0
1 _SYSSMU1_2447254666$ 1598465804
2 _SYSSMU2_1239925797$ 1598465806
3 _SYSSMU3_2656532484$ 1598465807
4 _SYSSMU4_3176981171$ 1598466259
5 _SYSSMU5_2905383241$ 1598465810
6 _SYSSMU6_2413608159$ 1598465811
7 _SYSSMU7_1373098659$ 1598465812
8 _SYSSMU8_1783268245$ 1598465813
9 _SYSSMU9_2055666087$ 1598465815
10 _SYSSMU10_671236586$ 1598465816
US# NAME SCNBAS
---------- ------------------------------ ----------
11 _SYSSMU11_2604095282$ 1598465818
12 _SYSSMU12_2294241932$ 1598465819
13 _SYSSMU13_271107589$ 1598465820
14 _SYSSMU14_1547069875$ 1598466343
15 _SYSSMU15_3567263891$ 1771715474 select to_char(1771715474,'xxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual 699a3b92
16 _SYSSMU16_3327925205$ 1761896992
17 _SYSSMU17_1680071811$ 1742849665
18 _SYSSMU18_4085893366$ 1742849666
20 _SYSSMU20_3885358044$ 1742849668
21 _SYSSMU21_2631778529$ 1742849670
22 _SYSSMU22_3426088633$ 1742848704
...
后面不显示

SYS@claimdb>set lines 1200 pages 1200
SYS@claimdb>col segment_name for a30
SYS@claimdb>col status for a30
SYS@claimdb>select s.segment_name,s.status from dba_rollback_segs s
2 /
SEGMENT_NAME STATUS
------------------------------ ------------------------------
SYSTEM ONLINE
_SYSSMU1_2447254666$ ONLINE
_SYSSMU2_1239925797$ ONLINE
_SYSSMU3_2656532484$ ONLINE
_SYSSMU4_3176981171$ ONLINE
_SYSSMU5_2905383241$ ONLINE
_SYSSMU6_2413608159$ ONLINE
_SYSSMU7_1373098659$ ONLINE
_SYSSMU8_1783268245$ ONLINE
_SYSSMU9_2055666087$ ONLINE
_SYSSMU10_671236586$ ONLINE
_SYSSMU11_2604095282$ ONLINE
_SYSSMU12_2294241932$ ONLINE
_SYSSMU13_271107589$ ONLINE
_SYSSMU14_1547069875$ ONLINE
_SYSSMU15_3567263891$ OFFLINE --------该undo segment 已经OFFLINE了
_SYSSMU16_3327925205$ OFFLINE
_SYSSMU17_1680071811$ OFFLINE
...
后面不显示

问题来了,15号undo segment已经offline了,为什么还报ora-01555呢? 也就是和我上面测试的情况不一致呀。
安照测试和oracle原理,undo segment 已经 offline 执行块延迟清除时会依据undo$信息更新数据块的ITL信息,
客户的问题却让人不解!!!

考虑是否可以将15号undo segmnet online,看看什么效果,不能确定,希望不大
这种情况重建udno 可否解决问题,不确定,希望不大
另外DG环境下主库和备库 undo 表空间没有关系,各自使用,
也就是主库重建undo表空间对备库redo日志应用没有影响。
尝试online 15号undo segment 如果还不行,再尝试重建undo表空间了
另外该问题rman 坏块检查是没有用的,DBV是可以的但是该表跨越了50多个数据文件,业务期间检查营销IO。
操作步骤:
拉起undo segment:
alter system set "_rollback_segment_count"=25;
set lines 1200 pages 1200
col name for a30
col segment_name for a30
col status for a10
select * from v$rollname;
select n.usn,s.segment_name,s.status from dba_rollback_segs s,v$rollname n where s.segment_name=n.name and s.status not in ('OFFLINE')
/
确认15号undo segment 在上面查询中

重建undo 表空间: 确保主备库有空间新建undo数据文件,path依据实际情况,注意权限正确。

通过拉起undo segment和重建undo表空间后问题依旧,这个比较头疼了,看来这个块中itl记录的scn信息是非法的。

这个报错是索引块的问题,不是数据块的问题,那么看来必须要重建索引了,通过申请检修窗口重建相关表的所有索引问题解决!
注意该问题部分SQL只是报ora-01555,部分SQL报ora-600 【2663】解决方式都一样。




您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表