> 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.
> 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:

> • | Vaccuuming 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:
> 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.

> Step 1 | Go to the “bin” sub-directory of the PostgreSQL installation directory (/users/ems/postgresx.x.x / by default) by entering the following command:

> cd /users/ems/postgres/bin

> Step 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#

> Step 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.

> Step 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 in the Configuring Automated Periodic Database Vacuuming section that follows. 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 /users/ems/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.
> Database and Table Parameters Within 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
> syslogkeywordlist | 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 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 * * * /users/ems/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 (/users/ems/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.

> Step 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.

> Step 2 | Go to the “bin” sub-directory of the Postgres installation directory (/users/ems/postgresx.x.x/ by default) by entering the following command:

> cd /users/ems/postgres/bin

> Step 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.

> Step 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/deletes, 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, infrequent alarm updates, and/or infrequent bulk statistics transfers.
> The database backup creates a number of text files in a directory of your choosing. These text files contain the records from the various tables in the available databases. In the event of data corruption or loss, these text files can be used to restore the information.
> Caution: Database restoration should only be performed after the WEM application has been successfully un- and re-installed. Additionally, it should only be performed with the assistance of a qualified customer support representative.
> Follow the instructions below to backup WEM application databases. 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.

> Step 1 | Go to the WEM installation directory that contains database scripts (/users/ems/server/dbscripts by default) by entering the following command:

> cd /users/ems/server/dbscripts

> Step 2 | Execute the database backup script by entering the following command:

> ./backup.sh backup /users/ems/server/postgres /tmp/backup_dir backup_log_file backup_err_file
> Various parameters used in the above command line are described below:
> backup: This is the key word for taking the backup.
> postgres_dir_path: Path of postgres directory.
> backup_dir_path: Full path name of backup directory.
> logfilename: Name of the log file for generating the log of backup. This parameter is used by WEM when called through periodic backup functionality. This file needs to be removed once backup is complete.
> errfilename: Name of the error log file. In case of failure error code / reason of failure will be logged in this file. This parameter is used by WEM when called through periodic backup functionality. This file also needs to be removed after backup is complete.
> Backup Script Error Codes
> This script returns the error code, which is used to understand the script result if it is succeeded or failed. This will be helpful for client application (caller of script) to take necessary action.
> Following are the supported error codes along with their description:

> 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 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. Script itself asks for more parameters like postgres dir path, backup file location, etc.
> For restoring the WEM database, enter the following command:
> ./backup.sh restore
> Caution: back.sh script is used by WEM Periodic backup functionality to the backup of WEM database. It is recommended to use the WEM Periodic backup functionality to take the WEM database backup.

Cisco Systems Inc.
Tel: 408-526-4000
Fax: 408-527-0883