Clarity

Expand all | Collapse all

Load Data Warehouse - error ORA-12541 No listener

Jump to Best Answer
  • 1.  Load Data Warehouse - error ORA-12541 No listener

    Posted 06-20-2017 04:55 AM

    Hi all,

     

    we have got a problem with execution of Load Data Warehouse job. Below is the content of bg-dwh.log file.

     

    I checked the listener and it's started properly, I'm also able to connect to the DWH database with SQL Developer.

     

    Please have you got any ideas?

     

    Thank you and Regards,

    Martin

     

    INFO  2017-06-20 10:33:38,252 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event Job Parameters
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event SRC_DATABASE_URL: jdbc:clarity:oracle://192.168.100.110:1521;SID=ppm;BatchPerformanceWorkaround=true;InsensitiveResultSetBufferSize=0;ServerType=dedicated;supportLinks=true
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event SRC_DATABASE_CLASS: com.ca.clarity.jdbc.oracle.OracleDriver
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event SRC_DATABASE_USER: ppm
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event TGT_DATABASE_URL: jdbc:clarity:oracle://192.168.100.110:1521;SID=ppm;BatchPerformanceWorkaround=true;InsensitiveResultSetBufferSize=0;ServerType=dedicated;supportLinks=true
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event TGT_DATABASE_CLASS: com.ca.clarity.jdbc.oracle.OracleDriver
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event TGT_DATABASE_USER: ppm_dwh
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event CLARITY_DB_TYPE: oracle
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event DWH_DB_TYPE: oracle
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event SRC_DBNAME: ppm
    INFO  2017-06-20 10:33:38,253 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event DB_LINK: PPMDBLINK
    INFO  2017-06-20 10:33:38,505 [Thread-13] dwh.event dwh_etl_master - Start of job execution
    INFO  2017-06-20 10:33:45,294 [dwh_jb_pre_execution UUID: 0df20849-3fd7-42f8-ba39-c2b402c62970] dwh.event audit table name -  & DWH_CFG_AUDIT

    ERROR 2017-06-20 10:33:46,007 [ETL_JOB_META_LOAD UUID: 7127ab10-98ac-43d8-929d-2456453b0d0a] dwh.event dwh_db_check_ - An error occurred executing this job entry :
    Couldn't execute SQL: UPDATE DWH_META_COLUMNS SET COL_MODIFIED = 1 WHERE (DWH_TABLE,DWH_COLUMN) IN
         (
         SELECT
        META_TGT_TAB_NAME,
        META_TGT_COL_NAME
         FROM
              (SELECT
                   UPPER(A.DWH_TABLE_NAME) META_TGT_TAB_NAME,
                   UPPER(A.DWH_COLUMN_NAME) META_TGT_COL_NAME,
                   UPPER(A.ATTR_DATA_TYPE) META_TGT_DATA_TYPE,
                   A.ATTR_DATA_SIZE META_TGT_DATA_SIZE,
                   B.DWH_TABLE,
                   B.DWH_COLUMN,
                   B.ATTRIBUTE_DATA_TYPE DWH_DATATYPE,
                   SUBSTR(B.ATTRIBUTE_DATA_TYPE, INSTR(B.ATTRIBUTE_DATA_TYPE,'(', 1)+1,INSTR(B.ATTRIBUTE_DATA_TYPE,')',1)-(INSTR(B.ATTRIBUTE_DATA_TYPE,'(', 1)+1)) DWH_DATA_SIZE
              FROM
                   DWH_META_COLUMNS@PPMDBLINK A INNER JOIN DWH_META_COLUMNS B
              ON
                   UPPER(A.DWH_TABLE_NAME) = B.DWH_TABLE AND UPPER(A.DWH_COLUMN_NAME) = B.DWH_COLUMN
              WHERE
                   UPPER(A.ATTR_DATA_TYPE) LIKE '%VARCHAR%' AND UPPER(A.ATTR_TYPE) <> 'CLOB')
      WHERE META_TGT_DATA_SIZE<>DWH_DATA_SIZE)

    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12541: TNS:no listener


    INFO  2017-06-20 10:33:46,012 [Thread-13] dwh.event dwh_etl_master - Job execution finished
    ERROR 2017-06-20 10:33:46,040 [Dispatch MSn Load Data Warehouse : bg (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
    2017/06/20 10:33:46 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
    2017/06/20 10:33:46 - dwh_db_check_ - Couldn't execute SQL: UPDATE DWH_META_COLUMNS SET COL_MODIFIED = 1 WHERE (DWH_TABLE,DWH_COLUMN) IN
    2017/06/20 10:33:46 - dwh_db_check_ -      (
    2017/06/20 10:33:46 - dwh_db_check_ -      SELECT
    2017/06/20 10:33:46 - dwh_db_check_ -     META_TGT_TAB_NAME,
    2017/06/20 10:33:46 - dwh_db_check_ -     META_TGT_COL_NAME
    2017/06/20 10:33:46 - dwh_db_check_ -      FROM
    2017/06/20 10:33:46 - dwh_db_check_ -           (SELECT
    2017/06/20 10:33:46 - dwh_db_check_ -                UPPER(A.DWH_TABLE_NAME) META_TGT_TAB_NAME,
    2017/06/20 10:33:46 - dwh_db_check_ -                UPPER(A.DWH_COLUMN_NAME) META_TGT_COL_NAME,
    2017/06/20 10:33:46 - dwh_db_check_ -                UPPER(A.ATTR_DATA_TYPE) META_TGT_DATA_TYPE,
    2017/06/20 10:33:46 - dwh_db_check_ -                A.ATTR_DATA_SIZE META_TGT_DATA_SIZE,
    2017/06/20 10:33:46 - dwh_db_check_ -                B.DWH_TABLE,
    2017/06/20 10:33:46 - dwh_db_check_ -                B.DWH_COLUMN,
    2017/06/20 10:33:46 - dwh_db_check_ -                B.ATTRIBUTE_DATA_TYPE DWH_DATATYPE,
    2017/06/20 10:33:46 - dwh_db_check_ -                SUBSTR(B.ATTRIBUTE_DATA_TYPE, INSTR(B.ATTRIBUTE_DATA_TYPE,'(', 1)+1,INSTR(B.ATTRIBUTE_DATA_TYPE,')',1)-(INSTR(B.ATTRIBUTE_DATA_TYPE,'(', 1)+1)) DWH_DATA_SIZE
    2017/06/20 10:33:46 - dwh_db_check_ -           FROM
    2017/06/20 10:33:46 - dwh_db_check_ -                DWH_META_COLUMNS@PPMDBLINK A INNER JOIN DWH_META_COLUMNS B
    2017/06/20 10:33:46 - dwh_db_check_ -           ON
    2017/06/20 10:33:46 - dwh_db_check_ -                UPPER(A.DWH_TABLE_NAME) = B.DWH_TABLE AND UPPER(A.DWH_COLUMN_NAME) = B.DWH_COLUMN
    2017/06/20 10:33:46 - dwh_db_check_ -           WHERE
    2017/06/20 10:33:46 - dwh_db_check_ -                UPPER(A.ATTR_DATA_TYPE) LIKE '%VARCHAR%' AND UPPER(A.ATTR_TYPE) <> 'CLOB')
    2017/06/20 10:33:46 - dwh_db_check_ -   WHERE META_TGT_DATA_SIZE<>DWH_DATA_SIZE)
    2017/06/20 10:33:46 - dwh_db_check_ -
    2017/06/20 10:33:46 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-12541: TNS:no listener


  • 2.  Re: Load Data Warehouse - error ORA-12541 No listener

    Broadcom Employee
    Posted 06-20-2017 06:02 AM

    Hi Martin,

     

    Can you test the DB link using the documentation below and if this fails, can you ask your DBA to create the DB link, update the properties.xml, restart the app and then run the job

     

    SELECT count(1) FROM SRM_RESOURCES@PPMDBLINK

     

    https://docops.ca.com/ca-ppm/14-2/en/installing-and-upgrading/set-up-the-data-warehouse#SetUptheDataWarehouse-SetUpaData… 

     

    Regards

    Suman Pramanik 



  • 3.  Re: Load Data Warehouse - error ORA-12541 No listener

    Posted 06-20-2017 05:26 AM

    I would check DWH DB setting within properties.xml file. Especially if there is service name and service ID properly set up. I would recommend to test your connectivity using SQL plus rather than SQL developer if you have oracle client installed.



  • 4.  Re: Load Data Warehouse - error ORA-12541 No listener

    Posted 06-20-2017 05:56 AM

    Please check the properties.xml as suggested by Lukas_Jezek.

    Also,

    Ensure that the supplied destination address matches one of the addresses used by
    the listener - compare the TNSNAMES.ORA entry with the appropriate LISTENER.ORA file (or
    TNSNAV.ORA if the connection is to go by way of an Interchange).

    Note:
    TNS Names, this must not have spaces at the left side of the ALIAS.
    Please check that OracleServiceORCL service is not disabled.



  • 5.  Re: Load Data Warehouse - error ORA-12541 No listener
    Best Answer

    Posted 06-20-2017 11:51 AM

    Hello,

     

    thank you, there was a problem with definition in tnsnames.ora.

     

    After removing of SERVICE_NAME and adding of SID it started to work (because of this the DBLINK was not working as well).

     

    Regards,

    Martin