java – Gokhan Atil's Blog

Amazon QLDB and the Missing Command Line Client

Amazon Quantum Ledger Database is is a fully managed ledger database which tracks all changes of user data and maintains a verifiable history of changes over time. It was announced at AWS re:Invent 2018 and now available in five AWS regions: US East (N. Virginia), US East (Ohio), US West (Oregon), Europe (Ireland), and Asia Pacific (Tokyo).

You may ask why you would like to use QLDB (a ledger database) instead of using your traditional database solution. We all know that it’s possible to create history tables for our fact tables, and keep them up to date using triggers, stored procedures, or even with our application code (by writing changes of the main table to its history table). You can also say that your database have write-ahead/redo logs, so it’s possible to track and verify the changes of all your data as long as you keep them in your archive. On the other hand, It’s clear that this will create an extra workload and complexity for the database administrator and the application developer while it does not guarantee that the data was intact and reliable. What if your DBA directly modifies the data and history table after disabling the triggers and even alter the archived logs? You may say it’s too hard, but you know that it’s technically possible. In a legal dispute, or a security compliance investigation, this might be enough to question to the integrity of the data.

QLDB solves this problem with cryptographically verifiable journal. When an application needs to modify data in a document, the changes are logged into the journal files first (WAL concept). The difference here is, each block is hashed (SHA-256) for “verification” and has a sequence number to specify its address within the journal. QLDB calculates this hash value using the content of the journal block and the hash value of previous block. So the journal blocks are chained by the hash values! The QLDB users do not have access to the logs and the logs are immutable. In anyway, if someone modifies data, they also need to update the journal blocks related with the data. This will cause a new hash to be generated for the journal block, and all the following blocks will have a different hash value than before.

September 12, 2019Gokhan AtilAWSjava, qldb

Sample AWS Lambda Function to Monitor Oracle Database

I wrote a very simple AWS Lambda function to demonstrate how to connect an Oracle database, gather the tablespace usage information, and send these metrics to CloudWatch. First, I wrote this lambda function in Python and then I had to re-write it in Java. As you may know, you need to use cx_oracle module to connect Oracle Databases with Python. This extension module requires some libraries which are shipped by Oracle Database Client (oh God!). It’s a little bit tricky to pack it for the AWS Lambda. Good thing is, I found a great document which explains all necessary steps.

Here’s the main class which will be used by Lambda service:

package com.gokhanatil.samplelambda;

import com.amazonaws.services.cloudwatch.AmazonCloudWatch;
import com.amazonaws.services.cloudwatch.AmazonCloudWatchClientBuilder;
import com.amazonaws.services.cloudwatch.model.Dimension;
import com.amazonaws.services.cloudwatch.model.MetricDatum;
import com.amazonaws.services.cloudwatch.model.PutMetricDataRequest;
import com.amazonaws.services.cloudwatch.model.StandardUnit;
import com.amazonaws.services.lambda.runtime.*;
import java.lang.Math;
import java.sql.*;
import java.util.Map;
import java.util.Properties;

public class Monitoring implements RequestHandler<Map<String, Object>, String> {
    public String handleRequest(Map<String, Object> input, Context context) {

        final AmazonCloudWatch cw = AmazonCloudWatchClientBuilder.defaultClient();

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }

        Connection conn;
        Properties connectionProps = new Properties();
        connectionProps.put("user", System.getenv("DB_USER"));
        connectionProps.put("password", System.getenv("DB_PASSWORD"));

        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@" + System.getenv("DB_HOSTNAME") + ":"
                    + System.getenv("DB_PORT") + ":" + System.getenv("DB_DATABASE"), connectionProps);

            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics");
            while (rs.next()) {

                Dimension dimension = new Dimension().withName("Tablespace")
                        .withValue(System.getenv("DB_DATABASE") + ":" + rs.getString(1).toUpperCase());

                MetricDatum datum = new MetricDatum().withMetricName("Space Used (pct)").withUnit(StandardUnit.None)
                        .withValue((double) Math.round(rs.getDouble(2) * 100)).withDimensions(dimension);

                PutMetricDataRequest request = new PutMetricDataRequest().withNamespace("Databases")
                        .withMetricData(datum);

                cw.putMetricData(request);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return "{'result': 'success'}";
    }
}

package com.gokhanatil.samplelambda;

import com.amazonaws.services.cloudwatch.AmazonCloudWatch;

import com.amazonaws.services.cloudwatch.AmazonCloudWatchClientBuilder;

import com.amazonaws.services.cloudwatch.model.Dimension;

import com.amazonaws.services.cloudwatch.model.MetricDatum;

import com.amazonaws.services.cloudwatch.model.PutMetricDataRequest;

import com.amazonaws.services.cloudwatch.model.StandardUnit;

import com.amazonaws.services.lambda.runtime.*;

import java.lang.Math;

import java.sql.*;

import java.util.Map;

import java.util.Properties;

public class Monitoring implements RequestHandler<Map<String, Object>, String> {

public String handleRequest(Map<String, Object> input, Context context) {

final AmazonCloudWatch cw = AmazonCloudWatchClientBuilder.defaultClient();

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

} catch (ClassNotFoundException e1) {

e1.printStackTrace();

}

Connection conn;

Properties connectionProps = new Properties();

connectionProps.put("user", System.getenv("DB_USER"));

connectionProps.put("password", System.getenv("DB_PASSWORD"));

try {

conn = DriverManager.getConnection("jdbc:oracle:thin:@" + System.getenv("DB_HOSTNAME") + ":"

+ System.getenv("DB_PORT") + ":" + System.getenv("DB_DATABASE"), connectionProps);

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics");

while (rs.next()) {

Dimension dimension = new Dimension().withName("Tablespace")

.withValue(System.getenv("DB_DATABASE") + ":" + rs.getString(1).toUpperCase());

MetricDatum datum = new MetricDatum().withMetricName("Space Used (pct)").withUnit(StandardUnit.None)

.withValue((double) Math.round(rs.getDouble(2) * 100)).withDimensions(dimension);

PutMetricDataRequest request = new PutMetricDataRequest().withNamespace("Databases")

.withMetricData(datum);

cw.putMetricData(request);

}

} catch (SQLException e) {

e.printStackTrace();

}

return "{'result': 'success'}";

}

September 4, 2019Gokhan AtilAWS, Oraclejava, lambda, oracle

Oracle Berkeley DB Java Edition

I was searching NoSQL databases and see that Oracle provides a NoSQL database called Berkeley DB. I examined it and wrote a blog to give quick tips to start Java Edition of Berkeley DB. This blog was published in Turkish, about 1 year ago. I’ve decided to translate (in fact, re-write) and publish it in English, and this is what you are reading now.

Berkeley DB is a high-performance embedded database originated at the University of California, Berkeley. It’s fast, reliable and used in several applications such as Evolution (email client), OpenLDAP, RPM (The RPM Package Manager) and Postfix (MTA). In contrast to most other database systems, Berkeley DB provides relatively simple data access services. Berkeley DB databases are B+Trees (like indexes in Oracle RDBMS) and can store only key/value pairs (there are no columns or tables). The keys and values are byte arrays. Databases are stored as files within a single directory which is called “environment”.

There are three versions of Berkeley DB:

Berkeley DB (the traditional database, written in C)
Berkeley DB Java Edition (native Java version)
Berkeley DB XML (for storing XML documents)

As a hobbyist Java programmer, I prefer Berkeley DB Java Edition (JE). Berkeley DB JE supports almost all features of traditional Berkeley DB such as replication, hot-backups, ACID and transactions. It is written in pure Java so it’s platform-independent.

Berkeley DB JE provides two interfaces:

Traditional Berkeley DB API (with DB data abstraction of key/value pairs)
Direct Persistence Layer (DPL) which contains “Plain Old Java Objects” (POJO)

Because I’m an old-school (ex)programmer, I’ll show how to use the traditional Berkeley DB API. Traditional Berkeley DB API will help you understand how Berkeley DB works.

September 14, 2011Gokhan Atil15 CommentsOracleBerkeley DB, java, nosql

How to Install Oracle Grid Control 11g (Step by Step Guide)

Oracle Enterprise Manager Grid Control is a tool to manage and monitor multiple instances of Oracle and non-Oracle platforms such as Microsoft .NET, Microsoft SQL Server, NetApp Filers, BEA weblogic and others.

I’ll show how to install Oracle Grid Control 11g on Oracle Linux (32bit). Here are the main steps:

1) Installation of Oracle Linux (How to Install Oracle Linux 5.6)
2) Installation of Oracle Database 11.2.0.2 (Repository Database)
3) Installation of Java and Weblogic Server (Middleware)
4) Installation of Grid Control
5) Installation of Grid Control Agent to a Target System

Installation of Oracle Database 11.2

After you install Oracle Linux, login as root and create the directories for Grid Control and other software we’ll install:

mkdir -p  /u01/oracle
chown -R oracle:oinstall /u01/oracle/

1 2	mkdir -p /u01/oracle chown -R oracle:oinstall /u01/oracle/

Because we installed oracle-validated package (How to Install Oracle Linux 5.6), user oracle and the related groups have been already created automatically. Password of the user Oracle is “oracle”. Login as oracle to the system, download the Oracle Database binaries. I’ll use 11.2.0.2 but the steps will be for 11.2.0.1 which you can download from Oracle.com:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linuxsoft-085393.html

Unzip the files:

unzip p10098816_112020_LINUX_1of7.zip
unzip p10098816_112020_LINUX_2of7.zip

1 2	unzip p10098816_112020_LINUX_1of7.zip unzip p10098816_112020_LINUX_2of7.zip

For regular 11.2.0.1 (Linux x86):

unzip linux_11gR2_database_1of2.zip
unzip linux_11gR2_database_2of2.zip

1 2	unzip linux_11gR2_database_1of2.zip unzip linux_11gR2_database_2of2.zip

Unzip will create a directory named “database”, go into this directory and run “./runInstaller”:

July 25, 2011Gokhan Atil52 CommentsOracledatabase, enterprise manager, grid control, install, java

How to Install/Update Java (JDK) on Oracle Linux

Oracle Linux uses RPM (Red Hat Package Manager). If you want to install latest Java platform, you can download Java RPM packages from:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Oracle releases Java RPM wrapped in a self-extracting binary file. So instead of using rpm tool, you just change the mode of file, and make it executable and then run it:

su - root
chmod +x jdk-6u26-linux-i586-rpm.bin 
./jdk-6u26-linux-i586-rpm.bin

Unpacking...
Checksumming...
Extracting...
UnZipSFX 5.50 of 17 February 2002, by Info-ZIP (Zip-Bugs@lists.wku.edu).
  inflating: jdk-6u26-linux-i586.rpm  
  inflating: sun-javadb-common-10.6.2-1.1.i386.rpm  
  inflating: sun-javadb-core-10.6.2-1.1.i386.rpm  
  inflating: sun-javadb-client-10.6.2-1.1.i386.rpm  
  inflating: sun-javadb-demo-10.6.2-1.1.i386.rpm  
  inflating: sun-javadb-docs-10.6.2-1.1.i386.rpm  
  inflating: sun-javadb-javadoc-10.6.2-1.1.i386.rpm  
Preparing...                ################################### [100%]
   1:jdk                    ################################### [100%]
Unpacking JAR files...
        rt.jar...
        jsse.jar...
        charsets.jar...
        tools.jar...
        localedata.jar...
        plugin.jar...
        javaws.jar...
        deploy.jar...
Installing JavaDB
Preparing...                ################################### [100%]
   1:sun-javadb-common      ################################### [ 17%]
   2:sun-javadb-core        ################################### [ 33%]
   3:sun-javadb-client      ################################### [ 50%]
   4:sun-javadb-demo        ################################### [ 67%]
   5:sun-javadb-docs        ################################### [ 83%]
   6:sun-javadb-javadoc     ################################### [100%]

Java(TM) SE Development Kit 6 successfully installed.

Product Registration is FREE and includes many benefits:
* Notification of new versions, patches, and updates
* Special offers on Oracle products, services and training
* Access to early releases and documentation

Product and system data will be collected. If your configuration
supports a browser, the JDK Product Registration form will
be presented. If you do not register, none of this information
will be saved. You may also register your JDK later by
opening the register.html file (located in the JDK installation
directory) in a browser.

For more information on what data Registration collects and 
how it is managed and used, see:
http://java.sun.com/javase/registration/JDKRegistrationPrivacy.html

su - root

chmod +x jdk-6u26-linux-i586-rpm.bin

./jdk-6u26-linux-i586-rpm.bin

Unpacking...

Checksumming...

Extracting...

UnZipSFX 5.50 of 17 February 2002, by Info-ZIP (Zip-Bugs@lists.wku.edu).

inflating: jdk-6u26-linux-i586.rpm

inflating: sun-javadb-common-10.6.2-1.1.i386.rpm

inflating: sun-javadb-core-10.6.2-1.1.i386.rpm

inflating: sun-javadb-client-10.6.2-1.1.i386.rpm

inflating: sun-javadb-demo-10.6.2-1.1.i386.rpm

inflating: sun-javadb-docs-10.6.2-1.1.i386.rpm

inflating: sun-javadb-javadoc-10.6.2-1.1.i386.rpm

Preparing... ################################### [100%]

1:jdk ################################### [100%]

Unpacking JAR files...

rt.jar...

jsse.jar...

charsets.jar...

tools.jar...

localedata.jar...

plugin.jar...

javaws.jar...

deploy.jar...

Installing JavaDB

Preparing... ################################### [100%]

1:sun-javadb-common ################################### [ 17%]

2:sun-javadb-core ################################### [ 33%]

3:sun-javadb-client ################################### [ 50%]

4:sun-javadb-demo ################################### [ 67%]

5:sun-javadb-docs ################################### [ 83%]

6:sun-javadb-javadoc ################################### [100%]

Java(TM) SE Development Kit 6 successfully installed.

Product Registration is FREE and includes many benefits:

* Notification of new versions, patches, and updates

* Special offers on Oracle products, services and training

* Access to early releases and documentation

Product and system data will be collected. If your configuration

supports a browser, the JDK Product Registration form will

be presented. If you do not register, none of this information

will be saved. You may also register your JDK later by

opening the register.html file (located in the JDK installation

directory) in a browser.

For more information on what data Registration collects and

how it is managed and used, see:

http://java.sun.com/javase/registration/JDKRegistrationPrivacy.html

Is it done? Let’s check it:

July 25, 2011Gokhan Atil29 CommentsOraclealternatives, java, oracle linux