Re: FRA size continuously filling up

From: Saad Khan <saad4u_at_gmail.com>
Date: Mon, 22 Sep 2014 16:45:06 -0400
Message-ID: <CACqGOPLTJeQevsy4PfFNzJtq2AHYU4tMgFghR1vqat7SX4y6Ng_at_mail.gmail.com>

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

Received on Mon Sep 22 2014 - 22:45:06 CEST