Source Extractor - SQL PLSQL - Connection step

This page provides the approach to troubleshoot the connection issues you can experience while running the SQL PLSQL extractors.Source Extractors

1. Check that the JDBC url used is following the below format :
   1. For SID : jdbc:oracle:thin:@<HOST>:<PORT>:<SID>. For instance : jdbc:oracle:thin:@TKBDBSERVER:1521:TKBSID
   2. For Service : jdbc:oracle:thin:@<HOST>:<PORT>/<SERVICE> . For instance : jdbc:oracle:thin:@TKBDBSERVER:1521/TKBService
2. If the JDBC url is correct, you must unsure that the TCP/IP connection protocole is enabled on the Oracle Server:
   1. Edit with notepad++ the file %oracle_home%/network\admin/tnsname.ora,
   2. Check the PROTOCOLE option for the the server you are connecting to. It must be set to TCP or TCPS as below screenshot:
      
      
      
      

3. If the protocole is TCP or TCPS, run the PL/SQL extractor using the Description that is in the file tnsname.ora :
   For instance, if the Description of the server to connect is like :

   TKBDBSERVER =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = TKBDBSERVER)(PORT = 1521))
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SID = TKBS)
       )
     )

   
   The JDBC url will be like : jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= TKBDBSERVER)(PORT=1521))(CONNECT_DATA=(SID= TKBS)))

4. If you are still not able to connect :
   1. If you are not in localhost, make sure that the connection to Oracle Database server is allowed :
      1. Use the JDBC Tester to test the connection by following the page Tools - JDBC Tester to check the JDBC connection
      2. If you are able to connect with the JDBC tester, check that the parameter configured on the PL/SQL extractor are exactly the same.
      3. If you are not able to connect with the JDBC connector :
         1. If Oracle is installed on your machine, check the connection using the tool Oracle SQL+
         2. If Oracle is not installed or if you are able to connect with Oracle SQL+, you can install a free tool like Oracle SQL Developer that uses JDBC connection and test the connection.
         3. If you are not able to connect with Oracle SQL Developper, it means that it is an Infrastructure issue.
         4. Check if a firewall prevent the connection to be done to the server.
5. If you are trying to connect to an Oracle 12c and the following error appear: ORA-28040: No matching authentication protocol or ORA-01017: invalid username/password; log denied:
   1. Make sure that the connection details is correct.
   2. If the credentials are correct, change the ALLOW_LOGIN_VERSION_CLIENT and the ALLOW_LOGIN_VERSION_SERVER parameters that are in the file %ORACLE_HOME%/NETWORK/ADMIN/SQLNET.ora, in the Oracle Server, to lower value. 

If you do not find the information you are looking for or solution for your problem, contact CAST Technical Support and provide the relevant input to help CAST Support team investigate your problem or procure the required information.