Db_recovery_file_dest_size Modify a larger and delete archive log |

Today, customers test the problem, let the customer send the data over. After the decompression, they still use the Oracle 815 version (they exp export, with the export log, from the export log to see the Oracle 815 version), but it is not related to the low version of Exp can be imported into the high version of the IMP version of the database. A look at the import is also very normal, import into one of the tables, suddenly stopped moving. At first I hadn't figured out what was going on. Later, inadvertently saw the Computer Management-Event Viewer, there are many error messages:

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 was in the archives.

Also looked at the Alert_oracle.log file, there are a lot of this error message. Here, this question gives me a lesson: Oracle-related operations, as long as there is a problem, will certainly write to the Alert_oracle.log file log, it depends on your awareness to see the log file.

To go to google.com to search a bit of information, it just dawned:
oracle10g by default, archived logs are saved in the Flashback recovery area (for me: E:\oracle\product\10.2.0\flash_recovery_area\ORACLE\ARCHIVELOG), If you use the default settings when you build the library,
The flashback recovery area should be 2G and the space will not be archived after it is fully occupied.

At this time I am from Sqlplus Open database, there are hints:

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, 2005, Oracle. All rights reserved.

Connect to:
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
*
An error occurred on line 1th:
ORA-16014: Serial number 317 for log 1 is not archived and there are no available destinations
ORA-00312: Online log 1 thread 1:
' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLE\REDO01. LOG '

Sql>

/*-------------------------Complete------------------------*/

So how do you solve this problem? Online experts also give a lot of methods (the following method for reprint, the original address of Bodhi Lao zu blog http://yaanzy.itpub.net/post/1263/286285):

Workaround:

1. Set the archive to a different directory, modify alter system set Log_archive_dest = Other path

2. Transfer or delete the archive log in the Flashback recovery area.

3. Increase the flash back recovery area.
ALTER SYSTEM SET db_recovery_file_dest_size=4g scope=both;

My approach is to use the 3rd method, 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 changed.

sql> ALTER DATABASE open;

The database has changed.

Sql> Show parameter db_recovery_file_dest_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_recovery_file_dest_size Big Integer 3G
Sql>

/*-------------------------Complete------------------------*/

It is worth noting that I executed the ALTER system set DB_RECOVERY_FILE_DEST_SIZE=3G, and immediately went to show parameter db_recovery_file_dest_size; At this point the 3g is displayed, not the original 2g. From another aspect: E:\oracle \product\10.2.0\db_1\dbs\spfileoracle. Ora This file modification time, is I execute ALTER system set DB_RECOVERY_FILE_DEST_SIZE=3G; This is even more proof that the change will take effect immediately.

It is worth noting that the available space under the archive path is expanded to 3G, which adds 1G on top of the original 2G. The newly formed archive log under Oracle database is actually used for this new 1G space. Perhaps someone will ask, "then I have already formed a 2G archive log deleted, Oracle database will not be able to use 3G?" "This is not the case, although in the physical space, 2G has been removed, but the dynamic performance view (V$recovery_file_dest) does not release this 2g space, you can use the SELECT * from V$recovery_file_dest query out. If you do not remove this 2G space from the dynamic performance view, Oracle database will assume that this 2G is still in use. If there is a big thing to commit, and there are frequent log switching, 1G of space will be used up immediately, then your alert_oracle.log will be wrong to appear, 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 are 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 the 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 above problem, you need to remove the information about the occupied space in the dynamic performance view. According to Master Eygle in the http://www.eygle.com/archives/2005/03/oracle10gecieif.html article, the method is to use Rman to delete this information. The Rman command used is as follows:

1. rman> crosscheck archivelog all;--The meaning of this command is to check the status of all archived logs and mark the missing as expired, that is, expired indicates that the archive log has been deleted by the operating system.
2. Is the delete expired archivelog all; --The meaning of this command is to delete the archive log for expired

Pro-Test:

Delete the previous archivelog after a level two incremental backup

Backup incremental Level 2 database plus archivelog;

The effect is still very strong, only 1G (three days ago the first backup is 7g,1 level levels after the backup is 6.5G, and no plus plus archivelog)

Delete an archive log

Rman> crosscheck Archivelog All;

Strange, why the detection no 1.13th number, is not I backed up in front of

Remove the number 15th look.

Finally, there's a mistake.

Execute delete expired archivelog all;

Strange, after I deletebackup, how is the level two increment still in

Additional 1:

When the Oracle archive log is full, you will not be able to log in to Oracle properly, and you need to delete a subset of the archived logs to log in to Oracle properly.

First, delete the archive log physical files, archive logs are generally located in the archive directory, AIX system file format is "1_17884_667758186.DBF", it is recommended to backup the database before operation, delete at least the last few days of the log for database recovery.

Second, after the physical file of the archived log is deleted, we can log into Oracle normally, but the archive log is not completely deleted, and Oracle's controlfile still records these archivelog information. In Oracle's OEM manager there is a visual log showing that when we manually clear the files in the archive directory, these records are not erased from the controlfile, and then we have to do this job.

We use Rman to delete operations, the following steps: (Window client system for example)

1. Specifying a DB instance

C:/Documents and Settings/administrator>set Oracle_sid =ORCL

2. Connect to the database

C:/Documents and Settings/administrator>rman TARGET sys/[email protected]

3. View the status of archived logs

rman> list Archivelog all;

4. Manually delete archived log files

Rman> DELETE ARCHIVELOG all completed before ' SYSDATE-7 ';

Description
SYSDATA-7, indicates that the current system time 7 days ago, the Before keyword represents the archive log 7 days ago, and if the flashback function is used, the flashback data is also deleted.
In the same way, you can delete all logs from 7 days ago to the present, but this command should be considered clearly, after this deletion, it is best to make full backup database immediately
DELETE ARCHIVELOG from time ' SYSDATE-7 '; Delete all logs from 7 days ago to the present, with caution
Unix/linux can also find the archived data 7 days ago using the EXEC sub-operation to delete
Find/oraarchive-xdev-mtime +7-name "*.dbf"-exec rm-f {};
Doing so will still leave an undocumented archive in Rman
You still need to execute the following 2 commands in Rman
Crosscheck Archivelog All;
Delete Expired Archivelog all;
So it's not as good as the method above, but the advantage of using find is that you can do a lot of things on the condition, and on the Exec subkey, to achieve more complex functions

5. Exiting Rman

Rman> exit

Additional 2:

Oracle about log files basic operations

1. What set of log files is used by the query system:
SELECT * from V$log;

2. Query the log file for the group you are using:
SELECT * from V$logfile;

3. Force log Switchover:
alter system switch logfile;

4. Query history log:
SELECT * from V$log_history;

5. The archive mode of the query log:
Select Dbid,name,created,log_mode from V$database;

6. Query the archive log for information:
Select Recid,stamp,thread#,sequence#,name from V$archived_log;

7. Adding and deleting log file groups
ALTER DATABASE Add logfile Group 1 ('/home1/oracle/oradata/ora8i/log1a.log '), '/home2/oracle/oradata/ora8i/log1b.log ' ) Size 100M;

ALTER DATABASE drop logfile Group 1;

8. Adding and deleting log members
ALTER DATABASE add logfile member '/home1/oracle/oradata/ora8i/log1a.log ' to group 1, '/home1/oracle/oradata/ora8i/ Log2a.log ' to group 2;

ALTER DATABASE drop logfile member '/home1/oracle/oradata/ora8i/log1a.log ';

9. log file Movement
Alter DATABASE rename file '/home1/oracle/oradata/ora8i/log1a.log ' to '/home2/oracle/oradata/ora8i/log1a.log ';
You must ensure that the log file is physically moved to a new directory before executing the command

10. Clear the Log file
ALTER DATABASE clear logfile '/home1/oracle/oradata/ora8i/log1a.log ';
This command is used when the log cannot be deleted with the delete Group and group member commands

8. View the archive log footprint:
Select space_limit/1024/1024/1024 as "Quota_g", space_used/1024/1024 as "Used_m", space_used/space_limit*100 "Used_%", Space_reclaimable as reclaimable,number_of_files as files from V$recovery_file_dest;

9. Modify the archive log space size
alter system set db_recovery_file_dest_size=40g;

10. View the list of archived logs
rman> list Archivelog all;
Delete Archive Log
rman> Delete archivelog until time ' sysdate-1 ';
can also be used under the dual machine
Delete obsolete;
Crosscheck Archivelog All;
Delete Expired Archivelog all;

11. Use the list expired to see if there is an invalid archive log, proving that there is no invalid archive log:
rman> list expired Archivelog all;

Zhuan:http://www.cnblogs.com/sumsen/archive/2013/01/17/2864008.html

Db_recovery_file_dest_size Modify a larger and delete archive log |