Clean Prime Collaboration Assurance (PCA) Database

Introduction

This document describes how to a perform a Database (DB) Vacuum to clean up stale or over-abundant DB entries in the Prime Collaboration Assurance (PCA) Application.

Contributed by Joseph Koglin, Cisco TAC Engineer. 

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics and perform these recommendations:

• Basic Knowledge of PCA
• When you perform the actions specified in this document you will require a maintenance window if the PCA is in production.
• You will require full root access - instructions can be found at the bottom of this document under Root Access if you do not have it configured

Components Used

The information in this document is based on these software versions and applications:

PCA and PCA Postrgres DB

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, ensure that you understand the potential impact of any command.

Symptom(s)

A DB Vacuum or DB Cleanup can be utilized

• When disk space repeatedly is increased from the /opt directory 
• Server has been online for an extended period of time, ex. 6 months or more and can be utilized for routine maintenance.
• Backups continuously fail

Think of this as a re-organization of the Database, the benefits can be

• System performance is faster
• Data that may have intermittently shown is now consistently displayed.
• Disk Space can descrease drastically to free up more space for larger db growth
• Backups finish faster and have a higher success ratio

Note: Before you perform the DB Vacuum, please note the DB Vacuum Process can take anywhere from 2 hours - 12 hours typically. As the DB Vacuum is dependant on the DB Size, the process time may vary

Prior Steps before you start the DB Vacuum

Prior to this please make a snapshot of the VM via Vsphere or take a PCA Backup, this is a precaution, just to be safe.

Although there have not been any reported incidents of issues with the DB Vacuum in PCA it is best to be safe.

There are two options 

Option 1

Take a VM Snapshot:

Step 1. Right click on the VM in Vsphere

Step 2. Select Snapshot>>Take Snapshot. View at the bottom of Vsphere to monitor completion 

Or

Option 2

Take a PCA Backup:

1. Navigate to System Administration>>Backup Settings>> Select New and fill in the needed information based on if you want just the assurance data or assurance and analytics.

After you have created a VM Snapshot or a successful backup please proceed forward.

To ensure the PCA DB Port is open

Please perform the next steps to ensure the ports needed to run the db vacuum are open to connect

Step 1. Log in to PCA via SSH as root user and port 26

Step 2. Input. /sbin/iptables -A INPUT -p tcp --dport 5433 -j ACCEPT
Step 3. Input.  /etc/init.d/iptables save

DB Vacuum Process

Step 1. Log in to PCA as root user via VM Console, if you use putty or securecrt for the SSH session it can timeout and is more reliable to run these commands in the VM Console

Step 2. Stop all processes and services.  /opt/emms/emsam//bin/cpcmcontrol.sh stop (this process can take 10-15 minutes to fully stop all services)

Step 3. Run to ensure all services are stopped.  /opt/emms/emsam/bin/cpcmcontrol.sh status

Step 4. Start the DB service only. /opt/emms/emsam/bin/start_db.sh

Step 5.You will change the directory to navigate to postgres to execute the remaining commands cd /opt/postgres/9.2/  

Note: The postgres version is subject to change per major version. You can execute cd /opt/postgres/ followed by ls -l to find the version installed if the command with version 9.2 does not work.

Step 6. Input. su postgres

Step 7. Input. vacuumlo -U cmuser -p 5433 -v cpcm

Note: Steps 7 -10 will take a few hours and each command will complete on it's own. Once it is complete, proceed to the next step.

Step 8.Input. vacuumlo -U cmuser -p 5433 -v qovr

Step 9. Input. /opt/postgres/9.2/bin/psql -p 5433 --username=cmuser  cpcm -c "VACUUM FULL ANALYZE;"

Step 10. Input. /opt/postgres/9.2/bin/psql -p 5433 --username=qovr  qovr -c "VACUUM FULL ANALYZE;"

Step 11.Input: su root and re-enter in the root password

Step 12. Stop DB - /opt/emms/emsam/bin/shutdown_db.sh

Step 13. Start all Processes - /opt/emms/emsam//bin/cpcmcontrol.sh start

  Please allow approximately 15 minutes for all services to come back up and then re-login to the gui.

  You now have completed the DB Vacuum Process

Root Access

This section describes how to obtain full Root Access for PCA

Step 1. Log in through SSH to PCA and use port 26 as the Admin User

Step 2. Input.root_enable

Type in the root password you want

Step 3. Input.rootand type in the root password

Step 4. Once logged in as root Input./opt/emms/emsam/bin/enableRoot.sh

Step 5. Input.passwdand re-enter in your root password

You now should be able to close the SSH session and re-log in directly as root