Flash Recovery Area space is insufficient, so the database cannot be opened or hang

Solutions to ORA-16014 errors
1. Problems and Solutions
SQL> select status from v $ instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-16014: log 2 serial number 27 Unarchived, no available destination
ORA-00312: Online log 2 thread 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

SQL> show parameter db_recovery_file
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string D:/oracle/product/10.2.0/flash_recovery_area
Db_recovery_file_dest_size big integer 2G
SQL> alter system archive log current;
Alter system archive log current
*
Row 3 has an error:
ORA-01109: the database is not open

SQL> alter system switch logfile;
Alter system switch logfile
*
Row 3 has an error:
ORA-01109: the database is not open

SQL> shutdown immediate;
ORA-01109: the database is not open

The database has been detached.
The ORACLE routine has been disabled.
SQL> startup
The ORACLE routine has been started.
Total System Global Area 201326592 bytes
Fixed Size 1248092 bytes
Variable Size 88081572 bytes
Database Buffers 109051904 bytes
Redo Buffers 2945024 bytes
The database has been loaded.
ORA-16038: log 2 serial number 27 cannot be archived
ORA-19809: exceeds the limit on the number of recovery files
ORA-00312: Online log 2 thread 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-16014: log 2 serial number 27 Unarchived, no available destination
ORA-00312: Online log 2 thread 1:
'D:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO02.LOG'

SQL> show parameter db_recovery
NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_recovery_file_dest string D:/oracle/product/10.2.0/flash_recovery_area
Db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size = 3G scope = both;
The system has been changed.
SQL> alter database open;
The database has been changed.
2. Reflection:
(1). Check the usage of the flash recovery area:
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 6.36 0 4
BACKUPPIECE. 22 0 1
IMAGECOPY 63.68 0 5
FLASHBACKLOG. 51. 25 2
You have selected 6 rows.
SQL>
(2) computing the space occupied by the flash recovery area:
SQL> select sum (percent_space_used) * 3/100 from v $ flash_recovery_area_usage;
SUM (PERCENT_SPACE_USED) * 3/100
-----------------------------
2.1231
We can see that 2.1231G has been used here. This indicates that the db_recovery_file_dest_size = 2G we just set is insufficient, leading to online redo log archiving failure. Here, we set the db_recovery_file_dest_size parameter, increase the flash recovery area to solve this problem.
(3) You can also delete unnecessary backups in the flash recovery area to release the flash recovery area space to solve this problem:
(1). delete obsolete;
(2). crosscheck backupset;
Delete expired backupset;

From: David Dai Oracle notes