Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Wednesday, December 28, 2011

ORA-16014: log not archived, no available destinations ORA-16038: ORA-19815 ORA-19809 ORA-19804

Today we started getting the below errors:

Even If manually tried to archive the current log I am getting the below error.

Wed Dec 28 11:19:41 2011
ORA-16014: log 14 sequence# 35224 not archived, no available destinations
ORA-00312: online log 14 thread 3: '+MYDBNAME_REDO_01/MYDBNAMEy/onlinelog/group_14.270.694773607'
ORA-00312: online log 14 thread 3: '+MYDBNAME_REDO_02/MYDBNAMEy/onlinelog/group_14.270.694773609'

and in the alert_log:


ORACLE Instance MYDBNAME3 - Archival Error
Wed Dec 28 11:19:41 2011
ORA-16038: log 13 sequence# 35232 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_01/MYDBNAMEy/onlinelog/group_13.269.694773601'
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_02/MYDBNAMEy/onlinelog/group_13.269.694773603'
Wed Dec 28 11:19:41 2011
Errors in file /fin01/u0001/oracle/admin/MYDBNAME/bdump/MYDBNAME3_arc3_26985.trc:
ORA-16038: log 13 sequence# 35232 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_01/MYDBNAMEy/onlinelog/group_13.269.694773601'
ORA-00312: online log 13 thread 3: '+MYDBNAME_REDO_02/MYDBNAMEy/onlinelog/group_13.269.694773603'
Wed Dec 28 11:19:42 2011
Completed checkpoint up to RBA [0x89a2.2.10], SCN: 12575024869961
Wed Dec 28 11:23:07 2011
ARCH: Archival stopped, error occurred. Will continue retrying

If you read the alert log carefully it is evident that it is not able to archive the logs and the archiver is hung .

My alert log also shows:
Errors in file /fin01/u0001/oracle/admin/MYDBNAME/bdump/MYDBNAME3_arc1_26944.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 348966092800 bytes is 100.00% used, and has 0 remaining bytes available.
Wed Dec 28 11:28:07 2011



from the above it is clear that my FRA got filled up, lets confirm that.


DiskGroup Name %Used TOTAL_MB FREE_MB
-------------------- ----- ------------ ------------
MYDBNAME_FRA_01 85 392,616 59,785


ahh, my FRA is still has space of around 59gb then why I am still getting the alert as cannot archive and db_recovery_file_dest_size is 100% full



lets see here:

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 100 0 638
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 0 0 0

6 rows selected.

Above confirms that we filled up the FRA of the allocated size of 350gb.


Options:

Wed Dec 28 11:28:07 2011
************************************************************************
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.
***********************************************************************



Here since I already have 59gb left in FRA I picked up option 4:

SQL> alter system set db_recovery_file_dest_size=390gb scope=both sid='*';

Now my database can archive the logs:

SQL>alter system archive log current;

system altered

Now kicked of the RMAN backup which deletes the archivelogs at the end of the backup. This freed up the space in FRA.


If you dont have any space is your FRA to grow then use the below options:

From RMAN:

RMAN> connect target /
RMAN>delete noprompt archivelog until time '(sysdate-1)' backed up 1 times to device type disk;

or if you want to delete the logs older than certain hours ago then use.
This deletes logs older than 2 hours
RMAN>delete noprompt archivelog until time '(sysdate-1/11)' backed up 1 times to device type

or If you don't have any standby in place then backup all the logs and delete them instantly

RMAN>backup archivelog all delete input;

No comments: