分享程序员开发的那些事...
更新时间：2022-09-13 10:58:30
[20150715]理解_offline_rollback_segments.txt
--曾经写过一篇 [0126]理解_corrupted_rollback_segments，链接http://blog.itpub.net/267265/viewspace-1415396/
--今天测试_offline_rollback_segments参数的情况。
--offline表示脱线。corrupted表示损坏。这两个参数放在一起很容易混淆，我自己也不是很清楚。做一个测试：
1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> @hide _offline_rollback_segments
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------------- -------------------------- ---------------------- ---------------------- ----------------------
_offline_rollback_segments offline undo segment list TRUE
SCOTT@test> create table t as select rownum id , cast ( 'test' as varchar2(20)) name from xmltable ('1 to 100');
Table created.
SYS@test> select rowid,t.* from scott.t where rownumROWID ID NAME
------------------ ---------- ----------------------------------------
AABLUPAAEAAABKLAAA 1 test
SYS@test> @ lookup_rowid AABLUPAAEAAABKLAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
308495 4 4747 0 4,4747 alter system dump datafile 4 block 4747
;
SCOTT@test> alter system checkpoint;
System altered.
--打开会话1，修改不提交：
SCOTT@test> update t set name='BBBB' ;
100 rows updated.
SCOTT@test> @xid
X
------------------------------
4.0.35872
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBAREC UBASQN STATUS USED_UBLK USED_UREC XID ADDR START_DATE C70
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
4 0 35872 3 8210 38 10696 ACTIVE 2 100 04000000208C0000 00000000BA3D4AA8 2015-07-15 22:09:24 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU4_1665036189$' XID 4 0 35872;
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU4_1665036189$';
SCOTT@test> column spare4 noprint
SCOTT@test> column spare5 noprint
SCOTT@test> column spare6 noprint
SCOTT@test> select * from sys.undo$ where us#=4;
US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# UGRP# KEEP OPTIMAL FLAGS SPARE1 SPARE2 SPARE3
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
4 _SYSSMU4_1665036189$ 1 3 176 4108924233 2 35868 10694 0 3 2 2
--打开会话2：
SYS@test> shutdown abort;
ORACLE instance shut down.
--使用bbed观察:
BBED> set dba 4,4747
DBA 0x0100128b (16781963 4,4747)
BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c
BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: test
--name='test'.
2.测试：
SYS@test> create pfile='/tmp/aa.ora' from spfile ;
File created.
--修改/tmp/aa.ora，加入如下内容：
*._offline_rollback_segments='_SYSSMU4_1665036189$'
SYS@test> startup mount pfile='/tmp/aa.ora'
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> @hide _offline_rollback_segments
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------------------- -------------------------- ---------------------- ---------------------- ----------------------
_offline_rollback_segments offline undo segment list FALSE _SYSSMU4_1665036189$ _SYSSMU4_1665036189$
SYS@test> alter database open ;
Database altered.
SYS@test> alter system checkpoint;
System altered.
--通过bbed观察，可以发现如下：
BBED> set dba 4,4747
DBA 0x0100128b (16781963 4,4747)
BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c
BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: BBBB
--name='BBBB',打开数据库时，应用了redo。但是由于*._offline_rollback_segments='_SYSSMU4_1665036189$'，没有提交的事务没有回滚。
column spare4 noprint
column spare5 noprint
column spare6 noprint
SYS@test> select * from sys.undo$ where us#=4;
US# NAME USER# FILE# BLOCK# SCNBAS SCNWRP XACTSQN UNDOSQN INST# STATUS$ TS# UGRP# KEEP OPTIMAL FLAGS SPARE1 SPARE2 SPARE3
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
4 _SYSSMU4_1665036189$ 1 3 176 4108924233 2 35868 10694 0 5 2 2
--注意看STATUS$＝5，与上面不同正常是STATUS$＝3。
SYS@test> select * from v$rollname ;
USN NAME
---------- ----------------------------------------
0 SYSTEM
1 _SYSSMU1_559505304$
2 _SYSSMU2_3752879465$
3 _SYSSMU3_2763804800$
5 _SYSSMU5_2973757209$
6 _SYSSMU6_3709901187$
7 _SYSSMU7_3362111860$
8 _SYSSMU8_819560936$
9 _SYSSMU9_3043963034$
12 _SYSSMU12_1585900997$
13 _SYSSMU13_494349874$
11 rows selected.
SCOTT@test> select rowid,t.* from scott.t where rownumROWID ID NAME
------------------ ---------- --------------------
AABLUPAAEAAABKLAAA 1 test
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8h9jfmz0pmjj8, child number 1
-------------------------------------
select rowid,t.* from scott.t where rownumPlan hash value: 508354683
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 104 |
|* 1 | COUNT STOPKEY | | 1 | | | | | 1 |00:00:00.01 | 104 |
| 2 | TABLE ACCESS FULL| T | 1 | 100 | 3700 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 104 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
--Buffers=104,说明读取undo段，得到正确的信息。name='test'.
SCOTT@test> alter system checkpoint;
System altered.
--通过bbed观察：
BBED> set dba 4,4747
DBA 0x0100128b (16781963 4,4747)
BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c
BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x02
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: BBBB
--块内信息并没有更改。
3.现在取消参数设置：
SYS@test> startup open read only;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
--以只读打开。
SYS@test> select rowid,t.* from scott.t where rownumROWID ID NAME
------------------ ---------- --------------------
AABLUPAAEAAABKLAAA 1 test
SYS@test> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
SYS@test> select rowid,t.* from scott.t where rownumROWID ID NAME
------------------ ---------- --------------------
AABLUPAAEAAABKLAAA 1 test
SYS@test> alter system checkpoint;
System altered.
--bbed观察：
BBED> p *kdbr[0]
rowdata[1089]
-------------
ub1 rowdata[1089] @8177 0x2c
BBED> x /rnc
rowdata[1089] @8177
-------------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: test
--总结：
1._offline_rollback_segments作用跟10513事件很相似，异常abort时，重启数据库应用日志，但是读取信息时要通过undo段来构造，但是不会更新数据块的信息。
2._corrupted_rollback_segments相当于undo段损坏，这样读取的是未提交的信息，参见http://blog.itpub.net/267265/viewspace-1415396/.