4 Replies Latest reply: May 3, 2013 2:35 AM by andy dufresne RSS

    Oracle Database Change Notification Logs

    andy dufresne
      We use Oracle's database change notification feature to implement a local cache in our application server. Oracle version - Standard edition 11.2.0.3.

      There are some use cases where the cache is not updated. Probably this could be because the registration of the application server is somehow deleted. To investigate these issues, I would like to know on how can I enable oracle logs for the below events
      1. Registration and un-registration for DCN.
      2. Notification sent from the oracle side.

      Kindly let me know how to enable oracle logs.

      Thanks!
        • 1. Re: Oracle Database Change Notification Logs
          rp0428
          >
          We use Oracle's database change notification feature to implement a local cache in our application server. Oracle version - Standard edition 11.2.0.3.

          There are some use cases where the cache is not updated. Probably this could be because the registration of the application server is somehow deleted. To investigate these issues, I would like to know on how can I enable oracle logs for the below events
          1. Registration and un-registration for DCN.
          2. Notification sent from the oracle side.

          Kindly let me know how to enable oracle logs.
          >
          There is an example in the docs in the troubleshooting section of the DBMS_CHANGE_NOTIFICATION doc. Have you reviewed that?
          http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_chngnt.htm
          >
          Troubleshooting

          If you have created a registration and seem to not receive notifications when the underlying tables are changed, please check the following.

          •Is the job_queue_processes parameter set to a non-zero value? This parameter needs to be configured to a non-zero value in order to receive PL/SQL notifications via the handler.

          •Are the registrations being created as a non-SYS user?

          •If you are attempting DML changes on the registered object, are you COMMITing the transaction? Please note that the notifications are transactional and will be generated when the transaction COMMITs.

          •It maybe possible that there are run-time errors during the execution of the PL/SQL callback due to implementation errors. If so, they would be logged to the trace file of the JOBQ process that attempts to execute the procedure. The trace file would be usually named <ORACLE_SID>j*<PID>.trc. '

          For example, if the ORACLE_SID is 'dbs1' and the process is 12483, the trace file might be named 'dbs1_j000_12483.trc.

          Suppose a registration is created with 'chnf_callback as the notification handler and with registration_id 100. Let us suppose the user forgets to define the chnf_callback procedure. Then the JOBQ trace file might contain a message of the following form.

          Runtime error during execution of PL/SQL cbk chnf_callback for reg CHNF100
          Error in PLSQL notification of msgid:
          Queue :
          Consumer Name :
          PLSQL function :chnf_callback
          Exception Occured, Error msg:
          ORA-00604: error occurred at recursive SQL level 2
          ORA-06550: line 1, column 7:
          PLS-00201: identifier 'CHNF_CALLBACK' must be declared
          ORA-06550: line 1, column 7:
          PL/SQL: Statement ignored
          • 2. Re: Oracle Database Change Notification Logs
            andy dufresne
            The example in the troubleshooting guide is of a PLSQL registration and in our case we do a JDBC registration. Our application server registers queries with the oracle database server on start up. Oracle seems to give notifications for a while but after some unknown use case, the notifications are stopped. To troubleshoot this I wanted to know if there are any logs maintained by oracle for the two events I mentioned above.

            I followed the troubleshoot steps mentioned in the guide but they seem to apply in usecases where notifications never work.

            Let me know your thoughts/suggestions. Thanks.
            • 3. Re: Oracle Database Change Notification Logs
              990507
              Hi

              We are also facing similar issue with the jdbc DCN registration, and notification stops working for some time.
              And after retry to refresh the cache it starts working. We are also looking for way to get the oracle logs, to confirm is
              oracle publishes the notification during the time frame of this issue.


              For analysis we added following code in java to match the DCN registration id registered with the oracle in table USER_CHANGE_NOTIFICATION_REGS :
              DatabaseChangeRegistration dcr=null;

                        logger.info("DCN RegistrationId : " + dcr.getRegistrationId());
                        logger.info("DCN State : " + dcr.getState());

              REGID (attr in table USER_CHANGE_NOTIFICATION_REGS) matches with the RegistrationId printed in java log and is active in our case , hence during retry after some time it works for us.

              Please share if you got the way to see the oracle DCN logs ?

              Re: DCN Notification stops working after 10 to 12 hour in test environment

              Edited by: user12204272 on Feb 13, 2013 11:38 AM
              • 4. Re: Oracle Database Change Notification Logs
                andy dufresne
                While investigating this further I got to know that the jdbc driver listens on port 47632 (default port if not specified for ojdbc 11.2.0.3) and the EMON processes (E000...E004) are responsible for sending notifications to the client (jdbc driver).

                Here is a further question - What oracle processes (on linux) are responsible for managing the DCN registration? i.e. add & remove a registration. Probably we could monitor if that process(s).

                Thanks.