WEM Database Maintenance

The following table lists the PostgreSQL databases that can be created during the installation of the WEM application. Certain databases are only created if their respective functionality is enabled in the application. Refer to the Determining Available Databases section of this chapter for information on determining which databases have been created.
Table 1. Databases Created by the WEM
Database Name Description
trapdb

Contains tables pertaining to traps and alarms generated by managed systems.

configdb

Contains tables pertaining to the system and WEM configuration.

mibdb

Contains tables pertaining to SNMP management information base (MIB) objects from managed systems.

templateX

Default PostgreSQL database template(s). X represents the numeric instance of the template.

auditdb

Contains tables pertaining to WEM audit trail information.

bsdb

Contains tables pertaining to bulk statistics generated by managed systems.

p2pdb

Contains tables pertaining to P2P statistics generated by managed systems.



This chapter provides information and instructions for performing the following routine maintenance on these databases to ensure proper data processing and integrity:
  • Vacuuming the Databases
  • Backing-up WEM Databases

CAUTION:

To ensure proper operation and data integrity, the PostgreSQL database instances installed with the WEM should only be used by the application.

This chapter includes the following topics:

IMPORTANT:

Unless otherwise specified, all information in this chapter applies to both Sun Solaris- and Red Hat Enterprise Linux-based WEM systems.

Determining Available Databases

Follow the instructions in this section to determine which databases were created during the installation of the WEM application. These instructions assume that you are logged in to the server on which the application is installed as the user root and have access to the server’s command line.

  1. Go to the “bin” sub-directory of the PostgreSQL installation directory (/<ems_dir>/postgresx.x.x/ by default) by entering the following command:
    cd /<ems_dir>/postgres/bin
    
  2. Enter the PostgreSQL interactive terminal for template1 database by entering the following command:
    ./psql -U postgres_name template1
    
    postgres_name is the name of the PostgreSQL database administrator (postgres by default). The following prompt appears:
    template1#
    
  3. Display available databases by entering the following command:
    select datid,datname
    from pg_stat_database;
    
    A table appears listing the database name and its corresponding identification number.
  4. Exit the PostgreSQL interactive terminal by entering the following command:
    \q
    

“Vacuuming” the Databases

Standard PostgreSQL operation periodically leaves older versions of records in the database even after the record has been updated or deleted. This method allows the record to remain available to other processes or transactions. As a result of frequent database deletes and/or updates, the number of these “old” records can grow and hinder database operation and performance. Excessive numbers of these “old” and unused records increase the time required for queries and consume disk space that could be better allocated for current records and processes. For these reasons, it is recommended that these “old” records be removed regularly.

The removal of these “old” records is done by “vacuuming” the database using the PostgreSQL vacuumdb command.

It is recommended that all WEM databases be “vacuumed” at least once a week. However, databases that undergo frequent record updates/deletions be vacuumed on a daily basis. Two such databases are the trap database (trapdb) and the bulk statistics database (bsdb).

CAUTION:

Database vacuuming should only be performed at a time when there are minimal database transactions. Typically, this would correspond to a time frame in which no configurations are being performed, infrequent alarm updates, and/or infrequent bulk statistics transfers. Before vacuuming the database, the server must check for already running vacuuming. If this is already running, then the next scheduled vacuum process must be skipped. Hence, WEM must execute only one vacuum query at a time for every database.

Periodic, automatic database vacuuming can be performed through the configuration of a text file as described below. This is the preferred method. One-time automatic vacuuming can be performed through the use of the UNIX cron application as described in the Using Cron to Automate Database Vacuuming section. Vacuuming can also be performed manually as described in the Manually Vacuuming WEM Databases section.

Configuring Automated Periodic Database Vacuuming

The WEM application contains a configuration file called vacuum.cfg that contains parameters related to database vacuuming operation and frequency. The file is located in the /<ems_dir>/server/etc directory by default.

The parameters provide the flexibility to vacuum either the database as whole or just various tables within the database. The values for these parameters can be modified using a text editor (such as Vi Editor) if required.

Each database and table is configured with a start time, and a frequency that specifies the number of hours that must pass prior to performing the vacuuming function again. For example, if vacuuming of the Configuration database is to start at 12:00 PM, and repeat after 24 hours, the parameter in the vacuum.cfg file would appear as follows:
ConfigDB = 12,24

IMPORTANT:

A value of 00,00 disables vacuuming for both databases and tables.

Note that the vacuuming of the MIB database is not supported because the content changes infrequently.

The following table provides a list of the parameters within the vacuum.cfg file.
Table 2. Database and Table Parameters in the vacuum.cfg File
Parameter Description Default Setting

Type

Vacuum_Type

Specifies the type of vacuum to perform as one of the following:
  • VACUUM ANALYZE: Performs the vacuum function as described previously in this chapter but also collects statistics about the proportions of duplicate values within a column and the min/max value.
  • VACUUM: Performs only the vacuum function.
  • VACUUM FULL: Physically reorders the tables. This requires an exclusive lock on each table while it is being processed.

VACUUM ANALYZE

Database Name*

ConfigDB

Sets vacuuming parameters for the Configuration database which contains tables pertaining to the system and WEM configuration.

00,00

TrapDB

Sets vacuuming parameters for the Trap database which contains tables pertaining to traps and alarms generated by managed systems.

00,00

AuditDB

Sets vacuuming parameters for the Audit Trail database which contains tables pertaining to WEM audit trail information.

00,00

BulkDB

Sets vacuuming parameters for the Bulk Statistics database which contains tables pertaining to bulk statistics generated by managed systems.

00,00

P2PDB

Sets vacuuming parameters for the P2P database which contains tables pertaining to protocol and threshold statistics generated by managed systems.

00,00

Table Name
ConfigDB

CompleteDB

This parameter dictates whether or not vacuuming will be performed on only the database or each table in the database individually.

A value of 0 indicates that vacuuming is to be performed on each table. A value of 1 indicates that vacuuming is to be performed only on the database.

1

boxer

Sets vacuuming parameters for the table that contains configuration information pertaining to the system.

00,00

portmontable

Sets vacuuming parameters for the table that contains configuration information pertaining to port monitoring.

00,00

boxeruserinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to administrative users configured on the system.

00,00

userpreference

Sets vacuuming parameters for the table that contains configuration information pertaining to preferences configured for WEM administrative users.

00,00

processthreshold

Sets vacuuming parameters for the table that contains configuration information pertaining to WEM process thresholds.

00,00

ippooldefaults

Sets vacuuming parameters for the table that contains configuration information pertaining to IP address pool default values.

00,00

scbrdefaults

Sets vacuuming parameters for the table that contains information about subscriber default parameters.

00,00

csmsessioninfo

Sets vacuuming parameters for the table that contains information on the session id between client and server, client IOR, login time, and address.

00,00

bkinfo

Sets vacuuming parameters for the table that contains information required for configbackup.

00,00

graphscreeninfo

Sets vacuuming parameters for the table that contains configuration information pertaining to graph displays.

00,00

batchjobcleanup

Sets vacuuming parameters for the table that contains information required for cleanup of batch jobs.

00,00

batchjobinfo

Sets vacuuming parameters for the table that contains information required for batch jobs.

00,00

boxeronmap

Sets vacuuming parameters for the table that contains configuration information pertaining to administrative user maps configured on the system.

00,00

dbbackupinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to database backup.

00,00

maptable

Sets vacuuming parameters for the table that contains configuration information pertaining to the map table.

00,00

p2pinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to P2P database.

00,00

updateinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to updates.

00,00

userinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to WEM administrative users.

00,00

dbcurrent

Sets vacuuming parameters for the table that contains configuration information pertaining to the current database.

00,00

hostname

Sets vacuuming parameters for the table that contains configuration information pertaining to host names.

00,00

syslog keyword list

Sets vacuuming parameters for the table that contains configuration information pertaining to the keyword log list configured on the system.

00,00

syslogmsgfileinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to the message log list configured on the system.

00,00

cdpreportinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to CDP reports.

00,00

filefetchinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to fetching of files from the database.

00,00

ongoingsftpinfo

Sets vacuuming parameters for the table that contains configuration information pertaining to the ongoing SFTP.

00,00

TrapDB

CompleteDB

This parameter dictates whether or not vacuuming will be performed on only the database or each table in the database individually.

A value of 0 indicates that vacuuming is to be performed on each table. A value of 1 indicates that vacuuming is to be performed only on the database.

1

trap

Sets vacuuming parameters for the table that contains information pertaining to SNMP traps received.

00,00

deletedtrap

Sets vacuuming parameters for the table that contains information pertaining to deleted SNMP traps.

00,00

forwardinginfo

Sets vacuuming parameters for the table that stores the information for forwarding the received SNMP traps in real time.

00,00

forwardpurgeinfo

Sets vacuuming parameters for the table that is used to get information about whether or not to purge the forwarded trap.

00,00

mailinfo

Sets vacuuming parameters for the table that stores trap e-mail address, e-mail type (TO, CC, or BCC), trap type and trap specific number.

00,00

mailmessage

Sets vacuuming parameters for the table that stores trap e-mail subject, trap message, trap type and trap specific number.

00,00

configure

Sets vacuuming parameters for the table that contains information pertaining to SNMP trap configuration.

00,00

schedule

Sets vacuuming parameters for the table that stores the trap severity, Audio start time, audio stop time.

00,00

forwardaddress

Sets vacuuming parameters for the table that contains information pertaining to the forward address.

00,00

pendingtrap

Sets vacuuming parameters for the table that contains information pertaining to the pending SNMP traps.

00,00

trap_current

Sets vacuuming parameters for the table that contains information pertaining to the current SNMP trap.

00,00

trapoperdetails

Sets vacuuming parameters for the table that contains information pertaining to trap operation.

00,00

AuditDB

CompleteDB

This parameter dictates whether or not vacuuming will be performed on only the database or each table in the database individually.

A value of 0 indicates that vacuuming is to be performed on each table. A value of 1 indicates that vacuuming is to be performed only on the database.

1

auditlog

Sets vacuuming parameters for the table that contains information pertaining to the WEM’s Audit Trail function.

00,00

chassisinfo

Sets vacuuming parameters for the table that contains information pertaining to the chassis.

00,00

cpuinfo

Sets vacuuming parameters for the table that contains information pertaining to the CPU.

00,00

daughtercardinfo

Sets vacuuming parameters for the table that contains information pertaining to the daughter card.

00,00

hardwareactivity

Sets vacuuming parameters for the table that contains information pertaining to the hardware activity.

00,00

hardwareinfo

Sets vacuuming parameters for the table that contains information pertaining to the hardware.

00,00

lcrccspioinfo

Sets vacuuming parameters for the table that contains information pertaining to Line Cards (LC), Redundant Crossbar Cards (RCC), and Switch Processor Input/Output (SPIO) cards.

00,00

pactacinfo

Sets vacuuming parameters for the table that contains information pertaining to the Packet Accelerator Card (PAC) and Telephony Accelerator Card (TAC).

00,00

smcinfo

Sets vacuuming parameters for the table that contains information pertaining to the System Management Card (SMC).

00,00

spccardinfo

Sets vacuuming parameters for the table that contains information pertaining to the Switch Processor Card (SPC).

00,00

imglastevtgentime

Sets vacuuming parameters for the table that contains information pertaining to the last event generation time of the system.

00,00

BulkDB

CompleteDB

This parameter dictates whether or not vacuuming will be performed on only the database or each table in the database individually.

A value of 0 indicates that vacuuming is to be performed on each table. A value of 1 indicates that vacuuming is to be performed only on the database.

1

card

Sets vacuuming parameters for the table that contains information pertaining to the card-level schema.

00,00

port

Sets vacuuming parameters for the table that contains information pertaining to the port-level schema.

00,00

system

Sets vacuuming parameters for the table that contains information pertaining to the system-level schema.

00,00

ggsnsystem

Sets vacuuming parameters for the table that contains information pertaining to the system-level schema for GGSN-based systems.

00,00

ppp

Sets vacuuming parameters for the table that contains information pertaining to the PPP-level schema.

00,00

mipfa

Sets vacuuming parameters for the table that contains information pertaining to the Mobile IP FA-level schema.

00,00

mipha

Sets vacuuming parameters for the table that contains information pertaining to the Mobile IP HA-level schema.

00,00

rp

Sets vacuuming parameters for the table that contains information pertaining to the PDSN-level schema.

00,00

gtpc

Sets vacuuming parameters for the table that contains information pertaining to the GGSN-level schema.

00,00

gtpp

Sets vacuuming parameters for the table that contains information pertaining to the GTPP-level schema.

00,00

ippool

Sets vacuuming parameters for the table that contains information pertaining to the IP Address Pool-level schema.

00,00

apn

Sets vacuuming parameters for the table that contains information pertaining to the APN-level schema.

00,00

lac

Sets vacuuming parameters for the table that contains information pertaining to the LAC-level schema.

00,00

radius

Sets vacuuming parameters for the table that contains information pertaining to the RADIUS-level schema.

00,00

imgaccess

Sets vacuuming parameters for the table that contains information on the XML files generated by the Bulk Statistics Server.

00,00

ecs

Sets vacuuming parameters for the table that contains information pertaining to the ECS-level schema.

00,00

misc

Sets vacuuming parameters for the table that contains information pertaining to the miscellaneous schema.

00,00

ipsg

Sets vacuuming parameters for the table that contains information pertaining to the IPSG-level schema.

00,00

asngw

Sets vacuuming parameters for the table that contains information pertaining to the ASNGW-level schema.

00,00

sgsn

Sets vacuuming parameters for the table that contains information pertaining to the SGSN-level schema.

00,00

sgtp

Sets vacuuming parameters for the table that contains information pertaining to the SGTP-level schema.

00,00

sccp

Sets vacuuming parameters for the table that contains information pertaining to the SCCP-level schema.

00,00

ss7rd

Sets vacuuming parameters for the table that contains information pertaining to the SS7RD-level schema.

00,00

mipv6ha

Sets vacuuming parameters for the table that contains information pertaining to the MIPv6 HA-level schema.

00,00

context

Sets vacuuming parameters for the table that contains information pertaining to the context-level schema.

00,00

cscf

Sets vacuuming parameters for the table that contains information pertaining to the CSCF-level schema.

00,00

ss7link

Sets vacuuming parameters for the table that contains information pertaining to the SS7 Link-level schema.

00,00

gprs

Sets vacuuming parameters for the table that contains information pertaining to the GPRS-level schema.

00,00

pdif

Sets vacuuming parameters for the table that contains information pertaining to the PDIF-level schema.

00,00

schemastatus

Sets vacuuming parameters for the table that contains information pertaining to the schema status.

00,00

P2PDB

CompleteDB

This parameter dictates whether or not vacuuming will be performed on only the database or each table in the database individually.

A value of 0 indicates that vacuuming is to be performed on each table. A value of 1 indicates that vacuuming is to be performed only on the database.

1

bwstat

Sets vacuuming parameters for the table that contains information pertaining to data traffic bandwidth statistics.

00,00

dailystat

Sets vacuuming parameters for the table that contains information pertaining to daily statistics.

00,00

hourlystat

Sets vacuuming parameters for the table that contains information pertaining to hourly statistics.

00,00

protocolstat

Sets vacuuming parameters for the table that contains information pertaining to protocol statistics.

00,00

scbrdailystats

Sets vacuuming parameters for the table that contains information pertaining to subscriber daily statistics.

00,00

scbrhourlystats

Sets vacuuming parameters for the table that contains information pertaining to subscriber hourly statistics.

00,00

scbrstats

Sets vacuuming parameters for the table that contains information pertaining to subscriber statistics.

00,00

tempp2p

Sets vacuuming parameters for the table that contains information pertaining to P2P statistics.

00,00

thresholdstats

Sets vacuuming parameters for the table that contains information pertaining to threshold statistics.

00,00

* Any non-zero number specified for a database in the Database Name section supersedes the CompleteDB parameter setting for the database. For example, if the start value for the Configuration database is 13 and the databases corresponding CompleteDB parameter is set to 0, then vacuuming is to be done for the entire database and all of the tables. However, if there is a conflict between the start times specified for any of the tables within the database and that specified for the database itself, vacuuming will not be started for that table.



Using Cron to Automate Database Vacuuming

As mentioned previously, the UNIX cron application can be used to automate database vacuuming. It is a daemon that is capable of executing commands or scripts at regular time intervals. Additional information on cron is available through its manual pages or on the Internet.

To use cron to automate the vacuum process, add an entry to a crontab on the server on which the WEM is installed. The entry specifies the time and database to be vacuumed. For example, if you wanted to vacuum the bulk statistics database (bsdb) every day at 3:00 AM, you might add the following entry in the crontab:
00 03 * * * /<ems_dir>/postgresx.x.x/bin/vacuumdb
-U postgres -d bsdb > /dev/null 2>&1

From the above example, you can see that the vacuumdb command is located in the bin sub-directory of the PostgreSQL installation directory (/<ems_dir>/postgresx.x.x by default). The -U parameter indicates the PostgreSQL database administrator name (postgres by default).

Manually Vacuuming WEM Databases

Follow the instructions in this section to manually vacuum databases used by the WEM application. These instructions assume that you are logged in to the server on which the application is installed as the user root and have access to the server’s command line.

  1. Ensure that the database(s) are backed-up prior to vacuuming. Refer to the Backing-up WEM Databases section of this chapter for information on performing a database backup.
  2. Go to the “bin” sub-directory of the Postgres installation directory (/<ems_dir>/postgresx.x.x/ by default) by entering the following command:
    cd /<ems_dir>/postgres/bin
    
  3. Vacuum the desired database by entering the following command:
    /vacuumdb
    -U postgres_name -d db_name &
    
    Command/Keyword Description
    postgres_name The name of the PostgreSQL database administrator (postgres by default).
    db_name The name of the database to be vacuumed. Refer to the Determining Available Databases section of this chapter for information on determining which databases are available.


  4. Repeat Step 3 to vacuum additional databases.
    select datid,datname
    from pg_stat_database;
    

Backing-up WEM Databases

To minimize the risk of data loss, WEM databases should be backed up on a regular basis. It is recommended that databases be backed up at least once a week. However, depending on the environment and frequency of record updates and/or deletions, you may choose to backup the data more often.

CAUTION:

Database backups should only be performed at a time when there are minimal database transactions. Typically, this would correspond to a time frame in which no configurations are being performed, alarm updates are infrequent, and/or infrequent bulk statistics transfers are occurring.

The database backup creates compressed (.gz) files that are saved to the <emsdir>/server/flash/backup_<periodicity>_<date+time>/ems_backup directory. For example: /ems/server/flash/backup_DAILY_20110419153530/ems_backup.These files contains records from the various tables available in the databases. In the event of data corruption or loss, these files can be used to restore the information by using the restoreDb.sh script in the <ems dir>/server/scripts directory.

To backup WEM application databases:

  1. Log in to the WEM application.
  2. On the WEM menu bar, click Configuration.
  3. Click System.
  4. Click WEM Database Periodic Backup. The WEM Database Periodic Backup Configuration Dialog Box appears.
  5. Make the desired Backup Date, Recurrence Pattern and Range of Recurrence entries for WEM database backups. For details on the required entries, click Help.
  6. Once you have made the required backup configuration entries, click Apply to activate your settings. WEM saves the database backup to the <emsdir>/server/flash/backup_<periodicity>_<date+time>/ems_backup directory.

Backup Script Error Codes

This script returns error codes for any problems encountered during the backup operation, if any. These codes can assist the WEM client user to take appropriate correction.

Following are the supported error codes along with their description:
Table 3. Error Codes Returned During WEM Database Backup Operation
Error Code Error Name Description
0 SUCCESS Successful
1 INVALID_ARGUMENT Invalid argument
2 REQUIRES_SUPERUSER This script requires superuser privileges
3 INVALID_POSTGRES_DIRECTORY Invalid postgres directory
4 BACKUP_FILE_NOT_EXIST Specified backup file does not exist
5 BACKUP_FAILED_WITH_ERROR Backup failed
6 BACKUP_FILE_CREATION_FAILED Backup file creation failed
7 DATABASE_RESTORE_FAILED Database restore operation failed
8 POSTGRES_NOT_RUNNING Postgres process is not running
9 SERV_SCRIPT_NOT_EXIST <ems_server>/serv script is not present


Restoring the WEM Database

This option is also used with the backup script backup.sh. It restores the WEM databases. No extra parameters are required for restoring the database other than the ‘restore’ keyword. The script itself will prompt for more parameters such as postgres directory path, backup file location, etc.

To restore the WEM database, enter the following command:

./backup.sh
restore

CAUTION:

Use the functionality of the WEM Database Periodic Backup Dialog Box in the WEM client application to perform a WEM database backup. Theback.sh script is used by the WEM periodic backup functionality to backup the WEM database. Refer to the Backing Up WEM Databases section in this chapter.