oracle – Gokhan Atil’s Blog

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 function:

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 Atil4 CommentsAWS, Oraclejava, lambda, oracle

Renaming Files for RMAN Restore

We usually need to duplicate our databases to test servers which have different disk layouts than production server. So we need to distribute the datafiles according to the disk space and directory structure of the new server. It becomes a time-consuming task if you’re dealing with hundreds of datafiles. Today I saw a PL/SQL script written by Bobby Durrett for renaming datafiles for RMAN restore. It produces required “set newname” commands which can be used by RMAN. That was a great idea and I decided to write my own version to satisfy my needs:

DECLARE
    CURSOR DF_CURSOR IS 
        SELECT 'data' ft, FILE_ID, FILE_NAME, 
        BYTES / (1024 * 1024 * 1024) fsize
        FROM dba_data_files
        union all 
        SELECT 'temp' ft, FILE_ID, FILE_NAME, 
        BYTES / (1024 * 1024 * 1024) fsize
        FROM dba_temp_files
        ORDER BY ft, file_id;

   TYPE array_number IS TABLE OF NUMBER;
   TYPE array_varchar2 IS TABLE OF VARCHAR2 (200);

   diskno      NUMBER;

   diskspace   array_number := array_number (
                                   30,
                                   20,
                                   20,
                                   120); -- Sizes in GB

   diskpath   array_varchar2 := array_varchar2 (
                                   '/u01/data',
                                   '/u02/data',
                                   '/u03/data',
                                   '/u04/data');

   prefix      VARCHAR2 (200) := '/SID'; -- will be added to diskpaths
   
BEGIN
   FOR df IN DF_CURSOR
   LOOP
      FOR diskno IN 1 .. diskspace.COUNT
      LOOP
         IF (diskspace (diskno) > df.fsize)
         THEN
            DBMS_OUTPUT.put_line ( 'set newname for ' || df.ft || 'file '
               || df.FILE_ID || ' to  ''' || diskpath (diskno) || prefix
               || SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)) 
               || ''';');
            diskspace (diskno) := diskspace (diskno) - df.fsize;
            EXIT;
            ELSE
            IF diskno = diskspace.COUNT then
               DBMS_OUTPUT.put_line ('# no space for '||df.FILE_ID||' '
               ||SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)));
            END IF;
         END IF;
      END LOOP;
   END LOOP;
END;
/

DECLARE

CURSOR DF_CURSOR IS

SELECT 'data' ft, FILE_ID, FILE_NAME,

BYTES / (1024 * 1024 * 1024) fsize

FROM dba_data_files

union all

SELECT 'temp' ft, FILE_ID, FILE_NAME,

BYTES / (1024 * 1024 * 1024) fsize

FROM dba_temp_files

ORDER BY ft, file_id;

TYPE array_number IS TABLE OF NUMBER;

TYPE array_varchar2 IS TABLE OF VARCHAR2 (200);

diskno NUMBER;

diskspace array_number := array_number (

30,

20,

120); -- Sizes in GB

diskpath array_varchar2 := array_varchar2 (

'/u01/data',

'/u02/data',

'/u03/data',

'/u04/data');

prefix VARCHAR2 (200) := '/SID'; -- will be added to diskpaths

BEGIN

FOR df IN DF_CURSOR

LOOP

FOR diskno IN 1 .. diskspace.COUNT

LOOP

IF (diskspace (diskno) > df.fsize)

THEN

DBMS_OUTPUT.put_line ( 'set newname for ' || df.ft || 'file '

|| df.FILE_ID || ' to ''' || diskpath (diskno) || prefix

|| SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1))

|| ''';');

diskspace (diskno) := diskspace (diskno) - df.fsize;

EXIT;

ELSE

IF diskno = diskspace.COUNT then

DBMS_OUTPUT.put_line ('# no space for '||df.FILE_ID||' '

||SUBSTR (df.FILE_NAME, INSTR (df.FILE_NAME, '/', -1)));

END IF;

END LOOP;

END;

June 21, 2013Gokhan Atil7 CommentsOracleoracle, rman