ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

##########################
## Error
##########################

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'

##########################
#  Error Occurred
##########################

Error occured while trying to bring up the database after a server reboot.

##########################
## Command Executed
##########################

startup from sqlplus prompt

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Start the Database
#########################################

SQL> startup

ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring after a server reboot. Looks like the file system mentioned in the error is not mounted.

=====================================================================================================================

##########################
## Solution 1
##########################

As from the above step we can see the filesystem(/u01) is not mounted. So mount the file system.

As a root user,

mount /u01

#### Shutdown the instance

shu immediate

SQL> shu immediate
ORA-01109: database not open

#### Then Bring the database up.

startup

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
Database opened.
SQL>

=====================================================================================================================

##########################
## Solution 2
##########################

#### If the filesystem can't be mounted and the datafile/tablespace data is not required then.

## Shut the instance

Shutdown immediate

SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

## Bring up the database in Mount State

Startup mount

SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
SQL>

#### Mark the datafile as Dropped.

Alter database datafile '/u01/ORALIN/datafiles/tbs01.dbf' offline drop;

SQL> Alter database datafile '/u01/ORALIN/datafiles/tbs01.dbf' offline drop;
Database altered.

#### Open the Database in Read-Write Mode

SQL> Alter database open;
Database altered.

#### You can Drop the tablespace and that User if its not required. In my case, it didnt work.

SQL> drop user ggadmin cascade;
drop user ggadmin cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'
ORA-06512: at line 1314
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'


SQL> Drop tablespace GGTBS including contents;
Drop tablespace GGTBS including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'
ORA-06512: at line 1314
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'


#### Shutdown and restart Database to verify it comes up good.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
Database opened.
SQL> exit

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORALIN  READ WRITE

SQL> select logins from v$instance;

LOGINS
----------
ALLOWED

SQL>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================