ORA-16038 – ORA-19809 – ORA-00312 – ORA-03113 – ORA-00257: archiver error

Archivelogs are filling up fast on your FRA.Soon there will be a space crunch on your disk unless you get rid of the archives from the disk.

This issue is a nightmare in case of very high transaction databases like telecom or banking applications.

ORA-16038: log 2 sequence# 113 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/app01/base/oradata/orcl12x/redo02.log'
Sun Oct 11 04:21:13 2020

I get the above errors on my alert logfile

We can increase the FRA to a high value and take a backup of all archivelog files with delete input

I couldnot open my database due to the space crunch and redologs couldnot be archived but i can mount it successfully

SQL> startup;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size             973082144 bytes
Database Buffers          654311424 bytes
Redo Buffers               13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 3906
Session ID: 1 Serial number: 7537

When i checked the files that occupy lot of space,i can find backuppiece and archivelog files filling up space

ARCHIVED LOG                      53655639                         0
            115          0


FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
----------------------- ------------------ -------------------------
NUMBER_OF_FILES     CON_ID
--------------- ----------
BACKUP PIECE                    19701765.1                         0
              5          0

i removed obsolete backups and deleted old backuppieces to release some space

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           4      29-SEP-20
  Backup Piece       4      29-SEP-20          /home/oracle/FRA/ORCL12X/backupset/2020_09_29/o1_mf_annnn_TAG20200929T070113_hq64zsf0_.bkp

FRA size is also not sufficient to hold the archives

In logs i can see lot of ora errors related to archive

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 50577408 bytes disk space from 10000 limit
Sun Oct 11 04:21:13 2020
ARC1: Error 19809 Creating archive log file to '/home/oracle/FRA/ORCL12X/archivelog/2020_10_11/o1_mf_1_114_%u_.arc'
Sun Oct 11 04:21:13 2020
Errors in file /app01/base/diag/rdbms/orcl12x/orcl12x/trace/orcl12x_ora_4441.trc:
ORA-16038: log 2 sequence# 113 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: '/app01/base/oradata/orcl12x/redo02.log'

SQL> show parameter db_recovery

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
/data01
db_recovery_file_dest_size           big integer
7000M
SQL> alter system set db_recovery_file_dest_size=10000;

System altered.

I couldnot take archive backup as well .So i have to increase my FRA and change the destination to a mountpoint with more space than current FRA directory

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/11/2020 04:53:35
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2143025664 bytes disk space from 8388608000 limit

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        20G   11G  7.2G  61% /
tmpfs           2.0G  1.1G  953M  52% /dev/shm
/dev/sda2        15G   11G  3.5G  75% /app01
/dev/sda5        12G  6.7G  4.2G  62% /data01

SQL> alter system set db_recovery_file_dest_size=12000m;

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
/home/oracle/FRA
db_recovery_file_dest_size           big integer
12000M

So i changed mountpoint for FRA with more seats and increased the FRA size

After increasing the FRA size,archive backup was successful and issue resolved !!!

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
validation succeeded for archived log
archived log file name=/home/oracle/FRA/ORCL12X/archivelog/2020_10_11/o1_mf_1_151_hr5ljhlb_.arc RECID=160 STAMP=1053493328
validation succeeded for archived log

RMAN> backup archivelog all delete input;

Starting backup at 11-OCT-20
current log archived
using target database control file instead of recovery catalog
..........................
Starting Control File and SPFILE Autobackup at 11-OCT-20
piece handle=/home/oracle/FRA/ORCL12X/autobackup/2020_10_11/o1_mf_s_1053493634_hr5lt4nt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-OCT-20

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: