1 2 Previous Next 19 Replies Latest reply: Oct 9, 2013 9:19 PM by Hemant K Chitale RSS

    Archive log full again

    yxes2013

      Hi all,

       

      11.2.0.1

      Aix 6.1

       

      Our database hangs again due to archive log full.

      I remove everything in the fast_recovery_area so the size will be free

      and shutdown abort the database, but still the same error:

       

      SQL> startup

      ORACLE instance started.

       

       

      Total System Global Area 6196547584 bytes

      Fixed Size                  2232952 bytes

      Variable Size            3657436552 bytes

      Database Buffers         2516582400 bytes

      Redo Buffers               20295680 bytes

      Database mounted.

      ORA-03113: end-of-file on communication channel

      Process ID: 7078052

      Session ID: 125 Serial number: 5

       

       

       

      Please helppp....

       

      Thanks,

      zxy

        • 1. Re: Archive log full again
          DK2010

          Hi,

           

          Questions:

           

          1.Did you search any thing in this forum ?

          2.Why you use the command shutdown abort ?

          3. Why you remove Everything form Recovery Area ?

          4. Have you read the process for shutdown and start for the database ?


          could you please explain this.

          • 2. Re: Archive log full again
            user10274093

            Hello,

            how much free space do you have hier :

            /u02/oracle/fast_recovery_area/BATCHQAT/


            Is /u02 full ?


            Try :

            startup mount;

            alter database noarchivelog

            alter database open.


            Regards.

            • 3. Re: Archive log full again
              yxes2013

              What can you do if your database hangs? and the cause is the recovery dest is full?  so u have to free up the cause which I did, but still the archive wont write anymore?

              even if the recovery file has freespace.

              • 4. Re: Archive log full again
                yxes2013

                /u02 has still 20G space and I deleted all files in fast recovery but archivelog seemed frozen and numb and dead? I really hate this archivelog thing

                You follow everything in the docs how to free it but it wont listen anymore once it stucked up.

                 

                Anyways this one did the trick:

                 

                SQL> startup mount;

                ORACLE instance started.

                 

                 

                Total System Global Area 6196547584 bytes

                Fixed Size                  2232952 bytes

                Variable Size            3657436552 bytes

                Database Buffers         2516582400 bytes

                Redo Buffers               20295680 bytes

                Database mounted.

                SQL> alter database noarchivelog;

                 

                 

                Database altered.

                 

                 

                SQL> alter database open;

                 

                 

                Database altered.

                • 5. Re: Archive log full again
                  DK2010

                  Hi,

                   

                  Oracle provide lot of Documents to manage the database, there is some steps/process to follow to manage/maintain  your database.

                  If you know what is the importance of Archive log file for the database before enabling the Archive log mode you have to plan

                  how you manage those Archive logs like backup or some deletion Policy, free Archive area.. etc.

                  • 6. Re: Archive log full again
                    yxes2013

                    Thanks DK,

                     

                    I read the docs and the soluition was to alter and increase the recovery_file_dest_size, but it did not work.

                    Can you tell me which one should have been the good solution? As far as I have known once this archivelog gets full, it is very hard to make it freeup

                    • 7. Re: Archive log full again
                      John Stegeman

                      Seriously, you "hate this archivelog thing?"

                       

                      I guess you hate being able to recover a database to a point in time.

                       

                      Based on your posts here, I'd advise you to step away from a keyboard completely until you've actually had some proper training.

                      • 8. Re: Archive log full again
                        DK2010

                        Hi,

                         

                        If you read the Doc , i believe on that you can also find about  the regular backup of Archive Area as per your Archive generation  frequency. or deletion of Archive log with the help of some scheduled job . and Noarchivelog is not an option , like your did..

                         

                        i have one suggestion before doing any thing  please think Twice* or read before that.. not After that..

                         

                        Good Luck

                        • 9. Re: Archive log full again
                          vk82

                          In case if your DB is Production. How will you continue to operate your DB in no archivelog mode.

                           

                          The solution for above mentioned problem is either:

                           

                          SQL> Shutdown abort;

                           

                          SQL> Startup Mount;

                           

                          1) Change your Archiving Destination by :

                          SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='NEW_PATH_LOCATION'

                           

                          or

                          2) Increase the size of Archiving Destination by first determining the size of the Archiving destination which intially you have:-

                           

                          SQL> SELECT NAME,(space_limit/1048576),(space_used/1048576) from v$recovery_file_dest;

                           

                          And after that increase the space according to above output:-

                          SQL> alter system set db_recovery_file_dest_size=more_size_than_getting_size_above scope=both;

                           

                          SQL> Alter database open;

                           

                           

                          Now

                           

                          SQL> Shutdown immediate;

                           

                          SQL> Startup;

                          or

                           

                          3) Zip the archive logs and move it to anyother destination but be careful to preserve this you require all this archivelogs during recovery.

                          • 10. Re: Archive log full again
                            yxes2013

                            Thanks Vk,

                             

                            That sounds the good process, I will apply it next time

                            • 11. Re: Archive log full again
                              Gamblesk

                              How did you 'I remove everything in the fast_recovery_area so the size will be free'?

                              • 12. Re: Archive log full again
                                sybrand_b

                                Obviously this answer is completely 100 percent INCORRECT. Also it is unprofessional to advise someone to put a production database in NOARCHIVELOG.

                                 

                                ------------

                                Sybrand Bakker

                                Senior Oracle DBA

                                • 13. Re: Archive log full again
                                  yxes2013

                                  Yes you are right Sybrand, but at least it work for a moment. Since Vk response(which I think the right solution) what not yet posted at the time of issue.

                                  I can return it back to archivelog mode anyways

                                  • 14. Re: Archive log full again
                                    yxes2013

                                    How did you 'I remove everything in the fast_recovery_area so the size will be free'?


                                    rm -f /u02/oracle/fast_recovery_area/BATCH/archivelogs/*/*

                                    1 2 Previous Next