Re: FRA size continuously filling up

From: Seth Miller <sethmiller.sm_at_gmail.com>
Date: Mon, 22 Sep 2014 15:35:13 -0500
Message-ID: <CAEueRAXY5hELWnXaN2uRgQTdJGc_8HT=gGAXzZ6Eoh9femxuTw_at_mail.gmail.com>

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

Received on Mon Sep 22 2014 - 22:35:13 CEST