[生產庫實戰] 采用存儲過程對生產線歷史表數據進行歸檔

發布時間：2020-07-13 07:07:13 來源：網絡閱讀：1658 作者：s_o_m 欄目：關系型數據庫

生產線歷史數據歸檔是數據庫運維的一項日常基本工作。在建表設計時，通常都將數據流水表（如：日志、用戶登錄歷史，軟件下載記錄，用戶屬性更改歷史表等）設計為范圍分區表、間隔分區表（11G），當超過業務要求的保留范圍時，此數據基本是靜態數據且應用程序再不對其進行訪問，但有可能會由于某些特殊要求需要手動查詢。在這個情況下，都將其數據從生產庫歸檔至歷史庫，并對其進行壓縮保存，如果超出特殊要求查詢的范圍，那就直接將其導出壓縮備份到磁帶。

在Oracle數據庫中，用做表數據歸檔操作的方法很多，如：exp/imp、expdp/impdp、傳輸表空間等方法，這些都是日常數據庫維護可能使用到的，這些工具的使用方法這里不展開了，下面進入今天的主題，使用存儲過程歸檔生產線歷史表數據，先對其簡單做下總結：

1、簡單，不容易出錯；

2、對其源庫（這里就是生產庫）來說，就是一條select查詢語句，性能影響小；

3、數據從源庫到目標庫不需要落地，和dblink+impdp遷移數據的方法類似，節約導出數據所需空間（上百GB的表）及歸檔時間；

4、可監控其歸檔進度及歸檔記錄數；

5、如果是跨機房傳輸，請監控好網絡帶寬流量。

6、......

操作流程：

1、在生產庫維護用戶的Schema下創建一張視圖，視圖中包含需要歸檔的表的分區：

create view log_table_p201209
as
select * from user01.log_table partition(P201209);

注：為什么要建視圖？因為通過dblink查詢不能用 select * from table partition (partition_name).....這樣的語句。

2、在歷史庫放歸檔數據的用戶下（歷史庫操作都下面都在此用戶下操作）創建數據歸檔狀態表及序列

-- Create table
create table data_archive_status
(
id   NUMBER,
threadno   NUMBER,
table_name VARCHAR2(60),
syncnum    NUMBER,
state      NUMBER,
starttime DATE,
synctime   DATE,
remark     VARCHAR2(2000)
);
-- Add comments to the columns
comment on column data_archive_progress.state
is '0:開始，1：打開、解析游標，2：提取數據，3：某個表同步完成，4：所有表全部完成，其他負數：錯誤編碼';

-- Create sequence
create sequence seq_id
minvalue 1
maxvalue 9999999999999
start with 1
increment by 1
cache 20;

3、在歷史庫創建一個可以通過只讀權限連接生產庫的dblink，示例:

-- Create database link
create database link XXDB.LOCALDOMAIN
connect to readonly
identified by ""
using '(DESCRIPTION=
    (LOAD_BALANCE=no)
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=172.16.XX.XX)
        (PORT=1521)
      )
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=172.16.XX.XX)
        (PORT=1521)
      )
    )
    (CONNECT_DATA=
      (FAILOVER_MODE=
        (TYPE=select)
        (METHOD=basic)
        (RETRIES=180)
        (DELAY=5)
      )
      (SERVER=dedicated)
      (SERVICE_NAME=XX_service)
    )
)';

4、歷史庫創建一張與生產庫相同表結構的表，表名建議改為帶上歸檔數據標識

create tabel log_table_p201209（......）;

5、創建用于數據歸檔的存儲過程：

create procedure p_log_table_p201209 as
--索引表
type u_type is table of log_table_p201209%rowtype index by pls_integer;
v_list u_type;
--定義數組，存放待同步的視圖名稱。
type varchar_arrary is table of varchar2(60) index by pls_integer;
v_remoteview_list varchar_arrary;
--定義一個引用索引
type cur_ref is ref cursor;
cur_data cur_ref;
--本地變量，記錄SQL%ROWCOUNT
v_counter number := 0;
v_rowid    rowid;
v_sqlcode varchar2(300) := null;
v_querystr varchar(1000) := null;
v_parse_elapsed_s date := null;
v_parse_elapsed_e date := null;
v_fetch_elapsed_s date := null;
v_fetch_elapsed_e date := null;
begin
--初始化數組（第1步中創建的視圖）
v_remoteview_list(1) := 'zhanghui.log_table_p201209';
--循環同步每個分區表
for k in 1 .. v_remoteview_list.count loop
    --添加一個同步任務記錄
    insert into data_archive_status
    values
      (seq_id.nextval,
       k,
       v_remoteview_list(k),
       0,
       0,
       sysdate,
       sysdate,
       null)
    returning rowid into v_rowid;
    commit;
    v_querystr := 'select /*+ rowid(t) */ * from ' || v_remoteview_list(k) ||
                  '@XXDB.LOCALDOMAIN t';
    update data_archive_status t
       set t.synctime = sysdate, t.state = 1
     where rowid = v_rowid;
    commit;
    --記錄打開、解析游標的時間長度。
    v_parse_elapsed_s := sysdate;
    open cur_data for v_querystr;
    v_parse_elapsed_e := sysdate;
    update data_archive_status
       set synctime = sysdate,
           state    = 2,
           remark   = remark || '[' || v_remoteview_list(k) ||
                      ':parse_elapsed=' ||
                      (v_parse_elapsed_e - v_parse_elapsed_s) || 'sec,'
     where rowid = v_rowid;
    commit;
    v_counter         := 0;
    v_fetch_elapsed_s := sysdate;
    --對打開的游標，進行循環同步。
    loop
      --使用Bulk Binding，一次處理10000條記錄
      fetch cur_data bulk collect
        into v_list limit 10000;
      forall i in 1 .. v_list.last
        insert into log_table_p201209
        values v_list
          (i);
      --記錄當前同步的記錄數
      v_counter := v_counter + sql%rowcount;
      update data_archive_status t
         set t.syncnum = v_counter, t.synctime = sysdate
       where rowid = v_rowid;
      commit;
      exit when cur_data%notfound;
    end loop;
    v_fetch_elapsed_e := sysdate;
    --更新進度表,將當前分區完成時間記錄到備注中。
    update data_archive_status
       set state    = 3,
           synctime = sysdate,
           remark   = remark || 'fetch_elapsed=' ||
                      round((v_fetch_elapsed_e - v_fetch_elapsed_s) * 24 * 60,
                            4) || 'min,syncnum=' || v_counter ||
                      ',endtime= ' || to_char(sysdate, 'yyyymmddhh34miss') || ']'
     where rowid = v_rowid;
    commit;
    close cur_data;
    --更新進度表
    update data_archive_status t set t.state = 4 where rowid = v_rowid;
    commit;
end loop;
exception
when others then
    v_sqlcode := sqlcode;
    update data_archive_status
       set synctime = sysdate, state = v_sqlcode
     where rowid = v_rowid;
    commit;
    raise;
end;

6、創建壓縮對象存儲過程，由于move操作需要接近雙倍的存儲空間，所以壓縮前請提前評估空間需求

create procedure p_compress_object(vObject_name    varchar2, --對象
vPartition_name varchar2 default null, --分區名
vParallel       int default 0, --并行度
vPctfree        int default 0, --存儲參數pctfree 不再考慮DML操作的設置為0
vTablespace     varchar2 default null, --表空間
vOwner          varchar2 default user, --對象擁有者
vType           number --類型:0、table 1、index 2、 partition table 3、index partition
                                              ) Authid Current_User is
vSql     varchar2(4000);
vSqlerrm varchar2(256);
v_sqlstring varchar2(4000);
begin
v_sqlstring := 'alter session set db_file_multiblock_read_count=128';
    execute immediate v_sqlstring;
if vType = 0 then
    begin
      vSql := 'alter table ' || vOwner || '.' || vObject_name || ' move ' || case when vTablespace is null then null else 'tablespace ' || vTablespace end || ' pctfree ' || vPctfree || ' compress nologging ' || case when vParallel in (0, 1) then null else 'parallel ' || vParallel end;
      execute immediate vSql;
    end;
elsif vType = 1 then
    begin
      vSql := 'alter index ' || vOwner || '.' || vObject_name ||
              ' rebuild ' || case when vTablespace is null then null else 'tablespace ' || vTablespace end || ' pctfree ' || vPctfree || ' compress nologging ' || case when vParallel in (0, 1) then null else 'parallel ' || vParallel end;
      execute immediate vSql;
    end;
elsif vType = 2 then
    begin
      vSql := 'alter table ' || vOwner || '.' || vObject_name ||
              ' move partition ' || vPartition_name || case when vTablespace is null then null else ' tablespace ' || vTablespace end || ' pctfree ' || vPctfree || ' compress nologging ' || case when vParallel in (0, 1) then null else 'parallel ' || vParallel end;
      execute immediate vSql;
    end;
elsif vType = 3 then
    begin
      vSql := 'alter index ' || vOwner || '.' || vObject_name ||
              ' rebuild partition ' || vPartition_name || case when vTablespace is null then null else ' tablespace ' || vTablespace end || ' pctfree ' || vPctfree || ' compress nologging ' || case when vParallel in (0, 1) then null else 'parallel ' || vParallel end;
      execute immediate vSql;
    end;
end if;
exception
when others then
    vSqlerrm := sqlerrm;
    dbms_output.put_line(vSqlerrm||'|'||vSql);
end;

7、上述工作準備完成，確認歷史庫表空間情況，調用數據歸檔存儲過程 p_log_table_p201209 ，處理完成后對數據進行壓縮，調用存儲過程 p_compress_object（....）；

8、確認數據無誤，drop掉生產庫維護用戶對應的視圖及業務表的分區，釋放對象占用空間（注意：檢查分區表的索引是否為local，否則就.....）.

以上......完！

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

[生產庫實戰] 采用存儲過程對生產線歷史表數據進行歸檔

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

[生產庫實戰] 采用存儲過程對生產線歷史表數據進行歸檔

猜你喜歡

最新資訊

相關推薦

相關標簽