Remedy generated invalid CREATE INDEX syntax

CREATE UNIQUE INDEX IT3884_ci ON T3884 ((nlssort(C1, 'NLS_SORT=NLS_SORT ')))]; SQL state [72000]; error code [12702]; ORA-12702: invalid NLS parameter string used in SQL function
; nested exception is java.sql.SQLException: ORA-12702: invalid NLS parameter string used in SQL function

This problem will be encountered when attempting to create a new form.   If applying any upgrades that attempt to create new forms this could leave tables that are orphaned.   In this case delete the tables at the DB level. Restart the remedy server.   Perform the upgrade after applying the hot fix.  There may be some scenarios where you can alternatively recreate the form using Developer Studio.

To identify any orphaned tables the database consistency checker can be utilized. Look for the following similar entries showing the tables are missing.  They keywords are 'Failed to fetch status XXX table information'

/ Checking dynamic tables(T/H/B), indexes and columns consistency ...                                                                                                                                                                                                   
/ Failed to fetch status history table information for form 'Untitled Regular Form 2__c'<H3894>

/ Failed to fetch status history table information for form 'xx'<H3895>                         
                                                                                              
/ Failed to fetch status history table information for form 'Test'<H3896>         

Below is the URL for the online documentation for running the utility.

https://docs.bmc.com/docs/ars91/en/running-the-database-consistency-checker-609073352.html

The server does not need to be stopped when running this utility however be aware if there isn't sufficient memory with the OS for this process to run, you will encounter an error indicating this. Below is an example of the error message.  In this scenario it will require to shut down the remedy server so that the java process for the dbconsistency checker can acquire the memory needed.


Java HotSpot(TM) 64-Bit Server VM warning: INFO: os::commit_memory(0x000000066aaa0000, 5726666752, 0) failed; error='Cannot allocate memory' (errno=12)
#
# There is insufficient memory for the Java Runtime Environment to continue.
# Native memory allocation (mmap) failed to map 5726666752 bytes for committing reserved memory.
                                                                                                
            
                                                                                                



 

This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers.

Hot fix SR_9104P2_20180416_CU_ALL is available for 9.1.04 patch 002.  This hot fix should be applied before applying any other upgrades.

Contact BMC Support to download the hot fix.

Please note that this hotfix should only be applied to systems running AR System version 9.1.04.002 as the base version.

It should not be used on systems with earlier or later Patches or Service Packs.


The following steps have also been reported to be a successful workaround for most scenarios.  Recommendation is to test before applying any upgrades to confirm.

Create a special database options file (ardb.cfg or ardb.conf) in the same directory where the AR System server configuration file exists.  Add the following entry to the file:

DB-Options:Oracle

{

name:NLS_SORT

value:BINARY_CI

name:NLS_COMP

value:LINGUISTIC

}

 
Restart the AR System server to ensure that the configuration changes take effect.