Incorrect LOG_ARCHIVE_DEST_n Setting Invalidates FRA Quotas


Today, I found out why the limit set by DB_RECOVERY_FILE_DEST_SIZE was not working.

The problem was that developers would have an errant PL/SQL procedure that did billions of DML operations and filled all of the disk group used for the Fast Recovery Area (FRA). In our environment, we have several hundred development databases sharing the same disk group. So, one developer can cause havoc for many other developers by causing other databases to have archiver errors (ORA-00257).

The following parameters were set for that development database:

DB_RECOVERY_FILE_DEST=+FRA
DB_RECOVERY_FILE_DEST_SIZE=800G

The DB_RECOVERY_FILE_DEST parameter was correctly pointing to the disk group used for FRA. And the DB_RECOVERY_FILE_DEST_SIZE parameter had a non-zero value.

However when I queried the V$RECOVERY_AREA_USAGE view, I got a response similar to the following:

FILE_TYPE	     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE			      0 			0		0
REDO LOG			      0 			0		0
ARCHIVED LOG			      0 			0		0
BACKUP PIECE			   7.69 			0		5
IMAGE COPY			      0 			0		0
FLASHBACK LOG			      6 			0		3
FOREIGN ARCHIVED LOG		      0 			0		0

Notice that there are zero (0) archived log files in the FRA!

Another DBA in my team suggested that I check the value of the
LOG_ARCHIVE_DEST_n parameters. The settings were:

LOG_ARCHIVE_DEST_1='LOCATION=+FRA'

As was pointed out to me, this should be:

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

So, I rectified this through the following command:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' COMMENT='2015/06/01 sending archlog to FRA';

To check the validity of this, I forced a log switch as follows:

ALTER SYSTEM SWITCH LOGFILE;

Once the archiver had finished, I checked the V$RECOVERY_AREA_USAGE view again:

FILE_TYPE	     PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE			      0 			0		0
REDO LOG			      0 			0		0
ARCHIVED LOG			   1.84 			0		1
BACKUP PIECE			   7.69 			0		5
IMAGE COPY			      0 			0		0
FLASHBACK LOG			      6 			0		3
FOREIGN ARCHIVED LOG		      0 			0		0

Now, there is a single archived log file in the FRA according to the view even though there are many others there according to ASM! I need to work out a way of registering these files to this view.

In MOS note, Space issue in Fast / Flash Recovery Area – FRA Full (Doc ID 829755.1), there is the following comment right at the bottom:

– For Archivelogs backup to FRA use USE_DB_RECOVERY_FILE_DEST rather than giving explict path of FRA

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s