Service Operations Insight

 View Only

Tech Tip: Finding and cleaning up DB Inconsistencies 

Feb 09, 2015 04:53 PM

From time to time you should use the soitoolbox to find and clean up any SOI DB inconsistencies .

 

To find out if there are any DB Inconsistencies you can run the following syntax and this does not require you to stop any

SOI Services. The syntax is case sensitive.

 

\soi\tools\soitoolbox.exe -x --findDBInconsistencies

 

The result will look someting like this

-> Checking DB schema version... DONE

-> Looking for orphan candidates... DONE, found 0
-> Looking for orphan CIs... DONE, found 7
-> Looking for Catalyst Inconsistencies... DONE, found 8

 

Inconsistency types are: Orphan CIs, Orphan Candidates and Catalyst Inconsistencies.

                   Where:

                           Orphan Candidates

                                     are CIs which will stay in SOI (due to the bug) if removed by the connector

                           Orphan CIs

                                     are CIs which do not have CIstaging record from any Connector except Reconciler

                                     (this excludes manually, in the Modeler, created services and groups)

                            Catalyst Inconsistencies

                                     are CIs/Relationships which do not exist in SOI DB (in CIStaging table), but

                                     are present in Catalyst DB (ca_ssa_ci_detail table)

 

 

To cleanup  DB Inconsistencies, you can use the following syntax and the soitoolbox utility will

require you to stop all SOI services. The syntax is case sensitive.

 

\soi\tools\soitoolbox.exe -x --purgeDBInconsistencies

Statistics
0 Favorited
3 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

Dec 13, 2016 02:22 AM

In my memory, PrimerUtility.bat has has to run if you observe catalyst errors.

 

But is is safe if you run PrimerUtility.bat after DB purge.

 

- Ashay

Dec 12, 2016 02:50 PM

Is running the PrimerUtility.bat script something that should be done after doing the purgeDBInconsistencies, or only if there are catalyst errors?

Jan 30, 2016 11:16 AM

Hi Swati,

 

After performing findDBInconsistencies and purgeDBInconsistencies you should run below command.

SOI\Tools\Priming Utility\PrimerUtility.bat

Feb 26, 2015 02:01 AM

Kiet

  MDR is available and running.

Now what further shall i adopt in order to remove this catalyst error.

Thanks

Swati

Feb 24, 2015 09:43 AM

Hi Swati,

 

I have review the latest email and stacktrace.txt file and confirm the latest email from catalyst are not for the same

problem so the SQL queries I have provided to you will not fix this problem.

As you can see from the email it is complaining about

com.ca.ucf.api.UCFException: com.ca.ucf.api.UCFException: connector instance or connector is down

at com.ca.ucf.connector.sca.server.ConnectorMux.update(ConnectorMux.java:791)

at com.ca.ucf.connector.sca.server.UnifiedConnectorServiceImpl.update(UnifiedConnectorServiceImpl.java:748)

at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:601)

 

As per the email you got, did you check the following

 

 

Possible Causes of Error:

  • Connection to MDR is Unavailable
  • This exception class can be used generically to warps actual exceptions returned by the underlying transport within UCF framework

 

 

Possible Actions to fix the Error: 

  • Check if MDR is Up and Running
  • Check if MDR Connector is Up and Running (URL to connect)
  • Check if MDR Connector is accessible from SSA Server


Thanks,

Kiet

Feb 24, 2015 05:59 AM

Hi Kiet

   Executed the suggested commands. Still we are getting inconsistencies emails though the count is not that heavy but yes with every restart we get catalyst error emails

Feb 11, 2015 10:07 AM

Hi Swati,

 

Base on the email content

  

Caused by: com.ca.ssa.sor.persistence.ObjectNotFoundException: Object is not available in Catalyst Persistence [getSheet() found no XML content in ci_detail] [sheetID: 71DD2B425EB141FA8B6AAEFC01BEA944]

 

it looks like you are getting those email because there are sheets in the ssa_ci_detail table that have no value in the c_xmlcontent column.

 

We will need to clean up those invalid sheets and let SOI rebuild them.

 

Since you already have an issue open for this problem so I will work from that issue.

 

Thanks,

Kiet

Feb 11, 2015 09:32 AM

Email test is like---

Error Metadata:

  • Error Message : Exception converting sheet data object.
  • Error Code : 105
  • Error Time : 2015/02/10 18:09:01

 

 

Possible Causes of Error: 

  • Failed to convert DataObject to Notebook/Sheet using SDO



Possible Actions to fix the Error:

  • Contact Level 1 or Level 2 Support. This error occured in the Internal code that is expected to convert data objects

 

Apart from this there is an attachment in email namely stack trace.txt that contains below information

NoteBook ID: 6460B92C562B4A7AA9831048E8473F64

Transaction ID:

 

 

com.ca.ssa.sor.notebook.DataObjectConversionException: Exception converting sheet data object.

  at com.ca.ssa.sor.notebook.DataObjectConverterImpl.convertSheet(DataObjectConverterImpl.java:77)

  at com.ca.ssa.sor.notebook.DataObjectConverterImpl.convertNotebook(DataObjectConverterImpl.java:48)

  at com.ca.ssa.sor.reconciliation.Instruction.processNotebookList(Instruction.java:433)

  at com.ca.ssa.sor.reconciliation.Instruction.call(Instruction.java:98)

  at com.ca.ssa.sor.reconciliation.InstructionObserver.notify(InstructionObserver.java:38)

  at com.ca.ssa.sor.logicserver.TopicObserver.update(TopicObserver.java:19)

  at com.ca.ssa.sor.notification.jms.JMSClusteredTopic$JMSClusteredSubscriber.onMessage(JMSClusteredTopic.java:159)

  at org.apache.activemq.ActiveMQMessageConsumer.dispatch(ActiveMQMessageConsumer.java:1229)

  at org.apache.activemq.ActiveMQSessionExecutor.dispatch(ActiveMQSessionExecutor.java:134)

  at org.apache.activemq.ActiveMQSessionExecutor.iterate(ActiveMQSessionExecutor.java:205)

  at org.apache.activemq.thread.PooledTaskRunner.runTask(PooledTaskRunner.java:122)

  at org.apache.activemq.thread.PooledTaskRunner$1.run(PooledTaskRunner.java:43)

  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

  at java.lang.Thread.run(Thread.java:722)

Caused by: com.ca.ssa.sor.persistence.ObjectNotFoundException: Object is not available in Catalyst Persistence [getSheet() found no XML content in ci_detail] [sheetID: 71DD2B425EB141FA8B6AAEFC01BEA944]

  at com.ca.ssa.sor.persistence.impl.SessionImpl.getSheet(SessionImpl.java:633)

  at com.ca.ssa.sor.notebook.DataObjectConverterImpl.convertSheet(DataObjectConverterImpl.java:71)

  ... 14 more

Feb 10, 2015 11:45 PM

Hi

  PFA for your reference

 

Regards

Swati

Feb 10, 2015 11:20 AM

Hi Swati,

 

The emails you are getting must likely are cause by something else and would not be resolved by running the steps above.

Can you post the email content you got?

 

Thanks,

Kiet

Feb 09, 2015 11:31 PM

FYI Sample Run and what to expect:

 

C:\Program Files (x86)\CA\SOI\Tools>soitoolbox --getStatisticalData -b 300 -m soisqldb

Started 30/01/2015 at 22:44:23

-> Going to access 'SAMStore' database on machine 'soisqldb'

   Provide password for user 'xxxx':

-> Initializing DB connection to SAMStore@soisqldb (timeouts=300/30)... DONE

-> Checking DB schema version... DONE

Database statistics:

Summary statistics for all 6 connectors

Alerts_Acknowledged_in_Catalyst:       2280

Alerts_Cleared_in_Catalyst    :          0

Alerts_in_Catalyst            :     284180

Alerts_in_SOI                 :       1518

BinRelationships_in_Catalyst  :     144630

BinRelationships_in_SOI       :     144630

OtherCIs_in_Catalyst          :     140960

OtherCIs_in_SOI               :     140959

Services_in_Catalyst          :         69

Services_in_SOI               :         69

 

Re-running the --findDBInconsistencies command:

 

C:\Program Files (x86)\CA\SOI\Tools>soitoolbox -x --findDBInconsistencies -b 300

Started 29/01/2015 at 17:16:45

-> Parsing configuration file soitoolbox.cfg ... Done, 56 lines processed

-> Initializing DB connection to SAMStore@SOISQLDB: (timeouts=300/30)... DONE

-> Checking DB schema version... DONE

-> Looking for orphan candidates... DONE, found 12

-> Looking for orphan CIs... DONE, found 126

-> Looking for Catalyst Inconsistencies... DONE, found 679

 

Running the actual --purgeDBInconsistencies command:

 

C:\Program Files (x86)\CA\SOI\Tools>soitoolbox --purgeDBInconsistencies -b 300 -m soisqldb

Started 30/01/2015 at 22:45:39

-> Looking for SOI services on machine soisqldb

-> Connecting to the server soisqldb ... DONE

   Service CA SAM UI Server                    : NOT FOUND

   Service CA SAM Store Indexer                : NOT FOUND

   Service CA SAM Integration Services         : NOT FOUND

   Service CA Cat. container CatalystConnector : NOT FOUND

   Service CA SAM Event Management             : NOT FOUND

   Service CA UCF Broker                       : NOT FOUND

   Service CA SAM Application Server           : NOT FOUND

 

-> Going to access 'SAMStore' database on machine 'soisqldb'

   Provide password for user '***':

-> Initializing DB connection to SAMStore@soisqldb (timeouts=300/7200)... DONE

-> Checking DB schema version... DONE

Warning, a destructive DB operation has been selected. Continue? (Yes / [No]) Yes

-> Checking if SOI is running... NO, all SOI services are stopped

-> Removing all orphan CIs... DONE in 5.3 seconds

-> Removing Catalyst inconsistencies DONE in 39.7 seconds

 

Re-running the --findDBInconsistencies command:

 

C:\Program Files (x86)\CA\SOI\Tools>soitoolbox --findDBInconsistencies -b 300 -m soisqldb

-----------------------

Then did the check and now down to 0:

C:\Program Files (x86)\CA\SOI\Tools>soitoolbox -x --findDBInconsistencies -b 300

Started 30/01/2015 at 22:52:18

-> Parsing configuration file soitoolbox.cfg ... Done, 56 lines processed

-> Initializing DB connection to SAMStore@SOISQLDB: (timeouts=300/30)... DONE

-> Checking DB schema version... DONE

-> Looking for orphan candidates... DONE, found 0

-> Looking for orphan CIs... DONE, found 0

-> Looking for Catalyst Inconsistencies... DONE, found 0

C:\Program Files (x86)\CA\SOI\Tools>

Feb 09, 2015 11:24 PM

Just an FYI for reference:

D:\APPS\CA\SOI\Tools>soitoolbox.exe --help

Started 09/02/2015 at 23:10:47

Integrator's toolbox for CA SOI 3.x

Version v. 0.19.5

Usage:

      soitoolbox [--machine=<localhost> | -m <localhost>]

                 [--dbName=<SAMStore> | -d <SAMStore>]

                 [--dbArchiveName=<SOIArchiveDB>]

                 [--dbUsername=<sa> | -u <sa>]

                 [--dbPassword=<> | -p <>]

                 [--mUsername=<Administrator> | -n <Administrator>]

                 [--mPassword=<> | -w <>]

                 [--credentials=<> | -x]

                 [--verbose | -v]

                 [--beSmart | -s]

                 [--timeout=<> | -t <>]

                 [--dbConnectionTimeout=<30> | -b <30>]

                 [--quiet | -q]

                 [--stopAllServices | --startAllServices | --restartAllServices | --getServiceStatus]

                 [--startConnector [--connector=<*> | -c <*>]]

                 [--stopConnector  [--connector=<*> | -c <*>]]

                 [--getConnectorStatus]

                 [--getAvailableConnectors]

                 [--getStatisticalData [--connector=<*> | -c <*>]]

                 [--cleanImportedData  [--connector=<*> | -c <*>]]

                 [--cleanHistoryData=<olderThanXDays>]

                 [--cleanSecurityData]

                 [--cleanData]

                 [--purgeClearedAlerts=<olderThanXDays>[:<purgeType>]]

                 [--archiveHistoryData=<olderThanXDays>]

                 [--restoreHistoryData=<newerThanXDays>]

                 [--reinitializeDB]

                 [--rebuildIndexes]

 

Where:

    --help           ... prints this help page

 

    CONFIGURATION OPTIONS

    -m, --machine    ... Specifies the machine to connect to. It runs the SOI DB (default: localhost)

                         This is for single machine SOI installation ONLY! Use -x for distributed SOI installation.

    -n, --mUsername  ... Specifies the Windows user with privileges to access 'machine' over network (default: Administrator)

                         (has no effect when machine=localhost)

                         This is for single machine SOI installation ONLY! Use -x for distributed SOI installation.

    -w, --mPassword  ... Specifies the password in plain text for the Windows user

                         (has no effect when machine=localhost)

                         This is for single machine SOI installation ONLY! Use -x for distributed SOI installation.

    -d, --dbName     ... Specifies SOI database name (default: SAMStore)

                         This is for single machine SOI installation ONLY! Use -x for distributed SOI installation.

    -u, --dbUsername ... Specifies the user with privileges to access SOI database (default: sa)

                         This is for single machine SOI installation ONLY! Use -x for distributed SOI installation.

    -p, --dbPassword ... Specifies the password in plain text (if not specified, utility asks for it during runtime)

                         This is for single machine SOI installation ONLY! Use -x for distributed SOI installation.

    --dbArchiveName  ... Specifies the archive database name (default:SOIArchiveDB)

                         This name is used by --archiveHistoryData or --restoreHistoryData action commands

    -x               ... Equivalent for --credentials=soitoolbox.cfg

    --credentials    ... Specifies the name of the file containing credentials (user name & password) for all machines

                         which run SOI components. This is needed when working with distributed SOI installations.

                         If the specified configuration file does not exist, the utility creates a default one

                         that needs to be edited manually.

    -q, --quiet      ... Specifies that the user is not alerted before running destructive(delete) operations

    -t, --timeout    ... Sets the generic timeout to a given value (in seconds).

                         The timeout is used differently by particular Action Commands (e.g. for DB operations,

                         it defines the value of a query timeout).

                         Note: For Action Commands which modify the DB, the default value is set to 7200 seconds and

                         for service operations the default is 60 sec.

    -b, --dbConnectionTimeout Specifies the timeout (in seconds) to wait time for the initial DB connection open

                         (default: 20)

    -c, --connector  ... Specifies the connector(s) which will be taken into an account by action commands.

                         It expects a comma separated list of connector names.

                         (the default value is '*' which means all connectors in the system)

                         Usage examples:

                           --connector=CA:09998_soimachine.ca.com,CA:00005_soimachine.ca.com

                           --connector=CA:09998_*

                           --connector=CA:*_soimachine.ca.com

                           --connector=*

    -v, --verbose    ... Turns on more detailed output for some of the action commands.

    -s, --beSmart    ... Changes the way the utility turns off particular SOI components.

                         The default behavior is to stop all SOI services on all machines.

                         This option forces the utility to stop only components (services, connectors)

                         which are affected by or may affect a given action command. (see below)

                         This option requires --credentials to be specified

  

     **ACTION COMMANDS**

                             ** Services related **

    --stopAllServices    ... Stops all Windows services which form SOI system

    --startAllServices   ... Starts all Windows services which form SOI system

                             (services are started in recommended order)

    --restartAllServices ... Restarts (stops and starts again) all Windows services which form SOI system

    --getServiceStatus   ... Prints status of all Windows services which form SOI system

                             ** Connectors related **

    --startConnector     ... Starts connector(s) specified by the --connector parameter

                             This command must be used with the --credentials parameter.

                             (Note: The SOI UI Server has to be running, otherwise the command fails)

    --stopConnector      ... Stops connector(s) specified by the --connector parameter

                             This command has to be used with the --credentials parameter.

                             (Note: The SOI UI Server has to be running otherwise this command fails)

    --getConnectorStatus ... Prints the status of all connectors (including system connectors) in SOI

    --getAvailableConnectors Lists the names of all connectors present in the system (excluding system ones)

                             These connector names can be passed to --connector option

 

                             ** Info **

    --getStatisticalData ... Displays statistical data of the SOI database

                             (e.g. how many CIs, Alerts, Services there are)

                             Output is influenced by -c option.

 

                             ** DB cleanup related **

    --cleanHistoryData   ... Deletes all historical data older than specified number of days from SOI database

                             <olderThanXDays> specifies point in time which is used to decide whether data is eligible

                                              for the clean operation

                                              (Note: 0 days ~ current day at 00:00 AM. It means it does not cover data

                                              created within that particular day.

                                              Use asterisk '*' to cover all data created up to "now")

    --cleanImportedData  ... Deletes all data which came from selected connector(s) (see -c option)

                             (it does not delete any user created data, such as manually created services)

    --cleanSecurityData  ... Deletes all service access rights (i.e. which users/groups can access which services)

    --cleanData          ... It is equivalent to:

                             "--cleanHistoryData=* --cleanSecurityData --cleanImportedData --connector=*"

    --purgeClearedAlerts ... Deletes all cleared alerts from the database based on defined filter parameters

                             <purgeType> is one of following:

                              - 'Strict' clean only alerts which have been marked as cleared by all the systems

                                         (i.e. in all connectors and SOI)

                              - 'Full'   clean all alerts which have been cleared in at least one system

                             <olderThanXDays> specifies point in time which is used to decide whether an alert is eligible

                                              for the purge operation .

                                              (Note: 0 days ~ current day at 00:00 AM. It means it does not cover data

                                              created within that particular day.

                                             Use asterisk '*' to cover all data created up to "now")

    --archiveHistoryData ... Moves all historical data older than specified number of days to the backup

                             database called 'SOIArchiveDB'

                             (Note: 0 days ~ current day at 00:00 AM. It means it does not cover data

                                    created within that particular day.

                                    Use asterisk '*' to cover all data created up to "now")

    --restoreHistoryData ... Copies historical data newer than specified number of days from the backup

                             database 'SOIArchiveDB' to the main database

    --reinitializeDB     ... Empties the whole SOI database - deletes data from all tables

                             (use it to recreate a clean state after a fresh installation)

    --rebuildIndexes     ... Rebuilds database indexes

 

D:\APPS\CA\SOI\Tools>

Feb 09, 2015 09:10 PM

Hi Kiet

    Deleting these DB inconsistency many times, still we are getting catalyst errors. This auto mailer comes in count of thousands with every restart of SAM especially.

 

 

 

 

 

Regards

Swati

Related Entries and Links

No Related Resource entered.