Ask the Expert

What to do with lost logfiles

I am confused because I have two answers to the same question.

Question: After a disk crash I lost some control files and all logfiles (no archive). After restoring the system I would like to copy the good control file to the old ones. What should I do with logfiles to restart without problem?

Answer 1:

    Requires Free Membership to View

  • Duplicate the control file
  • Mount the database
  • Open the database with reset logs
  • Answer 2:
    As the database was in NOARCHIVELOG mode at the time of media failure, you cannot use the current datafiles because they are inconsistent (and cannot be consistent because there are no archive logs). So You CANNOT perform ALTER DATABASE OPEN RESETLOGS.

    If you still want to open the database using RESETLOGS option, then follow the steps below (NOARCHIVELOG mode (cold backup)).

  • Copy all datafiles from backup location to production DB location (NOTE:: all datafiles are consistent)
  • Using the current controlfile, type the following command
  • STARTUP MOUNT
  • RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; You can type "CANCEL" and press enter as there is no archived logs.
  • ALTER DATABASE OPEN RESETLOGS;
  • The bottom line is that this method is the same as restoring the database from full cold backup (DATA LOST).
    Personally, I'd restore everything from a good cold backup. But if you want to use one of the two answers given, then the second answer is correct. When you open the database with RESETLOGS, it will recreate your missing online redo log files.

    The moral of this story is to make sure that you have a sound backup and recovery strategy. This example is one of the many recovery scenarios that DBAs practice.

    This was first published in February 2004

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: