Causes and Solution of ORA-00376: ORA-01110:

Error Description:
-----------------------
Whenever I try to access a objects it fails with errors.
The error message stack follows:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/oradata2/test_tbs02.dbf'

Possible Causes and Solutions:
----------------------------------------
A.Tablespace or Datafile Offline:
-------------------------------------------------
As you see the error return with affected file_id and file_name. Here file_id is 9 and file_name is /oradata2/test_tbs02.dbf.

1)Check the tablespace status with,

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id);
Enter value for give_file_id: 9
TABLESPACE_NAME STATUS
------------------------------ ---------
TEST_TBS ONLINE

If it would offline you should bring it online to make work with it,

SQL> alter tablespace TEST_TBS online;

So there is no problem with the tablespace as it is already online state.

2)Check the affected datafile.

To know all, SQL> select FILE# from v$datafile where status in ('OFFLINE','RECOVER');
Check the status of the affected data file as it is returned with error message.

SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
RECOVER DISABLED

If status is recover then media recovery is needed.
If status is offline then open the datafile.

SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
RECOVER DISABLED

SQL> recover datafile &file_id;
Enter value for file_id: 9
Media recovery complete.

SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
OFFLINE DISABLED

SQL> alter database datafile 9 online;
Database altered.

SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
ONLINE DISABLED

If you see status DISABLED then affected tablespace make online.

SQL> alter tablespace TEST_TBS online;
Tablespace altered.

SQL> select status, enabled from v$datafile where file#=&give_file_id;
Enter value for give_file_id: 9
STATUS ENABLED
------- ----------
ONLINE READ WRITE

If the status id RECOVER and ENABLED is READ WRITE then simply make the affect datafile online.

B.Datafile does not exist at the OS level.
---------------------------------------------------
Someone has deleted datafile from OS. In that case You many follow Recover lost file if you don't have backup or if you have backup then restore and recover the tablespace Restore and Recover Datafile

C.Backup Software is locking the file and Hence Error Comes.
----------------------------------------------------------------
The backup software might be locking the datafiles, preventing Oracle from reading the datafiles.
Check if there are any backup software running and stop them, thereby releasing the locks and try starting up the database again.