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
=====================================================================================================================

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

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

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

### Full Error

SQL> truncate table ABC_OWNER.TAB1;
truncate table ABC_OWNER.TAB1
                         *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

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

Error occured while truncating a table in 11.2.0.4.0 version

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

truncate table ABC_OWNER.TAB1;

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

#########################################
# 1) Check the table relationship
#########################################

set lines 200
col owner for a20
col status for a10
col deferrable for a15
col validated for a18
col generated for a15
col deferred for a15

select owner,constraint_name,constraint_type,table_name,status,DEFERRABLE,DEFERRED,VALIDATED,GENERATED from dba_constraints where table_name='TAB1' and constraint_type in ('R','P');

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS     DEFERRABLE      DEFERRED        VALIDATED          GENERATED
-------------------- ------------------------------ - ------------------------------ ---------- --------------- --------------- ------------------ ---------------
ABC_OWNER            PK_TAB1                        P TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_TAB1_30                     R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_ERROR_3A                    R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME

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

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

"TAB1" table has relationship with other tables. From the above output we can see it has a primary constraint and 2 referential constraints(Child).

##########################
## Solution
##########################

Solution would be to disable the constraints and then proceed with the truncate. This is a test environment and i'm doing some testing with the database export/import.

So i'm not worried about the data in it. But in case if you are worried about the data integrity then, best way is to find out what are the child tables and make sure this data is not required anymore and then perform your activity.

select 'alter table '||owner||'.'||table_name ||' disable constraint '||constraint_name||' cascade;' from dba_constraints where constraint_type in ('R','P') and table_name in ('TAB1') order by table_name;

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||'CASCADE;'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter table ABC_OWNER.TAB1 disable constraint PK_TAB1 cascade;
alter table ABC_OWNER.TAB1 disable constraint FK_TAB1_30 cascade;
alter table ABC_OWNER.TAB1 disable constraint FK_ERROR_3A cascade;

=====================================================================================================================
After Disabling the Constraints, truncate command worked.
=====================================================================================================================

SQL>  truncate table ABC_OWNER.TAB1;

Table truncated.

SQL>

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS     DEFERRABLE      DEFERRED        VALIDATED          GENERATED
-------------------- ------------------------------ - ------------------------------ ---------- --------------- --------------- ------------------ ---------------
ABC_OWNER            PK_TAB1                        P TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_TAB1_30                     R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_ERROR_3A                    R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME

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