Wednesday, November 20, 2013

ORA-19815: WARNING: db_recovery_file_dest_size of 66571993088 bytes is 85.04% used, and has 9960423424 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************

Pre-check:
--To check the location and size
 set lines 100
col name format a60
select  name , floor(space_limit / 1024 / 1024) "Size MB", ceil(space_used  / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name;

show parameter db_recovery_file_dest_size

--To verify which is occupying space.
Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,number_of_files as "number" from v$flash_recovery_area_usage;

Solution:

Login to database using rman
if it in prod try to connect to catalog /in DR just connect to database.
try to purge old backup and old archive logs
rman >  crosscheck archivelog all;

rman >  delete expired archivelog all;

if backup :
rman > CROSSCHECK BACKUP;

rman >Delete expired backup;

On your standby database, connect it through RMAN:
rman target sys/<pwd>@<standby-db>
RMAN>delete archivelog all completed before 'SYSDATE-7';


This might be because of your ARCHIVELOG DELETION POLICY not being set correctly in your standby database. Please run the command below. Is it set to "NONE"?
RMAN> show archivelog deletion policy;
When set to NONE, archivelogs are not considered reclaimable until they are backed up. If you only backup archivelogs on the primary database, you can set the archivelog deletion policy to "APPLIED ON STANDBY" on the standby database. This will make archivelogs on the standby database reclaimable as soon as they have been applied. Once they are reclaimable, they will be deleted from the Flash Recovery Area when there is space pressure.

You can find all the details in the documentation, http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#BAJDJEBE

Traditionally, you set archivelog deletion policy to NONE where you take your backups (primary or standby). On the other side, where you don't want to take any backups, you can set it to APPLIED ON STANDBY.
Where backups are taken:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Where backups are NOT taken:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
If you change the archivelog deletion policy, the view v$flash_recovery_area_usage will reflect the change immediately. If I'm correct, and the policy is your problem, you should see that most of your archived redo logs are listed as reclaimable (if they have been applied to the standby database). Also, the alert log should show that logs are being deleted in order to make room for new ones as they are being transferred from the primary database

No comments:

Post a Comment