Shulou(Shulou.com)06/01 Report--

Additional logs (supplemental log) instruct the database to add additional information to the log stream to support log-based tools such as logical standby, streams, GoldenGate, LogMiner. Can be set on databases and tables.

1. Database-level settings are divided into two categories:

1.1 minimum additional log (minimal supplemental logging):

The DATA option enables minimum additional logs. Enabling minimum logging ensures that LogMiner (or any other LogMiner-based product) can support row links, clustered tables, index organization tables, and so on.

The syntax is as follows:

Alter database {add | drop} supplemental log data

1.2Identification key log (identification key logging):

The DATA (all,primary key,unique,foreign key) columns option enables minimum logging and column data logging. In cases where the source database log synchronizes other databases for the source of change, such as a logical standby database, the affected rows must be identified by column data (not rowid), and this additional log must be enabled.

The syntax is as follows:

Alter database {add | drop} supplemental log {data (all,primary key,unique,foreign key) columns}

1.3 by default, Oracle does not enable any of the above additional logs. When using ALL,PRIMARY,UNIQUE or FOREIGN to attach logs, the minimum completion log is enabled by default (that is, the check result is IMPLICIT). After deleting all additional logs that cause IMPLICIT to minimize additional logs, minimizing additional logs becomes NO.

1.4 query current settings

SELECT supplemental_log_data_min min

Supplemental_log_data_pk competition

Supplemental_log_data_ui ui

Supplemental_log_data_fk fk

Supplemental_log_data_all allc

FROM v$database

2. Additional log settings at table level, which can be divided into two categories

2.1, you can set named log groups with the following statement:

Syntax:

Alter table table_name

Add supplemental log group group_a (column_a [no log], column_b, …) [always]

The NO LOG option specifies which columns are excluded from the log. There is at least one fixed-length column without "NO LOG" in a named log group. For example, by using the no log option on the LONG column, you can record the contents of other columns when you change the LONG column (the LONG column itself cannot exist in the log).

ALWAYS option, when updated, all columns in the log group are recorded in the log. This is called an "unconditional" log group, sometimes called "always log group". If you do not specify this option, all columns appear in the log only if any of the columns in the log group are modified. This is the so-called conditional log group.

Note: the same column can exist in multiple log groups, but only once in the log; when the same column exists in the unconditional and conditional log groups, the column will be recorded "unconditionally".

2.2, you can set all columns or primary / foreign key / unique key combination log groups with the following statement:

Syntax:

Alter table table_name

Add supplemental log data (all,primary key,unique,foreign key) columns

Oracle generates unconditional or conditional log groups. For unconditional log groups, all columns in the log group are recorded; for conditional log groups, all columns in the log group are recorded only if the columns in the log group change.

If you specify the "ALL" column, the log will contain all columns of maximum size and fixed length. This log is an unconditional log group created by the system.

If you specify the "PRIMARY KEY" column, all the columns that make up the primary key are recorded in the log whenever there is an update. This log is an unconditional log group created by the system. Oracle uses the following order to determine which columns to attach records:

* columns that make up the primary key (the primary key is valid, or rely and not DISABLED or INITIALLY DEFERRED status)

* the smallest unique index with at least one non-empty column

* record all scalar columns

If you specify the UNIQUE column, if any column that makes up the unique key or bitmap index is modified, the other columns that make up the unique key or bitmap index are recorded in the log. This kind of log is a conditional log group created by the system.

If you specify the "FOREIGN KEY" column, if any of the columns that make up the foreign key are modified, the other columns that make up the foreign key are recorded in the log. This kind of log is a conditional log group created by the system.

2.3 Table-level testing

Drop table test

Create table test (x int,y int)

-add additional logs

Alter table test

Add supplemental log data (all,primary key,unique,foreign key) columns

Select * from dba_log_groups

Select * from dba_log_group_columns

-Delete additional logs

Alter table test

Drop supplemental log data (all,primary key,unique,foreign key) columns

Select * from dba_log_groups

Select * from dba_log_group_columns

-add naming additional logs

Alter table test

Add supplemental log group group_a (XBI y)

Select * from dba_log_groups

Select * from dba_log_group_columns

Alter table test

Drop supplemental log group group_a

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.