ORACLE DB 的學習者們

2017年9月18日 星期一

ORACLE FLASHBACK

flashback 處理user errors。datafile損壞等錯誤則是由一般的備份與還原程序處理。

flashback 只可用在 table drop，不含 table truncation

flashback drop, flashback query, flashback transaction, flashback table 不須額外的設定

flashback database: 有異動的block images ，定期由database buffer cache，寫入到 flashback buffer(SGA內)

Configuring Flashback Database

  • 確認處於 Archive Log Mode
    • select log_mode from v$database;
  • 設定 FLASH Recovery Area 
    •             alter system set db_recovery_file_dest = '/flash_recovery' ;
            alter system set db_recovery_file_dest_size = 8G;
  • 設定 flashback retention target (單位: 分鐘)
    • alter system set db_flashback_retention_target = 240;
  • 關閉後再啟動 
    •            shutdown immediate;
           startup mount;
  • 啟動 flashback logging
    • alter database flashback on;
  • 打開資料庫
    • alter database open open;
  • 如何確認FLASHBACK已經啟動
    • select flashback_on from v$database;

Flashback usage statistics

設定完成後，有以下動態VIEW可觀察

v$flashback_database_log

v$flashback_database_stat

v$sgastat

select * from v$sgastat where name = 'flashback generation buff';

觀察 SGA 中，FLASHBACK 的記憶體使用狀態

Flashback enable

下圖顯示啟動 FLASHBACK 的過程

啟動後再觀察上述動態 VIEW ， 可發現上述動態VIEW不再毫無資料

Database recovery point-in-time using Flashback

以下建立一個TABLE，並記錄現在時間，然後將表格刪除，再使用 Database Control 進行復原
以下使用 RMAN 進行復原

在復原前，資料庫必須處於 MOUNT 狀態，然後執行復原 

RMAN> shutdown immediate; 
RMAN> startup mount;
RMAN> run{ flashback database to time "to_date('2017-09-18 01:20:22', 'YYYY-MM-DD HH24:MI:SS')"; }
RMAN> alter database open resetlogs;
執行畫面如下

執行完畢後，再重新查詢，可再找到該表格

Exclude tablespaces from Flashback Logging

啟動 flashback 後，會將 block 的異動寫入到 flashback logs，因此 logs 的成長會影響資料庫的效能，可將部分 TABLESPACE ，取消其 flashback logging

查詢 TABLESPACES 的 FLASHBACK 啟動狀態可用下列指令

select name, flashback_on from v$tablespace;

取消 FLACKBACK

alter tablespace flashback off;

開啟 FLASHBACK

alter tablespace flashback on;

因為 FLASHBACK 是透過 CONTROLFILE 啟動，而非資料字典，因此其相關資訊需透過動態效能視表(dynamic performance view)查詢，如 v$tablespace，而非資料字典 (DBA_TABLESPACES)。

Flashback DROP

Flashback Drop 用於 table recovery，透過 flashback drop，被刪除(不包含 truncate)的表格，可復原和無法復原的物件如下

  • 可復原物件： table, index, grant, triggers, grants, unique-key, primary-key, not-null constraints。
  • 無法復原物件：foreign constraints
從版本 10g 以後，drop table命令，會將原有 table，以重新命名的方式，從資料字典移開

DROP 以後，物件會被放置到 Recyclebin，可用 USER_RECYCLEBIN 或是 DBA_RECYCLEBIN 來查詢

舉例來說，以下刪除某個表格

刪除以後查詢回收桶

select OBJECT_NAME, ORIGINAL_NAME, OPERATION, TYPE, CAN_UNDROP, CAN_PURGE from user_recyclebin;

查詢後透過 FLASHBACK 復原 

SQL> flashback table emp_082209 to before drop;

Flashback complete.
上述命令亦可改為

SQL> flashback table emp_082209 to before drop rename to new_name;

也可以透過ENTERPRISE MANAGER 的DATABASE CONTROL 來復原，但必須以 SYSDBA 身分登入，選擇以下路徑

使用狀態 ==> 管理 ==> 執行復原 ==> 下拉選單選擇(表格) ==> 倒溯刪除的表格 ==> 綱要名稱 (輸入 HR ) ==> 執行

Flashback Query

Flashback Query最常使用在使用者不慎刪除某個表格內的某筆紀錄 (不是整個表格)，透過它確認已經刪除的資料。

常用語法如下： 

--查詢某個時間戳記，某表格的內容
SQL> select * from hr.emp_082209 as of timestamp to_timestamp('18-09-17 01:22:00','dd-mm-yy hh24:mi:ss') ;
--將某個表格，回逤到某個時間戳記
SQL> flashback table emp to timestamp to_timestamp('18-09-17 01:22:00','dd-mm-yy hh24:mi:ss') ;
--一次回朔兩個表格到某個 SCN ，同時啟用 TRIGGERS
SQL> flashback table emp, dept to scn 65544 enable triggers ;
前述回逤時間戳記，有一個先決條件，必須開啟 ROW MOVEMENT。語法如下：

SQL> ALTER TABLE XXX ENABLE ROW MOVEMENT ;

假設欲刪除某表格最後一筆資料如下

先記錄時間戳記，將表格開放 ROW MOVEMENT，然後將該筆資料刪除 

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-09-18 20:28:10

SQL> alter table hr.emp_082209 enable row movement;

Table altered.

SQL> delete from hr.emp_082209 where last_name='Jeng';

1 row deleted.

SQL> commit;

Commit complete.
刪除以後該筆資料消失

以 FLASHBACK TABLE 命令復原該表格至前述時間戳記(或之前) 

SQL> flashback table hr.emp_082209 to timestamp to_timestamp('18-09-17 20:28:00','dd-mm-yy hh24:mi:ss') ;                                   
Flashback complete.
復原以後再查詢，可以看到原先被刪除的最後一筆資料又出現了。

Flashback Versions Query

Flashback Versions Query 可讓使用者查詢某表格中，一筆紀錄的數個committed versions。

這些資訊被包含在表格的數個虛擬欄位當中 (ROW ID也是虛擬欄位)，虛擬欄位有：

  • VERSIONS_STARTSCN
  • VERSIONS_STARTTIME
  • VERSIONS_ENDSCN
  • VERSIONS_ENDTIME
  • VERSIONS_XID
  • VERSIONS_OPERATION
為了實驗，我們在短時間內對某一筆的SALARY進行數次更新，然後我們進行 FLASHBACK VERSIONS QUERY

欲查詢上述虛擬欄位，需在查詢時，在SQL中使用關鍵字 VERSIONS BETWEEN ，例如， 

select employee_id, first_name, salary, VERSIONS_XID, VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_OPERATION 
from emp_082209
versions between scn minvalue and maxvalue
where last_name='Jeng';
  • versions between scn minvalue and maxvalue
  • versions between timestamp (SYSTIMESTAMP- 1/24) and SYSTIMESTAMP
  • versions between timestamp to_timestamp('19-09-17 08:10:00','dd-mm-yy hh24:mi:ss') and SYSTIMESTAMP
VERSIONS_OPERATION 包含下列：(I) INSERT (U) UPDATE (D) DELETE

Flashback and Undo Data

Flashback 能復原的資料，端視存放在 UNDO DATA 的歷史資料而異。此區域的資料會被新的資料所覆蓋。UNDO DATA資料保留的時間長度，由參數 undo_retention 決定。 下圖顯示保留時間為900秒，也就是15分鐘。
如果該區域過小導致舊的資料被覆蓋，將會發生 ORA-1555 snapped too old 的錯誤訊息。 要改善此種狀況可透過設定TABLESPACE的參數 RETENTION GUARANTEE 來改善，或是透過下列的 FLASHBACK DATA ARCHIVE 來紀錄更多歷史資料。

FLASHBACK DATA ARCHIVE

FLASHBACK DATA ARCHIVE 使用額外的 TABLESPACE 來記錄歷史資料，其進行步驟如下

  • Create tablespace
  • Create flashback archive
  • Create user & schema that using the flashback data archive
  • Grant flashback archive on to user
  • Alter table flashback archive
範例如下 : 
create tablespace fda datafile '/oracle/app/oracle/oradata/sedb/fda1.dbf' size 10m;

create flashback archive fla1 tablespace fda retention 1 month;

CREATE USER fbdauser
  IDENTIFIED BY xxxx
  DEFAULT TABLESPACE fda
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  
  GRANT CONNECT TO fbdauser;
  GRANT RESOURCE TO fbdauser;
  ALTER USER fbdauser DEFAULT ROLE ALL;
  -- 2 System Privileges for PROCHUB 
 
  
  grant dba to fbdauser identified by xxxx;

grant flashback archive administer to fbdauser;
  
  grant flashback archive on fla1 to hr;
  alter table hr.emp_082209 flashback archive fla1;
建立後，可透過下列 VIEW 來觀察使用狀況
  • dba_flashback_archive
  • dba_flashback_archive_ts
  • dba_flashback_archive_tables
例如:
張貼者： 於