This blog post is intended for those who program or create applications to run on clustered Oracle Database RAC environments. This method can prevent a future operational problem.
I present this case as an improvement included in the mediation process described in the blog post “https://thinkincredible.intraway.com/2015/08/08/fast-integration-between-transactional-systems/”. It looks after the operation and ensures the continuity of processes that are executed within the Oracle engine.
The control that I will share below may be applied to stored procedures, packages or any code that runs inside Oracle’s engine and its objective is to perform the necessary actions so that the logic implemented is not paralyzed if a RAC’s node becomes out of service.
For the particular case in which the solution was applied, the mediation of orders works by taking blocks of information and processing them sequentially. These blocks are opened and closed by different processes, depending on the detailed information inside them. (For further information, please read the blog post shared above)
The mediation process uses the technique of block processing, based on the FIFO rule and “Each block is processed when its predecessor is closed”.
Based on the above, if some problem occurs within the cluster node while the process is running, it will “disappear”. In this case, the mediation block would stay open forever. This would cause a full stop in the mediation process, impacting heavily in the normal operation of the customer.
The way to avoid such kind of problems is mainly based on the storage of sessions’ data in special tables. This information represents the detailed data to identify each thread serving the mediation blocks.
To explain this point, I will separate the presentation into 2 groups: “1. Session Data Collection” and “2. Active Session Validator”
- Session Data Collection
Each process involved in the processing flow of order mediation will keep its own session data. In the particular case of an Oracle RAC, they are: SID, SessionID, Instance, Serial # (which are obtained by consulting the gv$session table or using the native SYS_CONTEXT function).
As indicated, these references must be persisted in special tables. These sources of information will be consulted by the other presented process in the following point.
- Active Session Validator
The Active Session Validator works as a Garbage Collector or Watchdog, which will evaluate the health of processes that deal with mediation blocks. This is mainly done by comparing the detailed information of the session of each thread listed as “open” versus the information found in the internal session table RAC (gv$session).
The output of this validation will be the element for making a decision to run or not corrective actions.
- Successful validation: Information stored in the control table that is marked as “running” can be found in the detailed table of RAC’s sessions (gv$session).
- Action to successful validation: No action is taken.
- Unsuccessful validation: Information stored in the control table that is marked as “running” cannot be found in the detailed table of RAC’s sessions (gv$session).
- Action for an unsuccessful validation: Mediation blocks lockdown and sends notifications to the monitoring system.
Applying this improvement, after the downtime of the nodes of an Oracle RAC, we can ensure the continuity of the operation with a very low impact.
In addition, active sessions control data information can be used to analyze other variables such as performance.
