8 Replies Latest reply: Nov 9, 2012 5:24 AM by Patrik Haaparanta RSS

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

    741795
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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...