This discussion is archived
7 Replies Latest reply: Feb 12, 2013 12:34 PM by 990507 RSS

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

990507 Newbie
Currently Being Moderated
Hi

We are using Oracle 11G and Java 5 (ojdbc5.jar).

We are using Oracle database change notification (DatabaseChangeRegistration) in java code to refresh the table cache (records of tables are stored in java hashtable for caching) in app server. The same code is working fine in production and was working fine in the test environment.
But from past one month we are facing issues in with our four application servers stop receiving the notification at the same time from the database, and we need to restart all the four application servers at the same time.

We are trying to find out if this issue is happening due to oracle database of some thing getting wrong with the java DatabaseChangeRegistration thread.

One more scenario is noticed if we are not restarting the app servers for almost one hour then this functionality start working again automatically, but some time it took more than hour too to get working without restart of the app servers.

We are using following code to register the Database change notification,

Properties prop = new Properties();
          prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"false");
          prop.setProperty(OracleConnection.DCN_IGNORE_DELETEOP,"true");
          
cr = ((OracleConnection) ((WrappedConnection)(((WrappedConnection)conn).getUnderlyingConnection())).getUnderlyingConnection()).registerDatabaseChangeNotification(prop);
          
               // Add the call back method to the oracle server for the jdbc layer
               logger.info("Adding DCN Listener");
               dcnList = new DCNListener(this);
          dcr.addListener(dcnList);
          
          // second step: add objects in the registration:
               logger.info("Add Object in the registration");
          stmt = ((WrappedConnection)(((WrappedConnection)conn).getUnderlyingConnection())).getUnderlyingConnection().createStatement();

          // associate the statement with the registration:
               logger.info("Associate the statement with the registration");
          ((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
               
          logger.info("Execute registered SQL : "+REGISTERED_SQL);
          rs = stmt.executeQuery(REGISTERED_SQL);
  • 1. Re: DCN Notification stops working after 10 to 12 hour in test environment
    sb92075 Guru
    Currently Being Moderated
    I suspect a bug in the application code; since this is development & all.
  • 2. Re: DCN Notification stops working after 10 to 12 hour in test environment
    990507 Newbie
    Currently Being Moderated
    Hi Thanks for the reply,

    Actually the same code is running in both test and Production. And after 4 month of the release in test suddenly this issue started in test, but this issue is never noticed in the Production.

    Thanks
  • 3. Re: DCN Notification stops working after 10 to 12 hour in test environment
    sb92075 Guru
    Currently Being Moderated
    user12204272 wrote:
    Hi Thanks for the reply,

    Actually the same code is running in both test and Production. And after 4 month of the release in test suddenly this issue started in test, but this issue is never noticed in the Production.
    therefore you logically conclude that the root cause is within the DB.

    The DB can only do what it is asked to do by the client.

    Best Wishes on your Snipe hunt.

    http://en.wikipedia.org/wiki/Snipe_hunt
  • 4. Re: DCN Notification stops working after 10 to 12 hour in test environment
    990507 Newbie
    Currently Being Moderated
    No I am not saying issue is with the db. We are analyzing from both end java and oracle. As we have 4 app servers and from server logs it look like all the four app servers stop getting notifications at the same time from the db which is a common for all 4 app servers. Hence can't confirm if app server code is having issue. All this piece of notification code was working fine since 2009 in both test and PROD, and suddenly stop working for test without change in code , this is the reason we are suspecting some thing may be wrong at db end. Though we are reviewing our app server code too.

    Any way thanks for the reply.
  • 5. Re: DCN Notification stops working after 10 to 12 hour in test environment
    sb92075 Guru
    Currently Being Moderated
    user12204272 wrote:
    No I am not saying issue is with the db. We are analyzing from both end java and oracle. As we have 4 app servers and from server logs it look like all the four app servers stop getting notifications at the same time from the db which is a common for all 4 app servers. Hence can't confirm if app server code is having issue. All this piece of notification code was working fine since 2009 in both test and PROD, and suddenly
    Are you claiming that each & every line on code running on the Test server is 100% identical to Prod?
    So what exactly has been happening on the test server since the previous software version release?
    stop working for test without change in code , this is the reason we are suspecting some thing may be wrong at db end.
    So explain again what changed in the DB Server that could account for the observed change in application behavior.
  • 6. Re: DCN Notification stops working after 10 to 12 hour in test environment
    rp0428 Guru
    Currently Being Moderated
    Have you checked the system views and confirmed that registration is still available?
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dcn.htm#ADFNS1014
    >
    Querying Change Notification Registrations
    You can query the following data dictionary views to obtain information about registered clients of the Database Change Notification feature:

    •DBA_CHANGE_NOTIFICATION_REGS

    •USER_CHANGE_NOTIFICATION_REGS

    For example, you can obtain the registration ID for a client and the list of objects for which it receives notifications. To view registration-ids and table names for HR, you can do the following from SQL*Plus:
  • 7. Re: DCN Notification stops working after 10 to 12 hour in test environment
    990507 Newbie
    Currently Being Moderated
    Hi

    We are printing the DCN status and registration id in app server log, and it says that registration id is active, and also checked the table USER_CHANGE_NOTIFICATION_REGS.

    App server log, after during this issue :
    --------------------------------------------------------
    Getting DCN Status --------
    DCN RegistrationId (dcr.getRegistrationId()) : 71390
    DCN State (dcr.getState()) : ACTIVE

    Records from the table :
    ------------------------------------------------
    REGID REGFLAGS CALLBACK OPERATIONS_FILTER CHANGELAG TIMEOUT TABLE_NAME

    71390 0 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=IP_ADDRESS)(PORT=XXXX))?PR=0 6 0 4294967295 dcn_queue_table

    The registration id at client and at the database matches during this issue and at client log it prints this registration id as ACTIVE during the issue.

    According to our logic we read the newly inserted data from table "dcn_queue_table", through select query and and delete the records after the success of read operation .
    Hence if there is any record in table dcn_queue_table, then it is an issue for us.


    Now during the issue there were records in table, app server didn't receive and notification through the DCN listner thread . Now after 15 min i had updated new record in our application form, and now after 15 min, after the insertion of the new record the notification starts working now for this same DCN RegistrationId 71390.

    Please suggest if it look like the issue with app server code that is handling this DatabaseChangeRegistration logic, and not an issue with the database ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points