Fix ORA-01157: cannot identify/lock data file # - see DBWR trace file ORA-01110: data file #: ORA-01136: specified size of file # (n blocks) is less than original size

advertisements

_____________________________________________________________________________________________________________________

Error Description: One of my applications is failing with following oracle error message.

"ORA-01157: cannot identify/lock data file 76 - see DBWR trace file ORA-01110: data file 76:

'/u01/oracle/product/database/11.2.0/testqa/dbs/DATA' " Please ensure that the database is accessible

Solution Description:

When I checked in the database, I found some issues with the datafile addition or creation. My database is running on ASM and this particular datafile has been created in normal file system where the path is wrong and not existing one. The file is not physically present in the file system.

select FILE_NAME,TABLESPACE_NAME,FILE_ID, bytes/1024/1024/1024 from dba_data_files where file_id=76;

FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME                   FILE_ID BYTES/1024/1024/1024

------------------------------ ---------- --------------------

/u01/oracle/product/database/11.2.0/testqa/dbs/DATA

DATA_TBS                               76                   30

You can use the following steps to resolve the issue. We have to recreate the file to the actual location. In this example, I need to create it to the ASM. See the steps for the same.

Pre Checks

1. Make sure you have all the archive logs from the false datafile creation
2. You will have to offline the datafile for this recreation

Recreate the datafile to the ASM, but you should specify the file size same as for the false file. Otherwise you will get ORA-01136: specified size of file 76 (12800 blocks) is less than original size error.

SQL> alter database create datafile 76 as '+DATA';

alter database create datafile 76 as '+DATA'

*

ERROR at line 1:

ORA-01136: specified size of file 76 (12800 blocks) is less than original size

of 3932160 blocks

ORA-01110: data file 76:

'/u01/oracle/product/database/11.2.0/testqa/dbs/DATA'

So here is the proper syntax

Step 1: Bring the datafile to offline status which will need a recovery with archivelogs

alter database datafile 76 offline;

Database altered.

Step 2: Recreate the datafile to the actual location with following command.

alter database create datafile 76 as '+DATA' size 30g ;

Database altered.

But this will not show the files size until unless you do a recovery for the datafile.

FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME                   FILE_ID BYTES/1024/1024/1024

------------------------------ ---------- --------------------

+DATA/testqa/datafile/data_tbs.922.191414954

DATA_TBS                               9                  

Step 3: Bring online the datafile which needs recovery

alter database  datafile 76 online;

alter database  datafile 76 online

*

ERROR at line 1:

ORA-01113: file 76 needs media recovery

ORA-01110: data file 76: '+DATA/testqa/datafile/DATA_TBS.922.191414954'

recover datafile 76;

ORA-00279: change 11161546759214 generated at 08/14/2012 10:36:44 needed for

thread 1

ORA-00289: suggestion :

+RECO/testqa/archivelog/2012_08_14/thread_1_seq_2739.7998.791290081

ORA-00280: change 11161546759214 for thread 1 is in sequence #2739

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Auto

. . . .

. . . .

ORA-00279: change 11161550809695 generated at 08/15/2012 11:43:30 needed for

thread 3

ORA-00289: suggestion :

+RECO/testqa/archivelog/2012_08_15/thread_3_seq_2037.19117.791389431

ORA-00280: change 11161550809695 for thread 3 is in sequence #2037

Log applied.

Media recovery complete.

SQL> alter database  datafile 76 online;

Database altered.

_____________________________________________________________________________________________________________________

Website Stats