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

This article introduces the knowledge of "how to understand nologging, force logging, supplemental log Diary". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Nologging

The influence of nologging on log writing is related to database operation mode, table mode and insertion mode.

The insert mode of the table refers to whether the hint prompt of append is adopted:

Insert / * + append*/ into T1 values (1, 'First')

When the database is in non-archive mode:

Regardless of whether the table uses nologging or logging, no redo is generated when the table is inserted using append, and redo is generated when the table is not using append.

When the database is in archive mode:

If the table uses nologging schema and uses append for insertion, redo will not be generated, and if append is not used, redo will still be generated.

If the table uses logging schema, it is generated regardless of whether or not append,redo is used when inserting.

To sum up: for non-archive mode, the insert statement does not generate redo as long as it uses append, while for archive mode, append still generates redo unless the table uses nologging.

Check whether the log mode of the table is logging. By default, the status when creating the table is logging:

Select table_name,logging from dba_tables where owner='SCOTT' and table_name='EMP'

TABLE_NAME LOG

-

EMP YES

Oracle has an internal parameter, _ disable_logging, which defaults to false. If this parameter is changed to true, Oracle will not log at all when changing the table, but this parameter should be used with caution.

II. Force logging

Make Oracle write to redo no matter what you do by using mandatory logging:

Alter database force logging

Check whether the database is in force logging state. Default is NO:

Select force_logging from v$database

FOR

-

NO

III. Supplemental log

The supplementary log, mainly for the update command, is a supplementary record of the change vector block in the redo log. Log miner (LogMiner), flashback transactions and their queries all need supplementary log support. The purpose of the supplement is to highly restore the update command, avoid the impact of line migration and line movement caused by the update command on the log analysis, and let LogMiner identify that the update command is not completed by insert and delete by analyzing the redo log. If supplementary logging is not enabled, the redo log saves only the old values of the fields changed by the update command in the change vector of the undo block. On the other hand, the new value of the modified field is recorded in the change vector in the data block, and the unmodified field in the same line is not recorded. If supplementary logging is enabled, the change vector of the undo data block in the redo log records not only the value before and after the modified field, but also the value of the condition of the modified field.

Supplementary logs are divided into database-level supplementary logs and table-level supplementary logs.

The database-level supplementary log is divided into the minimum supplementary log and the identification key field supplementary log.

Minimum supplementary log:

Is the most basic kind of database-level supplementary log, and LogMiner relies on the minimum supplementary log to work. Other supplementary logs are based on the database-level minimum supplementary log, and if the database-level minimum supplementary log is not enabled, the database's minimum supplementary log (Implicit) is automatically enabled when other supplementary logs are enabled or used.

Enable minimum supplementary log: alter database add supplemental log data

Turn off the minimum supplementary log: alter database drop supplemental log data

Identify the key field supplementary log:

It can be divided into four types: primary key, foreign key, unique index and supplementary log of all fields.

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

Alter database add supplemental log data (Primary key) columns

2) unique index supplementary log:

Unique indexes mainly serve composite indexes (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

3) Foreign key supplementary log:

As with the unique index supplementary log, the old values before modification are 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

4) Supplementary log for all fields:

As the name implies, the values of all fields are recorded regardless of whether they are modified or not (except the LOB and LONG fields). It will cause the disk to grow rapidly and the LGWR process is busy, so it is not recommended.

Alter database add supplemental log data (all) columns

These four supplementary logs can be used in parallel with cumulative effects.

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 T1 add supplemental log data (primary key) columns

2) alter?table T1 add supplemental log data (unique) columns

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

4) alter table T1 add supplemental log data (all) columns

5) alter table T1 add supplemental log group group_name (col01,col02,col05,col09) always;?

The table-level supplementary log of custom fields allows users to specify which fields' old values need to be supplemented, which can be divided into conditional and unconditional daily solstice groups:

Conditional log group: as long as one of the columns described in the supplementary log group has been modified (update), record the previous mirrors (old values) of all description columns in that log group.

Unconditional log group: record the previous mirror (old value) of all description columns in the log group when the table is modified, regardless of whether the change affects any of the description columns in the log group. The always keyword is required.

Table-level supplementary logs are available by querying the data dictionary views dba_log_groups and dba_log_group_columns.

Col owner for a10

Col log_group_name for a20

Col table_name for a20

Select * from dba_log_groups where owner = 'SCOTT'

OWNER LOG_GROUP_NAME TABLE_NAME LOG_GROUP_TYPE ALWAYS GENERATED

-

SCOTT SYS_C0014349 T1 ALL COLUMN LOGGING ALWAYS GENERATED NAME

SCOTT SYS_C0014350 T1 PRIMARY KEY LOGGING ALWAYS GENERATED NAME

SCOTT SYS_C0014351 T1 UNIQUE KEY LOGGING CONDITIONAL GENERATED NAME

SCOTT SYS_C0014352 T1 FOREIGN KEY LOGGING CONDITIONAL GENERATED NAME

Col owner for a10

Col table_name for a20

Col column_name for a20

Select * from dba_log_group_columns where owner = 'HR'

OWNER LOG_GROUP_NAME TABLE_NAME COLUMN_NAME POSITION LOGGIN

HR GGS_92582 EMPLOYEES1 EMPLOYEE_ID 1 LOG

HR GGS_91276 COUNTRIES COUNTRY_ID 1 LOG

HR GGS_91281 DEPARTMENTS DEPARTMENT_ID 1 LOG

HR GGS_91286 EMPLOYEES EMPLOYEE_ID 1 LOG

HR GGS_91284 JOBS JOB_ID 1 LOG

HR GGS_91290 JOB_HISTORY EMPLOYEE_ID 1 LOG

HR GGS_91290 JOB_HISTORY START_DATE 2 LOG

HR GGS_91278 LOCATIONS LOCATION_ID 1 LOG

HR GGS_91274 REGIONS REGION_ID 1 LOG

Because insert records all modified fields and delete records all pre-modified fields, supplementary logs only affect update, and SQL cursors in the shared pool will be invalidated when supplementary logs are enabled.

"how to understand nologging, force logging, supplemental log Diary" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.