Oracle Functions in private network

OCI Functions is a server-less platform. In this blog post, we will see how to run oracle functions in a private network. While creating the Application we need to select the desired VCN and private subnet.

Then these are the few things we need to configure for the subnet so that function can run.

1) Service Gateway to reach out OCI service

The function application in the private network needs to connect to the container registry and download the required image. To achieve this we need a Service gateway in the VCN. In the console Network ->  Virtual Cloud Network page we can edit the VCN to add a service gateway.

2) Route Rule for service gateway.

In the private subnet where the application is running, there would be an attached route table, and in that table, we need to add a route rule saying the OCI service calls need to be routed through the service gateway we had created in the previous step.

3) Secure Egress Rule

In that particular subnet we need to allow traffic from the subnet to the OCI service, to do so we will add a stateful Egress Rule in the security list of the subnet

After these steps function should be able to reach out desired OCI service and run.

References:

Access to Oracle Services: Service Gateway

Functions QuickStart Guides

How to connect Java a program to Oracle Autonomous Database over TLS without wallet

In this blog post, we will discuss how to connect Java a program using JDBC thin driver to Oracle Autonomous Database over TLS without a wallet.

With TLS support we can now connect to ADB without the credential wallet.

Part 1: We need to configure ADB for TLS to get the TLS connection string.

a) For the ADB we want to connect over TLS, In the Autonomous database details page, we need to set Mutual TLS authentication (mTLS) as not required. 

b) Then from the database connection page (we can navigate to it by clicking on DB connection button in the ADB console) chose "TLS" as TLS authentication and copy the connection string for desired TNS name. In this example, I had copied demodb_medium

Part 2: Java program which uses the above connect string in jdbc to execute sql statements.

Prereq : ojdbc8.jar and ucp.jar

I have used JDK 11 in this eample

package demo;

import java.sql.*;
import java.util.Properties;

public class ADBSharedTLSConnect {
    private static String atps_tls = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=rks9000p5ge4_demodb_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)(ssl_server_cert_dn=\"CN=adb.ap-mumbai-1.oraclecloud.com, OU=Oracle ADB INDIA, O=Oracle Corporation, L=Redwood City, ST=California, C=US\")))";
    private static String db_url = "jdbc:oracle:thin:@" + atps_tls;
    private static String dbUser = "admin";
    private static String dbPwd = "test@ATP122245";

    public static void main(String[] args) {
        System.out.println("Connecting to ATPS over TLS...");
        ResultSet rs = null;
        Statement stmt = null;
        Connection con = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Properties props = new Properties();
            props.setProperty("user", dbUser);
            props.setProperty("password", dbPwd);
            props.setProperty("oracle.jdbc.fanEnabled", "false");
            con = DriverManager.getConnection(db_url, props);
            stmt = con.createStatement();
            rs = stmt.executeQuery("select sysdate from dual");
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
            System.out.println("Demo Over...");

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

atps_tls is the one that I had copied from part 1 step b.

I have used oracle.jdbc.fanEnabled property as false, without this configuration there was an error 

SEVERE: attempt to configure ONS in FanManager failed with oracle.ons.NoServersAvailable: Subscription time out

Although query was getting executed.

Some useful links

1. JDBC connection without wallet

2. Update your Autonomous Database Instance to Allow both TLS and mTLS Authentication

3. View TNS Names and Connection Strings for an Autonomous Database Instance

4. Source Code in git

How to Connect a Go program to Oracle Autonomous Database

In this blog post, we will see how to connect Oracle Autonomous Database in Go programming language. This can be divided into three parts.

Part 1) We need to download Autonomous Database client credentials.  More details

In the Autonomous database OCI console, open to the service console of the DB which we need to connect. There in the Administration tab, we will find the option to download the client credentials.

 Once It is downloaded, unzip the same to a directory of choice.

Part 2) To connect to the database we need the database user, password, and the connect string.

We can use the admin user, which was provided as part of the database provision, or any other database user. Here we need to form the connect string.

example: protocol://host:port/service_name?wallet_location=/my/dir&retry_count=3&retry_delay=20

We need to form our connect string for the autonomous database we want to connect. We will get details in the tnsnames.ora file which is part of the zip we had downloaded in above Part 1.

There we will find many connection services (more details about them), We can pick the one we want to use for our connection.

example: 

db202107181649_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-mumbai-1.oraclecloud.com))(connect_data=(service_name=iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.ap-mumbai-1.oraclecloud.com, OU=Oracle ADB INDIA, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

Now by referring to this we can create our connection string like below.

tcps://adb.ap-mumbai-1.oraclecloud.com:1522/iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com?wallet_location=/Users/pallab/tool/wallet_DB/

The wallet_location is the path where we had unzipped our client credentials in above Part 1.

Part 3) Now let's write our go code.

We will use godror package to connect. 

Install godor # go get github.com/godror/godror

We need runtime dependency for Oracle Client libraries, We can download the free basic or free light version from https://www.oracle.com/database/technologies/instant-client/downloads.html.

package main

import (

"database/sql"

"fmt"

_ "github.com/godror/godror"

)

func main() {

connectToADb()

}

func connectToADb() {

fmt.Println("Connecting to Oracle Autonoumus database !!!")

db, err := sql.Open("godror", `user="admin" password="testPwd"

connectString="tcps://adb.ap-mumbai-1.oraclecloud.com:1522/iro8q5fzknp5ge4_db202107181649_medium.adb.oraclecloud.com?wallet_location=/Users/pallab/tool/wallet_DB/"

libDir="/Users/pallab/tool/instantclient_19_8/"`)

if err != nil {

fmt.Println(err)

return

}

defer db.Close()

rows, err := db.Query("select 'hello' from dual")

if err != nil {

fmt.Println("Error running query")

fmt.Println(err)

return

}

defer rows.Close()

var resData string

for rows.Next() {

rows.Scan(&resData)

}

fmt.Printf("The response is: %s\n", resData)

}

Note: In sql.open() we have passed our oracle client libraries which we downloaded and unzipped as libDir  parameter.

Useful Links

1. https://blogs.oracle.com/opal/how-connect-to-oracle-autonomous-cloud-databases

2. https://blogs.oracle.com/developers/how-to-connect-a-go-program-to-oracle-database-using-goracle

3. https://godror.github.io/godror/doc/installation.html