am using a CentOS Linux v6.5 on my home server and I am getting the following error when I execute yum command:

rpmdb: PANIC: fatal region error detected; run recovery
error: db3 error(-30974) from dbenv->open: DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db3 - (-30974)
error: cannot open Packages database in /var/lib/rpm

Error: rpmdb open failed

How do I fix this problem on a CentOS / RHEL based system?

This is quite the messy situation. You may fix this by cleaning out rpm database. To minimize risk, make a backup of files in /var/lib/rpm/ using cp command:

mkdir /root/backups.rpm.mm_dd_yyyy/
cp -avr /var/lib/rpm/ /root/backups.rpm.mm_dd_yyyy/
To find list of __db* file, enter:
# ls -l /var/lib/rpm/_*

Sample outputs:

-rw-r--r-- 1 root root 24576 Jan 28 04:00 /var/lib/rpm/__db.001
-rw-r--r-- 1 root root 229376 Jan 28 04:00 /var/lib/rpm/__db.002
-rw-r--r-- 1 root root 1318912 Jan 28 04:00 /var/lib/rpm/__db.003
-rw-r--r-- 1 root root 753664 Jan 28 04:00 /var/lib/rpm/__db.004

To fix this problem, try:
# rm -f /var/lib/rpm/__db*
# db_verify /var/lib/rpm/Packages
# rpm --rebuilddb
# yum clean all

Verify that error has gone with the following yum command
# yum update