Flashback_area overflow causes database downtime

Problem:
ORA-00257
ORA-16014 log 1 sequence #1085 not archived, no available destinations
ORA-00312 online log 1 thread 1 'usrapporacleoradatawanyanredo01. Log'

Cause:
2 GB of the flash recovery area of the archived logs is full.

Check Procedure:

SQL> shutdown immediate
SQL> startup
ORA-00257
ORA-16014 log 1 sequence #1085 not archived, no available destinations
ORA-00312 online log 1 thread 1 'usrapporacleoradatawanyanredo01. Log'
SQL> alter database open;
ERROR at line 1:
The ORA-16014: log 1 sequence #92 not archived, no available destinations
ORA-00312: online log 1 thread 1: 'C: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ redo01.log'

SQL> select * from v $ log
;

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIME
--------------------------------------------------------------------------------------------
1 1 93 52428800 1 no inactive 4345718 04-FEB-09
3 1 94 52428800 1 no inactive 4347356 04-FEB-09
2 1 95 52428800 1 no current 4368182 04-FEB-09

Based on the above information, check the archive space because GROUP1 cannot be archived.

SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest_size big integer 3G

Troubleshooting procedure

Method 1: delete a recovery file

After checking the actual archive folder, it is found that the actual archive space is much less than 3 GB, probably due to manual deletion of archive logs.
Use the following methods:

After the archived logs are manually deleted, the Rman backup detects that the logs are missing and cannot be further executed.
Therefore, you need to manually execute the crosscheck process, and then the Rman backup can be restored to normal.
1. Crosscheck log

$ Rman target/
Recovery Manager: Release 9.2.0.4.0-64bit Production
Copyright (c) 1995,200 2, Oracle Corporation. All rights reserved.
Connected to target database: AVATAR2 (DBID = 2480694409)

RMAN> crosscheck archivelog all;

Using target database controlfile instead of recovery catalog
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: sid = 25 devtype = DISK
Validation failed for archived log
.........................................

2. Run the delete expired archivelog all command to delete all expired archived logs:

RMAN> delete expired archivelog all;

RCL \ ARCHIVELOG \ 2009_02_04 \ o1_mf_1_86_4rl21_wb_.arc
81 1 87X04-2 month-09 C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ O
RCL \ ARCHIVELOG \ 2009_02_04 \ o1_mf_000087_4rl2rygn_.arc
82 1 88X04-2 month-09 C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ O
RCL \ ARCHIVELOG \ 2009_02_04 \ o1_mf_000088_4rl2sgfo_.arc
83 1 89X04-2 month-09 C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ O
RCL \ ARCHIVELOG \ 2009_02_04 \ O1_MF_1_89_4RL2SY0H_.ARC
84 1 90X04-2 month-09 C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ O
RCL \ ARCHIVELOG \ 2009_02_04 \ o1_mf_000090_4rl2tob8_.arc
85 1 91X04-2 month-09 C: \ ORACLE \ PRODUCT \ 10.2.0 \ FLASH_RECOVERY_AREA \ O
RCL \ ARCHIVELOG \ 2009_02_04 \ o%mf_%91_4rl2vhc7_.arc

SQL> alter database open;

Database altered.

Method 2: Modify the default parameter file so that the database can store large recovery files

The FLASH_RECOVERY_AREA space is changed to 20 GB based on the actual storage space of the database being 200 GB and 2 GB.

Alter system set DB_RECOVERY_FILE_DEST_SIZE = 20g;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12