7 Replies Latest reply: Jun 23, 2013 8:39 PM by Thanos A RSS

    ORA-12154 Connection error from HFM to Oracle Database

    Otein
      Hi,

      I am trying to configure Hyperion HFM but can write to HFM database.

      The implementation architecture:

      Hyperion 11.1.2.2 (with all the requiered patches for HFM, FDM, Shared Services, Workspace and Oracle Application Development)

      Server 1:
      Windows Server 2008 x64
      Installed products: Foundation (EPMA, CalcManager), BI, HFM web components and ADM driver
      Configured products: Foundation(EPMA, CalcManager), BI.
      Database Client: 11gR2 x64

      Server 2:
      Windows Server 2008 x64
      Installed products: HFM, FDQM
      Configured Products: FDQM, HFM
      Database Client: 11gR2 x32, 11gR2 x64 (x32 version installed first)

      Server 3:
      Database: Oracle 11.2.0.2

      All the products from server 1 are working fine, FDQM (server 2) is also working fine, but, when I try to do any action related with HFM database the system fails.

      I have tested the connection is these scenarios:

      1. SQLdeveloper: successfull!, I can create tables, views, etc. Double checking the user privileges it has all the required.

      2. tnsping: successfull!

      3. HFMApplicationCopy utility: Successfull using UDL file and writing the connection parameters.

      4. EPM System Configurator: the configurator successfully validates the database connection information, but does not create the tables on the database. No errors in the configtool log.

      5. EPM Diagnostic Tool: fails with this error message:


      ------------STARTING VALIDATION SCRIPTS----------
      LOGGING IN HFM....
      CREATING APPLICATION....
      ERROR: Unable to CreateApplicationCAS
      Number (dec) : -2147215936
      Number (hex) : &H800415C0
      Description  : <?xml version="1.0"?>
      +<EStr><Ref>{DC34A1FD-EE02-4BA6-86C6-6AEB8EF5E5A3}</Ref><AppName/><User/><DBUpdate>1</DBUpdate><ESec><Num>-2147467259</Num><Type>1</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>HfmADOConnection.cpp</File><Line>511</Line><Ver>11.1.2.2.300.3774</Ver><DStr>ORA-12154: TNS:could not resolve the connect identifier specified</DStr></ESec><ESec><Num>-2147215616</Num><Type>1</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxSQLConnectionPool.cpp</File><Line>585</Line><Ver>11.1.2.2.300.3774</Ver></ESec><ESec><Num>-2147215936</Num><Type>0</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxServerImpl.cpp</File><Line>8792</Line><Ver>11.1.2.2.300.3774</Ver></ESec><ESec><Num>-2147215936</Num><Type>0</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxServer.cpp</File><Line>90</Line><Ver>11.1.2.2.300.3774</Ver></ESec><ESec><Num>-2147215936</Num><Type>1</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxClient.cpp</File><Line>1356</Line><Ver>11.1.2.2.300.3774</Ver><PSec><Param><server_name></Param></PSec></ESec><ESec><Num>-2147215936</Num><Type>1</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxClient.cpp</File><Line>936</Line><Ver>11.1.2.2.300.3774</Ver></ESec><ESec><Num>-2147215936</Num><Type>0</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxClient.cpp</File><Line>4096</Line><Ver>11.1.2.2.300.3774</Ver></ESec></EStr>+

      Source       : Hyperion.HFMErrorHandler.1
      ERROR: while Application created

      7. HFM Classic application creation: fails with the following error:

      Error*11*<user_name+>*10/19/2012 08:30:52*CHsxServer.cpp*Line 90*<?xml version="1.0"?>+

      +<EStr><Ref>{DC34A1FD-EE02-4BA6-86C6-6AEB8EF5E5A3}</Ref><AppName/><User/><DBUpdate>1</DBUpdate><ESec><Num>-2147467259</Num><Type>1</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>HfmADOConnection.cpp</File><Line>511</Line><Ver>11.1.2.2.300.3774</Ver><DStr>ORA-12154: TNS:could not resolve the connect identifier specified</DStr></ESec><ESec><Num>-2147215616</Num><Type>1</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxSQLConnectionPool.cpp</File><Line>585</Line><Ver>11.1.2.2.300.3774</Ver></ESec><ESec><Num>-2147215936</Num><Type>0</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxServerImpl.cpp</File><Line>8792</Line><Ver>11.1.2.2.300.3774</Ver></ESec><ESec><Num>-2147215936</Num><Type>0</Type><DTime>10/19/2012 8:30:52 AM</DTime><Svr><server_name></Svr><File>CHsxServer.cpp</File><Line>90</Line><Ver>11.1.2.2.300.3774</Ver></ESec></EStr>+

      8. EPMA Application deployment: fails with same message.

      Please help me with some insights on this problem, I have tried everything but nothing works.

      Regards

      Edited by: Otein on 19-oct-2012 14:04
        • 1. Re: ORA-12154 Connection error from HFM to Oracle Database
          John A Booth
          Hi Otein,

          Sounds like your 64-bit tnsnames.ora file may not match the setup of your Oracle server. Have you had your DBA review the file? Are you using the sid or service in the file?

          You may also want to review the following:
          Oracle KB: ID 114085.1
          http://www.dba-oracle.com/t_oracle_sid_instance_name_service_name.htm
          http://ora-12154.ora-code.com/

          Regards,

          John A. Booth
          http://www.metavero.com
          • 2. Re: ORA-12154 Connection error from HFM to Oracle Database
            Otein
            Hi,

            I Have solved one of my problems, the one that keep HFM from connecting to the Oracle database.

            I just change the TNSNAMES.ORA, like this:

            Initial tnsnames.ora
            PRUEBA.WORLD=
            +(DESCRIPTION_LIST =+
            +(DESCRIPTION =+
            +(LOAD_BALANACE = ON)+
            +(FAILOVER = ON)+
            +(ADDRESS_LIST =+
            +(ADDRESS = (PROTOCOL = TCP)(HOST = <server_name>)(PORT = <port>))+
            +)+
            +(CONNECT_DATA =+
            +(SERVICE_NAME = <service_name>)+
            +)+
            +)+
            +)+

            Modified tnsnames.ora
            PRUEBA.WORLD=
            +(DESCRIPTION =+
            +(LOAD_BALANACE = ON)+
            +(FAILOVER = ON)+
            +(ADDRESS_LIST =+
            +(ADDRESS = (PROTOCOL = TCP)(HOST = <server_name>)(PORT = <port>))+
            +)+
            +(CONNECT_DATA =+
            +(SERVICE_NAME = <service_name>)+
            +)+
            +)+

            I Just delete the line "+(DESCRIPTION_LIST =+" and its corresponding closing parenthesis, I did this cause in the configuration utility log I saw this line:

            +TNS parsing: Entry: DESCRIPTION_LIST [[Address: Protocol:(TCP) Host:(<server_name>) Port:(1521) SID:(<service_name>)]]+

            So, if the applications were trying to connect to connection descriptor DESCRIPTION_LIST, the driver could not recognize DESCRIPTION_LIST as a valid one.

            There is a lot going on behind the scenes when you work with Oracle Database as the repository, maybe there is some other way to address this issue, but it worked for me, hope it can help you too.
            • 3. Re: ORA-12154 Connection error from HFM to Oracle Database
              Otein
              I still have one issue,

              The Hyperion configuration utility does not create the tables in the Oracle database, this is the scenario:

              * Configuration utility can connect to the listener, it validates the Server Name, Port, Service Name, User and password successfuly.
              * When the task is executed it finished successfully, but the tables are not created in the database.
              * No error on configtool.log.

              Please suggest on how to solve this issue, or at least how to have a better diagnostic.

              Regards
              • 4. Re: ORA-12154 Connection error from HFM to Oracle Database
                Cjx-Oracle
                Hi Otein,


                Please check the below link and refer to page 38 and re-check the privileges.

                http://docs.oracle.com/cd/E17236_01/epm.1112/epm_install_start_here_1112200.pdf


                Hope this helps,


                Thank you,
                Charles Babu J
                • 5. Re: ORA-12154 Connection error from HFM to Oracle Database
                  Otein
                  Hi,

                  Thanks for your reply, I already checked the privileges and they are just like the document says it should be.

                  I can connect to the schema with the user and create tables, views, etc., also checking the other users I have for the other products (Foundation, epma, calculation manager, bi) they all have the same privileges, but the only user that fails it the one used by HFM.

                  I want to remark the problem is just with the Configuration Utility. Validation tool, EPMA and HFM classic can wirte to the database, once I have created the base (initial) tables "manually".

                  Does anyone know wich connection method is used by the configuration utility (jdbc, OLEDB, ODP, OCI...) ?

                  Any help is welcome.

                  Thanks again
                  • 6. Re: ORA-12154 Connection error from HFM to Oracle Database
                    Alvaro Soares

                    Where did you changed the TNSNAMES.ORA ? On the client or server side ?

                     

                    Thanks

                    • 7. Re: ORA-12154 Connection error from HFM to Oracle Database
                      Thanos A

                      Hi there,

                       

                      Have you checked the number of operating system user processes that can simultaneously connect to Oracle database?

                       

                      Regards,

                       

                      Thanos