Monday, October 31, 2016

Oracle Linux - inspect hardware for configuration management database

In many cases the ideal world and the real world are miles apart. In an ideal world every system ever put into the datacenter is entered into a configuration management database and you will be able to find out with the click of a button what specific configuration is done to a system, what its use is and what hardware components it is using. As second part of the ideal world is that all your hardware for your compute farm is made of exactly the same hardware. However, reality is grim and in general configuration management database and asset management databases are not always as up to date as one would like.

When using Oracle Enterprise Manager and placing all operating systems under the management umbrella of Oracle Enterprise Manager you will already start to get the needed input for a unified and central database where you can look up a lot of the specification of a system. However, Oracle Enterprise Manager is build around the database, management of (Oracle) applications is added at a later stage just like the management of operating systems. For non-Oracle hardware the hardware inspect is also not as deep as one would like sometimes.

However, it can be vital to have a more in depth insight in the hardware that is used in a system. For example if you want to understand how your landscape is build up from an hardware point of view. A Linux tool that might be able to help you with that is lshw which will give you with a single command an overview of the hardware present in your system.

The Oracle YUM repository has the needed packages for lshw which makes the installation of lshw extremely easy as you can use the yum command for the installation as shown below;

yum install lshw

When using lshw in a standard mode you will get a standard user friendly view of the hardware as shown below. Interesting to note, the below is running on an Oracle Linux instance on the Oracle Compute cloud so you will some interesting insights into the inner workings of the Oracle Compute cloud while reading through the below output. When running this on physical hardware the output will look a bit different and more realistic.

[root@testbox09 ~]# lshw
    description: Computer
    product: HVM domU
    vendor: Xen
    version: 4.3.1OVM
    serial: ffc59abb-f496-4819-8d0c-a6fad4334391
    width: 64 bits
    capabilities: smbios-2.4 dmi-2.4 vsyscall32
    configuration: boot=normal uuid=FFC59ABB-F496-4819-8D0C-A6FAD4334391
       description: Motherboard
       physical id: 0
          description: BIOS
          vendor: Xen
          physical id: 0
          version: 4.3.1OVM
          date: 11/05/2015
          size: 96KiB
          capabilities: pci edd
          description: CPU
          product: Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz
          vendor: Intel Corp.
          vendor_id: GenuineIntel
          physical id: 1
          bus info: cpu@0
          slot: CPU 1
          size: 2993MHz
          capacity: 2993MHz
          width: 64 bits
          capabilities: fpu fpu_exception wp vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp x86-64 constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm xsaveopt fsgsbase smep erms
          description: CPU
          vendor: Intel
          physical id: 2
          bus info: cpu@1
          slot: CPU 2
          size: 2993MHz
          capacity: 2993MHz
          description: System Memory
          physical id: 3
          capacity: 3584MiB
             description: DIMM RAM
             physical id: 0
             slot: DIMM 0
             size: 7680MiB
             width: 64 bits
             description: DIMM RAM
             physical id: 1
             slot: DIMM 0
             size: 7680MiB
             width: 64 bits
          description: BIOS
          vendor: Xen
          physical id: 4
          version: 4.3.1OVM
          date: 11/05/2015
          size: 96KiB
          capabilities: pci edd
          description: CPU
          vendor: Intel
          physical id: 5
          bus info: cpu@2
          slot: CPU 1
          size: 2993MHz
          capacity: 2993MHz
          description: CPU
          vendor: Intel
          physical id: 6
          bus info: cpu@3
          slot: CPU 2
          size: 2993MHz
          capacity: 2993MHz
          description: System Memory
          physical id: 7
          capacity: 3584MiB
     *-memory:2 UNCLAIMED
          physical id: 8
     *-memory:3 UNCLAIMED
          physical id: 9
          description: Host bridge
          product: 440FX - 82441FX PMC [Natoma]
          vendor: Intel Corporation
          physical id: 100
          bus info: pci@0000:00:00.0
          version: 02
          width: 32 bits
          clock: 33MHz
             description: ISA bridge
             product: 82371SB PIIX3 ISA [Natoma/Triton II]
             vendor: Intel Corporation
             physical id: 1
             bus info: pci@0000:00:01.0
             version: 00
             width: 32 bits
             clock: 33MHz
             capabilities: isa bus_master
             configuration: latency=0
             description: IDE interface
             product: 82371SB PIIX3 IDE [Natoma/Triton II]
             vendor: Intel Corporation
             physical id: 1.1
             bus info: pci@0000:00:01.1
             version: 00
             width: 32 bits
             clock: 33MHz
             capabilities: ide bus_master
             configuration: driver=ata_piix latency=64
             resources: irq:0 ioport:1f0(size=8) ioport:3f6 ioport:170(size=8) ioport:376 ioport:c140(size=16)
        *-bridge UNCLAIMED
             description: Bridge
             product: 82371AB/EB/MB PIIX4 ACPI
             vendor: Intel Corporation
             physical id: 1.3
             bus info: pci@0000:00:01.3
             version: 01
             width: 32 bits
             clock: 33MHz
             capabilities: bridge bus_master
             configuration: latency=0
        *-display UNCLAIMED
             description: VGA compatible controller
             product: GD 5446
             vendor: Cirrus Logic
             physical id: 2
             bus info: pci@0000:00:02.0
             version: 00
             width: 32 bits
             clock: 33MHz
             capabilities: vga_controller bus_master
             configuration: latency=0
             resources: memory:f0000000-f1ffffff memory:f3020000-f3020fff
             description: Unassigned class
             product: Xen Platform Device
             vendor: XenSource, Inc.
             physical id: 3
             bus info: pci@0000:00:03.0
             version: 01
             width: 32 bits
             clock: 33MHz
             capabilities: bus_master
             configuration: driver=xen-platform-pci latency=0
             resources: irq:28 ioport:c000(size=256) memory:f2000000-f2ffffff
       description: Ethernet interface
       physical id: 1
       logical name: eth0
       serial: c6:b0:ed:00:52:16
       capabilities: ethernet physical
       configuration: broadcast=yes driver=vif ip= link=yes multicast=yes
[root@testbox09 ~]#

Even though the above is interesting, it is not helping in building a unified database containing the physical hardware of your servers. However, lshw has some more options that can be used as shown below;

[root@testbox09 ~]# lshw --help
Hardware Lister (lshw) - B.02.17
usage: lshw [-format] [-options ...]
       lshw -version

        -version        print program version (B.02.17)

format can be
        -html           output hardware tree as HTML
        -xml            output hardware tree as XML
        -short          output hardware paths
        -businfo        output bus information

options can be
        -dump OUTFILE   save hardware tree to a file
        -class CLASS    only show a certain class of hardware
        -C CLASS        same as '-class CLASS'
        -c CLASS        same as '-class CLASS'
        -disable TEST   disable a test (like pci, isapnp, cpuid, etc. )
        -enable TEST    enable a test (like pci, isapnp, cpuid, etc. )
        -quiet          don't display status
        -sanitize       sanitize output (remove sensitive information like serial numbers, etc.)
        -numeric        output numeric IDs (for PCI, USB, etc.)

[root@testbox09 ~]#

The most interesting to note from the above is the xml option. This means you can have the above output in an xml format. We can use the xml format option in a custom check within Oracle Enterprise Manager and instruct the agent deployed on Oracle Linux to use the xml output from lshw as input for Oracle Enterprise Manager and so automatically maintain a hardware configuration management database in Oracle Enterprise Manager without the need to undertake manual actions.

For those who want to check the xml output, you can print it to screen or save it to a file using the below command;

[root@testbox09 ~]#
[root@testbox09 ~]# lshw -xml >> /tmp/lshw.xml
[root@testbox09 ~]# ls -la /tmp/lshw.xml
-rw-r--r-- 1 root root 12151 Oct 31 14:29 /tmp/lshw.xml
[root@testbox09 ~]#

Oracle Linux - resolve dependency hell

Whenever you worked with a system that is not connected to a YUM repository you will know that installing software sometimes might result in something known as dependency hell. You want to install a single package however when you try to install the RPM file manually it tells you that you are missing dependencies. As soon as you have downloaded those they will tell you that they have dependencies as well. Anyone every attempting to install software like this will be able to tell you it is not a fun job to do and it can take a lot of time. However, having an insight into dependencies upfront can safe a lot of time.

A way to ensure that you know more upfront is to use some simple commands. When you have an Oracle Linux machine already installed which can access the public internet you can for example run the yum command with the deplist attribute.

the below example is from the deplist attribute where we filter on lines containing "dependency" reason for that is, it will by default also show you the "provider" result which might be a very long list.

[root@testbox09 lynis]# yum deplist man | grep dependency
  dependency: coreutils
  dependency: rpm
  dependency: nroff-i18n
  dependency: /bin/bash
  dependency: less
  dependency: config(man) = 1.6f-29.el6
  dependency: lzma
  dependency: rtld(GNU_HASH)
  dependency: bzip2
  dependency: findutils
  dependency: gzip
  dependency: /bin/sh
  dependency: mktemp >= 1.5-2.1.5x
  dependency: groff >= 1.18
  dependency: coreutils
  dependency: rpm
  dependency: nroff-i18n
  dependency: /bin/bash
  dependency: less
  dependency: config(man) = 1.6f-30.el6
  dependency: mktemp >= 1.5-2.1.5x
  dependency: rtld(GNU_HASH)
  dependency: bzip2
  dependency: findutils
  dependency: gzip
  dependency: /bin/sh
  dependency: lzma
  dependency: groff >= 1.18
  dependency: coreutils
  dependency: rpm
  dependency: nroff-i18n
  dependency: /bin/bash
  dependency: less
  dependency: lzma
  dependency: config(man) = 1.6f-32.el6
  dependency: rtld(GNU_HASH)
  dependency: bzip2
  dependency: findutils
  dependency: gzip
  dependency: /bin/sh
  dependency: mktemp >= 1.5-2.1.5x
  dependency: groff >= 1.18
[root@testbox09 lynis]#

Here you see that a simple package as man has a lot of dependencies. Without the filtering you will have lines like the one below:

   provider: glibc.x86_64 2.12-1.7.el6_0.3

This shows the is porvided by glibc.x86_64. Having this information up front can safe a lot of time when preparing an installation on a disconnected machine. You can also use some rpm command attributes as shown below to get more insight into the dependencies a RPM file might have during installation:

  • rpm -Uvh --test *.rpm
  • rpm -qpR *.rpm

Security auditing Oracle Linux with Lynis

When it comes to security it is good practice that you undertake auditing yourself. A large set of tools are available to do auditing on Linux systems. When running Oracle Linux and you have an Oracle oriented IT footprint you most likely have Oracle Enterprise Manager running within the overall IT footprint. It is good practice to ensure that the security compliancy framework is activated for all your Oracle Linux systems. This will ensure that  the security checks are done constantly and Oracle Enterprise Manager will inform you when something is configured incorrect. However, sometimes you want a second opinion and a second check on security.

One of the tools that is available as opensource is Lynis, provided by a company called CISOFY. Lynis is an open source security auditing tool. Used by system administrators, security professionals, and auditors, to evaluate the security defenses of their Linux and UNIX-based systems. It runs on the host itself, so it performs more extensive security scans than vulnerability scanners.

Installing Lynis:
The installation of Lynis is extremely easy, the code is available on github and can be retrieved with a git clone command as shown below:

[root@testbox09 tmp]#
[root@testbox09 tmp]# git clone
Initialized empty Git repository in /tmp/lynis/.git/
remote: Counting objects: 7092, done.
remote: Compressing objects: 100% (125/125), done.
remote: Total 7092 (delta 75), reused 0 (delta 0), pack-reused 6967
Receiving objects: 100% (7092/7092), 3.26 MiB | 1.99 MiB/s, done.
Resolving deltas: 100% (5159/5159), done.
[root@testbox09 tmp]#
[root@testbox09 tmp]#

As soon as you have the Lynis code on your Oracle Linux instance it can be used.

Running Lynis:
To start the standard Lynis auditing run you can run the below command in the location you have downloaded the Lynis code from Github:

./lynis audit system -Q

This will result in an onscreen result however, the result is also stored in /var/log where the following files will be stored:

  • Test and debug information stored in /var/log/lynis.log
  • Report data stored in/var/log/lynis-report.dat

Below is an example of a Lynis run:
If you need a fast additional check to security auditing, Lynis, next to some other available tools, is a great starting point to see what best fits your need.

Application clusters in the Oracle cloud

Traditionally (in the past) applications have been deployed commonly in a single instance manner. One application server running a specific application for a specific business purpose. When the application server encountered a disruption this automatically resulted in downtime for the business.

As this is not the ideal situation systems have been build more and more in a clustered fashion. Multiple machines (nodes) running all an instance of the application and balancing load between the nodes. When one node fails the other nodes take over the load. This is great model in which your end-users are protected against the failure of one of the nodes. Commonly an engineer would take the malfunctioning node and repair the issue and introduce it back to the cluster when fixed.

With the cloud (private cloud and public cloud) and the move to a more cattle like model the use of clustered solutions starts to make sense even more. In this model the engineer who traditionally fixed an issue and re-introduced the node back to the cluster will now be instructed to only spend a very limited time in fixing the issue. If he is unable to fix the issue on a node in a given set of minutes the action will be to “destroy” the node and re-deploy a fresh node.

Due to this model engineers will not spend hours and hours on fixing an issue on an individual node, they will only spend a couple of minutes trying to fix the issue. Due to this the number of nodes and engineer can maintain will be much higher, resulting in a lower cost for maintenance per node.

To be able to adopt a model where nodes are considered replaceable cattle and no longer pets a couple of things need to be in place and needs to be taken care of. The conceptual prerequisites are the same for a private cloud as they are for a public cloud even though the technical implementation might differ.

  1. Nodes should be stateless. 
  2. Nodes should be automatically deployable.
  3. Nodes should join the cluster automatically.
  4. The cluster needs to auto-aware.

Nodes should be stateless.
This means that a node, an application node, is not allowed to have a state. Meaning, it cannot hold transactions or application data. The application node is, simply put, to execute application tasks. Whenever a node is destroyed no data will be lost and whenever a node is deployed it can directly take its role in the cluster.

Nodes should be automatically deployable
A node should be deployable automatically. This means, fully automatically without any human interaction after the moment the node is deployed. Oracle provides a mechanism to deploy new compute nodes in the Oracle Public cloud based upon templates in combination with customer definable parameters. This will give you in essence only a virtual machine running Oracle Linux (or another operating system if so defined). The node will have to be configured automatically after the initial deployment step. You can use custom scripting to achieve this or you can use Puppet or Chef like mechanisms. In general a combination of both customer scripting within the VM and Puppet or Chef is the most ideal solution for fully automated deployment of a new node in the cluster.

Nodes should join the cluster automatically 
In many cases the automatic deployment of a new node, deploying Oracle Linux and configuring the application node within the Oracle Linux virtual machine is something that is achieved. What in many cases is lacking in the fully automated way of working is that this node is joining the cluster. Depending on your type of application, application server and node-distribution (load balancing for example) mechanism the technical implementation will differ. However, it is important to ensure that a newly provisioned node is able to directly become a part of the cluster and take its role in the cluster.

The cluster needs to auto-aware
The automatic awareness of the cluster go’s partially in to the previous section where we mention the fact that a new node needs to join the cluster fully automatically and ensure the node will take the requested role in the cluster. This means that the cluster needs to be auto-aware and aware of the fact that a new node has joined. Also the cluster needs to be automatically aware of the fact if a node malfunctions. In case one of the nodes become unresponsive the cluster should automatically ensure that the node is no longer served new workloads. For example, in case of a application server cluster which makes use of load-balancing, the malfunctioning node should be taken out of the balancing algorithm until the moment it is repaired or replaced. When using a product which is developed to be cluster aware, for example Oracle Weblogic this might not be that hard to achieve and the cluster will handle this internally. When you use a customer build cluster, for example a micro-services based application running

with NGINX and Flask and depending on load-balancing you will have to take your own precautions and ensure that this auto-aware mechanism is in place.  

Oracle Public cloud conceptual deployment
When we use the above model in the Oracle Public cloud conceptual deployment could look like the one below where we deploy a web-based application.

In this model, the API server will create a new instance for one of the applications in the application cluster it is part of. As soon as this is done the new server will report back to the API server. Based upon the machine will self-register at puppet and all required configuration will be done on the node. The latest version of the application software will be downloaded from the GIT repository and as a last step the new node will be added to the load balancer cluster to become a full member of the application cluster.

The above example uses a number of standard components from the Oracle cloud, however, when deploying a full working solution you will have to ensure you have some components configured specifically for your situation. For example, the API server needs to be build to undertake some basic tasks and you will have to ensure the correct puppet plans are available on the puppet server to make sure everything will be automatically configured in the right manner.

As soon as you have done so however you will have a fully automatic scaling cluster environment running in the Oracle Public Cloud. As soon as you have done so for one environment this is relatively easy to change into other types of deployments on the same cloud. 

API based architecture for web applications in the Oracle Cloud

When it comes down to developing websites and web applications a lot has changed since the time I developed my first web based applications. Changes ways of developing, changing platforms and frameworks, changing programming languages and changing architectures. Almost every company today is considering, having a website, as a given. Where websites used to be a relative static brochure showing what a company was about we are already well on the track of making websites a customer portal and application. Websites and web based applications are becoming more and more a part of the overall customer experience and customers expect that they can do everything and find everything they might want on a corporate website.

This makes that a website or web based application is becoming more and more critical to the success. A failing website, a slow website or a website unable to deliver the experience expected by the customer will have a direct negative effect on the customer satisfaction and as a result of this a decreasing willingness to do business with a company.

To cope with the growing importance of web based applications and to cope with the requirement to be scalable architectural principles used to develop web based applications are changing rapidly. One of the examples currently seen is the change to API centric development.

Traditional architecture
The traditional, as shown in figure 1, is based upon a direct connection between the web application and the Oracle database instance (or any other database for that matter). A customer would interact with the web application using HTTP or HTTPS (A) and the web application would interact with the database (B) using SQL.NET whenever needed.

figure 1 - traditional architecture 

Simple API based architecture
When looking at applications that are currently build a number of web based applications are still being developed using the more traditional architecture as shown above. Companies who do require their applications to be more fault tolerant, scalable and secure are adopting new architecture principles. Commonly a more API based architecture is being used where the web application is not directly communicating with the database. In those cases the customer facing application is communicating with an API and the API service will communicate with the database on behalf of the web application.

Another observation that can be made is that the trend is that more and more open source frameworks and solutions are used. In the below example you will for example see NGINX and Flask being deployed on Oracle Linux for serving the API’s to the web applications.

In the below model the web application is not directly communicating with the Oracle Database. In this model a customer would interact with the web application using HTTP or HTTPS (A) and the web application would interact with the API’s (B) using HTTPS whenever needed. The API server running NGINX and Flask will interact with the database server (C) using SQL.NET whenever needed.

figure 2 - API based architecture

As shown the above diagram in figure 2 shows an additional layer is introduced in the form of NGINX and flask deployed on Oracle Linux. The use of NGINX and flask is only an example which is chosen in this post as both as it is becoming more and more a popular deployment used in this type of scenarios. Other solutions can be used also in this place and play this role.
Added value

Companies who do require their applications to be more fault tolerant, scalable and secure are adopting new architecture principles.

The added options for scalability and fault tolerance are provided in the “layer” by having the option to create a cluster of servers providing the API functions to  the web application layer. As shown in figure 3, you can create a cluster of NGINX and Flask nodes running on Oracle Linux to your overall deployment architecture. When running a deployment as shown in this example on a public or private cloud you can quickly scale up and down your API layer when needed.

What you have to take into account when deploying a automatic or semi-automatic scaling of your API layer is the way you will distribute the load over the different nodes and how your routing / load balancing solution will be made aware of nodes that are added or removed.

By having a clustered API layer you will at the same time add on resilience against the failure of a node. In the deployment model shown in figure 3 the loss of a single node will not stop your web application to function. This provides an additional insurance that your application will be available to end users.

Figure 3 - API cluster
The added security by using this deployment model, regardless of the use of a clustered layer or not, comes from the fact that your customer facing web application is no longer directly connected to your database. In a traditional web configuration a malicious user could try to exploit for example SQL injection and regardless of the fact if that succeeded he would directly interact with the database. In the API based model injection would result in sending injected code to the API and not to the database directly. 

In this model you can add security measures in the web application itself, the API layer and the database opposed to having only security measures in the web application and the database. By adding this additional layer you can have build the API to function as an additional security tollgate. 

Enabling the next step
An added benefit of this model is that it enables enterprise to prepare, or adopt the next step in architecting applications. The model described above is ideally fitted to develop microservices oriented architecture applications. In a microservices oriented architecture you can use the API layer to develop and run your microservices. 

Applications build upon microservices are currently seen as the way forward and the next step to build robust, flexible and scalable applications. 

Moving it all to the cloud
The above example is drawn in a way that hints at a traditional deployment model. As stated, when deployed in a private or public cloud you will have the added benefits of options to quickly scale up and scale down the number of nodes in your API layer.

When selecting a public cloud solution the option for Oracle Public Cloud might be quite obvious. As Oracle provides both Oracle Database and Java a Oracle database and Java oriented application might find its best public cloud partner with Oracle. 

Figure 4 - cloud deployment

Database cloud deployment
The deployment of your database in the Oracle public cloud can be done by making use of the Oracle Public Cloud database service.

Java application deployment
For your java application you can make a selection out of a couple of products. The Oracle Java Cloud Service might make the most sense. However, you might also be able to make use of the Oracle Application Container Cloud or you can deploy everything yourself by making use of the Oracle Compute service which provides you raw computing power in the form of a Oracle Linux VM.

API layer deployment
Depending on the language you want to develop your application in and the level of “outside of the box” development you might want to do you can select the Oracle Integration cloud, you can also make use of the Oracle Application Container Cloud which enables you to deploy docker containers and deploy your API custom code in the container. When using the combination of NGINX and Flask and you focus on developing Python code (which is a very good choice for the API layer in my personal opinion) you might want to make use of the Oracle Compute service and the Oracle Linux VM’s provided by this public cloud service. 

Bridging the hybrid database gap for Oracle with memcached

Enterprises are starting to adopt the hybrid cloud model, this means that in some cases applications that are normally hosted on-premise are moved into the cloud in full. This also means that in some cases parts of an application are moved to the cloud and that some systems on which an application depends will stay on premise and will not move to the cloud.

A common question asked when discussing moving parts of an IT estate to the cloud is how to bridge the gap between the systems in the cloud and the systems that remain on premise. The below diagram shows a common deployment in enterprises where one application is depending on the database of another application.

Figure 1 - shared database deployment

In this deployment shown in figure 1 the following applies:
  • Application 0 makes use of database instance A
  • Application 1 makes use of database instance B and makes use of database instance A

When deployed in a single datacenter the connection between the applications and the databases will all be equal (in general). No significant delay or latency is to be expected and the user experience is equal for both the users of application 1 and application 2

Moving to the cloud
In case a requirement is stated that application 2 is moved to the cloud and application one (for a specific reason) will stay on premise, including the directly associated database, the deployment model will start to look as shown in figure 2. 

Figure 2 - Crossing the line

In this case the worry of many companies is with connection A shown in the above figure worry about potential latency over connection A, they worry what might happen to application availability of application 1 in case the connection becomes unavailable for a moment. 

A possible solution is making use of a caching mechanism, a solution often used to alleviate workloads from a database server and speed up application performance. A caching solution like this can also be used to resolve the issue of bridging the gap between cloud based applications and on premise data stores. Do note, data stores, as this can also be something else than the Oracle database used in this example. 

Using cache as a bridge
A good open source solution for this is memcached. A large number of enterprises use memcached. It is good to realize what memcached is, memcached is an in memory key-value store for small chunks of arbitrary data (strings, objects) from results of database calls, API calls or page rendering. If your application is able to function based upon this principle memcached is a very good solution to implement and mitigate the risk of a breaking or limited connection between application-1 and database A. This would result in a deployment as shown in figure 3.

figure 3 - using memcached

Understanding memcached
To fully grasp the possibilities it is important to take a closer look at memcached and how it can be used. As stated, memcached is an open source in memory key-value store for small chunks of arbitrary data (strings, objects) from results of database calls(, API calls or page rendering). You will be able to run memcached on an Oracle Linux instance, this can be an instance in the Oracle public cloud as part of the compute service (as shown in this example) or it can be an Oracle Linux instance deployed in a private cloud / traditional bare-metal deployment. 

Optimize the database footprint with AWR Warehouse

In general Oracle databases do play a role in wider critical production system used to provide vital services to the business. In cases where databases are deployed and they are being used to almost the maximum capabilities it is important to ensure you have a strategy for monitoring and tuning performance.

In those cases the majority of the companies will invest in implementing the correct monitoring and management solutions. For Oracle databases this is commonly, and for good reasons, Oracle Enterprise Manager. Oracle Enterprise Manager will provide a lot of options for monitoring performance out of the box and as part of the free (gratis) base installation.

When dealing with critical database systems, as described above, that are in need of more thorough performance monitoring and tuning companies will make use of AWR. A less known options is AWRW or in full Automatic Workload Repository Warehouse.

AWR Warehouse
The AWR Warehouse is a part of Oracle Enterprise Manager and provides a solution to one of the shortcomings of “standard” AWR and provides a lot more options to DBA’s and performance tuning specialist. With “standard” AWR you will be able to keep an 8 day set of data on your local database server. The advantage of AWR Warehouse is that all AWR data is collected and stored in one central warehouse as part of Oracle Enterprise Manager.

This provides a number of direct advantages as listed below;
  • The ability to store a long(er) period of AWR data
  • The ability to easily compare AWR data from different databases in one single location
  • Use out of the box diagnostics features from OEM on the historical AWR snapshots
Query, analyze and compare
One of the things AWR Warehouse will be supporting you in is to make your performance tuning team more efficient. With AWR Warehouse you have to option to query the AWR snapshots directly, you can analyze the data and run this same query for another database instance or for all database instances on your engineered system or the entire IT footprint. 

As an example, if you find a sub-optimal implementation of SQL code in an isolated database you might be interested if this same implementation is used in other databases across your estate. By making use of AWR Warehouse you will have the ability to check with one single query on which systems this also might be an issue and where you might need to do code refactoring or performance tuning. 

The business benefit
The benefits to the business are obvious. By enabling performance tuning teams, development teams and DBA’s to analyze all databases at once using a centralized AWR Warehouse the time to find possible performance issues  is shortened. 

The ability to and the effectiveness of analyzing AWR reports and finding possible performance issues is drastically improved while the time needed for the analysis is shortened. 

AWR Warehouse will give the ability to move away from case-by-case tuning. It provides the ability to move to a more overall tuning strategy. In general tuning teams and DBA’s will work on a case by case basis where they take an isolated issue in a single database. Tracking down if the same type of performance issues is in another database somewhere in the vast IT footprint is often a tedious task which is not performed. AWR Warehouse provides the option to run the same diagnostics you run for a single isolated database on all databases in your IT footprint. This moves a company into a wider, a better, tuning strategy which directly benefits the business. 

By optimizing your database, by finding issues in your SQL code you will be able to make your database instances more effective. Ensuring you do remove bottleneck and remove sub optimal implementations that use far more resources than required. Essentially it will free compute resources by doing this which can be used for other purposes. It provides the ability to run more database instances on existing hardware or grow load on your systems without the need to purchase additional hardware. 

The business case 
The business case for purchasing the required licenses needed to use AWR Warehouse need to involve a couple of data points to make a fare business case to invest into this. 
  • The number of critical databases in need of tuning
  • The amount of FTE spending time on tuning
  • The (potential) los in revenue due to slow performance
  • The (potential) gain in freeing compute resources due to tuning
  • The (potential) not needed investment in hardware expansion
Those pointers should be incorporated in a business case, next to the standard data points that you would include in a standard business case. Failing to include the above will result in a sub-optimal business case. 

General advice
In general the advice is to look into using AWR Warehouse when: 
  • You do have an Oracle database footprint which is significant. Significant in this case is open for discussion, we use a 15 production database threshold.  
  • You do have the need to tune your databases in an optimized manner without the need to have a significant number of people investing in optimizing. 
  • You have a system sizing which is “tight” and you need to ensure your databases are optimized in the most optimal way
  • You have a system sizing which is “generous” and you like to limit the number of resources per database to free resources for other use (other / more database instances on the same hardware)
  • You foresee that the load on your systems will grow in the near future and you need to ensure you are prepared for this and database response times will stay acceptable by the database. 

Oracle Linux and Oracle Cloud as alternative rsyslog consolidation target

One of the main com pliancy benchmarks which is used to build a lot of the security standards on is the benchmark developed by CIS, Centre of Internet Security. In general following the CIS guidelines for security and implementing them on your systems is a good start to become compliant with a lot of security standards. For example the DOD security standards are based for a part of the CIS benchmark as well as a lot of other security standards who find their origin in the CIS benchmark.

Within the Oracle Linux benchmark one of the topics is the installation, configuration and use of rsyslog. Some of the implementation points are scored when doing an official assessment based upon the CIS benchmark, some are not. One of the topics that are not scored under the rsyslog part is “configure rsyslog to Send Logs to a Remote Log Host”. This will take care, when implemented, to send logfiles to another host to ensure they are immutable on the machine itself in case it is hacked. Common practice when someone hacks into your server is to clean the logfiles, ensuring you have the logfiles in another location will make it harder to remove traces of a hack.

Remote logging in general:
In general it is good practice to ensure your operating system logs are moved to another location so they cannot be wiped by someone who hacked your system. Implementing the configuration for rsyslog to send logs to a remote log host is a good practice seen from the point of view you want to move away your logfiles from the server and secure them at a central location.

Additionally it is beneficial to have your logfiles all consolidated in one location so you can analyze all your systems in one place instead of having to log into every server to go through logs. If you do not have a centralized solution analyzing your entire landscape will become hard and will become almost impossible for a human.

The alternative: 
Even though using rsyslog configuration to send information to a central log server is working very well better solutions are in place at this moment. The most popular and the most commonly used solution is using elasticsearch and kibana for this in combination with logstash. Commonly refered to as the ELK stack which stands for ELasticsearch & Kibana. ELasticsearch is an opensource storage and search solution where Kibana is (also opensource) a dashboard and analysis tool.

The ELK based solutions:
As stated, the ELK based solution in combination with logstash is a popular solution for small and large IT footprints where you have the need to consolidate logging and analyze it. Using an ELK based solution is a great alternative to the rsyslog solution as stated in the benchmark documents for Oracle Linux as they are written down by CIS.

Building the architecture:
The below diagram shows how you can ship all your local logs to a central Elasticsearch and Kibana implementation.

Logstash shipper
Each Oracle Linux node will have a Logstash shipper deamon running. What the Logstash shipper will do in esscence is reading all the logfiles you indicated in the configuration and will send every line to Redis as soon as it finds a new line in the logfile.

All the Logstash shipper deamons will send the logfile lines to Redis. Redis will act as a "buffer" and message broker. This will ensure that if Logstash indexer is unable to receive a message Redis will keep it in memory and deliver it as soon as the indexer can receive it. Good practice is to build Redis as a cluster.

Logstash indexer
The logstash indexer will receive all message from Redis, possibly filter them, and ensure the record is stored in elasticsearch.

Elasticsearch is a search engine based on Lucene and will store all records of all servers that are sending information to it. In essence this is a searchable collection of all the logfile entries of all the servers you have included in this setup and instructed to consolidate log files centrally.

Kibana is an open source data visualization plugin for Elasticsearch and will be able to visualize and help in understanding the massive amounts of data from all the log files your servers are sending to Elasticsearch .

Hybrid cloud
As enterprises move the a more hybrid cloud model you will have servers on premise, in private cloud and in the public cloud. The desire from many IT departments is to ensure they will have a single consolidation of all log files in one place. Using the Oracle Public Cloud, the Oracle Compute service to setup a stack capable of hosting an ELK consolidation platform.

The above diagram shows how an architecture could look like if you consolidate all logging with ELK on the Oracle Public Cloud. This includes machines running on a private cloud as well as the Oracle public cloud and traditional bare metal servers. 

Wednesday, October 26, 2016

Elasticsearch automatic configure listening address on Oracle Cloud

In an earlier post we showed how we can install Elasticsearch on Oracle Linux within the Oracle Cloud for testing purposes. In this post we showed that using a default installation will make it so that your Elasticsearch node will listen to the IPv4 and IPv6 for your localhost. Even though this is fine for a single test system where you like to explore locally some of the options of Elasticsearch it is not making much sense if you want to integrate this with other systems. In this case you will have to ensure that you are able to access Elasticsearch  from external.

As we are running our Elasticsearch node on Oracle Linux on the Oracle Public IaaS cloud we can use the meta-data API as one of the options to retrieve the address we want use as the listening address and configure this in Elasticsearch.

To ensure Elasticsearch is listening on another address than the localhost address we have to make sure the configuration value in /etc/elasticsearch/elasticsearch.yml is set to the address we want it to listen on. The below script is an example of a script snippet you can use in a wider deployment script:

#  This is script is used to configure the settings for 
#  elasticsearch to ensure it is listening to the IP set for local-ipv4
#  in the Oracle public IaaS cloud on Oracle Linux. We will retrieve
#  the local IPV4 adress and add it to the elasticsearch configuration 
#  file /etc/elasticsearch/elasticsearch.yml after which we restart
#  elasticsearch to ensure the setting new setting is active. This will
#  ensure that elasticsearch is now accessible outside localhost

echo " $(curl -s" >> /etc/elasticsearch/elasticsearch.yml
service elasticsearch restart

For example, you can add this to the script used to install Elasticsearch on Oracle Linux. This script can also be found on github. As is the case for the above snippet which is available on Github on this location

Tuesday, October 25, 2016

Oracle Linux and understanding Oracle Cloud IP's

When working with the Oracle Public Cloud the first time and trying to bind services on your Oracle Linux instance to the public internet you might be a bit confused in first instance. If you look from a cloud portal point of view you will find two IP addresses, One public IP and one private IP. when you connect to your Linux machine remotley via SSH you will use the public IP however we you check the instance you will find only a single NIC containing the private IP.

As an example; the below screenshot from the cloud portal shows both the internal and the eternal IP;

When connected to the Oracle Linux instance we can check the IP's and we will notice only the private IP is available:

[opc@testbox08 ~]$ ifconfig
eth0      Link encap:Ethernet  HWaddr C6:B0:36:23:FE:CE
          inet addr:  Bcast:  Mask:
          inet6 addr: fe80::c4b0:36ff:fe23:fece/64 Scope:Link
          RX packets:2300612 errors:0 dropped:2 overruns:0 frame:0
          TX packets:643213 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:691364657 (659.3 MiB)  TX bytes:144613834 (137.9 MiB)

lo        Link encap:Local Loopback
          inet addr:  Mask:
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:2551 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2551 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:40849225 (38.9 MiB)  TX bytes:40849225 (38.9 MiB)

[opc@testbox08 ~]$

Oracle has a network translation on the edge of the network that will translate the external IP to the internal IP and will tunnel all traffic for the external IP to the internal IP while going through a firewall. This means you can control which traffic on the external IP will actually end up being tunneled to the internal IP address.

Even though this sounds like something you do not have to worry about to much, for some services it is vital to understand what the external IP is and not only what the internal IP is.

Monday, October 24, 2016

Oracle Cloud - persistent IaaS storage

In a recent post where I discussed working with orchestration in the Oracle IaaS cloud I made a warning that if you stopped the orchestration that also created your storage you where in danger of wiping out your storage all together for that instance.

In all reality, someone within Oracle did take that with him and in the latest version of the Oracle cloud this issue / feature has been changed. The new version shows a new option when you create a new instance. The below screenshot shows you that you have a new option to ensure that your storage will be persistent even if you stop your instance orchestration.

When you have a machine you want to make sure the storage will not be deleted when stopping your orchestration you will have to keep the "delete on termination" unchecked.

In all honesty, this is a great improvement on the way the Oracle IaaS cloud service is handling storage.

Friday, October 21, 2016

Oracle Linux : sending mail with Sendmail

There can be many reasons why you need to send mail from your Linux host to some mail account. For Example, you have an application that needs to send out mail to end users, in those cases you will use a central SMTP mail relay server within your corporate IT footprint. However, in some cases you want to have scripting that makes use of a local SMTP instance that will send the mail for you. This can be direct to the end user or using a SMTP relay server.

In cases you want to have your local Linux machine to send out the messages directly to the recipient you will have to ensure that (A) your machine is allowed to make the connection outside of your firewall to the recipient mail server and (B) you will have to make sure you have a local MTA (Mail Transfer Agent) in place. The best known MTA’s are Sendmail and Postfix. We will use Sendmail as an example while showing how to send mails from an Oracle Linux machine to a gmail account (or whatever account you require) by using simple bash commands and scripting.

Install Sendmail on Oracle Linux
Installing Sendmail is most likely the most easy step in the entire blogpost. You can install Sendmail by making use of the default Oracle Linux YUM repositories. Install Sendmail is done with the below command. You will notice we install Sendmail and sendmail-cf. Sendmail-cf is used to make your life much more easy when configuring and reconfiguring Sendmail.

yum install sendmail sendmail-cf

For some reason Sendmail migt be giving you some strange errors every now and then right after you installed it and start using it. A good practice to ensure everything is ready to go is to stop and start the sendmail service again as shown in the example below.

[root@testbox08 log]#
[root@testbox08 log]# service sendmail status
sendmail (pid  968) is running...
sm-client (pid  977) is running...
[root@testbox08 log]#
[root@testbox08 log]# service sendmail stop
Shutting down sm-client:                                   [  OK  ]
Shutting down sendmail:                                    [  OK  ]
[root@testbox08 log]#
[root@testbox08 log]# service sendmail start
Starting sendmail:                                         [  OK  ]
Starting sm-client:                                        [  OK  ]
[root@testbox08 log]# service sendmail status
sendmail (pid  1139) is running...
sm-client (pid  1148) is running...
[root@testbox08 log]#
[root@testbox08 log]#

After this your sendmail installation on Oracle Linux should be ready to go and you should be able to send out mails. We can easy test this by sending a test message.

Sending your first mail with sendmail
Sending mail with Sendmail is relative easy and you can make it even easier by ensuring your entire message is within a single file. As an example, I created the file /tmp/mailtest.txt with the following content:

Subject: this is a test mail

this is the content of the test mail

This would mean the mail is send to my gmail account, the subject should be “this is a test mail” and the body of the mail will show ” this is the content of the test mail”. Sending this specific mail (file) can be done by executing the below command:

[root@testbox08 tmp]# sendmail -t /tmp/mailtest.txt

However, a quicker way of ensuring your message is processed is removing the “To:” part and using a command like shown below:

[root@testbox08 log]# sendmail < /tmp/mailtest.txt

The below screenshot shows that the mail has arrived in the mailbox, as expected. You can also see it has gotten the name of the account and the fully qualified hostname from the box I used to send the mail from. In this case this shows a Linux host located in the Oracle Public cloud.

Making your reply address look better
The above mail looks a bit crude and unformulated. Not the mail you would expect to receive as an end user, and especially not as a customer for example. Meaning, we have to make sure the mail that is received by the recipient is formatted and in a better way.

The first thing we like to repair is the name of the sending party. We would, as an example, have the name shown as "customer service" and the reply address should become To do so we add a "Reply" line to the /tmp/mailtest.txt file which looks like:

From: customer service

Due to the formating it is not showing as it is rather showing in the way we commonly see and as is shown in the screenshot below:

Giving the mail priority
Now, as this is a mail from customer service informing your customer that his flight has been canceld  it might be appropriate to this mail a priority flag.

Doing more with headers
In essence you can define every mail header you like and which is understandable and which is allowed. To get an understanding of the type of headers that you can use and which are common you can have a look at RFC 2076 "Common Internet Message Headers".

Sending HTML formatted mail
It is quite common to use HTML to format emails. Ensuring you can send your email in a HTML formatted manner requires that you have the right headers in your email and you format your message in the appropriate HTML code (please review the example on github).

An important thing to remember is that not everyone is able to read HTML. For this it is good to use the "content-Type: multipart/alternative;" header in combination with the "Content-Type: text/html; charset=UTF-8". This will allow you to make a distinct between HTML formatted mail and non-HTML formatted mail.

All the examples below can be found in the example mail file "/tmp/mailtest.txt" which is available on github.

Deploying Elasticsearch test node on Oracle Linux

In some cases you want to have a certain type of service running on your Oracle Linux instance just for testing and playing purposes. In my case am experimenting with Elasticsearch from Elastic and I need to install single node Elasticsearch instances every now and than on a new and fresh Oracle Linux instance. Even though it is not that much work it makes more sense to build a script for this.

The below script will install Elasticsearch on Oracle Linux 6 3.8.13- by simply running the script. Elasticsearch is a search engine based on Lucene. It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents. Elasticsearch is developed in Java and is released as open source under the terms of the Apache License. Elasticsearch is the most popular enterprise search engine followed by Apache Solr, also based on Lucene.


function runMain {

function packageInstalled () {
  numberOfPackages=`yum list installed | grep $1 | wc -l`
  if [ "$numberOfPackages" -gt "0" ];
       echo "true"
       echo "false"

function installJava {
  javaInstalled=`packageInstalled java-1.8.0-openjdk`
  if [ "$javaInstalled" = "true" ];
       echo "java is already installed"
      echo "installing java"
      yum -y install java-1.8.0-openjdk

function installElasticsearch {
  elasticsearcInstalled=`packageInstalled elasticsearch`
    if [ "$elasticsearcInstalled" = "true" ];
       echo "elasticsearch is already installed"
       echo "importing elastic GPG key"
       rpm --import

       echo "adding elastic repository to yum"
       echo "" >> /etc/yum.repos.d/public-yum-ol6.repo
       echo "[elastic]" >> /etc/yum.repos.d/public-yum-ol6.repo
       echo "name=Elasticsearch repository for 2.x packages" >> /etc/yum.repos.d/public-yum-ol6.repo
       echo "baseurl=" >> /etc/yum.repos.d/public-yum-ol6.repo
       echo "gpgcheck=1" >> /etc/yum.repos.d/public-yum-ol6.repo
       echo "gpgkey=" >> /etc/yum.repos.d/public-yum-ol6.repo
       echo "enabled=1" >> /etc/yum.repos.d/public-yum-ol6.repo

       echo "installing elasticsearch"
       yum -y install elasticsearch

function startElasticsearch {
  echo "starting elasticsearch"
  service elasticsearch start


The script has been tested on Oracle Linux 6 running on the Oracle Public Cloud. After completing the script you can see that Elasticsearch is running and listening on port 9200 on both IPv4 and IPv6 by executing the below command:

[root@testbox08 init.d]#
[root@testbox08 init.d]# netstat -ln | grep 9200
tcp        0      0 ::ffff:       :::*                        LISTEN
tcp        0      0 ::1:9200                    :::*                        LISTEN
[root@testbox08 init.d]#
[root@testbox08 init.d]#

To test if Elasticsearch indeed is working and responding you can do a curl against port 9200 to see the default result from Elasticsearch after a vanilla installation.

[root@testbox08 init.d]#
[root@testbox08 init.d]# curl http://localhost:9200/
  "name" : "King Bedlam",
  "cluster_name" : "elasticsearch",
  "cluster_uuid" : "5fd7bOG-RP6MrTbI3denuA",
  "version" : {
    "number" : "2.4.1",
    "build_hash" : "c67dc32e24162035d18d6fe1e952c4cbcbe79d16",
    "build_timestamp" : "2016-09-27T18:57:55Z",
    "build_snapshot" : false,
    "lucene_version" : "5.5.2"
  "tagline" : "You Know, for Search"
[root@testbox08 init.d]#
[root@testbox08 init.d]#

Oracle Linux - Checking installed package

In some cases you want to verify if a package is installed on your Oracle Linux instance within a bash script. You query what is installed by using the "list installed" option for the yum command. However, this is giving you a more human readable result and not something that works easy in a the flow of a script. In essence you would like to have a boolean value returned to tell you if a package is installed or not on your Oracle Linux instance.

The below code example is a bash script that will exactly do so. Within the example you see the packageInstalled function which takes a variable for the package name you are looking for. The result will be true or false.


function packageInstalled () {
     numberOfPackages=`yum list installed | grep $1 | wc -l`
     if [ "$numberOfPackages" -gt "0" ];
           echo "true"
         echo "false"

packageInstalled wget

In the example we are checking the installation of the wget package. You can change wget for whatever you need to be sure is installed. Using this building block function can help you to write a more complex script for installing packages when needed. 

Check Exadata key InfiniBand fabric error counters via Oracle Linux

Checking key InfiniBand fabric error counters on your Exadata machine is good practice and can be done from the Linux operating system. This check is part of the exachk. The exachk should actually be run regularaly to ensure your Oracle Engineered system is in a good shape. However, a lot of people also tend to ensure that some checks are done more regular. As part of a list of checks that can be done more regular is checking on key InfiniBand fabric error counters.

For checking the key InfiniBand fabric error counters on your Oracle Exadata you can use the exachk report, however, you can also do this directly from the Oracle Linux operating system. The code used for this check is shown in the details pages of the exchck report (and shown below).

if [[ -d /proc/xen && ! -f /proc/xen/capabilities ]]
  echo -e "\nThis check will not run in a user domain of a virtualized environment.  Execute this check in the management domain.\n"
    RAW_DATA=$( | egrep 'Recover.*SymbolError|SymbolError.*Recover|SymbolError|LinkDowned|RcvErrors|RcvRemotePhys|LinkIntegrityErrors');
  if [ -z "$RAW_DATA" ]
    echo -e "SUCCESS: Key InfiniBand fabric error counters were not found"
    echo -e "WARNING: Key InfiniBand fabric error counters were found\n\nCounters Found:\n";
    echo -e "$RAW_DATA";

You can easily take this part of the code and put this into a custom bash script to be executed by your Exadata administrators. However, if you do implement a large set of custom build checks using Oracle Enterprise Manager you can also use the above code to build a custom check.

When all results are good you should receive the message "SUCCESS: Key InfiniBand fabric error counters were not found". When building a custom OEM check it might be better to change this into a numeric value. For example, all OK should represent 0.

In case there are errors you will receive a message like the one below shown as an example:

WARNING: Key InfiniBand fabric error counters were found

Counters Found:

   GUID 0x21286ccbaea0a0 port ALL: [SymbolErrorCounter == 2]
   GUID 0x21286ccbaea0a0 port 34: [SymbolErrorCounter == 2]

Using SQlite on Oracle Linux

Most people who are working with Oracle technology and who are in need of a database to store information will almost by default think about using an Oracle Database. However, even though the Oracle database is amazing, it is not a fit for all situations. If you are in need to just store some information locally or for a very small application and you do not worry too much about things like performance you might want to turn to other solutions.

In cases where you need something just a bit more smart and easy to use than flat file storage or JSON/XML files you can parse and a full Oracle database is overkill you might want to look at SQLite. SQLite is an open source software library that implements a self-contained (single file), zero-configuration, transactional SQL database engine. SQLite supports multi-user access, but only a single user can update the database at a time. It is largely an "untyped" system and all data is stored as strings.

SQLite is by default shipped with Oracle Linux 7 and is widely used in scripting whenever a semi-smart storage of data is needed. Understanding SQLite and investing some time into it well worth it if you regularly develop code and scripting for your Oracle Linux systems or for other purposes.

Interacting with SQLite
The easiest way to explore SQLite is using the SQLite command line. When on your Linux shell you can use the sqlite3 command to open the SQLite command line. The below example shows how we open a new database, create a table, write some data to the table, query it and after that exit. As soon as you open a new database that does not exist and write something to this database the file will be created on the filesystem.

[root@testbox08 tmp]#
[root@testbox08 tmp]# ls showcase.db
ls: cannot access showcase.db: No such file or directory
[root@testbox08 tmp]#
[root@testbox08 tmp]# sqlite3 showcase.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table objecttracker (object, version, object_id INTEGER);
sqlite> insert into objecttracker values ('api/getNewProduct','1.3',10);
sqlite> insert into objecttracker values ('api/getProductPrice','1.3',20);
sqlite> select * from objecttracker;
sqlite> .exit
[root@testbox08 tmp]
[root@testbox08 tmp]# ls showcase.db
[root@testbox08 tmp]#

As you can see from the above example we do not explicitly create the file showcase.db it is simply created the moment we start writing something to the database. In our case the first write is the creation of the table objecttracker.

Even though knowing your way around the SQLite command line is something you have to understand the more interesting part is using it in a programmatic manner.

Coding against SQLite
There are many ways you can interact and code against SQLite, a large number of languages provide a standard way of interacting with SQLite. However, if you simply want to interact with it using a bash script at your Oracle Linux instance you can very well do so.

Working from bash with SQLite is failry simple if you understand the SQLite command line. You can simply wrap all commands together with the command used to call the SQLite database. As an example, if we want to query the table we just created and have the output we can use the below:

[root@testbox08 tmp]#
[root@testbox08 tmp]# sqlite3 showcase.db "select * from objecttracker;"
[root@testbox08 tmp]#
[root@testbox08 tmp]#

As you can see we now have the exact same output as that we got when executing the select statement in the SQLite command line.

This means you can use the above way of executing a SQLite command in a bash script and parse the results in the bash code for future use. In general SQLite provides you a great way to store data in a database without the need to install a full fletched database. In a lot of (small) cases a full database such as the Oracle database is an overkill as you only want to store some small sets of data and retrieve it using SQL statements. 

Ensuring ILOM power up on Exadata with IPMI

Like it or not, power interruptions are still a thread to servers. Even though servers ship with dual power supplies and if done correctly they should be plugged into different power supplies within the datacenter a power outage can still happen. Even tough datacenters should have backup power and provide uninterrupted power to your machines it still might happen. To ensure all your systems are behaving in the right way when power comes back on you can make use of some settings within the IPMI configuration.

The Intelligent Platform Management Interface (IPMI) is a set of computer interface specifications for an autonomous computer subsystem that provides management and monitoring capabilities independently of the host system's CPU, firmware (BIOS or UEFI) and operating system. IPMI defines a set of interfaces used by system administrators for out-of-band management of computer systems and monitoring of their operation. For example, IPMI provides a way to manage a computer that may be powered off or otherwise unresponsive by using a network connection to the hardware rather than to an operating system or login shell.

Oracle Servers have IPMI on board and it is good practice to ensure you make use of the HOST_LAST_POWER_STATE information to ensure your server boots directly when power comes back online or is not booting up when the server was already down during the power outage.

To verify the ILOM power up configuration, as the root userid enter the following command on each database and storage server:

if [ -x /usr/bin/ipmitool ]
ipmitool sunoem cli force "show /SP/policy" | grep -i power
/opt/ipmitool/bin/ipmitool sunoem cli force "show /SP/policy" | grep -i power

When running this on an Exadata the output varies by Exadata software version and should be similar to:

Exadata software version or higher:

Exadata software version or lower:

If the output is not as expected you will have to ensure make the settings correct so your Exadata machine boots directly after the power is restored. 

Friday, October 14, 2016

Using osquery in Oracle Linux

Recently the guys at facebook released an internal project as opensource code. Now you can make use of some of the internal solutions facebook is using to keep track and analyse their compute nodes in the facebook datacenter. Osquery allows you to easily ask questions about your Linux, Windows, and OS X infrastructure. Whether your goal is intrusion detection, infrastructure reliability, or compliance, osquery gives you the ability to empower and inform a broad set of organizations within your company.

What osquery provides is a collector that on a scheduled basis will analyse your operating system and store this information in a sqlite database local on your system. In essence osquery is an easily configurable and extensible framework that will do the majority of collection tasks for you. What makes it a great product is that it is all stored in sqlite and that enables you to use standard SQL code to ask questions about your system.

After a headsup from Oracle Linux product teams about the fact that facebook released this as opensource I installed it on an Oracle Linux instance to investigate the usability of osquery.

Installing osquery
Installation is quite straightforward. A RPM is provided which installs without any issue on Oracle Linux 6. Below is an example of downloading and installing osquery on an Oracle Linux 6 instance.

[root@testbox08 ~]#
[root@testbox08 ~]# wget "" -b
Continuing in background, pid 28491.
Output will be written to “wget-log”.
[root@testbox08 ~]#
[root@testbox08 ~]# ls -rtl osq*.rpm
-rw-r--r-- 1 root root 13671146 Oct  4 17:13 osquery-2.0.0.rpm
[root@testbox08 ~]# rpm -ivh osquery-2.0.0.rpm
warning: osquery-2.0.0.rpm: Header V4 RSA/SHA256 Signature, key ID c9d8b80b: NOKEY
Preparing...                ########################################### [100%]
   1:osquery                ########################################### [100%]
[root@testbox08 ~]#
[root@testbox08 ~]#

When you check you will notice that osquery will not start by default and that some manual actions are required to get it started. In essence this is due to the fact that no default configuration is provided during the installation. To enable the collector (daemon) to start it will look for the configuration file /etc/osquery/osquery.conf to be available. This is not a file that is part of the RPM installation. This will result in the below warning when you try to start the osquery daemon;

[root@testbox08 init.d]#
[root@testbox08 init.d]# ./osqueryd start
No config file found at /etc/osquery/osquery.conf
Additionally, no flags file or config override found at /etc/osquery/osquery.flags
See '/usr/share/osquery/osquery.example.conf' for an example config.
[root@testbox08 init.d]#

Without going into the details of how to configure osquery and tune it for you specific installation you can start to test osquery by simply using the default example configuration file.

[root@testbox08 osquery]#
[root@testbox08 osquery]# cp /usr/share/osquery/osquery.example.conf /etc/osquery/osquery.conf
[root@testbox08 osquery]# cd /etc/init.d
[root@testbox08 init.d]# ./osqueryd start
[root@testbox08 init.d]# ./osqueryd status
osqueryd is already running: 28514
[root@testbox08 init.d]#
[root@testbox08 osquery]#

As you can see, we now have the osquery deamon osqueryd running under PID 28514. As it is a collector it is good to wait for a couple of seconds to ensure the collector makes its first collection and stores this in the sqlite database. However, as soon as it has done so you should be able to get the first results stored in your database and you should be able to query the results for data.

To make life more easy, you can use the below script to install osquery in a single go:

wget "" -O /tmp/osquery.rpm
rpm -ivh /tmp/osquery.rpm
rm -f /tmp/osquery.rpm
cp /usr/share/osquery/osquery.example.conf /etc/osquery/osquery.conf
./etc/init.d/osqueryd start

Using osqueryi
The main way to interact with the osquery data is using osqueryi which is located at /usr/bin/osqueryi . Which means that if you execute osqueryi you will be presented a command line interface you can use to query the data collected by the osqueryd collector. 

[root@testbox08 /]#
[root@testbox08 /]# osqueryi
osquery - being built, with love, at Facebook
Using a virtual database. Need help, type '.help'

As an example you can query which pci devices are present with a single SQL query as shown below:

select * from pci_devices;
| pci_slot     | pci_class | driver           | vendor | vendor_id | model | model_id |
| 0000:00:00.0 |           |                  |        | 8086      |       | 1237     |
| 0000:00:01.0 |           |                  |        | 8086      |       | 7000     |
| 0000:00:01.1 |           | ata_piix         |        | 8086      |       | 7010     |
| 0000:00:01.3 |           |                  |        | 8086      |       | 7113     |
| 0000:00:02.0 |           |                  |        | 1013      |       | 00B8     |
| 0000:00:03.0 |           | xen-platform-pci |        | 5853      |       | 0001     |

As osqueryi uses a sqlite backend we can use the standard options and SQL provided by sqlite and for example get a full overview of all tables that are present when using the .table command in the command line interface. This provides the below output, which can be a good start to investigate what type of information is being collected by default and can be used;


The example shown above is a extreme simple example, everyone with at least a bit SQL experience will be able to write much more extensive and interesting queries which can make life as a Linux administrator much more easy.

Script against osquery
Even though using the command line interface is nice for adhoc queries you might have for a single Oracle Linux instance it is more interesting to see how you can use osquery in a scripted manner. As this is based upon sqlite you can use the same solutions you would use when coding against a standard sqlite database. This means you can use bash scripting, however, you can also use most other scripting languages and programming languages popular on the Linux platform. Most languages now have options to interact with a sqlite database.