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