Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the Oracle additional log is, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Oracle additional Log

The classification is divided into two levels: database level and table level.

Database-level additional logs

-View v$database

? SUPPLEMENTAL_LOG_DATA_MIN: minimum additional log, switch

Minimum supplementary log: it is the most basic 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

? SUPPLEMENTAL_LOG_DATA_PK: record all changes record primary key

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

Alterdatabaseaddsupplemental log data (Primarykey) columns

? SUPPLEMENTAL_LOG_DATA_UI: record all changes record unique index

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

Alter database add supplemental log data (unique) columns

? Supplemental_log_data_fk:

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

? SUPPLEMENTAL_LOG_DATA_ALL: record all changes, record all 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

-it is recommended that only the minimum additional log be opened, and the table-level additional log of the required replicated table be opened

? ALTER DATABASE add | drop SUPPLEMENTAL LOG DATA

? The aim is to generate a minimum log increment to avoid a greater impact on the production host

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

Table-level additional log

-add through the add trandata schema.table command on the OGG command line

-add only the primary key if there is a primary key, add a unique index without the primary key, and add all columns if there is neither.

-equivalent to using Alter tableadd supplemental log group in the database

(column,..) Always; (dba_log_group_columns can be queried)

Experimental environment

1. Both the source and destination create tables:

Source table: create table tb10 (col1 int primary key, col2 int, col3 int)

Target: create table tb10 (col1 int primary key, col2 int, col3 int)

two。 Configure the local extraction process

GGSCI (pc6 as ogg@hyyk) 27 > add extract test tranlog begin now

GGSCI (pc6 as ogg@hyyk) 36 > add exttrail. / dirdat/oo,extract test

GGSCI (pc6 as ogg@hyyk) 33 > edit params test

Extract test

Setenv (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK")

Setenv (ORACLE_SID= "hyyk")

Userid ogg,password oracle

Gettruncates

Exttrail. / dirdat/oo

Table sender.tb10

GGSCI (pc6 as ogg@hyyk) 49 > add trandata sender.tb10

Add table-level additional logs

3. Configure the delivery process

GGSCI (pc6 as ogg@hyyk) 39 > add extract t_dump exttrailsource. / dirdat/oo

GGSCI (pc6 as ogg@hyyk) 41 > add rmttrail / u01 qqr appScale oggdUnix dirdatmax vvextract t_dump

GGSCI (pc6 as ogg@hyyk) 42 > edit params t_dump

Extract t_dump

Tranlogoptions excludeuser ogg

Setenv (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK")

Setenv (ORACLE_SID= "hyyk")

Passthru

Userid ogg,password oracle

Rmthost 192.168.1.80,mgrport 7809

Rmttrail / u01/app/oggd/dirdat/vv

Table sender.tb10

4. Configure the replication process on the destination side

GGSCI (ogg-80 as ogg@ogg) 3 > add replicat rep_t,exttrail. / dirdat/vv,checkpointtable ogg.checkpoint

GGSCI (ogg-80 as ogg@ogg) 5 > edit params rep_t

Replicat rep_t

-- handlecollisions

Assumetargetdefs

Setenv (NLS_LANG= "AMERICAN_AMERICA.ZHS16GBK")

Setenv (ORACLE_SID= "ogg")

Userid ogg,password oracle

Map sender.tb10,target receiver.tb10

Before the experiment, take a look at executing insert update delete in the source table.

Data analysis: Insert

We insert data into the source table

SENDER@hyyk > insert into tb10 values (1, 2, 3)

SENDER@hyyk > commit

Query on the destination side

We use dumplog on the source side to view

Logdump 72 > open. / dirdat/ww000000000

Current LogTrail is / u01/app/oggs/dirdat/ww000000000

Logdump 73 > ghdr on

Logdump 74 > detail on

Logdump 75 > detail date

This verifies that there are only post-images in the trail file.

Data analysis: update

We're on the target side.

SENDER@hyyk > update tb10 set col2=4 where col1=1

SENDER@hyyk > commit

Target segment query

Use dumplog to view

Update tb10 set col2=4 where col1=1 is executed here

Logdump will have a record of col1.

This is to show that Oracle logs only record changing columns and additional log corresponding columns by default, and additional logs correspond to primary key columns, and col1 is a gradual column.

Data analysis: delete

In the target execution

SENDER@hyyk > delete tb10 where col1=1

SENDER@hyyk > commit

SENDER@hyyk > select * from tb10

No rows selected

Use dumplog to view queries in the target segment

It is stated here that the delete operation retains only the previous image

About how the Oracle additional log is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.