10g Supplemental Log 设置与取消

最新推荐文章于 2023-08-23 08:00:00 发布

转载最新推荐文章于 2023-08-23 08:00:00 发布 · 357 阅读

注意正确顺序是先drop 表级的，再drop database 级的。如果需要加入，改drop 为 add 就可以了。

mxb2bcoredbsty$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 27 20:46:27 2010

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> alter database drop supplemental log data;
alter database drop supplemental log data
*
ERROR at line 1:
ORA-32589: unable to drop minimal supplemental logging

SQL> ALTER DATABASE drop SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE, FOREIGN KEY) COLUMNS;

Database altered.

SQL> alter database drop supplemental log data;

Database altered.

SQL>

============================================================

具体参考：

Supplemental Logging
=====================
P425

v$database 视图可查询的相关信息：

SUPPLEMENTAL_LOG_DATA_MIN                          VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK                           VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI                           VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_FK                           VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL                          VARCHAR2(3)

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.

缺省情况下，数据库没有打开对supplemental logging的支持。

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

(1) 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 the ROWID of one database will be different and
therefore meaningless in another database.

(2) An application may require that the before-image of 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:

(1) 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.

(2) Conditional supplemental log groups: The befre-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:
(1) minimal supplemental logging (使用LogMiner的至少要求)
(2) identification key logging

However, enabling database-wide identification key logging can impose overhead on the database
generating the redo log files.

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;

添加与删除的测试：

SQL> conn /as sysdba
Connected.
SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

SQL> alter database drop supplemental log data;

Database altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL>

警告日志的变化：

Tue Jul 21 17:43:00 2009
alter database add supplemental log data
Tue Jul 21 17:43:00 2009
SUPLOG: Updated supplemental logging attributes at scn = 5005756
SUPLOG: minimal = ON, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
Completed: alter database add supplemental log data
Tue Jul 21 17:43:18 2009
alter database drop supplemental log data
Tue Jul 21 17:43:18 2009
SUPLOG: Updated supplemental logging attributes at scn = 5005768
SUPLOG: minimal = OFF, primary key = OFF
SUPLOG: unique = OFF, foreign key = OFF, all column = OFF
Completed: alter database drop 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.

(1) ALL system-generated unconditional 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;

(2) 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 containning 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 constrains 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 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;

(3) 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.

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

(4) FOREIGN KEY system-generated conditional supplemental log group

This option causes the database to place all columns of 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;

当使用键值表示记录时需要谨记以下几条：
(1) 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.

(2) When you enable identification key logging at the database level, minimal supplemental logging
is enabled implicitly.

(3) Supplemenatl logging statements are cumlative. If you issue the following SQL statements,
both primary key and unique key supplemenatl 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
==============================================

开启与关闭的顺序
===============

错误的操作过程：
SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (unique) columns;

SQL> alter database drop supplemental log data (primary key) columns;
SQL> alter database drop supplemental log data;
alter database drop supplemental log data
*
ERROR at line 1:
ORA-32589: unable to drop minimal supplemental logging

SQL>

正确的操作过程：
To disable all database supplemental logging, you must first disable any identification key logging
that has been enabled, then disable minimal supplemenatl logging. The following example shows the
correct order:

SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (unique) columns;

SQL> alter database drop supplemental log data (primary key) columns;
SQL> alter database drop supplemental log data (unique) columns;
SQL> alter database drop supplemental log data;

详细过程：

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data (primary key) columns;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
IMPLICIT

SQL> alter database add supplemental log data (unique) columns;

Database altered.

SQL> alter database drop supplemental log data (primary key) columns;

Database altered.

SQL> alter database drop supplemental log data (unique) columns;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

SQL> alter database drop supplemental log data;

Database altered.

SQL> alter database drop supplemental log data;

Database altered.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

注意：

(1) 每次添加与删除都会在警告日志中留下信息已被参考

Tue Jul 21 15:36:15 2009
alter database add supplemental log data (unique) columns
Tue Jul 21 15:36:15 2009
SUPLOG: Updated supplemental logging attributes at scn = 5000969
SUPLOG: minimal = ON, primary key = ON
SUPLOG: unique = ON, foreign key = OFF, all column = OFF
Completed: alter database add supplemental log data (unique) columns

(2) 可以多次执行 alter database drop supplemental log data;

Table-Level Supplemental Logging
================================

Table-level supplemental logging specifies, at the table level, which columns are to be
supplementally logged.

(1) 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 spcified table is affected.

only when a column in the emp table is changed will the entire row (except for LOB, LONGs,
and ADTs) of the table be placed in the redo log file.

SQL> alter table yzhq.emp add supplemental log data (all) columns;

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

1) If the database is open when you enable identfication 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.

2) 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 YZHQ.EMP ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER TABLE YZHQ.EMP ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

(2) Table-Level User-Defined Suplemental Log Groups

1) 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.

   2) User-defined conditional supplemental log groups

    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.

   3) NO LOG

     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.