【ORACLE】查询通知（CQN）配置基础

慢工也能出细活

于 2022-09-20 17:31:53 发布

阅读量2k

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/bendywu/article/details/126929567

版权

介绍

连续查询通知 (CQN)允许应用程序向数据库注册查询以获取对象更改通知（默认）或查询结果更改通知。查询通知向数据库注册有两种方式：

（1）对象更改通知 (OCN)注册查询，则无论查询结果是否更改，只要事务更改了查询引用和提交的对象，数据库就会通知应用程序；

（2）查询结果更改通知 (QRCN)注册查询，则每当事务更改查询结果并提交时，数据库都会通知应用程序

配置步骤

（1）概述

可以使用 PL/SQL 接口或 Oracle 调用接口 (OCI)创建 CQN 注册，如果使用 PL/SQL 接口，则通知处理程序是服务器端 PL/SQL 存储过程，这里只介绍这种注册方法。使用 QRCN，可以选择保证模式（默认）或尽力而为模式：

i. 在保证模式下，不会出现误报：只有在保证查询结果集已更改时，数据库才会向应用程序发送 QRCN。

ii. 一些对于保证模式来说过于复杂的查询可以在尽力而为模式下为 QRCN 注册，在这种模式下，CQN 创建并注册它们的更简单版本,比如包含有聚合函数，子查询等。

当通知类型为 OCN 时，任何更改一个或多个已注册对象的 DML 事务都会在提交时为每个对象生成一个通知。

当通知类型为 QRCN 时，任何更改一个或多个已注册查询结果的 DML 事务都会在提交时生成通知。通知包括结果发生变化的查询的查询 ID。

对于任一通知类型，通知包括：

每个更改表的名称
操作类型（INSERT、、UPDATE或DELETE）
ROWID如果注册是使用该ROWID选项创建的并且修改的行数不是太大，则为每个更改的行。

对于 OCN 和 QRCN，这些数据定义语言 (DDL) 语句在提交时会生成通知：

ALTER TABLE
TRUNCATE TABLE
FLASHBACK TABLE
DROP TABLE

（2）注册

i. 先决条件(<username>要求非sys用户)

username：推荐单独建立专用的消息管理用户，本文即使用此方案

create user cqadmin identified by app default tablespace users;
grant connect,resource to cqadmin;
GRANT SELECT ANY DICTIONARY TO cqadmin;
grant select any table to  cqadmin;
GRANT CHANGE NOTIFICATION TO cqadmin;
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO cqadmin;

grant unlimited tablespace to cqadmin;

--dml_locks init.ora参数设置为非0（12c以上默认即可）
alter system set dml_locks=100 scope=both;

--对于QRCN,要求COMPATIBLE设置为>=11.0.0（12c以上默认即可）
alter system set COMPATIBLE=12.0.0 scope=both;

ii. 创建消息日志表

--操作用户可以是消息通知目标表的拥有者，也可以是独立的用户，推荐单独建立一个专门的消息通知管理用户
CREATE SEQUENCE cqadmin.SEQ_CHANGE_NOTIFICATIONS_ID;

CREATE TABLE cqadmin.CHANGE_NOTIFICATIONS(
CHANGE_NOTIFICATIONS_ID NUMBER PRIMARY KEY,
REGID NUMBER,
TRANS_ID VARCHAR2(40),
DBNAME VARCHAR2(30),
TABLE_NAME VARCHAR2(60),
OPERATION VARCHAR2(20),
NUMROWS NUMBER,
ROWIDS VARCHAR2(4000),
LAST_SCN NUMBER,
CURRENT_SCN NUMBER);

iii. 自定义消息处理过程

CREATE OR REPLACE PROCEDURE cqadmin.tables_changed_chnt(ntfnds IN SYS.chnf$_desc) IS
  l_regid           NUMBER;
  l_transaction_id	VARCHAR2(40);
  l_db_name			VARCHAR2(30);
  l_table_name      VARCHAR2(60);
  l_event_type      NUMBER;
  l_numtables       NUMBER;
  l_operation_type  NUMBER;
  l_numrows         NUMBER;
  l_operation       NUMBER;
  l_row_desc        SYS.CHNF$_RDESC;
  l_insert_cnt      NUMBER;
  l_insert_row_id   VARCHAR2(4000) := '';
  l_delete_cnt      NUMBER;
  l_delete_row_id   VARCHAR2(4000) := '';
  l_update_cnt      NUMBER;
  l_update_row_id   VARCHAR2(4000) := '';
  l_last_scn        NUMBER;
  l_current_scn     NUMBER;
BEGIN
  l_regid          := ntfnds.registration_id;
  l_transaction_id := ntfnds.transaction_id;
  l_db_name        := ntfnds.dbname;
  l_numtables      := ntfnds.numtables;
  l_event_type     := ntfnds.event_type;
  SELECT current_scn INTO l_current_scn FROM V$DATABASE;

  IF l_event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE THEN
    FOR i IN 1 .. l_numtables LOOP
      l_insert_cnt      := 0;
      l_update_cnt      := 0;
      l_delete_cnt      := 0;
      l_insert_row_id   := '';
      l_delete_row_id   := '';
      l_update_row_id   := '';
      l_table_name      := ntfnds.table_desc_array(i).table_name;
      l_operation_type  := ntfnds.table_desc_array(i).Opflags;
      l_numrows := ntfnds.table_desc_array(i).numrows;
      SELECT MAX(current_scn) INTO l_last_scn FROM CORE2.CHANGE_NOTIFICATIONS WHERE TABLE_NAME = l_table_name;

      IF nvl(l_numrows, 0) = 0 THEN
        l_numrows := 0; --ROWID INFO NOT AVAILABLE
        l_operation := TO_NUMBER(l_operation_type) - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.ALL_ROWS);

        IF l_operation - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.DELETEOP) >= 0 THEN
            l_operation := l_operation - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.DELETEOP);
            INSERT INTO CHANGE_NOTIFICATIONS(CHANGE_NOTIFICATIONS_ID, REGID, TRANS_ID, DBNAME, TABLE_NAME, OPERATION, NUMROWS, ROWIDS, LAST_SCN, CURRENT_SCN)
            VALUES(SEQ_CHANGE_NOTIFICATIONS_ID.NEXTVAL, l_regid, l_transaction_id, l_db_name, l_table_name, 'DELETE', l_numrows, null, l_last_scn, l_current_scn);
        END IF;
        IF l_operation - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.UPDATEOP) >= 0 THEN
            l_operation := l_operation - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.UPDATEOP);
            INSERT INTO CHANGE_NOTIFICATIONS(CHANGE_NOTIFICATIONS_ID, REGID, TRANS_ID, DBNAME, TABLE_NAME, OPERATION, NUMROWS, ROWIDS, LAST_SCN, CURRENT_SCN)
            VALUES(SEQ_CHANGE_NOTIFICATIONS_ID.NEXTVAL, l_regid, l_transaction_id, l_db_name, l_table_name, 'UPDATE', l_numrows, null, l_last_scn, l_current_scn);
        END IF;
        IF l_operation - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.INSERTOP) >= 0 THEN
            l_operation := l_operation - TO_NUMBER(DBMS_CHANGE_NOTIFICATION.INSERTOP);
            INSERT INTO CHANGE_NOTIFICATIONS(CHANGE_NOTIFICATIONS_ID, REGID, TRANS_ID, DBNAME, TABLE_NAME, OPERATION, NUMROWS, ROWIDS, LAST_SCN, CURRENT_SCN)
            VALUES(SEQ_CHANGE_NOTIFICATIONS_ID.NEXTVAL, l_regid, l_transaction_id, l_db_name, l_table_name, 'INSERT', l_numrows, null, l_last_scn, l_current_scn);
        END IF;
      ELSE
        FOR j In 1 .. l_numrows LOOP
          l_row_desc :=  ntfnds.table_desc_array(i).row_desc_array(j);
          l_operation_type  := l_row_desc.opflags;

           CASE
            WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.INSERTOP) != 0 THEN
              l_insert_row_id :=  l_insert_row_id||l_row_desc.row_id||' ';
              l_insert_cnt := l_insert_cnt + 1;
            WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UPDATEOP) != 0 THEN
              l_update_row_id :=  l_update_row_id||l_row_desc.row_id||' ';
              l_update_cnt := l_update_cnt + 1;
            WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DELETEOP) != 0 THEN
              l_delete_row_id :=  l_delete_row_id||l_row_desc.row_id||' ';
              l_delete_cnt := l_delete_cnt + 1;
          END CASE;
        END LOOP;

        --Generate a record for different operation types 
        IF l_insert_cnt > 0 THEN
            INSERT INTO CHANGE_NOTIFICATIONS(CHANGE_NOTIFICATIONS_ID, REGID, TRANS_ID, DBNAME, TABLE_NAME, OPERATION, NUMROWS, ROWIDS, LAST_SCN, CURRENT_SCN)
            VALUES(SEQ_CHANGE_NOTIFICATIONS_ID.NEXTVAL, l_regid, l_transaction_id, l_db_name, l_table_name, 'INSERT', l_insert_cnt, trim(l_insert_row_id), l_last_scn, l_current_scn);
        END IF;
        IF l_update_cnt > 0 THEN
            INSERT INTO CHANGE_NOTIFICATIONS(CHANGE_NOTIFICATIONS_ID, REGID, TRANS_ID, DBNAME, TABLE_NAME, OPERATION, NUMROWS, ROWIDS, LAST_SCN, CURRENT_SCN)
            VALUES(SEQ_CHANGE_NOTIFICATIONS_ID.NEXTVAL, l_regid, l_transaction_id, l_db_name, l_table_name, 'UPDATE', l_update_cnt, trim(l_update_row_id), l_last_scn, l_current_scn);
        END IF;
        IF l_delete_cnt > 0 THEN
            INSERT INTO CHANGE_NOTIFICATIONS(CHANGE_NOTIFICATIONS_ID, REGID, TRANS_ID, DBNAME, TABLE_NAME, OPERATION, NUMROWS, ROWIDS, LAST_SCN, CURRENT_SCN)
            VALUES(SEQ_CHANGE_NOTIFICATIONS_ID.NEXTVAL, l_regid, l_transaction_id, l_db_name, l_table_name, 'DELETE', l_delete_cnt, trim(l_delete_row_id), l_last_scn, l_current_scn);
        END IF;
      END IF;
    END LOOP;
    COMMIT;
  END IF;
END;

iv. 注册目标查询

DECLARE
  l_regds     SYS.CHNF$_REG_INFO;
  l_regid     NUMBER;
  l_qosflags  NUMBER;
  l_opflags    NUMBER;
  l_id        number;
BEGIN
  l_qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE  +
                DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
  l_opflags := DBMS_CHANGE_NOTIFICATION.INSERTOP + DBMS_CHANGE_NOTIFICATION.UPDATEOP + DBMS_CHANGE_NOTIFICATION.DELETEOP;
  l_regds := SYS.CHNF$_REG_INFO ('qcadmin.tables_changed_chnt', l_qosflags, 0, l_opflags,0);
  l_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start (l_regds);
   SELECT COUNT(*) INTO l_id FROM  MSTDATA.T_DATA mm  WHERE ROWNUM<10;
  DBMS_CHANGE_NOTIFICATION.reg_end;
END;

这里注册了表MSTDATA.T_DATA的查询，可以同时注册多个。相应的，这些表的select权限，及其表空间权限都要开放给cqadmin

grant select on MSTDATA.MD_MATERIAL to qcadmin;

注册成功后可以通过 USER_CHANGE_NOTIFICATION_REGS或DBA_CHANGE_NOTIFICATION_REGS视图查看

SELECT * FROM USER_CHANGE_NOTIFICATION_REGS;

（4）演示

i. 演示准备

create user MSTDATA identified by app;
grant connect,resource to mstdata;
drop table MSTDATA.T_DATA;
create table MSTDATA.T_DATA(
MD_MATERIAL_ID 			    VARCHAR2(32) constraint PK_T_DATA primary KEY,
 CREATED_BY					    VARCHAR2(32),
 CREATE_DATE					    DATE,
 UPDATED_BY					    VARCHAR2(32),
 UPDATE_DATE					    DATE
);
grant select on MSTDATA.MD_MATERIAL to qcadmin;
grant unlimited tablespace to mstdata;

ii. 测试

# 新增
insert into MSTDATA.MD_MATERIAL (MD_MATERIAL_ID,CREATED_BY,CREATE_DATE) values(lpad('acdd22la'||MSTDATA.seq_md_material.nextval,32,'x'),lpad('yys32a',32,'y'),sysdate);
commit;
# 更新
update  MSTDATA.MD_MATERIAL set UPDATED_BY=lpad('acddla',32,'x'),UPDATE_DATE=sysdate;
commit;
# 删除
delete from MSTDATA.MD_MATERIAL;
commit;

查询日志表

select * from cqadmin.CHANGE_NOTIFICATIONS;

运行正常可以分别看到一条OPERATION=INSERT、UPDATE、DELETE的记录

问题排查介绍

oracle通过jobq进程处理消息通知进程，会产生SID_jxxx_pid.trc这样进程运行跟踪日志文件，大部分的问题都可以在这里找到原因，文件大致位于

/opt/oracle/diag/rdbms/<SID>/<SID>/trace/<SID>_j<数字>_<数字>.tr

<SID>对应数据库的SID

常见无法收到消息通知的原因总结如下：

1. JOB_QUEUE_PROCESSES 参数应当设置为非0值；

2. 注册查询时必须是以非sys用户登录数据库，改用非sys用户登录后注册；

3. 事务未提交，未提交的信息不会发送变更消息通知；

4. 用户未正确授权，消息管理账户没有目标表的select权限，或原来授予消息管理员的权限被回收了，需要重新注册

5. 目标表重建，即使重建后名称相同，也需要重新进行注册；

慢工也能出细活

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【ORACLE】查询通知（CQN）配置基础

oracle通过jobq进程处理消息通知进程，会产生SID_jxxx_pid.trc这样进程运行跟踪日志文件，大部分的问题都可以在这里找到原因，文件大致位于/opt/oracle/diag/rdbms///trace/_j_.tr对应数据库的SID常见无法收到消息通知的原因总结如下：1.参数应当设置为非0值；2. 注册查询时必须是以非sys用户登录数据库，改用非sys用户登录后注册；3. 事务未提交，未提交的信息不会发送变更消息通知；
复制链接

扫一扫