ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database

Oracle database instance hung due to recovery area full causing “Archival Stopped Error”.

Check alert.log with below ORA errors:

— Alert.log

ORA-19816: WARNING: Files may exist in db_recovery_file_dest that 
           are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.
*************************************************************

Check recovery area view , which says a lot of free space available:

SQL> set pagesize 120
SQL> set linesize 120
SQL> select * from v$recovery_area_usage;

FILE_TYPE    PERCENT_SPACE_USED  PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-----------  ------------------- ------------------------- ---------------
...
..
ARCHIVED LOG               33.5                         0.8            248
BACKUP PIECE                0                           0                0
...
..
.
7 rows selected.

Check the database resetlog date and time:

SQL> alter session set nls_date_format='YYYYMMDD-HH24:MI:SS';

Session altered.

SQL> select CREATED,RESETLOGS_TIME,PRIOR_RESETLOGS_TIME,
            CONTROLFILE_TIME,VERSION_TIME from v$database;

CREATED           RESETLOGS_TIME    PRIOR_RESETLOGS_T CONTROLFILE_TIME  VERSION_TIME
----------------- ----------------- ----------------- ----------------- -----------------
20180817-11:05:10 20180817-11:05:43 20161009-20:55:02 20180821-15:19:21 20180817-11:05:10

CAUSE

There are archivelogs in recovery area from old databases, which were refreshed or restored with different DB ID, manually delete them, then everything is fine:

ASMCMD> ls -ltr
Type  Redund  Striped  Time             Sys  Name

                                        ...                                        ...
                                        ..
                                        .
                                        Y    2018_08_14/
                                        Y    2018_08_15/
                                        Y    2018_08_16/
           RESETLOGS DATE      ----->   Y    2018_08_17/
                                        Y    2018_08_18/
                                        Y    2018_08_19/
                                        Y    2018_08_20/

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.