Infrastructure at your Service

Category Archives: Database Administration & Monitoring

Daniel Westermann

pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist

By | Database Administration & Monitoring, Database management | No Comments

As I could not find any information about this error when I faced it this is just a short hint: If you try to use pg_dump or pg_dumpall out of a PPAS installation to dump or dumpall a community version of PostgreSQL you probably get this: pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist pg_dump: [archiver (db)] query was: SET search_path = pg_catalog, sys, dbo The solution is quite easy but you…

 
Read More
Daniel Westermann

Parallel sequential scans are coming to PostgreSQL

By | Database Administration & Monitoring | No Comments

It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up: max_parallel_degree: The maximum degree of parallelism for an individual parallel operation. parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a parallel worker process to another process. The default is 0.1 parallel_setup_cost: Sets the planner’s estimate…

 
Read More
David Schmitt

UKOUG 2015 Day 3: Security, RMAN, Upgrade, Latches and Mutexes

By | Database Administration & Monitoring | No Comments

UKOUG 2015 … Day 3 (Wednesday): Ref: Oracle Security, RMAN, 12c Upgrade, Latches and Mutexes. Here a litle overview of the last day in the Oracle UKOUG 2015 … Two Oracle Security sessions: – The first one about “Database Password Security” by Pete Finnigan. In this session we explored all of the ideas and issues related to database passwords. We also had a look on what is the strongest algorithms for a password and the…

 
Read More
David Schmitt

UKOUG 2015 Day 2: Oracle Enterprise Manager Mistake and HA, Active DG and GDS, Multitenant upgrade and AWR reports.

By | Database Administration & Monitoring | No Comments

UKOUG 2015 … Day 2 (Tuesday): Ref: Oracle Enterprise Manager Mistake and HA, Active DG and GDS, Multitenant upgrade and AWR reports. For the second day in the Oracle UKOUG 2015, I attented some very good topics and presentation. Here an overview … Two OEM presentation: – In the Morning, first presentation was about “Common mistakes when implementing 12c OEM” by Philipp Brown. A good overview on what to take care when you install and…

 
Read More
Daniel Westermann

Monitoring tools for PostgreSQL – POWA

By | Database Administration & Monitoring, Database management | No Comments

The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the file system layer pg_qualstats: gathers statistics of predicates found in where statements and join clauses btree_gist: provides GiST index operator…

 
Read More
Daniel Westermann

Monitoring tools for PostgreSQL – pgcluu

By | Database Administration & Monitoring | No Comments

The last posts introduced the logging system, pg_stat_statements and pg_activity. All of these can be used to monitor sql statements the PostgreSQL server is executing. In this post I’ll look into pgcluu: PostgreSQL Cluster utilization! This is a more complete monitoring solution as it is not only focused on sql statements but gives you information about the database cluster itself and other useful stuff. All you need to run pgcluu is a modern perl distribution…

 
Read More
David Schmitt

UKOUG 2015 Day 1: Oracle In-Memory, Table Locks, Dbvisit, Oracle Multitenant and Open Source Tuning tools.

By | Database Administration & Monitoring | No Comments

UKOUG 2015 Day 1 (Monday): a short overview. Ref: Oracle In-Memory, all about table locks, Dbvisit, Oracle Multitenant and Open Source Tuning tools. Today was my first day in the Oracle UKOUG 2015. My first presentation focussed on the “Oracle Database In-Memory Option: Challenges & Possibilities”by Christian Antognini. This option was introduced in Oracle version 12.1.0.2 and promises to deliver in-memory performance without modifying the application’s code. After a short explaining of the general concepts…

 
Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (3) – pg_activity

By | Database Administration & Monitoring | 7 Comments

The last posts looked at how the logging system and the pg_stat_statements extension can be used to monitor sql statements in PostgreSQL. This post will introduce pg_activity which is very similar to htop. There are some dependencies which need to be installed before we can start installing pg_activity. The first one is python. As I am on a redhat based distribution this is quite easy: [root@oel7 ~] yum install -y python Then we need to…

 
Read More
Daniel Westermann

SQL Monitoring in PostgreSQL (2) – pg_stat_statements

By | Database Administration & Monitoring | No Comments

The last post looked into how you can monitor queries using the logging system. This post will introduce pg_stat_statements. pg_stat_statements is a module that needs to be loaded and is not available in the default configuration. Loading it is quite easy. Create the extension as usual: postgres@oel7:/home/postgres/ [PG6] sqh Null display is “NULL”. Timing is on. psql (9.5alpha2) Type “help” for help. (postgres@[local]:4448) [postgres] > create extension pg_stat_statements; CREATE EXTENSION Time: 281.765 ms (postgres@[local]:4448) [postgres]…

 
Read More
Vincent Matthey

GoldenGate 12.2 additional column on the target

By | Database Administration & Monitoring | One Comment

My colleague Hervé last week posted a blog concerning a bug in GoldenGate 12.1. You can find the blog here. In fact the problem is that GoldenGate works with the column position and not with the column name. To follow up this bug I tried to reproduce that with GoldenGate 12.2 that was released last week. As Hervé did I used the schema scott/tiger. The goal is not to test an initial load. Source>@utlsampl.sql Target>@utlsampl.sql…

 
Read More