Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at email@example.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only). Unofficial Oracle Tricks
This month’s question comes from a DBA who wishes to remain anonymous: A disk failed on our server and we lost a datafile. We restored the datafile from a hot backup taken a week ago, but it turns out we are missing a few redo logs archived since then. Oracle is complaining because we can’t produce the required archived redo logs, and we can’t open the database. The datafile in question contains our most important tables. Is there any way we can salvage our data?
Terry Sutton of the Database Specialists team responds: You are caught between a rock and a hard place, because if you restore the complete hot backup from a week ago, you lose a week’s worth of transactions. If you drop the objects in the lost datafile, you lose your most important tables. This situation reinforces the importance of (1) keeping more than one backup set, and (2) making sure you have all relevant archived redo logs.
In this situation, you can’t truly recover. That would require all of the archived redo logs from the point in time when the oldest restored datafile was backed up, all the way to the point to which you wanted to recover. Providing all of the required archived redo logs is the only way Oracle can ensure the integrity of your data.
But, you may be able to extract some (maybe even most) of your data, depending on how much change activity has occurred in the database since the point of the first missing archived redo log. It will require using “hidden” Oracle instance parameters, and you won’t get an image of the data which is truly consistent in time. But it may provide enough data that your business can survive—after which you can implement proper, thorough, tested backup procedures.
In a nutshell, one method to recover data consists of:
Please see our white paper at http://dbspecialists.wpengine.com/presentations.html#recover for a detailed discussion of these steps. The information in this paper could also prove helpful for other database recovery scenarios involving missing archived redo logs.
If you’re looking for some Oracle-related tips, tricks, and work-arounds, check out the resources at http://www.geocities.com/oracletricks. There you will find a variety of code fragments and examples separated into categories such as Oracle PL/SQL and DBA. Many of the code fragments are pretty simple but could be very helpful and could save you some research and time. Here are some samples of what you’ll find at this website: Oracle Open Source Directory
For the open source fans in the crowd, here’s a valuable resource from the folks at O’Reilly books. Sean Hull, author of Oracle and Open Source has compiled a listing of Oracle-related open source tools covering the following topics: Cost-Effective Oracle Database Management
Explore the site and learn about the many open source Oracle tools and code available at http://www.oreillynet.com/oracle/os_dir.
Do you work for a small or mid-sized company without enough Oracle support? Database Specialists, Inc. provides DBA support services for many smaller companies with either one database administrator on staff or none at all. We take the load off an overworked DBA and provide 24/7 support that’s not possible with just one person. We can also handle all production support if desired.
Our remote database administration service can be tailored to your specific needs, from just one comprehensive database review per month to full 24/7 coverage and daily database check-in. In addition to production support, we can also help you in areas such as performance tuning, database design review, and backup and recovery strategy development. All of our Oracle certified experts have 10+ years of Oracle experience.
We’d be happy to answer any questions you may have about our services. Call us toll-free at 888/648-0500. Or, read more about our remote DBA services at http://dbspecialists.wpengine.com/dbapro.html.