orcal歸檔數據庫存了哪些內容？

orcal歸檔數據庫存下載的數據日志包括靜態和動態的數據，要訪問這些數據，就必須要進行手動訪問。

生產線歷史數據歸檔是數據庫運維的一項日常基本工作。在建表設計時，通常都將數據流水表(如：日志、用戶登錄歷史，軟件下載記錄，用戶屬性更改歷史表等)設計為范圍分區表、間隔分區表(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, 'yyyymmddhh24miss') || ']'

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，否則就.....).