Infrastructure at your Service

 
Daniel Westermann

How to efficiently add constraints to existing tables in PostgreSQL

By | Database Administration & Monitoring | No Comments

Is is not a surprise that applications change over time. New tables get added, columns need to be changed or new ones are required, and sometimes you need to add check constraints or foreign keys to already existing tables which contain data. The issue with adding constraints to tables that already contain data is, that PostgreSQL needs to scan the whole table, to validate that existing data does not violate the constraints you are adding….

Read More
Jérôme Dubar

First contact with OCI’s Autonomous Database

By | Cloud, Database Administration & Monitoring, Database management, Development & Performance, Oracle | No Comments

Introduction Benefits of going into the Oracle Cloud (OCI) are clear for most of us: no more datacenter and hardware to manage. But what else? On OCI, you can also decide to get rid of part of the DBA tasks (the less interesting ones, for sure). To create your Oracle database environment, you need to choose among these 3 solutions (for most of us): a compute instance you will manage yourself a DB System, basically…

Read More
Lazhar Felahi

SELECT FROM DUAL : Oracle Performance And Tuning

By | Database Administration & Monitoring, Development & Performance, Oracle | No Comments

The DUAL table is automatically created by Oracle and contains one column (DUMMY) and one row (x value). This table is often used by SQL developer in PL/SQL code (Package, Functions, Trigger) to initialize variables storing technical information such as for example SYSDATE, USER or HOSTNAME. Querying DUAL table is generally faster  as we can see below: SQL> select sysdate from dual; SYSDATE ——— 05-OCT-21 Elapsed: 00:00:00.01 Execution Plan ———————————————————- Plan hash value: 1388734953 —————————————————————–…

Read More
Lazhar Felahi

From Oracle Standard Auditing to Oracle Unified Auditing

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

In this post, I will not show you all advantages related to Oracle Unified Auditing. For that, read the oracle documentation. You can also read the blogs written by my colleagues : Oracle 12c Unified Auditing Feature in a multitenant environment Oracle 12c – RMAN and Unified Auditing – Does it really work ? Purging Unified Audit Trail in 12cR2 I just want to share with you the steps needed to move from Standard Auditing…

Read More
Casimir Schmid

Exadata X9M release

By | Database Administration & Monitoring, Database management, Hardware & Storage, Oracle | One Comment

I would like to share some personal thoughts. September 28th 2021, Oracle released the eleventh Exadata data machine called “X9M-2” (2 CPU sockets), X9M-8 (8 sockets) and ZDLRA X9M. Exadata is a computing platform to run Oracle RDBMS, zero data loss recovery appliance (ZDLRA) is a platform to backup Oracle RDBMS and based on Exadata hardware.

Read More
Joël Cattin

Scanning Ansible code with Ansible Lint

By | Ansible, Development & Performance, DevOps | No Comments

Introduction It’s always good to learn something new. But doing it the right way from the very beginning is even better. I recently started to work with Ansible and after creating a few roles, I quickly realized that there were many different ways to achieve the same goal. Some examples are : – The use of the Shell module vs the Command module – The use of Loop vs With_* – The use of an…

Read More
Daniel Westermann

PostgreSQL indexes and operators

By | Database Administration & Monitoring | No Comments

Creating a standard B-tree index in PostgreSQL is nothing special and usually we do it without thinking about what happens in the background. For the standard data types like e.g. int or text we do not need to know anything about operators or operator classes. In those cases the standard operator class for the data type is applied and all is fine. But sometimes we need to know about the possibilities PostgreSQL comes with, and…

Read More
Daniel Westermann

Some basics about time zones in PostgreSQL

By | Database Administration & Monitoring | No Comments

As soon as you have an application which works across time zones you will have to deal with that properly in the database. I’ve seen many applications that didn’t care of that at the beginning, and much work had to be done to change the implementation afterwards. The one and only recommendation is: Do it properly from the beginning and you don’t have to think much about it in the future. PostgreSQL comes with all…

Read More
Alain Fuhrer

Magic Upgrade to 21c with Replay Upgrade Feature

By | Database Administration & Monitoring | No Comments

Last week I did some upgrade tests from 19c to 21c. The plan was to copy a 19c PDB to a CDB 21c using Online Clone and then upgrade it to 21c. I have done this many times for 19c as well and just wanted to verify that it still worked with 21c. Clone PDB to 21c CDB Since it is a LAB, I gave the user SYSTEM in the CDB19c the appropriate duplicate permissions…

Read More