1 2 Previous Next 17 Replies Latest reply: Jan 7, 2013 8:02 AM by 620899 RSS

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

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

                      Edited by: 980149 on Jan 6, 2013 12:11 PM
                      • 8. Re: ORA-00376: file 2 cannot be read at this time
                        Osama_Mustafa
                        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
                          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
                            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
                              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
                                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
                                  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
                                    what is the result of this query?

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