Solution for oracle archiving logs exceeding the flash_recovery_area limit

Solution to oracle archive log exceeding the recovery flash_recovery_area limit I. Problem description: The system reports the following error: ORA-16038: Unable to archive ORA-19809: ORA-00312: Online log 2, Problem Analysis: oracle generates a large number of archive log files in the Flash recovery area at intervals when archiving logs are enabled by the system. These files can be used to restore the database to any point in time when the database fails. (Cause 1) archive logs are stored in the Flash recovery area (/oracle/app/flash_recovery_area/ORCL/archivelog). The default flash recovery area is generally 4 GB, when the number of archived log files exceeds 4 GB, archiving fails, and database access and various operations fail. (Cause 2) the archive log does not exceed the setting in the Flash recovery area, but the disk space is insufficient (usually because the flash recovery area is larger than the remaining disk space ), the preceding error may also occur. 3. Solution: You can solve the problem in the following three ways, but try to back up the database before the operation. (Method 1) Disable archiving logs and delete archived log files. You must back up the database before deleting logs.

Disable the archiving log function: SQL> archive log list; // check whether the archive mode is used. SQL> alter system set log_archive_start = falsescope = spfile; // disable automatic archiving SQL> shutdown immediate; SQL> startup mount; // open the control file, do not open the data file SQL> alter database noarchivelog; // switch the database to non-archive mode SQL> alter database open; // open the data file SQL> archive log list; // check that the data file is in non-archive mode SQL> select * from v $ log; view the list of archive file groups: GROUP # THREAD # SEQUENCE # bytes blocksize members arc status FIRST_CHANGE # FIR ST_TIM NEXT_CHANGE # NEXT_TIME ---------- hour ------------ ---------- --- hour ------------ --------- 1 1 13516 52428800 512 1 no inactive hour 363981839 07-NOV-12 2 1 13515 52428800 1 no inactive hour 512 07-NOV-12 3 363952887 13517 512 1 no current 36398183907-NOV-12 2.8147E + 14 SQL> alter database cl Ear unarchived logfile group 1; SQL> alterdatabase clear unarchived logfile group 2; SQL> alterdatabase clear unarchived logfile group 3; SQL> shutdownimmediate; SQL> startupopen; (method 2) increase the setting of the flash recovery area. $ Sqlplus/as sysdbasql> showparameter db_recovery_file_dest; SQL> shutdown immediatesql> startup mountsql> alter system set timeout = 20g scope = both; SQL> shutdown immediatesql> startupsql> quit

 

(Method 3) increase the disk space to delete or remove unnecessary files, or increase the disk space. Iv. References: (1) Open the archive log:

SQL> archive log list; // check whether the archive mode is used. SQL> alter system set log_archive_start = true scope = spfile; // enable active archiving SQL> shutdown immediate; SQL> startup mount; // open the control file, do not open the data file SQL> alter database archivelog; // switch the database to archive mode SQL> alter database open; // open the data file SQL> archive log list; # Check whether the data file is in archive mode to check whether the database is in archivelog mode and the archiving process is running SQL> select log_mode from v $ database; SQL> select archiver from v $ instance;

 

(2) Archiving commands

Archive log stop; archive log start; archive log list; show parameters log_archive_start; show parameters log_archive_max_process; # alter system set log_archive_max_process = 3; # change the number of archiving processes to 3 select * from v $ bgprocess; # Check the background process