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
參考文章：
%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住的情況，由此可見啟動資料庫的補充日誌應儘量避開資料庫高峰期。
--end--
文章出處：http://blog.itpub.net/23135684/viewspace-1122398/
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
參考文章：
%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住的情況，由此可見啟動資料庫的補充日誌應儘量避開資料庫高峰期。
--end--
文章出處：http://blog.itpub.net/23135684/viewspace-1122398/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9606200/viewspace-1435664/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 補充日誌Oracle
- Oracle 補充日誌(supplemental log)Oracle
- Java日誌記錄的5條規則Java
- oracle9i補充日誌Oracle
- php日誌,記錄日誌PHP
- oracle日誌操作記錄Oracle
- 關於oracle補充日誌作用的理解Oracle
- 日誌記錄器
- 『學了就忘』Linux日誌管理 — 93、日誌輪替補充Linux
- 關於Oracle LOGMNR找不到dml操作及補充日誌相關Oracle
- Laravel sql 日誌記錄LaravelSQL
- secureCRT記錄操作日誌Securecrt
- 記錄日誌檔案
- PHP日誌記錄方法PHP
- 日誌記錄 PHP下往linux目錄下寫日誌PHPLinux
- 【Oracle日誌】- 日誌檔案重建Oracle
- 記錄一則clear重做日誌檔案的案例
- wazuh日誌審計--定製規則
- Oracle dbca的日誌目錄Oracle
- 記錄騰訊雲使用日誌
- 記錄 | 實習日誌 9
- mysql開啟日誌記錄MySql
- 記錄 sql 查詢日誌SQL
- SecureCRT自動記錄日誌Securecrt
- Appfuse：記錄操作日誌APP
- storm 問題日誌記錄ORM
- 第11章 日誌記錄
- 日誌規約
- 為oracle新增重做日誌組及重做日誌成員Oracle
- Oracle日誌Oracle
- 操作日誌記錄(包括輸出至自定義日誌檔案)
- Laravel 指定日誌檔案記錄任意日誌Laravel
- ORACLE 11G RAC 增加日誌組及增大日誌檔案Oracle
- oracle 修改日誌大小及增加日誌成員Oracle
- monolog 日誌記錄器解析Mono
- iptables防火牆如何記錄日誌防火牆
- 使用Redis記錄系統日誌Redis
- log4net日誌記錄