ERROR:ORA-00257

When application user trying to connect to database then they are getting following error.

SQL> conn system/oracle

ERROR:ORA-00257: archiver error. Connect internal only, until freed.

When I checked alert_sid.log file then found following message

Sun Jul 20 16:39:45 2008

Errors in file f:\oracle\product\10.1.0\admin\test\bdump\test_arc0_3468.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 9493504 bytes disk space from 2147483648 limit
ARC0: Error 19809 Creating archive log file to 'F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2008_07_20\O1_MF_1_230_U_.ARC'

ARC0: All standby destinations failed; successful archival assumed

ARC0: Failed to archive log 1 thread 1 sequence 230 (19809)

Sun Jul 20 16:39:45 2008Errors in file f:\oracle\product\10.1.0\admin\test\bdump\test_arc0_3468.trc:

ORA-16038: log 1 sequence# 230 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO01.LOG'

Reason: FLASHBACK_RECOVERY_AREA is configured but no free space left in FLASH_RECOVERY_AREA that is why archived log is not created and all user except SYS are not able to connect to database

Solution: Connect with SYS user & increase DB_RECOVERY_FILE_DEST_SIZE parameter value

Before following parameter is set on 2gb.

SQL> CONN / AS SYSDBA

Connected.

SQL> alter system set db_recovery_file_dest_size=4g;
System altered.

SQL> conn system/oracle

Connected.

Second Condition:

If we are getting below error message during instance startup, then it is also becuase FLASH_RECOVERY_AREA have no free space left.

SQL> startup

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 144964076 bytes

Database Buffers 25165824 bytes

Redo Buffers 1048576 bytes

Database mounted.

ORA-16038: log 3 sequence# 454 cannot be archived

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 3 thread 1:'F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO03.LOG'

When I checked alert_sid.log file I found below entries

Sun Jul 20 17:09:38 2008

ARC1: Evaluating archive log 3 thread 1 sequence 454

ARC1: Archiving not possible: No primary destinations

ARC1: Failed to archive log 3 thread 1 sequence 454 (4)

Sun Jul 20 17:09:38 2008Errors in file f:\oracle\product\10.1.0\admin\test\bdump\test_arc1_2560.trc:

ORA-16014: log 3 sequence# 454 not archived, no available destinations

ORA-00312: online log 3 thread 1: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO03.LOG'

Solution: Connect with SYS user and increase DB_RECOVERY_FILE_DEST_SIZE parameter value

SQL> conn / as sysdba

Connected.

SQL> alter system set db_recovery_file_dest_size=8g;
System altered.

SQL> alter database open;
Database altered.