Database and System Administration
This information is provided for a system administrator who has Windows administrator privileges and is responsible for maintaining the server on which Service Statistics Manager resides:
•Administering the Database
•Recognizing Issues and Applying Workarounds
•Troubleshooting Login Problems
•Using an Evaluation License
•Installing a License
•Updating the SMTP Server and Port Number
Note The Service Statistics Manager server restarts weekly on Sunday. If any problem occurs, email is sent to the address that was entered during the installation.
Administering the Database
Service Statistics Manager automatically performs the tasks described in these topics:
•Understanding Database Purging
•Understanding Database Archiving and Archive Files
To perform additional database and server backups, see Backing Up and Restoring the Database. To move Service Statistics Manager data to another disk, see Moving Service Statistics Manager Server Data on the Same Server.
Understanding Database Purging
Database purging deletes older data from the database, helping to restrict database growth. Service Statistics Manager reports operate on two types of data:
•Aggregated data—Data collectors (monitors) process the following:
–File-based Operations Manager data.
–Sensor-based Service Monitor data.
Service Statistics Manager imports the aggregated data into the database, where it is retained for 90 days. Database purging deletes aggregated data that is older than 90 days.
•CDR data—Call detail records obtained from Service Monitor are imported into the database as is; no aggregation is performed. CDR data is retained for 30 days. Database purging deletes CDR data that is older than 30 days.
For more information, see Understanding Data Rollup and Data Retention.
Understanding Database Archiving and Archive Files
Service Statistics Manager automatically performs weekly full database backups. A full backup stores a snapshot of the database and transaction log files in the database archive directory and, on completion, purges older data from the transaction log. The existing archive and transaction log are temporarily stored before the archive job starts and are deleted on successful completion of the backup process. A full backup will:
•Require extra disk space—Because the existing archive and transaction log are stored temporarily while the archive runs and deleted after successful completion, the archive job requires enough space for two complete database backups. If there is not enough space to back up the database to the archive directory, an e-mail alert is sent.
•Recover from a failed backup attempt—If the archive job fails for any reason, such as insufficient disk space, the original files are restored so that there is no data loss.
Note If the specified archive directory does not have enough disk space to archive the database, the utility also sends an e-mail alert.
Table 8-1 lists files that are related to Service Statistics Manager database archive and restore.
Table 8-1 Service Statistics Manager Database-Related Files
Database-Related Files and Directories
storm_hostname.db in Installation Directory\pw\sybase
Service Statistics Manager database.
pronto.log in Installation Directory\pw\sybase
Current transaction log file; stores database transactions that have occurred since the previous full backup.
BackupLocation in Installation Directory\pw\dbarchive
A plain text file that contains the location of the most recent full backup of the database.
Note Do not edit the BackupLocation file. Whenever a full backup is done, the contents of this file and all existing incremental backup files are deleted.
Directory where Service Statistics Manager stores the weekly full database archive and transaction log.
Note If, in addition to the weekly full backup, you perform a full database backup from the command line, you can specify another archive directory. The BackupLocation file is updated with the name of the archive directory that you used.
Backing Up and Restoring the Database
Backing up (archiving) the Service Statistics Manager database is highly recommended as part of regular maintenance, to:
•Provide a measure of safety against hardware failure or administrative errors.
•Maintain optimal system performance by removing older data from the transaction log, preventing the log from becoming unmanageably large.
Service Statistics Manager automatically backs up the database weekly. A full backup runs as this scheduled task: pn_WeeklyJob_ArchiveDB0530.
For more information, see Understanding Database Archiving and Archive Files.
Note Database archiving does not interrupt data collection or stop any Service Statistics Manager process.
Service Statistics Manager uses the pw database archive utility to perform backups. You can use the utility from the command line to do additional database backups and to restore the database. See Performing a Full Database Backup.
Performing a Full Database Backup
You can use the pw database archive command to do full database backups that are in addition to the weekly full database backups that Service Statistics Manager performs.
The pw database archive command stores transaction log files and a snapshot of the Service Statistics Manager database in an archive directory. The archive directory can be specified as an input parameter or entered in response to a prompt. The command and options are:
pw database archive [archive directory] [send-email]
If you simply enter the following command:
pw database archive
you are prompted for an archive directory and the utility performs a full database backup.
Table 8-2 describes command-line options and provides links to important information.
Table 8-2 Database Archive Command Arguments
Places backup files in the specified archive directory.
Note It is recommended that you archive the database to a disk other than the one on which the database resides; if the database resides on drive C and you have a drive D, archive to it; for example:
pw database archive D:\tmp\archive
Note If the default directory (Installation Directory\pw\dbarchive) cannot be specified with the pw database archive command, specify another directory.
Sends e-mail if the specified archive directory does not have enough disk space to archive the database.
Example: Performing a Full Database Backup to a Specific Disk
Follow either of these examples:
•Example with a command-line argument:
pw database archive C:\tmp\archive
This command saves the archive in C:\tmp\archive.
•Example without a command-line argument:
The output of this command will be:
About 6 MB free diskspace is needed to archive the database
D:\Program Files\CUSSM\pw\dbarchive has sufficient space for database backup.
Do you want to specify another location to backup the database (Y/N) [Default is
Entering a "y" here produces the following:
Checking Drives to archive
Following drives have diskspace greater than 6 MB
Drive Available DiskSpace
Enter the archive directory for Cisco Unified Service Statistics Manager [default
is D:\Program Files\CUSSM\pw\dbarchive ]:
Pressing Enter produces the following result:
Taking backup of the database and the transaction log ..
Backup Directory is D:\Program Files\CUSSM\pw\dbarchive
Archive log file is relocated at D:\Program
Files\CUSSM\pw\pronto\logs\dbarchive.log for administration.
(Optional) Saving an Existing Full Database Backup Before Creating a New One
You might want to save an existing full database backup to:
•Keep more than one weekly full database backup—After the weekly full database backup completes successfully, Service Statistics Manager deletes the previous full backup file.
•Preserve the current weekly full backup along with any full backup that you do in the interim—If you perform a full database backup from the command line and do not specify a location different from the default archive directory, the most recent weekly full backup is overwritten without warning.
To save a full database backup, use Windows Explorer to copy the dbarchive directory to a new name. For example, copy this directory:
and paste it to this directory:
Restoring the Database
Note•Running the database restore command stops all Service Statistics Manager processes. After the restore completes successfully, all Service Statistics Manager processes are started.
•If the transaction log file pronto.log is moved from the directory Installation Directory\pw\sybase, restart Service Statistics Manager and do a full backup of the database.
You can restore the database using this command:
pw database restore
The utility obtains the location of the full backup file from the Installation Directory\pw\dbarchive\BackupLocation file.
To restore the database from the previous full backup, use this command:
pw database restore -full
This operation restores the storm_hostname.db and pronto.log files from the Installation Directory\pw\dbarchive directory to Installation Directory\pw\sybase directory.
Example: Restoring a Database from a Full Backup File
To restore the database using the full backup file, enter the following command:
pw database restore -full
The output of this command will be:
Restoring data can take a long time for a large system.
This requires that data collection be shutdown temporarily
while this operation takes place. Continue (y/n)[default is 'n']?
Entering a "y" here produces the following output:
Successfully stopped SSM Server...
Restoring the database only using the full backup files.
Backup directory specified: D:\Program Files\CUSSM\pw\dbarchive
Removing the existing database file storm_procis1.db and the transaction log
Restoring database to D:\Program Files\CUSSM\pw\sybase, please wait ...done.
Successfully brought up SSM Server...
Taking the full backup to D:\Program Files\CUSSM\pw\dbarchive after restoring the
About 5 MB free diskspace is needed to archive the database
Taking backup of the database and the transaction log ..
Backup Directory is D:\Program Files\CUSSM\pw\dbarchive
Moving Service Statistics Manager Server Data on the Same Server
Service Statistics Manager regularly performs database backups. However, the reports and the views that Service Statistics Manager generates are stored outside of the database in the file system.
While re-installing Service Statistics Manager on the same machine and on the same logical drive, to restore the Service Statistics Manager database, generated reports, and views on the same server, use the following procedure:
Note The Service Statistics Manager data cannot be moved to a different server.
Step 1 To stop Service Statistics Manager go to Start Menu > Programs > Cisco Unified Service Statistics Manager and click Stop Server.
A window appears:
Stopping Cisco Unified Service Statistics Manager...
Step 2 After the pop up window displays:
The Cisco Unified Service Statistics Manager service was stopped successfully
Enter the following command to ensure that all processes are stopped :
pw process list
If processes are running, wait and run the command again. Do not proceed until the command shows that no processes are running.
Proceeding while processes run will result in a corrupt database.
Step 3 Take a complete backup of the Installation Directory folder.
Note Failure to copy the entire directory structure can result in unrecoverable database corruption and loss of data.
Step 4 Restore the complete backup of the Installation Directory folder by copying to the New Installation Directory on the same server.
The user will be prompted to overwrite the pw directoryin the New Installation Directory. Proceed to overwrite.
Note The historic reports and views will be available after re-install and restore only if the Installation Directory and New Installation Directory are in the same directory. For example, if CUSSM was installed in C:\Program Files\CUSSM initially, the New Installation Directory should also be in the same directory.
Note Ensure that you take a backup of the new licenses in case you have applied for new Service Statistics Manager licenses after the second re-install before executing Step 4.
Step 5 To start the Service Statistics Manager server go to Start Menu > Programs > Cisco Unified Service Statistics Manager and click Start Server.
A window appears:
The Cisco Unified Service Statistics Manager service is starting.
Wait till the window displays
The Cisco Unified Service Statistics Manager service was started successfully.
Note The above backup and restore procedure is supported only for the same version of Service Statistics Manager. It should not be tried across two different versions.
Recognizing Issues and Applying Workarounds
For issues that arise in your Service Statistics Manager implementation, Table 8-3 contains information that might address them.
Table 8-3 Issues and Workarounds
Wrong sequence number exception (CDR data file transfer failure)
Resolve the problem by doing one of the following:
•Increase the value of file chunk size. To do so, edit the pronet.conf file on the Service Statistics Manager system in the Installation Directory\pw\custom\conf directory. In pronet.conf:
–Search for this property: pronet.apps.filetransfer.chunksize; its default value is 300 KB.
–Increase the value; it is not recommended to increase the file chunk size beyond 500 KB.
•Decrease the poll period in Service Statistics Manager for Service Monitor monitors to 5 minutes. The default poll period for Service Monitor monitor is 15 minutes. By decreasing the poll period, you also decrease the amount of CDR data to be transferred during each poll. For this procedure, contact the Cisco Technical Assistance Center (TAC). If you do not have a Cisco service contract, contact your account manager or reseller. To learn more about your support options, go to:
Jserver server ran out of memory
Increase the maximum heap memory of Jserver by changing the value of MaxHeap in pnjserver.conf.
SSM Agent ran out of memory
Note Usually, it is the SSM Agent on the Operations Manager system that runs out of memory.
To work around this problem, you can increase the maximum heap memory for the SSM Agent, or you can distribute the load across multiple SSM Agents.
Increase Maximum Heap Memory
Increase the maximum heap memory of SSM Agent:
1. On the system where SSM Agent is installed, edit the file
2. Change the value of MaxHeap and LOCMaxHeap
Distribute Load Across Multiple SSM Agents
Additionally, you can distribute the load across multiple SSM Agents on the same Operations Manager system. To do so:
1. Install additional SSM Agents using different ports on the Operations Manager system. Note the port names that you assign to the agents. You will need them in the next step.
2. On the Service Statistics Manager system, enable Autodiscovery to work with the additional SSM Agents that you have installed on Operations Manager. To do so, make the following changes in pronet.conf:
–pronet.ssm.useadditionalomagents = true
This flag specifies that Autodiscovery should not deploy all Operations Manager monitors on the default Operations Manager SSM Agent.
–pronet.ssm.omagent.default.monitors = MonitorUniqueName1, MonitorUniqueName2, etc.
This flag specifies the monitors to deployed on the default Operations Manager SSM Agent.
–pronet.ssm.omagent.1.port = 13133
–pronet.ssm.omagent.1.monitors= MonitorUniqueName3, MonitorUniqeName4
These flags specify the port number to be used by an SSM Agent and the monitors to be deployed on it.
For each SSM Agent that you add, add the properties: pronet.ssm.omagentn.port and pronet.ssm.omagentn.monitors, replacing n with a unique number. For example, if you add three SSM Agents, you would have three sets of properties. This example shows the number in the property name incremented by one each time:
pronet.ssm.omagent.1.port = 13133
pronet.ssm.omagent.1.monitors= MonitorUniqueName3, MonitorUniqeName4
pronet.ssm.omagent.2.port = 13134
pronet.ssm.omagent.2.monitors= MonitorUniqueName5, MonitorUniqeName6
pronet.ssm.omagent.3.port = 13135
pronet.ssm.omagent.3.monitors= MonitorUniqueName7, MonitorUniqeName84
3. Trigger Autodiscovery.
Some monitors stop collecting data on a loaded SSM Agent after restarting the SSM Agent
The agent controller process disconnects the agent if it does not respond to poll requests. A loaded SSM Agent might not respond to poll requests from the agent controller.
To work around this problem, increase values related to the polling period (using the procedure below) and then use the procedure Distribute Load Across Multiple SSM Agents in the previous row of this table.
Increase Values Related to the Polling Period
Edit pronet.conf on the Service Statistics Manager system and increase the values of these properties:
•pronet.apps.agent.pollperiod—Increase the agent polling period on the Service Statistics Manager system.
•pronet.apps.agent.pollperiod.allowednoreplies.tcp—Increase the number of No Replies allowed during agent polling.
•pronet.apps.agent.pollperiod.initnoreplies—Increase the number of No Replies allowed during agent initialization.
Loading CDR data to Service Statistics Manager database fails with a Read timed out exception
This problem can occur in the following cases:
•When running an SQL query on SSM_CDR_TABLE or SSM_CMR_TABLE using SQL tools such as Sybase Interactive SQL, and you do not close the tool.
Close the Sybase Interactive SQL or any other SQL tool that is open.
•When a CDR report is being generated at the same time.
Edit pronet.conf on the Service Statistics Manager system and change the values of these properties:
–pronet.agentcontroller.ssm.dim.DBStatementExecTimeoutInSeconds—Increase the timeout period for execution of database statements. If the value of this property is set to -1, then no timeout is set for database statement execution.
–pronet.agentcontroller.ssm.dim.LoadDBRetries—Increase the number of retries.
–pronet.agentcontroller.ssm.dim.LoadDBRetryWaitTimeInSeconds—Set the appropriate time to wait before doing a retry.
Database server process does not start
If the database process does not start, you can troubleshoot the problem as follows:
1. Restart the SSM server process. If Service Statistics Manager does not function properly after restart, continue to step 2.
2. Check whether the dbsrv process is running by entering the following command:
The command lists each process and the process status in this format: process name status. If the dbsrv process is not running, it is displayed as follows:
If dbsrv is not running, continue to step 3.
3. Start the dbsrv process by entering this command:
If the database starts successfully, you are done. If the database does not start and you see an "Assertion failed" error, the database is probably corrupt and you need to replace it with a backup as directed in step 4.
Note The instructions in the next step explain how to replace the corrupt database with the most recent weekly archive that Service Statistics Manager automatically created.
4. Copy the storm_hostname.db and pronto.log files from the Installation Directory\pw\dbarchive directory to the Installation Directory\pw\sybase directory.
5. Start the SSM server.
Troubleshooting Login Problems
If users cannot log in to Service Statistics Manager, try to find and resolve the problem using the information in Table 8-4.
Table 8-4 How to Verify Licenses, Credentials, and SSM Agent to Enable Login
Service Statistics Manager license
Confirm that your Service Statistics Manager license is valid. For more information, see Using an Evaluation License and Installing a License.
Operations Manager license
1. Log in to Operations Manager.
2. Select CiscoWorks > Server > Administration > Licensing to examine the Name, Version, Size, Status, and Expiration date for the OM license. For Operations Manager product versions with which Service Statistics Manager is compatible, see Quick Start Guide for Cisco Unified Service Statistics Manager 9.0.
Service Statistics Manager discovery credentials
Log in to Operations Manager using the credentials that you provided in Service Statistics Manager to trigger discovery. If you are unable to log in:
1. Update the credentials in Operations Manager. (If the credentials include the username admin and the password no longer works, see Release Notes for Cisco Unified Service Statistics Manager 9.0.)
2. Enter the updated credentials in Service Statistics Manager from the Administration tab. See Retriggering Discovery.
SSM Agent status
See SSM Agent Troubleshooting Tips.
Using an Evaluation License
An evaluation license displays a message at login, showing the number of days remaining until the license expires. When the license expires, a final message is displayed and you can no longer use Service Statistics Manager until you purchase the product, obtain a license file, and install the license file on the Service Statistics Manager system.
Installing a License
To be operationally valid, the license for Service Statistics Manager must support at least as many phones as the license for the associated Operations Manager. To add support for additional phones to Service Statistics Manager, you can purchase incremental licenses. To install a license, perform the following procedure.
Step 1 Copy the new license file to the Service Statistics Manager server into this directory: Installation Directory\pw\licenses\cisco\etc\licenses.
Step 2 Restart the server by selecting Start > All Programs > Cisco Unified Service Statistics Manager Server > Start Server.
For more information, see Quick Start Guide for Cisco Unified Service Statistics Manager.
Updating the SMTP Server and Port Number
The SMTP server that Service Statistics Manager uses is selected during installation. If you need to change it, use this procedure.
Step 1 As an Windows administrator, log into the system where Service Statistics Manager is installed.
Step 2 Edit the pronet.conf file (located in the Installation Directory\pw\custom\conf\ directory) and enter an IP address or DNS name and a port number for the following properties:
Step 3 Restart the server:
a. Select Start > All Programs > Cisco Unified Service Statistics Manager Server > Stop Server.
b. Select Start > All Programs > Cisco Unified Service Statistics Manager Server > Start Server.