Load Data Warehouse - error ORA-12541 No listener

[Martin Snizek]

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

[Lukas Jezek]

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.

[Kritika Rana]

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.

[Martin Snizek]

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

[Suman Pramanik]

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