August 2004

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

August 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Recovery with Missing Archived Logs

You’re invited to submit your Oracle-related questions to us at 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:

  1. Taking a cold backup of what you have now.
  2. Restoring the lost datafile from a backup and applying the archived redo logs that you do have.
  3. Setting an undocumented instance parameter which will allow you to open the database in its current state.
  4. Doing exports and selects to retrieve what data you can from the problem tablespace.
  5. Restoring the entire database from the cold backup taken earlier.
  6. Taking the damaged datafile offline.
  7. Doing exports and selects to retrieve additional data not salvaged in step 4.
  8. Restoring again from the cold backup.
  9. Dropping the problem tablespace.
  10. Recreating the problem tablespace.
  11. Rebuilding the data in the problem tablespace with the data extracted in steps 4 and 7.

Please see our white paper at 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 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

Oracle PL/SQL:

  • Credit card validation based on LUHN algorithm
  • U.S. Bank ABA number validation
  • Send e-mail messages from PL/SQL with MIME attachments
  • Function that returns the words for currency (good for checks)


  • Script to build SQL*Loader CTL files
  • Script to display a summary of blocking locks in a database
  • KSH script to get the DB status


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

  • Command Line Tools
  • Web-based Clients
  • GUI-based Clients
  • Libraries
  • Languages
  • Server Software

Explore the site and learn about the many open source Oracle tools and code available at


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


Leave a Reply

Your email address will not be published. Required fields are marked *