1 2 Previous Next 20 Replies Latest reply on Jun 29, 2020 5:58 AM by Stefan Hall

    Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?

    Miroslav Horvath
      Share This:

      Hi guys.

      We get often complains from our end users, that they face issue with our Remedy, because our Oracle DB was during the installation configured with Case Sensitivity turned ON. So, when users connect to Remedy, they should always follow the exact loginID. Our Oracle admin guy told us, that turning off the Case Sensitivity setting on the Oracle is easy. But....

       

      My question is, if someone has some experience with this, if it's possible or smth needs to be done before doing it, or it's not recommended or if there is some BMC tool or so, which can check all indexes and so within Remedy for possible duplicates, once turning off the Case Sensitivity on the DB.

      We are running 19.02.001 version and OracleDB 12.1.0.2.0, everything on RHEL.

       

      Any recommendations/experiences are appreciated.

        • 1. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
          Mark Walters

          Start by reading Remedy - Server - Enabling case-insensitive searches with an AR System server 8.1 or 9.x with Oracle which includes the steps necessary to create the functional indices you'll need.

          3 of 3 people found this helpful
          • 3. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
            Stefan Hall

            We use Oracle with Case Insensitive since our first installation 7.6.04.

            It works well if you pay attention to the various BMC bugs and fix them yourself.

             

            Short overview

            • All your previous indexes do not work anymore and have to be converted.
            • You need to reconfigure the AR server, the links of the others give a good overview
            • Depending on whether you switch the indexes to Binary_CI or how we switch to binary_AI, your configuration stable or sensitive.

            The BMC default is the _CI variant and that is ok.

            _AI needs more DB configurations in the AR server, which unfortunately don't end up in the ar.conf. This is where your problems start

            • some versions forgets to use the file/parameters
            • some version,s the default overwrites your settings
            • until today (1902) the global configuration via the centralized configuration doesn't work (very dangerous with installation instructions with a lot of manual rework)
            • 1908 ? we‘re on 1902

             

            I hope you can start with it. If something is missing, just ask, I think I have already seen everything.

             

            Good luck

            Stefan

            5 of 5 people found this helpful
            • 4. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
              Eric Wuensche

              Hi,

               

              we are also using case insensitive settings.

              As mentioned, the "basic" (using Binary_CI) configuration change is relatively easy, but it still seems that BMC is not including case-insensitive oracle DBs in the release test-cycle.

              Known Problems that we faced so far (and are facing regularly):

              - BMC creates indexes on forms with a high amount of columns or several varchar(255) columns, which results in an index creation error by oracle when deploying these forms in an upgrade or hotfix (this could be solved by using different tablespace block sizes which allow bigger key length for an index, but I didn't experiment with this, as I rather see the issue in unnecessary scaled columns and indexes by BMC - Stefan Hall do you have a working solution in place?)

              In the latest upgrade attempt from 19.02 to 19.08 we faced this issue again and BMC has provided an unreleased hotfix after 1 month - so long the system was stuck in upgrade attempt.

              - When using DB interfaces for other users querying AR tables, you should make sure, that these users also use case-insensitive session settings, as otherwise indexes won't be used.

              - Beside this there is still an oracle bug regarding the cursor_sharing=FORCE parameter and index usage:

              Linguistic Indexes Not Used For LIKE :BIND Predicates

               

              Best regards

              Eric

              3 of 3 people found this helpful
              • 5. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                Ariel Manka

                I agree with Stefan. One time we tried this conversion with Remedy 8.1 it created multitude of issues many of which couldn't be resolved. Conversion project was subsequently cancelled and if I remember correctly it was decided to enable FTS on a set of key fields which required case insensitive searches. But this is also a dangerous path especially from the performance point of view since FTS indexing can only run on a single server at a time.

                 

                Ariel

                • 6. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                  LJ LongWing

                  Ariel,

                  Your statement of 'FTS indexing can only run on a single server at a time' is incorrect, you can have as many indexers running as you want....the server group will only use one of them at a time, but multiple can be indexing at the same time.

                  • 7. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                    Ariel Manka

                    Hi LJ,

                     

                    Are you sure about that? Back in the 8.1 era, this was certainly not the case and I recall this being confirmed by BMC at some point when we hit performance issues. Only one server was indexing, and the remaining were dormant waiting to take over in case of the primary indexer outage.

                    Maybe we are just missing each other. Like, what do you mean by "server group will only use one of them"? Do you mean for search requests?

                     

                    Ariel

                    • 8. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                      Mark Walters

                      I would strongly recommend against using FTS to provide case insensitive searches as an alternative to setting up the database to do this.  I've seen cases where this has been done for small fields such as CI names in the CMDB and menu fields.  Remember that FTS will tokenise the data so characters like - and . may result in fields being indexed as multiple words.  The stemming functionality may also cause FTS enabled fields to return different results to a straight database search.

                       

                      I'd put this in the "just because you can do something it doesn't mean you should" category!  By all means use FTS to provide search functionality on large character fields but steer clear of using it as a budget case insensitivity option on Oracle.

                      1 of 1 people found this helpful
                      • 9. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                        LJ LongWing

                        Yes....you can have multiple indexers, but only a single server is used for searching, if the secondary indexer wasn't possible, it wouldn't be ready in the case of the primary indexer being offline...

                         

                        But even that has a caveat...if the search is happening on an indexer, I believe it'll always use itself as the search server, so if in that case the searcher is an indexer, you could in theory (I believe) have multiple active searchers...but only if the searcher and indexer are the same box....but in the scenario of a search coming from a server that's not an indexer, the server group controls which server the search is sent to.

                        • 10. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                          Stefan Hall

                          Hi Eric,

                          i don't have a perfect solution for the index problem with too many columns/characters.

                          An extra tablespace was too expensive for me. I think you're right, nobody needs indexes with so many columns, just to find the last record via an index. The DB is always faster when searching the last records. For me this is a BMC design error.

                           

                          I create these indexes manually with as many fields as possible, so an index exists and the search result comes very close to the complete index. The DB does the rest just as well.

                          —-

                          BMC has solved the Oracle bug you mentioned in one of the last versions with a workaround. The standard is now EXACT.

                           

                          Stefan

                          1 of 1 people found this helpful
                          • 11. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                            Mohammad Rehman

                            Sorry if already been answered.

                             

                            For your points:

                            - When using DB interfaces for other users querying AR tables, you should make sure, that these users also use case-insensitive session settings, as otherwise indexes won't be used.

                             

                            I will suggest to create the db trigger for users directly connect db to query.

                            create or replace TRIGGER set_sort_parameter AFTER LOGON ON schema

                                begin

                                EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP="LINGUISTIC"';

                                EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT="BINARY_CI"';

                                end;

                             

                            You can change set the parameters values as per your environment setup.

                             

                            - Beside this there is still an oracle bug regarding the cursor_sharing=FORCE parameter and index usage:

                            Oracle, Bug 27416997 and Case Insensitive queries failing due to NULL is NOT NULL predicate

                            Remedy 9.x, Oracle Case Insensitivity and SQL with LIKE conditions

                             

                            For your installer issues (which i believe were fixed with 9.1.x onward upgrades, i had no issue, not there yet on 19.02)

                            1 of 1 people found this helpful
                            • 12. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                              Andreas Mitterdorfer
                              ... So, when users connect to Remedy, they should always follow the exact loginID.  ..

                              If the login is the main concern, then case insensitive database will not help. Better to create your logins all upper or lowercase in remedy and then do a conversion at the login prompt (IIRC RSSO provides some out of the box functions for that).

                              • 13. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                                Eric Wuensche

                                Hi Stefan Hall,

                                 

                                either you or I'm not up to date regarding the CURSOR_SHARING settings

                                Current recommendation by BMC:

                                "If you are using case-insensitive database, for the versions up to 9.1.04, set it to EXACT; and for the 9.1.04.002 version and later, set it to FORCE."

                                https://docs.bmc.com/docs/brid1908/tuning-the-oracle-server-879731251.html#TuningtheOracleserver-CursorSharingCursorshar…

                                We had a long discussion with BMC on this issue, as we were facing a full database stop after our upgrade from 8.1 to 18.05.

                                We did the upgrade with a staging environment and during testing with a low amount of test users, there were no issues detected, but as soon as we went live, the database was slowing down to a non-working state. The issue was the EXACT cursor sharing, as it is causing each and every SQL command to be parsed and optimized again (parsing results will drop off the SGA if it is full and not frequently used - 50GB of SGA were not enough to handle the different queries and nobody can tell how much you would have to increase it - additionally it requires a powerfull CPU). When using FORCE, the database was usable quite good, but we faced the issue that some queries were not using indexes but always doing Full Table Scans. At the end BMC was building queries again like in 8.1 and is recommending cursor_sharing=FORCE for case-insensitivity since that.

                                In conclusion:

                                When using EXACT and your DB is powerful enough - it is perfect.

                                If DB is not as powerful, then use FORCE with the drawback of Linguistic Indexes Not Used For LIKE :BIND Predicates (same behaviour as in 8.1 case-insensitive with CURSOR_SHARING=FORCE)

                                There is nothing that BMC could fix from their end at this point - it's up to Oracle.

                                 

                                For the general question (Miroslav Horvath):

                                The login ID issue probably won't be solved by this, as mentioned by Andreas Mitterdorfer. You should check on implementing RSSO for this.

                                Beside this, a case-insensitve setup is a lot more user friendly.

                                The change is rather easy, but a change on a live system will need a downtime for index creation and change of settings.

                                Query performance will probably change by this change and therefore I would recommend a staging system or thorough testing on pre-prod systems.

                                For the index creation I would recommend the options "CREATE INDEX ... PARALLEL ONLINE" as it usually is a lot faster and doesn't block the table.

                                Afterwards you should disable parallelity of the index again: "ALTER INDEX idx_name NOPARALLEL;"

                                1 of 1 people found this helpful
                                • 14. Re: Turning OFF Case Sensitivity on OracleDB for Remedy App - Experiences ?
                                  Rajeev Patel

                                  For Remedy 9.x, whether the Remedy database is Case Sensitive or Case Insensitive, we recommend using cursor_sharing = EXACT.

                                  1 of 1 people found this helpful
                                  1 2 Previous Next