Experience the 2G size limit of oracle10g default archive path (in the flash back area)

Test the problem for the customer today and ask the customer to send the data. After decompression, we can see that they still use the oracle815 version (the export log is included when their exp is exported, and the export log shows that it is oracle815 version), but it does not matter, the exp of earlier versions can be imported to the database of later versions using the imp of later versions. First, the import is still

Test the problem for the customer today and ask the customer to send the data. After decompression, we can see that they still use the oracle 815 version (the export log is included when their exp is exported, and it can be seen from the export log that it is the oracle 815 version), but it does not matter, the exp of earlier versions can be imported to the database of later versions using the imp of later versions. First, the import is still

To customers todayTestAsk the customer to send the data. After decompression, they still useOracleVersion 815 (the export logs are included when their exp is exported, and oracle version 815 is included in the export logs), but it does not matter, exp of earlier versions can be imported to later versions with imp of later versions.Database. 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.

. GoGoogle. Com found some information, and then suddenly realized:
Oracle10g by default, archive logs are stored in the Flash recovery zone (for me: E:/oracle/product/10.2.0/flash_recovery_area/ORACLE/ARCHIVELOG ), if you use the default settings when creating a database,
The Flash recovery zone should be 2 GB. After the space is full, the data cannot be archived.

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

Microsoft Windows XP [version 5.1.2600]

C:/Documents and Settings/Administrator> sqlplus/as sysdba

SQL * Plus: Release 10.2.0.1.0-Production on Wednesday November 26 17:58:222008

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect:
Oracle Database10gEnterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP andDataMining 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>

/* ------------------------- Completed ------------------------*/

Blog of BodhiHttp://yaanzy.itpub.net/post/1263/286285):

Solution:

1. set the archive to another directory and modify alter system set log_archive_dest = 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>

/* ------------------------- Completed ------------------------*/

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.

It is worth noting that 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) has not released 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, and then 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.
* ** 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
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
* ** 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_2017344 _ % 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.