Infrastructure at your Service

Category Archives: Database Administration & Monitoring

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
Daniel Westermann

SQL Monitoring in PostgreSQL (1) – the logging system

By | Database Administration & Monitoring | No Comments

When developing an application as well as when the application is in production there is the need to identify long running queries. In Oracle one tool you might use for that is the SQL Monitor. In this post I’ll look into what PostgreSQL provides in this area. PostgreSQL has a very strong logging system. This system can be used to log many, many server messages as well as information about sql queries. To enable to…

 
Read More
Vincent Matthey

GoldenGate 12.2 new parameter ALLOWOUTPUTDIR

By | Database Administration & Monitoring | 9 Comments

I will start a series of blog posts about the new features of GoldenGate 12.2. This first blog will be concerned by the new parameter ALLOWOUTPUTDIR. When I tried GoldenGate 12.2 for the first time, I reused the same configuration as with GoldenGate 12.1. It means two virtual machines with OEL 6.5 and Oracle Database 12.1.0.2.4 Enterprise Edition. First I configured my SCOTT extract process without problems. It was the same for the REPSCOTT replicat…

 
Read More
Nathan Courtine

Manipulate Stretch database feature by script

By | Database Administration & Monitoring | No Comments

On November 30th, I presented the Stretch Database feature in “Les Journées SQL Server 2015” in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.   I – Enabling the feature at the instance level First, you need to enable the “Remote Data Archive” option at the instance level. To check if the options is enabled: sp_configure…

 
Read More
Nicolas Jardot

How to read XML database alert log?

By | Database Administration & Monitoring | No Comments

Since Oracle 11g, Oracle maintains two copies of the database’s alertlog in ADR: a flat text file in the sub-directory trace and an XML like in the folder alert. I had a case recently at a customer where the log.xml was moved to another place and compressed for archiving reason. As the regular text file was not containing old data, the goal was to exploit the archived XML -like file. When the file is still located…

 
Read More
Daniel Westermann

Upgrading PostgreSQL to a new major release

By | Database Administration & Monitoring | No Comments

The last post looked into how to upgrade PostgreSQL to a new minor version. In this post I’ll look into how to upgrade PostgreSQL to a new major version. This is not as simple as just installing the binaries and start the instance from there. For major upgrades there are two possibilities: dump the old version and restore into the new version by using pg_dump and pg_restore use pg_upgrade I’ll only look into pg_upgrade for…

 
Read More
Daniel Westermann

Patching PostgreSQL to a new minor release

By | Database Administration & Monitoring | 3 Comments

If you are used to patch Oracle databases you probably know how to use opatch to apply PSUs. How does PostgreSQL handle this? Do we need to patch the existing binaries to apply security fixes? The answer is: No. Lets say you want to patch PostgreSQL from version 9.4.1 to version 9.4.5. What do you need to do? For this little demo I’ll create a new database and a sample table in my 9.4.1 instance:…

 
Read More
Franck Pachot

Franck Pachot: OCM 12c preparation: Manage PDB

By | Database Administration & Monitoring, Oracle | No Comments

Let’s see the different ways to create a PDB, with different tools. Same disclaimer here as in the first post of the series: don’t expect to get those posts close to what you will have at the exam, but they cover important points that matches the exam topics. Documentation Information about the exam says: Be prepared to use the non-searchable documentation during the exam, to help you with correct syntax. Documentation about the ‘Create and…

 
Read More