Oracle supplemental logging

Soru: Supplemantal logging nedir? Ve nasıl çalışır

Cevap: Database level suplemental logging, Capture edilecek database üzerinde enable edilmesi gereken bir konfigürasyondur. Bu sayede redo log izlenir ve yapılan  x bir değişiklik capture edilmiş olur.

İki çeşit olarak ayrılabilir. Minimal supplemental logging ve identification key logging. Oracle en azından minimal supplemental loging’i logMiner için enable edilmesini öneriyor.

Minimal Supplemental Logging minimal oranda bilgi capture edilmiş olur

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.

Database level identification key level ise daha kapsamlıdır.  İçeriği ve Oracle örneklerine bakarsak 

ALL system-generated unconditional supplemental log group

This option specifies that when a row is updated, all columns of that row  are placed in the redo log file.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

• PRIMARY KEY system-generated unconditional supplemental log group

This option causes the database to place all columns of a row's primary key in the redo log file whenever a row containing a primary key is updated (even if no value in the primary key has changed).

If a table does not have a primary key, but has one or more non-null unique index key constraints or index keys, then one of the unique index keys is chosen for logging as a means of uniquely identifying the row being updated.

If the table has neither a primary key nor a non-null unique index key, then all columns except LONG and LOB are supplementally logged; this is equivalent to specifying ALL supplemental logging for that row. Therefore, Oracle recommends that when you use database-level primary key supplemental logging, all or most tables be defined to have primary or unique index keys.

To enable primary key logging at the database level, execute the following statement:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

•  UNIQUE system-generated conditional supplemental log group

This option causes the database to place all columns of a row's composite unique key or bitmap index in the redo log file if any column belonging to the composite unique key or bitmap index is modified. The unique key can be due to either a unique constraint or a unique index.

To enable unique index key and bitmap index logging at the database level, execute the following statement:

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

• FOREIGN KEY system-generated conditional supplemental log group

This option causes the database to place all columns of a row's foreign key in the redo log file if any column belonging to the foreign key is modified.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

İyi çalışmalar, 

Usta