Troubleshooting database configuration issues

1

Review configuration checks for the SQL Server database.

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

1. 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.
   
2. 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.
   
3. 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:

1. 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.
   
   
2. If you are using Oracle 12c Database, see the section "Considerations for an Oracle 12C database" in the topic Configuring-Oracle-databases.
   
   

3. 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

4. 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)

4

Review Current Upgrade Status

1. 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.
       
       
2. Upgrade failed and Rollback failed :

1. 
   
   • 
     
     • 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

7

Create a BMC Support Case

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

1. 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.

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

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

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

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:

5

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

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

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.