Schema的復原快照

Schema的復原快照

多天前，我們的一個客戶不小心在網站上做了一個＂刪除＂操作．但他發現他無法確認被他刪除的對象是否真的應該被刪除．於是求助於我們，希望能看到“刪除”操作之前的介面。

我們首先想到的是藉助於閃回查詢（Flashback Query）。但是，他的這個簡單的刪除操作實際上在後台資料庫當中刪除了十多張表的相關資料。而且用於在頁面上顯示這些對象的代碼的邏輯也相當複雜。因此，閃回查詢無助於他的要求。我們最終利用expdp的flashback_time參數，將這個schema的資料匯出，再匯入一個測試環境完成他的請求。

我當時想到，如果Oracle在會話層級提供一個參數flashback_scn/flashback_time控制這個會話的所有查詢都閃回到某一個時間點，那這個問題就很容易解決：只要建立一個新的串連，當連上資料庫後就修改該參數，就可以查詢到該時間點的快照了。

於是我又想，能否找到一個方法，類比實現出一個schema的閃回快照呢。最終，我找到一個不完善的方法：建立一個新的schema，在該schema當中，為每個源schema的表建立一個視圖，在試圖中引入閃回查詢。並且引入一個“全域變數”來控制視圖的閃回時間/scn。

以下就是代碼

-- ################################################################################ 

-- # 

-- #         $Id: schema_snapshot.sql 

-- # 

-- #        File: $RCSfile: schema_snapshot.sql,v $ 

-- # Description: create a snapshot for a schema 

-- #       Usage: sqlplus -s /nolog @schema_snapshot <existing_schema_name> <snapshot_schema_name> 

-- #     Created: 07/02/2014 

-- #      Author: Wei Huang 

-- # User run as: / as sysdba (OS user should be oracle owner) 

-- #  Parameters: 1: existing schema name 

-- #  Parameters: 2: new schema name 

-- #  Parameters: 3: snapshot timestamp 

-- # 

-- #  Copyright (c) 2014 Wei Huang 

-- # 

-- # History 

-- # Modified by   When      Why 

-- # -----------   -------   ---------------------------------------------------- 

-- ################################################################################ 

 

prompt Usage: @schema_snapshot <existing_schema_name> <snapshot_schema_name> 

prompt Description: create a snapshot for a schema 

prompt   

 

declare 

  sql_str varchar2(4000); 

  c number; 

begin 

  select count(1) into c from dba_users where username = upper('&2'); 

  if c = 0 then 

    execute immediate 'create user &2 identified by &2'; 

    execute immediate 'grant connect,resource to &2'; 

    sql_str := q'[ 

CREATE OR REPLACE PACKAGE &2.var_pkg IS 

  var varchar2(255); 

  PROCEDURE set_var(val varchar2); 

  function get_var return varchar2; 

END var_pkg ; 

/ 

 

CREATE OR REPLACE PACKAGE BODY &2.var_pkg IS 

  PROCEDURE set_var(val varchar2) IS 

  BEGIN 

    var := val; 

  end set_var; 

 

  function get_var return varchar2  

  IS 

  BEGIN 

    return var; 

  END get_var; 

END var_pkg; 

/ 

]'; 

 

    execute immediate sql_str; 

    for q in (select 'grant select,flashback on '||owner||'.'||table_name||' to '||upper('&2')||';' str from dba_tables where owner=upper('&1')) loop 

      execute immediate q.str; 

    end loop; 

    for q in (select 'create or replace view '||upper('&2')||'.V_'||table_name||' as select * from '||owner||'.'||table_name||'  as of timestamp to_timestamp(var_pkg.get_var,''yyyymmddhh24miss'');' from dba_tables where owner=upper('&1') loop 

      execute immediate q.str; 

    end loop; 

    for q in (select 'create or replace synonym '||upper('&2')||'.'||table_name||' for '||upper('&2')||'.V_'||table_name||';' from dba_tables where owner=upper('&1') loop 

      execute immediate q.str; 

    end loop; 

 

    &2.var_pkg.set_var('&3'); 

  end if; 

end; 

/ 

這段代碼將會產生用於建立閃回快照schema的中對象的代碼。串連該schema的用戶端將會讀取到源schema的某個時間點的快照資料。當然，如果源schema中還有預存程序、視圖等其他plsql代碼的話，還要在該schema當中重新建立。