Friday, 16 March 2018

ORA-01157: cannot identify/lock data file

Issue: ORA-01157: cannot identify/lock data file
Cause of the above error: The datafile may have been renamed at the operating system level, moved to a different directory or disk drive either intentionally or unintentionally. Or  the datafile does not exist or is unusable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore. 

Solution: For solution of the above error follow the steps as shown below:
  • If the database is down, mount it. 
    STARTUP MOUNT; 
  • Offline drop the datafile. 
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV01.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV02.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV03.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV04.DF' OFFLINE DROP;
  • If the database is at mount, open it. 
    ALTER DATABASE OPEN; 
  • Drop the user tablespace. 
    DROP TABLESPACE HR_DEV INCLUDING CONTENTS; 

Thus, the complete scripts executed in DOS command prompt is shown below:
Now we can connect to the database again and hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.