1 / 16

Supplemental Logging

Supplemental Logging. Julian Dyke Independent Consultant. Web Version. juliandyke.com. Supplemental Logging. Supplemental Logging Example. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;. Redo Header. Change Header.

deion
Download Presentation

Supplemental Logging

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SupplementalLogging Julian Dyke Independent Consultant Web Version juliandyke.com

  2. SupplementalLogging

  3. Supplemental LoggingExample ALTER DATABASEADD SUPPLEMENTAL LOG DATA(PRIMARY KEY) COLUMNS; Redo Header Change Header CREATE TABLE t1( c1 NUMBER, c2 NUMBER, c3 NUMBER); KTU KDO c3 = 1 Supplementary Header c1 = 100 ALTER TABLE t1ADD CONSTRAINT t1_pk PRIMARY KEY (c1,c2); c2 = 20 Change Header INSERT INTO t1 VALUES (100,20,1); KTU UPDATE t1 SET c3 = 2WHERE c1 = 100AND c2 = 20; KDO c3 = 2

  4. Supplemental LoggingMinimal Supplemental Logging • Required for LogMiner • Includes additional information in redo stream • Minimal supplemental logging can be enabled using: ALTER DATABASEADD SUPPLEMENTAL LOG DATA; • Minimal supplemental logging can be enabled using: ALTER DATABASEDROP SUPPLEMENTAL LOG DATA;

  5. Supplemental LoggingMinimal Supplemental Logging • To check whether minimal supplemental logging is enabled: SELECT supplemental_log_data_minFROM v$database; • Returns YES or IMPLICIT if enabled • Allows LogMiner to support • chained rows • clustered tables • index organized tables (IOTs)

  6. Supplemental LoggingDatabase Supplemental Logging • 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

  7. Supplemental LoggingDatabase Supplemental Logging • To enable supplemental logging at database level: ALTER DATABASEADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASEADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASEADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ALTER DATABASEADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; • Database can be mounted and open

  8. Supplemental LoggingDatabase Supplemental Logging • To disable supplemental logging at database level: ALTER DATABASEDROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER DATABASEDROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER DATABASEDROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; ALTER DATABASEDROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

  9. Supplemental LoggingDatabase 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_allFROM v$database;

  10. Supplemental LoggingLog Groups • 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

  11. Supplemental LoggingUnconditional Supplemental Log Groups • To specify an unconditional supplemental log group for primary key column(s): ALTER TABLE t1ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; • To specify an unconditional supplemental log group that includes all table columns: ALTER TABLE t1ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; • To specify an unconditional supplemental log group that includes selected columns: ALTER TABLE t1ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3) ALWAYS;

  12. Supplemental LoggingConditional Supplemental Log Groups • To specify a conditional supplemental log group for unique key column(s) and/or bitmap index column(s): ALTER TABLE t1ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; • To specify a conditional supplemental log group that includes all foreign key columns: ALTER TABLE t1ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS; • To specify a conditional supplemental log group that includes selected columns: ALTER TABLE t1ADD SUPPLEMENTAL LOG GROUP t1_g1 (c1,c3);

  13. Supplemental LoggingDropping Supplemental Log Groups • To drop a supplemental log group: ALTER TABLE t1DROP SUPPLEMENTAL LOG GROUP t1_g1; • To drop supplemental logging of data use: ALTER TABLE t1DROP SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ALTER TABLE t1DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ALTER TABLE t1DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS; ALTER TABLE t1DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

  14. Supplemental LoggingDBA_LOG_GROUPS view • Describes currently configured supplemental log groups: • LOG_GROUP_TYPE can be: • PRIMARY KEY LOGGING • UNIQUE KEY LOGGING • FOREIGN KEY LOGGING • ALL COLUMN LOGGING • USER LOG GROUP • ALWAYS can be: • ALWAYS • CONDITIONAL • GENERATED can be • GENERATED NAME • USER NAME

  15. Supplemental LoggingDBA_LOG_GROUP_COLUMNS view • Describes columns in currently configured supplemental log groups: • LOGGING_PROPERTY can be: • LOG • NO_LOG • Only applies to log groups (not log data) configurations

  16. Thank you for your interest info@juliandyke.com

More Related