Infrastructure at your Service

Category Archives: Database Administration & Monitoring

Daniel Westermann

Connecting your PostgreSQL instance to an Oracle database

By | Database Administration & Monitoring | 13 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
Daniel Westermann

Swiss Postgres Conference 2015

By | Database Administration & Monitoring | No Comments

At the 26th of June I had the chance to attend the second Swiss Postgres Conference at the HSR Rapperswil. It was packed with interesting sessions. Magnus Hagander, a well known community member, started by giving an overview of PostgreSQL Europe. Then I joined Hans Schönig for learning how to write aggregate functions in PostgreSQL. Honestly I did not understand all of it but at least I am now aware of what you can do…

Read More
Daniel Westermann

another way to list invalid objects

By | Database Administration & Monitoring | One Comment

How often did I type a query like this to list the invalid objects in a database? select count(*) from dba_objects where status ‘VALID'; — and user in/not in Today I learned another way to do the same. There is a view called dba_invalid_objects. Surprisingly the view is not listed in the official 12c database reference. It is however mentioned in the upgrade guide. Let’s do some tests. Currently I do not have any invalid…

Read More
Oracle Team

2 ways to move archivelogs – both need RMAN

By | Database Administration & Monitoring, Oracle | One Comment

By Franck Pachot . The database must know where are the files. If you move them from the OS, the database will not be aware of it. But there is a database tool to copy or move files and update the database repository (controlfile). It’s RMAN. With RMAN you can either: update the repository after you’ve moved the file from the OS or do the both: move and update the repository The syntax is a…

Read More
Daniel Westermann

When a function returns a collection: can you directly reference the index of the returned collection?

By | Database Administration & Monitoring | One Comment

As I am still interested in PLSQL I browsed the documentation some days ago and learned something new which is about functions returning collections. Here is a simple test case: Let’s create a simple package, specifying on collection and one function: create or replace package pkg1 as type t1 is table of number index by pls_integer; function f1 ( a number ) return t1; end pkg1; / create or replace package body pkg1 as function…

Read More
Daniel Westermann

ODA workshop at Arrow ECS

By | Database Administration & Monitoring | One Comment

On the 16th and 17th of June David Hueber, Georges Grey and myself had the chance to attend the ODA hands on workshop at Arrow ECS. Lead Trainer Ruggero Citton (Oracle ODA Product Development) did the first day with plenty of theory and examples. On the second day we had the opportunity to play on a brand new ODA X5-2: [root@odax50 ~]# oakcli show server Power State : On Open Problems : 0 Model :…

Read More