Transcript Supplemental Logging

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 support
 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
 USER LOG GROUP
© 2006 Julian Dyke


ALWAYS can be:
 ALWAYS
 CONDITIONAL
GENERATED can be
 GENERATED NAME
 USER 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
info@juliandyke.com
16
© 2006 Julian Dyke
juliandyke.com