An Analysis of database faults caused by no available space in the FRA Region of Oracle 10g

Document directory

• What is FRA?

 

If FRA is full, online logs cannot be archived.

 

When I started the database this morning and found that the database was not opened, I checked the database status:

SQL> select open_mode from V $ database;   Open_mode ---------- Mounted

 

 

SQL> select open_mode from V $ database;   Open_mode ---------- Mounted   SQL> alter database open; Alter database open * Row 3 has an error: ORA-16014: log 2 serial number 2 Unarchived, no available destination ORA-00312: Online log 2 thread 1: 'C: \ oracle \ product \ 10.2.0 \ oradata \ keymen \ redo02.log'

Check the disk space and find that there is enough space on the hard disk. What problems can cause online logs to be unable to be archived? Then, check the fast flash back area (flash_recovery_area) of Oracle10g and find that the occupied space has reached 1.99 GB (2146861056 bytes ). It seems that the problem is here.

Make a simple calculation:

SQL> select 2146861056/1024/1024/1024 as "FRA usage" from dual;   FRA usage ---------- 1.99942017

 

In Oracle10g, the FRA capacity is 2 GB by default, and the current space usage has reached 1.99 GB. Archive logs are not archived, so the database cannot be opened. You need to adjust the FRA size.

 

 

What is FRA?

The quick recovery zone is the unified storage location for all recovery-related files and activities in the Oracle database. All files required to completely recover the database from a media fault are included in the quick recovery zone. Recovery-related files that can be created in the quick recovery area include archive redo log files, control files, backup files created by recovery Manager (RMAN), flash log and change tracking files. By allocating storage locations and unifying recovery-related files to specific regions, the Oracle database server reduces the burden on Database Administrators so that they do not have to manage disk files created by these components. The default location of the quick recovery zone is $ oracle_base.

 

View the number of records in FRA

SQL> select substr (name, 1, 30) Name, space_limit as quota, 2 space_used as used, 3 space_reclaimable as reclaimable, 4 number_of_files as files 5 from V $ recovery_file_dest; Name quota used reclaimable files --------------------------------------------------------------------------------- C: \ oracle \ product \ 10.2.0/flash 5242880000 2256708096 0 66

 

View the file types recorded in FRA

SQL> select file_type from V $ flash_recovery_area_usage;   File_type ------------ Controlfile Onlinelog Archivelog Backuppiece Imagecopy Flashbacklog   You have selected 6 rows.