1 2 Previous Next 22 Replies Latest reply on Jan 9, 2012 9:06 PM by 878230

    Problem while archiving the redo Log

    710130
      Hi all,
      I m having few issues in my server...
      I get the following error in the alert log of oracle..
      There are many errors
      1) No space left on device
      2) ARC0: I/O error 19502 archiving log 1 to
      '/oracle/admin/SNM/arch/arch_1_393_668727286.arc'
      ARCH: Archival stopped, error occurred. Will continue retrying
      3) ORA-16014: log 1 sequence# 393 not archive*d, no available destinations*


      Also please find the v$log file query
      SQL> select * from v$log;
      GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
      ---------- ---------- ---------- ---------- ---------- --- ----------------
      FIRST_CHANGE# FIRST_TIM
      ------------- ---------
      1 1 405 1073741824 1 NO CURRENT
      60275455 20-OCT-09
      2 1 403 1073741824 1 NO INACTIVE
      59987366 19-OCT-09
      3 1 404 1073741824 1 NO INACTIVE
      60125083 20-OCT-09


      Also the diskspace is almost 97%.
      Can anyone let me know whether archival of redo log files is causing the issue here?
      If so if i stop the archival of logs ,will it solve the problem?

      Can anyone help me on this?

      Mon Oct 19 09:54:39 2009
      Errors in file /oracle/admin/SNM/bdump/snm_arc0_23831.trc:
      ORA-19502: write error on file
      "/oracle/admin/SNM/arch/arch_1_393_668727286.arc", blockno
      577537 (blocksize=512)
      ORA-27063: number of bytes read/written is incorrect
      SVR4 Error: 28: No space left on device
      Additional information: -1
      Additional information: 1048576
      ORA-19502: write error on file
      "/oracle/admin/SNM/arch/arch_1_393_668727286.arc", blockno
      559105 (blocksize=512)
      Mon Oct 19 09:54:39 2009
      Errors in file /oracle/admin/SNM/bdump/snm_arc0_23831.trc:
      ORA-19502: write error on file
      "/oracle/admin/SNM/arch/arch_1_393_668727286.arc", blockno
      577537 (blocksize=512)
      ORA-27063: number of bytes read/written is incorrect
      SVR4 Error: 28: No space left on device
      Additional information: -1
      Additional information: 1048576
      ORA-19502: write error on file
      *"/oracle/admin/SNM/arch/arch_1_393_668727286.arc", blockno*
      *559105 (blocksize=512)*
      ARC0: I/O error 19502 archiving log 1 to
      '/oracle/admin/SNM/arch/arch_1_393_668727286.arc'
      ARCH: Archival stopped, error occurred. Will continue retrying
      Mon Oct 19 09:54:40 2009
      ORACLE Instance SNM - Archival Error
      Mon Oct 19 09:54:40 2009
      ORA-16038: log 1 sequence# 393 cannot be archived
      ORA-19502: write error on file "", blockno (blocksize=)
      ORA-00312: online log 1 thread 1: '/oracle/oradata/SNM/redo01.log'
      Mon Oct 19 09:54:40 2009
      Errors in file /oracle/admin/SNM/bdump/snm_arc0_23831.trc:
      ORA-16038: log 1 sequence# 393 cannot be archived
      ORA-19502: write error on file "", blockno (blocksize=)
      ORA-00312: online log 1 thread 1: '/oracle/oradata/SNM/redo01.log'
      Mon Oct 19 09:54:40 2009
      ARCH: Archival stopped, error occurred. Will continue retrying
      Mon Oct 19 09:54:40 2009
      ORACLE Instance SNM - Archival Error
      Mon Oct 19 09:54:40 2009
      ORA-16014: log 1 sequence# 393 not archived, no available destinations
      ORA-00312: online log 1 thread 1: '/oracle/oradata/SNM/redo01.log'
      Mon Oct 19 09:54:40 2009
      Errors in file /oracle/admin/SNM/bdump/snm_arc1_23833.trc:
      ORA-16014: log 1 sequence# 393 not archive*d, no available destinations*
      ORA-00312: online log 1 thread 1: '/oracle/oradata/SNM/redo01.log'
      Mon Oct 19 10:00:16 2009
      ARC0: Encountered disk I/O error 19502
      Mon Oct 19 10:00:16 2009
      ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1:
      '/oracle/admin/SNM/arch/arch_1_393_668727286.arc' (error
      19502)
      (SNM)
        • 1. Re: Problem while archiving the redo Log
          661463
          Hi,

          You should NOT stop the archiverprocess, you should increase space asap and let it continue.

          --
          Best, J
          • 2. Re: Problem while archiving the redo Log
            asifkabirdba
            Remove you old archive logs first. Reduce your mount point usage to below 90%, then check.

            Regards
            Asif Kabir
            • 3. Re: Problem while archiving the redo Log
              Andreas Hess
              Hi

              You have run out of disk space in /oracle/admin/SNM/arch/

              Decide how many days worth of archivelogs you need on the server and implement a job to remove archivelogs > n days.

              Example of crontab entry to do this (> 3 days - runs at 10 min past midnight every night):

              00 10 * * * find /oracle/admin/SNM/arch/*.arc -mtime +2 -exec rm {} \;

              Cheers,
              A.
              • 4. Re: Problem while archiving the redo Log
                710130
                Please find the archive log list output and v$log output

                SQL> archive log list;
                Database log mode Archive Mode
                Automatic archival Enabled
                Archive destination /alcatel/oracle/admin/SNM/arch
                Oldest online log sequence 403
                Next log sequence to archive 403
                Current log sequence 405


                SQL> select * from v$log;

                1 1 405 1073741824 1 NO CURRENT 60275455 20-OCT-09
                2 1 403 1073741824 1 NO INACTIVE 59987366 19-OCT-09
                3 1 404 1073741824 1 NO INACTIVE 60125083 20-OCT-09

                IS these values fine?
                Because here it seems that the oldest online log sequence and next log sequence to archive is same.is this ok?

                Regards
                Srinivasan
                • 5. Re: Problem while archiving the redo Log
                  Andreas Hess
                  Have you cleared out some space?

                  If not, archiver will hang until you do.

                  And yes, the output above is fine...
                  1 person found this helpful
                  • 6. Re: Problem while archiving the redo Log
                    710130
                    The problem here is we are not able to clear the space..


                    The partition occupies 97%... out of this
                    sysaux01.dbf file occupies 18 GB and muse01.dbf file(my application data file) occupies 18 GB.
                    My total partition disk space is 50 GB.


                    Hence i though of droppping the 'muse' user and the tablespace which corresponds to the muse01.dbf file...

                    But while dropping the muse user i got this error and struck...

                    So can i drop the tablespace directly without removing the user muse?
                    Will it work?

                    Or suggest me some work around..

                    Srinivasan
                    • 7. Re: Problem while archiving the redo Log
                      Vijayaraghavan Krishnan
                      Woraround is

                      Just move the tablespace to another location.

                      if u want to drop the tablespace then go ahed with the below option.
                      I am assuming u are trying this in Test Environment only.

                      u can drop a tablespace with INCLUDING CONTENTS option if a schema object is associated with it.

                      Regards,
                      Vijayaraghavan K
                      1 person found this helpful
                      • 8. Re: Problem while archiving the redo Log
                        710130
                        Hi,
                        I dont have space in other location....
                        That is the problem here.

                        Can i delete the tablespace now along with the contents and the data file..Will it work?

                        If i am trying to create the tablespace with same name as the one that i deleted ? will it work?

                        Also if i reboot the machine or restart the oracle, there will be no problem right?

                        Also Will i be able to drop the tablespace because there is no disk space and will i get the same error in alert logs or will it succeed and clear the datafile..

                        If so, then i can try this?

                        Help me out

                        Srinivasan
                        • 9. Re: Problem while archiving the redo Log
                          Vijayaraghavan Krishnan
                          italics sysaux01.dbf file occupies 18 GB and muse01.dbf file(my application data file) occupies 18 GB.
                          My total partition disk space is 50 GB. italics


                          1) This is your TestDB?
                          2) why your sysayx datafile is having 18GB just investigate that.
                          3) Check out what are the objects resides in the muse datafile (Assuming the tablespace name for datafile muse01.dbf) by issuing the below query
                          select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'MUSE'
                          4) like that check for procedure and other schema objects.
                          5) If any object resides move the objects to any other tablespace you have or drop those objects if u dont need it.
                          6) Then u drop the tablespace including contents and datafile option.

                          First Take Backup!!!!


                          Regards,
                          vijayaraghavan K
                          • 10. Re: Problem while archiving the redo Log
                            710130
                            1) This is your TestDB?
                            Srini >> NO...
                            2) why your sysayx datafile is having 18GB just investigate that.
                            3) Check out what are the objects resides in the muse datafile (Assuming the tablespace name for datafile muse01.dbf) by issuing the below query
                            select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'MUSE'
                            Srini >> I dont want this tablespace...I would like to remove so that i can clear the muse01.dbf file but i want to do it in a graceful manner
                            4) like that check for procedure and other schema objects.
                            5) If any object resides move the objects to any other tablespace you have or drop those objects if u dont need it.
                            6) Then u drop the tablespace including contents and datafile option.
                            Srini >> Can i drop the tablespace with partition diskspace more than 97%....Will it work because i am constantly getting the archival error....
                            • 11. Re: Problem while archiving the redo Log
                              Vijayaraghavan Krishnan
                              temporirly you move any other files to another direcotry and do this.

                              This is your Production Server???


                              If this is your production Server please take the backup and get down time and start dropping the tablespace with including contents.

                              Regards,
                              Vijayaraghavan K
                              • 12. Re: Problem while archiving the redo Log
                                710130
                                yes it is the production server.

                                Also the arch folder contains 2 GB file - arc file....

                                Does disable of archival will solve the issue?

                                I am try to clear the space or move to some other location..But the thing is my application files is of 3 to 4 GB.remaining thing is of dbf files.


                                6564 drwxr-x--- 2 oracle oinstall 512 Oct 21 2008 .
                                6563 drwxr-x--- 3 oracle oinstall 512 Oct 21 2008 ..
                                6568 -rw-r----- 1 oracle oinstall 7061504 Sep 30 11:58 control01.ctl
                                6569 -rw-r----- 1 oracle oinstall 7061504 Sep 30 11:58 control02.ctl
                                6570 -rw-r----- 1 oracle oinstall 7061504 Sep 30 11:58 control03.ctl
                                9283 -rw-r----- 1 oracle oinstall 5128192 Sep 30 10:21 mfxpima.dbf
                                6600 -rw-r----- 1 oracle oinstall 17179877376 Sep 30 11:50 muse0.dbf
                                6572 -rw-r----- 1 oracle oinstall 1073742336 Sep 30 08:10 redo01.log
                                6573 -rw-r----- 1 oracle oinstall 1073742336 Sep 30 10:16 redo02.log
                                6574 -rw-r----- 1 oracle oinstall 1073742336 Sep 30 11:58 redo03.log
                                6578 -rw-r----- 1 oracle oinstall 19293806592 Sep 30 11:58 sysaux01.dbf
                                6576 -rw-r----- 1 oracle oinstall 1698701312 Sep 30 11:56 system01.dbf
                                6579 -rw-r----- 1 oracle oinstall 2147491840 Sep 30 11:04 temp01.dbf
                                6577 -rw-r----- 1 oracle oinstall 4084211712 Sep 30 11:58 undotbs01.dbf
                                6580 -rw-r----- 1 oracle oinstall 5251072 Sep 30 10:21 users01.dbf


                                What as to be done....in this case....
                                Help me out...
                                I have no other option ....


                                SRinivasan
                                • 14. Re: Problem while archiving the redo Log
                                  710130
                                  please check above...I have posted my query...I have not done the step..

                                  Srinivasan
                                  1 2 Previous Next