Transcript Redo Internals

Redo
Internals
Julian Dyke
Independent Consultant
Web Version
1
© 2005 Julian Dyke
juliandyke.com
Agenda
1.
2.
3.
4.
5.
6.
7.
8.
9.
2
© 2005 Julian Dyke
What is Redo?
Redo Records
Change Vectors
Row Operations
Index Operations
Other Redo Operations
Direct Loads / NOLOGGING
Transaction Auditing
Supplemental Logging
juliandyke.com
What is Redo?
3

Redo logs contain a history of all changes made to the
database

Redo log files are used by
 Recovery (instance and media)
 Log Miner
 Oracle Streams

Every change made to the database is
 written to the redo log buffer before it is written to the data
block buffer
 written to the redo log file before it is written to the data file

The redo log buffer is flushed to the redo log file when a
COMMIT is issued
© 2005 Julian Dyke
juliandyke.com
What is Redo?

4
Redo log files
 Include all changes made by DML statements
 INSERT
 UPDATE
 DELETE
 SELECT FOR UPDATE
 Do not include DML statement text
 Include all changes made to dictionary objects by DDL
statements
 Include DDL statement text (9.0.1 and above)
 Include all changes made by recursive statements
© 2005 Julian Dyke
juliandyke.com
Buffers and Writers
5
Redo Log
Buffer
LGWR
Redo Log
File
Data Block
Buffer
DBWR
Data File
© 2005 Julian Dyke
juliandyke.com
Logging and Archiving
Arch 1
Group 1
Arch 2
Arch 3
LGWR
Group 2
ARCH
Arch 4
Arch 5
Group 3
Arch 6
Redo Log Files
6
© 2005 Julian Dyke
Archive Log Files
juliandyke.com
Redo Log Files



Redo log uses operating system block size
 usually 512 bytes
 format dependent on
 operating system
 Oracle version
Each redo log consists of
 header
 redo records
Redo log is written sequentially
Block 0
File
Header
7
Block 1
Redo
Header
© 2005 Julian Dyke
Block 2
Block 3
Block 4
Redo
Redo
Redo
Record Records Records
2&3
3&4
1
...
Block M
Redo
Record
N
juliandyke.com
Redo Records

A redo record consists of
 redo record header
 one or more change vectors

Each redo record contains undo and redo for an atomic
change

Some changes do not require undo
Redo
Record
Header
8
Change
#1
© 2005 Julian Dyke
Change
#2
Change
#3
.....
Change
#N
juliandyke.com
Redo Record Header

Every redo record has a header
REDO RECORD - Thread:1 RBA: 0x003666.000000cf.0010 LEN: 0x019c VLD: 0x01
SCN: 0x0000.00eb1279 SUBSCN: 1 05/08/2003 15:44:12

Fields include
Thread
Thread Number
RBA
Redo Byte Address
LEN
Length of record in bytes
SCN
System Change Number
Date and Time of Change
9
© 2005 Julian Dyke
juliandyke.com
Redo Byte Address (RBA)

Every redo record has a Redo Byte Address (RBA) e.g.
RBA: 0x003666.000000cf.0010

RBA is 10 bytes in length

RBA identifies start of redo record
Fields are
 Log sequence number (0x3666)
 Block number within redo log (0xcf)
 Byte number within block (0x10)

10
© 2005 Julian Dyke
juliandyke.com
System Change Number (SCN)



Also called System Commit Number
Defines committed version of database
SCN is 6 bytes in length
SCN:0x0000.0ac67cc3

Contains
 Wrap (2 bytes) e.g 0000
 Base (4 bytes) e.g 0ac67cc3

Base is incremented for each new SCN
Saved in redo record header



11
RAC databases maintain a global SCN
Distributed transactions use highest SCN
© 2005 Julian Dyke
juliandyke.com
Change Vector
12

Describes a change to a single data block

Can apply to
 undo headers
 undo blocks
 data segment headers
 data blocks

Is created in PGA before the data block buffer is modified

Consists of
 header
 array of change record lengths
 array of change records
© 2005 Julian Dyke
juliandyke.com
Change Vector

For example
Change Header
13
Length Vector
16
Change Record 1
20
Change Record 2
48
Change Record 3
28
Change Record 4
29
Change Record 5
2
Change Record 6
2
Change Record 7
10
© 2005 Julian Dyke
juliandyke.com
Change Vector Header

Every change vector has a header e.g.
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0144d023 SCN:0x0000.0ac67cce
SEQ: 4 OP:11.5

14
Fields include
CHANGE
Change number
TYP
Change type
CLS
Class
AFN
Absolute File Number
DBA
Relative Database Block Address
SCN
System Change Number
SEQ
Sequence Number (relative to SCN)
OP
Operation Code
© 2005 Julian Dyke
juliandyke.com
Block Classes


15
Class in change header is equivalent to X$BH.CLASS
Class
Description
1
Data Block
2
Sort Block
3
Deferred Undo Segment Blocks
4
Segment Header Block (Table)
5
Deferred Undo Segment Header Blocks
6
Free List Blocks
7
Extent Map Blocks
8
Space Management Bitmap Blocks
9
Space Management Index Blocks
10
Unused
11 + 2r
Segment Header for Undo Segment r
12 + 2r
Data Blocks for Undo Segment r
e.g. 11 is System Rollback Segment Header
© 2005 Julian Dyke
juliandyke.com
Database Block Address (DBA)

Every database block has a Database Block Address (DBA)
e.g.
DBA:0x0144d023

DBA is 4 bytes in length
Fields are
 Upper 10 bits represent relative file number
 Lower 22 bits represent block number
For example


DECLARE
l_dba NUMBER := TO_NUMBER ('0144D023','XXXXXXXX');
l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
DBMS_OUTPUT.PUT_LINE ('File : '||l_file);
DBMS_OUTPUT.PUT_LINE ('Block : '||l_block);
END;
16
© 2005 Julian Dyke
juliandyke.com
Operation Codes

Each change is represented by an operation in the redo log
There are over 150 different operations
Each operation has an layer code and a sub code e.g. 11.2

Layers include


Layer
17
Description
Layer
Description
4
Block Cleanout
18
Block Image (Hot backups)
5
Transaction Management
19
Direct Loader
10
Index Operations
20
Compatibility Segment
11
Row Operations
22
Locally Managed Tablespaces
13
Segment Management
23
Block Writes
14
Extent Management
24
DDL Statements
17
Tablespace Management
© 2005 Julian Dyke
juliandyke.com
Transactions

The first DML statement in a session creates a transaction
 Allocates an undo segment
 Creates a 5.2 change to update transaction table in undo
segment header
CHANGE #1 TYP:0 CLS:25 AFN:3 DBA:0x00c0012e SCN:0x0000.0ac86eb8 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0010 sqn: 0x0000475a flg: 0x0012 siz: 96 fbi: 0
uba: 0x00c04d20.234b.0e pxid: 0x0000.000.00000000


A commit (or rollback) ends the transaction
A 5.4 change is created for a commit
CHANGE #1 TYP:0 CLS:25 AFN:3 DBA:0x00c0012e SCN:0x0000.0ac86ebf SEQ: 1 OP:5.4
ktucm redo: slt: 0x0010 sqn: 0x0000475a srt: 0 sta: 9 flg: 0x0

18
Rollbacks apply all undo for the transaction followed by a
commit
© 2005 Julian Dyke
juliandyke.com
Transaction ID (XID)

Every transaction has an XID
xid: 0x0004.00e.0000449b
19

XID is 8 bytes in length

Contains
 Undo segment number (USN) of transaction (0x0004)
 Undo segment header transaction table slot (0x00e)
 Sequence number (wrap) (0x0000449b)
© 2005 Julian Dyke
juliandyke.com
Undo Block Address (UBA)

Address of change in undo block
uba: 0x00c01f17.2758.04
20

UBA is 7 bytes in length

Contains
 DBA of undo block (0x00c01f17)
 Sequence number (2758)
 Record number in block (4)
© 2005 Julian Dyke
juliandyke.com
Transactions
Statements
UPDATE t1
SET c2 = 101
WHERE c1 = 1;
Redo Logs
Undo Header
Undo Header
5.2
Undo
Slot 0 c2 = 100
5.1
Redo
11.5
Slot 0 c2 = 101
UPDATE t1
SET c2 = 201
WHERE c1 = 2;
COMMIT;
21
© 2005 Julian Dyke
Undo
Slot 1 c2 = 200
5.1
SLOT
0
Undo Block
Undo
Slot 0 c2 = 100
Undo
Slot 1 c2 = 200
11.5
Redo
Slot 1 c2 = 201
Commit
5.4
STATUS
10
9
Data Block
SLOT
C1
C2
0
1
101
100
1
2
201
200
juliandyke.com
Physiological Logging



22
Oracle normally uses physiological logging
Only changes made to each block are recorded
Statement
Undo
Redo
INSERT INTO t1
VALUES (1, 'ABC');
Delete row 1
Row 1
c1 := 1
c2 := 'ABC'
UPDATE t1
SET c2 = 'DEF'
WHERE c1 = 1;
Row 1
c2 := 'ABC'
Row 1
c2 := 'DEF'
DELETE FROM t1
WHERE c1 = 1;
Row 1
c1 := 1
c2 := 'DEF'
Delete row 1
SELECT c2 FROM t1
WHERE c1 = 1
FOR UPDATE;
Unlock row 1
Lock row 1
Note that INSERT statements generate minimal undo
© 2005 Julian Dyke
juliandyke.com
Redo Record Example

This is an example of a redo log dump for a single row update
UPDATE t1 SET c2 = 20 WHERE c1 = 1;

The previous value of column c2 for this row was 10

The redo record starts with a header
REDO RECORD - Thread:1 RBA: 0x003e12.00000004.01cc LEN: 0x00f8 VLD: 0x01
SCN: 0x0000.0ac73691 SUBSCN: 1 06/26/2003 14:40:14

23
This redo record is 248 (0xF8) bytes in length
© 2005 Julian Dyke
juliandyke.com
Redo Record Example (Continued)

Change 1 updates the undo block
CHANGE #1 TYP:0 CLS:26 AFN:3 DBA:0x00c04ab7 SCN:0x0000.0ac73690 SEQ: 2 OP:5.1
ktudb redo: siz: 104 spc: 1860 flg: 0x0022 seq: 0x233f rec: 0x02
xid: 0x0005.00b.0000460b
ktubu redo: slt: 11 rci: 1 opc: 11.1 objn: 19378 objd: 19378 tsn: 4
Undo type: Regular undo
Undo type: Last buffer split: No
Tablespace Undo: No
0x00000000
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00c04ab7.233f.01
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0144d022 hdba: 0x0144d021
itli: 1 ispac: 0 maxfr: 1177
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
col 1: [ 2] c1 0b
24
© 2005 Julian Dyke
juliandyke.com
Redo Record Example (Continued)

Change 2 updates the data block
CHANGE #2 TYP:0 CLS: 1 AFN:5 DBA:0x0144d022 SCN:0x0000.0ac73690 SEQ: 1 OP:11.5
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00c04ab7.233f.02
KDO Op code: URP row dependencies Disabled
xtype: XA bdba: 0x0144d022 hdba: 0x0144d021
itli: 1 ispac: 0 maxfr: 1177
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 1 ckix: 0
ncol: 2 nnew: 1 size: 0
col 1: [ 2] c1 15
25
© 2005 Julian Dyke
juliandyke.com
Row Operations


26
Row operations generate layer 11 redo
Opcodes include
Opcode
Mnemonic
11.2
IRP
Insert Single Row
11.3
DRP
Delete Single Row
11.4
LKR
Lock Row
11.5
URP
Update Row
11.6
ORP
Chained Row
11.9
CKI
Cluster key index
11.10
SKL
Set cluster key pointers
11.11
QMI
Insert Multiple Rows
11.12
QMD
Delete Multiple Rows
© 2005 Julian Dyke
Description
juliandyke.com
Single Row Insert
Redo
Statements
-- Statement #1
INSERT INTO t1 VALUES (1);
HEADER
5.2
UNDO #1
5.1
REDO #1
11.2
-- Statement #2
INSERT INTO t1 VALUES (2);
UNDO #2
5.1
REDO #2
11.2
-- Statement #3
INSERT INTO t1 VALUES (3);
UNDO #3
5.1
REDO #3
11.2
COMMIT
5.4
COMMIT;
27
© 2005 Julian Dyke
juliandyke.com
Multi Row Insert
Redo
Statements
-- Statement #1
INSERT INTO t1
SELECT * FROM t2;
COMMIT;
28
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.11
COMMIT
5.4
juliandyke.com
Single Row Update
Redo
Statements
-- Statement #1
UPDATE t1 SET c2 = c2 + 1
WHERE c1 = 1;
-- Statement #2
UPDATE t1 SET c2 = c2 + 1
WHERE c1 = 2;
-- Statement #3
UPDATE t1 SET c2 = c2 + 1
WHERE c1 = 3;
COMMIT;
29
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.5
UNDO #2
5.1
REDO #2
11.5
UNDO #3
5.1
REDO #3
11.5
COMMIT
5.4
juliandyke.com
Multi Row Update
Redo
Statements
-- T1 contains 3 rows
UPDATE t1 SET c2 = c2 + 1;
COMMIT;
30
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.5
UNDO #2
5.1
REDO #2
11.5
UNDO #3
5.1
REDO #3
11.5
COMMIT
5.4
juliandyke.com
Single Row Delete
Redo
Statements
-- Statement #1
DELETE FROM t1
WHERE c1 = 1;
-- Statement #2
DELETE FROM t1
WHERE c1 = 2;
-- Statement #3
DELETE FROM t1
WHERE c1 = 3;
COMMIT;
31
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.3
UNDO #2
5.1
REDO #2
11.3
UNDO #3
5.1
REDO #3
11.3
COMMIT
5.4
juliandyke.com
Multi Row Delete
Redo
Statements
-- T1 contains 3 rows
DELETE FROM t1;
COMMIT;
32
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.3
UNDO #2
5.1
REDO #2
11.3
UNDO #3
5.1
REDO #3
11.3
COMMIT
5.4
juliandyke.com
Single Row Select For Update
Redo
Statements
-- Statement #1
SELECT c2 FROM t1
WHERE c1 = 1
FOR UPDATE;
-- Statement #2
UPDATE t1
SET c2 = c2 + 1
WHERE c1 = 1;
COMMIT;
33
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.4
UNDO #2
5.1
REDO #2
11.5
COMMIT
5.4
juliandyke.com
Multi Row Select For Update
Redo
Statements
-- T1 contains 3 rows
SELECT c2 FROM t1
FOR UPDATE;
COMMIT;
34
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.4
UNDO #2
5.1
REDO #2
11.4
UNDO #3
5.1
REDO #3
11.4
COMMIT
5.4
juliandyke.com
Rollback
Redo
Statements
HEADER
5.2
UNDO #1
5.1
REDO #1
11.2
-- Statement #2
INSERT INTO t1 VALUES (2);
UNDO #2
5.1
REDO #2
11.2
ROLLBACK;
UNDO #3
11.3
REDO #3
5.6
UNDO #4
11.3
REDO #4
5.11
COMMIT
5.4
-- Statement #1
INSERT INTO t1 VALUES (1);
35
© 2005 Julian Dyke
juliandyke.com
Global Temporary Tables
Redo
Statements
-- Statement #1
INSERT INTO t1 VALUES (1);
-- Statement #2
INSERT INTO t1 VALUES (2);
-- Statement #3
INSERT INTO t1 VALUES (3);
COMMIT;
36
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.2
UNDO #2
5.1
REDO #2
11.2
UNDO #3
5.1
REDO #3
11.2
COMMIT
5.4
juliandyke.com
Index Operations


37
Index operations generate layer 10 redo
Opcodes include
Opcode
Description
Opcode
Description
10.2
Insert Leaf Row
10.13
Make leaf block empty
10.3
Purge Leaf Row
10.15
Insert branch row
10.4
Delete Leaf Row
10.16
Purge branch row
10.5
Restore Leaf Log
10.18
Update key data in row
10.6
Lock block
10.19
Clear split flag
10.7
Clear block opcode on commit
10.21
Undo branch operation
10.8
Initialise header
10.22
Undo leaf operation
10.9
Apply XAT do to ITL 1
10.24
Shrink ITL
10.10
Set leaf block next pointer
10.30
Update nonkey value
10.11
Set leaf block previous pointer
10.31
Create/Load Index
10.12
Initialize root block after split
10.34
Make leaf block empty
© 2005 Julian Dyke
juliandyke.com
Index Insert
Redo
Statements
-- Insert into table t1
INSERT INTO t1
VALUES (1,10);
-- Insert into index i1
-- Insert into index i2
COMMIT;
38
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.2
UNDO #2
5.1
REDO #2
10.2
UNDO #3
5.1
REDO #3
10.2
COMMIT
5.4
juliandyke.com
Index Updates
Redo
Statements
-- Update table t1
UPDATE t1
SET c1 = 2
WHERE c1 = 1;
-- Delete from index i1
-- Insert into index i1
COMMIT;
39
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.5
UNDO #2
5.1
REDO #2
10.4
UNDO #3
5.1
REDO #3
10.2
COMMIT
5.4
juliandyke.com
Index Deletes
Redo
Statements
-- Delete from table t1
DELETE FROM t1
WHERE c1 = 1;
-- Delete from index i1
-- Delete from index i2
COMMIT;
40
© 2005 Julian Dyke
HEADER
5.2
UNDO #1
5.1
REDO #1
11.3
UNDO #2
5.1
REDO #2
10.4
UNDO #3
5.1
REDO #3
10.4
COMMIT
5.4
juliandyke.com
Conditional Index Updates
Before
After
1
2
UPDATE t1
SET c1 = 2
WHERE c1 = 1;
Update UNDO
5.1
table
#1
REDO #1 11.5
row
41
Delete
index
row
5.1
UNDO
#2
REDO #2 10.4
Insert
index
row
5.1
UNDO
#3
REDO #3 10.2
© 2005 Julian Dyke
Before
After
1
1
Before
After
1
NULL
UPDATE t1
SET c1 = 1
WHERE c1 = 1
UPDATE t1
SET c1 = NULL
WHERE c1 = 1;
5.1
UNDO
#1
REDO #1 11.5
5.1
UNDO
#1
REDO #1 11.5
Before
After
NULL
1
UPDATE t1
SET c1 = 1
WHERE c1 IS NULL
5.1
UNDO
#1
REDO #1 11.5
5.1
UNDO
#2
REDO #2 10.4
5.1
UNDO
#3
REDO #3 10.2
juliandyke.com
Block Cleanout

Occurs when ITL on data block has not been updated when
changes were committed
REDO RECORD - Thread:1 RBA: 0x003669.00000002.01ac LEN: 0x003c VLD: 0x01
SCN: 0x0000.00eb3219 SUBSCN: 1 05/08/2003 17:53:41
CHANGE #1 TYP:2 CLS: 1 AFN:5 DBA:0x0142fca2 SCN:0x0000.00eb3201 SEQ: 1
OP:4.1
Block cleanout record, scn: 0x0000.00eb3219 ver: 0x01 opt: 0x02, entries follow...
itli: 2 flg: 2 scn: 0x0000.00eb3201

42
Block Cleanout redo
 Can be generated by SELECT statements
 Opcode 4.1
 requires 60 byte redo record for each block
© 2005 Julian Dyke
juliandyke.com
Backups

If hot backups are in use, when backup mode is enabled
ALTER TABLESPACE <tablespace_name> BEGIN BACKUP;

A block image is written to the redo log for each block that is
modified (opcode 18.1)
REDO RECORD - Thread:1 RBA: 0x003e3b.00000005.0010 LEN: 0x0818 VLD: 0x01
SCN: 0x0000.0ac84345 SUBSCN: 1 06/27/2003 15:06:06
CHANGE #1 TYP:3 CLS: 1 AFN:5 DBA:0x014343c2 SCN:0x0000.0ac8433d SEQ: 1 OP:18.1
Log block image redo entry

When backup mode is disabled
ALTER TABLESPACE <tablespace_name> END BACKUP;

an end backup marker is written to redo log (17.1)
REDO RECORD - Thread:1 RBA: 0x003e3b.0000000c.0010 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.0ac84347 SUBSCN: 1 06/27/2003 15:06:09
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker - file:5 scn: 0x0000.0ac84341
43
© 2005 Julian Dyke
juliandyke.com
Block Writes


In Oracle 9.0.1 and above blocks written from buffer cache to
disk by DBWR are recorded in redo logs
Supports fast recovery
REDO RECORD - Thread:1 RBA: 0x003e34.00000012.0010 LEN: 0x2948 VLD: 0x02
SCN: 0x0000.0ac8289c SUBSCN: 1 06/27/2003 13:19:44
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 5 rdba: 0x014238ad(5,145581)
scn: 0x0000.0ac82889 seq: 0x07 flg:0x04
Block Written - afn: 5 rdba: 0x014238ac(5,145580)
scn: 0x0000.0ac82889 seq: 0x08 flg:0x04
Block Written - afn: 5 rdba: 0x014238ab(5,145579)
scn: 0x0000.0ac82889 seq: 0x07 flg:0x04
Block Written - afn: 5 rdba: 0x014238aa(5,145578)
scn: 0x0000.0ac82889 seq: 0x07 flg:0x04

44
Each block record is 32 bytes in length (OS dependent)
© 2005 Julian Dyke
juliandyke.com
Direct Loads


If archiving is enabled then direct load blocks are written to
redo
In Oracle 9.2 each block requires a 19.1 and 24.2 change
REDO RECORD - Thread:1 RBA: 0x003e37.00000007.0014 LEN: 0x0848 VLD: 0x01
SCN: 0x0000.0ac83828 SUBSCN: 1 06/27/2003 14:21:50
CHANGE #1 TYP:1 CLS: 1 AFN:5 DBA:0x0142b962 SCN:0x0000.0ac83826 SEQ: 1 OP:19.1
Direct Loader block redo entry
Block header dump: 0x0e5c0000
Object id on Block? Y
seg/obj: 0x4bd9 csc: 0x00.ac83825 itc: 3 flg: - typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl
Xid
Uba
Flag Lck
Scn/Fsc#
0x01 0x0003.003.00004540 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
-- Block dump follows
CHANGE #2 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:24.2
45
© 2005 Julian Dyke
juliandyke.com
NOLOGGING

Redo generation can be disabled for some operations using
 UNRECOVERABLE (Oracle 7)
 NOLOGGING (Oracle 8.0 and above)

Can be enabled for specific tables and indexes at
 object level
 statement level

The following redo log entry indicates a range of blocks
which cannot be recovered
REDO RECORD - Thread:1 RBA: 0x003674.00000006.01e0 LEN: 0x0028 VLD: 0x01
SCN: 0x0000.00ebaeec SUBSCN: 1 05/09/2003 11:16:07
CHANGE #1 INVLD AFN:5 DBA:0x0142ff03 BLKS:0x001f SCN:0x0000.00ebaeec
SEQ: 1 OP:19.2
Direct Loader invalidate block range redo entry
46
© 2005 Julian Dyke
juliandyke.com
NOLOGGING



47
These tests were performed on an ARCHIVELOG database
using a 100000 row table
Operation
LOGGING NOLOGGING
CREATE TABLE AS SELECT
14238844
39548
ALTER TABLE MOVE
14227236
45340
INSERT /*+ APPEND */
14221904
42452
CREATE MATERIALIZED VIEW
20726784
3784532
CREATE INDEX
2042532
24548
ALTER INDEX REBUILD
2056440
32192
ALTER INDEX REBUILD ONLINE
2083832
67840
SQL*Loader (Direct)
14248116
56712
Online Reorganization
21330788
7169472
Redo size in bytes
Median size from 3 runs
© 2005 Julian Dyke
juliandyke.com
FORCE NOLOGGING



Objects created using NOLOGGING cannot be recovered
Backup should be taken immediately affect using
NOLOGGING
In Oracle 9.2 and above NOLOGGING can be disabled using
STARTUP MOUNT
ALTER DATABASE FORCE LOGGING;

or
ALTER TABLESPACE <tablespace_name>
FORCE LOGGING;
48
© 2005 Julian Dyke
juliandyke.com
Transaction Auditing



Introduced in Oracle 8.1.5 to support Log Miner
Enabled by default
Each session includes 5.19 change vector
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.19
session number
=9
serial number
=5
current username = US01
login username = US01
client info
=
OS username
= BMC.COM\jdyke
Machine name
= BMC.COM\JDYKE-ABI-EU
OS terminal
= JDYKE-ABI-EU
OS process id
= 1784:1724
OS program name = sqlplusw.exe
transaction name =


49
Last change of first redo record in session
Record length is variable
© 2005 Julian Dyke
juliandyke.com
Transaction Auditing

Each subsequent transaction includes 5.20 change vector
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:5.20
session number
=9
serial number
=5
transaction name =



Last change of second and subsequent redo records in
session
Record length is variable
If LogMiner is not in use, transaction auditing can be disabled
by setting initialisation parameter
TRANSACTION_AUDITING = FALSE;
50
© 2005 Julian Dyke
juliandyke.com
Supplemental Logging
51

Introduced in Oracle 9.0.1 to enable LogMiner to support
 Chained rows
 Cluster keys

Extended in Oracle 9.2 to support Oracle Streams
 Used with logical standby databases
 ROWIDs may differ therefore cannot be used
 Allows updated rows to be identified logically

Can be specified at
 Database level
 Table level

Enabled by default in 9.0.1; disabled by default in 9.2.0
© 2005 Julian Dyke
juliandyke.com
Supplemental Logging

At database level can enable
 minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

identification key supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY
KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(UNIQUE KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE INDEX) COLUMNS;

Disabled using
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


52
Oracle selects a subset of columns to include in the redo log
Not necessarily unique
© 2005 Julian Dyke
juliandyke.com
Supplemental Logging


At table level columns must be specified
If ALWAYS keyword is specified, all specified columns will be
logged for every update
ALTER TABLE table1
ADD SUPPLEMENTAL LOG GROUP group1 (c2,c3) ALWAYS;

Otherwise specified columns are only logged if one of them is
updated
ALTER TABLE table1
ADD SUPPLEMENTAL LOG GROUP group1 (c2,c3);

Disabled using
ALTER TABLE table1
DROP SUPPLEMENTAL LOG GROUP group1;

53
See data dictionary views
 DBA_LOG_GROUPS
 DBA_LOG_GROUP_COLUMNS
© 2005 Julian Dyke
juliandyke.com
Supplemental Logging

Additional columns are appended to undo data
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER,c3 NUMBER);
INSERT INTO t1 VALUES (100, 200, 300);
ALTER TABLE table1
ADD SUPPLEMENTAL LOG GROUP group1 (c1,c2,c3) ALWAYS;
Statements
-- Statement #1
UPDATE t1
SET c3 = 400
WHERE c1 = 100;
Redo
HEADER
HEADER
UNDO #1
5.25.2
5.1
c3 = 300
c1 = 100
c2 = 200
REDO #1
11.5
c3 = 400
54
© 2005 Julian Dyke
juliandyke.com
Dumping Logfiles

To dump the contents of a logfile use
ALTER SYSTEM DUMP LOGFILE 'pathname';

A range of data file blocks can be specified
ALTER SYSTEM DUMP LOGFILE 'pathname';
DBA MIN <fileno> . <blockno>
DBA MAX <fileno> . <blockno>

A range of redo byte addresses can be specified
ALTER SYSTEM DUMP LOGFILE 'pathname';
RBA MIN <logfile> . <sequenceno>
RBA MAX <logfile> . <sequenceno>

A layer and (optional) opcode can be specified
ALTER SYSTEM DUMP LOGFILE 'pathname';
LAYER <integer>
OPCODE <integer>
55
© 2005 Julian Dyke
juliandyke.com
Thank you for your interest
For more information and to provide feedback
please contact me
My e-mail address is:
[email protected]
My website address is:
www.juliandyke.com
56
© 2005 Julian Dyke
juliandyke.com