ORA-00257 : Archiver Error Connect internal only

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

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 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…)

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>