How to drop and rebuild the Spectrum Report Manager database without performing a re-installation?

Products

• DX NetOps

Issue/Introduction

Due to the size or data integrity of the Spectrum Report Manager (SRM) database it may be necessary to recreate the database from scratch without performing a new installation.

Environment

Release : 10.2.x, 10.3.x, 10.4.x, 21.2.x, 22.2.x

Component : Spectrum Reporting

Resolution

NOTE: In the following commands where it requires to enter the password for root:

Replace <PASSWD> with "root" for Spectrum versions 21.2.2 and below
Replace <PASSWD> with "MySqlR00t" for Spectrum version 21.2.4 and above

Please follow the following steps in order to complete this process:

1. Login to the command line of the Report Manager system as the Spectrum install owner.

2. Login to the reporting database via mysql using the following commands:

cd <SPECROOT>/mysql/bin

./mysql -uroot -p<PASSWD> reporting -A                                                                (Windows)
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting -A          (Linux)

example:

./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting -A

3. Run the following commands in mysql:

select * from registry\G           (Copy the "ServersList" field and store it in a safe place)
drop database reporting;
create database reporting;

4. Stop and restart the Spectrum Tomcat service. This restart will re-create the database.

$SPECROOT/tomcat/bin/stopTomcat.sh

$SPECROOT/tomcat/bin/startTomcat.sh

5. Log back into MySQL using the same commands in step 2 above and run the following:

update registry set ServersList = '[Values from Step 3]';

example:

update registry set ServersList = '[server_0314]';

6. Stop the Spectrum Tomcat service again.

$SPECROOT/tomcat/bin/stopTomcat.sh

7. Run the following commands to initialize the database and pull in the data from all Archive Managers for the last 45 days.

cd <SPECROOT>/bin

./RpmgrInitializeLandscape.sh root <PASSWD> -initHist 45 -all

8. Start the Spectrum Tomcat service.

$SPECROOT/tomcat/bin/startTomcat.sh