This discussion is archived
8 Replies Latest reply: Nov 9, 2012 3:24 AM by Patrik Haaparanta RSS

ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0

741795 Newbie
Currently Being Moderated
Hello folks;

I got an error with my FRA in my stand by database :
Trace file /u01/app/oracle/diag/rdbms/dxprod/DXPROD1/trace/DXPROD1_rfs_23032.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      chuipbl1
Release:        2.6.18-308.el5
Version:        #1 SMP Fri Jan 27 17:17:51 EST 2012
Machine:        x86_64
Instance name: DXPROD1
Redo thread mounted by this instance: 1
Oracle process number: 35
Unix process pid: 23032, image: oracle@chuipbl1


*** 2012-11-06 14:45:30.332
*** SESSION ID:(430.9543) 2012-11-06 14:45:30.332
*** CLIENT ID:() 2012-11-06 14:45:30.332
*** SERVICE NAME:() 2012-11-06 14:45:30.332
*** MODULE NAME:(oracle@chuipbl4.chu-montpellier.priv (TNS V1-V3)) 2012-11-06 14:45:30.332
*** ACTION NAME:() 2012-11-06 14:45:30.332

ORA-19815: WARNING: db_recovery_file_dest_size of 214748364800 bytes is 100.00% used, and has 0 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.
************************************************************************
*** 2012-11-06 14:45:30.333 4320 krsh.c
Creating archive destination file : +FRA (3692 blocks)
Archivelog creation failed; error 19809
Check for bad filename or duplicated log_archive_format parameter
Trace file /u01/app/oracle/diag/rdbms/dxprod/DXPROD1/trace/DXPROD1_rfs_23032.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      chuipbl1
Release:        2.6.18-308.el5
Version:        #1 SMP Fri Jan 27 17:17:51 EST 2012
Machine:        x86_64
Instance name: DXPROD1
Redo thread mounted by this instance: 1
Oracle process number: 45
Unix process pid: 23032, image: oracle@chuipbl1
SQL> 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;
/ SQL> SQL>   2    3    4    5    6

NAME                                                            Size MB    Used MB
------------------------------------------------------------ ---------- ----------
+FRA                                                             204800     204800

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 200G
My ASM configuration :
oracle@chuipbl1 ~]$ ps -ef | grep ASM
oracle    3329  3219  0 10:14 pts/5    00:00:00 grep ASM
oracle   27461     1  0 09:41 ?        00:00:00 oracle+ASM1_o000_dxprod1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   27463     1  0 09:41 ?        00:00:00 oracle+ASM1_o001_dxprod1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   28963     1  0 Oct26 ?        00:00:00 asm_pmon_+ASM1
oracle   28965     1  0 Oct26 ?        00:00:00 asm_psp0_+ASM1
oracle   28971     1  0 Oct26 ?        00:00:10 asm_vktm_+ASM1
oracle   28975     1  0 Oct26 ?        00:00:00 asm_gen0_+ASM1
oracle   28977     1  0 Oct26 ?        00:00:02 asm_diag_+ASM1
oracle   28979     1  0 Oct26 ?        00:00:12 asm_ping_+ASM1
oracle   28981     1  0 Oct26 ?        00:21:12 asm_dia0_+ASM1
oracle   28983     1  0 Oct26 ?        00:00:16 asm_lmon_+ASM1
oracle   28985     1  0 Oct26 ?        00:01:48 asm_lmd0_+ASM1
oracle   28987     1  0 Oct26 ?        00:00:49 asm_lms0_+ASM1
oracle   28991     1  0 Oct26 ?        00:00:00 asm_lmhb_+ASM1
oracle   28993     1  0 Oct26 ?        00:00:00 asm_mman_+ASM1
oracle   28995     1  0 Oct26 ?        00:00:00 asm_dbw0_+ASM1
oracle   28997     1  0 Oct26 ?        00:00:00 asm_lgwr_+ASM1
oracle   28999     1  0 Oct26 ?        00:00:03 asm_ckpt_+ASM1
oracle   29001     1  0 Oct26 ?        00:00:00 asm_smon_+ASM1
oracle   29003     1  0 Oct26 ?        00:00:09 asm_rbal_+ASM1
oracle   29005     1  0 Oct26 ?        00:00:00 asm_gmon_+ASM1
oracle   29007     1  0 Oct26 ?        00:00:00 asm_mmon_+ASM1
oracle   29009     1  0 Oct26 ?        00:00:00 asm_mmnl_+ASM1
oracle   29011     1  0 Oct26 ?        00:00:00 asm_lck0_+ASM1
oracle   29015     1  0 Oct26 ?        00:00:00 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29032     1  0 Oct26 ?        00:00:00 oracle+ASM1_ocr (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29034     1  0 Oct26 ?        00:00:01 asm_asmb_+ASM1
oracle   29036     1  0 Oct26 ?        00:00:01 oracle+ASM1_asmb_+asm1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29229     1  0 Oct26 ?        00:00:00 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29231     1  0 Oct26 ?        00:00:00 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29235     1  0 Oct26 ?        00:00:00 oracle+ASM1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29408     1  0 Oct26 ?        00:00:01 oracle+ASM1_asmb_dxprod1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@chuipbl1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@chuipbl1 ~]$
[oracle@chuipbl1 ~]$ asmcmd
ASMCMD> ls -s
Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
   512   4096  1048576   1126400   332598                0          332598              0             N  DATA/
   512   4096  1048576    307200   101558                0          101558              0             N  FRA/
   512   4096  1048576      5120     4724                0            4724              0             Y  OCR/
ASMCMD>
ASMCMD> lsdsk -p
Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path
        2         0  2762184533  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_01
        2         1  2762184532  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_02
        2         2  2762184531  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_03
        2         3  2762184530  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_04
        2         4  2762184529  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_05
        2         5  2762184528  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_06
        2         6  2762184534  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_07
        2         7  2762184527  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_08
        2         8  2762184526  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_09
        2         9  2762184525  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_10
        2        10  2762184524  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/DATA_11
        3         0  2762184537  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/FRA_01
        3         1  2762184536  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/FRA_02
        3         2  2762184535  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/FRA_03
        1         0  2762184523  CACHED      MEMBER       ONLINE     NORMAL  /dev/oracleasm/disks/OCR_01
I try to purge old backup and old archive logs with RMAN :
$rman target / 
RMAN> crosscheck archivelog all;
...
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44001.5985.798596285 RECID=65467 STAMP=798596284
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44002.5987.798596345 RECID=65469 STAMP=798596345
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44003.5988.798596407 RECID=65470 STAMP=798596406
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44004.5989.798596463 RECID=65471 STAMP=798596463
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44005.5990.798596529 RECID=65472 STAMP=798596529
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44006.5991.798596585 RECID=65473 STAMP=798596585
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44007.5992.798596645 RECID=65474 STAMP=798596644
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44008.5993.798596707 RECID=65475 STAMP=798596706
validation succeeded for archived log
archived log file name=+FRA/dxprod/archivelog/2012_11_06/thread_2_seq_44009.5994.798596767 RECID=65476 STAMP=798596766
Crosschecked 5724 objects

RMAN > delete expired archivelog all;


released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1849 instance=DXPROD2 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1993 instance=DXPROD2 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2132 instance=DXPROD2 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=3 instance=DXPROD2 device type=DISK
specification does not match any archived log in the repository
Nothing... :/ :
SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
    number_of_files as "number" from v$flash_recovery_area_usage;

FILE_TYPE                  USED RECLAIMABLE     number
-------------------- ---------- ----------- ----------
CONTROL FILE                .03           0          1
REDO LOG                      0           0          0
ARCHIVED LOG              99.97           0       5724
BACKUP PIECE                  0           0          0
IMAGE COPY                    0           0          0
FLASHBACK LOG                 0           0          0
FOREIGN ARCHIVED LOG          0           0          0

7 rows selected.

SQL> select name, space_limit as Total_size ,space_used as Used,
    SPACE_RECLAIMABLE as reclaimable ,NUMBER_OF_FILES as "number"
    from  V$RECOVERY_FILE_DEST;   2    3

NAME
--------------------------------------------------------------------------------
TOTAL_SIZE       USED RECLAIMABLE     number
---------- ---------- ----------- ----------
+FRA
2.1475E+11 2.1475E+11           0       5725
I come back to RMAN :
RMAN> CROSSCHECK BACKUP;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1563 instance=DXPROD1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1706 instance=DXPROD1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1849 instance=DXPROD1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1993 instance=DXPROD1 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxprod/autobackup/2012_05_11/s_782997626.3019.782997627 RECID=248 STAMP=782997626
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxprod/autobackup/2012_05_11/s_782999596.3005.782999597 RECID=249 STAMP=782999597
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxprod/autobackup/2012_05_11/s_783001999.2972.783002001 RECID=251 STAMP=783002000
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxprod/autobackup/2012_05_15/s_783360785.4554.783360797 RECID=355 STAMP=783360797
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_05_24/s_784100118.4842.784100119 RECID=383 STAMP=784100119
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_06/s_785258965.4564.785258967 RECID=598 STAMP=785258967
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_13/s_785858576.3942.785858581 RECID=705 STAMP=785858580
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_22/s_786641415.5258.786641419 RECID=854 STAMP=786641418
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_25/s_786902111.4707.786902113 RECID=908 STAMP=786902113
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_25/s_786904814.4962.786904817 RECID=909 STAMP=786904816
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+FRA/dxsbyr/autobackup/2012_07_05/s_787833851.2969.787833855 RECID=1119 STAMP=787833855
Crosschecked 11 objects

RMAN> Delete expired backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
248     248     1   1   EXPIRED     DISK        +FRA/dxprod/autobackup/2012_05_11/s_782997626.3019.782997627
249     249     1   1   EXPIRED     DISK        +FRA/dxprod/autobackup/2012_05_11/s_782999596.3005.782999597
251     251     1   1   EXPIRED     DISK        +FRA/dxprod/autobackup/2012_05_11/s_783001999.2972.783002001
355     355     1   1   EXPIRED     DISK        +FRA/dxprod/autobackup/2012_05_15/s_783360785.4554.783360797
383     383     1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_05_24/s_784100118.4842.784100119
598     598     1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_06_06/s_785258965.4564.785258967
705     705     1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_06_13/s_785858576.3942.785858581
854     854     1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_06_22/s_786641415.5258.786641419
908     908     1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_06_25/s_786902111.4707.786902113
909     909     1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_06_25/s_786904814.4962.786904817
1119    1119    1   1   EXPIRED     DISK        +FRA/dxsbyr/autobackup/2012_07_05/s_787833851.2969.787833855

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=+FRA/dxprod/autobackup/2012_05_11/s_782997626.3019.782997627 RECID=248 STAMP=782997626
deleted backup piece
backup piece handle=+FRA/dxprod/autobackup/2012_05_11/s_782999596.3005.782999597 RECID=249 STAMP=782999597
deleted backup piece
backup piece handle=+FRA/dxprod/autobackup/2012_05_11/s_783001999.2972.783002001 RECID=251 STAMP=783002000
deleted backup piece
backup piece handle=+FRA/dxprod/autobackup/2012_05_15/s_783360785.4554.783360797 RECID=355 STAMP=783360797
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_05_24/s_784100118.4842.784100119 RECID=383 STAMP=784100119
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_06/s_785258965.4564.785258967 RECID=598 STAMP=785258967
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_13/s_785858576.3942.785858581 RECID=705 STAMP=785858580
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_22/s_786641415.5258.786641419 RECID=854 STAMP=786641418
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_25/s_786902111.4707.786902113 RECID=908 STAMP=786902113
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_06_25/s_786904814.4962.786904817 RECID=909 STAMP=786904816
deleted backup piece
backup piece handle=+FRA/dxsbyr/autobackup/2012_07_05/s_787833851.2969.787833855 RECID=1119 STAMP=787833855
Deleted 11 EXPIRED objects


RMAN> Delete expired archivelog all;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1563 instance=DXPROD1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1706 instance=DXPROD1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=1849 instance=DXPROD1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1993 instance=DXPROD1 device type=DISK
specification does not match any archived log in the repository

RMAN> Delete force obsolete;

Backup Set           1204   06-JUL-12
  Backup Piece       1204   06-JUL-12          65nfdquf_1_1
Backup Set           1205   06-JUL-12
  Backup Piece       1205   06-JUL-12          66nfdqvj_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 11/08/2012 11:15:05
RMAN-06091: no channel allocated for maintenance (of an appropriate type)
How can I fix it ???

HELP ! Thanls for your knowledge.

PS : I'm a noob in RMAN and dataguard and don't have Oracle Datagrid Coutrol or Oracle Enterprise Manager

Edited by: Moostiq on 8 nov. 2012 10:53
  • 1. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    Shivananda Rao Guru
    Currently Being Moderated
    On your standby database, connect it through RMAN:
    rman target sys/<pwd>@<standby-db>
    RMAN>delete archivelog all completed before 'SYSDATE-7';
    This frees up the archives that are located on the standby database and deletes archives on the standby database that are beyond 7 days. You can set the number of days depending on your requirement.
  • 2. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    mseberg Guru
    Currently Being Moderated
    Hello;

    You can run a script daily :
    #!/bin/bash
    #.................................................
    # Name: db_rman_arch_standby.sh 
    # Purpose: Database rman backup
    # Usage : db_rman_arch_standby <DBNAME>
    #..................................................
    # 
    
    if [ "$1" ]
    then DBNAME=$1
    else
    echo "basename $0 : Syntax error : use . db_rman_full <DBNAME> "
    exit 1
    fi
    
    # set ENV by database ( small file for each )
    
    . /u01/app/oracle/dba_tool/env/${DBNAME}.env
    
    
    echo ${DBNAME}
    
    MAILHEADER="Archive_cleanup_on_STANDBY_${DBNAME}"
    
    echo "Starting RMAN..."
    $ORACLE_HOME/bin/rman target / catalog rmancat/pasword/db << EOF
    
    
    delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-8';
    
    
    exit
    EOF
    
    
    echo `date`
    #
    echo
    echo 'End of archive cleanup on STANDBY'
    
    mailx -s ${MAILHEADER} $MAILTO < /tmp/rmandbarchstandby.out
    Best Regards

    mseberg
  • 3. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    Patrik Haaparanta Newbie
    Currently Being Moderated
    Hi,

    None of your archived redo logs in the Flash Recovery Area are considered reclaimable (eligible for deletion), so they will not be deleted. This is shown below.
    Moostiq wrote:
    SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
    number_of_files as "number" from v$flash_recovery_area_usage;
    
    FILE_TYPE                  USED RECLAIMABLE     number
    -------------------- ---------- ----------- ----------
    CONTROL FILE                .03           0          1
    REDO LOG                      0           0          0
    ARCHIVED LOG              99.97           0       5724
    BACKUP PIECE                  0           0          0
    IMAGE COPY                    0           0          0
    FLASHBACK LOG                 0           0          0
    FOREIGN ARCHIVED LOG          0           0          0
    
    7 rows selected.
    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.

    Hope that helps
    Patrik
  • 4. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    741795 Newbie
    Currently Being Moderated
    Hello Patrik ! Thank you for your answer.

    The RMAN configuration and how you said it's set to NONE :
    [oracle@chuipbl1 ~]$ rman target /
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Fri Nov 9 10:14:39 2012
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: DXPROD (DBID=1959419363, not open)
    
    RMAN>  show archivelog deletion policy;
    
    using target database control file instead of recovery catalog
    RMAN configuration parameters for database with db_unique_name DXPROD are:
    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
    But there is no RMAN backups of the archive logs on the STDBY database !!
    Therefore if I configure the policy to "APPLIED ON STANDBY", then the standby database will automatically make free space on the FRA ??

    Regards
  • 5. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    Patrik Haaparanta Newbie
    Currently Being Moderated
    Moostiq wrote:
    But there is no RMAN backups of the archive logs on the STDBY database !!
    Therefore if I configure the policy to "APPLIED ON STANDBY", then the standby database will automatically make free space on the FRA ??
    That's right. If you set the archivelog deletion policy to "APPLIED ON STANDBY", archivelogs will be automatically deleted when necessary to free upp space in the FRA - as long as they have been applied on the standby.
    You could set this in the standby database, if your backup strategy is to take backups of the primary database and not of the standby.

    If you decide to change the backup strategy, to offload backups from primary database and perform them on standby instead, you can do the opposite. The basic principle is still the same; set it to NONE where you take your backups and APPLIED ON STANDBY where you don't want to take any backups.

    /Patrik
  • 6. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    741795 Newbie
    Currently Being Moderated
    OK.
    But my DBA say that's have to be set on primary ("APPLIED ON STANDY") and that's don't work on the standby.
    So we increase the FRA and we put a purging script on oracle's crontab on the standby and it's working very well !

    Thanks to you for your support Patrik, have a nice day !

    Regards
  • 7. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    mseberg Guru
    Currently Being Moderated
    Hello;

    The deletion policy has to do with where the RMAN backup is done. Options :

    CONFIGURE ARCHIVE DELETION POLICY TO SHIPPED TO ALL STANDBY;

    OR


    CONFIGURE ARCHIVE DELETION POLICY TO APPLIED ON ALL STANDBY;

    In your case I would set this to this on the Standby :

    CONFIGURE ARCHIVE DELETION POLICY TO NONE;

    See - 11.3.1 Oracle Database Configurations on Primary and Standby Databases

    In - Data Guard Concepts and Administration 11g Release 2 (11.2) E10700-02

    http://oracle.su/docs/11g/server.112/e10700/rman.htm#BAJBBBDI

    Best Regards

    mseberg
  • 8. Re: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.0
    Patrik Haaparanta Newbie
    Currently Being Moderated
    Moostiq wrote:
    But my DBA say that's have to be set on primary ("APPLIED ON STANDY") and that's don't work on the standby.
    So we increase the FRA and we put a purging script on oracle's crontab on the standby and it's working very well !
    You can set it anywhere you want.
    In my opinion you should take advantage of the features of the flash recovery area, since you are in fact using it, and configure things correctly. But it's your system, and as long as it's working for you...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points