ORA-19815: WARNING: db_recovery_file_dest_size of 11646 bytes is 100.00% used, and has 0 remain

Problem:

You notice the following error in the alert.log of the Data Guard Standby database. Your Standby is configured to write its archive logs to the Fast Recovery Area.

ORA-19815: WARNING: db_recovery_file_dest_size of 11646 bytes is 100.00% used, and has 0 remain

Solution:

You have several of options in resolving this issue as suggested in the error message. Below we will detail the options available along with providing recommendations.

1)    Changing the RMAN RETENTION POLICY or Backup using BACKUP RECOVERY AREA

              2)    Adding space to DB_RECOVERY_FILE_DEST that can be reflected in DB_RECOVERY_FILE_DEST_SIZE

     

              3)    Delete archive logs that are no longer needed and set ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Delete archive logs that are no longer needed and set ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Removing archive logs that are no longer needed is a quick and easy way to free up space. Using RMAN is the recommended method and removes a lot of the guess work out of which logs to remove.

In RMAN you can use the delete archivelog all completed after|before|between ‘date’; command to remove archive logs that are no longer needed. Below is an example.

 
RMAN> delete archivelog all completed before 'sysdate';

Setting the ARCHIVE DELETION POLICTY TO APPLIED ON STANDBY is the best solution for ongoing archive log maintenance on the Data Guard Standby.


More :

ASM Real-Time Scenarios : Click Here


Performance Tuning Basics

How do you purge old archivelogs which are applied on Standby DB

 Is there a command in RMAN for standby db  which list the old archivelogs that has been applied and can be deleted ?

SOLUTIONS :

In a dataguard configuration, the archivelogs from the FRA will be automatically purged when the following conditions are met by the database. If you need it purged in at the Primary site, set it on Primary database.

1) Prior to 11g, if not using mandatory archivelog destinations, the database (primary and standby) must be restarted with the following parameter:

 SQL> alter system set "_ log_deletion_policy"='ALL' scope=spfile;  

2) configure the following parameter in RMAN (primary and standby):

 RMAN> CONFIGURE  ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;  

3) Use Below Script

 $ cat delete.sh  
 [oracle@ora-db u01]$ cat delete.sh  
 ORACLE_HOME=<ORACLE_HOME path>  
 export ORACLE_HOME  
 PATH=$PATH:$ORACLE_HOME/bin  
 export PATH  
 rman target sys/<pwd>@<standbydb> <<EOF  
 spool log to "/u01/delete.log";  
 run  
 {  
 delete force noprompt archivelog all completed before 'SYSDATE-4';  
 }  
 EXIT;  
 EOF  

This would delete all the archives before SYSDATE-4. If this does not work, then you can try deleting them by checking the sequence that was last applied and delete until that sequence using RMAN.

4) The archivelog must have been applied to the standby. Run the following query to list all archivelogs applied to the standby:

 SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;  

 Now,

 RMAN>delete archivelog until sequence <above value>;  

5) If on the primary site, the archivelog must be obsolete per RMAN retention policy

To list the obsolete objects, run the following query:

 RMAN> SHOW RETENTION POLICY;  
 RMAN>REPORT OBSELETE;  

RMAN> SHOW RETENTION POLICY;

RMAN>REPORT OBSELETE;

6) On Primary

 RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;  
 On Standby ( Depends upon where backup is preformed )  
 RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; 

 Or 

 RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; ( if Standby Where Backups Are Not Performed )  

REFERENCES

NOTE:740322.1 - RMAN Archived Redo Logs Are Deleted Before Being Applied at Standby Database

BUG:6216036 - RMAN+DG ARCHIVELOG DELETION POLICY APPLIED ON STANDBY NOT RESPECTED