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

As long as the supplementary log is for the UPDATE command, it is the supplementary information for the change vector block in the redo log record, which increases the recording amount of the change vector record. Log miner (LogMiner), flashback transaction query, flashback transaction and so on all need supplementary log support. In other words, the supplementary log is mainly for the UPDATE command, and the purpose of the supplement is to highly restore the UPDATE command, avoid line migration and line movement caused by the update command, and let LogMiner analyze the redo log to identify that the update command is not completed by insert and delete.

Database-level supplementary logs are divided into: minimum supplementary logs, identification of key fields supplementary logs

(1)。 Minimum supplementary log: is the most basic kind of database-level supplementary log, and LogMiner relies on the minimum supplementary log work service (that is, identify row mobile row migration).

Enable the minimum supplementary log command:

Alter database add supplemental log data

Turn off the minimum supplementary log command:

Alter database drop supplemental log data

(2)。 Identify the key field supplementary log: it can be divided into four types: primary key, foreign key, unique index and all field supplementary log.

Primary key supplementary log: add the old value of the primary key field of the modified row to the redo record of the update command, regardless of whether it has been modified or not. If the table does not have a primary key, it is replaced by the smallest unique index field, and if there is no unique index, all fields in that row are recorded.

Alter database add supplemental log data (Primary key) columns

Unique index supplementary log: the unique index mainly serves the composite index (unique). The value before the field is modified will be recorded only if the field with a unique index is update.

Alter database add supplemental log data (unique) columns

Foreign key supplementary log: like the unique index supplementary log, the old value before modification will be recorded only when the foreign key field is update, that is, it also serves the compound foreign key.

Alter database add supplemental log data (foreign key) columns

All fields supplementary log: as the name implies, the values of all fields are recorded regardless of whether they have been modified or not. This can lead to rapid disk growth and busy LGWR processes. Use is not recommended.

Alter database add supplemental log data (all) columns

Table-level supplementary log:

It is divided into five types: primary key, unique index, foreign key, all fields and user-defined fields.

The first four are the same as the database-level identification key fields to supplement the log effect. Simply enable supplementary logging on a specific table

1.alter table tb_name add supplemental log data (primary key) columns

2.alter table tb_name add supplemental log data (unique) columns

3.alter table tb_name add supplemental log data (foreign key) columns

4.alter table tb_name add supplemental log data (all) columns

5.alter table tb_name add supplemental log group group_name (col01,col02,col05,col09) | always

1. Check whether the database opens the supplementary log.

SQL > SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM Vogue database;-- Supplementary log is enabled

SUPPLEME

-

YES

SQL > SELECT supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui

Supplemental_log_data_fk,supplemental_log_data_all FROM v$database

SUPPLEME SUP SUP SUP SUP-only the supplementary log of the primary key is enabled

YES NO NO NO NO

SQL >

two。 Check to see if the table is added to the supplementary log

SQL > select * from dba_log_groups where table_name='abc';-- check to see if the table has supplementary logs

No rows selected

SQL > select * from dba_log_group_columns where table_name='abc';-View the fields of the supplementary log

No rows selected

SQL >

3. Solution (1)

GGSCI (iZ23jtzbnliZ) 35 > dblogin userid ggs,password system-- username and password in the EXTRACT process

Successfully logged into database.

GGSCI (iZ23jtzbnliZ) 36 > add TRANDATA tong.abc-add supplementary logs (tong is the database, abc is the table name)

Logging of supplemental redo data enabled for table UPCENTER.NEWS_FLASH_TAB_CLAS_RELA.

GGSCI (iZ23jtzbnliZ) 37 >

3. Solution (2)

SQL > alter table abc add supplemental log data (all,primary key,unique,foreign key) columns

SQL >

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.