This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Oct 9, 2013 6:04 PM by yxes2013 RSS

Archive log full again

yxes2013 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    /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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thanks Vk,

     

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

  • 11. Re: Archive log full again
    Gamblesk Explorer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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