Some
years ago, my team and I encountered a strange error on the Oracle
Applications, the situation was strange because we never envisaged that we will
find ourselves in such unexplainable situation, but here we are, faced with it.
What is this strange situation? Trial balance was showing
an imbalance position and the cause of the imbalance could not
be explained or traced. Three weeks of round round the clock analysis of
transactions coming from all sources failed to give us a clue or hint for the
imbalance.
From my years of experience with Oracle
Applications, it is a well known fact that imbalance transactions coming from
the subledgers or legacy applications cannot be posted in the General Ledger,
rather, it would hang on the interface table. Unfortunately, an imbalance
journal was able to creep into the General Ledger undetected and was
posted.
Permit me to say that this particular client had four (4)
other legacy applications communicating and interfacing with Oracle EBS
applications. These legacy applications drop financial data on the interface
staging tables on a daily basis.
Having exhausted all available functional options available
to us, we turned our attention to the DBA guy for help. His involvement yielded a positive result, in the sense that the
GL_BALANCES tables that was spooled out indicated that there was an
imbalance.
The problem now was how to identify the journal or journal
batch(es) that caused the imbalance. From which of the applications? What
period? etc. A lot of questions but no answer. Lest I forget, a severity
one SR (tar) was raised with Oracle Support on this issue when it was initially
observed, and for upward of three weeks, there was no solution coming from
them. Nevertheless, Oracle Support gave us maximum support during this period.
With a glimpse of hope received from the DBA guy, we asked
him,
Is it possible to rollback all the postings that have been
made to General Ledger from inception?
What are the likely effects or drawback from taking this
action?
Does Oracle supports transaction rollback?
Has it ever been done before and what was the result and
where?
He was asked to go do his research and get back to us with
his findings the following day. We raised rollback service request question with Oracle
Support. Oracle replied that they can't give us an affirmative answer as to whether General Ledger rollback was
possible or not, but should it be possible, they would never advise us doing
that.
We carried out our individual research on the General
Ledger rollback possibility. We reached out to some more experienced DBAs on
this issue, asked them whether they have carried out such an assignment before
and what was the outcome. Interestingly, 98% of the experienced DBA said, they
haven't carried out such assignment task before. And would align with Oracle
Support not to undertake such a risk venture for it may be costly.
So armed with all our findings, we met, evaluated all the
finds, and asked ourselves some serious questions. Please note, the client was
seriously concerned and had given us a "no going back date" to
resolve the issue.
With that ultimatum from the client and no solutions coming
forth from Oracle Support, we concluded that -
1. Oracle support did not say it is possible or not. They
said, even if it is possible, it won't support such.
2. None of the DBAs we asked had ever carried out such a
task. It means they have never encountered such situation.
3. Our DBA colleague was mandated to craft or write out the script to achieve the rollback.
4. The client DBA team were mandated to clone the
production instance unto the DEV and Training Instances.
5. We adjourned and agreed to meet the following day.
We reconvened as planned. The DBA said, he has the script
but has not tested it. He projected the script and we reviewed and fine tuned together. The next thing was to test the script on the
training instance.
The script was applied on the instance database, and like
magic, it worked. The script created and backed up the following GL tables
-
GL_BALANCES
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_PERIOD_STATUSES
GL_SETS_OF_BOOKS
GL_TRANSLATION_TRACKING
GL_TRANSLATION_STATUSES
GL_MC_BOOK_ASSIGNMENTS
GL_DAILY_BALANCES
GL_CODE_COMBINATIONS
And carried out other processes as specified in it, which includes deleting all the transactions posted in the GL_Balances table.
After carrying out the cleanup process on the GL_Balances table leaving no single data, we returned GL_JE_BATCHES data, GL_JE_HEADERS data and GL_JE_LINES data from the backup, back to the their various base tables and reviewed each batch alongside its header and line transactions. In the process of our reviews, we discovered the Journal Batch that caused the imbalance. This particular batch, its headers and lines
were isolated, others were reviewed and posted back and the trial balance report program was ran and the trial balance report balanced.
The offensive journal batch that caused the imbalance was
from one of the legacy system; the batch was reviewed, corrected and reposted.
The trial balance was rerun after the correction, and it balanced.
The same process was carried out on the development instance
and the result was the same. Having satisfied ourselves that our experiment
accomplished its targeted result, we elected to move the solution to
production instance. However, before doing that, we asked for the clone of the
production of the previous day.
On the
day the solution was to be migrated to the production instance which was on a
weekend; we decided to run the solution on two instances concurrently. First on
the clone instance, once it achieved the desired result, we replicate on the
production and so on.
Three weeks of agony was resolved in five hours and everyone was happy, going home to have a good cool drink and night rest.
Kudus to my DBA guy Ashok and support from Venkat. Not forgetting my brother from a different mother Adams Jubrin, a jolly nice fellow and great consultant to work with anytime, anyday and anywhere.
No comments:
Post a Comment