Infrastructure at your Service

All Posts By

Mouhamadou Diaw

Mouhamadou Diaw

Sharding with PostgreSQL

By | Database Administration & Monitoring | 11 Comments

In this article we are going to talk about sharding in PostgreSQL. What is sharding, Sharding is like partitioning. The difference is that with traditional partioning, partitions are stored in the same database while sharding shards (partitions) are stored in different servers. Below is an example of sharding configuration we will use for our demonstration PostgreSQL does not provide built-in tool for sharding. We will use citus  which extends PostgreSQL capability to do sharding and…

Read More
Mouhamadou Diaw

Understanding Row Level Security on PostgreSQL

By | Database Administration & Monitoring | One Comment

In this article we will talk about a nice feature Row Level Security on PostgreSQL. We are using EDB Postgres Advanced Server 9.5. Suppose that I am a team manager and that employee bonus are stored in a table Bonus. I want that each employee can see only data related to him and not data for other. How Can I implement this? I can simply use Row Level Security. Let’s go on. Below is the structure of my…

Read More
Mouhamadou Diaw

SharePlex Compare and Repair commands

By | Database Administration & Monitoring | 2 Comments

Reading Franck Pachot blog about comparing source and target in a Dbvisit replication (http://blog.dbi-services.com/compare-source-and-target-in-a-dbvisit-replication/), I decide to write a small article about how we can do same thing with SharePlex. Indeed SharePlex provides built-in commands to compare and repair synchronization. Below is our configuration file. We are just replicating table article to article_rep. Both tables contain 2000000 rows datasource:o.SPLEXDB #source tables target tables routing map titi.article titi.article_rep [email protected] From the source let’s insert 2 rows…

Read More
Mouhamadou Diaw

Manage DDL inside SharePlex

By | Database Administration & Monitoring | One Comment

In a precedent blog (http://blog.dbi-services.com/discovering-shareplex-for-oracle/) about SharePlex, we presented how to setup a replication between 2 environments. In this article we will try to see how SharePlex deals with replicating DDL statements. Before starting we present below our environment and our configuration file. We are just  replicating scott_atlas to scott_atlasrep2. We suppose that SharePlex is already configured. Server Name Database Name Oracle Version What atlas.localdomain SPLEXDB 12.1.0.2 Source atlasrep2.localdomain SPLEXSTR2 12.1.0.2 Target [[email protected] config]$ cat ddl_config_atlasrep2.cnf datasource:o.SPLEXDB…

Read More
Mouhamadou Diaw

Discovering SharePlex for Oracle

By | Database Administration & Monitoring | One Comment

Nowadays, replicating data is very important. Many tools exist in the market (Goldengate, Dbvisit …). In this article we talk about a Dell Product tool that can be used for data replication: SharePlex. The goal is to show how it is easy to setup a SharePlex environment for replicating data. After installing the software we will replicate scott schema to a target database into a new schema named scott_str1 We present below the environment we use Server Database…

Read More
Mouhamadou Diaw

Install Oracle 12c Flex Cluster Easily

By | Database Administration & Monitoring | No Comments

To install a flex cluster (or a flex ASM)  Grid Naming Service (GNS) is required. A GNS can be configured with dynamic public networks (DHCP) for VIP. This method is suitable for a big number of nodes.In this case GNS domain delegation is mandatory. In case of a few number of nodes, GNS can be configured without domain delegation. In this case we have to define manually all VIP addresses (like in a standard cluster)…

Read More
Mouhamadou Diaw

Oracle 12C Unified Auditing Feature in a multitenant environment

By | Database Administration & Monitoring | No Comments

In oracle 12c we have a new feature called Unified Auditing. What is it, how to implement it, how it works in a multitenant environment that’s what we will try to explain briefly in this article. Traditional oracle audit involves many audit-trail locations and tables and does not follow a standard. To help with this oracle 12c has introduced Unified Auditing  1- Enabling Unified Auditing By default the feature is disabled SQL> select value from…

Read More
Mouhamadou Diaw

Dataguard Environment and Database Tempfiles

By | Database Administration & Monitoring | One Comment

Last day I found a curious message in the backup alert log in a dataguard environment. The message was related to an issue with the resync catalog and was something like RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of resync command on default channel at 02/18/2016 17:48:42 ORA-00001: unique constraint (RMAN.TS_P2) violated After few researches, it seems that this error is due to a mismatch of tempfiles between Primary and Standby….

Read More
Mouhamadou Diaw

About OCR backup

By | Database Administration & Monitoring | No Comments

Last day Franck and me were discussing about OCR backup Let’s take a 2 nodes RAC. We can see that OCR backup is automatically done by oracle in only one node on local (with a certain frequency every day, every week and every 4 hours)) [[email protected] ~]$ /u01/app/12.1.0.2/grid/bin/ocrconfig -showbackup racsrv2 2016/02/03 19:58:05 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/backup00.ocr 2528224568 racsrv2 2016/02/03 15:58:03 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/backup01.ocr 2528224568 racsrv2 2016/02/03 11:58:02 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/backup02.ocr 2528224568 racsrv2 2016/02/02 23:57:57 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/day.ocr 2528224568 racsrv2 2016/02/02 23:57:57 /u01/app/12.1.0.2/grid/cdata/racsrv-cluster/week.ocr 2528224568 But…

Read More
Mouhamadou Diaw

Protect your oracle password function

By | Database Administration & Monitoring | 13 Comments

Every DBA knows the famous oracle password verification script $ORALE_HOME/rdbms/admin/utlpwdmg.sql This function can be used to control password complexity. This script should be protected from unauthorized users. The reason is that by adding an insert in this function, someone can get passwords of users in the database. Let’s go through an example Here is my script (I have modified initial oracle script by just adding the 2 lines after the BEGIN)   CREATE OR REPLACE…

Read More