Shulou(Shulou.com)06/01 Report--

Advanced queue (Advanced Queue, AQ):

Advanced queue is an advanced application of oracle, which is mainly an application formed by the combination of tables and triggers. Its main function is to transmit messages in various application systems.

The advanced queue is used to realize the asynchronous transmission of messages between two different databases to meet the transformation needs of the business system.

We use the trigger + advanced sequence and then add ruby to read the primary key of the queue, and then find out the data in the corresponding table, insert into mysql, which is the process from oracle in idc computer room to mysql in idc; as for the mysql from idc computer room to Aliyun, for security reasons, ruby cannot be directly connected to rds, with the help of mq, it is first put into mq, and then read from mq into rds. Achieve synchronization from oracle to mysql. It should be noted that the oracle advanced sequence allows multiple Oracle advanced queues to develop as follows: (1) first determine the requirements of the application, whether it is suitable to use advanced queues? Expected values for expected performance improvements using advanced queues

(2) give the database account the corresponding aq permission.

(3) determine the queue packet structure, that is, create a type.

(4) create queue lists and queues.

(5) queue management

One: our queue structure (type):

Because we have two information tables in oracle: t_publish_info and t_publish_zbxx, we have one more field than the type in the member table t_member_info: table_name is used to distinguish between reading data from that table, where opr is also the identification bit, 1 for insert, 2 for update, 3 for delete CREATE OR REPLACE TYPE INFOSERVICE. "INFO_SYNC_TYPE2" as object (table_name number (3), opr number (2), record_id number (20)) Second, team list: 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 255storage (initial 16m next 16M minextents 1 maxextents unlimited)'); end / 3: create queue: 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; launch queue: begin sys.dbms_aqadm.start_queue (queue_name = > 'infoservice.q_info_sync_message',enqueue = > true, dequeue = > true); end

Pause queue: begin sys.dbms_aqadm.stop_queue (queue_name = > 'queue name'); end; delete queue: begin sys.dbms_aqadm.drop_queue (queue_name = > 'queue name'); end; delete queue table: begin sys.dbms_aqadm.drop_queue_table (queue_table = > 'queue table name'); end Four: 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; pair stored procedure 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; 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 Five: the trigger we use here can automatically enroll in the queue, which will trigger enrollment when adding or deleting changes are made on the Tunable PUBLISHINFOTable. Distinguish by the value of the identification field opr: 1 for insert, 2 for update, 3 for 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; 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 The table_name:=22 here, that is, when the trigger joins the queue, the identification bit table_name is set to 22, so that when the ruby reads, it is convenient for ruby to judge to read data from that table, call infoservice.info_sync_enqueue (21meme 39097403); call infoservice.info_sync_enqueue (155pint 1pint 39097403) -in this way, the stored procedure for t_publish_info_20142015 enrollment will store the values of the three fields corresponding to type (INFO_SYNC_TYPE2) in the queue list, as follows: it should be noted that the fields of the queue list are fixed. 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 View the data in the current queue table USER_DATA.table_name, USER_DATA.opr, and USER_DATA.record_id are the fields defined in the previous type. Select * from INFOSERVICE.T_INFO_SYNC_MESSAGE

Summary: the most important information actually stored in the high-level queue in our case is the primary key id, which is record_id, and then ruby connects to oracle and mysql. Through the primary key id to find the data in the table corresponding to oracle (which table to find the data from by the value of table_name), and the value of the identifier opr to determine whether it is an add, delete or change operation. Ruby reads oracle data into memory, and then insert to mysql. Reliable asynchronous synchronization is realized.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.