基于行跟踪的ROWDEPENDENCIES ORA_ROWSCN信息

在Oracle 10g中的引入了ORA_ROWSCN伪列新特性。基于此种伪列所提供的信息,我们可以方便地找出某个数据块或某一个行最近被修改
的时间戳。在默认情况下,10g下表会以非行依赖性(NOROWDEPENDENCIES)的属性创建,这意味着我们可观察的ORA_ROWSCN信息是以块级跟踪的,无法分辨同一块内的多行间不同的修改时间。为了达到行级粒度的跟踪我们需要在建表时指定基于行依赖性的ROWDEPENDENCIES字句。如:

SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> drop table maclean;
Table dropped.
SQL> create table maclean(t1 int,t2 timestamp) ;
Table created.
SQL> insert into maclean values(1,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into maclean values(2,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_timestamp_format='hh24:mi:ss';
Session altered.
SQL> col t2 for a35
SQL> col orscn for a35
SQL> select scn_to_timestamp(ora_rowscn) orscn,t2 from maclean;
ORSCN                               T2
----------------------------------- -----------------------------------
20:30:11                            20:29:56
20:30:11                            20:30:10
/* 可以看到默认情况下创建的数据表使用块级依赖性追踪(Block-level Dependency Tracking)
故而其返回的ORA_ROWSCN伪列仅能代表某数据块最近被更新的SCN
*/
create table maclean_rd(t1 int,t2 timestamp) rowdependencies;
Table created.
SQL> select table_name,dependencies from user_tables where dependencies!='DISABLED';
TABLE_NAME                     DEPENDEN
------------------------------ --------
MACLEAN_RD                     ENABLED
/* 包括字典基表在内所有的表都会默认以NOROWDEPENDENCIES创建*/
SQL> insert into maclean_rd values(1,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into maclean_rd values(2,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> select scn_to_timestamp(ora_rowscn) orscn,t2 from maclean_rd;
ORSCN                               T2
----------------------------------- -----------------------------------
20:31:26                            20:31:25
20:31:35                            20:31:37
/* 可以看到在行依赖性跟踪情况下,ORA_ROWSCN反映的时间戳与插入的时间戳间仍会有误差;
显然这种误差部分源于scn_to_timestamp函数使用的smon_scn_time SCN记录表也仅是粗略记录SCN对应的时间戳。
*/
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from maclean_rd;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1                                94122
1                                94122
/* 以上通过rowid找到了插入的2行所在的数据块*/
SQL> alter system dump datafile '/s01/10gdb/oradata/CLINICA/datafile/o1_mf_system_6fp8d3f0_.dbf' block 94122;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/clinica/udump/clinica_ora_12934.trc
block_row_dump:
tab 0, row 0, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.0351b8bd
col  0: [ 2]  c1 02
col  1: [11]  78 6f 01 02 15 20 1a 21 d8 52 68
tab 0, row 1, @0x1f70
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.0351b8c4
col  0: [ 2]  c1 03
col  1: [11]  78 6f 01 02 15 20 26 02 ab c2 f8
/* 可以从block dump中看到每行都多出了dscn信息,这就是基于行追踪的行级ORA_ROWSCN信息的来源。
注意这里的dscn需要占用6个字节的空间,换而言之启用ROWDEPENDENCIES会为每一行多出6个字节的磁盘开销。
*/
/* 此外行级追踪仅能在创建表(create table)的同时指定,而无法使用ALTER TABLE来修改  */
SQL> alter table maclean move tablespace users ROWDEPENDENCIES;
alter table maclean move tablespace users ROWDEPENDENCIES
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

logfile switch causes incremental checkpoint?

不少Oracle的初学者都会因为checkpoint这个知识点而头痛,绝大多数Oracle文档对完全检查点和增量检查点的描述又都略显朦胧;譬如在线日志的切换引起的是完全检查点还是增量检查点这个问题,就有不少的争论。实际上增量检查点与完全检查点有一个显著的区别:完全检查点发生时控制文件和数据文件头中的checkpoint scn都会被更新,而增量检查点发生时只有控制文件中的checkpoint scn更新;
我们可以通过以下演示证明日志切换引发的到底是何种检查点?:

SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1665476
1665476
1665476
1665476
1665476
1665476
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 手动执行checkpoint,数据文件头的checkpoint scn立即更新了 */
SQL> alter system flush buffer_cache;
System altered.
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 单纯flush buffer cache冲刷数据库高速缓存不会更新数据文件头的checkpoint scn */
SQL> alter system set log_checkpoints_to_alert=true;
System altered.
SQL> alter system set log_checkpoint_timeout=20;
System altered.
/* 设置log_checkpoint_timeout为20s,频繁引发增量检查点 */
alert log:
Wed Nov  3 20:24:49 2010
Incremental checkpoint up to RBA [0x3d.dff1.0], current log tail at RBA [0x3d.dff6.0]
Wed Nov  3 20:25:07 2010
Incremental checkpoint up to RBA [0x3d.dff7.0], current log tail at RBA [0x3d.dffc.0]
Wed Nov  3 20:25:25 2010
Incremental checkpoint up to RBA [0x3d.dffd.0], current log tail at RBA [0x3d.e002.0]
Wed Nov  3 20:25:43 2010
Incremental checkpoint up to RBA [0x3d.e003.0], current log tail at RBA [0x3d.e008.0]
Wed Nov  3 20:26:01 2010
Incremental checkpoint up to RBA [0x3d.e009.0], current log tail at RBA [0x3d.e00e.0]
SQL> set time on;
20:26:38 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 可以看到增量检查点并不会引起数据文件头的checkpoint scn 被更新 */
20:26:43 SQL>  alter system set log_checkpoint_timeout=1800;
System altered.
/* 那么日志文件切换就会引起数据文件头的checkpoint scn被更新吗?*/
20:28:10 SQL> alter system switch logfile;
System altered.
20:29:16 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* logfile switch 日志文件切换引起的是一种slow慢的完全检查点,它不同于alter system checkpoint(ASC),
ASC要求的脏块写出和控制文件及数据文件头更新时要立即完成的,也就是说当alter system checkpoint语句返回"System altered."
后以上工作都已经完成了;而alter system switch logfile或者自然的日志切换引发的是一种慢的完全检查点,
它在返回"System altered"时不要求写脏块等工作必须已经完成
*/
/* 我们可以用冲刷高速缓存的方式保证脏块写出的工作被督促完成 */
20:33:39 SQL> alter system flush buffer_cache;
System altered.
20:33:45 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697544
1697544
1697544
1697544
1697544
1697544
6 rows selected.
/* 虽然日志切换所引发的slow checkpoint(慢的检查点)并无立即完成的要求,但也并非全无限制;
当某次日志切换由1号日志组切换到2号日志组时,
将引发一个slow checkpoint,之后日志连续切换又要切到1号日志组时要求之前的那个slow checkpoint在切换前必须完成
*/
20:41:35 SQL> set timing on;
20:42:02 SQL>  select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1          1         67   52428800          2 YES INACTIVE               1698288 2010-11-03 20:41:19
2          1         68   52428800          2 YES INACTIVE               1698292 2010-11-03 20:41:21
3          1         69   52428800          2 NO  CURRENT                1698302 2010-11-03 20:41:35
Elapsed: 00:00:00.00
20:42:17 SQL> delete tv;
51134 rows deleted.
Elapsed: 00:00:01.68
20:42:34 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
20:42:36 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
20:42:40 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
20:42:43 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:02.00
20:45:28 SQL>  select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1700686
1700686
1700686
1700686
1700686
1700686
6 rows selected.
Elapsed: 00:00:00.00
alter.log告警日志中的内容:
Wed Nov  3 20:42:40 2010
Beginning log switch checkpoint up to RBA [0x46.2.10], SCN: 1700686
...........................
Wed Nov  3 20:42:45 2010
Thread 1 cannot allocate new log, sequence 72
Checkpoint not complete
....................
Completed checkpoint up to RBA [0x46.2.10], SCN: 1700686
/* 最近一次的日志切换耗费2s,在告警日志中可以看到此次slow checkpoint的相关记录 */

手动递增SCN号的几种方法:How to increase System Change Number by manual

 

 

手动递增SCN号的几种方法

 

除去下面几种,还有一种方法直接修改 实例的Global Lamport SCN,在SGA中由kcsgscn变量存储,对于一个实例来说这是唯一的源SCN,所有其他的SCN均由这个source scn所驱动。 这种递增方式是直接用oradebug 修改该Global Lamport SCN kcsgscn

 

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

 

SQL> oradebug setmypid
Statement processed.

 

SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;

TO_CHAR(CURRE
————-
D3E1E

SQL>
SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 000D3E1E 00000000 00000000 00000000 0000162D 00000000 00000000 00000000 00000000 00000000 60012338 00000000

 

ORADEBUG POKE 0x060012658 4 0xfffff

poke 命令的语法

<address> <length> <value>” allows you to modify a given region of memory (length of memory is limited to size of scalar C types)

 

SQL> select current_scn from v$database;

CURRENT_SCN
———–
1048583

 

SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;

TO_CHAR(CURRE
————-
10000B

SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 00100010 00000000 00000000 00000000 0000004E 00000000 00000000 00000000 00000000 00000000 60012338 00000000

 

 

 

 

 

How to jump SCN  by manual ,  this could be a problem:

1. We can bump up the SCN by using the procedure from Note: 386830.1

 

Bump the system SCN on the primary database to fix any metadata index corruptions for both
primary and physical standby databases. Set the following parameters in the init.ora and restart
the database in restricted mode to bump the system SCN of the primary database.
For Real Application Clusters, perform the steps on only one node of the cluster. 
Init.ora syntax:
*._allow_error_simulation = TRUE
*._smu_debug_mode = 268435456
If using an spfile, Oracle recommends creating a temporary init.ora using the CREATE PFILE SQL command.
SQL> create pfile='/tmp/initTMP.ora' from spfile='';
Then add the parameters to this temporary file.
To use the temporary init.ora file when starting the instance, include the 'PFILE' clause with the STARTUP SQL command i.e.
SQL> startup restrict pfile=
WARNING: These parameters should only be used for this fix and must be removed immediately afterwards in step 2.
If the above parameters are used through multiple database restarts, a complete database rebuild will be required.
To know the system SCN has been bumped, monitor the instance's alert.log for the following message:
advance SCN to wrap base xxxx
Where xxx represents the new wrap SCN.

 

 

 

 

2.EVENT: ADJUST_SCN – Quick Reference (Doc ID 30681.1)

 

 

 

WORKAROUND:
-----------
Searched in webiv (ora-1555, ora-604 see note:1063408.6 ) suggests workaround
is to adjust serial number using event
Will try : (30681.1)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1';
ora-600 [2256][0][1073741824][1][293672646]
ERROR:
ORA-600 [2256][a][b][c][d][e]
VERSIONS:
versions 7.3.X, 8.0.X, 8.1.X
DESCRIPTION:
This exception indicates that you attempted to ADJUST_SCN but the level
supplied would be less that the current SCN.
ARGUMENTS:
a.  Requested SCN WRAP
b.  Requested SCN BASE
c.  Current SCN WRAP
d.  Current SCN BASE = [293672646]
*4 = 1174690584
will try level 2
ora-600 2256[0][2147483648][1][293672646]
level 3
ora-600 2256[0][3221225472][1][293672646]
Now, we increase the SCN on DST7 by using the ADJUST_SCN event -
(Note: do not use this event outside of your test environment)
set the following hidden parameter in init.ora on DST7 database and bounce
the database.
_allow_error_simulation=true
Now, the SCN is increased by doing -
alter system set events 'immediate trace name adjust_scn level ';
where  translates to (n*0x40000000) as the target SCN value. ie. n = 1
will set the SCN as 0x0000.40000000. For the testcase pick a value that is
larger than the current SCN on both databases by atleast 100000 SCNs (our
earlier SCN adjust threshold value).
alter system set events 'immediate trace name adjust_scn level 2';
select to_char(current_scn, 'xxxxxxxxxxxx') from v$database;

 

 

 

3.Note 552438.1 How To Adjust the SCN using parameter _MINIMUM_GIGA_SCN

 

 

 

 

Parameter: MINIMUM_GIGA_SCN
~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Oracle8i:	HIDDEN
@Identifier:	kcmmsn
@Versions:	See <IVERS.MINIMUM_GIGA_SCN>
Values:
Related:
Description:	Minimum SCN to start with in 2^30 units
~~~~~~~~~~~~
Articles:	
<Event:ADJUST_SCN>
Overview of Init.Ora Parameter Reference notes

终极方案:
使用bbed修改datafile header实现修改scn

主要是修改system01.dbf datafile header kcvfh.kcvfhckp.kcvcpscn

helpbkup_us@oracle.com
helpkern_us@oracle.com
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1* select name,CHECKPOINT_CHANGE# from v$datafile
SQL> /
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf                3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf                   3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf                 3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf                3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf                  3905523
SQL>  select to_char(3905523,'XXXXXXXXXXXXX') from dual;
TO_CHAR(390552
--------------
3B97F3
SQL> oradebug tracefile_name
/s01/oracle/product/10.2.0/db_1/admin/MACLEAN1/udump/maclean1_ora_9468.trc
[oracle@vrh8 ~]$ cp /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf  /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf.bak
[oracle@vrh8 ~]$ bbed filename=/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 24 16:50:39 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set mode edit
MODE            Edit
BBED>  set blocksize 8192
BLOCKSIZE       8192
BBED> set block 1
BLOCK#          1
BBED> map
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                                     Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes                    @0       
ub4 tailchk                                @8188    
BBED> p kcvfh
struct kcvfh, 676 bytes                     @0       
struct kcvfhbfh, 20 bytes                @0       
ub1 type_kcbh                         @0        0x0b
ub1 frmt_kcbh                         @1        0xa2
ub1 spare1_kcbh                       @2        0x00
ub1 spare2_kcbh                       @3        0x00
ub4 rdba_kcbh                         @4        0x00400001
ub4 bas_kcbh                          @8        0x00000000
ub2 wrp_kcbh                          @12       0x0000
ub1 seq_kcbh                          @14       0x01
ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
ub2 chkval_kcbh                       @16       0x4fc6
ub2 spare3_kcbh                       @18       0x0000
struct kcvfhhdr, 76 bytes                @20      
ub4 kccfhswv                          @20       0x00000000
ub4 kccfhcvn                          @24       0x0a200100
ub4 kccfhdbi                          @28       0x157f2927
text kccfhdbn[0]                      @32      M
text kccfhdbn[1]                      @33      A
text kccfhdbn[2]                      @34      C
text kccfhdbn[3]                      @35      L
text kccfhdbn[4]                      @36      E
text kccfhdbn[5]                      @37      A
text kccfhdbn[6]                      @38      N
text kccfhdbn[7]                      @39      1
ub4 kccfhcsq                          @40       0x00000675
ub4 kccfhfsz                          @44       0x00010900
s_blkz kccfhbsz                       @48       0x00
ub2 kccfhfno                          @52       0x0001
ub2 kccfhtyp                          @54       0x0003
ub4 kccfhacid                         @56       0x00000000
ub4 kccfhcks                          @60       0x00000000
text kccfhtag[0]                      @64       
text kccfhtag[1]                      @65       
text kccfhtag[2]                      @66       
text kccfhtag[3]                      @67       
text kccfhtag[4]                      @68       
text kccfhtag[5]                      @69       
text kccfhtag[6]                      @70       
text kccfhtag[7]                      @71       
text kccfhtag[8]                      @72       
text kccfhtag[9]                      @73       
text kccfhtag[10]                     @74       
text kccfhtag[11]                     @75       
text kccfhtag[12]                     @76       
text kccfhtag[13]                     @77       
text kccfhtag[14]                     @78       
text kccfhtag[15]                     @79       
text kccfhtag[16]                     @80       
text kccfhtag[17]                     @81       
text kccfhtag[18]                     @82       
text kccfhtag[19]                     @83       
text kccfhtag[20]                     @84       
text kccfhtag[21]                     @85       
text kccfhtag[22]                     @86       
text kccfhtag[23]                     @87       
text kccfhtag[24]                     @88       
text kccfhtag[25]                     @89       
text kccfhtag[26]                     @90       
text kccfhtag[27]                     @91       
text kccfhtag[28]                     @92       
text kccfhtag[29]                     @93       
text kccfhtag[30]                     @94       
text kccfhtag[31]                     @95       
ub4 kcvfhrdb                             @96       0x00400179
struct kcvfhcrs, 8 bytes                 @100     
ub4 kscnbas                           @100      0x00000008
ub2 kscnwrp                           @104      0x0000
ub4 kcvfhcrt                             @108      0x221e01bf
ub4 kcvfhrlc                             @112      0x31dd7868
struct kcvfhrls, 8 bytes                 @116     
ub4 kscnbas                           @116      0x00080634
ub2 kscnwrp                           @120      0x0000
ub4 kcvfhbti                             @124      0x00000000
struct kcvfhbsc, 8 bytes                 @128     
ub4 kscnbas                           @128      0x00000000
ub2 kscnwrp                           @132      0x0000
ub2 kcvfhbth                             @136      0x0000
ub2 kcvfhsta                             @138      0x2000 (NONE)
struct kcvfhckp, 36 bytes                @484     
struct kcvcpscn, 8 bytes              @484     
ub4 kscnbas                        @484      0x003b97f3
ub2 kscnwrp                        @488      0x0000
ub4 kcvcptim                          @492      0x342a9561
ub2 kcvcpthr                          @496      0x0001
union u, 12 bytes                     @500     
struct kcvcprba, 12 bytes          @500     
ub4 kcrbaseq                    @500      0x00000052
ub4 kcrbabno                    @504      0x00013fc0
ub2 kcrbabof                    @508      0x0010
ub1 kcvcpetb[0]                       @512      0x02
ub1 kcvcpetb[1]                       @513      0x00
ub1 kcvcpetb[2]                       @514      0x00
ub1 kcvcpetb[3]                       @515      0x00
ub1 kcvcpetb[4]                       @516      0x00
ub1 kcvcpetb[5]                       @517      0x00
ub1 kcvcpetb[6]                       @518      0x00
ub1 kcvcpetb[7]                       @519      0x00
ub4 kcvfhcpc                             @140      0x00000087
ub4 kcvfhrts                             @144      0x3427f1a8
ub4 kcvfhccc                             @148      0x00000086
struct kcvfhbcp, 36 bytes                @152     
struct kcvcpscn, 8 bytes              @152     
ub4 kscnbas                        @152      0x00000000
ub2 kscnwrp                        @156      0x0000
ub4 kcvcptim                          @160      0x00000000
ub2 kcvcpthr                          @164      0x0000
union u, 12 bytes                     @168     
struct kcvcprba, 12 bytes          @168     
ub4 kcrbaseq                    @168      0x00000000
ub4 kcrbabno                    @172      0x00000000
ub2 kcrbabof                    @176      0x0000
ub1 kcvcpetb[0]                       @180      0x00
ub1 kcvcpetb[1]                       @181      0x00
ub1 kcvcpetb[2]                       @182      0x00
ub1 kcvcpetb[3]                       @183      0x00
ub1 kcvcpetb[4]                       @184      0x00
ub1 kcvcpetb[5]                       @185      0x00
ub1 kcvcpetb[6]                       @186      0x00
ub1 kcvcpetb[7]                       @187      0x00
ub4 kcvfhbhz                             @312      0x00000000
struct kcvfhxcd, 16 bytes                @316     
ub4 space_kcvmxcd[0]                  @316      0x00000000
ub4 space_kcvmxcd[1]                  @320      0x00000000
ub4 space_kcvmxcd[2]                  @324      0x00000000
ub4 space_kcvmxcd[3]                  @328      0x00000000
word kcvfhtsn                            @332      0
ub2 kcvfhtln                             @336      0x0006
text kcvfhtnm[0]                         @338     S
text kcvfhtnm[1]                         @339     Y
text kcvfhtnm[2]                         @340     S
text kcvfhtnm[3]                         @341     T
text kcvfhtnm[4]                         @342     E
text kcvfhtnm[5]                         @343     M
text kcvfhtnm[6]                         @344      
text kcvfhtnm[7]                         @345      
text kcvfhtnm[8]                         @346      
text kcvfhtnm[9]                         @347      
text kcvfhtnm[10]                        @348      
text kcvfhtnm[11]                        @349      
text kcvfhtnm[12]                        @350      
text kcvfhtnm[13]                        @351      
text kcvfhtnm[14]                        @352      
text kcvfhtnm[15]                        @353      
text kcvfhtnm[16]                        @354      
text kcvfhtnm[17]                        @355      
text kcvfhtnm[18]                        @356      
text kcvfhtnm[19]                        @357      
text kcvfhtnm[20]                        @358      
text kcvfhtnm[21]                        @359      
text kcvfhtnm[22]                        @360      
text kcvfhtnm[23]                        @361      
text kcvfhtnm[24]                        @362      
text kcvfhtnm[25]                        @363      
text kcvfhtnm[26]                        @364      
text kcvfhtnm[27]                        @365      
text kcvfhtnm[28]                        @366      
text kcvfhtnm[29]                        @367      
ub4 kcvfhrfn                             @368      0x00000001
struct kcvfhrfs, 8 bytes                 @372     
ub4 kscnbas                           @372      0x00000000
ub2 kscnwrp                           @376      0x0000
ub4 kcvfhrft                             @380      0x00000000
struct kcvfhafs, 8 bytes                 @384     
ub4 kscnbas                           @384      0x00000000
ub2 kscnwrp                           @388      0x0000
ub4 kcvfhbbc                             @392      0x00000000
ub4 kcvfhncb                             @396      0x00000000
ub4 kcvfhmcb                             @400      0x00000000
ub4 kcvfhlcb                             @404      0x00000000
ub4 kcvfhbcs                             @408      0x00000000
ub2 kcvfhofb                             @412      0x000a
ub2 kcvfhnfb                             @414      0x000a
ub4 kcvfhprc                             @416      0x221e01a8
struct kcvfhprs, 8 bytes                 @420     
ub4 kscnbas                           @420      0x00000001
ub2 kscnwrp                           @424      0x0000
struct kcvfhprfs, 8 bytes                @428     
ub4 kscnbas                           @428      0x00000000
ub2 kscnwrp                           @432      0x0000
ub4 kcvfhtrt                             @444      0x00000000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x003b97f3
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x342a9561
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x00000052
ub4 kcrbabno                       @504      0x00013fc0
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> set offset 484
OFFSET          484
BBED> modify /x 0xF397
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
f397f4f3 00000000 61952a34 01000000 52000000 c03f0100 1000a865 02000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
0d000d00 0d000100 00000000 00000000 00000000 02004000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> set offset 486
OFFSET          486
BBED> modify /x 0x4B00
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                Offsets:  486 to  997           Dba:0x00000000
------------------------------------------------------------------------
4b000000 00006195 2a340100 00005200 0000c03f 01001000 a8650200 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000d00 
0d000d00 01000000 00000000 00000000 00000200 40000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x004b97f3
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x342a9561
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x00000052
ub4 kcrbabno                       @504      0x00013fc0
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> sum
Check value for File 0, Block 1:
current = 0x4fc6, required = 0x4fb6
BBED> sum apply
Check value for File 0, Block 1:
current = 0x4fb6, required = 0x4fb6
BBED> verify
DBVERIFY - Verification starting
FILE = /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf
BLOCK = 1
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   : 0
Total Blocks Influx           : 0
SQL> select file#,name,checkpoint_change# from v$datafile;
1 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf                  3905523
2 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf                3905523
3 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf                  3905523
4 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf                   3905523
5 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf                 3905523
6 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf                3905523
7 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf                  3905523
8 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf                  3905523
8 rows selected.
SQL> select file#,FUZZY,CHECKPOINT_CHANGE#        from v$datafile_header;
1 NO             4954099
2 NO             3905523
3 NO             3905523
4 NO             3905523
5 NO             3905523
6 NO             3905523
7 NO             3905523
8 NO             3905523
8 rows selected.
SQL> alter database open;
Database altered.
SQL> select file#,FUZZY,CHECKPOINT_CHANGE#        from v$datafile_header;
1 YES            4954100
2 YES            4954100
3 YES            4954100
4 YES            4954100
5 YES            4954100
6 YES            4954100
7 YES            4954100
8 YES            4954100
8 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area  591396864 bytes
Fixed Size                  2022536 bytes
Variable Size             180355960 bytes
Database Buffers          402653184 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.

Know more about DML

The change vectors as constructed by the transaction layer are used by the cache layer to apply the physical block changes.

Redo log ordering and recovery as well as read consistency are the fundamental purposes of SCNs.

The change vector:
Change vectors are built in the PGA process of the process modifying a block, have the DBA and SCN/SEQ of the block before the change, and only ever perform the change for one block.

The system commit number:
The SCN is a 6-byte structure consisting of the SCN base and SCN wrap.
Is a 6-byte structure consisting of the SCN base and SCN wrap, 4 bytes for the SCN base and 2 bytes for the SCN wrap

The SCN is used within the data block to reflect the committed version of the block.
There is only one global SCN generator for each database.
A large transaction is defined as a transaction that is participating in a parallel DML (PDML) operation or a distributed transaction.
One of the first checks for rollback segment allocation is whether the transaction is participating in a PDML or distributed transaction. These are used to determine the largest rollback segment to be allocated.

steps below to illustrate the order necessary to begin a transaction.

1.bind to the available rollback segment
2.allocate a slot in the transaction table
3.allocate an undo block to the rollback segment

When a free slot cannot be allocated in the transaction table, exclusive access is acquired in the rollback segment header.
Only when a free slot can be allocated in the transaction table do you gain exclusive access to the RBS header.

The system rollback segment will be used only for data for the system tablespace and data that is being created from recursive SQL calls generated by user data.

The search for the allocation of an undo block to a rollback segment will occur in:
Searching for an undo block to be allocated will begin in the current extent if the extent boundary has not been reached, in the next extent provided there are no uncommitted transactions, or in a newly allocated extent if the next extent has uncommitted data.

Rollback segments are shrunk either explicitly through the ’alter rollback segment … shrink to …’ command or implicitly by the SMON process. Implicit shrinkage can be disabled by:
PCTINCREASE has no affect on rollback segments. All extents will be sized by the setting of the next_extent storage attribute.
Setting the event 10512 will disable the implicit shrink operation performed by SMON.
Setting the initialization parameter ‘rbs_disable_shk’. There is no such parameter. Setting the event 10512 will disable the implicit shrinkage operation performed by SMON.

The Oracle server begins shrinking a rollback segment starting from the current extent +2

Rollback segment shrinkage will occur while optimal or minextents is not reached, there are at least two remaining extents, the extent is not busy, and the extent is not extent #0.

An extent is reused if all its blocks are inactive. RBS undo blocks are reused on a per extent basis. An undo block becomes inactive when the active transaction in the undo block commits.

correct order of Interested Transaction List (ITL) allocation.

1. Find an  Unused ITL
2. FInd ITL of oldest commited TX
3. DO a partial block cleanout that stops when an ITL becomes available
4. Extend ITL area

The variable portion of the transaction layer is dictated by the MAXTRANS parameter.
The INITRANS parameter dictates the number of ITL’s and hence the variable portion of the Transaction Layer.

An ITL must be acquired by any transaction wishing to modify a block. If an ITL is unavailable and there is no space left in the block, a transaction will wait.

A transaction that currently has an ITL within a block cannot update any row in the block if the required row is locked by a previous transaction. That transaction will wait on the TX enqueue.When attempting to update a row already held by a previous transaction, a transaction will wait on the TX enqueue in exclusive mode.

The free space credit field of the transaction layer maintains the space as freed for a given transaction, and that is returned on commit of the transaction.

When a transaction is unable to allocate an ITL within a data block for which it needs to modify a block, the transaction will:
That transaction will wait on the TX enqueue in share mode until an ITL becomes available within the block where the modification is required.

The Oracle server maintains the row header flag and lock byte (total 2 bytes) for the deleted row. It is necessary to keep these first two bytes to maintain the row lock. This is referred to as stub space.

The correct statistic Rollback changes – undo records applied is incremented for each link in the undo record chain.

The Data Block Address (DBA) in the Transaction Table slot indicates:
The beginning of the undo chain for the transaction  Correct! The DBA in the Transaction Table slot is the starting point for rolling back a transaction

the correct sequence of events at the COMMIT of a transaction.

1. FIND an scn value
2. update the transaction table slot with the scn
3. create a commit record in the redo log buffer
4. flush the redo log buffer to disk
5. release locks held on the rows and tables

The application of the undo to the data block is performed by the
The data layer ,and the transaction layer do not have the right to perform physical block-level changes.

沪ICP备14014813号

沪公网安备 31010802001379号