Troubleshoot CVP Reporting Server Database Purge Failure

Introduction

This document describes steps to troubleshoot Customer Voice Portal (CVP) Reporting Server Database Purge Failure (both Emergency and Nightly Purge).

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

• CVP Server
• Cisco Unified Intelligent Contact Management (ICM)
• Cisco Unified Contact Center Enterprise (UCCE)

Components Used

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

• CVP Reporting Server 8.5 and higher
• CVP Operations Console (OAMP)

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.

Troubleshoot Most Common Scenarios

Password Mismatch

Ensure CVP_dbadmin and CVP_dbuser passwords are same. There is a defect for this CSCuw90124 .

This can be verified in the CVP logs of the reporting server.

Logsnippets:

During night purge time 12:00 midnight as per the configuration done on OAMPserver, Purge started.
10.XX.XX.XX: Sep 20 2017 00:00:02.986 +0400: %CVP_10_5_RPT-1-REPORTING_ALERT_MESSAGE:  Starting purge handler operation. [id:4024]

But failed due to password issue,
10.XX.XX.XX: Sep 20 2017 00:00:06.012 +0400: %CVP_10_5_RPT-3-REPORTING_DB_ERROR_MSG:   (Database: cvp_data) Failed running the stored procedure sp_got_space(): java.sql.SQLException: Incorrect password or user com.informix.asf.IfxASFRemoteException: cvp_dbadmin@XX.com is not known on the database server.: java.sql.SQLException: Incorrect password or user com.informix.asf.IfxASFRemoteException: cvp_dbadmin@XX.com is not known on the database server. (DBScript.testPurgeNeeded)
java.sql.SQLException: Incorrect password or user com.informix.asf.IfxASFRemoteException: cvp_dbadmin@XX.com is not known on the database server.

Password Mismatch Solution

Reset the password of CVP_dbadmin and CVP_dbuser:

Step 1. Open a Remote Desktop Session to the Reporting Server.

Step 2. Log in to the server with a user who has administrator privileges.

Step 3. Ensure that the CVP Reporting users Informix, cvp_dbadmin, cvp_dbuser exist on CVP Reporting Server.

Step 4. CVP Reporting users must be members of local administrators and Informix admin groups.

Step 5. Ensure the CVP Reporting user accounts are not locked out.

Step 6. Ensure the Reporting Server is stopped via CVP OAMP console before the Windows Password is changed. If the Reporting Server is not stopped, it attempts to connect with the bad username/password combination and lock the cvp_dbuser account.

Step 7. Ensure the Cisco CVP Resource Manager service is in Running status on the CVP Reporting Server.

Step 8. Navigate to Computer Management > Local Users and Groups > Users.

Step 9. Reset the password for informix, cvp_dbadmin, cvp_dbuser to a temporary password.

Step 10. Enter the OAMP console webpage and navigate to Device Management > Unified CVP Reporting Server. Select and edit CVP Reporting Server Configuration. Select Database Administration > Change User Passwords.

Step 11. Enter the temporary password from step 9 as the old password, and then enter your new permanent password.

Step 12. Ensure the accounts are not locked out through Windows Local Users and Groups.

Step 13. Restart the CVP Reporting Server service.

Purge Fail with Error: Failed running the stored procedure sp_frag_mgt: -310

This can be verified in the CVP logs of the reporting server.

Logsnippet:

10.XX.XX.XX 00:00:03.683 -0500: %CVP_8_5_RPT-3-REPORTING_DB_ERROR_MSG: (Database: cvp_data) runDBPurge -- Failed running the stored procedure sp_frag_mgt: -310
10.XX.XX.XX 00:00:03.683 -0500: %CVP_8_5_RPT-1-REPORTING_DB_PURGE_FAILED:The Reporting (Database: cvp_data) database purge operation failed [Duration: 0.50

Purge Fail Solution for Error -310

In order to resolve this failure, drop the junk table in the cvp_data table in the reporting server.

The error code -310 indicates that a table (or fragment rather) already exists. When the purge is run, fragments are detached to a table named junk and then the table is dropped. Since the purge does not run successfully, the Informix.junk table is not deleted at the end of the purge process. In order to resolve this problem, you need to delete the table manually and take these steps:

Step 1. Navigate to Start > All Programs > Informix Dynamic Server > cvp_db_<dbname>.

Purge Fail with Error Failed running the stored procedure sp_frag_mgt: -206

This can be verified in the CVP logs of the reporting server.

Logsnippet:

10.XX.XX.XX Nov 04 2017 00:02:55.489 +0400: %CVP_10_5_RPT-3-REPORTING_DB_ERROR_MSG:   (Database: cvp_data) runDBPurge -- Failed running the stored procedure sp_frag_mgt: -206 [id:4012]
10:XX.XX.XX Nov 04 2017 00:02:55.520 +0400: %CVP_10_5_RPT-1-REPORTING_DB_PURGE_FAILED:  The Reporting  (Database: cvp_data)  database purge operation failed [Duration: 89.72 seconds]. [id:4006]

Purge Fail Solution for Error -206

 Check Frag_mgt output in order to understand why stored procedure failed. If it is because of purgelist, delete the purgelist from cvpadmin table.

Caution: For SQL Fragment logs, ensure to take these steps while in a low voice traffic period.

Step 1. Use the dbaccess tool in order to log in to the cvp_data database and run Call sp_frag_mgt('D');. Output is written in CVPHOME\logs\Frag_mgt.out

Step 2. Verify if it was stuck due to the junk purgelist.

Log snippet:

evaluates to alter fragment on table vxmlelement detach partition sys_p5962 junk 
EXECUTE IMMEDIATE alter fragment on table vxmlelement detach partition sys_p5962 junk 
;
exception : looking for handler
SQL error = -626 ISAM error = -106  error string =  = ""

Step 3. Run the query select *  from ciscoadmin:purgelist against the cvp_admin table in order to check the purgelist.

Caution: Ensure to take these steps while in a low voice traffic period.

Step 4. In order to delete the purgelist, stop the CVP Service Cisco CVP CallServer on the Reporting server.

Step 5. Run the command dbaccess ciscoadmin “delete from purgelist where action='D';”

Step 6. Manually run the procedure sp_frag_mgt in order to check if executes successfully.

Step 7. Start the CVP service Cisco CVP CallServer on the Reporting Server.

Verify

In order to verify whether purge is working properly or not, after the purge fail with Error 206 is solved, follow these steps:

Step 1. You can check the cvp logs and confirm no purge failure messages exist.

Step 2. Run the command onstat –d in order to check the database space, usage, availability, etc.

Step 3. Run these queries against the cvp_data table in order to confirm the data available based on the retention period configured for the respective tables in CVP OAMP purge settings.

   select min(dbdatetime) from vxmlelement
   select min(dbdatetime) from call