Re: FRA size continuously filling up

Checkout the following article to determine which are the top processes in redo generation:

http://blog.tanelpoder.com/2008/05/30/oracle-troubleshooting-with-snapper-detecting-whos-causing-excessive-redo-generation/

kind regards,

Freek

On ma, 2014-09-22 at 16:45 -0400, Saad Khan wrote:
> Here is the output as I see it for these queries:
>
>
>
> SQL> select file_type, percent_space_used||'%' per_used,
> percent_space_reclaimab
> le||'%' per_recl, number_of_files num_file from v
> $flash_recovery_area_usage;
>
>
> FILE_TYPE PER_USED PER_RECL NUM_FILE
> ------------------------------ -------- -------- --------
> CONTROL FILE 0% 0% 0
> REDO LOG 0% 0% 0
> ARCHIVED LOG 95.13% 62.9% 280
> 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> # Flash Recovery Area Space
> SP2-0734: unknown command beginning "Flash Reco..." - rest of line
> ignored.
> SQL> set linesize 110 pagesize 80
> SQL> col name for a50
> SQL> col space_limit_GB for 999999.9
> SQL> col space_used_GB for 999999.9
> SQL> col space_recl_GB for 999999.9
> SQL> col num_file for 99999
> SQL> select name, space_limit/1024/1024/1024 space_limit_GB,
> space_used/1024/102
> 4/1024 space_used_GB, space_reclaimable/1024/1024/1024 space_recl_GB,
> number_of_
> files num_file from v$recovery_file_dest;
>
>
> NAME SPACE_LIMIT_GB
> SPACE_USED_GB
> SPACE_RECL_GB NUM_FILE
> -------------------------------------------------- --------------
> -------------
> ------------- --------
> E:\oracle\fast_recovery_area 13.0
> 12.3
> 8.1 279
>
>
>
>
> On Mon, Sep 22, 2014 at 4:35 PM, Seth Miller <sethmiller.sm_at_gmail.com>
> wrote:
>
> Saad,
>
>
>
> Run these two queries.
>
>
> # Flash Recovery Area Usage
> set linesize 110 pagesize 80
> col file_type for a30
> col per_used for a8
> col per_recl for a8
> col num_file for 99999
> select file_type, percent_space_used||'%' per_used,
> percent_space_reclaimable||'%' per_recl, number_of_files
> num_file from v$flash_recovery_area_usage;
> # Flash Recovery Area Space
> set linesize 110 pagesize 80
> col name for a50
> col space_limit_GB for 999999.9
> col space_used_GB for 999999.9
> col space_recl_GB for 999999.9
> col num_file for 99999
> select name, space_limit/1024/1024/1024 space_limit_GB,
> space_used/1024/1024/1024 space_used_GB,
> space_reclaimable/1024/1024/1024 space_recl_GB,
> number_of_files num_file from v$recovery_file_dest;
>
>
> Seth Miller
>
>
> On Mon, Sep 22, 2014 at 3:24 PM, Saad Khan <saad4u_at_gmail.com>
> wrote:
>
> This is Oracle 11.2.0.3 on windows platform.
>
>
>
> We started getting following message this morning:
>
>
>
>
> ORA-19815: WARNING: db_recovery_file_dest_size of
> 13958643712 bytes is 100.00% used, and has 0 remaining
> bytes available.
>
>
>
> For now i've tried following actions but its just not
> working:
>
>
> 1) Extending the value of db_recovery_file_dest_size
> as much as 4G to 13G --wutever the size i give it just
> fills within no time.
> 2) Taking the backup to tape, deleting old archivelogs
> 3) Crosscheck archivelogs, delete expired/obsolete
> ones
> 4) Most important, as per MOS doc ID 293418.1, I took
> the backup of FRA and it did help for some time but
> now its 100% filled again.
>
>
> Its barely 8GB size database.
>
>
> Do I need to bounce DB at this point?
>
>
> Below is my backup configuration:
>
>
> CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
> CONFIGURE BACKUP OPTIMIZATION OFF; # default
> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
> CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
> TYPE SBT_TAPE TO '%F'; # defa
> ult
> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE
> TYPE DISK TO '%F'; # default
> CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP
> TYPE TO BACKUPSET; # default
>
>
> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
> TO BACKUPSET; # default
> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE
> SBT_TAPE TO 1; # default
> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK
> TO 1; # default
> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
> SBT_TAPE TO 1; # default
> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE
> DISK TO 1; # default
> CONFIGURE MAXSETSIZE TO UNLIMITED; # default
> CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
> CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
> CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE
> 'DEFAULT' OPTIMIZE FOR LOA
> D TRUE ; # default
> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; #
> default
> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE
> \11.2.0\DATABASE\SNCFICD10DB.OR
> A'; # default
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l