The Seven Deadly Habits of a DBA . . and How to Cure Them

The Seven Deadly Habits of a DBA . . and How to Cure Them
by Paul Vallée

Calling widespread bad habits in database administration "deadly" may seem extreme. However, when you consider the critical nature of most data, and just how damaging data loss or corruption can be to a corporation, "deadly" seems pretty dead-on.

Although these habits are distressingly common among DBAs, they are curable with some shrewd management intervention. What follows is a list of the seven habits we consider the deadliest, along with some ideas on how to eliminate them.

Habit #1. THE LEAP OF FAITH: "We have faith in our backup."
Blind faith can be endearing, but not when it comes backing up a database. Backups should be trusted only as far as they have been tested and verified.
Cures:
§ Have your DBAs verify that the backup is succeeding regularly, preferably using a script that notifies them if there's an issue.
§ Maintain a backup to your backup. DBAs should always use at least two backup methods. A common technique is to use those old-fashioned exports as a backup to the online backups.
§ Resource test recoveries as often as is practical. An early sign that your DBA team is either overworked or not prioritizing correctly is having a quarter go by without a test recovery. Test recoveries confirm that your backup strategy is on track, while allowing your team to practice recovery activities so they can handle them effectively when the time comes.

Habit #2. GREAT EXPECTATIONS: "It will work the way we expect it to. Let's go ahead."
Although not user friendly in the traditional sense, Oracle is very power-user friendly— once you've been working with it for a while, you develop an instinct for the way things "should" work. Although that instinct is often right, one of the most dangerous habits any DBA can possess is an assumption that Oracle will "just work" the way it should.
Cures:
§ Inculcate a "practice, practice, practice" mentality throughout the organization. DBAs need to rehearse activities in the safe sandbox of a test environment that's designed to closely mimic the behaviour of the production system. The organization needs to allow the time and money for them to do so.
§ Pair inexperienced DBAs with senior ones whenever possible—or take them under your own wing. New DBAs tend to be fearless, but learning from someone else's experience can help instill some much needed paranoia.
§ Review the plans for everything. It's amazing how often DBAs say, "I've done that a hundred times, I don't need a plan." If they're heading into execution mode, they absolutely need a plan.

Habit #3. LAISSEZ-FAIRE ADMINISTRATION: "We don't need to monitor the system. The users always let us know when something's wrong."
If you depend on the users to inform the DBA team that there's a problem, it may already be too late.
Cures:
§ Install availability and performance monitoring systems so that issues are identified and resolved before they cause service-affecting failures.
§ Avoid post-release software issues by working with developers and testers to ensure that all production-ready software is stable and high-performance.

Habit #4. THE MEMORY TEST: "We'll remember how this happened, and what we did to get things going again."
It may seem impossible that a DBA team would forget a massive procedure that took them weeks to get right, and yet it happens all the time. In order to prevent recurring mistakes and take advantage of gained experience, documentation is essential.
Cures:
§ Require that your DBAs maintain a comprehensive documentation library and activity diary, including a significant level of rationale, syntax, and workflow detail.
§ Provide your team with groupware on your intranet so that these documents become searchable in an emergency.
§ Enforce the discipline of documentation and check it periodically. Ask your DBAs: When was this tablespace created, by whom, and with what SQL? What tasks were performed on a particular day? If they can't answer quickly, you'll know they've gone back to relying on memory.

Habit #5. THE BLAME GAME: "Don't look at me, it's the developer's fault that SQL is in production"
Some DBAs have a real "us versus them" mentality when it comes to developers in their organization. They see themselves not as facilitators helping the developers develop quality code from a database standpoint, but rather as guardians who prevent poor-quality code from making it into production. This might seem like semantics, but a confrontational relationship between developers and DBAs results in a lack of developer initiative and significant slowdowns in release cycles.
Cures:
§ Select DBAs who understand it's their responsibility to work as an integrated team with the developers they support.
§ Cultivate a team attitude by structuring continuous DBA involvement in every project rather than at review milestones.
§ Consider assigning an individual DBA in a developer support role. If it's clearly in the job description, there's more motivation to do it well.

Habit #6. THE SOLO ACT: "I know what I'm doing and don't need any help."
Database administration is increasingly complex and even the most senior DBAs can't possibly know every last detail. DBAs have different specialties, which need to be culled and utilized. When DBAs feel like they know, or should know, everything, they don't ask questions and miss out on valuable knowledge they could be gaining from others.
Cures:
§ Foster a teamwork culture where it's acceptable for DBAs to admit they don't know the answer and to ask for help.
§ Encourage your DBAs to seek out an outside peer group as a forum for brainstorming and testing their assumptions. No single person can match the expertise and experience of even a relatively small group.
§ Provide a safety net of tech resources such as reference materials, courses, and outside experts or consultants on call.

Habit #7. TECHNO-LUST: "Things would work so much better if only we had..."
DBAs are often on top of the latest technology, which can help them do a superlative job. But when the desire for new technology causes DBAs to recommend unnecessary hardware purchases or software add-ons, costs tend to skyrocket quickly—as do problems.
Cures:
§ Never upgrade your hardware infrastructure without first exhausting all tuning opportunities. Remember, ten years ago enormous enterprises were run on servers one-tenth the capacity—all thanks to necessity and skill.
§ Never consent to using advanced or new features until you're well aware of the ongoing maintenance commitment and resulting costs.
§ Watch out for DBA support software that presents friendly GUI interfaces for difficult tasks. This type of interface allows a beginner DBA to act as an intermediate DBA under certain circumstances, but simultaneously prevents that beginner from learning the actual skills behind the tasks. Moreover, these tools tend to hide real risks from the DBA, making potentially damaging activities as easy as point-and-click.

Whether it takes a twelve-step program or one tiny adjustment, all of these deadly DBA habits can be kicked. Of course, the first step is recognizing the problem. By starting with this list and doing a careful inventory of the successes and failures in your team's database administration, you'll be well on your way to finding a cure.

-------------------------------------------------------------------------------------------------
Paul Vallée is the Executive Chairman and Founder of Pythian, a leading global database application and infrastructure services company for Oracle, MySQL and SQL Server.

Toad World PIPELINE Newsletter (May '10 Issue) has my Second Technical Paper

The May 2010 Edition of Toad World PIPELINE Newsletter has published my second Oracle Technical Paper "Database Disaster Recovery using only RMAN Backups".

Infact, they asked me to re-write the paper using Oracle 10g or 11g so that we could relate it with the supported versions. I have chosen Oracle 10g R2 (10.2.0.4) Standard Edition on Windows O/S to demonstrate the same.

Here is the link to Toad World PIPELINE Newsletter (May '10 Issue):
http://www.quest.com/ecard/28941/default.htm

Hope it is a nice read.

Published my Second Oracle Technical Paper

Today, I have successfully published my Oracle Technical Paper titled "Database Disaster Recovery using only RMAN Backups" under My Oracle Support Community (formerly known as Oracle Customer Knowledge Exchange)
Link: https://communities.oracle.com/

The technical paper demonstrates how an Oracle Database can be recovered or reconstructed by using only the RMAN Backup files in case of a complete server crash. On this blog, you know it as "Disaster Recovery using RMAN Backups".

The access to the published document in My Oracle Support Community would require customer logon credentials. So if you are interested to know more about my technical paper, feel free to email me at zk.oracle@gmail.com for the PDF file.

Disaster Recovery using RMAN Backups

Every DBA has to ensure that his hours spent in planning and testing the backup-recovery strategy works, especially it should be relied upon in any unexpected scenarios occur. We DBAs are always tend to be at ease when our scheduled backup jobs are performing well and are error-free, followed by couple of recovery testings scenarios.

Nevertheless, there are always scenarios which may be left unanticipated during the B/R Testing. One such scenario was haunting me since couple of days. And it took me a while to figure it out (due to known and unknown recovery conditions).

Let's take an example. Say I have a complete RMAN Backup including archivelog backups on Disks. Let's assume that the backups are all in recoverable conditions. And then, suddenly the Production Database Server goes off. Boom!!! The only way to recover the server is to rebuild it or to have us recover the Instance from the RMAN Backups on to another Server. In this post, we will see how to recover or reconstruct a Database by only using the RMAN Backups.

2 pre-conditions to use the RMAN Backups for recovery are:

1. Any Server should be available in a state where Oracle Product can be installed.
2. Same version of Oracle Product needs to be installed with which the RMAN Backup was taken

In my case the O/S is Windows and the Oracle Database Product is 9i R2 (9.2.0.8) Standard Edition.

The following steps would outline the procedure of recovering the Database using only RMAN Backups:

1. Create the Oracle Service
2. Create the relevant folders under admin and oradata folders
3. Restore the spfile from autobackup
4. Restore the controlfile from autobackup
5. Restore the Database
6. Recover the Database
7. Open the Database resetting the logs

We create an Oracle Service using oradim without passing any parameter file

c:\>oradim -new -sid DBTEST -intpwd DBTEST

Now, we need to create the relevant folders for Oracle Database, as follows:

1. Create BDUMP, CDUMP, and UDUMP folders under 'c:\oracle\admin' folder,
2. Create DBTEST folder under 'c:\oracle\oradata' folder

We connect to RMAN and start the Recovery process. First we need to set the DBID of the Database. At times we may or maynot know the DBID of the Database. There are 2 ways to find the DBID:

1. If any RMAN Logs are maintained, the DBID can be found when the initial connection is made to the target
2. If autobackup is enabled ans has the autobackup format set, with say '%F', then the filename of the autobackup has the DBID.
   In my case the file name is 'c-1103102985-20100216-02', and the DBID is '1103102985' in the filename.

And then we need to set the autobackup location which will help us identify the location where all the RMAN files.

C:\>rman
Recovery Manager: Release 9.2.0.8.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)

RMAN> set dbid=1103102985;
executing command: SET DBID

RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file C:\ORACLE\PRODUCT\9.2.0\DB_1\DATABASE\INITDBTEST.ORA'
trying to start the Oracle instance without parameter files ...
Oracle instance started
Total System Global Area 97590688 bytes
Fixed Size 454048 bytes
Variable Size 46137344 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\\testdb\dbtest\%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Then we have to restore the SPFILE from the autobackup file. We can either give the keyword "autobackup" or directly reference the path and filename of the autobackup in the 'from' part of the command. I did the latter.

RMAN> restore spfile to 'c:\spdbtest01.ora' from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: c:\c-1103102985-20100216-02
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-FEB-10

Now, we need to shutdown the instance and startup with the restored parameter file.

RMAN> shutdown immediate
Oracle instance shut down

Remember that we have just restored the SPFILE in a non-default location and hence we would require referring a PFILE that should point to the SPFILE so as to start the instance. We need to create a text file, 'init.ora', and then mention the path and name of the restored SPFILE in it. The initDBTEST.ora would contain the following statement:

SPFILE=C:\spdbtest01.ora

And then, we need to reference the newly created PFILE to start the instance.

Alternately, you could also restore the SPFILE to the default home location, under DATABASE directory, where Oracle automatically looks for an SPFILE and thus avoid creating the PFILE to point at the SPFIL. I have chosen the former option for the demonstration.

Start the instance in NOMOUNT state using the parameter file pointing to the spfile.

RMAN> startup force pfile=’C:\initDBTEST.ora’ nomount
Oracle instance started

Total System Global Area 907482184 bytes
Fixed Size 455752 bytes
Variable Size 276824064 bytes
Database Buffers 629145600 bytes
Redo Buffers 1056768 bytes

Now, we need to restore the controlfile to bring back the database. Again here, we can either give the keyword ‘AUTOBACKUP’ or directly reference the path and filename of the autobackup in the ‘FROM’ part of the command.

RMAN> restore controlfile from "c:\c-1103102985-20100216-02";
Starting restore at 16-FEB-10
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\ORACLE\ORADATA\DBTEST\CONTROL01.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\DBTEST\CONTROL03.CTL
Finished restore at 16-FEB-10

With the restored controlfiles, now we are in a position to restore the Database, for which we need to mount the Instance first and the fire the restore command. Once restore is complete we need to fire the recover command for applying the archivelogs.

RMAN> alter database mount;
using target database controlfile instead of recovery catalog
database mounted

RMAN> restore database;
Starting restore at 16-FEB-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=16 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\DBTEST\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\DBTEST\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\DBTEST\DRSYS01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\DBTEST\EXAMPLE01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\DBTEST\INDX01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\DBTEST\TOOLS01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\DBTEST\USERS01.DBF
.
.
.
restoring datafile 00023 to C:\ORACLE\ORADATA\DBTEST\CWMLITE01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_1_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 2
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_2_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 3
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_3_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 4
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_4_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 5
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_5_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 6
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_6_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 7
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_7_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 8
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_8_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restored backup piece 9
piece handle=\\TESTDB\DBTEST\DBTEST_01L65FRL_9_1_20100216 tag=FULL_160210_1200 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 16-FEB-10

RMAN> recover database;
Starting recover at 17-FEB-10
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_03L65IJE_1_1_20100216 tag=FULL_160210_1230 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00002.001 thread=1 sequence=2
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=\\TESTDB\DBTEST\DBTEST_05L65Q1B_1_1_20100216 tag=FULL_160210_1430 params=NULL
channel ORA_DISK_1: restore complete
archive log filename=C:\ORACLE\ORADATA\DBTEST\ARCHIVE\ARC00003.001 thread=1 sequence=3
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: =========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =========================================================
RMAN-03002: failure of recover command at 02/17/2010 10:25:28
RMAN-06054: media recovery requesting unknown log: thread 1 scn 34558001

Don't Panic when you see the above RMAN Error Message. You just need to open the database with resetlogs as the recovery is over.

RMAN> alter database open resetlogs;
database opened

RMAN>

Looks simple! Yet it is a critical test case, as this is the what your full RMAN backups should do, i.e. A Full Recovery.

May'09 Disaster Recovery: Production brought back to Life!

The test setup for selected users was functioning smoothly. This bought us sometime to look for the bad Disks in local Market. The IT Administrators somehow managed to get the Disks. By the en of the 3rd Day, they replaced and rebuilt the Disks in the RAID5 Configuration. The O/S Partition was formatted and server O/S was upgraded to MS Windows 2003 Server [Standard Edition].

We managed to recover most of the data on the RAID5 Disks, especially the Application. The database files were physically recovered, but I considered rebuilding the Database on the Server for the following reasons:

1. The Server O/S was upgraded
2. Oracle 9iR2 Software had to be reinstalled
3. Data had changed in the last 3 days, as people were using the Secondary Setup. So, I needed to clone the production as the Secondary Setup.

On the 4th Day, I cleared the Database Files on the Production Server and installed the Oracle 9iR2 software and patched it to 9.2.0.8. The Application was intact, so I did not have to do any thing on those lines. Now, I had 3 options to setup the Production Server.

1. Using export dump of the Secondary Instance, create the Production Oracle Database.
2. Using RMAN, clone the Production Oracle Database with the "Duplicate Target Database" command.
3. Or, simply clone the Production Oracle Database using the Cold Backup of the Secondary Database.

The 3rd option was simple and effective. With assistance from one of my colleague, we performed the Cloning using Cold Backup. And, Within an hour the Production Instance was up.

The Application Testing was carried out to check the Application were running smoothly and for the Data Validation. Once the testing was successful, we registered the database in the Recovery Catalog, and took a full database backup. We had to ensure that the Oracle Services were owned by the Domain Administrator Account and not the Local Account for registering the database and taking a Full Database RMAN Backup ensuring that the Controlfile Autobackup and SPFILE backup was on the shared location along with the RMAN Backups.
[Note: 145843.1 How to Configure RMAN to Write to Shared Drives on Windows NT/2000]

Once the backup was complete, the Temporary Setup was shutdown and we brought the Production Server online for all the users.

In the next 2 weeks, the following necessary arrangements were made for the short-comings seen in the Disaster Situation:

1. Application Backup is daily ensured to Tapes
2. Application files, as of 13th May 2009, have been backed up to DVDs. Every 15 days, Application files backup to DVDs is being ensured.
3. A Temporary Server was arranged by IT Administrators and has been cloned (using RMAN) same as that of the Production Server. Scheduled Jobs run at 3 intervals so as to Clone the Secondary database is put in place. The cloning process takes more than 2 hours to complete. In case of an unforeseen disaster, we can easily switch to the Secondary Server with a minimal Data Loss.
4. Source Code Backup and its relevant Document Control is strictly ensured prior to moving to Production.
5. After the Production Server’s Operating System Upgrade to Windows Server 2003, the RMAN backup location has been changed to SAN Storage location, which is further backed up to Tapes by the IT Administrators. The earlier RMAN backup performance issue of 13 hours has been resolved. Now, the backup completes in less than an hour. You can read about it here.
6. For the next 3 months, we will be carrying out planned monthly recovery of complete Application from the Tapes and/or the DVDs. Once the recovery simulation comfort level is attained, we can carry out the simulation every Quarter.
7. Finally, the new Server Procurement Process has been started, and which server to purchase has been finalized.

We have setup the Secondary Database on a Temporary Server. Due to our limitations on Oracle Licensing (Standard Edition), we are not able to use the Oracle Dataguard, as it is only available as part of the Enterprise Edition. Being simple and effective, I have currently opted to use RMAN Duplicate Target Database to clone the Secondary Database on the Temporary Server. Alternately, I am trying to find a solution for manual replication via own scripts, as the current cloning method adds some data loss disadvantages. The Cloning has been scripted and scheduled to run at 3 intervals in a day:

• 4 am: Clone after the Full Database RMAN Backups
• 11 am: Clone in the mid of the Working Day (after all Archivelog Backups are available up to 11 am)
• 4 pm: Clone at the end of the Working Day (after all Archivelog Backups are available up to 4 pm)

Once the Production System is migrated to a new server, the old server can be used as a Standby Database. Also, we will be migrating the production Database from Oracle 9iR2 [9.2.0.8] to Oracle Database 10g [10.2.0.4].

May'09 Disaster Recovery: Production Lost

Finally, I am able to write on the Production Server Crash that happened at 07:25 am on 14th May '09.

Our server has a 4 disk configuration for RAID5. We had encountered a disk failure couple of weeks back, which was recovered the same day. That was when our ERP Database Files had got corrupted. You can read about it here.

This time, 2 Disks of the RAID5 Configuration crashed and to survive a crash in a RAID5 setup you need minimum 3 disks running. So, everything along with the server went down. The server was hosting our Oracle Forms6i-based ERP Application as well as our Oracle ERP Database.

Initially, I was a little confident as I had my Full Database RMAN Backups available as well as last night's full database export dump file. For the disks in RAID5 Configuration, being kind of an obsolete model, we were not available to carry out the replacements. Our supplier told us that the disks may be available in the Australian Warehouse, and it would take 2-3 weeks to send it to us. As far as business continuity is concerned, there was none in place and we had to make a temporary setup available as earliest as possible. The IT Administrators immediately arranged for a Server where I could build and bring the whole ERP online. I started the Oracle Product Installation [9.2.0, and patched it to 9.2.0.8]. The Oracle Product Installation and Oracle Database creation was too slow. Literally slow, to the extent that it took almost 5-6 hours and the database creation still did not complete. The Server was a Pentium-III 1 GHz single processor, with 1 GB RAM.

Meanwhile, I found two severe concerns that would hinder the re-build/recovery of the ERP.

1. The Application Backup was supposed to be on the DDS tapes, the tape drive of which was in-built with the production server. To access the tape drives, we would have to bring back the server, as we did not have any spare tape drive.
2. The controlfile autobackup and spfile Backup related to the Full Database RMAN Backup were on the crashed server (which should have been on the backup location in te first place). This means that all my RMAN Backups were of no use, until the controlfile autobackup file was recovered from the crashed server's disks.
   Later, I found out that one of the batch jobs was resetting the controlfile autobackup location from "\\testdb\orcl" to "D:\ORACLE\ora92\DATABASE\...".

I had the last night's full export dump, and had no choice but to create a new database with the same. Because the installation activity on the alternate server was quite slow, I started the Database Creation on the Test Server in parallel, which was a Pentium IV with 2 GB RAM. The Database Creation finished in less than 1 hour. Then, I started the full database import, which took another 3-4 hours to complete. If I had the RMAN Backups available, this whole process would have finished in 3 hours or less. Nevertheless, the import activity provided some benefits to me which were only possible in the migration of the database to a new server. I will be discussing about this in the next post.

Meanwhile, I had another problem at my head. From where do I get the ERP Application as the backups were supposed to be on the tapes, and the tape drive was not functional. We had an year old application source available on one of the locations. The issue here was that in a year's span, the developers had put in a couple of new modules, reports and bug fixes. We were maintaining a Manual Source Code Control [Change Management] and were able to recover 95-96% of the ERP Application. There were 2-3 sub modules whose source code was missed due to mismanaged source code control. The only way we could have a complete recovery was if we could recover from the tapes, or repair and recover the same from the Disks.

We managed to bring the temporary setup online for a set of users per department.

By now, the management realized that a Business Continuity Plan needs to be in place. And, that our Disaster Recovery Plan needed a serious re-visit.

1. Our Backups to tapes should be made easily recoverable, even in case of a tape drive failure.
2. Our Application Backup and Source Code should be maintained in other Medias atleast at an interval of 1 month or less.
3. Our RMAN Backup needs to be made more fool proof if we were to completely rely on it.
4. Our Business Continuity Setup should be made available so as to avoid major downtime.

In the next post, I will be sharing with you more on the subject and the Complete Recovery of the Production Server.

Major Disaster Recovered Last Week

Last week, to be more precise 14th May '09, we had a major Server Crash. More than 2 of the Disks in the RAID5 Configuration went Poof!!! And yes, We brought the Server back from the dead, even though it really needed to lay in its grave this time (server's been running for more than 7-8 years) !!!! Can't wait to share with you my last week's experience, but before I post about it I need to carry out some priority tasks.

Lot of endeavors to share related to RMAN and exp Backups, Temporary System setup, Application Recovery Failure, Tape Backup Startegy, Re-Visit on our D/R Plan, Recovery from Failed Disks, O/S Upgrade and new findings on the RMAN Backup performance issue, Standby and DG, and last but not the least procurement of a new server!!! At last, after 2 years the management realizes we need to Change the server. :D

Complete Recovery of Production Instance

Yesterday at 01:12 pm, the production instance crashed. We were reported of "Shared Memory Realm Does not Exist" error by the users.

I noticed in the Alertlog, that the Control File (control05.ctl) was having some problem.

Wed Apr 29 13:04:37 2009
Errors in file d:\oracle\admin\orcl0\bdump\orcl_arc2_4852.trc:
ORA-00202: controlfile: 'C:\ORACLE\ORADATA\ORCL\CONTROL05.CTL'
ORA-27091: skgfqio: unable to queue I/O
ORA-27070: skgfdisp: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 2) The system cannot find the file specified.

I brought down the instance, as it was in nomount state, and then renamed the corrupted Control File (control05.ctl) and by copying one of the other control files, I created a new Control File (control05.ctl).

I fired the startup to see the control file issue was resolved, but also got a couple of new error messages related to datafile 12.

The alert log revealed the following:

Wed Apr 29 13:17:29 2009
Errors in file d:\oracle\admin\orcl0\udump\orcl_ora_6512.trc:
ORA-00600: internal error code, arguments: [krdsod_1], [198804811], [0], [198804867], [0], [], [], []
ORA-01115: IO error reading block from file 12 (block # 334316)
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\ORCL\ORION_ISCO01.DBF'
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.

This file was part of the main production tablespace, kind of the heart of the Functional ERP System. I fired a recover on the tablespace and it still gave the same error.

SQL> alter database recover datafile 12;
alter database recover datafile 12
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01115: IO error reading block from file 12 (block # 334316)
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\ORCL\ORION_ISCO01.DBF'
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.

I was wondering how many files have been affected in this manner, to decide between full database restore or individual datafile/tablespace restore and recover.

Meanwhile, from the IT Admins, I came to know that one of the disks from the 4 RAID5 disks had a problem (defunc) at almost the same time when we noticed a Instance Crash, and that the disk needed to be fixed.

The database is in Archivelog mode and we have daily full database backups and also Archivelog backups in every 4 hours taken. Then I tried to restore the whole tablespace (datafiles 11, 12, 18, 19), and it again failed at datafile 12. Then, I renamed the existing datafile (ORION_ISCO01.DBF) and then restored the datafile 12 (ORION_ISCO01.DBF) from the backup, and eventually restored all the other 3 datafiles. And then I sucessfully recovered the tablespace (SR 7473105.994).

Now, I was able to successfully open the database.

SQL> alter database open;
Database altered.

SQL>

I checked the Systems and they were all running fine. And, then I took a full database RMAN backup with retention policy of redundacy 2. I raised a Service Request (SR 7559528.992) with Oracle Support to look into what the error messages mean and is it directly related to the RAID5 disk failure.
I would require to answer a few questions to the Management regarding this Incident, or should I say, Disaster Recovery Situation. It's high time, we need to change the database server.

Today, the users are using the system as if nothing happened. Thanks to the implementation of RMAN, I was able to bring the database back online in 3:30 hours. I can't imagine what I would have done with the full export dumps, or the ealier weekly Cold Backups (NOARCHIVELOG).

I will keep you all posted on my further findings.