oracle佇列（AQ)---實現orale到mysql的資料同步

賀子_DBA時代發表於2017-11-15
Oracle佇列MySql
高階佇列（Advanced Queue，簡稱AQ）: 
高階佇列是oracle的一種高階應用,它主要是表和觸發器之間的組合而成的一種應用。其主要作用是在各應用系統中進行訊息傳遞。 
利用高階佇列來實現訊息在兩個不同資料庫之間的非同步傳輸，滿足業務系統的改造需求。 
我們利用觸發器+高階序列 然後加ruby讀取佇列的主鍵，然後再在對應表中查出資料，insert 進mysql，這是idc機房oracle到idc的mysql的過程;
至於idc機房到阿里雲的mysql，處於安全考慮，ruby不能直接連線rds,藉助了mq, 先放放到mq，然後從mq讀取放進rds. 實現oracle到mysql的同步。需要注意oracle高階序列是可以讓多個
Oracle 高階佇列具體開發步驟如下：
（1）首先確定應用的需求，是否適合使用高階佇列？使用高階佇列預計提高效能的預期值
（2）賦予資料庫賬戶相應aq許可權。
（3）確定佇列包體結構，即建立type。
（4）建立佇列表及佇列。
（5）佇列管理
一：我們的佇列結構（type)：
因為我們oracle中資訊表有兩個：t_publish_info 和t_publish_zbxx，所以會比會員表t_member_info 的type多一個欄位：table_name 用來區分是從那個表讀取資料，其中opr也是是標識位，1表示insert, 2表示update ,3表示delete
CREATE OR REPLACE TYPE INFOSERVICE."INFO_SYNC_TYPE2" as object
(
table_name number(3),
opr number(2) ,
record_id number(20)
);
二：佇列表：INFOSERVICE.T_INFO_SYNC_MESSAGE
begin
sys.dbms_aqadm.create_queue_table(
queue_table => 'INFOSERVICE.T_INFO_SYNC_MESSAGE',
queue_payload_type => 'INFOSERVICE.INFO_SYNC_TYPE2',
sort_list => 'ENQ_TIME',
compatible => '10.0.0',
primary_instance => 0,
secondary_instance => 0,
storage_clause => 'tablespace INFOSERVICE pctfree 10 initrans 1 maxtrans 255 storage ( initial 16M next 16M minextents 1 maxextents unlimited )');
end;
/
三：建立佇列：
begin
  sys.dbms_aqadm.create_queue
 (
  queue_name     => 'infoservice.q_info_sync_message',
  queue_table    => 'INFOSERVICE.T_INFO_SYNC_MESSAGE',                 
  queue_type     => sys.dbms_aqadm.normal_queue,
  max_retries    => 3,                              
  retry_delay    => 1,                              
  retention_time => 0                                
 );
end;
啟動佇列：
begin
  sys.dbms_aqadm.start_queue
  (queue_name => 'infoservice.q_info_sync_message',enqueue => true ,dequeue => true );
end;
暫停佇列：
begin
  sys.dbms_aqadm.stop_queue ( queue_name => '佇列名');
end;
刪除佇列：
begin
  sys.dbms_aqadm.drop_queue ( queue_name => '佇列名');
end;
刪除佇列表：
begin
  sys.dbms_aqadm.drop_queue_table (queue_table   =>  '佇列表名');
end;
四：入隊儲存過程DBMS_AQ.enqueue：
create or replace procedure infoservice.info_sync_enqueue( table_name in number, opr in number,record_id in number)
as
begin
DECLARE
queue_options DBMS_AQ.enqueue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_id RAW(16);
my_message info_sync_type2;
BEGIN
my_message := info_sync_type2( table_name, opr,record_id );
DBMS_AQ.enqueue(queue_name => 'infoservice.q_info_sync_message',
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
COMMIT;
END;
end info_sync_enqueue;
出對儲存過程DBMS_AQ.DEQUEUE：
create or replace procedure infoservice.info_sync_dequeue(table_name out number,opr out number ,record_id out number)
as
begin
DECLARE
queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(200);
my_message info_sync_type2;
BEGIN
DBMS_AQ.DEQUEUE(
queue_name => 'infoservice.q_info_sync_message',
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id );
COMMIT;
table_name := my_message.table_name;
opr :=my_message. opr;
record_id := my_message.record_id ;
END;
end info_sync_dequeue;
五：我們這裡用的觸發器實現自動入隊，當在T_PUBLISH_INFO表上做增刪改的時候，觸發入隊，透過標識欄位 opr的值 來區分： 1表示insert, 2表示update ,3表示delete：
create or replace trigger INFOSERVICE.TRG_PUBLISH_2016A_Q
before insert or delete or update of table_name,table_name2,cust_id, title,publish_date,OK_STATUS,OK_DATE,UP_DATE,IN_DATE,FILE_NAME on T_PUBLISH_INFO
for each row
declare
queue_options DBMS_AQ.enqueue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_id RAW(16);
my_message info_sync_type2;
opr number(2);
table_name number(3);
begin
opr := 0;
table_name := 22;###就把標識位置為22,
CASE
WHEN inserting THEN
if (:NEW.OK_STATUS = 'Y') then
opr := 1;
my_message := info_sync_type2( table_name, opr,:new.record_id );
end if;
WHEN updating THEN
if ((:NEW.OK_STATUS != :OLD.OK_STATUS)or (:NEW.OK_STATUS = 'Y' )) then
opr := 2;
my_message := info_sync_type2( table_name, opr,:new.record_id );
end if;
WHEN deleting THEN
opr := 3;
my_message := info_sync_type2( table_name, opr,:old.record_id );
END CASE;
if ( opr != 0) then
DBMS_AQ.enqueue(queue_name => 'infoservice.q_info_sync_message',
enqueue_options => queue_options,
message_properties =>message_properties,
payload => my_message,
msgid => message_id);
end if;
end;
這裡的table_name:=22,就是說這個觸發器入隊的時候，就把標識位table_name設定成了22，方便接下來ruby在讀取的時候，判斷從那個表讀取資料，
call infoservice.info_sync_enqueue(21,1,39097403);
call infoservice.info_sync_enqueue(155,1,39097403); ---這樣就讀t_publish_info_20142015
執行入隊的儲存過程，就會把type（INFO_SYNC_TYPE2）對應的三個欄位的值存進佇列表中，如下：需要注意的是佇列表的欄位是固定的。
SQL> desc INFOSERVICE.T_INFO_SYNC_MESSAGE;
Name Null? Type
----------------------------------------- -------- ----------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
CORRID VARCHAR2(128)
PRIORITY NUMBER
STATE NUMBER
DELAY TIMESTAMP(6)
EXPIRATION NUMBER
TIME_MANAGER_INFO TIMESTAMP(6)
LOCAL_ORDER_NO NUMBER
CHAIN_NO NUMBER
CSCN NUMBER
DSCN NUMBER
ENQ_TIME TIMESTAMP(6)
ENQ_UID VARCHAR2(30)
ENQ_TID VARCHAR2(30)
DEQ_TIME TIMESTAMP(6)
DEQ_UID VARCHAR2(30)
DEQ_TID VARCHAR2(30)
RETRY_COUNT NUMBER
EXCEPTION_QSCHEMA VARCHAR2(30)
EXCEPTION_QUEUE VARCHAR2(30)
STEP_NO NUMBER
RECIPIENT_KEY NUMBER
DEQUEUE_MSGID RAW(16)
SENDER_NAME VARCHAR2(30)
SENDER_ADDRESS VARCHAR2(1024)
SENDER_PROTOCOL NUMBER
USER_DATA INFOSERVICE.INFO_SYNC_TYPE2
USER_PROP ANYDATA
檢視現在佇列表中的資料，USER_DATA.table_name ,USER_DATA.opr ,USER_DATA.record_id 這三個是之前type中定義的欄位。
select * from INFOSERVICE.T_INFO_SYNC_MESSAGE;

總結：我們這個案例中高階佇列中實際上儲存的最重要的資訊是那個主鍵id,也就是record_id,然後ruby連線上oracle和mysql。透過主鍵id在oracle對應的表中查出資料（透過table_name 的值來判斷是從哪個表找資料），透過識別符號opr的值來判斷是增刪改操作。ruby讀取oracle資料到記憶體中，然後insert 到mysql。實現了可靠的非同步同步。

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

相關文章