Recovering when sysaux data file is lost

How to Recover database when sysaux data file is lost:

Previously we have taken the HOT backup to /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ path. Now whenever a sysaux 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 sysaux datafile by restoring the file from /u01/oracle/app/oradata/pavan/database_1/HOT_BKP/ to /u01/oracle/app/oradata/pavan/database_1/.

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

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;

Tablespace altered.

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

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

3. Now we shall be doing recovery operation

SQL>RECOVER DATAFILE 2;

or

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

Tablespace altered.