Infrastructure at your Service

All posts by Daniel Westermann

Daniel Westermann
Senior Consultant and Technology Leader Open Infrastructure

Daniel Westermann has more than 10 years of experience in management, engineering and optimization of databases and infrastructures. He is specialized in Oracle Technologies as Performance Optimization and Tuning, Standardization, Backup & Recovery, in High Avaibility solutions as Oracle Real Application Clusters (RAC), Oracle Data Guard, Oracle Grid Infrastructure, as well as in storage technologies as Oracle Automatic Storage Management (ASM). Daniel Westermann is Oracle Certified Professional 10g/11g/12c. He is also a PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. Prior to dbi services, Daniel Westermann was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper & Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel Westermann holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery, telecommunications.
Daniel Westermann

Setting up a sample replication with GoldenGate

By | Database Administration & Monitoring | 6 Comments

The first post in this series outlined on how to install and patch Oracle GoldenGate to the latest release: Installing and patching Oracle GoldenGate 12c to the latest release In this post I’ll look in how to prepare the databases and how to setup GoldenGate for a first sample replication between two Oracle 12.1.0.2 databases. My test setup is as follows: Hostname Database Type oelgg1 DB1 Source oelgg2 DB2 Target   The idea is that…

 
Read More
Daniel Westermann

Installing and patching Oracle GoldenGate 12c to the latest release

By | Database Administration & Monitoring | 8 Comments

As more and more customers ask for expertise in Oracle GoldenGate this blog post is the first in a series and outlines how to install Oracle GoldenGate 12c and how to patch it to the latest release on Oracle Linux 7.1 x64. The installation sources (as always) can be downloaded from otn.oracle.com. For the latest patchset you’ll need access to mos. At the time of writing the latest patchset is 12.1.2.1.9 and can be downloaded…

 
Read More
Daniel Westermann

Row level security is coming to PostgreSQL

By | Database Administration & Monitoring | No Comments

Before PostgreSQL 9.5 (which is in alpha2 currently) you could grant access to individual columns of a table to users or roles. A little test script to demonstrate this: (postgres@[local]:5432) [postgres] > select version(); version ————————————————————————————————————– PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit (1 row) create table t ( id int primary key, name varchar(50), salary bigint ); insert into t ( id, name, salary ) values (…

 
Read More
Daniel Westermann

Representing ranges with one data type?

By | Database Administration & Monitoring | No Comments

How many applications do you know that need to define a validity of a row? And how many of these do that by adding two columns: valid_from and valid_to or similar column names? Well, in PostgreSQL (you already suspect it ) there is much more elegant way to do that. PostgreSQL knows the concept of range types. Several of these are pre-defined and you get them once you install PostgreSQL: int4range — Range of integer…

 
Read More
Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database – The PostgreSQL 9.5 way

By | Database Administration & Monitoring | 6 Comments

As you might know PostgreSQL 9.5 is currently in alpha 2. One of the new features will be the possibility to import foreign schemas. Having this it is no longer required to create all the foreign tables which saves a lot of work. Setting up oracle_fdw was done in exactly the same way as in the previous post. Make sure you use the latest version of oracle_fdw as some internals changed in PostgreSQL 9.5 which…

 
Read More
Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database

By | Database Administration & Monitoring | 9 Comments

For integrating data from other systems PostgreSQL has the concept of foreign data wrappers. Many of these exist for different types of systems. In this post I’ll look into how you may connect PostgreSQL to Oracle. The home of the foreign data wrapper for Oracle (as most of the PostgreSQL stuff) is on github. If you do not want to clone the git repository you can download it from the PostgreSQL extension network, too. For…

 
Read More
Daniel Westermann

ACFS 12.1.0.2 on Oracle Linux 7.1

By | Database Administration & Monitoring | 2 Comments

Recently we wanted to create an ACFS filesystem on a brand new 12.1.0.2 GI installation on Oracle Linux 7.1. According to the documentation this should not be an issue as “Oracle Linux 7 with the Unbreakable Enterprise kernel: 3.8.13-33.el7uek.x86_64 or later” is supported. The “or later” is the important point. In our case: uname -a Linux racp1vm1 3.8.13-55.1.6.el7uek.x86_64 #2 SMP Wed Feb 11 14:18:22 PST 2015 x86_64 x86_64 x86_64 GNU/Linux   Should be fine as…

 
Read More
Daniel Westermann

Testing the just released PostgreSQL 9.5 Alpha in a docker container

By | Database Administration & Monitoring | One Comment

On the 2cnd of July the PostgreSQL Global Development Group released an alpha version of the upcoming PostgreSQL 9.5. The same day, Josh Berkus, another of those PostgreSQL core team members released a docker image for testing this alpha release. It’s never been that easy to get started with PostgreSQL or testing new features. If you are on Linux the docker packages should be available for your distribution. For Oracle Linux 7.1 make sure that…

 
Read More
Daniel Westermann

Indexing for like/similarity operations

By | Database Administration & Monitoring | One Comment

Indexing queries for like/similarity conditions is not that easy with the usual index types. The only option you have with btree indexes (especially if the wild-card is at the beginning of the filter) is to create a partial index on that columns for a very specific query. Let’s do a simple example with a btree index. The test data: drop table if exists t1; create table t1 ( a varchar(50) ); insert into t1 (…

 
Read More
Daniel Westermann

Quickly create a hundred databases and users

By | Database Administration & Monitoring | One Comment

Do you need a hundred databases and users for training etc. in PostgreSQL? Just a few lines of code: [postgres8@oel7 data]$ cat create_databases.sh #!/bin/bash NUMOFDBS=100 for i in `seq ${NUMOFDBS}`; do psql -q -c “create user u${i} UNENCRYPTED password ‘u${i}’ NOCREATEDB NOCREATEROLE NOCREATEUSER” postgres createdb -O u${i} db${i} done Execute it and you are ready: [postgres8@oel7 data]$ time ./create_databases.sh real 1m37.825s user 0m0.143s sys 0m0.109s Have a look at the databases and its owners: postgres=#…

 
Read More