Deep Dive into Oracle data change notification

deep Dive into Oracle data change notification

1. What is Oracle data change notification? When there are multiple applications or processes operating the same database, where process 1 inserts, deletes, modifies, and so on, one of the tables in Oracle, Process 2 wants to do the Table1 after the first process operation is complete.      Is there any way for process 2 to get to process 1 operations? Similar processes, multi-threaded synchronization mechanisms, or message response mechanisms. There is a similar implementation in Oracle, with the name of the mechanism:Data Change notification. 2. Support Oracle versionOracle 10gr2 or later.
3. Data change notification supported operations(1) Database status change DB status Changes:startup and shutdown (2) Database object changes DB objects changes:1) DDL change S:alter or Drop actions 2) DML Changes:insert, delete, update actions
3. Two methods of use
3.1 OTL Data Change Notification source detailedSample source Code reference: "1" http://otl.sourceforge.net/otl4_subscriber.htm "2" http://otl.sourceforge.net/otl4_ex585.htm
there is a bug in the Subs.subscribe () interface in use:Bug number and details-ora-24912:listener thread failed. Listen failed. Google offers solutions: Theclient needs to be restarted. (But the test does not work) Error and see my question: http://bbs.csdn.net/topics/391054125http://stackoverflow.com/questions/30847188/ When-use-change-notification-interface-the-ora-24912-listener-thread-failed-l
As of 2015-6-16, the root cause has not yet been found, i.e. the demo has not been tested successfully.
3.2 ocilib Data Change Notification source detailedSample source code reference: http://orclib.sourceforge.net/doc/html/group___ocilib_c_api_subscriptions.html
Code parsing (VS2010 C + + implementation, verified)

#include "stdafx.h" #include "ocilib.h" #pragma comment (lib, "Ociliba.lib") #ifdef _windows#define sleep (x) sleep (x*1000 ) #endif # define Wait_for_events () Sleep (Event_handler (oci_event *event), void Error_Handler (Oci_error *err); int main (void) {oci_connection *con;oci_subscription *sub;oci_statement *st;printf ("= = Initializing Ocilib in Event Mode...\n\n ");//0. The second parameter is the path name of the original Oracle DLL. if (! Oci_initialize (Error_Handler, "Oracle", Oci_env_events)) return exit_failure;printf ("= = connecting to [email  Protected]\n\n ");//1. Connect the first parameter format:" IP: Port/Service Name ", second parameter: Login user name, third parameter: password. con = oci_connectioncreate ("100.200.10.50:1521/ts54", "Tss54", "psdts**", Oci_session_default); Oci_setautocommit ( Con, TRUE);p rintf ("= = Creating statement...\n\n"); st = Oci_statementcreate (con);p rintf ("= = Creating tables...\n \ n ");//2. Create Data Table oci_executestmt (ST," CREATE TABLE table1 (code number) ") Oci_executestmt (St," CREATE TABLE table2 (str VARCHAR2);p rintf ("= = registering subscription...\n\n"),//3. Registration NoticeThe event sub-00 is the notification name. Sub = Oci_subscriptionregister (Con, "sub-00", Oci_cnt_all, Event_handler, 5468, 0);p rintf ("= = Adding Q Ueries to being notified...\n\n "); Oci_prepare (St," select * from table1 "); Oci_subscriptionaddstatement (Sub, ST); oci_ Prepare (St, "select * from table2"); Oci_subscriptionaddstatement (Sub, ST);//wait for the response Register event wait_for_events (); You can sleep long enough to wait until there are other processes that modify the table, and the service responds. The following is for testing purposes and can be used without execution. #if 0////4. Execute the corresponding database alter operation//printf ("= = executing some DDL operation...\n\n");//Oci_executestmt (ST, "ALTER TABLE TA Ble1 Add price number "); Alter Event/////wait for 5s, wait for printout.//Wait_for_events ()////////5. Perform inser,update operations on the database. printf ("= = executing some DML operation...\n\n");//Oci_executestmt (St, "insert into table1 values (1, 10.5)");//OCI  _executestmt (St, "insert into table2 values (' Shoes ')");//Oci_executestmt (ST, "update table1 set price = 13.5 where code = 1 ");//Oci_executestmt (St," Delete from table2 ");//Wait_for_events ();/////6. Perform a drop database table operation. printf ("= droping tables...\n\n");//Oci_executestmt (St," drop table table1 ");//Oci_executestmt (St," drop table table2 ");//Wait_for_events ();//PRI ntf ("= = disconnecting from db...\n\n");//Oci_connectionfree (con);//printf ("= = stopping the remote database...\n \ n ");//Oci_databaseshutdown (" db "," sys "," SYS ",//oci_session_sysdba,//oci_db_sdm_full,//oci_db_sdf_immediate);// #endifprintf ("= = Unregistering subscription...\n\n"), Oci_subscriptionunregister (sub);p rintf ("+ = cleaning up Ocilib resources...\n\n "), Oci_cleanup ();p rintf (" = = done...\n\n "); return exit_success;} void Error_Handler (Oci_error *err) {int err_type = Oci_errorgettype (err); const char *err_msg = oci_errorgetstring (err); printf ("* *%s-%s\n", Err_type = = oci_err_warning? "Warning": "Error", err_msg);} void Event_handler (Oci_event *event) {unsigned int type = Oci_eventgettype (event); unsigned int op = oci_eventgetoperation (event); Oci_subscription *sub = Oci_eventgetsubscription (event);p rintf ("* * Notification:%s\n\n", Oci_subscriptiongeTname (sub));p rintf ("... Database:%s\n ", Oci_eventgetdatabase (event)); switch (type) {case oci_ent_startup:printf (" ... Event:startup\n "); Break;case oci_ent_shutdown:printf (" ... Event:shutdown\n "); Break;case oci_ent_shutdown_any:printf (" ... Event:shutdown any\n ") break;case oci_ent_drop_database:printf (" ... Event:drop database\n ") break;case oci_ent_deregister:printf (" ... Event:deregister\n "); Break;case oci_ent_object_changed:printf (" ... Event:object changed\n ");p rintf (" ..... Object:%s\n ", Oci_eventgetobject (event)), switch (OP) {case oci_ont_insert:printf (" ... Action:insert\n "); Break;case oci_ont_update:printf (" ..... Action:update\n "); Break;case oci_ont_delete:printf (" ..... Action:delete\n "); Break;case oci_ont_alter:printf (" ..... Action:alter\n "); Break;case oci_ont_drop:printf (" ..... Action:drop\n "); break;} if (Op < oci_ont_alter) printf ("..... Rowid:%s\n ", Oci_eventgetrowid (event)); printf ("\ n ");} 

//Data Change Notification effectThe three steps are as follows:

4, two kinds of thinking comparison and analysis of the issue of reflectionMonday PM stepped, the validation of data change notification is expected to be completed in the evening of Monday. but the final test found ra-24912:listener thread failed. Listen failed bug. Google and StackOverflow have more than n data, there is no solution. continued until Tuesday 4 o'clock in the afternoon, in the OTL tried n Many methods are not resolved. test ideas include:1) Authorizing the local user to have grant permission, which can execute a successful "Grant Change notifiation to username", has been granted, but the bug persists. 2) StackOverflow Foreigners to provide ideas, may be related to the service, restart the Oracle database, the bug still exists. 3) from the Program Subscribe interface analysis, but due to the third-party interface, there is no deep print log, not clear the root cause. and the Subscirbe interface is of type void, with no return value, only by catching an exception and getting an error. And the error message is the only listener failure.
In conclusion, since OTL this road is not clear, why not try other ideas. when Google entered the "Oracle Data change notification C + +" keyword, it found a 3.2 implementation.
Reflection:1, for unfamiliar areas, can not "a tree hanging dead", when trying N long a road does not pass, and the industry Daniel also no good plan, you can consider changing ideas, perhaps will steady. 2, of course, for the first road bug why exist, as a programmer or to take spare time to get to the bottom of the problem, finally solved.
2015-6-16 22:44 Think about the bed in front of the house

Ming Yi World

Reprint please indicate source, original address: http://blog.csdn.net/laoyang360/article/details/46524519

If you feel this article is helpful, please click on the ' top ' support, your support is I insist on writing the most power, thank you!

Deep Dive into Oracle data change notification