This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 7, 2013 6:02 AM by 620899 RSS

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

983152 Newbie
Currently Being Moderated
Hi,

Our SAP system is down. When checked I found PSAPUNDO tablespace needs recovery. (From SYSSMU1$ to SYSSMU10$ NEEDS RECOVERY and SYSSMU11$ to SYSSMU17$ are offline).

So I have created another tablespace PSAPUNDO2 and altered the system.

SQL >create undo tablespace PSAPUNDO2 datafile 'D:\Oracle\SOL\sapdata1\undo_2\UNDO.DATA2' size 700MB;
SQL>alter system set undo_tablespace=PSAPUNDO2 scope=spfile;

I have changed undo_management to Manual. And I have not dropped old tablespace PSAPUNDO yet,
I have restarted the system and initially the database was not open.
I gave command alter database open and opened it.
The oracle has been started normally. But still SAP instance cannot be started.
Last few lines of aler log:
------------------------------------------------
Sun Jan 06 04:05:12 2013
Completed: ALTER DATABASE OPEN
Sun Jan 06 04:06:08 2013
Shutting down archive processes
Sun Jan 06 04:06:13 2013
ARCH shutting down
ARC2: Archival stopped
Adjusting the default value of parameter parallel_max_servers
from 160 to 65 due to the value of parameter processes (80)
Sun Jan 06 04:06:29 2013
Starting ORACLE instance (normal)
Sun Jan 06 04:14:09 2013
Errors in file d:\oracle\sol\saptrace\background\sol_smon_3796.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\SOL\SAPDATA1\UNDO_1\UNDO.DATA1'
----------------------------------------------

Now when i give command:
show parameter undo, I got the table_space name as PSAPUNDO2.
But if I give command

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs;

I am getting PSAPUNDO as the tablespace insteadof PSAPUNDO2. PSAPUNDO tablespace needs recovery. (From SYSSMU1$ to SYSSMU10$ NEEDS RECOVERY and SYSSMU11$ to SYSSMU17$ are offline).

Also
In BRTOOLS, in show tablespace command I cannot see the new tablespace PSAPUNDO2.

Kindly suggest how to resolve this issue.
  • 1. Re: ORA-00376: file 2 cannot be read at this time
    sb92075 Guru
    Currently Being Moderated
    980149 wrote:
    Hi,

    Our SAP system is down. When checked I found PSAPUNDO tablespace needs recovery. (From SYSSMU1$ to SYSSMU10$ NEEDS RECOVERY and SYSSMU11$ to SYSSMU17$ are offline).

    So I have created another tablespace PSAPUNDO2 and altered the system.

    SQL >create undo tablespace PSAPUNDO2 datafile 'D:\Oracle\SOL\sapdata1\undo_2\UNDO.DATA2' size 700MB;
    SQL>alter system set undo_tablespace=PSAPUNDO2 scope=spfile;

    I have changed undo_management to Manual. And I have not dropped old tablespace PSAPUNDO yet,
    I have restarted the system and initially the database was not open.
    I gave command alter database open and opened it.
    The oracle has been started normally. But still SAP instance cannot be started.
    Last few lines of aler log:
    ------------------------------------------------
    Sun Jan 06 04:05:12 2013
    Completed: ALTER DATABASE OPEN
    Sun Jan 06 04:06:08 2013
    Shutting down archive processes
    Sun Jan 06 04:06:13 2013
    ARCH shutting down
    ARC2: Archival stopped
    Adjusting the default value of parameter parallel_max_servers
    from 160 to 65 due to the value of parameter processes (80)
    Sun Jan 06 04:06:29 2013
    Starting ORACLE instance (normal)
    Sun Jan 06 04:14:09 2013
    Errors in file d:\oracle\sol\saptrace\background\sol_smon_3796.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'D:\ORACLE\SOL\SAPDATA1\UNDO_1\UNDO.DATA1'
    ----------------------------------------------
    what happened prior to this that you have not told us about?
    For how long has Oracle been complaining about PARALLEL_MAX_SERVERS?
    why can't Oracle access 'D:\ORACLE\SOL\SAPDATA1\UNDO_1\UNDO.DATA1'?
  • 2. Re: ORA-00376: file 2 cannot be read at this time
    615576 Oracle ACE
    Currently Being Moderated
    Hi it is not clear the scenary.
    First why do you set up the undo_management in manual?
    Second why do you do recover the tablespace psapundo1?
    Please confirm it also which is the error in the log of sap?
  • 3. Re: ORA-00376: file 2 cannot be read at this time
    983152 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your reply. When I checked SAP down issue(around 6th Dec) I had exteneded the tablespace PSAPUNO and PSAPSR3 with brtools and restarted the server.
    Then I checked further and found the problem was PSAPUNDO needs recovery.
    Then I created another tablespace PSAPUNDO2 and altered system. (See my previous post).

    Now as you told I checked alert log history to check when the problem started and found the following:
    ------------------------------
    Incremental checkpoint up to RBA [0x2aad.15fb6.0], current log tail at RBA [0x2aad.15ff4.0]
    Sun Dec 02 19:01:16 2012
    Beginning log switch checkpoint up to RBA [0x2aae.2.10], SCN: 179359877
    Thread 1 advanced to log sequence 10926
    Current log# 2 seq# 10926 mem# 0: D:\ORACLE\SOL\ORIGLOGB\LOG_G12M1.DBF
    Current log# 2 seq# 10926 mem# 1: C:\ORACLE\SOL\MIRRLOGB\LOG_G12M2.DBF
    Sun Dec 02 19:06:21 2012
    Completed checkpoint up to RBA [0x2aae.2.10], SCN: 179359877
    Sun Dec 02 19:25:04 2012
    Incremental checkpoint up to RBA [0x2aae.2d36.0], current log tail at RBA [0x2aae.2e11.0]
    Sun Dec 02 19:30:00 2012
    KCF: write/open error block=0x19 online=1
    file=2 D:\ORACLE\SOL\SAPDATA1\UNDO_1\UNDO.DATA1
    error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
    O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
    Automatic datafile offline due to write error on
    file 2: D:\ORACLE\SOL\SAPDATA1\UNDO_1\UNDO.DATA1
    Sun Dec 02 19:30:01 2012
    Errors in file d:\oracle\sol\saptrace\usertrace\sol_ora_5408.trc:
    ORA-00376: file 2 cannot be read at this time
    ORA-01110: data file 2: 'D:\ORACLE\SOL\SAPDATA1\UNDO_1\UNDO.DATA1'
    ORA-00376: file 2 cannot be read at this time
    ------------------------------------------

    I am not sure what caused this error: ORA-00376: file 2 cannot be read at this time

    I think there is no issue with Parallel_Max_Server as I can see its entry in Alert log since 2007.
    -----
    Thu Oct 25 20:48:48 2007
    Adjusting the default value of parameter parallel_max_servers
    from 160 to 65 due to the value of parameter processes (80)
    Thu Oct 25 20:48:48 2007
    Starting ORACLE instance (normal)
    --------

    Will wait for your reply.

    Edited by: 980149 on Jan 6, 2013 9:32 AM
  • 4. Re: ORA-00376: file 2 cannot be read at this time
    983152 Newbie
    Currently Being Moderated
    Hi,

    I changed the undo_management to manual for recreation of tablespace PSAPUNDO2.
    There is no tablespace called PSAPUNDO1.
    PSAPUNDO is the old tablespace which needs recovery for which I recreated PSAPUNDO2.
  • 5. Re: ORA-00376: file 2 cannot be read at this time
    615576 Oracle ACE
    Currently Being Moderated
    Hi could you do a recover tablespace to your old undo tbs and copy the output please
  • 6. Re: ORA-00376: file 2 cannot be read at this time
    983152 Newbie
    Currently Being Moderated
    Hi,

    Are you asking me to run the following command:
    SQL>alter system set undo_tablespace=PSAPUNDO scope=spfile;
    Do you want me to copy paste the alert log after running the command?
    But the old tablespace PSAPUNDO is corrupted and needs recovery.
  • 7. Re: ORA-00376: file 2 cannot be read at this time
    983152 Newbie
    Currently Being Moderated
    -

    Edited by: 980149 on Jan 6, 2013 12:11 PM
  • 8. Re: ORA-00376: file 2 cannot be read at this time
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Refer to Mos notes :
    ORA-604, ORA-376, And ORA-1110 On Startup [ID 1013912.6]
    ORA-00376 and ORA-01110 Database File Unavailable [ID 160705.1]
  • 9. Re: ORA-00376: file 2 cannot be read at this time
    983152 Newbie
    Currently Being Moderated
    Hi

    I am not getting the MOS notes.
    What is the link?
  • 10. Re: ORA-00376: file 2 cannot be read at this time
    615576 Oracle ACE
    Currently Being Moderated
    Hi, please give the output of this query:

    select * from V$RECOVER_FILE

    Regards
  • 11. Re: ORA-00376: file 2 cannot be read at this time
    Jairam Explorer
    Currently Being Moderated
    check the online_status of the data file from the view dba_data_files.,
  • 12. Re: ORA-00376: file 2 cannot be read at this time
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    980149 wrote:
    Hi

    I am not getting the MOS notes.
    What is the link?
    https://support.oracle.com
    you should have valid Account .
  • 13. Re: ORA-00376: file 2 cannot be read at this time
    983152 Newbie
    Currently Being Moderated
    Hi,

    SQL> select * from V$RECOVER_FILE;

    FILE# ONLINE ONLINE_
    ---------- ------- -------
    ERROR CHANGE#
    ----------------------------------------------------------------- ----------
    TIME
    ---------------
    2 OFFLINE OFFLINE
    179359877
    02-DEC-12

    37 OFFLINE OFFLINE
    179359877
    02-DEC-12

    FILE# ONLINE ONLINE_
    ---------- ------- -------
    ERROR CHANGE#
    ----------------------------------------------------------------- ----------
    TIME
    ---------------


    SQL>
  • 14. Re: ORA-00376: file 2 cannot be read at this time
    620899 Newbie
    Currently Being Moderated
    what is the result of this query?

    SELECT segment_name,tablespace_name, status
    FROM dba_rollback_segs;
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points