db_recovery_file_dest_size of N bytes is X% used and has R remaining bytes available

The boat launch can be a tough place, especially if you are a beginner on a busy lake.  People are anxious to get onto and off of the lake.  You don’t want people to be sitting there waiting on you, so the temptation is to go as fast as you can.  This can be a huge mistake for many though.  Even experienced boaters can make rookie mistakes that are downright embarrassing in front of all the other boaters.  Personally, I’ve made two mistakes that were somewhat embarrassing.  My first wasn’t a big deal – I forgot to put in the boat plug.  I’d guess most boaters have made this mistake at one time or another.  Water started gushing in the boat.  Once I saw it I quickly jammed in the plug and started the bilge pump, which drained out the water in about five minutes.  No real harm done.  My other big mistake was while I was pulling off a river.  The launch was gravel and had no dock.  I walked out on the tongue of the trailer, but while I was adjusting the boat I lost my balance.  Unfortunately my shoe got stuck on a bracket so instead of stepping down and getting my leg wet up to my knee, I fell back first and ended up totally submerged in 1 1/2 feet of water.  I’m the kind of guy who is able to laugh at my mistakes, so even though I ended up soaking wet and embarrassed, I still had a good laugh at my own expense.

A DBA can certainly see the lesson in this.  If you go too fast you make mistakes.  You need to be very careful and pay close attention to details.  One way to cover yourself is to maintain backups.  I recently ran into a warning in Oracle Enterprise Manager that said I was using 97% of the recovery area free space.  A quick search found that if the used space got up to 100%, the database would cease to process transactions.

20161122-recovery-file-size-oem

I wanted to see within the database what my db_recovery_file_dest_size parameter was set to.  I ran a quick query and found that it was set to 350 GB.

20161122-recovery-file-size-parameter

Next I queried the v$recovery_file_dest to see the total space, used space, and directory where I was storing the backupsets.

20161122-recovery-file-size-recovery-dest

I checked the directory and found it quite full of back files.

20161122-recovery-file-size-backups

My next step was to attempt to delete any expired archivelogs.  Unfortunately, this didn’t do anything:

20161122-recovery-file-size-delete-expired-results

I needed to first backup the archivelogs before I could delete any of them.  So I ran the following command in RMAN:

20161122-recovery-file-size-backup-archivelog-all-delete-input

This ran a VERY long time, but eventually succeeded.  This fixed my problem.  After running this command I checked my free space and found I’d gained plenty:

20161122-recovery-file-size-fixed-size

Taking backups is not enough – you need to be aware of what is happening to the backups.  Testing recover-ability is also a great idea!

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