Dbeaver Oracle Tns

Posted on by



  1. Dbeaver Oracle Tns Connection
  2. Dbeaver Oracle Tns Login
  3. Dbeaver Oracle Tnsnames.ora
  4. Dbeaver Oracle Tns Download
  5. Dbeaver Oracle Tns
  6. Dbeaver Oracle Tns Training
  7. Dbeaver Oracle Tns Software

Autonomous Database mandates a secure connection that uses Transport Layer Security (TLSv1.2). Java applications that use JDBC Thin driver require either Oracle Wallet or Java KeyStore (JKS). The wallet and keystore files are included in the client credentials .zip file that is available by clicking DB Connection on the Oracle Cloud Infrastructure console.

Topics

JDBC Thin Driver Connection Prerequisites

Applications that use JDBC Thin driver require the Oracle database credentials including the Oracle wallets or Java KeyStore (JKS) files when connecting to the database.

End-to-end connectivity using IPv6 in Oracle Database 11 g requires the following configuration: The client TNS connect address must connect to the Oracle Net Listener on the IPv6 endpoint. The database instance configured for Oracle Net Listener must listen for connection requests on IPv6 endpoints.

  1. DBeaver is a free, open source multiplatform database management tool and SQL client for developers and database administrators. DBeaver can be used to access any database or cloud application that has an ODBC or JDBC driver, such as Oracle, SQL Server, MySQl, Salesforce, or MailChimp.
  2. Manage Oracle data with visual tools in DBeaver like the query browser. The CData JDBC Driver for Oracle implements JDBC standards that enable third-party tools to interoperate, from wizards in IDEs to business intelligence tools. This article shows how to connect to Oracle data with wizards in DBeaver and browse data in the DBeaver GUI.

Perform the following steps before connecting to the database:

  1. Provision Autonomous Database: Create a database and obtain your database credentials (username and password).
  2. Download Client Credentials: Unzip the wallet_databasename.zip to a secure location. Make sure that only authorized users have access to these files.

    See Download Client Credentials (Wallets) for information on downloading client credentials for Autonomous Database.

  3. Verify your JDK version for security: If you are using JDK11, JDK10, or JDK9 then you don’t need to do anything for this step. If your JDK version is less than JDK8u162 then you need to download the JCE Unlimited Strength Jurisdiction Policy Files. Refer to the README file for installation notes. Download the JCE files from Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 8 Download.
  4. Check JDBC Driver Version: Download the latest 18.3 JDBC Thin driver (ojdbc8.jar and ucp.jar) from Oracle Database 18c (18.3) JDBC Driver & UCP Downloads. Use the latest 18.3 JDBC driver, or newer, to take advantage of recent enhancements that simplify connections and provide easy steps for configuration. You also need the additional jars: oraclepki.jar, osdt_core.jar, and osdt_cert.jar for use with Oracle wallets.

Using a JDBC URL Connection String with JDBC Thin Driver

The connection string is found in the file tnsnames.ora which is part of the client credentials download. The tnsnames.ora file contains the predefined service names. Each service has its own TNS alias and connection string.

A sample entry, with dbname_high as the TNS alias and a connection string in tnsnames.ora follows:

Set the location of tnsnames.ora with the property TNS_ADMIN in one of the following ways:

  • As part of the connection string (only with the 18.3 or newer JDBC driver)
  • As a system property, -Doracle.net.tns_admin
  • As a connection property (OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN)

Using the 18.3 JDBC driver, the connection string includes the TNS alias and the TNS_ADMIN connection property.

Sample connection string using 18.3 JDBC driver (Linux):

Sample connection string using 18.3 JDBC driver (Windows):

The TNS_ADMIN connection property specifies the following:

  • The location of tnsnames.ora.
  • The location of Oracle Wallet (ewallet.sso, ewallet.p12) or Java KeyStore (JKS) files (truststore.jks, keystore.jks).
  • The location of ojdbc.properties. This file contains the connection properties required to use Oracle Wallets or Java KeyStore (JKS).

Note:

If you are using 12.2.0.1 or older JDBC drivers, then the connection string contains only the TNS alias. To connect using older JDBC drivers:

  • Set the location of the tnsnames.ora, either as a system property with -Doracle.net.tns_admin or as a connection property (OracleConnection.CONNECTION_PROPERTY_TNS_ADMIN).
  • Set the wallet or JKS related connection properties in addition to TNS_ADMIN.

For example, in this case you set the TNS alias in the DB_URL without the TNS_ADMIN part as:

See Predefined Database Service Names for Autonomous Database for more details.

Using a JDBC Connection with 18.3 JDBC Driver

Applications that use JDBC Thin driver can connect to Autonomous Databases using either Oracle Wallets or Java KeyStore (JKS).

Using Oracle Wallet

To use Java and the 18.3 JDBC Thin Driver to connect to Autonomous Database with the Oracle Wallet, do the following:

Download

  1. Make sure that the prerequisites are met: See JDBC Thin Driver Connection Prerequisites for more information.

  2. Verify the connection: You can either use a Java program, a servlet, or IDEs to verify the connection to the database. A simple test is to download DataSourceSample.java or UCPSample.java from JDBC code samples and update the connection URL to have the required TNS alias and pass TNS_ADMIN, providing the path for tnsnames.ora and the wallet files. Also, in the sample source code update the database username and password. For example:

    Note:

    If you are using Microsoft Active Directory with a database, then in the sample source code update the username with the Active Directory username and update the password with the Active Directory user password. See Use Microsoft Active Directory with Autonomous Database for more information.

  3. Set the wallet location: The properties file ojdbc.properties is pre-loaded with the wallet related connection property.

    Note:

    You do not modify the file ojdbc.properties. The value of TNS_ADMIN determines the wallet location.

  4. Compile and Run: Compile and run the sample to get a successful connection. Make sure you have oraclepki.jar , osdt_core.jar, and osdt_cert.jar, in the classpath. For example:

Note:

The auto-login wallet part of Autonomous Database downloaded client credentials zip file removes the need for your application to use username/password authentication.

Using Java KeyStore

To use Java and the 18.3 JDBC Thin Driver to connect to Autonomous Database with Java KeyStore (JKS), do the following:

  1. Make sure that the prerequisites are met: See JDBC Thin Driver Connection Prerequisites for more information.

  2. Ready the database details: You can either use a Java program, a servlet, or IDEs to check the connection to your database. A simple test is to download DataSourceSample.java or UCPSample.java from JDBC code samples. In this sample, use the connection URL as shown below. Note that the connection DB_URL contains the TNS alias, for example, dbname_high present in tnsnames.ora. You can provide the path for tnsnames.ora file through TNS_ADMIN property as shown in the URL. Make sure to use the database username and password related to your database.

    Note:

    If you are using Microsoft Active Directory with Autonomous Database, then make sure to change the sample source code to use the Active Directory username and the Active Directory user password. See Use Microsoft Active Directory with Autonomous Database for more information.

  3. Set JKS related connection properties: Add the JKS related connection properties to ojdbc.properties file. The keyStore and truststore password are the password specified when you're downloading the client credentials .zip file from the Autonomous Database service console.

    To use SSL connectivity instead of Oracle Wallet, specify the keystore and truststore files and their respective password in the ojdbc.properties file as follows:

    Note:

    Make sure to comment the wallet related property in ojdbc.properties. For example:

  4. Compile and Run: Compile and run the sample to get a successful connection. For example:

Connecting Using JDBC Thin Driver 12.2 or Older

Oracle

If you are using the JDBC driver 12.2.0.2 or older, set the Java properties prior to starting the application. Usually you set the properties in the application's startup script.

If you are not able to use the latest 18.3 JDBC drivers, then you can connect to Autonomous Database using 12.2.0.2 or other older JDBC drivers. The 12.2 or older JDBC drivers do not support the ojdbc.properties file. With older JDBC driver versions, you need to pass wallets or JKS related properties either as system properties or as connection properties to establish a connection.

Dbeaver Oracle Tns Connection

Using Oracle Wallet

To use Java and the 12.2 or older JDBC Drivers to connect to Autonomous Database with the Oracle Wallet, do the following:

  1. Make sure that the prerequisites are met: See JDBC Thin Driver Connection Prerequisites for more information.

  2. Verify the connection: You can either use a Java program, a servlet, or IDEs to verify the connection to the database. A simple test is to download DataSourceSample.java or UCPSample.java from JDBC code samples and update the connection URL to have the required TNS alias. Also, update the sample source code to use the database username and password. For example:

    Note:

    If you are using Microsoft Active Directory with Autonomous Database, then update the sample source code to use the Active Directory username and Active Directory user password. See Use Microsoft Active Directory with Autonomous Database for more information.

  3. Set the wallet location: Add the OraclePKIProvider at the end of the provider list in the file java.security (this file is part of your JRE install located at $JRE_HOME/jre/lib/security/java.security) which typically looks like:

  4. Compile and Run: Compile and run the sample to get a successful connection. Make sure to have oraclepki.jar , osdt_core.jar, and osdt_cert.jar, in the classpath. Also, you need to pass the connection properties. Update the properties with the location where tnsnames.ora and wallet files are located.

Note:

These are Windows system examples. Add a

Dbeaver Oracle Tns Login

continuation character if you are setting –D properties on multiple lines on UNIX ( Linux or a Mac).

Using Java KeyStore

To use Java and the 12.2 or older JDBC Thin Drivers to connect to Autonomous Database with Java KeyStore (JKS), do the following:

  1. Make sure that the prerequisites are met: See JDBC Thin Driver Connection Prerequisites for more information.

  2. Verify the connection: You can either use a Java program, a servlet, or IDEs to verify the connection to the database. A simple test is to download DataSourceSample.java or UCPSample.java from JDBC code samples and update the connection URL to have the required TNS alias and pass TNS_ADMIN, providing the path for tnsnames.ora and update the connection URL to have the required TNS alias. Also, in the sample source code update the database username and password. For example:

    Note:

    If you are using Microsoft Active Directory with Autonomous Database, then update the sample source code to use the Active Directory username and Active Directory user password. See Use Microsoft Active Directory with Autonomous Database for more information.

  3. Compile and Run: Compile and run the sample to get a successful connection. You need to pass the connection properties as shown below. Update the properties with the location where tnsnames.ora and JKS files are placed. If you want to pass these connection properties programmatically then refer to DataSourceForJKS.java. For example:

JDBC Thin Connections with an HTTP Proxy

Dbeaver Oracle Tnsnames.ora

If the client is behind a firewall and your network configuration requires an HTTP proxy to connect to the internet, you need to use the JDBC Thin Client 18.1 or higher which enables connections through HTTP proxies.

To connect to Autonomous Database through an HTTPS proxy, open and update your tnsnames.ora file. Add the HTTP proxy hostname(https_proxy) and port (https_proxy_port) to the connection string. Replace the values with your HTTPS proxy information. For example:

Dbeaver Oracle Tns Download

  1. Add the HTTP proxy hostname and port to the connection definitions in tnsnames.ora. You need to add the https_proxy and https_proxy_port parameters in the address section of connection definitions. For example, the following sets the HTTP proxy to proxyhostname and the HTTP proxy port to 80; replace these values with your HTTP proxy information:

Notes:

  • JDBC Thin client versions earlier than 18.1 do not support connections through HTTP proxy.

  • Successful connection depends on specific proxy configurations and the performance of data transfers would depend on proxy capacity. Oracle does not recommend using this feature in Production environments where performance is critical.

  • Configuring tnsnames.ora for the HTTP proxy may not be enough depending on your organization's network configuration and security policies. For example, some networks require a username and password for the HTTP proxy.

  • In all cases, contact your network administrator to open outbound connections to hosts in the oraclecloud.com domain using the relevant port without going through an HTTP proxy.

The CData JDBC Driver for Oracle implements JDBC standards that enable third-party tools to interoperate, from wizards in IDEs to business intelligence tools. This article shows how to connect to Oracle data with wizards in DBeaver and browse data in the DBeaver GUI.

Create a JDBC Data Source for Oracle Data

Follow the steps below to load the driver JAR in DBeaver.

  1. Open the DBeaver application and, in the Databases menu, select the Driver Manager option. Click New to open the Create New Driver form.
  2. In the Driver Name box, enter a user-friendly name for the driver.
  3. To add the .jar, click Add File.
  4. In the create new driver dialog that appears, select the cdata.jdbc.oracleoci.jar file, located in the lib subfolder of the installation directory.
  5. Click the Find Class button and select the OracleOCIDriver class from the results. This will automatically fill the Class Name field at the top of the form. The class name for the driver is cdata.jdbc.oracleoci.OracleOCIDriver.
  6. Add jdbc:oracleoci: in the URL Template field.

Create a Connection to Oracle Data

Dbeaver Oracle Tns

Follow the steps below to add credentials and other required connection properties.

  1. In the Databases menu, click New Connection.
  2. In the Create new connection wizard that results, select the driver.
  3. On the next page of the wizard, click the driver properties tab.

  4. Enter values for authentication credentials and other properties required to connect to Oracle.

    To connect to Oracle, you'll first need to update your PATH variable and ensure it contains a folder location that includes the native DLLs. The native DLLs can be found in the lib folder inside the installation directory. Once you've done this, set the following to connect:

    • Port: The port used to connect to the server hosting the Oracle database.
    • User: The user Id provided for authentication with the Oracle database.
    • Password: The password provided for authentication with the Oracle database.
    • Service Name: The service name of the Oracle database.

    Built-in Connection String Designer

    For assistance in constructing the JDBC URL, use the connection string designer built into the Oracle JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

    java -jar cdata.jdbc.oracleoci.jar

    Fill in the connection properties and copy the connection string to the clipboard.

    Below is a typical connection string:

    jdbc:oracleoci:User=myuser;Password=mypassword;Server=localhost;Port=1521;

Dbeaver Oracle Tns Training

Query Oracle Data

Dbeaver Oracle Tns Software

You can now query information from the tables exposed by the connection: Right-click a Table and then click Edit Table. The data is available on the Data tab.