Troubleshooting database configuration issues

You can encounter issues while upgrading Remedy AR System server due to incorrect database configurations. Use this guide to understand the appropriate logging and troubleshooting steps to either resolve the problem or create a BMC Support case.

Related topics

Contacting Support

Symptoms

Upgrade failed due to insufficient database privileges.
Upgrade failed to run the Database Upgrade Utility.
Upgrade failed to resolve Database Service Name or SID.

Scope

One or more users can experience this problem.
In a server group environment, these issues can affect one or more servers.
These issues can affect any server in the group that is currently being upgraded.
These issues can occur with primary or secondary servers.

Resolution

Step

Task

Description

1

Review configuration checks for the SQL Server database.

If you are using a SQL server, perform the following checks:

If you are using Windows Authentication, the domain account used must be a full administrator on the Windows Server and must have the db_securityadmin and setupadmin roles in SQL Server. This domain account must be a Database Owner (dbo) of the Remedy AR System database.
If any values are different, see Symptom 1.
If you are using a pre-created database, the DB User, for example - 'ARAdmin' must have sp_addrolemember and dbcreator role.
If any values are different, see Symptom 2.
ARAdmin must also be 'dbo' of the AR System database.
The values of the parameters - PARAMETERIZATION must be Simple and READ_COMMITTED_SNAPSHOT should be set to "T" on the database.
If any values are different, see Symptom 3.

For more information, see Configuring the Microsoft SQL Server.

2

Review the configuration for Oracle database.

If you are using Oracle, perform the following checks:

If you are using a pre-created database, ARole_arsys should have these privileges:
alter session, create cluster, create database link, create sequence, create session, create synonym, create table, create view, create procedure, create trigger, query rewrite.
If any values are different, see Symptom 5.
If you are using Oracle 12c Database, see the section "Considerations for an Oracle 12C database" in the topic Configuring Oracle databases.

Review the topic - Best Practice Recommendations. Check the Oracle settings for your environment size and set accordingly.

Component	Database size Small	Medium	Large
`memory_target` (>11g)	8	16	32
`sga_target` (10g)	5	10	20
`Db_cache_size`	3	7	15
`Shared_pool_size`	2	3	5
`Pga_aggregate_target`	3	6	12
`Cursor_sharing`	EXACT	EXACT	EXACT
`_b_tree_bitmap_plans`	False	False	False

If you are using Oracle Data Guard, see the section "Configuring Oracle Data Guard" in the topic Configuring Oracle databases.

3

AR Server Database Configuration checklist

(common for SQL & Oracle DB)

Run the following queries by using a database client:
- select serverName from servgrp_board
- select serverName from servgrp_config
- select serverName from servgrp_resources
1. Check if the correct Server-Connect-Name value is displayed under serverName column.
2. If any values are different, see Symptom 6.
Run the following queries on the control table by using a database client to find values in dbVersion or currDbVersion columns:
- For Remedy version 9.1.03 and earlier: select dbVersion from control;
- For Remedy version 9.1.04 and later: select currDbVersion from control;
1. Validate these values with the DBVERSION and API Protocol Numbers section in the following article:
  BMC Remedy Release Version Strings.
2. If any values are different, See Symptom 7.

Edit the ARSystemInstalledConfiguration.xml file in a text editor and search for the following parameters:
If any values are different, take a backup of this file and make the required changes.
Restart AR Server Services before running the upgrade.

Parameter Setting	Recommended Setting
For SQL Database:
DATABASE	Review all the parameters which contain this name.
BMC_DATABASE_HOST	Current AR Server Database host name. Compare this value with the value on the ar.cfg/ar.conf file.
BMC_IS_SECONDARY_SERVER	False : On standalone servers or on Server Group Administrator Server (primary server) True : All secondary AR server(s)
BMC_SQLSERVER_WINDOWSAUTH_OR_SQLAUTH	SQLAUTH: For SQL and Windows Authentication. WINDOWSAUTH: For Windows Authentication.
BMC_SQLSERVER_ALWAYS_ON	True: When using SQL Server Always On False: When not using SQL Server Always On
For Oracle Database:
DATABASE	Review all parameters which contain this parameter.
BMC_DATABASE_HOST	Current AR Server Database host name. Compare this value with the value on the ar.cfg/ar.conf file.
BMC_IS_SECONDARY_SERVER	False : On standalone servers or on Server Group Administrator Server (primary server) True : All secondary AR server(s)
BMC_DATABASE_ORACLE_SID_OR_SERVICE	False: To use Service Name True: To use SID (From Remedy version 9.1.0x, we recommend to use Service Name)
BMC_DATABASE_ORACLE_CONNECTION_IDENTIFIER	Set this value as Service Name
BMC_DATABASE_INSTANCE	Set this value as Service Name

Check the VUI db table for invalid values. Run the following query by using a database client:

select count (*) from VUI where vuiType = '0'

If the result count is higher than 0, see Symptom 4.

4

Review Current Upgrade Status

Upgrade failed and Rollback successful:
- - To start analyzing this issue, review the following table for specific symptoms.
  - Review Task 5 to Task 8 in the following table and record your analysis while reviewing the logs.
  - After the cause of failure is found, review the control db table to confirm that the database was rolled back successfully to a previous version. See the following article: https://communities.bmc.com/docs/DOC-37267
  - If the database was not rolled back successfully by the installer, then ask your Database Administrator (DBA) to use manual DB rollback method. This method avoids inconsistency.
  - Review the configuration checklist before running the upgrade.
    Note: The Rollback utility does not update ARSystemInstalledConfiguration.xml in some versions. Validate this file while reviewing the configuration checklist.
Upgrade failed and Rollback failed :

- - To start analyzing this issue, review the following table for specific symptoms.
  - Review Task 5 to Task 8 in the following table and record any analysis you find while reviewing the logs.
  - After the cause of failure is found, involve your DBA to perform manual database rollback. Also perform an AR server file system rollback. This rollback is required as you cannot continue the upgrade from the current stage.
  - Review the configuration checklist before running the next attempt of upgrade.

5

Collect, disable, and analyze the logs.

Collecting the logs:

Run the Maintenance Tool Utility for each upgrade issue. See the steps to run the Maintenance Tool Utility.

Enable and combine API & SQL logs in one log file and reproduce the issue. See the steps to capture combined logs.

Disabling the logs:

Disable API & SQL logs (if enabled). Revert steps from steps to capture combined logs.

Analyzing the logs:

You can review the logs yourself to try to identify error messages or behaviors. Use the following solution table to help identify the possible symptoms and solutions.

6

Keywords to review logs

arsystem_install_log.txt: Open the log file in a text editor and perform a search by using the following keywords from end to top:
Note the date and time.

SEVERE: To find failure messages
FAIL: To find Import or Update failure messages
descriptionStarting: To find status of all upgrade utilities; for example, Encryption, FTS, Database and pluginsvrclient.
Each descriptionStarting should have descriptionCompleted which confirms the utility ran successfully
For example:
arserver.installer.encryption.descriptionStarting Encryption Utility should be terminated by arserver.installer.encryption.descriptionCompleted Encryption Utility.
Starting execution of pluginsvrclient utility should be terminated by Pluginsvrclient utility execution completed.

2. armonitor.log: If the AR Server failed to start, open this log file in a text editor and search using these keywords:
Note the date and time.

- - */ Processes failed to Start
  - */ Processes Stopped

3. RIK Logs: These logs are located under <ARInstallDiectory>\ARSystem\Logs directory. Review this log in a scenario of timeout error or definition import failure.

- - Review all logs under this directory which ends with _error.log, search by using 'ERROR RIKMain'.

7

Create a BMC Support Case

Collect and send logs and detailed information when creating a case with BMC Support:

1. Provide the following information as part of your case:
- - Operating system and database versions
  - Is this a Server Group Environment?
    How many servers are in the group?
    Is this a Primary or Secondary server?
  - Environment: PROD/QA/TEST/DEV
  - Is this the first time an upgrade was attempted on this environment?
  - Please share any analysis you found.

b. Collect the following files:

- - Maintenance Tool zip logs
  - API & SQL Log File (if captured)

Attach the ZIP file containing your logs to your case (up to 2 GB). You can also upload the files on FTP.
For more information, see How to use BMC's Managed File Transfer (MFT) to send log files to BMC Support to comply with GDPR policies.

Cause and resolution for error messages

Symptom

Cause

Solution

Reference

1

Windows Authentication is denied when connecting to the database client.

Domain Account does not have full Administrator permissions and does not have required roles.

Involve your DBA for any DB related activity and take required backup.

Perform the following query by using a database client:

exec sp_addrolemember 'dbcreator', 'ARAdmin' (ARAdmin is the db user)

For more information, see Configuring the Microsoft SQL Server.

2

A DB user such as ARAdmin is not able to access or modify the database table

Db User does not have the 'dbo' role.

Involve your DBA for any DB related activity and take required backup.

Perform the following query by using a database client:

For SQL Database:

exec sp_addrolemember 'db_owner', 'ARAdmin' ( ARAdmin is the db user )

For Oracle Database:

GRANT privilege-type TO ROLE_NAME;
For example:
GRANT alter session, create cluster, create database link, create sequence, create session, create synonym, create table, create view, create procedure, create trigger, query rewrite to AROLE_ARSYS;

For more information, see Remedy install fails with "INSUFFICIENT_DATABASE_LOGIN_PRIVILEGES"

3

PARAMETERIZATION not set on SQL Database Server

Remedy AR System uses SQL bind variables when interacting with the database server.

Involve your DBA for any db related activity and take required backup.

Perform the following query by using a database client to set SQL Server SIMPLE Parameterization and READ_COMMITTED_SNAPSHOT:

ALTER DATABASE ARSystem SET PARAMETERIZATION SIMPLE
ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT ON

SELECT is_read_committed_snapshot_on FROM sys.databases where name = 'ARSystem' (ARSystem is the database table name)

For example:

alter database ARSystem set recovery simple;
alter database ARSystem set single_user with Rollback immediate;
alter database ARSystem set READ_COMMITTED_SNAPSHOT ON;
alter database ARSystem set multi_user;
alter database ARSystem set PARAMETERIZATION SIMPLE;

For more information, see Configuring the Microsoft SQL Server.

4

Invalid Views in VUI db Table

Database Upgrade utility failed to run

Involve your DBA for any db related activity and take required backup.

Run the following SQL statements by using a database client at the AR Server Database level:

SQL or Oracle Database Server

Step 1: Execute the following SQL:

select count (*) from vui where vuiType = '0'

Step 2: If the output is greater than 0, execute the following SQL statements to fix all vuiType = '0':

Update vui

set vuiType = 1 where vuiType = 0

5

Oracle database admin user does not have 'All Privileges and Roles'.

DB user is not able to run the upgrade or does not have All Privileges in order to update the db table.

Involve your DBA for any db related activity and take required backup

Run the following SQL statements using a database client at Oracle Database level:

create role ARole_arsys not identified;

Set the following privileges for the role:
grant alter session, create cluster, create
database link, create sequence, create session, create synonym, create
table, create view, create procedure, create trigger, query rewrite to
ARole_arsys;

Grant the role to the Db Admin User:
grant ARole_arsys to <user>;

For more information, see Configuring Oracle Databases.

For more information, see Remedy install fails with "INSUFFICIENT_DATABASE_LOGIN_PRIVILEGES"

6

servgrp_** database tables show incorrect or duplicate Server Connect Name entries under the serverName column

Database was copied from another instance

Server name changed due to a policy or host name change

Involve your DBA for any db related activity and take required backup

Run the following SQL statements by using a database client at the AR Server Database level:

Delete * from servgrp_board where serverName = 'add incorrect server name'
Delete * from servgrp_config where serverName = 'add incorrect server name'
Delete * from servgrp_resources where serverName = 'add incorrect server name'

Re-run the following statement to confirm that the changes were applied:

select * from servgrp_**

7

dbVersion and/or currDbVersion columns does not show correct API Protocol Numbers

Database was copied from another instance.

Database was not rolled back after previous failed upgrade attempt.

Involve your DBA for any db related activity and take required backup

Ask DBA to roll back the Remedy Server Database to a stage before the upgrade.

control db table should not be modified manually

For more information, see BMC Remedy Release Version Strings

8

Upgrade failed with Oracle Database with error "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor "

Incorrect Oracle Service Name configuration

Roll back database and file system (if rollback utility didn't ran through installation).
Review Oracle Database Configuration and use Service Name in all required configuration files. For more information, see Database Configuration Checklist.

9

Upgrade failed immediately with SQL Database Server with error "Failed to validate AR DB user sessions],Detail=[com.microsoft.sqlserver.jdbc.SQLServerException: User does not have permission to perform this action.]"

Insufficient permission for domain account

Check what value is used for SET PROPERTY BMC_SQLSERVER_WINDOWSAUTH_OR_SQLAUTH],
Detail=[WINDOWSAUTH]}
To use Windows Authentication mode, ensure that the user has the correct permissions in both the Windows Server and the SQL Server.

For more information, see Installer fails on MSSQL with Windows Authentication mode: "[Failed to validate AR DB user sessions],Detail=....User does not have permission to perform this action ]"

10

AR Server Services failed to start during upgrade, when using MSSQL Server Always On feature setting

Incorrect configuration

Involve your DBA for any db related activity and take required backup

You need to add AR database (AR System) to the Always On availability group.
You can also add the AlwaysOn instances in the multi subnet.
For each setup, client's JDBC connection URL should have parameter multisubnetfailover=true.
Update ar.cfg/ar.conf file with SQL-Server-Always-On: T

Involve DBA to perform database related activities.

For more information, see Configure MS SQL Server Always On and Always On availability groups.

11 AR Server Upgrade failed with error "arserver.installer.preupgrade.descriptionFailed to run Database Upgrade Utility" Invalid vuiType in VUI table See details on symptom 4.

Troubleshooting database configuration issues

Symptoms

Scope

Resolution

Cause and resolution for error messages

Comments