Recovering when users data file is lost

How to recover when users datafile was lost?

Previously we have taken the HOT backup to /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ path. Now whenever users datafile got missed, deleted, corrupted, overridden or disk errors occurred then we don’t need to shut down the database. We can perform recovery by keeping database in open status only.

In this case we need to recover the users datafile by restoring the file from /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ to /u01/oracle/app/oradata/pavan/database_1/.

This is most similar to recovery of sysaux datafile.

1. Before proceeding with recovery operation we need to make the users tablespace offline

SQL> ALTER TABLESPACE USERS OFFLINE ;

Tablespace altered.

2. Now copy the files from backup location to datafiles location. This is restoration of users datafile.

cp   /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/users01.dbf /u01/oracle/app/oradata/pavan/database_1/

3. Now we shall be doing recovery operation

SQL>RECOVER DATAFILE 4;

Or

SQL>ALTER DATABASE RECOVER DATAFILE ‘/u01/oracle/app/oradata/pavan/database_1/users01.dbf’;

In this process it will start applying all available archivelogs to the database and recovers it. We need to cancel once all archive logs are applied.

Enter cancel for cancelling the operation. Or else we can give AUTO to apply all archive logs automatically

4. Check status of database;

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS

------------

OPEN

5. Make tablespace online

SQL> ALTER TABLESPACE USERS ONLINE;

Tablespace altered.