Oracle中的補充日誌(supplemental log)

標籤：supplemental log   dataguard   補充日誌   外鍵、主鍵   

在配置DG的時候，有涉及到補充日誌的知識，故做以整理。

     補充日誌不是獨立的一種日誌，是對重做記錄中變更向量的補充資訊，增加了變更向量記載的記錄量，Oracle資料庫某些功能要求啟用補充日誌才能正常地或更好的工作，如logminer、DG、閃回事務查詢、閃回事務。

Oracle中insert、delete命令在預設情況下產生的重做記錄足以表明被修改的行的每個欄位在被修改前後的值是什麼，insert，變更前行不存在，產生的重做記錄會記載新行的每個欄位的值，delete，變更後不存在，但是由於重做記錄也要負責產生撤銷資料區塊的變更，所以行被刪除之前的各欄位的值也記錄在撤銷資料區塊的變更向量中，也就是insert和delete命令的重做記錄中，能找到整行的所有資訊。

但是update不同於insert和delete一定會涉及一行的所有欄位，常常只是更改一個或幾個欄位，處於對效能的考慮，Oracle沒必要把修改的欄位的值也儲存在變更向量中，事實上是在撤銷塊的比昂向量中只記載被修改的欄位的更改前的值，而在資料區塊的變更向量中則記載了被修改的欄位的新值，同一行中其他沒有被修改的欄位，不會記載其修改前的值，因為沒有被修改。

如：update scott.emp set sal=12000 where empno=7369 ;

為了支援執行個體恢複和介質恢複，其重做記錄只需要記載sal欄位的值為12000即可，在常規的重做記錄中完全不會出現empno為7369的資訊

Insert、delete、update的常規重做記錄具體記載何種資訊是以保證執行個體恢複與介質恢複能夠正常進行為標準的，不相關的資訊不會記錄在重做日誌中。正是因為有撤銷資料區塊的變更向量，所以保證執行個體恢複與介質恢複也簡介地保證了復原操作可以被執行。

如果啟用了補充日誌，資料區塊的變更向量的記載可以變為; sal欄位的值為12000,並且再額外的記錄該行被修改之前第一個欄位empno的值是7369以及sal為800(修改之前的值)，更多的資訊意味著update命令被等價還原的可能性提高了，資料的恢複性提高

補充日誌主要就是為update服務，補充的目的就是要高度還原真實的update命令。

補充日誌可以在資料庫層級和表層級上啟用

supplemental log可以在資料庫層級啟動，在資料庫層級啟動minimal logging會記錄操作的足夠資訊，通常邏輯standby資料庫建立minimal logging已經足夠使用。資料庫supplemental log補充日誌還能針對所有欄位、主鍵欄位、唯一欄位和外鍵欄位建立。也可以設定表層級設定supplemental log，稱為identification key logging，不同類型的supplemental log會產生不同大小的日誌記錄。

針對邏輯standby資料庫記錄的日誌資訊有以下規則：

如果一個表有主鍵被定義，那麼主鍵被記錄作為update語句的一部分來標識修改的行

如果主鍵不存在，最短的非空約束、唯一索引被記錄作為update語句的一部分來標識修改的行在主鍵和非空約束/索引都沒有的情況下，所有的列被記錄，作為update語句的一部分來標識修改的行，也就是除了long,lob,longg raw，物件類型和集合類型的欄位，所有欄位都將被記錄一個函數索引，即使它被定義為唯一的，也不能用於唯一標識修改的行，只要修改的行能被唯一標識，邏輯standby資料庫支援函數索引表的同步

推薦主要資料庫儘可能給表添加主鍵或者非空唯一索引，確保sql apply能有效應用redo資料來更新邏輯standby資料庫。

尋找資料庫中沒有唯一邏輯標識的表

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‘;

添加禁用主鍵rely的約束

如果能確保應用在表中的行為是唯一的，在表上建立禁用主鍵rely約束，這能避免維護

主要資料庫主鍵的開銷在主要資料庫表上使用alter table語句的relay disable子句建立禁用主鍵rely約束，表test有兩列ID、name，以下sql在test表建立禁用rely約束

alter table test add primary key (id,name) rely disable ;

當明確指定了rely約束，系統將嘉定行能通過指定欄位標識唯一性

為了提高sql apply的效能，在表的欄位上添加唯一的約束/索引，確保邏輯standby資料庫維護的表都能唯一標識行，如果沒添加唯一的約束/索引，當通過sql apply在表上執行update或者delete語句期間會全表掃描，會降低效能。

查看當前資料庫中補充日誌狀態：

SQL> select * from v$version ;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE11.2.0.3.0ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> 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

可以看到，補充日誌的類型有：最小補充日誌、標識關鍵字段補充日誌兩大類

1.最小補充日誌

最小補充日誌是最基本的一種資料庫級補充日誌，

啟用最小補充日誌

SQL> alter database add supplemental log data ;Database altered.SQL> select supplemental_log_data_min min from v$database ;MIN--------YES

關閉最小補充日誌

SQL> alter database drop supplemental log data ;Database altered.SQL> select supplemental_log_data_min min from v$database ;MIN--------NO

需要注意的是，凡是啟用或者關閉資料庫級補充日誌(包括最小補充日誌和另外幾種日誌)都會導致共用池中所有SQL命令遊標非法，也就是短期之內應解析會顯著上升。

2.標識關鍵字段補充日誌

  標識關鍵字段補充日誌有四種：主鍵、唯一索引、外鍵、全體欄位補充日誌

2.1主鍵補充日誌

主鍵補充日誌的作用是在update命令的重做記錄中添加被修改行的主鍵欄位的舊值，這是無條件式的補充日誌，所謂無條件即無論主鍵欄位本身是否被update命令修改，其舊值都會被記錄。但是，不能保證每張表一定有主鍵。如果存在沒有主鍵的表，則主鍵欄位由長度最小的非空唯一索引欄位代替。如果表結構中一個非空索引欄位都沒有，那麼oracle將：將被修改行的所有欄位(除了lob和long類型)的舊值都記錄下來，這將導致重做記錄的資料量暴漲，所以如果要啟用主鍵補充日誌，又為了維護lgwr和重做日誌，每張表最好具有主鍵或至少一個非空唯一欄位。

啟用主鍵補充日誌：

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

標識關鍵字段補充日誌(包括主鍵補充日誌)必須建立在最小補充日誌的基礎上，當其被啟用時，若最小補充日誌尚未啟用，則oracle會隱式開啟最小補充日誌 狀態為IMPLICIT

同樣在沒有關閉標識關鍵字段補充日誌的時候，不能先關閉最小補充日誌

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  NOSQL> 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唯一索引補充日誌

只有當唯一欄位被update時，才會記錄該欄位被修改前的值，因為唯一鍵索引是能夠建立在多個欄位上的

如：

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

update 命令現在更新first_name，常規日誌會只記載first_name欄位的舊值，而唯一鍵索引將會記錄first_name和last_name，所以這種補充日誌完全可以說是為複合欄位唯一索引服務的

2.3外鍵補充日誌

外鍵補充日誌和唯一索引補充日誌一樣同為有條件式的，只有當外鍵欄位被update命令

修改時，其修改前的舊值才會被記錄。所以這種補充日誌完全是為複合欄位外鍵服務的

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全體欄位補充日誌

全體欄位補充日誌和主鍵補充日誌一樣為無條件式的，無論哪個欄位被update命令修改，所有欄位(除了lob，long類型)的舊值都將被記錄，其效果相當於啟用了主鍵補充日誌的前提下既沒有主鍵也沒有非空唯一索引欄位的情況，這樣幾乎所有的表資料都搬到了重做日誌中，不但存在當前的，曆史資料也沒有丟下。對恢複操作來說比較好，但是對於lgwr和磁碟空間就不是太好，一般很少啟用這樣的日誌

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

資料庫級的補充日誌，共同點是啟用和關閉導致所有SQL遊標非法

無論需要啟用哪一種標識關鍵字段補充日誌，都需要啟用最小補充日誌，4種標識關鍵字段的效果可以累加，不衝突。

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  NOSQL> 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

表級補充日誌

只是針對某個表，沒有必要在整個資料庫範圍啟用補充日誌功能，在啟用表級補充日誌之前，應該先啟用資料庫級最小補充日誌

表級補充日誌分為主鍵、唯一索引、外鍵、全欄位和自訂5種類型，前四種和資料級對應的類型特點一致，只是在特定的表上啟用。

如：

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 ;

自訂欄位是表級補充日誌特有的功能，使用者可以任意指定那些欄位的舊值需要被補充記錄。

alter table hr.employees add supplemental log group emp_info (first_name,last_name,email) ;

以上將first_name,last_name,email欄位指派為一個名為empinfo的日誌組，意思就是只要update命令修改了這3個欄位中的任意一個，重做記錄必須記載全部3個欄位的舊值這稱為有條件式的，無條件式的要加關鍵字always

alter table hr.employees add supplemental log group emp_info (first_name,last_name,email) always ;

表級補充日誌的情況可以查詢dba_log_groups和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;

特定表上的表級補充日誌的啟用與關閉會導致所有引用該表的SQL遊標非法，會引起一段時間的硬分析增加。

補充日誌主要為update服務，額外記錄指定欄位的舊值，使得有能力分析重做日誌的工具可以高度還原update命令，由於額外記錄了欄位的舊值，也能將其看成一種特殊的備份。

本文出自 “相守姑娘說” 部落格，請務必保留此出處http://sugarlovecxq.blog.51cto.com/6707742/1693092

Oracle中的補充日誌(supplemental log)