SQL Error 376 occured while accessing program SAPM

[Former Member]

Hello,

We are facing the mentioned errors while doing any transaction in our freshly installed Ehp4 SR1 system on MSCS.

Attached is the alert log for your reference.

Please help as my go-live is next week only.

Fri Jan 29 15:00:37 2010

Errors in file i:\oracle\prd\saptrace\background\prd_smon_6652.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'L:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1'

Fri Jan 29 15:00:48 2010

MMNL absent for 7045 secs; Foregrounds taking over

MMNL absent for 7045 secs; Foregrounds taking over

MMNL absent for 7045 secs; Foregrounds taking over

MMNL absent for 7045 secs; Foregrounds taking over

Fri Jan 29 15:00:57 2010

Errors in file i:\oracle\prd\saptrace\background\prd_smon_6652.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'L:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1'

Fri Jan 29 15:01:07 2010

Errors in file i:\oracle\prd\saptrace\background\prd_smon_6652.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'L:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1'

Fri Jan 29 15:01:10 2010

Completed checkpoint up to RBA [0x3e0.2.10], SCN: 8400539

Fri Jan 29 15:01:17 2010

Errors in file i:\oracle\prd\saptrace\background\prd_smon_6652.trc:

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'L:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1'

*********************

Pls help if anyone has ever faced this error .

Regards

Ankita

[former_member524429 · Accepted solution]

Hi,

Run the following command and post the status of UNDO.DATA1 file and other doubtful Files (with Recovery or Offline Status)

sqlplus / as sysdba

select file#, name from gv$dbfile; (note down the file number)

select file#, status from v$datafile; (check the status of file with the file number)

Regards,

Bhavik G. Shroff

Edited by: Bhavik G. Shroff on Jan 29, 2010 3:54 PM

[Former Member · Accepted solution]

Hi,

Followings is the output of SQL queries -

---

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE STATUS = 'OFFLINE';

no rows selected

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS = 'RECOVER';

NAME

-

---

L:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS = 'OFFLINE';

no rows selected

******************************************

I see that the datafile UNDO.DATA1 is shows status as recover.

Kindly suggest if

RECOVER DATAFILE 'L:\ORACLE\PRD\SAPDATA1\UNDO_1\UNDO.DATA1';

command is going to resolve my issue or what should i do now?

Regards,

Ankita.

[former_member524429 · Accepted solution]

Hi,

Run the following commands and restart database and sap.

sqlplus / as sysdba

select file#, status, name from v$datafile; (check the status of file with the file number)

recover datafile #NUMBER;

alter database datafile #NUMBER online;

where #NUMBER is a File number of "L:\ORACLE\<SID>\SAPDATA1\UNDO_1\UNDO.DATA1" data file as mentioned in v$datafile.

Regards,

Bhavik G. Shroff

Edited by: Bhavik G. Shroff on Jan 30, 2010 11:30 AM