Oracle DBA – A lifelong learning experience

Posts Tagged ‘db_recovery_file_dest_size’

Problem with V$RECOVERY_AREA_USAGE view and FRA space not being reclaimed

Posted by John Hallas on March 16, 2017

We received the following alert from our alerting system
 
Flash Recovery Area for FOLPRD1A has 9.97 percent of usable space left.
 
This is a standby database:
 
HOST       INSTANCE   STATUS     VERSION      STARTED                   UPTIME
———- ———- ———- ———— ————————- ————————————————–
xxxxxxxx   INSTANCE   MOUNTED    11.2.0.3.0   18-JAN-2017 18:58:18      33 days(s) 13 hour(s) 4 minute(s) 1 seconds
 
DB_NAME     UNIQUE_NAME DB_ROLE            OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL
———– ———– —————— ———- ——————– ——————–
xxxxxxxx    INSTANCE    PHYSICAL STANDBY   MOUNTED    MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
 
Usually, when under space pressure the standby will delete archivelogs and flashback logs that it no longer needs so this alert isn’t normal for a standby.  However, in this scenario, none of the space is reclaimable.  Therefore, without intervention the FRA would eventually hit 100% and stop logs being transported to the standby.
 
NAME                                     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
—————————————- ———– ———- —————– —————
+FRA                                             400     379.48                 0           11268
 
FILE_TYPE               USED_GB RECLAIMABLE_GB PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
——————– ———- ————– —————— ————————- —————
CONTROL FILE                .12              0                .03                         0               1
REDO LOG                      0              0                  0                         0               0
ARCHIVED LOG             326.12              0              81.53                         0           11068
BACKUP PIECE                .12              0                .03                         0               1
IMAGE COPY                    0              0                  0                         0               0
FLASHBACK LOG             53.08              0              13.27                         0             197
FOREIGN ARCHIVED LOG          0              0                  0                         0               0
 
I checked the RMAN configuration suspecting it hadn’t been changed post switchover:
 
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
 
Rather than being “NONE” it did look right but I did notice it was ‘ALL’.  I thought we usually had it as follows:
 
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
 
I assumed this wouldn’t make a difference as we only have one standby and all the logs had been applied.  However, I changed it anyway.  Straight away this had the desired effect and practically all the space became reclaimable.
 
NAME                                     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
—————————————- ———– ———- —————– —————
+FRA                                             400     379.48            325.92           11268
 
FILE_TYPE               USED_GB RECLAIMABLE_GB PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
——————– ———- ————– —————— ————————- —————
CONTROL FILE                .12              0                .03                         0               1
REDO LOG                      0              0                  0                         0               0
ARCHIVED LOG             326.12         325.88              81.53                     81.47           11068
BACKUP PIECE                .12              0                .03                         0               1
IMAGE COPY                    0              0                  0                         0               0
FLASHBACK LOG             53.08              0              13.27                         0             197
FOREIGN ARCHIVED LOG          0              0                  0                         0               0
 
The alert subsequently cleared.
 
Looking at the report “Archivelog Deletion Policy – Core Production Databases” we have many databases configured with the ALL parameter.  I checked another at random and it was fine.  I suspected maybe it was the action of changing the parameter rather than the parameter being wrong and thought maybe dbms_backup_restore.refreshagedfiles would have done the job.
 
After speaking to a colleague he said this alert came out weeks ago and the trick of lowering the db_recovery_file_dest_size to force the database to be under space pressure had cleared the old logs and the alert.  Therefore, the fact that this worked suggests that the space was always reclaimable, just not shown in view which the alert uses.  I found a nice blog that shows the same issue and alludes to a bug.
 
 
“V$RECOVERY_AREA_USAGE is an aggregate view. If we check its definition, we see that the reclaimable size comes from x$kccagf.rectype.”  It directs you to a bug (for this version) that describes that the standby “does not refresh reclaimable space automatically”.
 
Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA
 
The workaround is to run exec dbms_backup_restore.refreshagedfiles; 
 
The blog also claims…..”but I’ve found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag – even when there is no change.”
 
This is effectively what I did.  Therefore, I’ve put the original parameter back and switched logs on the primary numerous times and the reclaimable space is being updated.  For now we shall keep an eye on this as it’s not an issue anywhere else.

Posted in Oracle | Tagged: , , , , | Leave a Comment »

ORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver error

Posted by John Hallas on July 16, 2008

I have long been plagued by the parameter db_recovery_file_dest_size causing a problem when the size limit is exceeded.
As I have been working on development systems my normal work around was to free up some space in the db_recovery_file_dest and hope that
that the database would continue after the archiver logger error.
Invariably I needed to restart the database and hope that the problem resolves itself.
Today I decided to take some action and work out how to fix the problem.

I logged onto the ASM disk using asmcmd -p (the -p shows the current working directory) and moved to the archive log directory
ASCMD > cd FRA/SID/ARCHIVELOG/2008-07-16
ASCMD > rm thr* (I did not need to keep the archive logs as we had no need to backup/restore the database as it could be rebuilt if necessary)
ASCMD > YES (to confirm the prompt)

I then waited for the database to continue from the archiver error but this did not happen

The alert log typically shows an entry as follows :-

ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************
Errors in file /u00/app/oracle/diag/rdbms/sid/SID/trace/SID_ora_20214.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1063256064 bytes disk space from 42949672960 limit
ARCH: Error 19809 Creating archive log file to ‘+FRA’

I am sure that this message is either new in 11g or has been improved because I have never noticed the 4th option before.

I had previously configured RMAN on the database and I realised that I needed to ensure that the RMAN catalog was aware that the files had been removed manually and space was now available.

RMAN > CHANGE ARCHIVELOG ALL VALIDATE;
RMAN > DELETE EXPIRED ARCHIVELOG ALL;

Metalink Document https://metalink.oracle.com/metalink/plsql/f?p=200:27:9917209390401703684::::p27_id,p27_show_header,p27_show_help:621248.995,1,1 has an unpublished note on the subject

Cause
~~~~~~~

We register all the information about what we place in the flash recovery area in
the rman repository/controlfile. If we determine that there is not sufficient space
in the recovery file destination, as set by dest_size then we will fail.

Just deleting the old backups/archive logs from disk is not sufficient as it’s the rman repository/controlfile
that holds the space used information.

Fix
~~~~

There are a couple of possible options.

1) Increase the parameter db_recovery_file_dest_size
2) Stop using the db_recovery_file_dest by unsetting the parameter.
( This assumes you never really wanted to use this option )
3) Remove the Entries from the rman repository/Controlfile

The removal is desribed in the RMAN documentation but this is a quick and
dirty way if you don’t have an rman repository – but could endanger your ability
to recover – so be careful.

a) delete unwanted archive log files from disk ( rm /del )
b) connect to rman
c) rman crosscheck archivelog all – marks the controlfile that the archives have been deleted
d) rman delete expired archivelog all – deletes the log entries identified above.

Posted in Oracle | Tagged: , , , , | 8 Comments »