8.1 Oracle Redo Log

Oracle Redo Log 簡單來說就是用來記錄交易資訊的 Transaction Log，用途在於紀錄每一筆異動資料的紀錄，藉由這些交易紀錄，在進行資料庫還原時就可以將資料回復到之前最新的狀態。Redo Log 的內容會記錄每一筆交易的 Transaction ID、DML與DDL的SQL Statement，以及 Checkpoint/SCN 的資訊，在進行資料庫還原時，需要藉由 Redo Log 所記錄的SCN來恢復每個階段的交易，資料庫在 Apply 完所有 Redo Log 的交易資訊後，所有 Data File 的SCN便會一致，達成資料還原以及資料的完整與一致性。

在 Oracle 資料庫當中，Redo Log 是以 Group 的方式存在，每一個 Redo Log Group 可以有一個或多個 Redo Log File，在同一個 Group 裡面的 Redo Log File 稱為 Redo Log File Member，且每一個 Member 的內容都是相同的，建立多個 Member 的目的是可以互相作為備份，避免當有 Redo Log File 損壞而造成交易資訊的遺失。Redo Log Group 是以循環的方式來使用:

例如資料庫目前有四個 Redo Log Group，當 Redo Log Group 1 的 Redo Log File 被寫滿時，便會切換至下一個 Redo Log Group 2 繼續寫交易資訊，Group 2 被寫滿時再切換至 Group 3，同理 Group 3 寫滿後切換至 Group 4，而當 Group 4 被寫滿之後，便會把 Group 1 的內容清空然後循環回 Group 1 開始再寫交易資訊，如此不斷的循環。那麼把 Group 1 的內容清空，交易資訊不是會不見嗎 ? 為了避免這個問題，在 Redo Log 寫滿的時候會把所有的內容複製一份出來，而這些被複製出來的內容就稱作 Archive Log，如此就可以避免因為 Redo Log Group 的循環而造成交易資訊的遺失。

當交易發生時，並不會直接寫入到Redo Log，而是會先暫存在Log Buffer，然後再透過LGWR(Log Writer) 這個 Background Process寫入Redo Log:

當發生以下的現象時，便會觸發LGWR將資料寫入Redo Log:

• 交易被 commit 時。

• Log Buffer 以使用 1/3 時。

• 發生 checkpoint 時。

由以上可知實際上交易資訊存放在 Log Buffer 時間並不長，因此Log Buffer實際上不需要設定太大，一般使用預設的32M就已經足夠。

除了等待 Redo Log寫滿之後切換，我們也可以透過 “alter system switch logfile” 這個命令來手動強制切換一個Redo Log Group。透過 v$log 與 v$logfile 可以知道目前所有Redo Log Group 的狀態:

SQL> select a.thread#,a.group#,a.sequence#,b.member,a.bytes/1024/1024 mb,a.status         from v$log a,v$logfile b      where a.group#=b.group# order by 1,2

Redo Log Group 會有四種狀態，ACTIVE、INACTIVE、CURRENT與UNUSED。CURRENT表示目前交易正在寫入這個 Group 的 Redo Log File；ACTIVE表示這個 Group 正在等待 Checkpoint；INACTIVE表示目前為閒置狀態，可以被使用；UNUSED表示這是一個新的 Group，裡面的 Redo Log File還從未被使用。

由 Redo Log Group的狀態欄可以得知，必須為 INACTIVE狀態的Group才可以重複再被使用，如果交易量太大導致Redo Log Group 切換速度過快，這時候可能造成所有的Group都為ACTIVE狀態，此時前端的交易就會產生等待的情形，直到有Group變為INACTIVE時，前端交易才有辦法再進行下去，這個時候在資料庫的 alert log 當中可能就會看到 “checkpoint not complete” 字樣，表示Redo Log 切換的速度趕不上交易產生的速度，這個時候就必須要增加 Redo Log Group，提供多一點的Group來切換，或者是增加Redo Log File的大小，減少切換的頻率來避免大交易量的問題。

Redo Log Group 或是Redo Log File Member可以在線上進行新增或刪除的動作，只要是狀態為 INACTIVE的Group就可以進行刪除，使用 alter database命令進行操作:

SQL> alter database add logfile group 4 '/oradata/ORA11/redo04.log' size 200M;      (新增 group 4 且大小為 200M) SQL> alter database add logfile member '/oradata/ORA11/redo04b.log' to group 4;      (為 group 4 添加一個 Redo Log File) SQL> alter database drop logfile group 5;      (刪除 Redo Log Group 5) SQL> alter database drop logfile member '/oradata/ORA11/redo05b.log';      (刪除這一個 Redo Log File)

Redo Log File 的大小在建立後就無法更改，如果有變更 Redo Log 大小的需求，必須將他刪除後再重建才行。早期資料庫預設的Redo Log Size為50M，這大多時候是不足的，而現在版本的資料庫已經把預設值改為200M了。

Oracle 資料庫預設是 No archive log Mode，也就是說在預設的狀態下，Redo Log 的交易資訊會被覆蓋掉，當資料庫發生問題時便無法由備份還原到最新的狀態，只能還原到備份當下的時間點，而中間的交易資料便遺失了，所以一般來說都會將資料庫設定為 archive log Mode，以確保資料能夠被完全的保護，我們可以簡單的在 sqlplus 裡面使用 archive log list 命令來檢查目前設定是否為 archive log Mode:

在設定 archive log Mode 之前必須先設定兩個參數 log_archive_dest_1 與 log_archive_format，log_archive_dest_1 設定 archive log 存放的位置，可以指定一個或多個位置，log_archive_dest_n 可以從 1 ~ 31，最多可以設定 32 個 archive log 位置；log_archive_format 則是設定產出的 archive log 的格式(檔名)，比較要注意的是，log_archive_format設定上必須包含 %s、%t與 %r 三個參數，%s表示交易的 sequence number、%t表示instance的thread number，%r則表示為 resetlogs number。

將log_archive_dest_1 設定為 use_db_recovery_file_dest 表示將 archive log 存放在 recovery area 裡面，如果是這個設定，那麼就還要先設定兩個參數db_recovery_file_dest與db_recovery_file_dest_size來指定 recovery area 的位置與大小，log_archive_dest_1 也可以設定在任何一個指定的目錄，例如設定將 archive log 存放至 /u01/app/arch 底下:

SQL> alter system set log_archive_dest_1='location=/u01/app/arch';

設定完參數之後，便可以將資料庫設定為 archive log Mode，在 mount 模式下使用 alter database archivelog 指令便可以設定為 archive log Mode；如果要設定為 No archive log Mode，則是使用 alter database noarchivelog 指令:

設定完 archive log Mode之後，可以馬上使用 “alter system switch logfile”，手動切換一個 Redo Log Group來檢查 archive log 是否有產生在指定的位置，驗證設定是否正確。

7.5 Oracle Unified Audit

Unified Audit 是 Oracle 12c 開始有的新功能，整合了 Standard Audit 與 Fine-Grained Audit 到單一個 UNIFIED_AUDIT_TRAIL 中，具有下列的優點:

1. Unified Audit 產生的 audit trail 會先暫存在SGA 中，再經由GEN0這個 Background Process 寫入 AUDSYS 這個使用者的 Table 中，處理 audit trail 的效能上比起傳統的 Audit 較好。透過執行 SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL 可以手動強制將SGA中的 audit trail 寫入 Table 當中。

2. SYS.AUD$ 與 SYS.FGA_LOG$ 存在一定的風險，只要具有資料庫高級權限的使用者便可以來竄改這兩個 Audit 的紀錄，而 Unified Audit 是存放在 AUDSYS 這個使用者的 Table 當中，AUDSYS 底下的 Table 都是 Read-Only 屬性，可以保護 audit trail 不被竄改。

3. Unified Audit 可以針對 DB 的一些維運操作進行稽核，例如 Data Pump、RMAN…等作業都可以進行 Audit。

自 Oracle 12c 開始，資料庫的稽核有兩種模式可以使用:

Mixed Auditing Mode: 為預設模式，表示可以使用 Standard Audit、Fine-Grained Audit 或者是 Unified Audit。

Unified Auditing Mode: 開啟純 Unified Audit 模式，Standard Audit 與 Fine-Grained 在此模式下不可使用。需透過下列步驟轉換為純 Unified Auditing Mode:

SQL> shutdown immediate SQL> exit; $ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk uniaud_on ioracle $ sqlplus / as sysdba SQL> startup

在轉換為 Unified Auditing Mode 之後，audit_ 相關的資料庫參數便會消失。

我們可以透過以下查詢來確認資料庫是使用 Mixed Auditing Mode 或是 Unified Auditing Mode:

SQL> select parameter, value from v$option where parameter like '%Unified%';

如果 value 是 FALSE 表示為 Mixed Auditing Mode；如果 value 為 TRUE 表示為 Unified Auditing Mode。

使用 Unified Audit 的方式首先必須要先建立 Audit Policy，然後再將此 Policy Enable 來使用它，如果要取消的話可以將 Policy Drop 或是 Disable。Audit Policy 的建立與維護不一定要使用 SYS 使用者來操作，只要資料庫的使用者具有 AUDIT_ADMIN 的權限即可；而要讀取UNIFIED_AUDIT_TRAIL 的話則是需要 AUDIT_VIEWER 的權限。

Unified Audit 可以建立的 Audit Policy 非常多樣化，例如針對select any table權限與 DBA 角色進行 Audit:

SQL> create audit policy audit_any_table privileges select any table; SQL> create audit policy audit_dba roles dba;

對於使用者的操作也可以針對不同行為進行 Audit:

SQL> create audit policy audit_alter_table actions alter table;     (對 alter table 指令進行 audit) SQL> create audit policy audit_emp actions update on hr.emp;     (對 update hr.emp 這個 Table 時進行 audit) SQL> create audit policy audit_exp actions component=datapump export;     (對 data pump 的 export 進行 audit)

另外也可以使用條件式來設計更細微的 Audit Policy:

SQL> create audit policy audit_scott actions update on hr.emp, delete on hr.emp, when 'sys_context (''userenv'', ''session_user'')=''scott''' evaluate per session;    (當使用者 scott 對 hr.emp 進行 update, delete 時進行 audit)    (evaluate per session 表示只針對此 session audit 一次，     若是 evaluate per statement 表示每下一次語法就進行 audit 次)

這邊比較特殊的是 RMAN 的操作不需要建立 Audit Policy，系統會自動Audit RMAN 的作業，但這個僅限於 Unified Auditing Mode 才會發生。

在建立完 Audit Policy 之後，接下來就是要來啟用這些 Audit Policy，透過 AUDIT 指令可以用來啟用 Audit Policy，同樣的也可以針對不同條件來啟用:

SQL> audit policy audit_any_table;    (啟用 audit_any_table 這個 policy，針對資料庫所有使用者生效) SQL> audit policy audit_alter_table by scott, hr;    (啟用 audit_alter_table 這個 policy，只有對 scott 與 hr 兩個使用者生效) SQL> audit policy audit_emp except hr;    (啟用 audit_emp 這個 policy，除了 hr 這個使用者外都生效) SQL> audit policy audit_alter_table by scott whenever successful;    (whenever successful，只有在指令成功時進行 audit 紀錄) SQL> audit policy audit_alter_table by scott whenever not successful;    (whenever not successful，只有在指令不成功時進行 audit 紀錄)

如果要 Disable Audit Policy 則使用 NOAUDIT 指令。

我們可以透過 AUDIT_UNIFIED_POLICIES 來查詢目前所有的 Unified Audit Policy；透過 AUDIT_UNIFIED_ENABLED_POLICIES 來查詢目前被 Enabled 的 Unified Audit Policy。Oracle 12c 不論是 Mixed 或是 Unified Audit Mode，預設都會有三個 Unified Audit Policy，ORA_ACCOUNT_MGMT、ORA_DATABASE_PARAMETER 與 ORA_SECURECONFIG:

ORA_ACCOUNT_MGMT 針對使用者的帳號與權限做稽核、ORA_DATABASE_PARAMETER 針對資料庫參數的異動做稽核，ORA_SECURECONFIG 針對資料庫安全性相關的操作進行稽核，例如 drop any table、alter any table …等操作。這三個 Policy 只有 ORA_SECURECONFIG 預設是被 Enabled 的:

對於 Unified Audit Trail 的維護必須透過 DBMS_AUDIT_MGMT 進行操作，例如建立一個 Auto Purge 的 Job 來定時清理 Unified Audit Trail:

SQL> begin dbms_audit_mgmt.create_purge_job  (audit_trail_type => dbms_audit_mgmt.audit_trail_unified, audit_trail_purge_interval => 24,  audit_trail_purge_name => 'audit_trail_purge_job',  use_last_arch_timestamp => true); end; /

audit_trail_purge_interval 單位為 hour，上述範例表示每24小時執行清理 Unified Audit Trail 的 Job；use_last_arch_timestamp 表示刪除 Last Archive Time 之前的資料，在此必須先定義 Last Archive Time ，例如定義 Last Archive Time 為 30 天前的資料，透過DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP 來設定:

SQL> begin dbms_audit_mgmt.set_last_archive_timestamp (audit_trail_type => dbms_audit_mgmt.audit_trail_unified,  last_archive_time => sysdate – 30); end; /

若是不用排程 JOB 的話也可以透過DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL手動進行清理:

SQL> begin dbms_audit_mgmt.clean_audit_trail (audit_trail_type  => dbms_audit_mgmt.audit_trail_unified, use_last_arch_timestamp => true); end; /

由於 Unified Audit Trail 預設是先寫入 SGA 再 Flush 至 Table 中，若是資料庫發生問題而造成非預期的crash ，此時有可能會遺失存放在 SGA 且尚未 Flush 的 Unified Audit Trail，此時我們可以透過 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY 來改變 Unified Audit Trail 存放的行為，讓他直接寫入 Table 而不暫存在 SGA:

SQL> begin  dbms_audit_mgmt.set_audit_trail_property (dbms_audit_mgmt.audit_trail_unified, dbms_audit_mgmt.audit_trail_write_mode, -   dbms_audit_mgmt.audit_trail_immediate_write); end; /

dbms_audit_mgmt.audit_trail_immediate_write 表示立即寫入 Table，預設為dbms_audit_mgmt.audit_trail_queued_write 表示會先寫入 SGA 再 Flush 至 Table。

7.4 資料庫稽核

Oracle 資料庫稽核的方式可分為 Value-Base Audit、Standard Database Audit、Fine-Grained Audit(FGA) 與 Unified Audit(12c新功能)。

• Value-Base Audit:

Value-Base Audit 主要是以 Trigger 的方式來做稽核，藉由 Trigger 可以用來記錄 Table 所做的 DML 異動，或者是透過 Database Trigger 來記錄使用者登入登出的紀錄，例如員工資訊表 emp 這個 Table 上的 salary 薪資欄位是一個敏感性資料，我們可以建立一個 audit_emp 的 Table 並使用 Trigger 將 salary 異動前後的資料存入 audit_emp:

SQL> create table audit_emp  (aud_user varchar2(20),b_salary number,f_salary number,modify_date date); SQL> create or replace trigger t_audit_emp before update on emp for each row Begin         IF :old.salary != :new.salary THEN          insert into audit_emp values  (sys_context('USERENV', 'SESSION_USER'),:old.salary,:new.salary,sysdate);         END IF; End t_audit_emp; /

以下是建立 Database Trigger 來記錄使用者登入的時間:

SQL> create table audit_logon (audit_user varchar2(20),logon_date date); SQL> create or replace trigger t_audit_logon after logon on database Begin          insert into audit_logon values  (sys_context('USERENV', 'SESSION_USER'),sysdate); End t_audit_logon; /

• Standard Database Audit:

Standard Database Audit 指的是透過設定資料庫參數 audit_trail 並且使用 audit 指令來進行稽核。audit_trail 參數是用來設定 audit 紀錄所存放的位置，可設定以下選項:

⠂NONE: 表示不產生 audit trail，意即不使用 audit。

⠂DB: audit 紀錄存放在 DB 的 sys.aud$ 這個 table 裡面。

⠂DB, EXTENDED: 同 DB 選項，EXTENDED 表示多紀錄 SQL Statament。

⠂XML: audit 紀錄以 XML 格式存放在作業系統上，使用參數audit_file_dest 指定位置，預設是$ORACLE_BASE/admin/<ORACLE_SID>/adump 目錄。

⠂XML, EXTENDED: 同 XML 選項，EXTENDED 表示多紀錄 SQL Statament。

⠂OS: audit 紀錄放在作業系統上，使用參數audit_file_dest 指定位置，預設是$ORACLE_BASE/admin/<ORACLE_SID>/adump 目錄。

audit_trail 是靜態參數，設定完畢後必須重啟 DB 讓參數生效，例如:

SQL> alter system set audit_trail=DB,EXTENDED scope=spfile; SQL> shutdown immediate; SQL> startup

設定完 audit_trail 之後便可以使用 audit 指令來進行稽核，語法為 ”audit <陳述式或物件> by <session或access> by <owner> whenever <not> successful”；by session 表示此登入 session 所有操作只稽核一次，by access 表示每次觸發到稽核都進行紀錄；by <owner> 表示針對某一位使用者進行稽核，不指定的話表示所有使用者都稽核；whenever successful 表示只稽核成功的操作，whenever not successful 表示只稽核失敗的操作，不使用 whenever 選項的話表示兩者都稽核。例如:

SQL> audit select table by scott by access;    (稽核 scott 這個使用者做 select 的動作) SQL> audit create any trigger by hr by access;    (稽核 hr 這個使用者做 create trigger 的動作) SQL> audit update,delete on hr.emp by session;    (稽核 hr.emp 這個 table 所有 update 與 delete 的動作)

若是 audit_trail 設定為 DB 的話，所有 audit 紀錄可以從 sys.aud$ 查到，除此之外可以透過 dba_audit_trail 這個 view 來查詢，例如我們要稽核登入失敗的紀錄可以設定如下:

SQL> audit create session by access whenever not successful;

當有人登入失敗時便可以從 dba_audit_trail 得到 audit 紀錄:

Standard Audit 預設會稽核sys這個使用者的登入登出紀錄，並且將 audit trail 存放在 audit_file_dest 所設定的位置，若是要稽核 sys 的其他行為，必須設定參數 audit_sys_operations 為 TRUE。

DBA 可以透過 DBA_STMT_AUDIT_OPTS、DBA_OBJ_AUDIT_OPTS，DBA_PRIV_AUDIT_OPTS 來查詢做了哪些 Standard audit 設定。

• Fine-Grained Audit:

Fine-Grained Audit 是從 Oracle 9i 開始有的功能，但必須是企業版才可使用。Fine-Grained Audit 是 Standard Audit 的進階版，可以針對 Table 的某個欄位或者是某些敏感性資料來做更細項的稽核，使用的是 dbms_fga 這個 Package 來設定 audit policy，而audit trail 則是存放在 sys.fga_log$ 這個 Table 當中。例如 hr.emp 這個 Table 裡面的 department_id=80 這部分資料較為敏感，我們就可以建立一個 Fine-Grained Audit Policy 來稽核，只要有人存取 department_id=80 的時候就進行 audit，如果是存取 hr.emp 的其他資料則不會 audit:

SQL> begin dbms_fga.add_policy (   object_schema    =>     'hr',   object_name        =>     'emp',   policy_name        =>     'audit_emp',   audit_condition=>    'department_id=80',   audit_column    =>     'salary',   statement_types=>    'select' ); end; /

Policy 建立完畢之後必須將其 enable:

SQL> begin dbms_fga.enable_policy (   object_schema    =>     'hr',   object_name        =>     'emp',   policy_name        =>     'audit_emp'); end; /

同樣是查詢 hr.emp ，department_id=10 不會被稽核而 department_id=80則會被稽核:

不需要的 Policy 可以透過 dbms_fga.disable_policy 將其disable 或是 dbms_fga.drop_policy 將其刪除。

Fine-Grained Audit 的紀錄可以從 sys.fga_log$ 或者是 dba_fga_audit_trail 來查詢，DBA 可以透過 dba_audit_policies 來檢視所有 Fine-Grained Audit 的 Policy 設定。

由於 sys.aud$ 與 sys.fga_log$ 預設都是放在 SYSTEM 這個 Tablespace 中，隨著 audit 量的增長使得此 Table 容量越來越大，進而增加 SYSTEM Tablespace 的使用率，建議幫 audit 建立專屬的 Tablespace 並且把 sys.aud$ 與 sys.fga_log$ 移動到此專屬的 Tablespace 當中，在 Oracle 11.2 之後提供了 dbms_audit_mgmt 這個 Package 來方便我們搬移，例如將 sys.aud$ 與 sys.fga_log$ 搬移到 AUDIT_TBS 這個 Tablespace:

SQL> BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 🡨 搬移 AUD$ audit_trail_location_value => 'AUDIT_TBS'); END; / SQL> BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,  🡨 搬移 FGA_LOG$ audit_trail_location_value => 'AUDIT_TBS'); END; /