Knowledge Article
Remedy AR System Server - Enabling case-insensitive searches with an AR System server 8.1 and later with Oracle - INCLUDES VIDEO
Remedy - Server - Enabling case-insensitive searches with an AR System server 8.1 and later with Oracle
Remedy AR System Server
AR System
BMC Remedy AR System Server 8.1, 9.x, 21.x +
Enabling case-insensitive searches with an AR System server 8.1, and later with Oracle |
Note: This is a guide provided by Support as a courtesy. There may be better or different ways to set Oracle to case-insensitive and it is up to the DBA Team to perform this Oracle task.
Enabling case-insensitive searches with an AR System server 8.1 and later (including 21.x and later) with Oracle By default, the Oracle database is case-sensitive. This topic discusses how you can enable case-insensitive searching for fixed-length text fields in BMC Remedy AR System 8.1 and later versions using Oracle. Note: Versions 21.x below 21.3.05 will not work properly due to some limitations. If you are on a version 21.x that is below 21.3.05, please upgrade to at least 21.3.05 before converting to Oracle Case-Insensitivity This topic includes: · Pre-requisites for using database indexes for case-insensitive search on Oracle
· Configuring the AR System server for case-insensitive search
· Converting AR System server indexes into functional indexes
· Considerations for case-insensitive searches
Note:
Pre-requisites for using database indexes for case-insensitive search on Oracle- BMC supports using ARSystem in an Oracle case-insensitive environment when searching limited-length text fields. - This topic describes one method of configuring this environment. there may be a different method to set Oracle into case-insensitive. This is a task for the DBA. - There is some work that needs to be performed on the Oracle side in order complete the Case-Insensitivity setup. This involves the creation of Linguistic indexes wherever a character index exists. - As a courtesy, BMC has provided information and an unsupported utility to help customers with the creation of these Linguistic Indexes. - BMC recommends that you make these changes in a non-production environment and monitor the environment for potential problems before deploying this solution in a production environment. - The database administrator should also monitor the usage pattern of newly created functional (linguistic) indexes. - For any issues with the Oracle database after these changes, work directly with Oracle Support as it is outside of the BMC Remedy AR System scope. Version Info: li_20230310.sql: Updated 03-10-2023. Resolved issue with storage clause for a temp table. ARServer now keeps AR Temp tables. So this script deals with them appropriately
li_20210505.sql: Updated 05-10-2021 We have changed the script to align with the ARServer naming convention for Linguistic Indexes. For optimal usage of database indexes for case-insensitive searches, your environment must meet the following configuration requirements: · A minimum client version of Oracle 11.2.
· The Oracle parameter cursor_sharing needs to be set as specified in our documentation depending the exact version level. Please check the following link to get this information: Tuning the Oracle server - Cursor sharing This is set at the database and in the ar.cfg/ar.conf file with the Oracle-Cursor-Sharing parameter.. Configuring the AR System server for case-insensitive search To enable a case-insensitive search for fixed-length text fields in BMC Remedy AR System on Oracle, complete the following steps. (For a successful implementation, follow all of the steps in the order described.) 1. Configure the AR System server as follows:
For versions 8.1, through 9.1.04 a. Add the following configuration to the AR System server configuration file (ar.cfg or ar.conf):
Db-Case-Insensitive: T This causes special session parameters (NLS_SORT and NLS_COMP) to be set to support case-insensitive searches and invalidate existing database indexes.
ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI IMPORTANT NOTE!!! A defect in 9.1 SP2 caused the NLS_SORT setting to change to BINARY_AI, which is Case Insensitive and Accent Insensitive. This will impact performance as the existing functional indexes are configured with BINARY_CI and will not be used. This has been fixed in 9.1 SP2 Patch 1 - this version reverts to using BINARY_CI. b. Add the following configuration to the AR System server configuration file (ar.cfg or ar.conf):
Db-Functional-Index: T By default, the AR System server creates regular indexes when you add an index to a form. To support case-insensitive searches on Oracle databases, for all indexes based on character fields functional indexes are required instead of regular indexes. Setting this parameter will cause the AR System server to create functional indexes for the form when a new index is added to a form. This parameter helps to avoid performance degradation that can result from not using database indexes. For versions 18.05 and above
a. Add the following configuration to the AR System server configuration use the Centralized Configuration to add it it as a global setting:
Db-Case-Insensitive set to T This causes special session parameters (NLS_SORT and NLS_COMP) to be set to support case-insensitive searches and invalidate existing database indexes.
ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI b. Add the following configuration to the AR System server configuration use the Centralized Configuration to add it it as a global setting:
Db-Functional-Index set to T By default, the AR System server creates regular indexes when you add an index to a form. To support case-insensitive searches on Oracle databases, for all indexes based on character fields functional indexes are required instead of regular indexes. Setting this parameter will cause the AR System server to create functional indexes for the form when a new index is added to a form. This parameter helps to avoid performance degradation that can result from not using database indexes. 2. Restart the AR System server to ensure that the configuration changes take effect.
These configuration changes cause the AR System server to configure the Oracle session to support case-insensitive searching. This is applicable to all searches, including those generated by workflow (active links, filters, escalations, and so on). 3. Recreate indexes in the Oracle database as functional indexes using the attachedli_<date>.sqlPL/SQL script. (see “Converting AR System server indexes into functional indexes” below)4. Test Perform functionality and performance testing to ensure proper operation. There should be a behavior difference since upper and lower case characters will now be treated the same whereas in the past they were treated as different.
Converting AR System server indexes into functional indexes While running in case-insensitive mode, Oracle does not use the regular indexes that the AR System server creates. Instead, Oracle uses functional indexes (also called linguistic indexes). You can create a functional index based on a specific language or on a multilingual option. This ensures that the indexed entries are sorted in the correct order for the specific language or language set. To ensure satisfactory performance when using case-insensitive searching in the AR System server, you must create functional indexes in the BMC Remedy AR System database.
BMC provides an unsupported li_<date>.sql PL/SQL script to manage these existing indexes and to convert them from regular to functional indexes.
You can find this unsupported script attached to this document. IMPORTANT NOTE!!!
This script needs to be run as SYSDBA. After connecting to sqlplus as SYSDBA, execute the following command: SQL> alter session set current_schema = <Remedy Schema Owner Name, e.g. ARADMIN> Execute the li_<date>.sql script. The script will prompt you for the schema owner name and tablespace name. These are likely upper case such as ARADMIN and ARSYSTEM. This script will create 4 new scripts based on the current existing indexes.
a) drop_i.sql -- Drops existing Normal indexes
b) drop_ci.sql -- Drops Linguistic indexes if reversion to Case Sensitivity is needed
c) recreate_ni.sql -- Recreates Normal indexes if reversion to Case Sensitivity is needed
d) create_ci.sql -- Creates Linguistic indexes
To run the scripts: 1. The first step is to run the drop_i.sql script. This will drop all the current indexes. Make sure you have a Database backup. These indexes need to be deleted so that the new Linguistic Indexes do not conflict. 2. Next, execute the create_ci.sql script to create new Linguistic indexes for every existing regular index. Spool the output to a text file. This script could take up to 30-60 minutes to complete. For example:
SQL> spool c:\temp\createli.txt SQL> @create_li.sql SQL> spool off 3. At this point you can test to ensure that these indexes exist and work properly. check the spool file for errors from this script. If there are errors, check the Troubleshooting section below Troubleshooting 1. You may encounter "ORA-01450: maximum key length (xxxx) exceeded" errors when creating some Linguistic Indexes. This is because the total key length of the columns in the index exceeds the (xxxx) value above. That value is 6398 for a database block size of 8K. Your best option then may be to create those indexes in a tablespace of larger block size.
a. Create a 16k blocksize tablespace and name it something like ARSYSTEM16
b. copy the lines from the create_ci.sql (or create_li,sql) script that returned the ORA-01450 error into a new file, say or ORA1450.sql c. Edit the Tablespace parameter to reflect the 16k tablespace for each line in the new script For example:
CREATE index ARADMIN.I1129_0_536870928 on ARADMIN.T1129(NLSSORT(C536870928,'NLS_SORT=BINARY_CI')) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ARSYSTEM16;
d. Run the script to create all the longer indexes in the 16k tablespace
e. If desired, execute the following script to move all the indexes into the 16k tablespace (substitute the tablespace name as necessary as well as the schema owner):
SQL> set trimspool on
SQL> set trimout on
SQL> set termout off
SQL> set lines 15000
SQL> set pages 0
SQL> set feedback off
SQL> set echo off
SQL> set verify off
SQL> spool mv_indexes.sql
SQL> SELECT ‘alter index ‘||owner||’.’||index_name||’ rebuild tablespace ARSYSTEM16 online;’
SQL> FROM dba_indexes
SQL> WHERE owner = ‘<ARADMIN or whatever the AR schema owner name is>’;
SQL> spool off
SQL> @mv_indexes.sql
2. You may also encounter "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found"
If this occurs, you will need to identify the data that is in violation, remove or repair the duplicate data and manually rebuild the specific index by re-executing the SQL statement. For example: If the spooled output shows the following
CREATE unique index I253_8_1_LI on T253(NLSSORT(C8,'NLS_SORT=BINARY_CI')) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE ARSYSTEM
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
You can try identifying the duplicate data using the following example SQL statement: SQL> select UPPER(C8),count(*) from T253 group by UPPER(C8) having count(*) > 1 order by 1;
If the table name in the error message is 'T' and some number, you can use the numeric portion of the table name in the following SQL statement to identify the name of the form. SQL> select name from arschema where schemaId=253
3. You can ignore errors referring to Primary Key indexes such as, "ORA-02429: cannot drop index used for enforcement of unique/primary key". Some of the Data-Dictionary tables have PKs using regular indexes. These indexes cannot be dropped since they are defining the PK. When you run the 'create_li.sql' script, it will create a new Linguistic Index and the original 'regular' index will remain. This is normal and does not present a problem.
Considerations for case-insensitive searches
Case-insensitive search for unlimited-length text and attachments fields The AR System server stores unlimited-length text fields and attachment fields as CLOBs in the Oracle database, which does not allow case-insensitive text search on CLOB fields. Use AR System server Full Text Search (FTS) for case-insensitive search capability on unlimited-length text fields and attachment fields. Case-insensitive search for limited-length-text fields
Use the underlying database capability to provide case-insensitive search for limited-length text fields in the AR System server. If a field is marked for FTS indexing, a database search is not performed for that field. Instead, the search request uses the FTS search engine. For the fields that are marked for FTS indexing solely for providing case-insensitive search in the AR System server, set the Index For FTS field property to None. This option disables FTS indexing for the field and routes any searches on the field to the database. Case-insensitive searches for non-ARserver clients If you use another tool to perform searches on the Remedy database, such as SQLPlus, Toad, or SQL Developer, you must set the 2 session parameters from that session to have a Case-Insensitive session. Alternatively, you can create a login trigger that sets these automatically: ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI NOTE: It was found that if AR System was being run in Case Insensitive mode on Oracle 12c or above, certain use cases would not return expected rows. In this example, "Site" drop down menu on the CTM:People form had returned 72 rows (as expected) when Remedy was in Case Sensitive mode, however NO RECORDS were listed when it was ran in Case Insensitive mode. It turned out to be hitting a known Oracle Bug 27416997 (check Metalink Doc ID 2390584.1).You will need Oracle credentials to access this Bug, you may reach out to your DBA). The Bug has been resolved in Oracle 19.1. To work around this issue, set below parameters (as per above Oracle solution doc) alter system set "_optimizer_generate_transitive_pred"=FALSE scope=both See this article for more information |
Attachment(s):