A document describing various suggestions from the CA Support Team on how to maintain the databases within the CA Spectrum application including Spectrum Report Manager (reporting) and Spectrum Archive Manager Database (DDMDB)
MySQL Database Maintenance
Quick Reference Sheet for DX NetOps Spectrum
If the Mysql database is corrupt, this command should be tried first.
./myisamchk –-sort_buffer_size=500M –r –n ../data/ddmdb/*.MYI
./myisamchk –-sort_buffer_size=500M –r –n ../data/reporting/*.MYI
The time to fully repair the database will vary greatly on size of the database and system resources available. The above commands have been found to perform the fastest. Watch the mysql CPU usage to know that it is working.
Example: 4GB DDMDB can take up to 2-3 hours in some cases. Please plan accordingly.
Each of the following tools can be used to repair corruption. Disk space needed will be 2x the size of the largest table located in the database directory. $SPECROOT/mysql/data/reporting and ddmdb for Archive Manager. Event table us typically the largest table.
There are 2 programs (mysqlcheck and myisamchk) that are used to check tables for corruption and repair (if required).
(Option 1) - mysqlcheck (online database repair)
The first, mysqlcheck, is a MySQL client utility that must be run while the database server is up and running. Using mysqlcheck is the recommended first method of detecting and repairing database corruption. The suggested way to utilize mysqlcheck is to follow the sequence below:
oscommandline> mysqlcheck -u<username> -p<password> <database_name> [<table_name>]
If any errors are reported proceed to step 2; otherwise, no further processing is required.
oscommandline> mysqlcheck -u<username> -p<password> --repair --quick <database_name> [<table_name>]
If any errors are reported proceed to step 3; otherwise no further processing is required.
oscommandline>mysqlcheck -u<username> -p<password> --repair <database_name> [<table_name>]
If any errors are reported proceed to step 4; otherwise no further processing is required.
oscommandline>mysqlcheck -u<username> -p<password> --repair --force <database_name> [<table_name>]
If none of the mysqlcheck operations above repair detected damage, utilize the 'myisamchk' program referenced in the next section.
(Option 2) - myisamchk (offline database repair)
The second, myisamchk, operates on underlying MySQL files directly; therefore, it should only be run when the database server has been shut down. Failure to shutdown the database server before executing myisamchk can lead to database corruption. A best practice is to exhaust the corruption and repair capabilities of mysqlcheck before considering the use of myisamcheck since it must be run when the database is offline. The suggested way to utilize myisamchk is to follow the sequence below:
If you are interested in checking the state of a single table and associated indices, issue the following to perform myisamchk on 1 table:
If any errors are reported, proceed to step 5; otherwise no further processing is required.
oscommandline>myisamchck -repair <table_name>
If any errors are reported, proceed to step 6; otherwise no further processing is required.
oscommandline>myisamchck -repair --safe-recover <table_name>
The following should be run at least once a month to maintain speed, and system resources like disk space and memory.
./mysqlcheck –-defaults-file=../my-spectrum.cnf –uroot –proot –o reporting
Answer ‘Y’ to the prompt to continue.
Both of the above can be triggered via cron or scheduled task to run during non-peak times. These will utilize a WRITE LOCK, meaning nothing else will be able to access the tables while the command runs. Any questions or concerns should be forwarded to CA Spectrum Support Team.
InnoDB Maintenance and Backup
It is very important to back files up regularly, especially before an upgrade of a CA product.
The following files should be backed up regularly as they related to the InnoDB tables:
The database should be backed up as often as possible. This will give more restore points should an issue arise.
./mysqldump --opt --routines --ignore-table=reporting.v_active_user_model --ignore-table=reporting.v_alarm_activity --ignore-table=reporting.v_ncm_config_diff --ignore-table=reporting.v_security_string_accessibility_by_landscape --ignore-table=reporting.v_user_report_security --compress -uroot –p<root_pw> --databases reporting > <dump_log>
To restore a backup taken with mysqldump, do the following from $SPECROOT/mysql/bin:
./mysql --defaults-file=../my-spectrum.cnf -uroot –p<root_pw> reporting < <dump_log>
To restore a backup of the Archive Manager database, do the following from $SPECROOT/SS/DDM: