Redo Internals Julian Dyke Independent Consultant Web Version

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 u Redo logs contain a history of all changes made to the database u Redo log files are used by u Recovery (instance and media) u Log Miner u Oracle Streams u Every change made to the database is u written to the redo log buffer before it is written to the data block buffer u written to the redo log file before it is written to the data file u The redo log buffer is flushed to the redo log file when a COMMIT is issued © 2005 Julian Dyke juliandyke. com

What is Redo? u 4 Redo log files u Include all changes made by DML statements u INSERT u UPDATE u DELETE u SELECT FOR UPDATE u Do not include DML statement text u Include all changes made to dictionary objects by DDL statements u Include DDL statement text (9. 0. 1 and above) u 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

Logging and Archiving Arch 1 Group 1 Arch 2 Arch 3 LGWR Group 2

Redo Log Files u u u Redo log uses operating system block size u usually 512 bytes u format dependent on u operating system u Oracle version Each redo log consists of u header u 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 Records 2&3 3&4 1 . . . Block M Redo Record N juliandyke. com

Redo Records u A redo record consists of u redo record header u one or more change vectors u Each redo record contains undo and redo for an atomic change u Some changes do not require undo Record Header 8 Change #1 © 2005 Julian Dyke Change #2 Change #3 . . . Change #N juliandyke. com

Redo Record Header u Every redo record has a header REDO RECORD - Thread: 1 RBA: 0 x 003666. 000000 cf. 0010 LEN: 0 x 019 c VLD: 0 x 01 SCN: 0 x 0000. 00 eb 1279 SUBSCN: 1 05/08/2003 15: 44: 12 u Fields include 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) u Every redo record has a Redo Byte Address (RBA) e. g. RBA: 0 x 003666. 000000 cf. 0010 u RBA is 10 bytes in length u RBA identifies start of redo record Fields are u Log sequence number (0 x 3666) u Block number within redo log (0 xcf) u Byte number within block (0 x 10) u 10 © 2005 Julian Dyke juliandyke. com

System Change Number (SCN) u u u Also called System Commit Number Defines committed version of database SCN is 6 bytes in length SCN: 0 x 0000. 0 ac 67 cc 3 u Contains u Wrap (2 bytes) e. g 0000 u Base (4 bytes) e. g 0 ac 67 cc 3 u Base is incremented for each new SCN Saved in redo record header u u u 11 RAC databases maintain a global SCN Distributed transactions use highest SCN © 2005 Julian Dyke juliandyke. com

Change Vector 12 u Describes a change to a single data block u Can apply to u undo headers u undo blocks u data segment headers u data blocks u Is created in PGA before the data block buffer is modified u Consists of u header u array of change record lengths u array of change records © 2005 Julian Dyke juliandyke. com

Change Vector u 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 u Every change vector has a header e. g. CHANGE #2 TYP: 0 CLS: 1 AFN: 5 DBA: 0 x 0144 d 023 SCN: 0 x 0000. 0 ac 67 cce SEQ: 4 OP: 11. 5 u 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 u u 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 + 2 r Segment Header for Undo Segment r 12 + 2 r Data Blocks for Undo Segment r e. g. 11 is System Rollback Segment Header © 2005 Julian Dyke juliandyke. com

Database Block Address (DBA) u Every database block has a Database Block Address (DBA) e. g. DBA: 0 x 0144 d 023 u u u DBA is 4 bytes in length Fields are u Upper 10 bits represent relative file number u Lower 22 bits represent block number For example DECLARE l_dba NUMBER : = TO_NUMBER ('0144 D 023', 'XXXX'); 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 u 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 u Layers include u u 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 u The first DML statement in a session creates a transaction u Allocates an undo segment u Creates a 5. 2 change to update transaction table in undo segment header CHANGE #1 TYP: 0 CLS: 25 AFN: 3 DBA: 0 x 00 c 0012 e SCN: 0 x 0000. 0 ac 86 eb 8 SEQ: 1 OP: 5. 2 ktudh redo: slt: 0 x 0010 sqn: 0 x 0000475 a flg: 0 x 0012 siz: 96 fbi: 0 uba: 0 x 00 c 04 d 20. 234 b. 0 e pxid: 0 x 0000 u u 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: 0 x 00 c 0012 e SCN: 0 x 0000. 0 ac 86 ebf SEQ: 1 OP: 5. 4 ktucm redo: slt: 0 x 0010 sqn: 0 x 0000475 a srt: 0 sta: 9 flg: 0 x 0 u 18 Rollbacks apply all undo for the transaction followed by a commit © 2005 Julian Dyke juliandyke. com

Transaction ID (XID) u Every transaction has an XID xid: 0 x 0004. 00 e. 0000449 b 19 u XID is 8 bytes in length u Contains u Undo segment number (USN) of transaction (0 x 0004) u Undo segment header transaction table slot (0 x 00 e) u Sequence number (wrap) (0 x 0000449 b) © 2005 Julian Dyke juliandyke. com

Undo Block Address (UBA) u Address of change in undo block uba: 0 x 00 c 01 f 17. 2758. 04 20 u UBA is 7 bytes in length u Contains u DBA of undo block (0 x 00 c 01 f 17) u Sequence number (2758) u Record number in block (4) © 2005 Julian Dyke juliandyke. com

Transactions Statements UPDATE t 1 SET c 2 = 101 WHERE c 1 = 1; Redo Logs Undo Header 5. 2 Undo Slot 0 c 2 = 100 5. 1 Redo 11. 5 Slot 0 c 2 = 101 UPDATE t 1 SET c 2 = 201 WHERE c 1 = 2; COMMIT; 21 © 2005 Julian Dyke Undo Slot 1 c 2 = 200 5. 1 SLOT 0 Undo Block Undo Slot 0 c 2 = 100 Undo Slot 1 c 2 = 200 11. 5 Redo Slot 1 c 2 = 201 Commit 5. 4 STATUS 10 9 Data Block SLOT C 1 C 2 0 1 100 1 2 201 200 juliandyke. com

Physiological Logging u u u 22 Oracle normally uses physiological logging Only changes made to each block are recorded Statement Undo Redo INSERT INTO t 1 VALUES (1, 'ABC'); Delete row 1 Row 1 c 1 : = 1 c 2 : = 'ABC' UPDATE t 1 SET c 2 = 'DEF' WHERE c 1 = 1; Row 1 c 2 : = 'ABC' Row 1 c 2 : = 'DEF' DELETE FROM t 1 WHERE c 1 = 1; Row 1 c 1 : = 1 c 2 : = 'DEF' Delete row 1 SELECT c 2 FROM t 1 WHERE c 1 = 1 FOR UPDATE; Unlock row 1 Lock row 1 Note that INSERT statements generate minimal undo © 2005 Julian Dyke juliandyke. com

Redo Record Example u This is an example of a redo log dump for a single row update UPDATE t 1 SET c 2 = 20 WHERE c 1 = 1; u The previous value of column c 2 for this row was 10 u The redo record starts with a header REDO RECORD - Thread: 1 RBA: 0 x 003 e 12. 00000004. 01 cc LEN: 0 x 00 f 8 VLD: 0 x 01 SCN: 0 x 0000. 0 ac 73691 SUBSCN: 1 06/26/2003 14: 40: 14 u 23 This redo record is 248 (0 x. F 8) bytes in length © 2005 Julian Dyke juliandyke. com

Redo Record Example (Continued) u Change 1 updates the undo block CHANGE #1 TYP: 0 CLS: 26 AFN: 3 DBA: 0 x 00 c 04 ab 7 SCN: 0 x 0000. 0 ac 73690 SEQ: 2 OP: 5. 1 ktudb redo: siz: 104 spc: 1860 flg: 0 x 0022 seq: 0 x 233 f rec: 0 x 02 xid: 0 x 0005. 00 b. 0000460 b 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 0 x 0000 KDO undo record: KTB Redo op: 0 x 02 ver: 0 x 01 op: C uba: 0 x 00 c 04 ab 7. 233 f. 01 KDO Op code: URP row dependencies Disabled xtype: XA bdba: 0 x 0144 d 022 hdba: 0 x 0144 d 021 itli: 1 ispac: 0 maxfr: 1177 tabn: 0 slot: 2(0 x 2) flag: 0 x 2 c lock: 0 ckix: 0 ncol: 2 nnew: 1 size: 0 col 1: [ 2] c 1 0 b 24 © 2005 Julian Dyke juliandyke. com

Redo Record Example (Continued) u Change 2 updates the data block CHANGE #2 TYP: 0 CLS: 1 AFN: 5 DBA: 0 x 0144 d 022 SCN: 0 x 0000. 0 ac 73690 SEQ: 1 OP: 11. 5 KTB Redo op: 0 x 02 ver: 0 x 01 op: C uba: 0 x 00 c 04 ab 7. 233 f. 02 KDO Op code: URP row dependencies Disabled xtype: XA bdba: 0 x 0144 d 022 hdba: 0 x 0144 d 021 itli: 1 ispac: 0 maxfr: 1177 tabn: 0 slot: 2(0 x 2) flag: 0 x 2 c lock: 1 ckix: 0 ncol: 2 nnew: 1 size: 0 col 1: [ 2] c 1 15 25 © 2005 Julian Dyke juliandyke. com

Row Operations u u 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 t 1 VALUES (1); HEADER 5. 2 UNDO #1 5. 1 REDO #1 11. 2 -- Statement #2 INSERT INTO t 1 VALUES (2); UNDO #2 5. 1 REDO #2 11. 2 -- Statement #3 INSERT INTO t 1 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 t 1 SELECT *

Single Row Update Redo Statements -- Statement #1 UPDATE t 1 SET c 2 = c 2 + 1 WHERE c 1 = 1; -- Statement #2 UPDATE t 1 SET c 2 = c 2 + 1 WHERE c 1 = 2; -- Statement #3 UPDATE t 1 SET c 2 = c 2 + 1 WHERE c 1 = 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 -- T 1 contains 3 rows UPDATE t 1 SET c 2 = c 2 + 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 t 1 WHERE c 1 = 1; -- Statement #2 DELETE FROM t 1 WHERE c 1 = 2; -- Statement #3 DELETE FROM t 1 WHERE c 1 = 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 -- T 1 contains 3 rows DELETE FROM t 1; 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 c 2 FROM t 1 WHERE c 1 = 1 FOR UPDATE; -- Statement #2 UPDATE t 1 SET c 2 = c 2 + 1 WHERE c 1 = 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 -- T 1 contains 3 rows SELECT c 2 FROM t 1 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 t 1 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 t 1 VALUES (1); 35 © 2005 Julian Dyke juliandyke. com

Global Temporary Tables Redo Statements -- Statement #1 INSERT INTO t 1 VALUES (1); HEADER 5. 2 UNDO #1 5. 1 REDO #1 11. 2 -- Statement #2 INSERT INTO t 1 VALUES (2); UNDO #2 5. 1 REDO #2 11. 2 -- Statement #3 INSERT INTO t 1 VALUES (3); UNDO #3 5. 1 REDO #3 11. 2 COMMIT 5. 4 COMMIT; 36 © 2005 Julian Dyke juliandyke. com

Index Operations u u 37 Index operations generate layer 10 redo Opcodes include 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 t 1 INSERT INTO t 1 VALUES (1, 10); HEADER 5. 2 UNDO #1 5. 1 REDO #1 11. 2 -- Insert into index i 1 UNDO #2 5. 1 REDO #2 10. 2 UNDO #3 5. 1 REDO #3 10. 2 COMMIT 5. 4 -- Insert into index i 2 COMMIT; 38 © 2005 Julian Dyke juliandyke. com

Index Updates Redo Statements -- Update table t 1 UPDATE t 1 SET c 1 = 2 WHERE c 1 = 1; -- Delete from index i 1 -- Insert into index i 1 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 t 1 DELETE FROM t 1 WHERE c 1 = 1; -- Delete from index i 1 -- Delete from index i 2 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 t 1 SET c 1 = 2 WHERE c 1 = 1; Update UNDO #1 5. 1 table REDO #1 11. 5 row 41 Delete index row UNDO #2 Insert index row UNDO #3 5. 1 REDO #2 10. 4 5. 1 REDO #3 10. 2 © 2005 Julian Dyke Before After 1 1 UPDATE t 1 SET c 1 = 1 WHERE c 1 = 1 UNDO #1 5. 1 REDO #1 11. 5 Before After 1 NULL UPDATE t 1 SET c 1 = NULL WHERE c 1 = 1; UNDO #1 5. 1 REDO #1 11. 5 UNDO #2 Before After NULL 1 UPDATE t 1 SET c 1 = 1 WHERE c 1 IS NULL UNDO #1 5. 1 REDO #1 11. 5 5. 1 REDO #2 10. 4 UNDO #3 5. 1 REDO #3 10. 2 juliandyke. com

Block Cleanout u Occurs when ITL on data block has not been updated when changes were committed REDO RECORD - Thread: 1 RBA: 0 x 003669. 00000002. 01 ac LEN: 0 x 003 c VLD: 0 x 01 SCN: 0 x 0000. 00 eb 3219 SUBSCN: 1 05/08/2003 17: 53: 41 CHANGE #1 TYP: 2 CLS: 1 AFN: 5 DBA: 0 x 0142 fca 2 SCN: 0 x 0000. 00 eb 3201 SEQ: 1 OP: 4. 1 Block cleanout record, scn: 0 x 0000. 00 eb 3219 ver: 0 x 01 opt: 0 x 02, entries follow. . . itli: 2 flg: 2 scn: 0 x 0000. 00 eb 3201 u 42 Block Cleanout redo u Can be generated by SELECT statements u Opcode 4. 1 u requires 60 byte redo record for each block © 2005 Julian Dyke juliandyke. com

Backups u If hot backups are in use, when backup mode is enabled ALTER TABLESPACE <tablespace_name> BEGIN BACKUP; u A block image is written to the redo log for each block that is modified (opcode 18. 1) REDO RECORD - Thread: 1 RBA: 0 x 003 e 3 b. 00000005. 0010 LEN: 0 x 0818 VLD: 0 x 01 SCN: 0 x 0000. 0 ac 84345 SUBSCN: 1 06/27/2003 15: 06 CHANGE #1 TYP: 3 CLS: 1 AFN: 5 DBA: 0 x 014343 c 2 SCN: 0 x 0000. 0 ac 8433 d SEQ: 1 OP: 18. 1 Log block image redo entry u When backup mode is disabled ALTER TABLESPACE <tablespace_name> END BACKUP; u an end backup marker is written to redo log (17. 1) REDO RECORD - Thread: 1 RBA: 0 x 003 e 3 b. 0000000 c. 0010 LEN: 0 x 0038 VLD: 0 x 01 SCN: 0 x 0000. 0 ac 84347 SUBSCN: 1 06/27/2003 15: 06: 09 CHANGE #1 MEDIA RECOVERY MARKER SCN: 0 x 0000 SEQ: 0 OP: 17. 1 End backup marker - file: 5 scn: 0 x 0000. 0 ac 84341 43 © 2005 Julian Dyke juliandyke. com

Block Writes u u 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: 0 x 003 e 34. 00000012. 0010 LEN: 0 x 2948 VLD: 0 x 02 SCN: 0 x 0000. 0 ac 8289 c SUBSCN: 1 06/27/2003 13: 19: 44 CHANGE #1 MEDIA RECOVERY MARKER SCN: 0 x 0000 SEQ: 0 OP: 23. 1 Block Written - afn: 5 rdba: 0 x 014238 ad(5, 145581) scn: 0 x 0000. 0 ac 82889 seq: 0 x 07 flg: 0 x 04 Block Written - afn: 5 rdba: 0 x 014238 ac(5, 145580) scn: 0 x 0000. 0 ac 82889 seq: 0 x 08 flg: 0 x 04 Block Written - afn: 5 rdba: 0 x 014238 ab(5, 145579) scn: 0 x 0000. 0 ac 82889 seq: 0 x 07 flg: 0 x 04 Block Written - afn: 5 rdba: 0 x 014238 aa(5, 145578) scn: 0 x 0000. 0 ac 82889 seq: 0 x 07 flg: 0 x 04 u 44 Each block record is 32 bytes in length (OS dependent) © 2005 Julian Dyke juliandyke. com

Direct Loads u u 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: 0 x 003 e 37. 00000007. 0014 LEN: 0 x 0848 VLD: 0 x 01 SCN: 0 x 0000. 0 ac 83828 SUBSCN: 1 06/27/2003 14: 21: 50 CHANGE #1 TYP: 1 CLS: 1 AFN: 5 DBA: 0 x 0142 b 962 SCN: 0 x 0000. 0 ac 83826 SEQ: 1 OP: 19. 1 Direct Loader block redo entry Block header dump: 0 x 0 e 5 c 0000 Object id on Block? Y seg/obj: 0 x 4 bd 9 csc: 0 x 00. ac 83825 itc: 3 flg: - typ: 1 – DATA fsl: 0 fnx: 0 x 0 ver: 0 x 01 Itl Xid Uba Flag Lck Scn/Fsc# 0 x 01 0 x 0003. 00004540 0 x 0000. 00 ---- 0 fsc 0 x 0000 0 x 02 0 x 0000. 00 ---- 0 fsc 0 x 0000 0 x 03 0 x 0000. 00 ---- 0 fsc 0 x 0000 -- Block dump follows CHANGE #2 MEDIA RECOVERY MARKER SCN: 0 x 0000 SEQ: 0 OP: 24. 2 45 © 2005 Julian Dyke juliandyke. com

NOLOGGING u Redo generation can be disabled for some operations using u UNRECOVERABLE (Oracle 7) u NOLOGGING (Oracle 8. 0 and above) u Can be enabled for specific tables and indexes at u object level u statement level u The following redo log entry indicates a range of blocks which cannot be recovered REDO RECORD - Thread: 1 RBA: 0 x 003674. 00000006. 01 e 0 LEN: 0 x 0028 VLD: 0 x 01 SCN: 0 x 0000. 00 ebaeec SUBSCN: 1 05/09/2003 11: 16: 07 CHANGE #1 INVLD AFN: 5 DBA: 0 x 0142 ff 03 BLKS: 0 x 001 f SCN: 0 x 0000. 00 ebaeec SEQ: 1 OP: 19. 2 Direct Loader invalidate block range redo entry 46 © 2005 Julian Dyke juliandyke. com

NOLOGGING u u u 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 u u u 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; u or ALTER TABLESPACE <tablespace_name> FORCE LOGGING; 48 © 2005 Julian Dyke juliandyke. com

Transaction Auditing u u u 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: 0 x 0000 SEQ: 0 OP: 5. 19 session number =9 serial number =5 current username = US 01 login username = US 01 client info = OS username = BMC. COMjdyke Machine name = BMC. COMJDYKE-ABI-EU OS terminal = JDYKE-ABI-EU OS process id = 1784: 1724 OS program name = sqlplusw. exe transaction name = u u 49 Last change of first redo record in session Record length is variable © 2005 Julian Dyke juliandyke. com

Transaction Auditing u Each subsequent transaction includes 5. 20 change vector CHANGE #4 MEDIA RECOVERY MARKER SCN: 0 x 0000 SEQ: 0 OP: 5. 20 session number =9 serial number =5 transaction name = u u u Last change of second and subsequent redo records in session Record length is variable If Log. Miner 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 u Introduced in Oracle 9. 0. 1 to enable Log. Miner to support u Chained rows u Cluster keys u Extended in Oracle 9. 2 to support Oracle Streams u Used with logical standby databases u ROWIDs may differ therefore cannot be used u Allows updated rows to be identified logically u Can be specified at u Database level u Table level u Enabled by default in 9. 0. 1; disabled by default in 9. 2. 0 © 2005 Julian Dyke juliandyke. com

Supplemental Logging u At database level can enable u minimal supplemental logging ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; u 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; u Disabled using ALTER DATABASE DROP SUPPLEMENTAL LOG DATA; u u 52 Oracle selects a subset of columns to include in the redo log Not necessarily unique © 2005 Julian Dyke juliandyke. com

Supplemental Logging u u At table level columns must be specified If ALWAYS keyword is specified, all specified columns will be logged for every update ALTER TABLE table 1 ADD SUPPLEMENTAL LOG GROUP group 1 (c 2, c 3) ALWAYS; u Otherwise specified columns are only logged if one of them is updated ALTER TABLE table 1 ADD SUPPLEMENTAL LOG GROUP group 1 (c 2, c 3); u Disabled using ALTER TABLE table 1 DROP SUPPLEMENTAL LOG GROUP group 1; u 53 See data dictionary views u DBA_LOG_GROUPS u DBA_LOG_GROUP_COLUMNS © 2005 Julian Dyke juliandyke. com

Supplemental Logging u Additional columns are appended to undo data CREATE TABLE t 1 (c 1 NUMBER, c 2 NUMBER, c 3 NUMBER); INSERT INTO t 1 VALUES (100, 200, 300); ALTER TABLE table 1 ADD SUPPLEMENTAL LOG GROUP group 1 (c 1, c 2, c 3) ALWAYS; Statements -- Statement #1 UPDATE t 1 SET c 3 = 400 WHERE c 1 = 100; Redo HEADER UNDO #1 5. 2 5. 1 c 3 = 300 c 1 = 100 c 2 = 200 REDO #1 11. 5 c 3 = 400 54 © 2005 Julian Dyke juliandyke. com

Dumping Logfiles u To dump the contents of a logfile use ALTER SYSTEM DUMP LOGFILE 'pathname'; u A range of data file blocks can be specified ALTER SYSTEM DUMP LOGFILE 'pathname'; DBA MIN <fileno>. <blockno> DBA MAX <fileno>. <blockno> u A range of redo byte addresses can be specified ALTER SYSTEM DUMP LOGFILE 'pathname'; RBA MIN <logfile>. <sequenceno> RBA MAX <logfile>. <sequenceno> u 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: info@juliandyke. com My website address is: www. juliandyke. com 56 © 2005 Julian Dyke juliandyke. com