Supplemental logs in Oracle (supplemental log)

In the configuration of DG, there are related to the knowledge of additional logs, so do to organize.

The supplemental log is not a separate log, it is a supplemental information to the change vectors in the redo record, which increases the amount of records recorded by the change vector, and some features of the Oracle database require that the supplemental log be enabled for normal or better work, such as Logminer, DG, Flashback transaction query, flashback transaction.

Oracle The redo records produced by default in the INSERT and delete commands are sufficient to indicate what values are changed before and after each field of the modified row, insert, the change does not exist, the resulting redo record records the value of each field of the new row, delete, the change does not exist, However, since Redo records are also responsible for making changes to the undo block, the values of the fields before the row is deleted are also recorded in the change vectors of the undo block, which is the redo record of the insert and delete commands, and all the information for the entire line can be found.

However, update differs from insert and delete to all fields of a row, often just changing one or several fields, in performance considerations, Oracle does not need to keep the values of the modified fields in the change vectors. In fact, the Morbihan vector in the undo block only records the value of the changed field before the change, and the new value of the Modified field is recorded in the change vector of the block, and the other fields that are not modified in the same row are not recorded before the modified value, because they are not modified.

such as: Update scott.emp set sal=12000 where empno=7369;

In order to support instance recovery and media recovery, the redo record only needs to record the value of the Sal field is 12000, in the regular redo record does not appear completely empno to 7369 of the information

Insert , delete, and update the general redo record specifically describes what information is to ensure that the instance recovery and media recovery can be normal, the irrelevant information is not recorded in the redo log. It is precisely because there is a change vector for the undo data block, so that instance recovery and media recovery are also introduced to ensure that the rollback operation can be performed.

If the supplemental log is enabled, the record of the change vector of the data block can be changed; The value of the Sal field is 12000, and after the additional record that the row is modified before the value of the first field empno is 7369 and Sal is 800 (the value before the modification), more information means that the update command is more likely to be restored in an equivalent order, with improved data recoverability

The supplemental log is primarily for the update service, and the purpose of the supplement is to highly restore the real update command.

Supplemental logs can be enabled at the database level and at the table level

Supplemental log can be started at the database level, minimal logging will be logged at the database level enough information, usually the logical standby database created minimal logging is sufficient to use. Database Supplemental Log Supplemental logs can also be created for all fields, primary key fields, unique fields, and foreign key fields. You can also set the table-level setting supplemental log, called identification key logging, and different types of supplemental log will produce different sizes of logging.

Log information for logical standby database records has the following rules:

If a table has a primary key defined, the primary key is logged as part of the UPDATE statement to identify the modified row

If the primary key does not exist, the shortest non-null constraint, the unique index is logged as part of the UPDATE statement to identify the modified row in the absence of primary and non-null constraints/indexes, all columns are logged as part of the UPDATE statement identifies modified rows, that is, fields other than Long,lob,longg raw, object type, and collection type, all fields will be recorded a function index, even if it is defined as unique, cannot be used to uniquely identify the modified row, as long as the modified row can is uniquely identified, the logical standby database supports the synchronization of function index tables

The main database is recommended to add primary key or non-null unique index to the table as far as possible, ensure SQL apply can effectively apply Redo data to update the logical standby database.

To find a table in a database that does not have a unique logical identity

select owner, table_name
  from dba_logstdby_not_unique
 where (owner, table_name) not in
       (select distinct owner, table_name from dba_logstdby_unsupported)
   and bad_column = ‘Y‘;

To add a constraint that disables primary key rely

If you can ensure that the behavior applied to the table is unique, create a disabled primary key rely constraint on the table, which avoids maintenance

overhead for primary database primary key Use the relay disable clause of the ALTER TABLE statement on the primary database table to create a disabled primary key rely constraint. table test has two column IDs, name, and the following SQL in the test table to create the Disable rely constraint

ALTER TABLE test Add primary KEY (Id,name) rely disable;

When the rely constraint is explicitly specified, the system will be able to identify the uniqueness of the jiading row by specifying the field

to improve the performance of SQL apply, add a unique constraint/index to the field on the table to ensure that the logical standby database A maintained table can uniquely identify a row, if no unique constraint/index is added, when executed on a table by SQL apply full table scans during update or DELETE statements can degrade performance.

To view the Supplemental log status in the current database:

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,
       SUPPLEMENTAL_LOG_DATA_PK  pk,
       SUPPLEMENTAL_LOG_DATA_UI  ui,
       SUPPLEMENTAL_LOG_DATA_FK  fk,
       SUPPLEMENTAL_LOG_DATA_ALL "all"
  from v$database;  2    3    4    5    6  

MIN	 PK  UI  FK  all
-------- --- --- --- ---
NO	 NO  NO  NO  NO

As you can see, the types of supplemental logs are: Minimum supplemental log, identify key field supplemental log two categories

1. Minimum supplemental log

Minimum Supplemental log is the most basic kind of database-level supplemental log,

Enable minimal supplemental logging

SQL> alter database add supplemental log data ;

Database altered.

SQL> select supplemental_log_data_min min from v$database ;

MIN
--------
YES

Turn off minimal supplemental logging

SQL> alter database drop supplemental log data ;

Database altered.

SQL> select supplemental_log_data_min min from v$database ;

MIN
--------
NO

It is important to note that all SQL command cursors in the shared pool are illegal by enabling or shutting down the database-level supplemental logs (including minimal supplemental logs and several other logs), which means that the resolution should rise significantly in the short term.

2. Identify key field supplemental logs

Identifies four key field supplemental logs: Primary key, unique index, foreign key, all fields supplemental log

2.1 Primary Key Replenishment log

the role of the primary key supplement log is to add the old value of the primary key field of the modified row to the redo record of the update command, which is an unconditional supplemental log, so-called unconditional, regardless of whether the primary key field itself is modified by the update command, its old values are recorded. However, there is no guarantee that each table must have a primary key. If there is a table with no primary key, the primary key field is replaced by the smallest, non-null, unique index field. If a non-empty index field in the table structure is not available, Oracle will: Record the old values of all fields (except LOB and long) of the modified row, which will cause the data volume of the redo record to skyrocket, so if you want to enable the primary key replenishment log, and to maintain LGWR and redo logs, each table preferably has a primary key or at least one non-null unique field.

To enable the primary key replenishment log:

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

Database altered.

SQL> select supplemental_log_data_pk as pk from v$database ;

PK
---
YES

Identifying key field supplemental logs (including primary key replenishment logs) must be based on minimal supplemental logs and when enabled, Oracle will implicitly open the minimum supplemental log state to implicit if the minimum supplemental log is not enabled

It is also not possible to turn off the minimal supplemental log when the Identity key field supplement log is not closed

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,
       SUPPLEMENTAL_LOG_DATA_PK  pk,
       SUPPLEMENTAL_LOG_DATA_UI  ui,
       SUPPLEMENTAL_LOG_DATA_FK  fk,
       SUPPLEMENTAL_LOG_DATA_ALL "all"
  from v$database;  2    3    4    5    6  

MIN	 PK  UI  FK  all
-------- --- --- --- ---
IMPLICIT YES NO  NO  NO

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

2.2 Unique Index Supplemental log

The value before the field is modified is only recorded when the unique field is updated, because the unique key index is the one that can be built on multiple fields

Such as:

SQL> create unique index name_idx on hr.employees(first_name,last_name);

Index created.

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

Database altered.

SQL> select supplemental_log_data_ui ui from v$database ;

UI
---
YES

up The date command now updates first_name, and the regular log records only the old values of the First_Name field, and the unique key index will record first_name and last_name, so this supplemental log can be said to be a unique Indexing Service for the composite field.

2.3 Foreign Key Supplemental log

The foreign key supplement log and the unique index supplemental log are the same as conditionally, only if the Foreign key field is updated by the update command

When modified, the old value before its modification is recorded. So this supplemental log is purely for the composite field foreign key service

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

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,
       SUPPLEMENTAL_LOG_DATA_PK  pk,
       SUPPLEMENTAL_LOG_DATA_UI  ui,
       SUPPLEMENTAL_LOG_DATA_FK  fk,
       SUPPLEMENTAL_LOG_DATA_ALL "all"
  from v$database;  2    3    4    5    6  

MIN	 PK  UI  FK  all
-------- --- --- --- ---
IMPLICIT YES YES YES NO

2.4 All fields Supplemental log

All fields Supplemental log The same as the primary key supplement log is unconditional, regardless of which field is modified by the update command, all fields (except the Lob,long type) of the old values will be recorded, the effect is the same as the primary key supplement log is enabled without a primary key and no non-null unique index field, In this way, almost all the table data is moved to the redo log, not only the current, but also the historical data is not dropped. This is good for recovery operations, but not very good for LGWR and disk space, and it is generally rare to enable such a log

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

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,
       SUPPLEMENTAL_LOG_DATA_PK  pk,
       SUPPLEMENTAL_LOG_DATA_UI  ui,
       SUPPLEMENTAL_LOG_DATA_FK  fk,
       SUPPLEMENTAL_LOG_DATA_ALL "all"
  from v$database;  2    3    4    5    6  

MIN	 PK  UI  FK  all
-------- --- --- --- ---
IMPLICIT YES YES YES YES

Database-level supplemental logs, with the common denominator that enabling and shutting down causes all SQL cursors to be illegal

Regardless of which identity key field supplemental log you need to enable, you need to enable minimal supplemental logging, and the effects of 4 identifying key fields can be cumulative and non-conflicting.

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

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,
       SUPPLEMENTAL_LOG_DATA_PK  pk,
       SUPPLEMENTAL_LOG_DATA_UI  ui,
       SUPPLEMENTAL_LOG_DATA_FK  fk,
       SUPPLEMENTAL_LOG_DATA_ALL "all"
  from v$database;  2    3    4    5    6  

MIN	 PK  UI  FK  all
-------- --- --- --- ---
NO	 NO  NO  NO  NO

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

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN min,
       SUPPLEMENTAL_LOG_DATA_PK  pk,
       SUPPLEMENTAL_LOG_DATA_UI  ui,
       SUPPLEMENTAL_LOG_DATA_FK  fk,
       SUPPLEMENTAL_LOG_DATA_ALL "all"
  from v$database;  2    3    4    5    6  

MIN	  PK  UI  FK  all
--------  --- --- --- ---
YES	  YES YES YES YES

Table-Level supplemental logs

For a table only, there is no need to enable the Supplemental logging feature throughout the database scope, and you should enable the database-level minimal supplemental log before you enable the table-level supplemental log

Table-level supplemental logs are divided into primary key, unique index, foreign key, full field, and custom 5 types, the first four and the data level corresponding to the type characteristics of the same, only on a specific table enabled.

Such as:

ALTER TABLE SCOTT.EMP add supplemental log data (primary key) columns;

ALTER TABLE SCOTT.EMP add supplemental log data (unique) columns;

ALTER TABLE SCOTT.EMP add supplemental log data (foreign key) columns;

ALTER TABLE SCOTT.EMP add supplemental log data (all) columns;

Custom fields are features that are unique to table-level supplemental logs, and users can arbitrarily specify that the old values of those fields need to be added to the record.

ALTER TABLE Hr.employees add supplemental log Group Emp_info (first_name,last_name,email);

Assign the First_name,last_name,email field as a log group named Empinfo, meaning that if any of these 3 fields are modified by the Update command, the redo record must record the old value of all 3 fields, which is called conditional. Unconditionally Add the keyword always

ALTER TABLE Hr.employees add supplemental log Group Emp_info (first_name,last_name,email) always;

Table-level supplemental logs can be queried for dba_log_groups and Dba_log_grup_columns

select g.log_group_name name,
       g.owner || ‘.‘ || g.table_name table_name,
       g.always,
       g.generated,
       c.column_name
  from dba_log_groups g, dba_log_group_columns c
 where g.log_group_name = c.log_group_name(+)
   and g.table_name = c.table_name(+)
   and g.table_name = ‘EMPLOYEES‘
   and g.owner = ‘HR‘
 order by 1, 2, 3, 4;

Enabling and closing a table-level supplemental log on a particular table causes all SQL cursors referencing the table to be illegal, causing a period of hard analysis to increase.

The supplemental log is primarily an update service, with additional records of the old value of the specified field, allowing the tool capable of analyzing the redo log to highly restore the update command, which can be considered a special kind of backup due to the extra recording of the old value of the field.

Supplemental logs in Oracle (supplemental log)