7 Replies Latest reply on Oct 16, 2017 4:25 AM by Rahul Priyadarshy

    BMC Smart Reporting With Replicated Oracle 12 c Database

    Rahul Priyadarshy
      Share:|

      Hi All,

       

       

      We have BMC SMART REPORTING 9.1.02 with Replicated DB Setup (Oracle 12c).

      Action Request System(R) Server Version 9.1.02.003 201704191747 (Read Only DB is supported for same)

       

       

      STEPS FOLLOWED-

       

       

      1 Install 5 th Ar Server in Remedy ITSM Server Group- On this Server Only Install Ar Server

      2 Create a New schema for Smart Reporting on Remedy production DB server.  RepAdmin Schema and It Will be in Read/Write Mode.

      3 Install BMC Smart Reporting Using Smart Reporting Database Created in Step 2

      4 Perform onboarding, import content pointing to your Admin AR server from server group.

      5 Set Db Replication For Ar System Production -> Create Replicated Reporting Database . Reporting Database  will be in Read Mode.

      6 Remove  5 th Ar Server from Server Group and Point  to Replicated Reporting Database. Start Ar Server Service.

      7 Change Smart Reporting Connection String from Primary AR Server to 5 th Ar Server.

       

       

      Now We are getting error when we are doing STEP 6 getting below error.

       

       

      INFO - Fri Oct 13 14:15:07.218 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

      INFO -

      INFO - Fri Oct 13 14:15:18.678 2017 <SQL > <TID: 0000000358> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Fri Oct 13 2017 14:15:18.6750 */ Error deleting ft_pending record by servername and sequencenum

      INFO - org.eclipse.persistence.exceptions.DatabaseException:

      INFO - Internal Exception: java.sql.SQLException: ORA-16000: database or pluggable database open for read-only access

      INFO -

      INFO - Error Code: 16000

       

      Experts Your Input Please.

       

      Regards

      RP

        • 1. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
          Mohammad Rehman

          I am sure experts will explain and provide you better solution but here is my understanding and 2 cents.

          I think there is one technical flaw here which is smart reporting database schema on the same db server (step 2) and connecting to replicated smart reporting schema not production.

          Either you should create a separate db instance not schema for smart reporting and keep it on the same db server, it means your smart reporting db will be read/write for smart reporting repository. OR connect the smart reporting to production db and for reporting connect to read only replicated db ar server then it should work.

          • 2. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
            Rahul Priyadarshy

            We have raised a Case with BMC. Above steps are confirmed with BMC before proceeding. :-(

             

            Regards

            RP

            • 3. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
              Nitin Chaturvedi

              Hi Rahul,

                

                  Following document talks about some features not supported with Read Only DB in section "Using read only database".

               

               

                 Setting database options - BMC Remedy Action Request System 9.1

               

                 You may want to check if in your case this is being done or not.

               

              Thanks,

              Nitin

              1 of 1 people found this helpful
              • 4. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
                Rahul Priyadarshy

                Thanks Nitin for Input.

                 

                Services are Up and Running. I am able to Access This Smart Reporting ARS. But in Arerror Log i see a periodic entry of below error messages.

                Mon Oct 16 08:19:48.213 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Mon Oct 16 08:19:58.846 2017 <SQL > <TID: 0000000360> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:19:58.8420 */ Error deleting ft_pending record by servername and sequencenum

                Mon Oct 16 08:19:58.865 2017 <SQL > <TID: 0000000359> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:19:58.8600 */ Error creating ft_schedule record

                Mon Oct 16 08:19:58.881 2017 FTS action: INDEX_FORM_UPDATED_SINCE failed Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly.

                Mon Oct 16 08:19:58.938 2017 <SQL > <TID: 0000000356> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:19:58.9330 */ Error creating ft_schedule record

                Mon Oct 16 08:19:58.952 2017 FTS action: INDEX_FORM_UPDATED_SINCE failed Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly.

                Mon Oct 16 08:19:59.000 2017 <SQL > <TID: 0000000356> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:19:58.9950 */ Error creating ft_schedule record

                Mon Oct 16 08:19:59.016 2017 FTS action: INDEX_FORM_UPDATED_SINCE failed Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly.

                Mon Oct 16 08:19:59.393 2017 <SQL > <TID: 0000000359> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:19:59.3880 */ Error creating ft_schedule record

                Mon Oct 16 08:19:59.408 2017 FTS action: INDEX_FORM_UPDATED_SINCE failed Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly.

                Mon Oct 16 08:20:08.201 2017 Attachment fieldvalue has bad location type.ERROR (658): Failed to read full text documents

                Mon Oct 16 08:20:09.984 2017 <SQL > <TID: 0000000358> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:20:09.9820 */ Error creating ft_schedule record

                Mon Oct 16 08:20:09.986 2017 FTS action: INDEX_FORM_UPDATED_SINCE failed Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly.

                Mon Oct 16 08:20:13.630 2017 Attachment fieldvalue has bad location type.ERROR (658): Failed to read full text documents

                Mon Oct 16 08:20:17.626 2017 Attachment fieldvalue has bad location type.ERROR (658): Failed to read full text documents

                Mon Oct 16 08:20:24.499 2017 Attachment fieldvalue has bad location type.ERROR (658): Failed to read full text documents

                Mon Oct 16 08:20:28.837 2017 <SQL > <TID: 0000000356> <RPC ID: 0000000000> <Queue: FTS       > <Client-RPC: 390602   > <USER: FTS_INDEXER                                  > <Overlay-Group: 1         > /* Mon Oct 16 2017 08:20:28.8350 */ Error creating ft_schedule record

                Mon Oct 16 08:20:28.838 2017 FTS action: INDEX_FORM_UPDATED_SINCE failed Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction rolled back because transaction was set to RollbackOnly.

                Mon Oct 16 08:20:48.232 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Mon Oct 16 08:20:48.237 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Mon Oct 16 08:20:48.241 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Mon Oct 16 08:20:48.246 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Mon Oct 16 08:20:48.251 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Mon Oct 16 08:20:48.256 2017 Error in failover monitor ERROR (552): The SQL database operation failed.; ORA-16000: database or pluggable database open for read-only access

                 

                 

                Regards

                RP

                • 5. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
                  Nitin Chaturvedi

                  All the log entries are coming from FTS Queue and the document clearly mentioned that FTS needs to be disabled. In case FTS is disabled and you are still getting these log entries then BMC Support may have to look into it.

                   

                  Thanks,

                  Nitin

                  • 6. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
                    Rahul Priyadarshy

                    From logs i also guessed the same  .....:-) Now here is the classic Catch 22 situation.

                    I tried disabling the FTS flag from Ar System Admin Console. I am getting error related with read only DB- I tried same from GUI.

                     

                    Tried running Driver Program- /opt/bmc/ARSystem/api/src/driver 

                     

                    getting error related with some missining library file.

                     

                     

                    [root@ driver]# ./driver

                    ./driver: error while loading shared libraries: libicuucbmc_lx64.so.32: cannot open shared object file: No such file or directory

                    [root@ driver]#

                     

                     

                    Seems library is missing since below command is not giving me anything.

                     

                     

                    rpm -qa|grep libicuucbmc_lx64

                     

                     

                    Regards

                    RP

                    • 7. Re: BMC Smart Reporting With Replicated Oracle 12 c Database
                      Rahul Priyadarshy

                      I am bit cautious here as we have a pretty close go live date. So No heroics. Awaiting BMC response . :-)

                       

                      Also for driver program to run - Seems we need this 32 bit library- libicuucbmc_lx64.so.32

                       

                      I

                       

                      Regards

                      RP