通過oracle 資料庫連結db link自動化備份生產庫的指令碼儲存過程

create or replace procedure proc_auto_backup
/************************************************************************************
編寫者:翟勳楊
編寫日期:2013-2-25
功能:定期備份庫至專用備份庫,且僅備份表結構及其它物件;
     備份前判斷,若未備份且備份已變更,則備份;否則不備份;
     若備份超過一定期限,則自動刪除所屬使用者

要點:1,構建一個表,用於儲存源端所有的物件名稱,儲存過程會掃描它們,判斷是否已備份此物件;
     2,判斷備份的表是否發生了變化,即:新增列,列變更,刪除列;
                                     user_tab_columns
                                     新增列:多一條記錄
                                     列變更:
                                   
     3,在源資料庫建立 日誌表:t_obj_log
     4,在目標資料庫建立自源資料庫的db_link
    
     5,再用dbms_scheduler呼叫此儲存過程即可
    
     6,明天要實現的:以變數式配置一個期限,過此期限則刪除此使用者或刪除使用者下對應的物件所有備份,以減少空間佔用
    
     7,實現其它物件,諸如儲存過程,道理一樣
*************************************************************************************/
AUTHID CURRENT_USER is
v_cnt pls_integer;--
cursor cur_source is select  dl.object_name,
                             dl.created,
                             dl.last_ddl_time as source_ddl_time
                            
                     from user_objects@db_link1 dl
                    
                     where dl.object_type='TABLE' and
                           dl.object_name!='T_OBJ_LOG';
v_tname user_objects.object_name%type;
v_created user_objects.created%type;
v_last_ddl_time user_objects.last_ddl_time%type;
v_target_ddl_time user_objects.last_ddl_time%type;

v_sql varchar2(4000);
v_creation varchar2(100);
v_last varchar2(100);

begin
--在目標端的備份庫查詢是否已備份此表
--通過資料庫連結查指定表是否
open  cur_source;
loop
fetch cur_source into v_tname,v_created,v_last_ddl_time;
exit when cur_source%notfound;

--若日誌表無匹配才插入記錄,否則不插入資料
select count(object_name) into v_cnt from t_obj_log@db_link1 where object_name=v_tname;
if v_cnt=0 then
insert into t_obj_log@db_link1(OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME,CREATED) values(v_tname,'table',v_last_ddl_time,v_created);
commit;
end if;

select count(1) into v_cnt from tab where tname=v_tname;

if v_cnt>0 then
 --已備份過此表
 ----進一步判斷備份表是否變化過
 
   --v_target_ddl_time如為null,則首次複製
   select tar into v_target_ddl_time from  t_obj_log@db_link1 where object_name=v_tname;
   if v_target_ddl_time is null then
     
      update t_obj_log@db_link1 set tar=v_target_ddl_time  where object_name=v_tname;
      commit;
      execute immediate 'create table '||v_tname||' as select * from '||v_tname||'@db_link1 where 1=2 ';
      commit;
   end if;
   select tar into v_target_ddl_time from t_obj_log@db_link1 where object_name=v_tname;
   if v_last_ddl_time<=v_target_ddl_time then
      --說明表未變化
      null;
   else
      --說明表結構變化了
      --備份此表
      --create table t_test as select * from t_test@db_link1 where 1=2;
     
    
      v_creation:=to_char(v_created,'yyyy-mm-dd hh24:mi:ss');
      v_last:=to_char(v_last_ddl_time,'yyyymmddhh24miss');
     
     
      --判斷若已重新命名則不進行操作
      v_cnt:=0;
     
      --必須新增'''',不然報無效識別符號
      ---自第三次變化,即執行第三次時,會報錯,因為目標庫已經存在t_test和t_test_日期  2個表,此時下述程式碼僅處理t_test_日期,未處理t_test
      v_sql:='select count(1)  from user_objects where object_name='||''''||v_tname||v_last||'''';
      execute immediate v_sql into v_cnt;
      if v_cnt=0 then
      execute immediate 'alter table '||v_tname||' rename to '||v_tname||v_last;
      end if;
     
      --故先判斷是否存在t_test,若存在,先重新命名,再建立它
      select count(1) into v_cnt  from user_objects where object_name=v_tname;
      if v_cnt=0 then
      execute immediate 'create table '||v_tname||' as select * from '||v_tname||'@db_link1 where 1=2 ';     
      dbms_output.put_line(v_sql);
      commit;
      end if;
   end if;
else
     execute immediate 'create table '||v_tname||' as select * from '||v_tname||'@db_link1 where 1=2 ';
     commit;
     select last_ddl_time into v_target_ddl_time from user_objects where object_name=v_tname;
     commit;
    
     update t_obj_log@db_link1 set tar=v_target_ddl_time  where object_name=v_tname;
     commit;
end if;
end loop;
close cur_source;
end;

小結:1,中間日誌表,用於user_objects與此表的對應列查行比較,以判斷物件是否

             變化

         2,構建自目標庫到源庫的db_link

         3,基於db link的分散式事務,在儲存過程程式碼中要顯式編寫commit;

         4,使用cursor遊標遍歷整個源庫的物件,和目標庫進行比對.再進行下一步的動作

         5,先編寫大處理邏輯,再細分每人大處理邏輯為子處理邏輯.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-754717/，如需轉載，請註明出處，否則將追究法律責任。