Skip to Content

Hi Team,

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;
Regards,
Kannan Murugan
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply