Oracle – Archiver error: ORA-00257

Sometime the archiver process received an error while trying to archive a redo log. You can see error: ORA-00257. The most likely cause of this message is the destination device is out of space to store the redo log file or some reason archiver is disabled.

Solution

First check archiver – is it enabled??

oracle>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 30 18:52:44 2022
Version 19.9.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/SID/oraarch/SIDarch
Oldest online log sequence 56584
Next log sequence to archive 56587
Current log sequence 56587
SQL>

If archiver is not enabled – how to run it

...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Startup mount;
ORACLE instance started.

Total System Global Area 2818572168 bytes
Fixed Size 8900488 bytes
Variable Size 1409286144 bytes
Database Buffers 1392508928 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> ALTER DATABASE ARCHIVELOG;

Database altered

SQL> alter database open;

-- Verify:
SQL> archive log list;

-- To disable archivelog use command: SQL> database noarchivelog;

The space is sufficient – how to check it?

We known that archiving is enabled and now we have to find archive destinations…

-- check parameter:
SQL> show parameter db_recovery_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/SID/oraarch
db_recovery_file_dest_size big integer 300G
SQL>

The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use:

SQL> select name, floor(space_limit / 1024 / 1024) "Size MB", ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name;

NAME
-----------------------
Size MB Used MB
---------- ----------
/oracle/SID/oraarch
307200 1183

SQL>

-- Similar information You can get from OS using command: df -h |grep oraarch

You may find that the SPACE_USED (Used MB) is the same as SPACE_LIMIT (Size MB), if this is the case, to resolve ORA-00257 should be remedied by:

Add more space to the mount where the logs are going (resize filesystem on OS).
Take a backup of the logs via RMAN or other external backup system, e.g. Networker.
Move the archivelog location to another mount/drive/location temporarily while you do one of the above -> (SQL>alter system set LOG_ARCHIVE_DEST_.. = ‘location=/Archivelog_New_Path‘;)
Delete the archivelogs if we don’t need the ability to restore the database:

oracle>rman target /

RMAN>list archivelog all;
RMAN>delete archivelog until time 'SYSDATE-1'; -- older than 1 day
RMAN>delete archivelog until time 'SYSDATE-4/24'; -- older that 4 hours
RMAN>delete archivelog until time 'SYSDATE-10/1440'; -- older than 10 minutes
RMAN>delete force archivelog until time 'SYSDATE-1/24'; -- force delete older than 1 hour
RMAN>delete archivelog all; -- delete all archive log
RMAN>delete noprompt archivelog all; -- delete all archive log without prompt
RMAN>crosscheck archivelog all; -- crosscheck

Solution

Cancel Reply