Assume you are closing your month-end periods, running Create Accounting for Finance and OPM modules and finance stakeholders report this issue –
So, first thing we do is check request logs for Journal Import program–
Checking alert log gave me,
After raising ticket with Oracle, they gave us Patch – 23268430.
Readme gave us below information, online patchable may sound familiar to most of view, but was something new for me, so thought of documenting this.
Going through 761111.1 got some key highlights that are worth noting –
1. Online RDBMS patches are special patches that are applied on live instance, no shutting down of database instance.
2. A shared library is constructed with relevant fixes.
3. They internally use oradebug to apply patch.
4. Oradebug is responsible to inform each process to map shared library into their address space, original function changes to jump to new version in shared library and resolve references to right memory location for static functions
Some common DBA questions popping up now –
1. Which patch is an online rdbms patch?
Not all patches are RDBMS online applicable, we can confirm for patch to be online applicable in 3 ways –
Readme.txt
cd <PATCH_TOP>/23268430
opatch query -all online
cd <PATCH_TOP>/23268430
ls
etc/ files/ online/ README.txt
etc/ files/ online/ README.txt
2. Limitations and Implications?
They support only few OS versions.
Additional memory consumption.
Process start time delays
Use-case –
Coming back to our scenario, lets analyze both situations assuming client is a single node ERP system.
ONLINE MODE –
OFFLINE MODE –
We were having those days where it is difficult to shutdown system, with end-users from different modules performing activities like, closing periods, generating payroll, closing batches, etc. Applying this patch in online mode can be done as follows –
opatch apply online -connectString <SID>:<USERNAME>:<PASSWORD>:<NODE>
opatch lsinventory -details | grep online
Check for folder –
cd $ORACLE_HOME/hpatch
orapatchSTAGE.cfg is like a flag controller to enable/disable this hot patch.
Whenever this hotpatch is rollbacked, only orapatchSTAGE.cfg is updated.
Over the weekend, when it was possible to take downtime, we rollbacked this online patch and applied it as regular with
opatch rollback -id 23268430 -connectString STAGE
SQL> shut immediate
opatch apply
Subsequent startup –
opatch lsinventory -details | grep 23268430
Conclusion –
1. We bought some time and delayed downtime to apply this rdbms patch online without disturbing business activities during peak hours.
2. Though this feature was introduced in 11.1, it is not being applied on a larger scale due to its implications like per process memory overhead.
3. Again, this feature is overshadowed by Rolling patches in RAC environment.
Happy DBAing 😊
