Error 19809 creating archive log file

Hello

Following some maintenance on our server (change raid battery + reboot) my database (Oracle 11g) is not available anymore. I m a newbie in Oracle and I have been through the forum and I can t find the solution of my pb, I can t fix the ORA-19809 error since I can t connect to the DB as sysdba, all the solution that I have found need to be connected as sysdba. Please help I have been struggling on that for days and I seems to go in circle at the moment. Here is what I ve got :

C:>set oracle_sid=MYTIKKA

C:>sqlplus /nolog

SQL>connect / as sysdba

ERROR ORA-12560: TNS:protocol adapter error

Here are the errors I have in my c:/oracle/diag/rdbms/mytikka/mytikka/trace/alert_mytikka.log :

Errors in file c:oraclediagrdbmsmytikkamytikkatracemytikka_arc1_6420.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 30839808 bytes disk space from 10485760000 limit

ARC1: Error 19809 Creating archive log file to ‘C:ORACLEFLASH_RECOVERY_AREAMYTIKKAARCHIVELOG2015_03_31O1_MF_1_88141_%U_.ARC’

Errors in file c:oraclediagrdbmsmytikkamytikkatracemytikka_ora_7524.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 10485760000 bytes is 99.86% used, and has 14655488 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.

************************************************************************

Errors in file c:oraclediagrdbmsmytikkamytikkatracemytikka_ora_7524.trc:

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 30839808 bytes disk space from 10485760000 limit

ARCH: Error 19809 Creating archive log file to ‘C:ORACLEFLASH_RECOVERY_AREAMYTIKKAARCHIVELOG2015_03_31O1_MF_1_88141_%U_.ARC’

Errors in file c:oraclediagrdbmsmytikkamytikkatracemytikka_ora_7524.trc:

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

ORA-19809: limit exceeded for recovery files

ORA-00312: online log 1 thread 1: ‘C:ORACLEORADATAMYTIKKAREDO01.LOG’

USER (ospid: 7524): terminating the instance due to error 16038

Tue Mar 31 16:08:06 2015

ARC3 started with pid=23, OS id=8104

Instance terminated by USER, pid = 7524

TNSPING seems to work

C:>tnsping mytikka

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 — Production on 01-APR-2

015 02:32:14

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

C:oracleproduct11.2.0dbhome_1networkadminsqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 420025-w

eb1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mytikka)

))

OK (0 msec)

C:>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 — Production on 01-APR-2015 02:35:21

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=420025-web1)(PORT=1521))

)

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 — Produ

ction

Start Date                31-MAR-2015 16:08:17

Uptime                    0 days 10 hr. 27 min. 4 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:oracleproduct11.2.0dbhome_1networkadminlisten

er.ora

Listener Log File         c:oraclediagtnslsnr420025-web1listeneralertlog.

xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=420025-web1)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))

The listener supports no services

The command completed successfully

Here is my tnsnames.ora :

# tnsnames.ora Network Configuration File: C:oracleproduct11.2.0dbhome_1networkadmintnsnames.ora

# Generated by Oracle configuration tools.

MYTIKKA =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 420025-web1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = mytikka)

    )

  )

TSAPIDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 420025-web1)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = mytikka)

    )

  )

We usually watch the space usage of log archive destination very closely because the database will be suspended if the space is full. Particularly, if the log archive destination is USE_DB_RECOVERY_FILE_DEST, then you must watch the usage of Fast Recovery Area (FRA) instead to prevent ORA-19809.

When you meet the following error, you hit the space limit of FRA.

ORA-16038: log 2 sequence# 230348 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'd:/oracle/oradata/dbname/redo/redo02.log'

If you have already shutdown the database, the error prevents you from startup, it could be severe.

Let’s see the content of this error:

Description

ORA-19809: limit exceeded for recovery files

Cause

The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded.

Action

There are five possible solutions:

1. Take frequent backup of recovery area using RMAN.
2. Consider changing RMAN retention policy.
3. Consider changing RMAN archived log deletion policy.
4. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
5. Delete files from recovery area using RMAN.

Then check current initialization parameters:

SQL> show parameter db_recovery

Solutions

Several ways that can solve ORA-19809 are listed in the following:

1. Resize FRA

Resize FRA to a larger value if disks still have more space available for FRA. Assuming that your database cannot be opened normally.

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=200G SCOPE=SPILE;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Actually, starting up the database to MOUNT or NOMOUNT is no difference. Just make sure that the value does not exceed the overall disk space for FRA.

If your database is still online, then use SCOPE=BOTH

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

2. Delete Expired Archived Logs

Delete expired archive logs if there have no more space for archived logs. Assuming a lot of expired archive logs are available to be deleted.

[oracle@test ~]$ rman target /
...
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

Adding NOPROMPT right after DELETE makes RMAN to directly delete all archived logs without user’s confirmation.

If the space is still full, you need a stronger medication like the next one.

3. Delete All Archived Logs

Delete all archive logs, no matter what.

RMAN> DELETE ARCHIVELOG ALL;

Or do it directly without asking.

RMAN> DELETE NOPROMPT ARCHIVELOG ALL;

Then make the archived log list consistent.

RMAN> CROSSCHECK ARCHIVELOG ALL;

4. Switch FRA to Another Location

Change log archive destination to another location

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=e:oracleflash_recovery_area2dbname SCOPE=SPFILE;

Don’t forget to bounce the database service in order to apply the new change.

5. Change Destination of Archived Logs

Again, you have to restart the database to make it work.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=e:oracledbnamearchivelog SCOPE=SPFILE;

Preventive Actions

Here are preventive actions that you can consider to take to prevent ORA-19809.

1. Larger FRA or Destination

Set a reasonable size for FRA in advance. Just like we talked about in solution #1, #4 and #5.

2. Smaller Recovery Window

Impose a smaller recovery window on the retention period, if the current recovery windows is significantly big. For example, we change the recovery window from 60 days into 7 days.

RMAN> SHOW ALL;
...
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Then we delete some obsolete backups like the following command according to the new policy immediately, or put it into your backup scripts.

RMAN> DELETE OBSOLETE;

Consequently, there should have fewer backups left in FRA.

3. Fewer Backup of Archived Logs

Change archived log deletion policy. Assuming the current setting is to backup 2 times, we set to 1 time or NONE.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO SBT;
RMAN> DELETE OBSOLETE;

4. Apply NOLOGGING

Set tables NOLOGGING temporarily before importing or bulk data loading.

SQL> ALTER TABLE tablename1 NOLOGGING;
SQL> ALTER TABLE tablename2 NOLOGGING;
...

I hope this post can help you to solve ORA-19809.

 I faced ORA-19809 when
backup of one of my databases failed during archived log backup phase. In our
backup script we had a statement “ALTER SYSTEM ARCHIVE LOG CURRENT” just before
the backup command for archived redo logs. When ALTER SYSTEM ARCHIVE LOG
CURRENT statement was executed, following error stack was returned.

Meanwhile we also saw
following errors/warning appearing in the alert log file

After seeing above, it
can easily be judged that problem was with fast recovery area. If fast recover
area is full, no more archived logs could be created and ORA-19809 is evident.

To solve this problem the
suggestions appearing in alert log file can be follows. Either free up the fast
recovery area by deleting the files in the FRA, or space in the FRA can be
increased. A soft increase in the FRA space is to rest
DB_RECOVERY_FILE_DEST_SIZE to a value closer to the actual size of the FRA file
system, thus allowing more files to be created under FRA.

If archive generation has
increased over time, or flashback logs are there in the FRA for a guaranteed
restore point that you cannot drop, you might consider adding more space to the
FRA file system.

PROBLEM:

While taking rman backup, it failed with below error ( ORA-19809 & ORA-19804)

RMAN> backup archivelog all;

Starting backup at 26-MAY-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1942 RECID=1750 STAMP=944758808
input archived log thread=1 sequence=1943 RECID=1751 STAMP=944773254
channel ORA_DISK_1: starting piece 1 at 26-MAY-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/26/2017 11:53:47
ORA-19809: limit exceeded for recovery files          ---------------------------------------- >  ERROR 
ORA-19804: cannot reclaim 882983936 bytes disk space from 5368709120 bytes limit    ----------->  ERROR

SOLUTION:

1. Check the value of db_recovery_file_dest_size:

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /archive/TESTDB
db_recovery_file_dest_size           big integer 5G

2. Check the space usage in recovery_dest

SQL> select SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)" from  v$recovery_file_dest;

SPACE_USED(GB) SPACE_LIMIT(GB)
----------      -----------
4.5                5 

We can see out of 5G, 4.5G is already used. Let’s increase the value of db_recovery_file_dest to a higher value.

3. Increase db_recovery_file_dest

SQL> alter system set db_recovery_file_dest_size=8G;

System altered.

SQL> select SPACE_USED/1024/1024/1024 "SPACE_USED(GB)" ,SPACE_LIMIT/1024/1024/1024 "SPACE_LIMIT(GB)" from  v$recovery_file_dest;

SPACE_USED(GB) SPACE_LIMIT(GB)
-------------- ---------------
    5.31985807               8

Run the rman job again, It will complete successfully.

RMAN> backup archivelog all;

Starting backup at 26-MAY-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=282 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1942 RECID=1750 STAMP=944758808
input archived log thread=1 sequence=1943 RECID=1751 STAMP=944773254
input archived log thread=1 sequence=1989 RECID=1797 STAMP=944999626
input archived log thread=1 sequence=1990 RECID=1798 STAMP=944999713
channel ORA_DISK_1: starting piece 1 at 26-MAY-17
channel ORA_DISK_1: finished piece 1 at 26-MAY-17
piece handle=/archive/TESTDB/TESTDB/backupset/2017_05_26/o1_mf_annnn_TAG20170526T115514_dlhvcm48_.bkp tag=TAG20170526T115514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-MAY-17

Starting Control File and SPFILE Autobackup at 26-MAY-17
piece handle=/archive/TESTDB/TESTDB/autobackup/2017_05_26/o1_mf_s_944999722_dlhvctjw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAY-17

RMAN

if following error occurred on Oracle database or database hanged due to this error

Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_1202.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 44131328 bytes disk space from 4070572032 limit
ARC3: Error 19809 Creating archive log file to ‘/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_%u_.arc’

check space is fill !!!!!!!!!

set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) «Size MB»
, ceil(space_used / 1024 / 1024) «Used MB»
from v$recovery_file_dest
order by name
/

oracle@apt-amd-02:~> id
uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall)
oracle@apt-amd-02:~> rman

Recovery Manager: Release 11.2.0.1.0 — Production on Fri Apr 29 06:56:30 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> delete force archivelog until time ‘sysdate -1’; —- delete some archive log file

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
——- —- ——- — ———
91 1 330 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc

92 1 331 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc

93 1 332 A 13-APR-11
Name: /opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_330_6vnqzs5c_.arc RECID=91 STAMP=749717522
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_331_6vnr0q87_.arc RECID=92 STAMP=749717587
deleted archived log
archived log file name=/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_04_29/o1_mf_1_332_6vnr0x7c_.arc RECID=93 STAMP=749717596
Deleted 3 objects

check all the file deleted ….

RMAN> delete force archivelog until time ‘sysdate -1’;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
specification does not match any archived log in the repository

Posted by John Hallas on July 16, 2008

This entry was posted on July 16, 2008 at 1:30 pm and is filed under Oracle.
Tagged: archiver, db_recovery_file_dest_size, ORa-19804, ORA-19809, ORA-19815. You can follow any responses to this entry through the RSS 2.0 feed.

You can leave a response, or trackback from your own site.