Oracle補充日誌及日誌記錄規則

jinqibingl發表於2015-02-13
Oracle

    這篇文章是在《構建最高可用Oracle資料庫系統：Oracle 11gR2 RAC管理、維護與效能優化》11.4.2小節的基礎上進一步修改而成的，並感謝老譚的提醒和幫助。

    Supplemental Log是一種補充型別的日誌，主要用於邏輯Standby資料庫的同步、LogMiner、Streams和Flashback等技術。對於邏輯Standby資料庫來說，不能通過ROWID來標識某一行，Oracle推薦使用主鍵、約束和索引唯一標識行資料，但如果某部分表由於某種原因沒有主鍵、唯一約束和唯一索引，不能有效的標識行資料，那麼必須通過Supplemental Log記錄多個欄位甚至所有欄位值來唯一標識錶行。Supplemental Log也是記錄在線上Redo日誌中，所以啟用Supplemental Log會加快線上Redo日誌的填充。

    Supplemental Log可以在資料庫級別啟動，在資料庫級別啟動Minimal Logging會記錄操作的足夠資訊，通常在主資料庫建立Minimal Logging已經足夠使用。資料庫級別Supplemental Log補充日誌還能針對所有欄位、主鍵欄位、唯一欄位和外來鍵欄位建立。另外也可以在表級別設定Supplemental Log，這叫做Identification Key Logging，不同型別的Supplemental Log會產生不同大小的日誌記錄。

    執行以下的SQL啟用或禁用資料庫級別Minimal Logging：
    ALTER DATABASE ADD|DROP SUPPLEMENTAL LOG DATA;

針對邏輯Standby資料庫記錄的日誌資訊有以下規則：
1).如果一個表有主鍵被定義，那麼主鍵被記錄作為UPDATE語句的一部分去標識修改的行。
2).如果主鍵不存在，最短的非空唯一約束、唯一索引被記錄作為UPDATE語句的一部分標識修改的行。
3).在主鍵和非空唯一約束/索引都沒有的情況下，所有的列被記錄，作為UPDATE語句的一部分標識修改的行。換句話說，除了LONG、LOB、LONG RAW、物件型別和集合型別的欄位，所有欄位都將被記錄。
4).一個函式索引，即使它是被定義為唯一的，也不能用於唯一標識修改的行。然而，只要修改的行能被唯一標識，邏輯Standby資料庫支援函式索引表的同步。

Oracle推薦在主資料庫儘可能的給表新增主鍵或者非空唯一索引，確保SQL Apply能有效的應用Redo資料更新邏輯Standby資料庫。

下面通過實驗來討論Oracle補充日誌的語法及型別：

C:\Users\LIUBINGLIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期六 3月 15 20:27:28 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col supplemental_log_data_fk format a30
SQL> col supplemental_log_data_all format a30
SQL> col supplemental_log_data_min format a30
SQL> set linesize 200
SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             YES

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES                            NO                             IMPLICIT
SUPPLEMENTAL_LOG_DATA_FK=YES表示啟動了外來鍵補充日誌。

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             IMPLICIT

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

資料庫已更改。

SQL>  select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             IMPLICIT

IMPLICIT - Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled.
YES - Minimal supplemental logging is enabled through an ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement.

    SUPPLEMENTAL_LOG_DATA_MIN=YES和IMPLICIT都表示資料庫啟動了最小化的補充日誌。YES表示通過ALTER DATABASE ADD SUPPLEMENTAL LOG DATA語句啟動的最小化補充日誌;如果啟動了對主鍵、唯一鍵、外來鍵、ALL的補充日誌，預設情況下都會開啟最小化的補充日誌，IMPLICIT表示的是通過啟動對主鍵、唯一鍵、外來鍵或者ALL的支援而開啟的最小化補充日誌。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

下面是ALTER DATABASE ADD SUPPLEMENTAL LOG DA他的語法：


SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE,FOREIGN KEY) COLUMNS;

資料庫已更改。

多種型別的補充日誌可以一起設定。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES                            NO                             IMPLICIT

SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE,FOREIGN KEY) COLUMNS;

資料庫已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             NO                             NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

資料庫已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
NO                             YES                            IMPLICIT

開啟ALL型別的補充日誌不包含外來鍵補充日誌，ALL表示的是ALL COLUMN。

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

資料庫已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES                            YES                            IMPLICIT

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

資料庫已更改。

SQL> select SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEMENTAL_LOG_DATA_FK       SUPPLEMENTAL_LOG_DATA_ALL      SUPPLEMENTAL_LOG_DATA_MIN
------------------------------ ------------------------------ ------------------------------
YES                            YES                            YES

以上的查詢結果表示開啟了所有型別的補充日誌。

由此實驗我們可以得出，Oracle資料庫補充日誌分為：最小化補充日誌，主鍵補充日誌，外來鍵補充日誌，唯一鍵補充日誌，ALL型別的補充日誌。

執行ALTER DATABASE SQL語句開啟資料庫補充日誌後，告警日誌會顯示補充日誌開啟的情況：
SUPLOG: Commencing to rollback failed DDL at scn = 13732025618576
SUPLOG:  minimal = ON, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF

    參考文章：
http://www.askmaclean.com/archives/oracle-supplemental-%E8%A1%A5%E5%85%A8%E6%97%A5%E5%BF%97%E4%BB%8B%E7%BB%8D.html

另外，還需要說明一點的是如果對非常繁忙的資料庫啟動補充日誌可能會帶來效能問題和出現Hang住的情況，下面通過一篇metalink文章來說明該問題：

Alter Database Add Supplemental Log Data Hangs (文件 ID 406498.1)
修改時間:2013-2-6     型別:PROBLEM

In this Document

Symptoms
Changes
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to 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.


CHANGES


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, OracleDatabase 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;

   在啟動資料庫補充日誌的時候，會使得所有快取的遊標變得無效，突如其來的大量硬解析將對資料庫的效能帶來影響。啟動資料庫補充日誌將等待當前所有的事務結束，使得該操作之後記錄的所有Redo資料都有補充日誌的屬性，如果資料庫有長事務或事務過於頻繁將導致該操作出現Hang住的情況，由此可見啟動資料庫的補充日誌應儘量避開資料庫高峰期。

    相關文章：《Oracle GoldenGate – Supplemental Logging》：http://www.juliandyke.com/Blog/?p=301

--end--


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23135684/viewspace-1122398/，如需轉載，請註明出處，否則將追究法律責任。

相關文章