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…!!

Opatch Failed error code 73: OUI-67073: UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables"

Upgrade error from 11.2.0.2 to 11.2.0.4

Error Description:

Oracle SPU / CPU patch deployment using Opatch filed with following error message.

Following executables are active :

/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1

 UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

Log file location: /u01/app/oracle/product/11.2.0.2/home/cfgtoollogs/opatch/opatch2014-9-14_12-10-00PM.log

OPatch failed with error code 73

Cause: there are some files which are locked or some processes still running while applying patch . those should be avoided

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

Full Error in log:

[Nov 22, 2014 2:10:20 PM]    Finish fuser command /sbin/fuser /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1 at Fri Nov 22 14:10:20 CET 2014

[Nov 22, 2014 2:10:20 PM]    Following executables are active:

                             /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1

[Nov 22, 2014 2:10:20 PM]    Prerequisite check "CheckActiveFilesAndExecutables" failed.

                             The details are:

                             Following executables are active:

                            /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1

[Nov 22, 2014 2:10:20 PM]    OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

[Nov 22, 2014 2:10:20 PM]    Finishing UtilSession at Fri Nov 22 14:10:20 CET 2014

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

Solution Description

This error is simple. Firstly make sure DB and listener are down . 

Solution 1:

some processes are still in running .to find out them try

ps -ef|grep db_name

then kill each process by  using   kill -9 1196 command

Solution 2:
check which process is locking this library file using below command and kill it

$ /sbin/fuser /u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1

/u01/app/oracle/product/11.2.0.2/home/lib/libclntsh.so.11.1:  1196m 2215m

 $ kill -9 1196

now run opatch apply


Cheers.....!!!!