Supplemental Logging Julian Dyke Independent Consultant Web Version

Supplemental Logging 2 © 2006 Julian Dyke juliandyke. com

Supplemental Logging Example ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; CREATE TABLE t 1 ( c 1 NUMBER, c 2 NUMBER, c 3 NUMBER ); ALTER TABLE t 1 ADD CONSTRAINT t 1_pk PRIMARY KEY (c 1, c 2); INSERT INTO t 1 VALUES (100, 20, 1); UPDATE t 1 SET c 3 = 2 WHERE c 1 = 100 AND c 2 = 20; 3 © 2006 Julian Dyke Redo Header Change Header KTU KDO c 3 = 1 Supplementary Header c 1 = 100 c 2 = 20 Change Header KTU KDO c 3 = 2 juliandyke. com

Supplemental Logging Minimal Supplemental Logging u u u Required for Log. Miner Includes additional information in redo stream Minimal supplemental logging can be enabled using: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; u Minimal supplemental logging can be enabled using: ALTER DATABASE DROP SUPPLEMENTAL LOG DATA; 4 © 2006 Julian Dyke juliandyke. com

Supplemental Logging Minimal Supplemental Logging u To check whether minimal supplemental logging is enabled: SELECT supplemental_log_data_min FROM v$database; u u 5 Returns YES or IMPLICIT if enabled Allows Log. Miner to support u chained rows u clustered tables u index organized tables (IOTs) © 2006 Julian Dyke juliandyke. com

Supplemental Logging Database Supplemental Logging 6 u Can be enabled u For all columns u For primary key columns u For unique columns u For foreign key columns u All columns are included with the exception of: u LONG u LOB u LONG RAW u Abstract Data Types u Collections © 2006 Julian Dyke juliandyke. com

Supplemental Logging Database Supplemental Logging u To enable supplemental logging at database level: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; u 7 Database can be mounted and open © 2006 Julian Dyke juliandyke. com

Supplemental Logging Database Supplemental Logging u To disable supplemental logging at database level: ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS; 8 © 2006 Julian Dyke juliandyke. com

Supplemental Logging Database Supplemental Logging u To monitor database level supplemental logging: SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk, supplemental_log_data_all FROM v$database; 9 © 2006 Julian Dyke juliandyke. com

Supplemental Logging Log Groups u u 10 Implemented as constraints If no name specified for log group then system constraint name will be allocated e. g SYS_C 005223 Can be u Unconditional u Before image of supplemental columns always included in redo u Conditional u Before image of supplemental columns only included in redo if supplemental columns have changed In Oracle 10. 2, minimal supplemental logging must be enabled at database level before supplemental logging can be enabled at table level © 2006 Julian Dyke juliandyke. com

Supplemental Logging Unconditional Supplemental Log Groups u To specify an unconditional supplemental log group for primary key column(s): ALTER TABLE t 1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; u To specify an unconditional supplemental log group that includes all table columns: ALTER TABLE t 1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; u To specify an unconditional supplemental log group that includes selected columns: ALTER TABLE t 1 ADD SUPPLEMENTAL LOG GROUP t 1_g 1 (c 1, c 3) ALWAYS; 11 © 2006 Julian Dyke juliandyke. com

Supplemental Logging Conditional Supplemental Log Groups u To specify a conditional supplemental log group for unique key column(s) and/or bitmap index column(s): ALTER TABLE t 1 ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; u To specify a conditional supplemental log group that includes all foreign key columns: ALTER TABLE t 1 ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; u To specify a conditional supplemental log group that includes selected columns: ALTER TABLE t 1 ADD SUPPLEMENTAL LOG GROUP t 1_g 1 (c 1, c 3); 12 © 2006 Julian Dyke juliandyke. com

Supplemental Logging Dropping Supplemental Log Groups u To drop a supplemental log group: ALTER TABLE t 1 DROP SUPPLEMENTAL LOG GROUP t 1_g 1; u To drop supplemental logging of data use: ALTER TABLE t 1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ALTER TABLE t 1 DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER TABLE t 1 DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER TABLE t 1 DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; 13 © 2006 Julian Dyke juliandyke. com

Supplemental Logging DBA_LOG_GROUPS view u Describes currently configured supplemental log groups: u 14 OWNER VARCHAR 2(30) LOG_GROUP_NAME VARCHAR 2(30) TABLE_NAME VARCHAR 2(30) LOG_GROUP_TYPE VARCHAR 2(19) ALWAYS VARCHAR 2(11) GENERATED VARCHAR 2(14) LOG_GROUP_TYPE can be: u PRIMARY KEY LOGGING u UNIQUE KEY LOGGING u FOREIGN KEY LOGGING u ALL COLUMN LOGGING u USER LOG GROUP © 2006 Julian Dyke u u ALWAYS can be: u ALWAYS u CONDITIONAL GENERATED can be u GENERATED NAME u USER NAME juliandyke. com

Supplemental Logging DBA_LOG_GROUP_COLUMNS view u Describes columns in currently configured supplemental log groups: OWNER VARCHAR 2(30) LOG_GROUP_NAME VARCHAR 2(30) TABLE_NAME VARCHAR 2(30) COLUMN_NAME VARCHAR 2(4000) POSITION NUMBER LOGGING_PROPERTY VARCHAR 2(6) u u 15 LOGGING_PROPERTY can be: u LOG u NO_LOG Only applies to log groups (not log data) configurations © 2006 Julian Dyke juliandyke. com