CopyDisable

Sunday, 14 August 2011

Oracle Database Backup and Recovery: Recovering a datafile without any backup

Problem Scenario:
One of my data file is damaged and I do not have backup of that data file, but I have all the archive logs. I can recover the data file under the following conditions:
1) I must have all the archive logs since the creation of the damaged data file.
2) The control file should contain the name of the damaged data file (The control file must be current or is a backup taken after the damaged data file was added).
Steps of recovery:
Two ways we can perform the recovery:
A) The first way requires database shutdown
1.  Shutdown the database. We have to use abort option to shutdown the database because of the damaged data.
SQL> shutdown abort;
2. Start the database in mount mode
SQL> startup mount;
3. Now create the damaged data file
SQL> alter database create datafile DATAFILE_NAME;
4. Recover the data file
SQL> recover datafile DATAFILE_NAME;
5. After the file is recovered, open the database
SQL> alter database open;
B)  The second way is to recover the data file without shutting the database
1. Take the damaged data file offline
SQL> alter database datafile DATAFILE_NAME offline;
2. Create the damaged data file
SQL> alter database create datafile DATAFILE_NAME;
3. Recover the data file
SQL> recover datafile DATAFILE_NAME;
4. Make the data file online
SQL> alter database datafile DATAFILE_NAME online;


জয় আই অসম,
প্রণব শর্মা