Oracle补全日志(Supplemental logging)特性因其作用的不同可分为以下几种：最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique),支持外键(foreign key)。包括LONG,LOB,LONG RAW及集合等字段类型均无法利用补全日志。
最小(Minimal)补全日志开启后可以使得logmnr工具支持链式行，簇表和索引组织表。可以通过以下SQL检查最小补全日志是否已经开启：
| SELECT supplemental_log_data_min FROM v$database; |
若结果返回YES或IMPLICIT则说明已开启最小补全日志，当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。
一般情况下我们在使用逻辑备库时启用主键和惟一键的补全日志，而有时表上可能没有主键，惟一键或唯一索引；我们通过以下实验总结这种情况下Oracle的表现。
首先建立相关的测试表：
| alter database add supplemental log data (primary key,unique index) columns ;
create table test (t1 int , t2 int ,t3 int ,t4 int )； alter table test add constraint pk_t1 primary key (t1); –添加主键 随后使用循环插入一定量的数据 update test set t2=10; commit; — 更新数据 |
使用LOGMNR工具分析之前的操作，可以看到REDO中记录的SQL形式如下：
| update “SYS”.”TEST” set “T2” = ’10’ where “T1” = ’64’ and “T2” = ’65’ and ROWID = ‘AAAMiSAABAAAOhiAA/’; |
其中where字句后分别记录了主键值，被修改字段的值和原行的ROWID。
现在我们将原表上的主键去掉来观察。
| alter table test drop constraint pk_t1 ;
update test set t2=11; commit; — 更新数据 使用LOGMNR分析可以发现，REDO中的SQL记录如下： update “SYS”.”TEST” set “T2” = ’11’ where “T1” = ‘1’ and “T2” = ’10’ and “T3” = ‘3’ and “T4” = ‘4’ and ROWID = ‘AAAMiSAABAAAOhiAAA’; |
当没有主键的情况下，where子句后记录了所有列值和ROWID。
| 以下实验在存在唯一索引情况下的表现
create unique index pk_t1 on test(t1); update test set t2=15; commit; 使用LOGMNR分析可以发现，REDO中的SQL记录如下： update “SYS”.”TEST” set “T2” = ’15’ where “T1” = ‘9’ and “T2” = ’11’ and “T3” = ’11’ and “T4” = ’12’ and ROWID = ‘AAAMiSAABAAAOhiAAI’; 以上是t1列有唯一索引但不限定not null的情况，下面我们加上not null限制 alter table test modify t1 not null； update test set t2=21; commit; 使用LOGMNR分析可以发现，REDO中的SQL记录如下： update “SYS”.”TEST” set “T2” = ’21’ where “T1” = ‘2’ and “T2” = ’15’ and ROWID = ‘AAAMiSAABAAAOhiAAB’; |
如以上SQL所示，在存在唯一索引的情况下where子句后仍记录了所有列和ROWID;在存在唯一索引和非空约束的情况下表现与存在主键的情况一致。
当某个表上的列数量较多时且没有主键或唯一索引和非空约束的情况下，开启补全日志可能导致重做日志总量大幅提高。
| 首先建立一个存在250列的表：
Drop table test; create table test ( t1 varchar2(5), t2 varchar2(5), t3 varchar2(5), t4 varchar2(5), …t250 varchar2(5))
insert into test values (‘TEST’,’TEST’ ……); commit; –将255个列填入数据 alter database drop supplemental log data (primary key,unique index) columns; –关闭补全日志 set autotrace on; update test set t2=’BZZZZ’ where t1=’TEST’; commit; 可以从自动跟踪信息中看到，本条更新产生了516的重做量。 alter database add supplemental log data (primary key,unique index) columns； –重新开启补全日志 update test set t2=’FSDSD’ where t1=’TEST’; 跟踪信息显示产生了3044的重做量。 |
补全日志因作用域的不同又可分为数据库级的和表级的。表级补全日志又可以分为有条件的和无条件的。有条件限制的表级补全日志仅在特定列被更新时才会起作用，有条件限制的表级补全日志较少使用，这里我们不做讨论。
下面我们来观察无条件限制表级补全日志的具体表现：
| alter database drop supplemental log data (primary key,unique index) columns;
alter table test add supplemental log data (primary key,unique index) columns; update test set t2=’ZZZZZ’; commit; 使用LOGMNR工具查看redo中的SQL: 可以发现where子句之后包含了所有列值。 delete test; commit; 使用LOGMNR工具查看redo中的SQL: delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘ZZZZZ’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” …… delete操作同样在where子句之后包含了所有列值。 又我们可以针对表上字段建立特定的补全日志组，以减少where子句后列值的出现。 alter table test drop supplemental log data (primary key,unique index) columns; –关闭表上原先的补全日志 alter table test add supplemental log group test_lgp (t1 ,t2,t3,t4,t5,t6,t12,t250) always; –创建补全日志组 update test set t2=’XXXXX’ ; commit; 使用LOGMNR工具查看redo中的SQL: update “SYS”.”TEST” set “T2” = ‘XXXXX’ where “T1” = ‘TEST’ and “T2” = ‘TEST’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” = ‘TEST’ and “T6” = ‘TEST’ and “T12” = ‘TEST’ and “T250” = ‘TEST’ and ROWID = ‘AAAMieAABAAAOhnAAA’; 如上所示重做日志中正确地显示了UPDATE操作中用户指定的字段值。 delete test; 使用LOGMNR工具查看redo中的SQL: delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘XXXXX’ and “T3” = ‘TEST’ …… delete操作在重做日志中仍然保留了所有列值。 |
针对字段较多的表，我们在能够以多个列保证数据唯一性且非空的情况下(即应用概念上的主键)来指定表上的补全日志组，以减少update操作时所产生的重做日志，而对于delete操作则无法有效改善。
complex post. due one detail where I quarrel with it. I am emailing you in detail.
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
Goal
Supplemental Logging :
Redo log files are generally used for instance recovery and media recovery. The data needed for such operations is automatically recorded in the redo log files. However, a redo-based application may require that additional columns be logged in the redo log files. The process of logging these additional columns is called supplemental logging.
Please refer to the following documentation for Utilities, which mentions two important points regarding Supplemental Logging.
Oracle® Database
Utilities
10g Release 2 (10.2)
B14215-01
1) You must enable supplemental logging prior to generating log files that will be analyzed by LogMiner.
When you enable supplemental logging, additional information is recorded in the redo stream that is needed to make the information in the redo log files useful to you. Therefore, at the very least, you must enable minimal supplemental logging, as the following SQL statement shows:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To determine whether supplemental logging is enabled, query the V$DATABASE view, as the following SQL statement shows:
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
If the query returns a value of YES or IMPLICIT, minimal supplemental logging is enabled.
You can turn off Supplemental Logging by following command.
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
2) By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable.
Let us take an example of this, which is using Online Catalog option for logminer dictionary.
Part A : Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned OFF (default option )
Part B : Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned ON.
Solution
Part A :
Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned OFF (default option )
———————————————————————————————————–
Example assumes that database is in ARCHIVE LOG MODE
SQL> connect / as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL> alter system switch logfile;
System altered.
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
——————————————————————————–
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_11_17\O1_MF_1_10_4L2PCZNJ_.ARC
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
——–
NO
SQL> connect scott/tiger
Connected.
SQL> create table test ( n number );
Table created.
SQL> insert into test values ( 1);
1 row created.
SQL> insert into test values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> connect / as sysdba
Connected.
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
——————————————————————————–
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_11_17\O1_MF_1_ 12_4L2PPL4D_.ARC
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( –
> LOGFILENAME =>’C:\ORACLE\PRODUCT\10.2.0FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_11_17\O1_MF_1_12_4L2PPL4D_.ARC’ , –
> OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( –
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> SELECT SQL_REDO , SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username=’SCOTT’;
no rows selected
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
PL/SQL procedure successfully completed.
———————————————————————————————————–
———————————————————————————————————–
———————————————————————————————————–
Part B :
Shows result from V$LOGMNR_CONTENTS when supplemental logging is turned ON.
The same example as above but with Supplemental Logging turned ON.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
——–
YES
SQL> alter system switch logfile;
System altered.
SQL> connect scott/tiger
Connected.
SQL> create table test2 ( n2 number );
Table created.
SQL> insert into test2 values ( 2211);
1 row created.
SQL> insert into test2 values ( 2222);
1 row created.
SQL> commit;
Commit complete.
SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> SELECT NAME FROM V$ARCHIVED_LOG
2 WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
NAME
——————————————————————————–
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_11_17\O1_MF_1_14_4L2QFMRF_.ARC
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( –
> LOGFILENAME => ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_11_17\O1_MF_1_14_4L2QFMRF_.ARC’ , –
> OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( –
> OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> SELECT SQL_REDO , SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username=’SCOTT’;
SQL_REDO
——————————————————————————–
SQL_UNDO
——————————————————————————–
commit;
set transaction read write;
insert into “SCOTT”.”TEST2″(“N2”) values (‘2211’);
delete from “SCOTT”.”TEST2″ where “N2” = ‘2211’ and ROWID = ‘AAAM7oAAEAAAAGtAAA’
SQL_REDO
——————————————————————————–
SQL_UNDO
——————————————————————————–
;
insert into “SCOTT”.”TEST2″(“N2”) values (‘2222’);
delete from “SCOTT”.”TEST2″ where “N2” = ‘2222’ and ROWID = ‘AAAM7oAAEAAAAGtAAB’
;
commit;
23 rows selected.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR();
PL/SQL procedure successfully completed.
Please make a note that V$LOGMNR_CONTENTS also shows Data dictionary logs like inserting data into Data Dictionary of Database but have been removed in our example and only relevant data is captured.
Applies to:
Oracle GoldenGate – Version: 4.0.0 – Release: 4.0.0
Information in this document applies to any platform.
Solution
There are two types of supplemental logging: database supplemental logging and table supplemental logging.
Database supplemental logging specifies supplemental logging for an entire database, while table supplemental logging enables you to specify log groups for supplemental logging for a particular table. If you use table supplemental logging, then you can choose between unconditional and conditional log groups.
Unconditional log groups log the before images of specified columns any time the table is updated, regardless of whether the update affected any of the specified columns. This is sometimes referred to as an ALWAYS log group. Conditional log groups log the before images of all specified columns only if at least one of the columns in the log group is updated.
Supplementing logging at the database level, unconditional log groups at the table level, and conditional log groups at the table level together determine which old values are logged in an update statement or piecewise LOB update.
To check whether one or more log groups are specified for the table at the source database, run the following query:
COLUMN LOG_GROUP_NAME HEADING ‘Log Group’ FORMAT A20
COLUMN TABLE_NAME HEADING ‘Table’ FORMAT A20
COLUMN ALWAYS HEADING ‘Type of Log Group’ FORMAT A30
SELECT
LOG_GROUP_NAME,
TABLE_NAME,
DECODE(ALWAYS,
‘ALWAYS’, ‘Unconditional’,
NULL, ‘Conditional’) ALWAYS
FROM DBA_LOG_GROUPS;
Your output looks similar to the following:
Log Group Table Type of Log Group
——————– ——————– ——————————
LOG_GROUP_DEP_PK DEPARTMENTS Unconditional
LOG_GROUP_JOBS_CR JOBS Conditional
To list the columns in a particular log group, query the DBA_LOG_GROUP_COLUMNS data dictionary view. You can also query the V$DATABASE dynamic performance view to display supplemental logging specified at the database level.
The GoldenGate ADD TRANDATA command is equivelant to the following SQL statement,
alter table [TABLE_NAME] add supplemental log group [GROUP_NAME] ([COLUMN_LIST]) always;
The Group_name will usually be GGS_TABLENAME, the COLUMN_LIST is either the primary key columns or unique index columns, or if there is no primary key or unique index, it will be all columns.
The GoldenGate DELETE TRANDATA command is equivelant to the following SQL statement
alter table [TABLE_NAME] drop supplemental log group [GROUP_NAME]
To check the database level supplemental logging info
select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, force_logging from v$database;
GoldenGate needs at least “supplemental_log_data_min” to be YES to Extract chained row correctly from the Oracle redo log. See attached doc for the detail explain of what other options are.
Applies to:
Oracle Server Enterprise Edition – Version: 10.1.0.2 to 11.2
Information in this document applies to any platform.
Purpose
Oracle Streams enables the sharing of data and events in a data
stream, either within a database or from one database to another.
This article is intended to assist Replication DBAs in understanding the supplemental logging requirements of Streams Replication.
Scope and Application
To be used by Oracle support analysts and replication DBAs to
setup replication using Streams in Oracle 10.1 or higher. Most of the information in this note is obtained from ORACLE documentation. The links to the documentation can be found in the REFERENCES section.
Streams Replication Supplemental Logging Requirements
INTRODUCTION
ORACLE redo log files contain redo information needed for instance and media recovery . However, some of the redo based applications such as STREAMS, Logical Standby, Adhoc LogMiner need additional information to be logged into the redolog files. The process of logging this additional information into the redo files is called Supplemental Logging.
SUPPLEMENTAL LOGGING LEVELS
There are two levels of Supplemental Logging.
1. Database Level Supplemental Logging – There are two types of Database level logging.
Minimal supplemental Logging – This places information needed for identifying the rows in the redo logs. This can be enabled using the following command.
SQL> Alter database add supplemental log data;
Identification Key Logging – This places the before and after images of the specified type of columns in the redo log files. This type of logging can be specified for ALL ,PRIMARY KEY, UNIQUE and FOREIGN KEY. This can be enabled using the following command
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL,PRIMARY KEY,UNIQUE,FOREIGN KEY) COLUMNS;
You can check the database level supplemental logging using the following query
select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,
SUPPLEMENTAL_LOG_DATA_all from v$database;
2. Table-level Supplemental Logging – Creates individual log groups for each table. Logging can be unconditional or conditional .
Unconditional Logging means before images of the columns are logged regardless of whether the column is updated or not. Conditional means the before images of the columns are logged only when the corresponding columns are updated. After images are always captured for the columns specified in the log group. The following query can be used to check the table level log groups defined in the database.
select * from DBA_LOG_GROUPS;
select owner,log_group_name,table_name,column_name,logging_property, from DBA_LOG_GROUP_COLUMNS;
SUPPLEMENTAL LOGGING REQUIREMENTS FOR STREAMS REPLICATION
Supplemental logging should always be enabled on the source database. The following types of columns at the APPLY site are candidates for supplemental logging on the source.
1. All columns that are used in Primary keys at the source site for which changes are applied
on the target must be unconditionally logged at the table level or at the db level.
2. All columns that are used as substitute columns at the APPLY site must be unconditionally logged .
3. All columns that are used in DML handlers, Error handlers, Rules, Rule based transformations,
virtual dependency definitions, Subset rules must be unconditionally logged.
4. All columns that are used in column list for conflict resolution methods must be conditionally logged,
if more than one column from the source is part of the column list.
5. If Parallelism of APPLY is > 1, then all columns that are part of FOREIGN KEY, UNIQUE KEY
constraints that are defined on more than 1 column and BIT MAP indexes that are defined on more than
one column at the source must be conditionally logged.
You can enable table level supplemental logging using the
following command.
alter table HR.EMPLOYEES
ADD SUPPLEMENTAL LOG GROUP emp_fulltime (EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID);
Alternately, you can create user-defined log groups using the following command. Omitting the ALWAYS clause creates a conditional log groups.
Alter table HR.EMPLOYEES
add SUPPLEMENTAL LOG data (PRIMARY KEY,UNIQUE,FOREIGN,ALL) columns;
Note: LOB, LONG, and ADT columns cannot be supplementally logged.
These columns will be ignored when an ALL clause if specified in the command.
Alter table HR.EMPLOYEES
ADD SUPPLEMENTAL LOG GROUP emp_parttime(
DEPARTMENT_ID NO LOG, EMPLOYEE_ID);
In versions 10g and above, prepare_xxx_instantiation procedure implicitly creates supplemenal log groups. Type of supplemental logging that is enabled implicitly using this command can be checked using the sql in the following link to the documentation. However, additional supplemental logging might neeed to be enabled depending on the requirements as mentioned above.
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1
This problem can occur on any platform.
Symptoms
As part of the Streams setup you can specify supplemental logging at the database level.
It can be done manually:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
or implicitly running procedures like DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
or if you setup Streams using Grid Control it is automatically executed by SrcSchemaRule1 Stage Step 12.
In some cases, this step hangs and the statement ALTER DATABASE add SUPPLEMENTAL LOG DATA remains waiting for TX lock in shared mode.
Cause
The statement ALTER DATABASE add SUPPLEMENTAL LOG DATA is waiting for TX lock in shared mode when there is any uncommitted transaction. This is the expected behavior.
You can issue ALTER DATABASE ADD SUPPLEMENTAL LOG DATA when the database is open. However, Oracle Database will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated. Besides, we also need to wait for the completion of all the in-flight transaction so that any redo generated after this DDL would have the right supplemental logging attributes.
Solution
You need to wait for the completion of all the in-flight transaction.
In those databases with high activity where there are always active transactions the supplemental logging can be enabled by bouncing the database and running the statement manually:
STARTUP MOUNT
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE OPEN;
References
ORA-32593 database supplemental logging attributes in flux
Why Does GoldenGate For Oracle Require Supplemental Logging At The Database Level For Capture?
Applies to:
Oracle GoldenGate – Version: 4.0.0 – Release: 4.0.0
Information in this document applies to any platform.
Database Oracle
Solution
Question
Why does GoldenGate for Oracle require Supplemental Logging at the Database Level for Capture?
Answer
GoldenGate version 7.3.1 for Oracle 9i and above has enhanced the Log Based Capture. In the process, it became necessary to enable supplemental logging at the database level. By enabling this feature, Oracle adds in a few extra bytes of information into the redo logs for some DML operations.