How to recover missing datafiles?

ORA-01110: ORA-01565:  ORA-27041

Recovery of SID went wrong though the DB opened successfully because unfortunately we used old control file and missed almost 11 data files in controlfile.sql file and which created soft link in dbs path.

In order to overcome the situation below steps performed and database become consistent. If in case in the future if we face same issue, we can use below steps confidently.

1) Find the missing datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;

/oracle/SID/121/dbs/MISSING00268                     268 PSAPSID  ONLINE

/oracle/SID/121/dbs/MISSING00269                     269 PSAPSID ONLINE

 

2) Take tablespace or datafiles offline:

Come with below error because data file already in offline

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’ offline;

Database altered.

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’ offline;

Database altered.

 

3) Rename missing datafiles with original files: 

SQL> alter database rename file ‘/oracle/SID/121/dbs/MISSING00268’ to ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’;

SQL> alter database rename file ‘/oracle/SID121/dbs/MISSING00269’ to ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’;

4) Shutdown and mount the database:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6.4425E+10 bytes

Fixed Size                  3729840 bytes

Variable Size            7381976656 bytes

Database Buffers         5.6908E+10 bytes

Redo Buffers              130486272 bytes

Database mounted.

5) Perform media recovery:

SQL> recover datafile 268;

Media recovery complete.

SQL> recover datafile 279;

Media recovery complete.

6) Open the database:

SQL> alter database open;

Database altered.

7) check the status of datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;  – After recovery data files online_status went recover to offline.

8) Bring the datafiles online:

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’ online;

Database altered.

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’ online;

Database altered.

9) check again the status of datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;