Supplemental Logging 5w592h

Supplemental Logging Julian Dyke Independent Consultant Web Version 1

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging

2

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Example ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER, c3 NUMBER ); ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1,c2);

Redo Header Change Header KTU KDO

c3 = 1 Supplementary Header c1 = 100 c2 = 20 Change Header

INSERT INTO t1 VALUES (100,20,1); UPDATE t1 SET c3 = 2 WHERE c1 = 100 AND c2 = 20;

3

© 2006 Julian Dyke

KTU KDO c3 = 2

juliandyke.com

Supplemental Logging Minimal Supplemental Logging   

Required for LogMiner Includes additional information in redo stream Minimal supplemental logging can be enabled using: ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;



Minimal supplemental logging can be enabled using: ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

4

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Minimal Supplemental Logging 

To check whether minimal supplemental logging is enabled: SELECT supplemental_log_data_min FROM v$database;

 

5

Returns YES or IMPLICIT if enabled Allows LogMiner to  chained rows  clustered tables  index organized tables (IOTs)

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Database Supplemental Logging

6



Can be enabled  For all columns  For primary key columns  For unique columns  For foreign key columns



All columns  All columns are included with the exception of:  LONG  LOB  LONG RAW  Abstract Data Types  Collections

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Database Supplemental Logging 

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; 

7

Database can be mounted and open

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Database Supplemental Logging 

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 

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   



10

Implemented as constraints If no name specified for log group then system constraint name will be allocated e.g SYS_C005223 Can be  Unconditional  Before image of supplemental columns always included in redo  Conditional  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 

To specify an unconditional supplemental log group for primary key column(s): ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;



To specify an unconditional supplemental log group that includes all table columns: ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;



To specify an unconditional supplemental log group that includes selected columns: ALTER TABLE t1 ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3) ALWAYS;

11

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Conditional Supplemental Log Groups 

To specify a conditional supplemental log group for unique key column(s) and/or bitmap index column(s): ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;



To specify a conditional supplemental log group that includes all foreign key columns: ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;



To specify a conditional supplemental log group that includes selected columns: ALTER TABLE t1 ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3);

12

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging Dropping Supplemental Log Groups 

To drop a supplemental log group: ALTER TABLE t1 DROP SUPPLEMENTAL LOG GROUP t1_g1;



To drop supplemental logging of data use: ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

13

© 2006 Julian Dyke

juliandyke.com

Supplemental Logging DBA_LOG_GROUPS view 

Describes currently configured supplemental log groups:



14

OWNER

VARCHAR2(30)

LOG_GROUP_NAME

VARCHAR2(30)

TABLE_NAME

VARCHAR2(30)

LOG_GROUP_TYPE

VARCHAR2(19)

ALWAYS

VARCHAR2(11)

GENERATED

VARCHAR2(14)

LOG_GROUP_TYPE can be:  PRIMARY KEY LOGGING  UNIQUE KEY LOGGING  FOREIGN KEY LOGGING  ALL COLUMN LOGGING  LOG GROUP

© 2006 Julian Dyke





ALWAYS can be:  ALWAYS  CONDITIONAL GENERATED can be  GENERATED NAME  NAME

juliandyke.com

Supplemental Logging DBA_LOG_GROUP_COLUMNS view 

Describes columns in currently configured supplemental log groups: OWNER

VARCHAR2(30)

LOG_GROUP_NAME

VARCHAR2(30)

TABLE_NAME

VARCHAR2(30)

COLUMN_NAME

VARCHAR2(4000)

POSITION

NUMBER

LOGGING_PROPERTY

VARCHAR2(6)





15

LOGGING_PROPERTY can be:  LOG  NO_LOG

Only applies to log groups (not log data) configurations

© 2006 Julian Dyke

juliandyke.com

Thank you for your interest

[email protected] 16

© 2006 Julian Dyke

juliandyke.com