Experience using Object Change Notification in Oracle
Recently I had to work with an interesting thing and I decided to share this with the readers of Habrakhabr. I want to talk about the experience of using Object Change Notification in Oracle. How to find out if the data has changed without making a request.
We needed to watch for changes in the database. The solution that first came to mind was simple - updating data for a given time interval (for example, once a minute). The disadvantages of this approach are obvious - the data could not change over the selected period and then we simply load the server and the data transfer channel (the data we observe is quite voluminous). Either the data will change at the beginning of the time interval and we will not know about it until time runs out (this is not so critical, but the first one is quite likely). Given the fact that there could be quite a few clients, the first minus is really serious. Fortunately, we have found that Oracle offers a better solution to this problem, starting with version 10g.
OCN can work both exclusively on the server side of the database and inform the client application via a special interface that the data it has observed has changed.
Detailed information on how it works inside is easily googled; I thought that it is not necessary to inflate the article with information, which is already in abundance, so I will tell only briefly.
')
At the beginning, it is necessary to allow the use of request registrations on the server and allow the use of parallel notifications. Then, the user logs the alert. When registering, a request is indicated that must be observed, for example:
Let's go in order:
I almost completely took the example from the documentation, only added it with a couple of important points in my opinion and reduced it a little. After the code is written, compiled, and the program is running, you can go and do something like:
When done, we should see this:
Hooray, everything works! More detailed information can be taken, for example, in the documentation . It is better to go straight to what is usually not indicated in the documentation - the difficulties encountered.
For our case, there were not so many disadvantages that they outweighed the advantages of this approach, and some of them are difficult to call minuses, as nuances. However, it seems to me that the enemy must be known in person.
First of all, we tried to run it on our own and everything worked fine here. And, as is usually the case, nothing worked for the customer. Or rather, it didn't even take off. To figure out what the matter was, it turned out to be a rather non-trivial task. After you start a registration, you can see it by performing a special request:
So, we saw this registration immediately after creation, but at the moment when the alert was supposed to work, the registration record simply disappeared. In the logs, unfortunately, we could not find anything that would explain this behavior. I had to take a chance and without looking to blame the firewall for everything. Fortunately, we guessed it. Therefore, my first recommendation is to remember to check the firewall and specify the port when registering notifications.
Not all registration requests are supported. It must be remembered that it may not be possible to read the data in one request and hang a notification on it. If there are many joines in the request, it is better to mentally prepare yourself for having to write a similar, but simple request. In addition, if you register an alert for a query with join-e, in fact, it will register as many alerts as there are tables in the query (each for its own table), which may waste resources.
By default, registrations are created without a timeout. That is, as it were, forever. As a rule, such registration is not needed by anyone, because the time of the application is usually limited. Such registration is very difficult then to pick out from the base, especially if there are a lot of them. The correct way, apparently, would be to delete the registration when you finish working with it (if you do it in the same connection, and there is a link to the OracleDependency instance, this is easy to do, just call the RemoveRegistration method on the instance of OracleDependency class). However, it seems to me that this method is only suitable for ideal worlds in which your applications never fall, communication channels never break and resources are always released. Otherwise, ultimately, we will have the same thing as without deletion, however, the consequences will begin to manifest later (just when we go into production). We solved the problem in this way - we began to always specify a timeout during registration. And a few seconds before its expiration, delete the old registration and create a new one (to minimize the time of no registration). The solution, of course, is not the most ideal, but in principle we are satisfied. If you have any ideas on how to do better, I will be very happy to rewrite this piece of code.
In order for the alerts to work faster within one application, we have divided into separate classes the logic for working with the database, the logic for notifying our objects of data change events, and the logic for managing both. Thanks to this, we were able to use one common notification mechanism and send notifications inside the application manually, without going outside (in oracle). For a user who works within his session and should see instantly the result of his actions, everything works well.
That read my article to this place. I would be very happy with any feedback, from the criticism of my code and methods of presentation of the material and ending with the emotions you experienced in the process of exploring this technology. If you tell me about your experience with OCN, I will also be very happy and will be able to add some more material to my piggy bank.
Thank you in advance.
So why do we need Object Change Notification (OCN)
We needed to watch for changes in the database. The solution that first came to mind was simple - updating data for a given time interval (for example, once a minute). The disadvantages of this approach are obvious - the data could not change over the selected period and then we simply load the server and the data transfer channel (the data we observe is quite voluminous). Either the data will change at the beginning of the time interval and we will not know about it until time runs out (this is not so critical, but the first one is quite likely). Given the fact that there could be quite a few clients, the first minus is really serious. Fortunately, we have found that Oracle offers a better solution to this problem, starting with version 10g.
How does OCN work
OCN can work both exclusively on the server side of the database and inform the client application via a special interface that the data it has observed has changed.
Detailed information on how it works inside is easily googled; I thought that it is not necessary to inflate the article with information, which is already in abundance, so I will tell only briefly.
')
At the beginning, it is necessary to allow the use of request registrations on the server and allow the use of parallel notifications. Then, the user logs the alert. When registering, a request is indicated that must be observed, for example:
select * from TESTUSER.TESTTABLE; Where without sample code
Let's go in order:
- Allow the user to register continuous notifications;
GRANT CHANGE NOTIFICATION TO TESTUSER; - Set the value of the variable “JOB_QUEUE_PROCESSES” to a value greater than 0 (by default it is already greater than 0, but suddenly something broke on the default on your server);
ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4; - Create a project, add a link to the library Oracle.ManagedDataAccess.dll (can be downloaded from the Oracle website along with the ODP.NET package with ODAC, or pick it up via NuGet);
- Then you can just try.
Big code example
using System; using System.Threading; using System.Data; using Oracle.ManagedDataAccess.Client; namespace ChangeNotificationSample { public class ChangeNotificationSample { public static bool IsNotified; public static void Main(string[] args) { const string constr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myServiceName))); Validate Connection = true; Connection Timeout=180;User Id=TESTUSER;Password=myPass"; OracleConnection con = null; try { con = new OracleConnection(constr); OracleCommand cmd = new OracleCommand("select * from TESTUSER.TESTTABLE", con); con.Open(); // . OracleDependency.Port = 1005; // OracleDependency var dep = new OracleDependency(cmd); //, // , cmd.Notification.IsNotifiedOnce = false; // , cmd.Notification.Timeout = 300; // "- " dep.OnChange += OnMyNotificaton; //, , cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine(e.Message); } finally { if (con != null) { con.Close(); con.Dispose(); } } // , 10 while (IsNotified == false) { Thread.Sleep(100); } Console.ReadLine(); } private static void OnMyNotificaton(object sender, OracleNotificationEventArgs eventArgs) { Console.WriteLine("Notification Received"); DataTable changeDetails = eventArgs.Details; Console.WriteLine("Data has changed in {0}", changeDetails.Rows[0]["ResourceName"]); IsNotified = true; } } } I almost completely took the example from the documentation, only added it with a couple of important points in my opinion and reduced it a little. After the code is written, compiled, and the program is running, you can go and do something like:
insert into testuser.testtable (col1, col2) values (1,1); commit; When done, we should see this:
Hooray, everything works! More detailed information can be taken, for example, in the documentation . It is better to go straight to what is usually not indicated in the documentation - the difficulties encountered.
Minuses
For our case, there were not so many disadvantages that they outweighed the advantages of this approach, and some of them are difficult to call minuses, as nuances. However, it seems to me that the enemy must be known in person.
First of all, we tried to run it on our own and everything worked fine here. And, as is usually the case, nothing worked for the customer. Or rather, it didn't even take off. To figure out what the matter was, it turned out to be a rather non-trivial task. After you start a registration, you can see it by performing a special request:
select * from USER_CHANGE_NOTIFICATION_REGS; So, we saw this registration immediately after creation, but at the moment when the alert was supposed to work, the registration record simply disappeared. In the logs, unfortunately, we could not find anything that would explain this behavior. I had to take a chance and without looking to blame the firewall for everything. Fortunately, we guessed it. Therefore, my first recommendation is to remember to check the firewall and specify the port when registering notifications.
Not all registration requests are supported. It must be remembered that it may not be possible to read the data in one request and hang a notification on it. If there are many joines in the request, it is better to mentally prepare yourself for having to write a similar, but simple request. In addition, if you register an alert for a query with join-e, in fact, it will register as many alerts as there are tables in the query (each for its own table), which may waste resources.
By default, registrations are created without a timeout. That is, as it were, forever. As a rule, such registration is not needed by anyone, because the time of the application is usually limited. Such registration is very difficult then to pick out from the base, especially if there are a lot of them. The correct way, apparently, would be to delete the registration when you finish working with it (if you do it in the same connection, and there is a link to the OracleDependency instance, this is easy to do, just call the RemoveRegistration method on the instance of OracleDependency class). However, it seems to me that this method is only suitable for ideal worlds in which your applications never fall, communication channels never break and resources are always released. Otherwise, ultimately, we will have the same thing as without deletion, however, the consequences will begin to manifest later (just when we go into production). We solved the problem in this way - we began to always specify a timeout during registration. And a few seconds before its expiration, delete the old registration and create a new one (to minimize the time of no registration). The solution, of course, is not the most ideal, but in principle we are satisfied. If you have any ideas on how to do better, I will be very happy to rewrite this piece of code.
In order for the alerts to work faster within one application, we have divided into separate classes the logic for working with the database, the logic for notifying our objects of data change events, and the logic for managing both. Thanks to this, we were able to use one common notification mechanism and send notifications inside the application manually, without going outside (in oracle). For a user who works within his session and should see instantly the result of his actions, everything works well.
thank
That read my article to this place. I would be very happy with any feedback, from the criticism of my code and methods of presentation of the material and ending with the emotions you experienced in the process of exploring this technology. If you tell me about your experience with OCN, I will also be very happy and will be able to add some more material to my piggy bank.
Thank you in advance.
Source: https://habr.com/ru/post/260407/
All Articles