Looking for something? Try here..

Wednesday, February 3, 2016

Flash recovery area limit exceeded

Here is a simple basic troubleshooting that happened today.
I was starting the database to encounter the error below.
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             662700496 bytes
Database Buffers          402653184 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5636
Session ID: 5 Serial number: 3

Checking the alert log for what has just happened and why is this throwing an error, I can find the following in the alert log file.
...
...
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 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 e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 41948672 bytes disk space from 4039114752 limit
ARCH: Error 19809 Creating archive log file to 'E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_02_04\O1_MF_1_229_%U_.ARC'
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc:
ORA-16038: log 1 sequence# 229 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'
USER (ospid: 5636): terminating the instance due to error 16038
ARC1: Archival started
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc0_6168.trc:
ORA-01092: ORACLE instance terminated. Disconnection forced
ARC2: Archival started
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc0_6168.trc:
ORA-01092: ORACLE instance terminated. Disconnection forced
Thu Feb 04 10:20:47 2016
ARC3 started with pid=23, OS id=6272 
Instance terminated by USER, pid = 5636
See the highlighted last line which states the process ID responsible for the instance termination. The flash recovery area for my test database is set at 3 GB and now it is full.

Solution:
Clear some of the files by backing up or deletion from the flash recovery area and retry to startup the database.
I now have cleared the space after taking a backup of required archive logs.

Note: Deleting files manually from OS level will not be considered as freeing up space for the recovery area as they are managed by RMAN and Oracle. In this case again the startup will fail. You need to login to RMAN, crosscheck all the recovery area files and delete expired archive log and backup files to let Oracle know that we now have free space in the flash recovery area.
After performing these above, we can start up the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             662700496 bytes
Database Buffers          402653184 bytes
Redo Buffers                4603904 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Priya>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 4 10:45:20 2016

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

connected to target database: ORCL (DBID=1421168703, not open)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
validation failed for archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC RECID=83 STAMP=899754777
validation failed for archived log
...
...
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_227_CBV8YSFP_.ARC RECID=177 STAMP=902576057
validation failed for archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_228_CBV903F8_.ARC RECID=178 STAMP=902576095
Crosschecked 96 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
83      1    133     X 29-DEC-15
        Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC

84      1    134     X 29-DEC-15
        Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_134_C855T36B_.ARC
...
...
...

178     1    228     X 31-JAN-16
        Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_228_CBV903F8_.ARC


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC RECID=83 STAMP=899754777
deleted archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_134_C855T36B_.ARC RECID=84 STAMP=899754787
...
...
...

RMAN> exit


Recovery Manager complete.

C:\Users\Priya>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 10:46:15 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL>

Now my database is open again.

Happy troubleshooting!

No comments:

Post a Comment