非IMU和IMU模式下redo log格式解析

-----------------非IMU模式-------------------------

1. 改为非IMU模式

SQL> alter system set "_in_memory_undo"=false scope=spfile;

System altered.

SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  751595520 bytes
Fixed Size                  2257032 bytes
Variable Size             490737528 bytes
Database Buffers          255852544 bytes
Redo Buffers                2748416 bytes
Database mounted.
Database opened.
SQL> show parameter "_in_memory_undo";

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo                      boolean     FALSE

2. 切换日志，对表进行update操作，多个update一次提交

SQL> alter system switch logfile;

System altered.

SQL> update t set name='AAAAA' where id=1;

1 row updated.

SQL> update t set name='BBBBB' where id=2;

1 row updated.

SQL> commit;

Commit complete.

3. dump redo进行分析

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 ACTIVE

SQL> alter system dump logfile '/u01/app/oracle/oradata/orcl/redo01a.log';

System altered.

4. dump文件分析

REDO RECORD1: 一个REDO RECORD=RECORD头+CHANGE VACTOR(一个CV就是一个操作)

REDO RECORD - Thread:1 RBA: 0x00000b.00000008.0010 LEN: 0x021c VLD: 0x05
SCN: 0x0000.00142426 SUBSCN:  1 11/22/2016 04:07:07
(LWN RBA: 0x00000b.00000008.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00142425)

日志头:

thread1：线程号，单实例的话就是thread1,rac的话会有1,2 对应不同的实例。

RBA：0x00000b.00000008.0010，将16进制转换为10进制分别logfile sequence number，logfile block number，byte offset into the block，即redo序列号，redo block    号，以及偏移量

sys@ORCL> select to_number('b','x') from dual;

TO_NUMBER('B','X')
------------------
                11

1 row selected.

VLD：0x05，日志格式，非IMU时是这个，IMUI时为0x0d

SCN：0x0000.00142426 SUBSCN:  1 11/22/2016 04:07:07，指此redo条目产生时的SCN号。与CHANGE4#提交时的SCN一致。

sys@ORCL> select scn_to_timestamp(to_number('142426','xxxxxx')) from dual;

SCN_TO_TIMESTAMP(TO_NUMBER('142426','XXXXXX'))
---------------------------------------------------------------------------
22-NOV-16 04.07.04.000000000 AM

1 row selected.

(LWN RBA: 0x00000b.00000008.0010 LEN: 0002 NST: 0001 SCN: 0x0000.00142425)：括号中的SCN比上一行的SCN少一个

CHANGE #1

CHANGE #1 TYP:0 CLS:19 AFN:3 DBA:0x00c00090 OBJ:4294967295 SCN:0x0000.0014238e SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: sl