Tuesday, February 8, 2022

Managing your ZDLRA replication queue remotely

 With the rise of Cyber Crime, more and more companies are looking at an architecture with a second backup copy that is protected with an airgap.   Below is the common architecture that I am seeing.


In this post I will walk through an example of how to implement a simple Java program that performs the tasks necessary to manage the airgap for a ZDLRA that is implemented in a cyber vault (DC1 Vault in the picture).  Feel free to use this as a starting point to automate the process.

Commands

There are 3 commands that I need to be able execute remotely

  • PAUSE      -This will pause the replication server that I configured
  • RESUME - This will resume the replication server that I configured
  • QUERY    - This will query the queue on the upstream to determine how much is left in the queue.
First however I need to configure the parameters to execute the calls.

Config file (airgap.config).

I create config file to customize the script for my environment. Below are the parameters that I needed to connect to the ZDLRA and execute the commands.
  • HOST                    - This is name of the scan listener on upstream ZDLRA.
  • PORT                     - This is the Sqlnet port being used to connect to the upstream ZDLRA
  • SERVICE_NAME - Service name of the database on the upstream ZDLRA
  • USERNAME         - The username to connect to the upstream database
  • PASSWORD          - Password for the user. Feel free to encrypt this in java.
  • REPLICATION_SERVER - Replication server to manage

Below is what my config file looks like.

airgap.host=oracle-19c-test-tde
airgap.port=1521
airgap.service_name=ocipdb
airgap.username=bgrenn
airgap.password=oracle
airgap.replication_server=replairgap


Java code (airgap.java).

Java snippet start

The start of the Java Code will import the functions necessary and set up my class


import java.sql.*;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.FileInputStream;
import java.util.Date;
import java.util.Properties;

// Create a arigap class
public class airgap {

   private Properties prop = new Properties();


Java snippet get properties

The first method will get the airgap properties from the property files so that I can use them in the rest of the methods.

// Create a get_airgap_properties method
  public void get_airgap_properties()
        {
                String fileName = "airgap.config";
                try (FileInputStream fis = new FileInputStream(fileName)) {
                    prop.load(fis);
                } catch (FileNotFoundException ex) {
                    System.out.println("cannot find config file airgap.config");
                } catch (IOException ex) {
                    System.out.println("unknown issue finding config file airgap.config");
                }
        }



Java snippet pause replication server

The code below will connect to the database and execute DBMS_RA.PAUSE_REPLICATION_SERVER


// Create a pause_replication  method
  public void pause_replication()
        {
                try     {
                        //Loading driver
                        Class.forName("oracle.jdbc.driver.OracleDriver");

                        //creating connection
                        Connection con = DriverManager.getConnection
                                        ("jdbc:oracle:thin:@//"+
                                         prop.getProperty("airgap.host")+":"+
                                         prop.getProperty("airgap.port")+"/"+
                                         prop.getProperty("airgap.service_name"),
                                         prop.getProperty("airgap.username"),
                                         prop.getProperty("airgap.password"));

                        CallableStatement cs=con.prepareCall("{call dbms_ra.pause_replication_server(?)}");

                        //Set IN Parameters
                        String in1 = prop.getProperty("airgap.replication_server");
                        cs.setString(1,in1);

                        ResultSet rs = cs.executeQuery();   //executing statement


                        con.close();    //closing connection
                        System.out.println("replication server '"+ prop.getProperty("airgap.replication_server")+"' paused");
                        }
                catch(Exception e)      {
                        e.printStackTrace();
                                        }
        }



Java snippet resume replication server

The code below will connect to the database and execute DBMS_RA.RESUME_REPLICATION_SERVER


// Create a pause_replication  method
  public void resume_replication()
        {
                try     {
                        //Loading driver
                        Class.forName("oracle.jdbc.driver.OracleDriver");

                        //creating connection
                        Connection con = DriverManager.getConnection
                                        ("jdbc:oracle:thin:@//"+
                                         prop.getProperty("airgap.host")+":"+
                                         prop.getProperty("airgap.port")+"/"+
                                         prop.getProperty("airgap.service_name"),
                                         prop.getProperty("airgap.username"),
                                         prop.getProperty("airgap.password"));

                        CallableStatement cs=con.prepareCall("{call dbms_ra.resume_replication_server(?)}");

                        //Set IN Parameters
                        String in1 = prop.getProperty("airgap.replication_server");
                        cs.setString(1,in1);

                        ResultSet rs = cs.executeQuery();   //executing statement


                        con.close();    //closing connection
                        System.out.println("replication server '"+ prop.getProperty("airgap.replication_server")+"' resumed");
                        }
                catch(Exception e)      {
                        e.printStackTrace();
                                        }
        }


Java snippet query replication server

The java code below will query the replication queue in the upstream ZDLRA and return 4 columns
  • REPLICATION SERVER - name of the replication server
  • TASKS QUEUED - Number of tasks in the queue to be replicated
  • TOTAL GB QUEUED - Amount of data in the queue
  • MINUTES IN QUEUE - The number of minutes the oldest replication piece has been in the queue.
The last piece of information can be very useful to tell you how current the replication is. With real-time redo, the queue may never be empty.

// Create a queue_select method
  public void queue_select()
        {
                try     {
                        //Loading driver
                        Class.forName("oracle.jdbc.driver.OracleDriver");

                        //creating connection
                        Connection con = DriverManager.getConnection
                                        ("jdbc:oracle:thin:@//"+
                                         prop.getProperty("airgap.host")+":"+
                                         prop.getProperty("airgap.port")+"/"+
                                         prop.getProperty("airgap.service_name"),
                                         prop.getProperty("airgap.username"),
                                         prop.getProperty("airgap.password"));

                        Statement s=con.createStatement();      //creating statement

                        ResultSet rs=s.executeQuery("select replication_server_name,"+
                                                    "       count(*)  tasks_queued,"+
                                                    "       trunc(sum(total)/1024/1024/1024,0) AS TOTAL_GB_QUEUED,"+
                                                    "       round("+
                                                    "         (cast(current_timestamp as date) - cast(min(start_time) as date))"+
                                                    "             * 24 * 60"+
                                                    "         ) as queue_minutes "+
                                                    "from RA_SBT_TASK "+
                                                    "    join ra_replication_config on (lib_name = SBT_library_name) "+
                                                    "          where archived = 'N'"+
                                                    "group by replication_server_name");   //executing statement

                        System.out.println("Replication Server,Tasks Queued,Total GB Queued,Minutes in Queue");

                        while(rs.next()){
                                System.out.println(rs.getString(1)+","+
                                                   rs.getInt(2)+","+
                                                   rs.getInt(3)+","+
                                                   rs.getString(4));
                                        }

                        con.close();    //closing connection
                        }
                catch(Exception e)      {
                        e.printStackTrace();
                                        }
        }



Java snippet main section

Below is the main section, and as you can see you can pass one of the 3 parameters mentioned earlier.





  public static void main(String[] args)
        {

//      import java.sql.*;
         airgap airgap = new airgap();   // Create a airgap object


         airgap.get_airgap_properties();      // Call the queue_select() method
         switch(args[0]) {

                case "resume":
                        airgap.resume_replication();      // Call the resume_replication() method
                        break;
                case "pause":
                        airgap.pause_replication();      // Call the pause_replication() method
                        break;
                case "query":
                        airgap.queue_select();      // Call the queue_select() method
                        break;
                default:
                         System.out.println("parameter must be one of 'resume','pause' or 'query'");
                        }
        }
}


Executing the Java code (airgap.class).

Now if you take the snipets above and put them in a file airgap.java you can compile them into a class file.

javac airgap.java
This creates a class file airgap.class

In order to connect to my oracle database, I downloaded the jdbc driver.

"ojdbc8.jar"

Now I can execute it with the 3 parameters 

$ java -Djava.security.egd=file:/dev/../dev/urandom -cp ojdbc8.jar:. airgap pause
replication server 'replairgap' paused

$ java -Djava.security.egd=file:/dev/../dev/urandom -cp ojdbc8.jar:. airgap resume
replication server 'replairgap' resumed

$ java -Djava.security.egd=file:/dev/../dev/urandom -cp ojdbc8.jar:. airgap query
Replication Server,Tasks Queued,Total GB Queued,Minutes in Queue
ra_replication_config,4,95,58


It's that easy to create a simple java program that can manage your replication server from within an Airgap.