?
Solved

flash_recovery_area full-- how do you use RMAN to delete directories of .arc files

Posted on 2008-11-09
2
Medium Priority
?
2,741 Views
Last Modified: 2012-05-05
About a week ago we turned archivelog mode on in an 11g oracle server.  The flash_recovery_area is now full, and redo logs can't be archived, so the database won't open.

I need to delete some of the dated directories containing the .arc files using rman.  What is the exact process for connecting using rman and for deleting these directories?

Also-- I use dbconsole to RMAN to backup the database to local disk, and Computer Associates Arcserve to backup to tape.  I expected the archivelogs to be deleted (like they are truncated in MS SQL server) when a backup is done.  Is this not a correct expectation?

I've read that one can also free up space by backing up components of the flash_recovery_area.  I can only do this to disk.  Anyone know exactly the complete correct syntax for rman?
0
Comment
Question by:dakota5
2 Comments
 
LVL 16

Accepted Solution

by:
Richard Olutola earned 1400 total points
ID: 22920862
FRA should be automatically managed. However, if this fails, you can use the following methods to free up space.

Log into RMAN in a command shell

Assuming you're on Linux/Unix

export ORACLE_SID=<sid>

rman target /

You should now be in RMAN CLI (Command Line Interface)

Show all;
This will show you all configured persistent settings. What you want to watch is RETENTION POLICY. Make sure this is not set to some ridiculous value. You may need to adjust this parameter to a more acceptable value like RECOVERY WINDOW OF 5 DAYS

One this is set (or if the the current setting is acceptable), now run the following commands:

crosscheck database;
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt obsolete;
delete noprompt expired backup;

These commands should free up space for you by reclaiming the space from redundant backups according to you backup policy.

Finally, when you issue the backup database command in RMAN, I always backup the archivelogs at the same time and then delete the backed-up archivelogs in one command as follows:

RMAN> backup database plus archivelog delete input;

Without this command, if you use FRA for your archivelogs, you may run into space issues.

R.
0
 
LVL 6

Assisted Solution

by:peter991
peter991 earned 600 total points
ID: 22920983
Hi!
Here are som useful commands:

-find backupset to be deleted.
RMAN> list backup of database summary;


RMAN> list backupset 123;
...
.....

RMAN> delete backupset 123;


SQL> SELECT object_type, message_type, message_level,
reason, suggested_action
FROM dba_outstanding_alerts;

The following actions can be done to resolve the space issue :
- Add disk space to the Flash Recovery Area.
- Back up your files to a tertiary device.
- Delete the files from the Flash Recovery Area using RMAN.
- Changing RMAN retention policy. If you are using Data Guard, then consider changing
   RMAN ARCHIVELOG DELETION POLICY.

The database handles a Flash Recovery Area with insufficient reclaimable space  just as it handles a disk full condition. Often, the result is a hang of the database.

To free space in the FRA, use command BACKUP RECOVERY AREA.
This command will take the backup of al the files in the FRA to tape only.
After this the space occupied by the files in the FRA will be marked as reclaimable.

RMAN> backup recovery area;

If you deleted files via OS then use RMAN CROSSCHECK.

Read more:
Metalink-notes: 305812.1 , 401163.1 , 305817.1 and 315098.1
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question