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