Limit on the size of 2 GB of the default archive path in the flash back area of Oracle 10 GB

Oracle 10gThe default archive path is2 GB space limit of the flash back areaThe problem is what we will introduce in this article. Next we will introduce it through a practical example. The instance causes this. When solving the problem for the customer, open the data decompression and check that they still use the Oracle 815 version (when their exp is exported, the export log is included, it can be seen from the export log that it is in oracle 815), but it does not matter. The exp of the lower version can be imported into the database of the later version with the imp of the later version.

It seems that the import is normal. When you import a table to it, it suddenly does not move. At first, I had not figured out what was going on. Later, I accidentally saw a lot of error messages in the Computer Management-Event Viewer:

 
 

  
  
Archive process error: ORA-16038: log 1 sequence# 317 cannot be archived  
  
  
ORA-19809: limit exceeded for recovery files  
  
  
ORA-00312: online log 1 thread 1: 'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORACLE/REDO01.LOG' 
 
 

I found out that the problem lies in archiving.

I also read the alert_oracle.log file and found many error messages. Here, this problem has taught me a lesson: oracle-related operations will certainly write logs to the alert_oracle.log file as long as there is a problem, check whether you are aware of this log file.

After checking the information on the Internet, I learned that Oracle 10 Gb archive logs are stored in the Flash recovery area by default. For me, E: /oracle/product/10.2.0/flash_recovery_area/ORACLE/ARCHIVELOG). If you use the default settings when creating a database, the flashback recovery zone should be 2 GB, once the space is full, it cannot be archived.

At this time, I have a prompt from sqlplus open database:

 
 

  
  
Microsoft Windows XP [version 5.1.2600]
  
  
(C) Copyright 1985-2001 Microsoft Corp.
  
  
C:/Documents and Settings/Administrator> sqlplus/as sysdba
  
  
SQL * Plus: Release 10.2.0.1.0-Production on Wednesday November 26 17:58:22 2008
  
  
Copyright (c) 1982,200 5, Oracle. All rights reserved.
  
  
Connect:
  
  
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
  
  
With the Partitioning, OLAP and Data Mining options
  
  
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 1 Serial Number 317 Unarchived, no available destination
  
  
ORA-00312: Online log 1 thread 1:
  
  
'E:/ORACLE/PRODUCT/10.2.0/ORADATA/ORACLE/redo01.log'
  
  
SQL>
 
 

How can this problem be solved? Online experts also provided a lot of methods the following method for reprint, original address: http://yaanzy.itpub.net/post/1263/286285 ):

Solution:

1. set the archive to another directory and modify alter system set log_archive_dest = to another path.

2. Transfer or delete the archived logs in the Flash recovery area.

3. Increase the flash recovery zone.

 
 

  
  
ALTER SYSTEM SET db_recovery_file_dest_size=4g scope=both; 
 
 

My solution is to use 3rd methods. below is my operation process:

 
 

  
  
SQL> show parameter db_recovery_file_dest_size;
  
  
NAME TYPE VALUE
  
  
-----------------------------------------------------------------------------
  
  
Db_recovery_file_dest_size big integer 2G
  
  
SQL> alter system set db_recovery_file_dest_size = 3G;
  
  
The system has been changed.
  
  
SQL> alter database open;
  
  
The database has been changed.
  
  
SQL> show parameter db_recovery_file_dest_size;
  
  
NAME TYPE VALUE
  
  
-----------------------------------------------------------------------------
  
  
Db_recovery_file_dest_size big integer 3G
  
  
SQL>
 
 

It is worth noting that after I finish executing alter system set db_recovery_file_dest_size = 3G; then I will immediately go to show parameter db_recovery_file_dest_size; 3G is displayed, instead of 2G. In another aspect: E:/oracle/product/10.2.0/db_1/dbs/SPFILEORACLE. the modification time of the ORA file is that I execute alter system set db_recovery_file_dest_size = 3G. This proves that this change takes effect immediately.

If the available space in the archive path is expanded to 3 GB, that is, 1 GB is added on the basis of the original 2 GB. the new archive log under oracle database actually uses this new 1 GB space. Someone may ask, "Can I delete the 2G archive logs that have already been created, but can't oracle database use 3G ?" In fact, this is not the case. Although 2 GB has been deleted in the physical space, the dynamic performance view v $ recovery_file_dest does not release this 2 GB space, you can use select * from v $ recovery_file_dest to query the data.

If you do not delete the 2 GB space from the dynamic performance view, oracle database considers that the 2 GB space is still occupied. If there is a large transaction commit and frequent log switching, 1 GB of space will be used up immediately. At that time, your alert_oracle.log will have an error, for example:

 
 

  
  
ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available.  
  
  
*** 2008-11-28 10:05:13.375  
  
  
************************************************************************  
  
  
You have following choices to free up space from flash recovery area:  
  
  
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,  
  
  
   then consider changing RMAN ARCHIVELOG DELETION POLICY.  
  
  
2. Back up files to tertiary device such as tape using RMAN  
  
  
   BACKUP RECOVERY AREA command.  
  
  
3. Add disk space and increase db_recovery_file_dest_size parameter to  
  
  
   reflect the new space.  
  
  
4. Delete unnecessary files using RMAN DELETE command. If an operating  
  
  
   system command was used to delete files, then use RMAN CROSSCHECK and  
  
  
   DELETE EXPIRED commands.  
  
  
************************************************************************  
  
  
ORA-19809: limit exceeded for recovery files  
  
  
ORA-19804: cannot reclaim 47715840 bytes disk space from 3221225472 limit  
  
  
*** 2008-11-28 10:05:13.406 60680 kcrr.c  
  
  
ARC0: Error 19809 Creating archive log file to 'E:/ORACLE/PRODUCT/10.2.0/FLASH_RECOVERY_AREA/ORACLE/ARCHIVELOG/2008_11_28/O1_MF_1_344_%U_.ARC' 
 
 

To solve the preceding problem, you need to delete the occupied space information in the dynamic performance view. The method in the http://www.eygle.com/archives/2005/03/oracle10gecieif.html article by the eygle master is to use rman to delete the information. The rman command used is as follows:

1. is RMAN> crosscheck archivelog all; -- this command indicates to check the status of all archived logs and mark the missing logs as expired, that is, expired indicates the archived logs that have been deleted by the operating system.

2. delete expired archivelog all; -- this command indicates deleting expired archive logs.

This article introduces how to limit the size of 2 GB of Oracle 10 Gb default archive path in the flash back area. I hope this introduction will help you gain some benefits!