My application currently polls database every n seconds to see if there are any new records.
To reduce network round trips, and CPU cycles of this polling i was thinking to replace it with CQN based approach where database will itself update subscribed application if there is any Commit to database.
The only problem is what if Oracle was NOT able to notify application due to any connection issue between oracle and subscribed application or if the application was crashed or killed due to any reason? ... Is there a way to know if application have missed any CQN notification?
Is polling database via application code itself the only way for mission critical applications?
You didn't say whether every 'n' seconds means you're expecting data every few seconds, or you just need your "staleness" to as low as that. That has an impact on the choice of CQN, because as per docs, https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/cqn.html#GUID-98FB4276-0827-4A50-9506-E5C1CA0B7778
"Good candidates for CQN are applications that cache the result sets of queries on infrequently changed objects in the middle tier, to avoid network round trips to the database. These applications can use CQN to register the queries to be cached. When such an application receives a notification, it can refresh its cache by rerunning the registered queries"
However, you have control over how persistent you want the notifcations to be:
"Reliable Option: By default, a CQN registration is stored in shared memory. To store it in a persistent database queue instead—that is, to generate reliable notifications—specify QOS_RELIABLE in the QOSFLAGS attribute of the CQ_NOTIFICATION$_REG_INFO object. The advantage of reliable notifications is that if the database fails after generating them, it can still deliver them after it restarts. In an Oracle RAC environment, a surviving database instance can deliver them. The disadvantage of reliable notifications is that they have higher CPU and I/O costs than default notifications do."