Clarity

Expand all | Collapse all

Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

  • 1.  Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 09:43 AM

    Hi everyone,

     

    has anyone ever faced this issue?

     

    I have just installed 14.1 on Win2k8, Tomcat with Oracle on a separate server.

     

    In CSA I have entered the correct Home Directory and then all correct settings under Internal Connection Niku (hostname, port, service name, login name and password).  However when I try to save my settings I keep getting Status = Unavailable...

     

    Looking into nsa-ca.log I found the following:

     

    WARN  2015-02-10 15:28:04,735 [Config Monitor] config.TenantConfigurationManager (none:none:none:none) Found invalid database connection for local tenant clarity

    ERROR 2015-02-10 15:29:22,439 [http-bio-8090-exec-8] persistence.PersistenceController (clarity:sa:a4S17jjH6iGvnfKmJC8Mpn7Cyv9IcWOXgq7lW5uof+o0:nsa.properties.database.save) Exception due to DB Connection failure

    WARN  2015-02-10 15:29:22,439 [http-bio-8090-exec-8] niku.nsa (clarity:sa:a4S17jjH6iGvnfKmJC8Mpn7Cyv9IcWOXgq7lW5uof+o0:nsa.properties.database.save) Unable to persist configuration properties to database. Likely cause is database properties are not yet set or misconfigured.

    ERROR 2015-02-10 15:29:34,669 [http-bio-8090-exec-8] handlers.SaveDWHDatabaseLinkHandler (clarity:sa:a4S17jjH6iGvnfKmJC8Mpn7Cyv9IcWOXgq7lW5uof+o0:nsa.properties.database.save) Connection with Data Warehouse database is not established

    java.sql.SQLNonTransientConnectionException: [CA Clarity][Oracle JDBC Driver]Error establishing socket to host and port: localhost:1521. Reason: Connection refused: connect

      at com.ca.clarity.jdbc.oraclebase.ddcr.b(Unknown Source)

    ...

    ...

     

     

    Looking in Properties.xml I found extra hidden parameters under <dwhDatabaseServer> tag... which are all defaults that do not apply to my setup at all...

     

    Furthermore the hostname and port are NOT the ones I entered manually in CSA !!! how is it supposed to connect if its not using the correct settings I entered???

     

     

    many thanks in advance,

     

    Olivier



  • 2.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 10:44 AM

    What's the value for

     

    Are the values in the databaseServer element correct?



  • 3.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 11:04 AM

    Hello Andrew,

     

    SingleTenantMode is Enabled by default and I didnt modify it.  Should I modify it?

     

    the databaseServer element should be correct according to local DBA here at the customer.  We have tried to modify the tnsnames.ora file to try with SID and also SERVICE_NAME.  We have to tried to use Net Configuration Assistant to add, reconfigure and delete Service Name and Listener where CSA resides.  tnsping and sqlplus work fine...



  • 4.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 11:36 AM

    I think that you want to leave it as it is.



  • 5.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)
    Best Answer

    Broadcom Employee
    Posted Feb 10, 2015 11:50 AM

    Hi Olivier,

     

    I ran in similar situation but it was during upgrade and it was failing, try the below workaround and see

     

    Extract the install.jar in my temp directory

    - Take the library "c-oracle.jar" inside ...temp\checkinstall\tools\lib and

    copy it inside the folder ..temp\tools\lib replacing the existing one.

    - Take the file "package.jar" contained in ..temp\install-packages\14.1.0

    and unbuild (renamed it in package.zip and extracted) it in my temp2

    directory.

    - Take the library "c-oracle.jar" inside ...temp\checkinstall\tools\lib and

    copy it inside the folder ..temp2\lib replacing the existing one.

    - rebuild the package.jar modified and run the install

     

    Do let me know how it goes.

     

    Regards

    Suman Pramanik



  • 6.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 12:50 PM

    You are my hero Suman!!!

     

    your workaround did work! this is definitely a bug!  I have spent more than 6 hours investigating and hassling DBAs and SysAdmins to experiment all sorts of possible parameter combinations... until your magical workaround got to me per email   this package.jar file from ..temp\install-packages\14.1.0 does include the WRONG/buggy c-oracle.jar file...

     

    many many thanks!



  • 7.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Broadcom Employee
    Posted Feb 11, 2015 02:53 AM

    Glad to hear Olivier, I do believe that two different jar files are breaking the install/upgrade. I will continue to find more information and will update soon.

     

    Regards

    Suman Pramanik



  • 8.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 12:23 PM

    The letters 'dwh' refer to the data warehouse schema that isn't in use yet (not for this version).  I believe what you're seeing is just the same kind of issue that occurs when Clarity is unable to get a valid 'tenant' database configuration (for the 'niku' database id), at which point it is unsure whether it should be single or multi tenant, and whether it should or shouldn't be working with the dwh configuration, so it checks anyway and then complains about it also - but this is a knock-on from the original fault that needs to be corrected.

     

    I've seen similar error reports in logs on other systems prior to the primary 'niku' database configuration being correct.

     

    You didn't mention the Service ID parameter, but you did mention Service Name.  Actually the Service Name isn't used for establishing a database connection; this is used as an 'alias' for the purposes of the Reports Server to connect to the database.  To make a connection to Oracle you instead need a valid Service ID parameter (set to the Oracle SID).

     

    Also don't worry about the Home Directory parameter, this doesn't apply to your setup of having a db server separate from your Clarity server either.



  • 9.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 12:55 PM

    thanks for your explanations regarding data warehouse and service ID+ service Name Nick

     

    in the end I found the magical parameter combination and even then the Status would still show as Unavailable! and no more errors showing in nsa-ca.log!  this is when I started to suspect there could be a bug... The workaround from Suman above did the trick!!    the c-oracle.jar file is wrong...



  • 10.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 10, 2015 01:54 PM

    Glad to hear you've gotten a way around the problem for now.

     

    I know we have environments both new-installs and upgrades that haven't required this though, so although I believe your problem to have been genuine I also believe we stand to learn more about the causes behind this, so we can get it fixed or publish the parts of a configuration that can be identified as a known and root cause.

     

    Would it be possible to share with me (via email if you prefer) some details?  I'd like to consider the following I think:

     

    1. Precise Oracle version, edition (e.g. standard, enterprise, etc.), and patch levels.

    2. OS vendor for the Oracle database server.

    3. Oracle hostname (or at least the make-up of it, such as the length of the hostname in characters and any non-alphabet characters used like periods or hyphens or similar, so as to keep your details confidential), username, service ID, and length of the password.

    4. I presume it is not an Oracle RAC environment, given you are using the CSA fields and not a JDBC URI string of your own, but if you could confirm if it is or isn't RAC please.

    5. Health Check database reported item values (for nls_date, nls_sort, nls_comp, database characterset)

     

    This should help give me an angle to check for possible contributing factors (or help identify an existing reported defect to corroborate the same findings).  Thanks a lot if you are able to do this.



  • 11.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Feb 11, 2015 05:03 AM

    1. Precise Oracle version, edition (e.g. standard, enterprise, etc.), and patch levels.

    11.2.0.4.0 Enterprise

     

    2. OS vendor for the Oracle database server.

    Linux SUSE 11

     

    3. Oracle hostname (or at least the make-up of it, such as the length of the hostname in characters and any non-alphabet characters used like periods or hyphens or similar, so as to keep your details confidential), username, service ID, and length of the password.

    hostname: YYXXXXXXprd12.fpprod.corp

    username: niku

    serviceID: CLARITY

    service Name: XXXDEV1

     

    4. I presume it is not an Oracle RAC environment, given you are using the CSA fields and not a JDBC URI string of your own, but if you could confirm if it is or isn't RAC please.

    No RAC normal storage

    5. Health Check database reported item values (for nls_date, nls_sort, nls_comp, database characterset)

    here is a copy paste from an export to excel from the portlet nsql query you added in one of your posts regarding nls_date_format setting:

     

    parameter value level

    NLS_CHARACTERSET AL32UTF8

    NLS_COMP BINARY   database NLS_COMP BINARY session NLS_COMP BINARY instance

    NLS_DATE_FORMAT   database NLS_DATE_FORMAT DD-MON-RR session NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS instance

    NLS_LANGUAGE ENGLISH database NLS_LANGUAGE AMERICAN session NLS_LANGUAGE ENGLISH instance

    NLS_RDBMS_VERSION 11.2.0.4.0

    NLS_SORT BINARY database NLS_SORT XWEST_EUROPEAN session NLS_SORT   instance

    NLS_TERRITORY UNITED KINGDOM database NLS_TERRITORY UNITED KINGDOM session NLS_TERRITORY AMERICA instance

     

     

    *** there are 2 potential conflicts which CSA is complaining about...

    Date Format is only OK at Instance level since they created a trigger for it

    Sort is OK at DB level but not at session level...

     

     

    Can I continue on with the installation in that case? it is only a DEV/Test environment... and the customer does NOT want to create a dedicated DB instance for Clarity as they claim it is not a critical application here and only maximum 30 concurrent users will ever use the system (100 in total). I told them I would not be flexible for PROD environment but maybe we could give it a try for DEV... what do you reckon?

     

     

    many thanks!



  • 12.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Jan 09, 2019 06:03 AM

    Hello Nick,

     

    I've got a new case at one of my clients. Oracle DBA decided to use exclusively "Service Name" in their connection strings as a new policy : it is not possible anymore to use SID on their network. It seems that Clarity does not support the service name with its own JDBC Connection String... 

     

    The URL edition in CSA does not seem to work with the property ServiceName.

    jdbc:clarity:oracle://server:1521;ServiceName=serviceTNS;BatchPerformanceWorkaround=true;

     

    Do you (or does anybody) know any workaround ?

    Seems kind of a big restriction to me

     

    Thx,

    David

     

    PS : Fresh install on a CA PPM 15.5.1, Windows Server 2016 with Oracle 12.x.



  • 13.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Broadcom Employee
    Posted Jan 09, 2019 06:15 AM

    What version of PPM you are using and Service name does work. We need to take a look on this 



  • 14.  Re: Connection settings ignored by CSA... always localhost:1521... (14.1 on Oracle)

    Posted Jan 21, 2019 10:05 AM

    Hello Suman,

     

    You can ignore my last reply : it was due to a parameter on the db side regarding jdbc connection. The server rejected all jdbc connections in the first place.

     

    Thx anyway !

    David