ORA-01157: cannot identify/lock data file 255

[Former Member]

Hi there,

I'm using SAP ECC6.0, Oracle 10G, HPUX B11.23.

Recently I had performed database restoration from my backup tape. However, during the process of bringing up the Oracle and SAP database, I observed that there could be some archive logs went missing. I had tried to check them from my backup tape, but could not find it.

In short, now my SAP database is up and running, but I'm having another problem when I executed a "CheckDB" job in DB13. The job is unable to complete successfully. Please refers to messages in my next post.

Please kindly advice on how to fix this issue.

Appreciate for any of your help and advice.

Thank you.

Regards,

Peter

[JPReyes]

Certainly seems like PSAPTEMP datafiles are missing.. check in DB02 if any datafiles exist.

Also read Note 659946 - FAQ: Temporary tablespaces

Regards

Juan

[Former Member]

Hi Juan,

Thanks for the response.

I've checked my QAS system and able to see the PSAPTEMP tablespace name from DB02. Please see below.

PSAPTEMP 5,440.00 5,440.00 0 YES 10,000.00 10,000.00 0 1 0 0 ONLINE TEMPORARY

Would appreciate if you could help to advice on how to fix this issue.

Thanks.

- Peter

[Former Member]

You need to create the PSAPTEMP tablespace as system is not able to identified the datafile with the file_id 255. For more info for TEMP tbs read the SAPnote

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'oracle/QAS/sapdata2/temp_1/temp.data1' SIZE 1048576000 REUSE AUTOEXTEND OFF;

All the best !

[Former Member]

Hi prateek_y,

Thanks for your help.

It seems to be working.

Will need to monitor it further should there be anymore issues.

- Peter

[Former Member]

Good... you can make this as answered if you feel its solved !

[Former Member]

DB13 job log:

30.08.2010 19:44:40 Job started

30.08.2010 19:44:40 Step 001 started (program RSDBAJOB, variant &0000000000061, user ID BASIS)

30.08.2010 19:44:40 Execute logical command BRCONNECT On host drqaecc

30.08.2010 19:44:40 Parameters: -u / -jid CHECK20100830194440 -c -f check

30.08.2010 19:44:40 SXPG_COMMAND_EXECUTE(LONG)

30.08.2010 19:44:40 COMMANDNAME = BRCONNECT

30.08.2010 19:44:40 OPERATINGSYSTEM = ANYOS

30.08.2010 19:44:40 TARGETSYSTEM = drqaecc

30.08.2010 19:44:40 DESTINATION =

30.08.2010 19:44:41 BR0801I BRCONNECT 7.00 (41)

30.08.2010 19:44:41 BR0477I Oracle pfile /oracle/QAS/102_64/dbs/initQAS.ora created from spfile /oracle/QAS/102_64/dbs/spfileQAS.ora

30.08.2010 19:44:41 BR0805I Start of BRCONNECT processing: cedzywno.chk 2010-08-30 19.44.40

30.08.2010 19:44:41 BR0484I BRCONNECT log file: /oracle/QAS/sapcheck/cedzywno.chk

30.08.2010 19:44:41 BR0280I BRCONNECT time stamp: 2010-08-30 19.44.41

30.08.2010 19:44:41 BR0301E SQL error -1157 at location BrTspListGet-15, SQL statement:

30.08.2010 19:44:41 'OPEN curs_41 CURSOR FOR'

30.08.2010 19:44:41 'SELECT TABLESPACE_NAME, BYTES, 0 FROM DBA_FREE_SPACE UNION ALL SELECT TABLESPACE_NAME, BYTES_FREE + BYTES_USED, 0 FROM V$TEMP_S

30.08.2010 19:44:41 ORA-01157: cannot identify/lock data file 255 - see DBWR trace file

30.08.2010 19:44:41 ORA-01110: data file 255: '/oracle/QAS/sapdata2/temp_1/temp.data1'

30.08.2010 19:44:41

30.08.2010 19:44:41 BR0806I End of BRCONNECT processing: cedzywno.chk2010-08-30 19.44.41

30.08.2010 19:44:41 BR0280I BRCONNECT time stamp: 2010-08-30 19.44.41

30.08.2010 19:44:41 BR0804I BRCONNECT terminated with errors

30.08.2010 19:44:41 External program terminated with exit code 3

30.08.2010 19:44:41 BRCONNECT returned error status E

30.08.2010 19:44:41 Job finished

[Former Member]

Hello...

30.08.2010 19:44:41 ORA-01157: cannot identify/lock data file 255 - see DBWR trace file

What is the status of file 255?

execute the command below and paste the output :-

SELECT * FROM V$recover_file;

SELECT * FROM DBA_DATA_FILES WHERE FILE_ID=255;

As it seems your tempfile has been lost... try to re-create it ..

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 'oracle/QAS/sapdata2/temp_1/temp.data1' SIZE 1048576000 REUSE AUTOEXTEND OFF;

All the best !

[Former Member]

Hi prateek_y,

Thanks for your response.

Pleas see below for the SQL output.

SQL> SELECT * FROM V$recover_file;

no rows selected

SQL> SELECT * FROM DBA_DATA_FILES WHERE FILE_ID=255;

no rows selected

The SQL output is showing no information, is this a problem? or is there anything miss out?

Please kindly advice.

Thank you.

- Peter