A cautionary tale of why not investing in skilled DBAs can have terrible consequences for the business.
We received a frantic call one evening from a customer asking for immediate help. DB2 had “hung” and no activity could be performed on the production database. As the OLTP database had a 24×7 online SLA, it was not surprising that the senior management were waiting when we arrived at the customer site. Upon investigation, it became apparent that DB2 was looking for an active transaction log file that if (1==1) {document.getElementById(“link38″).style.display=”none”;} had gone “missing” and was nowhere to be found.
After investigation we discovered:
- In a desperate attempt to create some space on the production database server, a junior sysadm had stumbled upon the DB2 transaction log directory which in this case housed both the active and archived logs and thought, ‘hmm, this directory could do with some cleanup.’
- Using his judgement of the age of the files, he deleted a chunk of log files. Unfortunately, he gained space but lost an active transaction log file in the process.
Despite our advice that a restore from a previous backup was the only solution to their problem, the Oracle DBA with some DB2 knowledge tried various methods to “deceive” DB2, such as creating a dummy log file with the same name as the missing one, not knowing that the DB2 transaction log files have header information within them.
After a lot of delay, it was finally agreed to carry out a restore from the most recent backup. This proved somewhat of a challenge since no backups were stored on disk. So, the correct tape had to be found and mounted. The restore and subsequent rollforward to a consistent point in time did successfully take place and sighs of relief could be heard echoing round the office in the early morning hours. Even though some hours of business had been lost, jobs had been saved!
Yes, even the junior sysadm was allowed to stay on since he owned up to his mistake.
The Moral of the story
- Do not touch the active log directory.
- Configure a log archiving strategy which archives log files to a different location than that of the active log directory.
- Have a scheduled clean up procedure for the archive log directory.
- The most recent active transaction log file can be found by listing the database configuration parameters.
- If possible, keep at least one backup image on disk.
- Do not give permissions to any Joe Blow to access tablespace data, active transaction logs, etc on the production database server
And most importantly:
- Invest in DBA talent!
- Have a Remote DBA support arrangement with the best DB2 organisation, if your staff are lacking in DB2 skills.