Share This:

Welcome to September’s new AR Server Blog post. Vicki OBrien is discussing checkdb Utility.

 

The Database Consistency Checker utility is used to check the consistency of the metadata stored in the Remedy AR System data dictionary.

 

It checks the following database information:

    • Data dictionary consistency - Verifies whether all the tables that comprise the data dictionary are available and structured properly in the database, and reports errors for any inconsistencies found
    • Metadata consistency - Verifies whether the BMC Remedy AR System metadata is intact and consistent across the various data dictionary tables
    • Index consistency - Verifies whether all expected indexes are intact, and reports warnings or errors for any inconsistencies found. A missing or damaged index can cause unexpected performance issues
    • Workflow assignment or qualification and display properties consistency - Verifies the consistency of workflow actions and their qualifications during server cache loading on AR System server startup

NOTE:

The -checkdb option is a standalone option. When you use this option, the process checks the consistency of the AR System Server database, reports the results in the checkdb log file, and then quits.
Therefore, do not include the -checkdb option with arserver/arserverd in the armonitor.conf file.

 

 

Setup Requirement

For Remedy version 9.1, it's required to set dt_socket port

    • From ARSystemServerInstallDir, open the arserver.config file for Windows or the arserverd.conf file for UNIX
    • Change the port 12444 for the dt_socket to 12445 to avoid any port conflict.  If it does not exist, add the line manually:

jvm.option.<number>=-agentlib:jdwp=transport=dt_socket, server=y, suspend=n, address=12445

    • Save the file
      • NOTE: Remember to revert the change after the dbcheck has completed
      • Above steps are not applicable for Remedy version older than 9.0 and steps are not required for Remedy version 9.1.02 onwards

 

 

Executing the Command

Start the AR System server executable using the -checkdb option as shown below.  The full path to the logs is optional, if omitted it will create it in the default directory <ARSystemInstallDir>\Arserver\db.
If the log file already exists it will be overwritten.

Note: If your AR Server is non-Unicode then remove --unicode from command line

    • Windows:

<JAVA_HOME>\bin\java.exe -jar <ARSystemInstallDir>\arserver.jar --unicode -checkdb "<Full Path to log file>" -s "<ARSystemServerName>" -i "<ARSystemInstallDir>" -l "<ARSystemLicenseDir>"

 

    • Unix:

<JAVA_HOME>/bin/java -jar <ARSystemInstallDir>/bin/arserver.jar --unicode -checkdb "<Full Path to log file>" -s "<ARSystemServerName>" -i "<ARSystemInstallDir>" -l "<ARSystemLicenseDir>"

db checker 01.PNG

 

Alternative method to run the database consistency checker in Windows

 

The ardbutils.bat – dbCheckOnly can be run against an open Remedy AR Server

 

    • Open command prompt (cmd.exe) as "Run as administrator"
    • Navigate to ARSystemServerInstallDir
    • Run the following command:  ardbutils.bat -dbCheckOnly

dbcheckeronlu.png

 

Sample output – ardbcheck.log

 

/ Checking DB Consistency Trace Log -- ON (AR Server 9.1.07 201902182039)

/ Checking AR System metadata tables schema consistency ...

/ OK

/ Checking foreign keys for metadata tables ...

/ OK

/ Checking value limits of columns for metadata tables ...

/ OK

/ Checking group consistency in group_cache table ...

/ OK

/ Checking container references consistency ...

/ <containerId=4944,referenceId=4,overlayGroup=0> has an invalid referenceObjId inner reference value in <arreference> table: containerId [0] was not found in [arcontainer] table or has invalid overlayGroup

/ <containerId=4944,referenceId=19,overlayGroup=0> has an invalid referenceObjId inner reference value in <arreference> table: containerId [0] was not found in [arcontainer] table or has invalid overlayGroup

/ <containerId=4958,referenceId=10,overlayGroup=0> has an invalid referenceObjId inner reference value in <arreference> table: containerId [0] was not found in [arcontainer] table or has invalid overlayGroup

/ WARNING

/ Checking join form consistency ...

/ OK

/ Checking overlay consistency ...

/ OK

/ Checking encoded structures consistency (properties, qualifications, assignments) ...

/ OK

/ Loading forms for dynamic data consistency check ...

/ OK

/ Checking form references consistency ...

/ OK

/ Checking dynamic tables(T/H/B), indexes and columns consistency ...

/ View ShadowLogFormTemplate is either missing or corrupted for form ShadowLogFormTemplate

/ CheckDB Trace Log -- OFF

 

 

Database table verification

The database consistency checker verifies the following tables during server cache initialization:

 

Table name

group_cache

arschema

schema_group_ids

subadmin_group

schema_list_fields

schema_sort

schema_index

schema_archive

schema_audit

schema_join

schema_view

schema_vendor

schema_vui

field

join_mapping view_mapping vendor_mapping

field_int, field_real, field_char, field_diary, field_enum, field_enum_values, field_attach, field_dec, field_curr, field_table, field_column, field_view, field_display, and field_date

field_dispprop

arcontainer

arctr_group_IDs

arctr_subadmin

cntnr_ownr_obj

arreference arcontainer

filter_set, filter_push, filter_sql, filter_call, filter_serviceaction

filter

filter_mapping

escalation

escal_mapping

actlink

actlink_mapping

arreference arref_group_IDs

field_permissions

 

 

Troubleshooting execution failures

1.

troubleshooting 01.png

    • Check the arserver.config/arserverd.conf  for the port no specified for the dt_socket as explained earlier in this blog.  This port needs to be set to 12445

 

2.

Server Startup Notification Port [ARSERVERPORT] :null

Mon Jul 27 11:23:21.085 2020 com.bmc.arsys.dbutil.UpgradeManager.init() failed!! Cause:

  1. com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'SQ'

 

    • Check for any discrepancies or missing jar file - if you can compare with a working environment or any other environment.
    • In the ARSystem\lib\upgradeutils directory there is a jar file called ddlutils-1.0.jar, you can replace this jar file from another environment or even a later version will work.

 

3.

A defect has been reported against Remedy version 19.08 and 20.02 where the check fails due to a port conflict on port 40002.

You will see a return code of 2 along with the Address already in use: NET_Bind message

2002 defect.PNG

 

 

Gathering information for support:

If you encounter problems trying to execute the checkdb utility, then create a case with Support and provide the following:

  1. Enable sql logging from the Server Information Logs tab, rerun the dbcheck and check the resulting sql.log file
  2. You can also configure debug logging for the DB Checker initialization that will be appended to the arexception.log.
    • Open logback_server.xml from the "Conf" directory
    • Locate these lines:

<!---Logger for the dbchecker invoked via arserver.exe -checkdb -->
<logger name="com.bmc.arsys.upgradeutils.consistency.log.DBCheckLogger"
level="info" additivity="false">
<appender-ref ref="DBConsistencyLog" />
</logger>

    • Just below those lines add the following and save the file:

<logger name="com.bmc.arsys.upgradeutils.UpgradeManager"
level="debug" additivity="false">
<appender-ref ref="ExceptionLog" />
</logger>

 

    • Run DBChecker again and look at the arexception.log

 

 

Reference Information for additional details