Ask the Expert

Recovering database with newly added datafiles

  1. We have scheduled hot backups at 9:30 every day. At 2:00 pm, we added two datafiles and at 3:00 pm the database crashed. We do not have log files. How do we recover the database with two newly added datafiles?
  2. We have a scheduled backup at 10:00 am. At 11:00 am, unfortunately, redo logs are deleted by a DBA. The database crashed at 12:00. How do we recover the database?
  3. We have a scheduled backup at 9:00 am. The database crashed at 11:00 am. How do we recover the database if the control files are corrupted?

    Requires Free Membership to View

  1. If you do not have log files, then your only recourse is incomplete recovery. You can use the RECOVER UNTIL command to recover up until a point in time, or recover up until your lost archived redo log. Once you have recovered as much as you can, you need to open the database with RESETLOGS. It is a very good idea to bring down your database at this point and take a backup.
  2. If you have the archived redo logs, then you can restore all transactions up to the ones in your last archived redo log. At that point, you need to open the database with RESETLOGS. It is a very good idea to bring down your database at this point and take a backup.
  3. Hopefully, you have multiplexed your control file. If so, copy one of your other control files to the same location and same name as the missing one. If you have not multiplexed your control file, then you can use a backup of the control file and recover your database with the RECOVER USING BACKUP CONTROLFILE command. If you do not have a backup control file, then you can manually create the control file with the CREATE CONTROLFILE command.

This was first published in June 2006

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: