supplemental logging總結

 1 什麼是supplemental logging

Redo log files一般被用作例項恢復和介質恢復,恢復需要的變動被自動記錄(以更新為例子 只是記錄更改的rowid 和更改前後的列值),而supplemental logging可以記錄額外的需要的列值如:主鍵等來滿足其他例項的邏輯恢復

 

  開啟supplemental logging和關閉supplemental logging對logminer的影響

    參考:Effect of Supplemental Logging on LogMiner with Example (Doc ID 750198.1)

 

2 supplemental logging分類

2.1Database-Level Supplemental Logging

Minimal Supplemental Logging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database-Level Identification Key Logging

• ALL system-generated uncondititional supplemental log group

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

 

• PRIMARY KEY system-generated uncondititional supplemental log group

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

 

• UNIQUE system-generated conditional supplemental log group

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

 

• FOREIGN KEY system-generated conditional supplemental log group

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

2.2 Table-Level Supplemental Logging

·          User-defined unconditional log groups

ALTER TABLE HR.EMPLOYEES

   ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME,

   DEPARTMENT_ID) ALWAYS;

 

 

• User-defined conditional supplemental log groups

ALTER TABLE HR.EMPLOYEES

   ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME,

   DEPARTMENT_ID);

 

3 supplemental logging作用

 

  全庫級別的supplemental logging和表級別的supplemental logging 各種組合的效果總結如下：

（1）、當全庫supplemental logging以minimal模式開啟時（此時IMU會被禁掉）：

   (a) 如果不對目標表單獨開啟supplemental logging，則Oracle在redo中並不會額外的記錄主鍵列和其他列的值；

   (b) 如果對目標表單獨開啟主鍵模式的supplemental logging,則又細分為如下兩種情況：

       (b1)如果目標表有主鍵，則Oracle會在redo中額外的記錄主鍵列的值；

       (b2)如果目標表沒有主鍵，則Oracle會在redo中額外的記錄所有列的值；

（2）、當全庫supplemental logging以主鍵模式開啟（此時IMU會被禁掉）且目標表並沒有單獨開啟任何模式的supplemental logging時：

   (a) 如果目標表有主鍵，則Oracle會在redo中額外的記錄主鍵列的值；

   (b) 如果目標表沒有主鍵，則Oracle會在redo中額外的記錄所有列的值；

（3）、當把全庫supplemental log禁掉後（此時IMU會被開啟）,不管目標表是否單獨開啟主鍵模式的supplemental logging，Oracle在redo中都並不會額外的記錄主鍵列和其他列的值。

from:

 

  

4 10g 以後supplemental logging預設是關閉的 需要基於logminer日誌挖掘的如: logical standby  stream logical stanby OGG 都必須開啟supplemental logging

 

 

 

supplemental logging 官方解釋

Supplemental Logging

Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging.

By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging prior to generating log files which will be analyzed by LogMiner.

The following are examples of situations in which additional columns may be needed:

·         An application that applies reconstructed SQL statements to a different database must identify the update statement by a set of columns that uniquely identify the row (for example, a primary key), not by the ROWID shown in the reconstructed SQL returned by the V$LOGMNR_CONTENTS view, because theROWID of one database will be different and therefore meaningless in another database.

·         An application may require that the before-image of the whole row be logged, not just the modified columns, so that tracking of row changes is more efficient.

A supplemental log group is the set of additional columns to be logged when supplemental logging is enabled. There are two types of supplemental log groups that determine when columns in the log group are logged:

·         Unconditional supplemental log groups: The before-images of specified columns are logged any time a row is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group.

·         Conditional supplemental log groups: The before-images of all specified columns are logged only if at least one of the columns in the log group is updated.

Supplemental log groups can be system-generated or user-defined.

In addition to the two types of supplemental logging, there are two levels of supplemental logging, as described in the following sections:

·         Database-Level Supplemental Logging

·         Table-Level Supplemental Logging

 

Database-Level Supplemental Logging

There are two types of database-level supplemental logging: minimal supplemental logging and identification key logging, as described in the following sections. Minimal supplemental logging does not impose significant overhead on the database generating the redo log files. However, enabling database-wide identification key logging can impose overhead on the database generating the redo log files. Oracle recommends that you at least enable minimal supplemental logging for LogMiner.

Minimal Supplemental Logging

Minimal supplemental logging logs the minimal amount of information needed for LogMiner to identify, group, and merge the redo operations associated with DML changes. It ensures that LogMiner (and any product building on LogMiner technology) has sufficient information to support chained rows and various storage arrangements, such as cluster tables and index-organized tables. To enable minimal supplemental logging, execute the following SQL statement:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

 

Database-Level Identification Key Logging

Identification key logging is necessary when redo log files will not be mined at the source database instance, for example, when the redo log files will be mined at a logical standby database.

Using database identification key logging, you can enable database-wide before-image logging for all updates by specifying one or more of the following options to the SQL ALTER DATABASE ADD SUPPLEMENTAL LOG statement:

• ALL system-generated uncondititional supplemental log group

This option specifies that when a row is updated, all columns of that row (except for LOBs, LONGS, and ADTs) are placed in the redo log file.

To enable all column logging at the database level, execute the following statement:

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

 

• PRIMARY KEY system-generated uncondititional 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.

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

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

 

Note: Regardless of whether or not identification key logging is enabled, the SQL statements returned by LogMiner always contain the ROWID clause. You can filter out the ROWID clause by using the NO_ROWID_IN_STMT option to theDBMS_LOGMNR.START_LOGMNR procedure call. See Formatting Reconstructed SQL Statements for Reexecution for details.

 

Keep the following in mind when you use identification key logging:

• If the database is open when you enable identification key logging, all DML cursors in the cursor cache are invalidated. This can affect performance until the cursor cache is repopulated.
• When you enable identification key logging at the database level, minimal supplemental logging is enabled implicitly.
• Supplemental logging statements are cumulative. If you issue the following SQL statements, both primary key and unique key supplemental logging is enabled:

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

·         ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

Disabling Database-Level Supplemental Logging

You disable database-level supplemental logging using the SQL ALTER DATABASE statement with the DROP SUPPLEMENTAL LOGGING clause. You can drop supplemental logging attributes incrementally. For example, suppose you issued the following SQL statements, in the following order:

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

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

 

The statements would have the following effects:

• After the first statement, primary key supplemental logging is enabled.
• After the second statement, primary key and unique key supplemental logging are enabled.
• After the third statement, only unique key supplemental logging is enabled.
• After the fourth statement, all supplemental logging is not disabled. The following error is returned: ORA-32589: unable to drop minimal supplemental logging.

To disable all database supplemental logging, you must first disable any identification key logging that has been enabled, then disable minimal supplemental logging. The following example shows the correct order:

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

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

 

Dropping minimal supplemental log data is allowed only if no other variant of database-level supplemental logging is enabled.

Table-Level Supplemental Logging

Table-level supplemental logging specifies, at the table level, which columns are to be supplementally logged. You can use identification key logging or user-defined conditional and unconditional supplemental log groups to log supplemental information, as described in the following sections.

Table-Level Identification Key Logging

Identification key logging at the table level offers the same options as those provided at the database level: all, primary key, foreign key, and unique key. However, when you specify identification key logging at the table level, only the specified table is affected. For example, if you enter the following SQL statement (specifying database-level supplemental logging), then whenever a column in any database table is changed, the entire row containing that column (except columns for LOBs, LONGs, and ADTs) will be placed in the redo log file:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 

However, if you enter the following SQL statement (specifying table-level supplemental logging) instead, then only when a column in the employees table is changed will the entire row (except for LOB, LONGs, and ADTs) of the table be placed in the redo log file. If a column changes in the departments table, only the changed column will be placed in the redo log file.

ALTER TABLE HR.EMPLOYEES ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

 

Keep the following in mind when you use table-level identification key logging:

• If the database is open when you enable identification key logging on a table, all DML cursors for that table in the cursor cache are invalidated. This can affect performance until the cursor cache is repopulated.
• Supplemental logging statements are cumulative. If you issue the following SQL statements, both primary key and unique index key table-level supplemental logging is enabled:

·         ALTER TABLE HR.EMPLOYEES

·           ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

·         ALTER TABLE HR.EMPLOYEES

·           ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

·          

See  for a description of each of the identification key logging options.

Table-Level User-Defined Supplemental Log Groups

In addition to table-level identification key logging, Oracle supports user-defined supplemental log groups. With user-defined supplemental log groups, you can specify which columns are supplementally logged. You can specify conditional or unconditional log groups, as follows:

• User-defined unconditional log groups

To enable supplemental logging that uses user-defined unconditional log groups, use the ALWAYS clause as shown in the following example:

ALTER TABLE HR.EMPLOYEES

   ADD SUPPLEMENTAL LOG GROUP emp_parttime (EMPLOYEE_ID, LAST_NAME,

   DEPARTMENT_ID) ALWAYS;

 

This creates a log group named emp_parttime on the hr.employees table that consists of the columns employee_id, last_name, and department_id. These columns will be logged every time an UPDATE statement is executed on the hr.employees table, regardless of whether or not the update affected these columns. (If you want to have the entire row image logged any time an update was made, use table-level ALL identification key logging, as described previously).

 

Note: LOB, LONG, and ADT columns cannot be supplementally logged.

 

• User-defined conditional supplemental log groups

To enable supplemental logging that uses user-defined conditional log groups, omit the ALWAYS clause from the SQL ALTER TABLE statement, as shown in the following example:

ALTER TABLE HR.EMPLOYEES

   ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME,

   DEPARTMENT_ID);

 

This creates a log group named emp_fulltime on table hr.employees. Just like the previous example, it consists of the columns employee_id, last_name, and department_id. But because the ALWAYS clause was omitted, before-images of the columns will be logged only if at least one of the columns is updated.

For both unconditional and conditional user-defined supplemental log groups, you can explicitly specify that a column in the log group be excluded from supplemental logging by specifying the NO LOG option. When you specify a log group and use the NO LOG option, you must specify at least one column in the log group without the NO LOG option, as shown in the following example:

ALTER TABLE HR.EMPLOYEES

   ADD SUPPLEMENTAL LOG GROUP emp_parttime(

   DEPARTMENT_ID NO LOG, EMPLOYEE_ID);

 

This enables you to associate this column with other columns in the named supplemental log group such that any modification to the NO LOG column causes the other columns in the supplemental log group to be placed in the redo log file. This might be useful, for example, if you want to log certain columns in a group if a LONG column changes. You cannot supplementally log the LONG column itself; however, you can use changes to that column to trigger supplemental logging of other columns in the same row.

Usage Notes for User-Defined Supplemental Log Groups

Keep the following in mind when you specify user-defined supplemental log groups:

• A column can belong to more than one supplemental log group. However, the before-image of the columns gets logged only once.
• If you specify the same columns to be logged both conditionally and unconditionally, the columns are logged unconditionally.