Recovering when users/non system data file is lost using RMAN

Recovering when users/non system data file is lost using RMAN

How to recover when users datafile was lost?

Previously we have taken the RMAN backup to /u01/oracle/app/oradata/pavan/database_1/RMAN_BKP/ path. Now whenever users/non-system 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/RMAN_BKP/ to /u01/oracle/app/oradata/pavan/database_1/.

The common error we face during this situation is:

ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/oracle/app/oradata/pavan/database_1/users_1.dbf
ORA-27041: unable to open file

Solution:

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

SQL> ALTER DATABASE DATAFILE 4 OFFLINE ;

2. Restore the datafile from backup

RMAN> restore datafile 4;

3. Now we shall be doing recovery operation

RMAN>RECOVER DATAFILE 4;

Or

SQL>ALTER DATABASE RECOVER DATAFILE ‘/u01/oracle/app/oradata/pavan/database_1/users_1.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 DATABASE DATAFILE 4 ONLINE;

Cheers…!!