Insufficient Flash Recovery area space causes DB to not open or hang handle method

when the archive directory is set up in the Flashback recovery area and the Flashback recovery area is full, the DB will not be archived and hang or cannot be opened.

In this case, opening the database will encounter the following error message:

Sql> select status from V$instance;

STATUS

------------

Mounted

sql> ALTER DATABASE open;

ALTER DATABASE Open

*

An error occurred on line 1th:

ORA-16014: Serial number 27 for log 2 is not archived and there are no available destinations

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

*

An error occurred on line 1th:

ORA-01109: Database is not open

sql> alter system switch logfile;

ALTER SYSTEM switch logfile

*

An error occurred on line 1th:

ORA-01109: Database is not open

sql> shutdown immediate;

ORA-01109: Database is not open

The database has been uninstalled.

The ORACLE routine has been closed.

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 is loaded.

ORA-16038: Log 2 serial number 27 cannot be archived

ORA-19809: Exceeded the limit of the number of recovered 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

*

An error occurred on line 1th:

ORA-16014: Serial number 27 for log 2 is not archived and there are no available destinations

ORA-00312: Online log 2 thread 1:

' D:/oracle/product/10.2.0/oradata/orcl/redo02. LOG '

By increasing the size of the flashback recovery area, we can open the database normally

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 changed.

sql> ALTER DATABASE open;

The database has changed.

Check the use of 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

6 rows have been selected.

Sql>

Calculate the space already 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

As you can see, there's already 2.1231G here, which means we're just starting to set up the db_recovery_file_dest_size=2g, causing the online redo log to fail to archive, where we set db_recovery_file The _dest_size parameter increases the flash recovery area to solve this problem.

Adding flash Recovery area is a workaround, or you can assign the archive to a different directory to solve the problem. Or back up the database and delete some archived files to release the Flash recovery area. can solve the problem.

"from:http://blog.csdn.net/tianlesoftware/article/details/4677378"

Insufficient Flash Recovery area space causes DB to not open or hang handle method