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;

 

ORA-12557: TNS:protocol adapter not loadable

Question:  What causes the ORA-12577 error below?  I am running on Windows.

ORA-12557: TNS:protocol adapter not loadable

The ORA-12577 error is related to Windows Environment or Oracle Home PATH because sqlplus command works smoothly when I execute it inside ORACLE_HOME\bin.

Answer:  There are two solutions to this issue:

1 - put the Oracle DB Home in front of the other paths in the PATH environment variable.

2 - Remove ORACLE_HOME From environment Variable and re-boot PC

Oracle author Osama Mustafa notes a solution to the ORA-12577 error.

RUN: SYSDM.CPL to open Windows System Properties

Click on Advanced Tab > Environment Variables…

Click the Path variable under System Variable, then click Edit…

Change the order between Oracle Client Home and Oracle DB Home:

From: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;

To: D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\client_1\bin;

In other words, put the Oracle DB Home in front of the other path.