Fix ORA-00257: archiver error. Connect internal only, until freed – in Oracle 11G

  • Published on Apr 2 2012
  • # 91,683
  • oracle

When your database hangs with a ORA-00257 error while you notice there is plenty of space in the /recoveryarea mountpoint you’re likely to have reached the maximum size defined by the new ** initialization parameter ‘db_recovery_file_dest_size‘. Fast solution is to simply increase the value for db_recovery_file_dest_size (and after that of course start archiving…)

** 2012-04-02T12:10:; I see now the init parameter db_recovery_file_dest_size is not so new in fact has been around since Oracle 10.1 … OK….

Mon Apr 02 06:01:12 2012
Errors in file /ora/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/$ORACLE_SID_arc0_25342.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
SQL> sho parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /recoveryarea
db_recovery_file_dest_size           big integer 10G
SQL>


Still plenty of space in the /recoveryarea mountpoint – namely 42G however in the database’s archive log dest is getting pretty filled up – reaching it’s defined maxium (calucalated by the os command ‘du’).

Overall space usage in /recoveryarea mountpoint

$ df -h /recoveryarea/$ORACLE_SID/archivelog/
Filesystem             size   used  avail capacity  Mounted on
/recoveryarea           98G    55G    42G    57%    /recoveryarea

Space usage in database /recoveryarea directory

$ du -sh /recoveryarea/$ORACLE_SID/archivelog/
  10G    /recoveryarea/$ORACLE_SID/archivelog

So in this scenario the quick fix was to issue command below;

SQL> alter system set db_recovery_file_dest_size = 12g;

System altered.

SQL>

Update 2012-07-18T07:54:20+00:00
– slightly different error message – same cause / solution

Tue Jul 17 19:57:21 2012
ARC3 started with pid=22, OS id=24912
Errors in file /ora/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/${ORACLE_SID}_ora_24902.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10066329600 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/recoveryarea/${ORACLE_SID}/archivelog/2012_07_17/o1_mf_1_4395_%u_.arc'
Errors in file /ora/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/${ORACLE_SID}_ora_24902.trc:
ORA-16038: log 3 sequence# 4395 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/m003/oradata/${ORACLE_SID}/${ORACLE_SID}redo3a.log'
ORA-00312: online log 3 thread 1: '/m002/oradata/${ORACLE_SID}/${ORACLE_SID}redo3b.log'
USER (ospid: 24902): terminating the instance due to error 16038
System state dump requested by (instance=1, osid=24902), summary=[abnormal instance termination].
System State dumped to trace file /ora/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace/${ORACLE_SID}_diag_24861.trc
Dumping diagnostic data in directory=[cdmp_20120717195721], requested by (instance=1, osid=24902), summary=[abnormal instance termination].
Instance terminated by USER, pid = 24902

13 thoughts on “Fix ORA-00257: archiver error. Connect internal only, until freed – in Oracle 11G

  1. Miroslav says:

    tnx….it works.

  2. Shahid says:

    In my case on test server, it was reached on 100%, so I manage some same space and its working fine. Thanks for tip.
    Regards,
    Shahid.

  3. Ash says:

    Great tip… Issue fix. I am back on business. Thx

  4. james says:

    Marverlous solution….success!.Thanks

  5. Jose Sanguino says:

    Thanks for the tip, my issue was fixed :)

  6. Aruna S says:

    they resolved the issue with high priority.

  7. Projo says:

    Thank you very much

  8. Robert says:

    Thank you very much for the tip, it worked great

  9. Humayun says:

    Greate solution
    thanks
    is this permanent solution?

  10. Ramesh Velauthem says:

    Timely helped…

  11. sanjaya says:

    Fixed Thank you !

  12. guillermo says:

    in my case i cant even log in as sysdba…

  13. Senthil Murugan says:

    good fix, thanks

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>