[生產(chǎn)庫實戰(zhàn)] 采用存儲過程對生產(chǎn)線歷史表數(shù)據(jù)進(jìn)行歸檔

發(fā)布時間：2020-07-13 07:07:13 來源：網(wǎng)絡(luò) 閱讀：1649 作者：s_o_m 欄目：關(guān)系型數(shù)據(jù)庫

生產(chǎn)線歷史數(shù)據(jù)歸檔是數(shù)據(jù)庫運維的一項日?；竟ぷ鳌Ｔ诮ū碓O(shè)計時，通常都將數(shù)據(jù)流水表（如：日志、用戶登錄歷史，軟件下載記錄，用戶屬性更改歷史表等）設(shè)計為范圍分區(qū)表、間隔分區(qū)表（11G），當(dāng)超過業(yè)務(wù)要求的保留范圍時，此數(shù)據(jù)基本是靜態(tài)數(shù)據(jù)且應(yīng)用程序再不對其進(jìn)行訪問，但有可能會由于某些特殊要求需要手動查詢。在這個情況下，都將其數(shù)據(jù)從生產(chǎn)庫歸檔至歷史庫，并對其進(jìn)行壓縮保存，如果超出特殊要求查詢的范圍，那就直接將其導(dǎo)出壓縮備份到磁帶。

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

1、簡單，不容易出錯；

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

3、數(shù)據(jù)從源庫到目標(biāo)庫不需要落地，和dblink+impdp遷移數(shù)據(jù)的方法類似，節(jié)約導(dǎo)出數(shù)據(jù)所需空間（上百GB的表）及歸檔時間；

4、可監(jiān)控其歸檔進(jìn)度及歸檔記錄數(shù)；

5、如果是跨機(jī)房傳輸，請監(jiān)控好網(wǎng)絡(luò)帶寬流量。

6、......

操作流程：

1、在生產(chǎn)庫維護(hù)用戶的Schema下創(chuàng)建一張視圖，視圖中包含需要歸檔的表的分區(qū)：

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

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

2、在歷史庫放歸檔數(shù)據(jù)的用戶下（歷史庫操作都下面都在此用戶下操作）創(chuàng)建數(shù)據(jù)歸檔狀態(tài)表及序列

-- 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：打開、解析游標(biāo)，2：提取數(shù)據(jù)，3：某個表同步完成，4：所有表全部完成，其他負(fù)數(shù)：錯誤編碼';

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

3、在歷史庫創(chuàng)建一個可以通過只讀權(quán)限連接生產(chǎn)庫的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、歷史庫創(chuàng)建一張與生產(chǎn)庫相同表結(jié)構(gòu)的表，表名建議改為帶上歸檔數(shù)據(jù)標(biāo)識

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

5、創(chuàng)建用于數(shù)據(jù)歸檔的存儲過程：

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;
--定義數(shù)組，存放待同步的視圖名稱。
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
--初始化數(shù)組（第1步中創(chuàng)建的視圖）
v_remoteview_list(1) := 'zhanghui.log_table_p201209';
--循環(huán)同步每個分區(qū)表
for k in 1 .. v_remoteview_list.count loop
    --添加一個同步任務(wù)記錄
    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;
    --記錄打開、解析游標(biāo)的時間長度。
    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;
    --對打開的游標(biāo)，進(jìn)行循環(huán)同步。
    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);
      --記錄當(dāng)前同步的記錄數(shù)
      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;
    --更新進(jìn)度表,將當(dāng)前分區(qū)完成時間記錄到備注中。
    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;
    --更新進(jìn)度表
    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、創(chuàng)建壓縮對象存儲過程，由于move操作需要接近雙倍的存儲空間，所以壓縮前請?zhí)崆霸u估空間需求

create procedure p_compress_object(vObject_name    varchar2, --對象
vPartition_name varchar2 default null, --分區(qū)名
vParallel       int default 0, --并行度
vPctfree        int default 0, --存儲參數(shù)pctfree 不再考慮DML操作的設(shè)置為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、上述工作準(zhǔn)備完成，確認(rèn)歷史庫表空間情況，調(diào)用數(shù)據(jù)歸檔存儲過程 p_log_table_p201209 ，處理完成后對數(shù)據(jù)進(jìn)行壓縮，調(diào)用存儲過程 p_compress_object（....）；

8、確認(rèn)數(shù)據(jù)無誤，drop掉生產(chǎn)庫維護(hù)用戶對應(yīng)的視圖及業(yè)務(wù)表的分區(qū)，釋放對象占用空間（注意：檢查分區(qū)表的索引是否為local，否則就.....）.

以上......完！

向AI問一下細(xì)節(jié)

[生產(chǎn)庫實戰(zhàn)] 采用存儲過程對生產(chǎn)線歷史表數(shù)據(jù)進(jìn)行歸檔

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽