ORA-19815 Problem Discovery and Solution

During the Bi development process today, some people reported that they could not log on. Then they tried it and found that they did prompt that the archive failed when they logged on to PL/SQL developer. Then check alert_orcl.log, found the error message: ORA-19815: Warning: db_recovery_file_dest_size of 2147483648 bytes is 100.00%, use statement Query

Select substr (name, 1, 30) Name, space_limit as quota, space_used as used, space_reclaimable as reclaimable, number_of_files as files from V $ recovery_file_dest;

The usage rate is 100%, so it is determined that the db_recovery_file_dest directory is full. Run the show parameter db_recovery_file_dest command to query the disk directory and export the unused archive to free up disk space.

Run the following command to clear the log group:

Alter database clear Unarchived logfile group 1;

Alter database clear Unarchived logfile group 2;

Alter database clear Unarchived logfile group 3;

Restart the instance and check the connection.

 

Also: RMAN can be used to solve this problem, see the blog http://www.eygle.com/archives/2005/03/oracle10gecieif.html of eygle for details

Postscript:

Early the next morning, I found it unavailable again. The error message is

Errors in file E:/Oracle/product/10.2.0/admin/orcl/bdump/orcl_arc1_7296.trc:
ORA-16038: Log 1 sequence #674 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: Online log 1 thread 1: 'e:/Oracle/product/10.2.0/oradata/orcl/redo01.log'

The database is suspended again.

Use RMAN to delete old expired logs

C:/> RMAN

RMAN> connect target sys/Oracle

RMAN> crosscheck archivelog all;

RMAN> Delete noprompt expired copy;

..........

RMAN>

Check space usage after execution

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 8.48 0 4
Backuppiece 0 0 0
Imagecopy 0 0 0
Flashbacklog 50.69 50.36 153

You have selected 6 rows.

SQL> alter database open

You can see that the space usage is back to normal.

 

It can also be mitigated temporarily by expanding the flash area size. However, this method should be a non-permanent indicator.

Alter system set db_recovery_file_dest_size = 4G scope = both;