Recovering Datafiles in ARCHIVELOG Mode

Recovering Database when the database is running in ARCHIVELOG Mode.

Recovering from the lost of Damaged Datafile.

If you have lost one datafile. Then follow the steps shown below.

STEP 1. Shutdown the Database if it is running.

STEP 2. Restore the datafile from most recent backup.

STEP 3. Then Start sqlplus and connect as SYSDBA.

$sqlplus

Enter User:/ as sysdba

SQL>Startup mount;

SQL>Set autorecovery on;

SQL>alter database recover;

 If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery Completely" statement. Go on to next step.

STEP 4. Now open the database

SQL>alter database open;

Upon Startup DB Instance one of Datafile is missing or corrupted

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state

Step 1 Copy the missing Datafile from last taken Backup.

Note : Here you need all the archive log files after last taken backup

Step 2 sql> recover datafile 4.

When Media recovery completed messages shown you can open the database

Step 3 sql> alter database open.

Time Based Recovery (INCOMPLETE RECOVERY).

Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.

You have taken a full backup of the database on Monday night and the table was created on Tuesday and thousands of rows were inserted into it. Some user accidentally drop the table on Thursday and nobody notice this until Saturday.

Now to recover the table follow these steps.

STEP 1. Shutdown the database and take a full offline backup.

STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.

STEP 3. Start SQLPLUS and start and mount the database.

STEP 4. Then give the following command to recover database until specified time.

SQL> recover database until time '2011:08:16:13:55:00' using backup controlfile;

STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this

SQL> alter database open resetlogs;

STEP 6. After database is open. Export the table to a dump file using Export Utility.

STEP 7. Restore from the full database backup which you have taken before this activity.

STEP 8. Open the database and Import the table.