오라클 19c DCN(Database Change Notification) 기능 테스트

OS 환경 : Oracle Linux 8.7 (64bit)

 

DB 환경 : Oracle Database 19.31.0.0

 

방법 : 오라클 19c DCN(Database Change Notification) 기능 테스트

Oracle DCN(Database Change Notification)은 데이터베이스의 특정 테이블이나 데이터가 변경(INSERT, UPDATE, DELETE 등)되었을 때 오라클 DB가 클라이언트 애플리케이션(Java 등)에 변경 이벤트를 실시간으로 전달해 주는 기능임
일반적으로 애플리케이션은 데이터 변경 여부를 확인하기 위해 일정 주기로 DB를 반복 조회(Polling)해야 함
하지만 DCN을 사용하면 데이터 변경 시점에 DB가 먼저 클라이언트로 Notification을 전달하기 때문에 불필요한 반복 조회를 줄일 수 있음
DCN은 JDBC 애플리케이션에서 특정 SQL 또는 테이블을 등록(Registration)하고 리스너(Listener)를 연결하는 방식으로 동작함
이후 다른 세션에서 데이터 변경 후 COMMIT이 발생하면, 오라클 서버가 JDBC 드라이버 쪽으로 TCP 기반 Notification 이벤트를 전달함

 

 

주요 목적은 애플리케이션의 로컬 캐시(Cache)를 실시간으로 무효화(Cache Invalidation)하거나 데이터 변경 이벤트를 빠르게 감지하는 데 있음. 특히 데이터 변경 빈도는 높지 않지만, 변경 시 즉시 반영이 필요한 환경에서 효율적으로 사용할 수 있음
DCN은 단순 테이블 변경 감지가 아니라 Query Result 기반으로 동작할 수도 있음
예를 들어 특정 WHERE 조건을 가진 SELECT 문을 등록한 경우, INSERT뿐 아니라 UPDATE로 인해 조회 결과셋이 변경되는 상황도 감지 가능함
또한 일반적인 방식에서는 오라클 서버가 클라이언트(Java 프로세스) 방향으로 TCP Callback 연결을 수행함
따라서 VPN 환경이나 방화벽 환경에서는 Notification 수신이 실패할 수 있음
이런 경우에는 JDBC의 Client Initiated DCN 방식을 사용하여 클라이언트가 DB 방향으로 Notification 연결을 유지하는 방식으로 우회 가능함
이 기능을 사용하려면 오라클 사용자 계정에 CHANGE NOTIFICATION 권한이 필요함.
참고로 DCN은 오라클 JDBC 드라이버(ojdbc)에 종속적인 기능이고 데이터베이스 변경 시 애플리케이션으로 실시간 이벤트를 전달할 수 있다는 장점이 있지만 DBMS 이식성(Portability)은 낮은 편임

 

 

본문에서는 DCN 기능(Client Initiated DCN 방식)을 활성화해둔 다음 테이블에 dml 및 commit을 해보고 notification(변경사항)을 받아봄

 

 

테스트
imsi 유저에 change notification 권한 부여

1 2 3 4 5

SQL> conn / as sysdba grant change notification to imsi; grant create table to imsi; grant create session to imsi;

 

 

imsi 유저에 dcn 테스트용 테이블 생성

1 2 3 4 5 6 7 8 9 10 11

SQL> conn imsi/imsi drop table dcn_test purge; create table dcn_test (     id      number,     name    varchar2(50),     status  varchar2(20) );   insert into dcn_test values (1, 'APPLE', 'READY'); commit;

 

 

윈도우에서 dcn이라는 폴더 생성
이 폴더에 ojdbc10.jar, orai18n.jar 파일 준비
(현재 테스트 db의 캐릭터셋이 KO16MSWIN949여서 orai18n.jar도 필요함)
참고 : ojdbc 다운로드 ( https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html )

 

 

윈도우에서 dcn 폴더에 OracleDcnTest.java 파일생성
*String url 부분은 본인 db서버 환경에 맞게 설정

SQL>
import java.sql.*;
import java.util.Properties;
 
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.*;
 
public class OracleDcnTest {
 
    public static void main(String[] args) throws Exception {
 
        Class.forName("oracle.jdbc.OracleDriver");
 
        String url =
            "jdbc:oracle:thin:@192.168.137.50:1521/ORA19DBFS";
 
        Properties props = new Properties();
 
        props.setProperty(
            OracleConnection.CONNECTION_PROPERTY_USER_NAME,
            "imsi"
        );
 
        props.setProperty(
            OracleConnection.CONNECTION_PROPERTY_PASSWORD,
            "imsi"
        );
 
        OracleConnection conn =
            (OracleConnection) DriverManager.getConnection(url, props);
 
        System.out.println("CONNECTED");
 
        Properties dcnProps = new Properties();
        
        dcnProps.setProperty(
            OracleConnection.DCN_CLIENT_INIT_CONNECTION,
            "true"
        );
        
        dcnProps.setProperty(
            OracleConnection.DCN_NOTIFY_ROWIDS,
            "true"
        );
        
        dcnProps.setProperty(
            OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,
            "true"
        );
 
        DatabaseChangeRegistration reg =
            conn.registerDatabaseChangeNotification(dcnProps);
 
        reg.addListener(event -> {
        
            System.out.println("");
            System.out.println("===== DCN EVENT RECEIVED =====");
            System.out.println("EVENT TYPE : " + event.getEventType());
            System.out.println("REG ID     : " + event.getRegistrationId());
        
            QueryChangeDescription[] queries =
                event.getQueryChangeDescription();
        
            if (queries != null) {
                for (QueryChangeDescription query : queries) {
                    System.out.println("QUERY ID   : " + query.getQueryId());
                    System.out.println("QUERY OP   : " + query.getQueryChangeEventType());
        
                    TableChangeDescription[] tables =
                        query.getTableChangeDescription();
        
                    if (tables != null) {
                        for (TableChangeDescription table : tables) {
                            System.out.println("TABLE      : " + table.getTableName());
                            System.out.println("TABLE OP   : " + table.getTableOperations());
        
                            RowChangeDescription[] rows =
                                table.getRowChangeDescription();
        
                            if (rows != null) {
                                for (RowChangeDescription row : rows) {
                                    System.out.println("ROW OP     : " + row.getRowOperation());
                                    System.out.println("ROWID      : " + row.getRowid());
                                }
                            }
                        }
                    }
                }
            }
        
            System.out.println("==============================");
            System.out.println("");
        });
        Statement stmt = conn.createStatement();
 
        ((OracleStatement) stmt)
            .setDatabaseChangeRegistration(reg);
 
        ResultSet rs = stmt.executeQuery(
            "select id, name, status from dcn_test"
        );
 
        while (rs.next()) {
            System.out.println(
                rs.getInt(1) + " " +
                rs.getString(2)
            );
        }
 
        rs.close();
        stmt.close();
 
        System.out.println("");
        System.out.println("DCN REGISTERED");
        System.out.println("WAITING FOR EVENTS...");
        System.out.println("");
 
        while (true) {
            Thread.sleep(1000);
        }
    }
}

 

 

윈도우에서 cmd 창 열고 java 컴파일

1 2 3

CMD> javac -cp .;ojdbc10.jar;orai18n.jar OracleDcnTest.java Note: OracleDcnTest.java uses or overrides a deprecated API. Note: Recompile with -Xlint:deprecation for details.

에러는 아니고 경고 메세지만 나옴

 

 

윈도우에서 java 실행

1 2 3 4 5 6

CMD> java -cp .;ojdbc10.jar;orai18n.jar OracleDcnTest CONNECTED 1 APPLE   DCN REGISTERED WAITING FOR EVENTS...

이렇게 연결되고 dcn 대기 상태라고 표시됨

 

 

insert 테스트
sqlplus 열어서 dcn_test 테이블에 insert 시도

1 2 3 4 5 6 7

SQL> insert into dcn_test values (2, 'ORANGE', 'READY');   1 row created.   SQL> commit;   Commit complete.

 

 

commit을 입력하는 순간 cmd창에 dcn 이벤트가 표시됨

1 2 3 4 5 6 7 8 9 10 11

CMD>  ===== DCN EVENT RECEIVED ===== EVENT TYPE : QUERYCHANGE REG ID     : 325 QUERY ID   : 5 QUERY OP   : QUERYCHANGE TABLE      : IMSI.DCN_TEST TABLE OP   : [INSERT] ROW OP     : INSERT ROWID      : AAAMvEAAEAABL9VAAB ==============================

insert 되었다고 나오고 rowid 까지 나옴

 

 

dcn_test 테이블 확인

1 2 3 4 5 6 7 8 9 10

SQL> set lines 200 pages 1000 col name for a10 col status for a10 select a.*,rowid from dcn_test a;           ID NAME       STATUS     ROWID ---------- ---------- ---------- ------------------          1 APPLE      READY      AAAMvEAAEAABL9VAAA          2 ORANGE     READY      AAAMvEAAEAABL9VAAB

id 2번 rowid가 AAAMvEAAEAABL9VAAB인 row가 확인됨

 

 

update 테스트
sqlplus 열어서 dcn_test 테이블에 update 시도

1 2 3 4 5 6 7

SQL> update dcn_test set id = 99 where id = 2;   1 row updated.   SQL> commit;   Commit complete.

 

 

commit을 입력하는 순간 cmd창에 dcn 이벤트가 표시됨

1 2 3 4 5 6 7 8 9 10 11

CMD>  ===== DCN EVENT RECEIVED ===== EVENT TYPE : QUERYCHANGE REG ID     : 325 QUERY ID   : 5 QUERY OP   : QUERYCHANGE TABLE      : IMSI.DCN_TEST TABLE OP   : [UPDATE] ROW OP     : UPDATE ROWID      : AAAMvEAAEAABL9VAAB ==============================

update 되었다고 나오고 rowid 까지 나옴

 

 

dcn_test 테이블 확인

1 2 3 4 5 6 7 8 9 10

SQL> set lines 200 pages 1000 col name for a10 col status for a10 select a.*,rowid from dcn_test a;           ID NAME       STATUS     ROWID ---------- ---------- ---------- ------------------          1 APPLE      READY      AAAMvEAAEAABL9VAAA         99 ORANGE     READY      AAAMvEAAEAABL9VAAB

id 99번 rowid가 AAAMvEAAEAABL9VAAB인 row가 확인됨

 

 

delete 테스트
sqlplus 열어서 dcn_test 테이블에 delete 시도

1 2 3 4 5 6 7

SQL> delete dcn_test where id = 99;   1 row deleted.   SQL> commit;   Commit complete.

 

 

commit을 입력하는 순간 cmd창에 dcn 이벤트가 표시됨

1 2 3 4 5 6 7 8 9 10 11

CMD>  ===== DCN EVENT RECEIVED ===== EVENT TYPE : QUERYCHANGE REG ID     : 325 QUERY ID   : 5 QUERY OP   : QUERYCHANGE TABLE      : IMSI.DCN_TEST TABLE OP   : [DELETE] ROW OP     : DELETE ROWID      : AAAMvEAAEAABL9VAAB ==============================

delete 되었다고 나오고 rowid 까지 나옴

 

 

dcn_test 테이블 확인

1 2 3 4 5 6 7 8 9

SQL> set lines 200 pages 1000 col name for a10 col status for a10 select a.*,rowid from dcn_test a;           ID NAME       STATUS     ROWID ---------- ---------- ---------- ------------------          1 APPLE      READY      AAAMvEAAEAABL9VAAA

정상적으로 id 99번인 row가 제거됨

 

 

ddl 테스트
컬럼 추가

1 2 3

SQL> alter table dcn_test add test_col number;   Table altered.

 

 

alter 구문을 입력하는 순간 cmd창에 dcn 이벤트가 표시됨

1 2 3 4 5 6 7 8

===== DCN EVENT RECEIVED ===== EVENT TYPE : QUERYCHANGE REG ID     : 325 QUERY ID   : 5 QUERY OP   : QUERYCHANGE TABLE      : IMSI.DCN_TEST TABLE OP   : [ALL_ROWS, ALTER] ==============================

 

 

참고로 만약 dcn이 제대로 동작하지 않는 경우 v$subscr_registration_stats 뷰에 정보가 남음

SQL> select * from v$subscr_registration_stats;
 
    REG_ID  NUM_NTFNS NUM_GROUPING_NTFNS NUM_NTFNS_CURRENT_GROUP LAST_NTFN_START_TIME
---------- ---------- ------------------ ----------------------- ---------------------------------------------------------------------------
LAST_NTFN_SENT_TIME                                                         TOTAL_EMON_LATENCY      EMON# ALL_ TOTAL_PAYLOAD_BYTES_SENT   SHARD_ID NUM_RETRIES TOTAL_PLSQL_EXEC_TIME
--------------------------------------------------------------------------- ------------------ ---------- ---- ------------------------ ---------- ----------- ---------------------
LAST_ERR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LAST_ERR_TIME                                                               LAST_UPDATE_TIME                                                            NUM_PENDING_NTFNS TOTAL_PENDING_NTFN_BYTES
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------------- ------------------------
    CON_ID
----------
       301          2                  0                       0 29-MAY-26 06.34.24.630 AM +09:00
29-MAY-26 06.34.24.630 AM +09:00                                                             0          4                             0          0           0                        0
pls cbk error: 6550
29-MAY-26 06.34.50.273 AM +09:00                                            29-MAY-26 06.34.50.273 AM +09:00                                              0       752
         0

 

 

이 경우 DBMS_CHANGE_NOTIFICATION.DEREGISTER를 이용해 제거할 수 있음(감시 해제)

1 2 3 4 5

SQL> BEGIN     DBMS_CHANGE_NOTIFICATION.DEREGISTER(301); END; /

 

 

결론 :
Oracle DCN(Database Change Notification) 기능을 이용해 데이터가 변경되었을 때 실시간으로 변경 사항을 받을 수 있음
대량으로 데이터를 넣어보고 commit 하는 테스트는 해보지 않아 부하가 얼마나 갈지는 모르지만 실제 업무에 적용하는 경우 부하테스트도 해보는것이 좋을듯함

 

 

 

참조 : 

https://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/226969.htm

https://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/jdbc/dcn/package-summary.html

https://www.alachisoft.com/ko/blogs/sync-ncache-with-oracle-server/