This is the first is a series of blog posts from my years working in the Oracle field support organization known as Advanced Customer Services (ACS). #DatabaseParamedics

Postmortem analysis is often critical to avoiding problems in the future.  In a cruel twist of fate, one customer suffered a failed recovery immediately following their annual DR test.  They had spent 3 long, sleepless days testing all of their recovery mechanisms, so everything should have been fine. We discovered the source of the problem during postmortem, but I’ll tell more about that later. 

Failure Scenario

The entire IT organization had just finished their annual DR test over a long weekend. All of the testing came off without a hitch, and the team was satisfied with a job well done.  All databases and applications came up successfully at the DR site according to plan, and the team celebrated their success. On the following weekend, a database upgrade was completed successfully on Saturday afternoon. Everything ran without any issues until the following weekend, when they suffered a hardware failure on their disk array. The DR test 2 weeks earlier had shown their backup/recovery solution had been working perfectly, but this time the recovery failed.

How could a flawless backup/recovery solution suddenly go bad in such a short time?  As we shall see, the database upgrade played a role in this failure and for a very simple reason. The customer had been using a fatally flawed backup script without solid error trapping.  The database was upgraded to a new version, but the backup script was not updated to reflect the new software location. The backup script failed, but the error code was not properly trapped an reported.

Potential Impact

Database recovery obviously failed because the backups had been failing for a week.  The last good backup was taken over a week before the disk array crash, so the customer was faced with losing an entire week of transactions.  The IT team was looking at a loss of data on their corporate financial system in the middle of their annual financial close. Not only would their end users be losing time due to the outage, they were also facing a week of rework due to lost data!  The business was facing delays in SEC (Securities Exchange Comission) filings and potential fines.  This was a very serious situation.

Another DUL Job

Going back to the last good backup (taken a week before) was going to be painful for the business.  The only copy of the database was corrupt and wouldn’t open because the database files were inconsistent.  My team and I had a solution, but it wasn’t pretty (still some potential data loss) and required special expertise and a proprietary tool called the Data Unloader or DUL for short.

Data Unloader is a proprietary tool used by Oracle ACS (Advanced Customer Services) to extract data from corrupted databases. DUL is a tool of last resort, and is only used under a special services contract.  DUL is a virtual guarantee of data loss, but can often recover more data than other methods. The ACS team typically uses all of the other tricks in the recovery book along with the DUL tool. In this case, we used DUL to extract all of the data, then rebuilt the database from scratch.  In the end, we lost just a few transactions, but saved almost a week of effort by the business.

Postmortem & Lessons Learned

The postmortem analysis showed that a simple scripting error can cause failed backups.  Our main recommendation was to code the script to trap for positive success rather than simply trapping for errors.  This is based on the principal that absence of failure does not necessarily mean success.

The other lesson we learned from this engagement is that you never truly know the state of your backup until it comes time to recover. This customer had followed best practices by performing comprehensive tests of their backup/recovery solution, but a change was introduced less than 1 week later and caused a silent failure of the solution.

What customers need is a solution that will validate backups and validate the ability to recover using those backups instead of waiting until they are needed.