CA Spectrum Database Maintenance Guidelines and Suggestions
search cancel

CA Spectrum Database Maintenance Guidelines and Suggestions

book

Article ID: 48878

calendar_today

Updated On:

Products

CA Spectrum DX NetOps

Issue/Introduction

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)


Environment

All Supported Releases

Resolution

MySQL Database Maintenance

Quick Reference Sheet for DX NetOps Spectrum

 

Database Maintenance

Quick Reference

If the Mysql database is corrupt, this command should be tried first.

  1. Find and shutdown the Spectrum mysql service
  2. Navigate into $SPECROOT/mysql/bin
  3. Type the following to repair the Archive Manager database:

./myisamchk –-sort_buffer_size=500M –r –n ../data/ddmdb/*.MYI

 

  1. Type the following to repair the Reporting database:

./myisamchk –-sort_buffer_size=500M –r –n ../data/reporting/*.MYI

 

Repair times

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.

 

Repair Details

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:

 

  1. execute mysqlcheck without any options to determine if any table corruption exists:

oscommandline> mysqlcheck -uroot -p<passwd> <database_name> [<table_name>]

If any errors are reported proceed to step 2; otherwise, no further processing is required.

  1. execute mysqlcheck with both '--repair' and '--quick' options to attempt a quick repair:

oscommandline> mysqlcheck -uroot -p<passwd> --repair --quick <database_name> [<table_name>]

If any errors are reported proceed to step 3; otherwise no further processing is required.

  1. execute mysqlcheck with just the '--repair' option to perform a standard repair operation:

oscommandline>mysqlcheck -uroot -p<passwd> --repair <database_name> [<table_name>]

If any errors are reported proceed to step 4; otherwise no further processing is required.

  1. execute mysqlcheck with the '--repair' and '--force' options:

oscommandline>mysqlcheck -uroot -p<passwd> --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:

 

  1. Stop the execution of any applications utilizing the database that you are interested in checking/repairing (e.g. One-Click, Spectrum, Archive Manager ).
  2. Stop the MySQL server process.
  3. Navigate to the directory of the database (e.g. 'reporting','ddmdb' ) containing tables to be checked/repaired.
  4. Execute myisamchk without any options to determine if table corruption exists; the following will check all tables within the given database directory:

oscommandline>myisamchk *.MYI

If you are interested in checking the state of a single table and associated indices, issue the following to perform myisamchk on 1 table:

oscommandline>myisamchk <table_name>

If any errors are reported, proceed to step 5; otherwise no further processing is required.

  1. Execute myisamchk with the '-repair' option to repair each damaged table individually:

oscommandline>myisamchck -repair <table_name>

If any errors are reported, proceed to step 6; otherwise no further processing is required.

  1. Execute myisamchk with the '-repair' and '--safe-recover' options to repair each damaged table individually:

oscommandline>myisamchck -repair --safe-recover <table_name>

 

 

Index Maintenance

The following should be run at least once a month to maintain speed, and system resources like disk space and memory.

  1. Navigate into $SPECROOT/mysql/bin
  2. Execute the following for the Reporting database:

./mysqlcheck –-defaults-file=../my-spectrum.cnf –uroot –p<PASSWD> –o reporting

 

  1. Do the following for the Archive Manager database:
  2. Navigate into $SPECROOT/SS/DDM/scripts
  3. Type:

./db_optimize.pl

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:

$SPECROOT/mysql/data/ib*

This includes:

Ibdata1

 

Ib_logfile0

Ib_logfile1

 

Backing up the database

 

The database should be backed up as often as possible.  This will give more restore points should an issue arise.

  1. Navigate to $SPECROOT/mysql/bin
  2. Type the following for the reporting database:

./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<PASSWD> --databases reporting > <dump_log>

 

  1. Do the following for the Archive Manager database:
  2. Navigate to $SPECROOT/SS/DDM
  3. ./ddm_save <filename>

 

Restoring backups

 

To restore a backup taken with mysqldump, do the following from $SPECROOT/mysql/bin:

./mysql --defaults-file=../my-spectrum.cnf -uroot –p<PASSWD> reporting < <dump_log>

To restore a backup of the Archive Manager database, do the following from $SPECROOT/SS/DDM:

./ddm_load <savefile>

Attachments

1571051807343__TEC593028.zip get_app
Powered by Wolken Software