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 Habrahabr. I want to talk about the experience of using Object Change Notification in Oracle. About how to find out that the data has changed without making a request.

So why do we need Object Change Notification (OCN)


We needed to observe the data changes in the database. The solution that first came to mind was a simple one - updating the data over a given time interval (for example, once a minute). The disadvantages of this approach are obvious - the data may not have changed over the selected period and then we just load the server and the data transfer channel (the data that we observe is very voluminous). Or the data will change at the beginning of the time interval and we won’t know about it until the time runs out (this is not so critical, but the first is likely). Given that there could be quite a lot of customers, the first minus is really serious. Fortunately, we 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 through a special interface that the data it observes has changed.

Details of how this works inside is easy to google; I thought that you should not inflate the article with information, which is already abundant, so I’ll tell you only briefly.



First, you must enable the server to use request registrations and allow the use of concurrent notifications. Then, the user registers an alert. When registering, a request is indicated that must be observed, for example:
select * from TESTUSER.TESTTABLE;
After the data in the result set changes, oracle will notify the client about this (either send a special packet over the registered ip, or execute the registered stored procedure). Support for this mechanism is built into the Database Provider for .NET and Java. Since we wrote our application in .NET, I will continue to speak in its context (although most of the things, with the exception of the sample code, I suppose, do not depend on this, because the most interesting thing is happening on the server).

Where without code example


Let's go in order:
  • We allow the user to register continuous notifications;
    GRANT CHANGE NOTIFICATION TO TESTUSER;
  • Set the value of the "JOB_QUEUE_PROCESSES" variable to a value greater than 0 (by default it is already greater than 0, but suddenly something has broken the defaults on your server);
    ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;
  • We create a project, add a link to the Oracle.ManagedDataAccess.dll library (you can download it from the Oracle website along with the ODP.NET with ODAC package, or pick it up via NuGet);
  • Then you can just try.

Great 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 an example from the documentation, only supplemented it with a couple of important points in my opinion and slightly reduced it. After the code is written, compiled, and the program is running, you can go and execute something like:

insert into testuser.testtable (col1, col2) values (1,1); commit;

Having completed, we should see this:



Hooray, everything works! More detailed information can be taken, for example, in the documentation . It’s better to go straight to the point that the documentation usually doesn’t indicate - the difficulties encountered.

Minuses


For our case, there were not so many minuses that they outweighed the advantages of this approach, and some of them can hardly be called minuses, so, nuances. However, it seems to me that the enemy should be known in person.

First of all, we tried to run it at home and everything worked fine for us. And, as it usually happens, nothing worked for the customer. More truly, it didn’t even take off. Finding out what’s the matter turned out to be a rather non-trivial task. After you register, you can see it by completing a special request:

select * from USER_CHANGE_NOTIFICATION_REGS;

So, we saw this registration immediately after creation, but at that moment when the notification should have worked, the registration record simply disappeared. Unfortunately, we could not find anything in the logs that would explain this behavior. I had to take a chance and without looking to blame the firewall for everything. Fortunately, we guessed right. Therefore, my first recommendation is to remember to check the firewall and specify the port when registering notifications.

Not all registration requests are supported. We must remember that it may not be possible to read the data in one request and hang a notification on it. If there are a lot of joins in the request, it’s better to mentally prepare yourself to write a similar but simple request. In addition, if you register an alert for a query with join, you will actually register as many alerts as there are tables in the query (each on its own table), which can waste resources.

By default, registrations are created without a timeout. That is, as it were, forever. As a rule, nobody needs such registration, because the application’s working time is usually limited. Such registration is very difficult to pick out from the database, especially if there will be a lot of them. The correct way, most likely, would be to unregister when you finish working with it (if you do this in the same connection, and there is a link to an OracleDependency instance, this is easy to do, just call the RemoveRegistration method on the instance of the OracleDependency class). However, it seems to me that this method is only suitable for ideal worlds in which your applications never crash, communication channels never break, and resources are always freed. Otherwise, ultimately, we will have the same thing as without deleting, however, the consequences will begin to manifest later (just when we go into production). We solved the problem this way - we began to always indicate 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 absence of registration). The solution, of course, is not the most ideal, but in principle we were satisfied. If you have ideas on how to do it better, I will be very happy to rewrite this piece of code.

In order to make alerts work faster within the framework of one application, we separated into separate classes the logic for working with the database, the logic for notifying our objects about 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 as part of his session and needs to see the result of his actions instantly, everything works well.

thanks


That they read my article to this place. I would be very happy for any feedback, from criticism of my code and methods of presenting the material to the emotions you experienced in the process of getting to know this technology. If you tell me about your experience with OCN, I will also be very happy and can add some more material to my piggy bank.

Thanks in advance.

Also popular now: