Ora 00257 archiver error connect as sysdba only until resolved

Recently, one of the Oracle users complained that the database was unusable and received the error below

Check #1: Are we physically out of space?

When the host is out of space on the drive/device where the FRA has been set, it can produce this error. We run Oracle on Windows. So, it could be

• Shadow Copy
• Orphaned files not referenced by the database
• Extraneous non-database files
• Archivelog files no longer needed after being backed up (DELETE INPUT option)

I looked at the space situation and it looked good (PowerShell command is from the dbatools module and allows you to get this server space info. from your own host)

PS C:WINDOWSsystem32> Get-DbaDiskSpace -com MyHostName | Format-Table

Server       Name Label     SizeInGB FreeInGB PercentFree BlockSize
------       ---- -----     -------- -------- ----------- ---------
MyHostName   C:  OS         99.9    64.02       64.08      4096
MyHostName   D:  DATA       500    89.78       17.96      4096

Check #2: The Alert Log

Next, I looked at the alert log and saw the problem and the solution was offered right there:

Thu Apr 18 20:15:03 2019
Errors in file D:ORACLEdiagrdbmsMyInstanceMyInstancetraceMyInstance_arc7_4596.trc:
ORA-19809: limite dépassée pour les fichiers de récupération
ORA-19804: impossible de récupérer un espace disque de 45032960 octets de la limite 268435456000
ARC7: Error 19809 Creating archive log file to '\MyHostNameORAFRA$MyInstanceARCHIVELOG2019_04_18O1_MF_1_518897_%U_.ARC'
Thu Apr 18 20:15:03 2019
Errors in file D:ORACLEdiagrdbmsMyInstanceMyInstancetraceMyInstance_arc8_4576.trc:
ORA-19815: AVERTISSEMENT : db_recovery_file_dest_size octets sur 268435456000 sont utilisés (100.00%) ; il reste 0 octets disponibles.
Thu Apr 18 20:15:03 2019
************************************************************************
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.
************************************************************************
Thu Apr 18 20:15:04 2019
Errors in file D:ORACLEdiagrdbmsMyInstanceMyInstancetraceMyInstance_arc8_4576.trc:
ORA-19809: limite dépassée pour les fichiers de récupération
ORA-19804: impossible de récupérer un espace disque de 45032960 octets de la limite 268435456000
ARC8: Error 19809 Creating archive log file to '\MyHostNameORAFRA$MyInstanceARCHIVELOG2019_04_18O1_MF_1_518897_%U_.ARC'
Thu Apr 18 20:15:05 2019
Errors in file D:ORACLEdiagrdbmsMyInstanceMyInstancetraceMyInstance_arc9_4572.trc:
ORA-19815: AVERTISSEMENT : db_recovery_file_dest_size octets sur 268435456000 sont utilisés (100.00%) ; il reste 0 octets disponibles.
Thu Apr 18 20:15:05 2019
************************************************************************
You have following choices to free up space from recovery area:

Sorry, the messages are in French. I did a Google Translate to English below

Thu Apr 18 20:15:03 2019
Errors in file D:  ORACLE  diag  rdbms  MyInstance  MyInstance  trace  MyInstance_arc7_4596.trc:
ORA-19809: Exceeded limit for recovery files
ORA-19804: Unable to recover disk space of 45032960 bytes from limit 268435456000
ARC7: Error 19809 Creating archive log file to '\ MyHostName  ORAFRA $  MyInstance  ARCHIVELOG  2019_04_18  O1_MF_1_518897_% U_.ARC'
Thu Apr 18 20:15:03 2019
Errors in file D:  ORACLE  diag  rdbms  MyInstance  MyInstance  trace  MyInstance_arc8_4576.trc:
ORA-19815: WARNING: db_recovery_file_dest_size bytes on 268435456000 are used (100.00%); there are 0 bytes left.
Thu Apr 18 20:15:03 2019
************************************************** **********************
You have to choose from the following areas:
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
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   RMAN CROSSCHECK and system
   DELETE EXPIRED commands.
************************************************** **********************
Thu Apr 18 20:15:04 2019
Errors in file D:  ORACLE  diag  rdbms  MyInstance  MyInstance  trace  MyInstance_arc8_4576.trc:
ORA-19809: Exceeded limit for recovery files
ORA-19804: Unable to recover disk space of 45032960 bytes from limit 268435456000
ARC8: Error 19809 Creating archive log file to '\ MyHostName  ORAFRA $  MyInstance  ARCHIVELOG  2019_04_18  O1_MF_1_518897_% U_.ARC'
Thu Apr 18 20:15:05 2019
Errors in file D:  ORACLE  diag  rdbms  MyInstance  MyInstance  trace  MyInstance_arc9_4572.trc:
ORA-19815: WARNING: db_recovery_file_dest_size bytes on 268435456000 are used (100.00%); there are 0 bytes left.
Thu Apr 18 20:15:05 2019
************************************************** **********************
You have to choose from the following areas:<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Check #3: Check the space allocated to DB_RECOVERY_FILE_DEST_SIZE

Basically it is saying that the “BACKUP RECOVERY AREA” is full. Although there is ample diskspace, the parameter DB_RECOVERY_FILE_DEST_SIZE determines how much space Oracle can use for database recovery related activities.

To check the space allocated to DB_RECOVERY_FILE_DEST_SIZE you could use one of the following options

SQL*Plus

SHOW PARAMETER DB_RECOVERY_FILE_DEST_SIZE

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 250G

SQL:

This also shows you the current usage as opposed to just the setting with SQL*Plus above

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

As can be seen, I have it set to 250GB and most of it is used:

Check #4: Are backups succeeding?

Check your backup history to make sure Archived Redo Log File backups are successful. If the backups are failing then the Oracle Flash Recovery Area (FRA) may get full. Run archive log backups using the standard procedure you use in your shop. To check if backups are failing you can use this SQL (I am not the author, original source unknown)

Run in SQL*Plus. Substitute &NUMBER_OF_DAYS and tweak WHERE clause as necessary

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds for 999999999 heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_gb for 9,999,999.00 heading "OUTPUT|GB"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"

  SELECT vi.HOST_NAME,
         vi.INSTANCE_NAME,
         J.SESSION_RECID,
         J.SESSION_STAMP,
         TO_CHAR (J.START_TIME, 'yyyy-mm-dd hh24:mi:ss') START_TIME,
         TO_CHAR (J.END_TIME, 'yyyy-mm-dd hh24:mi:ss') END_TIME,
         (J.OUTPUT_BYTES / 1024.0 / 1024.0 / 1024.0)                OUTPUT_GB,
         J.STATUS,
         J.INPUT_TYPE,
/*         CASE
            WHEN TO_CHAR (J.START_TIME, 'd') = 1 THEN 'Monday'
            WHEN TO_CHAR (J.START_TIME, 'd') = 2 THEN 'Tuesday'
            WHEN TO_CHAR (J.START_TIME, 'd') = 3 THEN 'Wednesday'
            WHEN TO_CHAR (J.START_TIME, 'd') = 4 THEN 'Thursday'
            WHEN TO_CHAR (J.START_TIME, 'd') = 5 THEN 'Friday'
            WHEN TO_CHAR (J.START_TIME, 'd') = 6 THEN 'Saturday'
            WHEN TO_CHAR (J.START_TIME, 'd') = 7 THEN 'Sunday'
         END
            DOW,
*/            
         J.ELAPSED_SECONDS,
         J.TIME_TAKEN_DISPLAY
         --X.CF,
         --X.DF,
         --X.I0,
         --X.I1,
         --X.L,
         --RO.INST_ID                                    OUTPUT_INSTANCE
    FROM v$instance vi, 
        V$RMAN_BACKUP_JOB_DETAILS J
         LEFT OUTER JOIN
         (  SELECT D.SESSION_RECID,
                   D.SESSION_STAMP,
                   SUM (
                      CASE
                         WHEN D.CONTROLFILE_INCLUDED = 'YES' THEN D.PIECES
                         ELSE 0
                      END)
                      CF,
                   SUM (
                      CASE
                         WHEN     D.CONTROLFILE_INCLUDED = 'NO'
                              AND D.BACKUP_TYPE || D.INCREMENTAL_LEVEL = 'D'
                         THEN
                            D.PIECES
                         ELSE
                            0
                      END)
                      DF,
                   SUM (
                      CASE
                         WHEN D.BACKUP_TYPE || D.INCREMENTAL_LEVEL = 'D0'
                         THEN
                            D.PIECES
                         ELSE
                            0
                      END)
                      I0,
                   SUM (
                      CASE
                         WHEN D.BACKUP_TYPE || D.INCREMENTAL_LEVEL = 'I1'
                         THEN
                            D.PIECES
                         ELSE
                            0
                      END)
                      I1,
                   SUM (CASE WHEN D.BACKUP_TYPE = 'L' THEN D.PIECES ELSE 0 END) L
              FROM V$BACKUP_SET_DETAILS D
                   JOIN V$BACKUP_SET S
                      ON S.SET_STAMP = D.SET_STAMP AND S.SET_COUNT = D.SET_COUNT
             WHERE S.INPUT_FILE_SCAN_ONLY = 'NO'
          GROUP BY D.SESSION_RECID, D.SESSION_STAMP) X
            ON     X.SESSION_RECID = J.SESSION_RECID
               AND X.SESSION_STAMP = J.SESSION_STAMP
         LEFT OUTER JOIN
         (  SELECT O.SESSION_RECID, O.SESSION_STAMP, MIN (INST_ID) INST_ID
              FROM GV$RMAN_OUTPUT O
          GROUP BY O.SESSION_RECID, O.SESSION_STAMP) RO
            ON     RO.SESSION_RECID = J.SESSION_RECID
               AND RO.SESSION_STAMP = J.SESSION_STAMP
   WHERE J.START_TIME > SYSDATE - &NUMBER_OF_DAYS
   --AND J.STATUS = 'FAILED'
   --AND J.INPUT_TYPE = 'DB INCR'
--trunc(sysdate)
--
--ORDER BY TIME_TAKEN_DISPLAY desc;
ORDER BY start_time desc;

Solutions:

If disk cleanup is necessary, do so. If backups are failing, remedy the situation and get a successful archive log backup. If FRA is too small, increase the size.

In my case today, the solution to the problem is offered in the alert log itself:

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.

I chose to increase the space for DB_RECOVERY_FILE_DEST_SIZE as we are going through other problems with Backup.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=300G SCOPE=BOTH;

System altered.

Alternate solutions

You could move the archive log files to a different drive/folder where there is space and recatalog them using the command below by pointing to the directory the archive logs were moved to and then crosscheck/delete expired (shown later).

catalog start with '/path/to/directory/';

If you know that certain archive log files were already backed up, you could remove them to make room as shown in this post:

How to delete archive logs already archived to backup device?

Run this in RMAN (and change to disk if you are not using sbt_tape)

--Get the list archive logs already backed up atleast once
list archivelog all backed up 1 times to sbt_tape;

--Then remove them if any were returned by the above
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
}

Alternatively, you could first backup the archive log files if you can and then remove them from the recovery area. If you are unable to backup for whatever reason, the archive log files can be physically moved to another location for safekeeping (and recatalog later when you move back) and update the RMAN catalog. You need to check the location pointed to by “db_recovery_file_dest”

SELECT * FROM V$PARAMETER WHERE NAME = 'db_recovery_file_dest'

..physically move the archive log files and update RMAN catalog to reflect the freed-up space in the recovery area.

crosscheck archivelog all;
delete expired archivelog all;

The problem is resolved for now!

ORA-00257 is one of the commonest error in Oracle DBA life. He/She often has to deal with it.Oracle Database almost got frozen because of it and all transaction get stopped .Let us see how to deal with ORA-00257 error

Error Message

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

 As per The oerr ORA-00257 error means

ORA-00257: archiver error. Connect internal only, until freed. Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter archive_log_dest is set up properly for archiving.

Where do you see this error message

1) You will see below type of error message pertaining in alert log

Following is some if the information from the alert log:

Errors in file u01oracleproduct11.2.0diagrdbmsTESTtesttracetest_arc1_1010.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.Wed jan 21 02:44:02 2016
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************
ARC1: Failed to archive thread 1 sequence 1459 (1809)
ARCH: Archival stopped, error occurred. Will continue retrying
Wed jan 21 02:44:02 2016
Errors in file u01oracleproduct11.2.0diagrdbmsTESTtesttracetest_arc1_1010.trc
ORA-16038: log 3 sequence# 1459 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: 'u01oracleoradataTESTredo03.LOG'

2) If you try to login with non sysdba user, you will the below error message

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 - Production on
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn apps/apps
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.
Warning: You are no longer connected to ORACLE.
SQL

3)  If you try to login with sysdba user and check the wait event for the session , you will find session waiting log archive switch event
Why ORA-00257 error occurs

This error happens as the target location for archive log is either full or not available. The Oracle ARCH background process is responsible for taking the redo logs from the online redo log file system and writing them to the flat file is not able to write to the filesystem

How to resolve ORA-00257 errors

1) First of all we should find the archive log destination for the database

sqlplus / as sysdba
SQL> archive log list;

you can also find archive destinations by either USE_DB_RECOVERY_FILE_DEST

SQL> show parameter db_recovery_file_dest;

Also get the size of the db_recovery_file_dest

show parameter db_recovery_file_dest_size;

2) 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 * FROM V$RECOVERY_FILE_DEST;

You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
+FLASH  21474836480 21474836480 212428800 200

3) We can have many solutions in these situation

a) Increase the size of db_recovery_file_dest if you have space available in Oracle ASM or filesystem whatever you are using

alter system set db_recovery_file_dest_size=40g;

b) We can delete the archive log which have been already backed up

rman target /
delete archivelog UNTIL TIME = 'SYSDATE-1' backed up 1 times to sbt_tape;rman target /
RMAN>delete archivelog
until time 'SYSDATE-3';
or,
RMAN>delete archivelog all;
or
delete archivelog UNTIL TIME = 'SYSDATE-1.5' backed up 1 times to sbt_tape;

c) If you have not taken the backup,then it is advise  to take backup and then delete the archive log files

rman target /run {
allocate channel d1 type disk;
backup archivelog all delete input format '/u11/ora/arch_%d_%u_%s';
release channel d1;
}

d) Sometimes old backup piece ,flashback logs may be occupying space in the db_recovery_file_dest, you check the content of db_recovery_file_dest

SQL>Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;FILE_TYPE USED RECLAIMABLE number
------------ ---------- ----------- ----------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 4.77 0 2
BACKUPPIECE 56.80 0 10
IMAGECOPY 0 0 0
FLASHBACKLOG 11.68 11.49 63

Sometimes old guaranteed restore point might be present, Dropping will release the space.

Flashback Database explained and limitation

how to Flashback when we have dataguard

Top 10 Useful Queries for Flashback Database

e) If we dont need archive log , then simply deleting will also serve the purpose

rman target /
DELETE NOPROMPT ARCHIVELOG ALL;

Other useful command in this case

LIST COPY   OF ARCHIVELOG ALL         COMPLETED AFTER 'SYSDATE-1';
DELETE NOPROMPT BACKUP COMPLETED BEFORE 'SYSDATE-4';
LIST COPY   OF ARCHIVELOG UNTIL TIME = 'SYSDATE-18';
BACKUP ARCHIVELOG COMPLETION TIME BETWEEN  'SYSDATE-28' AND 'SYSDATE-7';

Important Note

Please dont delete archive log file manually from the filesystem, it will not update control file and it will not clear the issue. Then you have do crosscheck all in rman and delete obsolete and expired

f)  We can specify alternate path for archiving

Archiving is automatically performed to the alternate dest2

log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2'
log_archive_dest_2='LOCATION=/other_dest_for_archiving'
log_archive_dest_state_1='enable'
log_archive_dest_state_2='alternate'
db_recovery_file_dest='/u01/app/oradata/flash_recovery_area'
db_recovery_file_dest_size=200G

4) Once space is available in db_recovery_file_dest, Please check the system by doing alter system switch logfile

alter system switch logfile;
system alerted

Also try connecting with non sysdba user to make sure ,issue is resolved

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 - Production on
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn apps/apps
connected
SQL>

It is advisable to take regular backup of archive log and delete them. We should put a monitoring script to keep a check on the flash recovery area space.

Related Articles
ORA-00936 missing expression
ORA-01017: invalid username/password; logon denied
ORA-29913 with external tables
ora-00904 invalid identifier
ORA-00001 unique constraint violated
ORA-01111 in MRP
How to find archive log history
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability