OCI function in golang connecting to autonomous database over TLS

 In this post we will discuss how to connect oracle cloud infrastructure function implemented in golang connect to the autonomous database. We will configure the adb on TLS, so that we don't need the client credential wallet for connection. If we need to connect over mtls still this post will give some idea but we need to stage the client credential in the function runtime container, this I may discuss in a different post. 

1. Configure ADB for TLS

We need to enable TLS connection in the autonomous database detail page. (More details).  

a) In adb detail page under the Network section enable the access control list, here I configured the allow-only ip addresses of a particular vcn should able to connect the adb.

 b) Set Mutual TLS authentication to not required, so that TLS connection will be allowed. 

2. Create a oracle function appliaction in the same VCN, which we configured  in above step (1. a), if we wish to run the function in private subnet then few more things we need to take care, details can be found here

3. Go lang function which will connect to ADB. 

we will use godror library to connect to ADB. A standalone golang program connecting ADB will be found here, the same concept we will use to write our function code. 

/**

* @Author Pallab (pallab.rath@gmail.com)

*/

package main

import (

"context"

"database/sql"

"encoding/json"

"fmt"

"io"

fdk "github.com/fnproject/fdk-go"

_ "github.com/godror/godror"

)

func main() {

fdk.Handle(fdk.HandlerFunc(myHandler))

}

func myHandler(ctx context.Context, in io.Reader, out io.Writer) {

db_user := "<db-user>"

db_pwd := "<db-pwd>"

db_host := "<db-host>"

db_port := "<db-port>>"

db_srvc := "<db-service>"

db_details := fmt.Sprintf(`user="%s" password="%s" connectString="tcps://%s:%s/%s"`, db_user, db_pwd,

db_host, db_port, db_srvc)

db, err := sql.Open("godror", db_details)

if err != nil {

fmt.Println(err)

return

}

defer db.Close()

rows, err := db.Query("select sysdate 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)

}

json.NewEncoder(out).Encode(&resData)

}

In this program, we need to provide db user, pwd, host, port, and service name. we can find the host port and service name from tls connect string. The tls connection string can be found on the Database Connection page.

4. Building and publishing the above function.

To build this function we need godror dependency, and this requires gcc compile to be present in build time env. So I created my custom build environment where gcc is installed, instead of the default fnproject/go:1.15-dev build image.

a) Create the build image docker definition (gofn_build.Dockerfile)

FROM oraclelinux:8

RUN yum -y install golang && yum -y clean all && rm -rf /var/cache

Then build this image using the below command

$ docker build -f gofn_build.Dockerfile -t gofn_build .

b) Create the runtime image docker definition (gofn_runtime.Dockerfile), in the runtime container godror need oracle instant client.

FROM oraclelinux:8

WORKDIR /function

RUN curl https://download.oracle.com/otn_software/linux/instantclient/217000/oracle-instantclient-basiclite-21.7.0.0.0-1.el8.x86_64.rpm --output oracle-instantclient-basiclite-21.7.0.0.0-1.el8.x86_64.rpm

RUN yum -y install oracle-instantclient-basiclite-21.7.0.0.0-1.el8.x86_64.rpm && yum -y clean all && rm -rf /var/cache

RUN rm -f oracle-instantclient-basiclite-21.7.0.0.0-1.el8.x86_64.rpm

Then build this image using the below comman

$ docker build -f gofn_build.Dockerfile -t gofn_runtime

c) Create the custom Dockerfile with below content, where we use build go function in the build conatiner we just created in above steps and create a image of our runtime image and the executable function.

FROM gofn_build as build-stage

WORKDIR /function

WORKDIR /go/src/func/

ENV GO111MODULE=on

COPY . .

RUN cd /go/src/func/ && go build -o func

FROM gofn_runtime

WORKDIR /function

COPY --from=build-stage /go/src/func/func /function/

ENTRYPOINT ["./func"]

d) Finally change the func.yaml with below content

schema_version: 20180708

name: function-adb

version: 0.0.26

runtime: docker

$ fn deploy -verbose <ap-name>

Then fn deploy should do the job for us.

$ fn invoke <ap-name>

"2022-09-04T17:56:09Z"

All these source code would be found in https://github.com/pallabrath/myexpjava/tree/master/function-adb

How to measure elapsed time of a method in Java

There are times we need to compute how much time is elapsed while executing a certain task. To compute elapsed time we can think something like below.

public class ElapsedTimeDemo {

public static void main(String[] args) {

long startTime = System.currentTimeMillis();

doSomething();

long endTime = System.currentTimeMillis();

System.out.println("Elapsed time = "+ (endTime - startTime));

}

public void doSomething() {

}

}

This code keeps track of start time of function execution and end time once the execution is complete. The difference between start time and end time gives us the elapsed time. This looks good. The only problem is that for start time and end time we are depending upon the system clock. Because of the clock skew system clock tends to drift away from the actual time. To make the system clock sync usual practice is computer connects to a time server and adjusts its own clock. While adjusting the system clock it may be set forward or backward based on the drift. This can cause some issues in the above program. If the clock is getting adjusted while the method execution of doSomething(), we will get the end time which can be either before or after the real end time. This makes the elapsed time calculation faulty.

To make the correct elapsed time calculation consider the below program.

public class ElapsedTimeDemo {

public static void main(String[] args) {

long startTime = System.nanoTime();

doSomething();

long endTime = System.nanoTime();

System.out.println("Elapsed time = "+ (endTime - startTime));

}

public void doSomething() {

}

}

Here instead of currentTimeMillis() we are using nanoTime(), Nano time comes from a JVM monotonic clock. A monotonic clock means it starts with a fixed origin, let's assume it's a counter that starts from 0 when JVM starts it keeps on incrementing. At any point in time counter value will not make much sense in absolute terms. In elapsed time calculation this is useful. And this doesn't get adjusted like a system clock. 

References

Clock Skew

System.currentTimeMillis()

System.nanoTime()

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