Infrastructure at your Service

Category Archives: Database Administration & Monitoring

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

PostgreSQL on Amazon RDS – Loading the beast

By | Database Administration & Monitoring | No Comments

The last posts outlined how you can bring up a PostgreSQL instance in the Amazon cloud, how you can restrict access to the instance, how you can configure it and how you can add a read replica. In this post we’ll look at how you can load data to the instance. A database without data does not make much sense, does it? For having some data available to load I’ll populate my local PostgreSQL 9.4…

 
Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Adding a replica to the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked on how you can change the PostgreSQL parameters when the instance is running in the Amazon cloud. In this post we’ll look at how to add a replica to the PostgreSQL instance that is already running. Even if you push things to the cloud you want to make sure that you have a standby database in case the master crashes. At best the replica is running in another data…

 
Read More
Franck Pachot

log file sync / user commits

By | Database Administration & Monitoring | 3 Comments

By Franck Pachot . When presenting ‘Interpreting AWR Reports – Straight to the Goal’ at UKOUG TECH15 I had a very good question about the Statspack report I read which had log file sync much smaller than user commits. I realized that this needs a longer explanation, and that my slide is very misleading because I divided log file sync wait time per user commits, which probably make no sense here.  

 
Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Configuring the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked at how you can strengthen the security for your PostgreSQL instance in Amazon RDS. In this post I’ll look at how you can configure your PostgreSQL instance. When you login to the Amazon console and select your running instance the screen looks like this: If you want to modify the instance there is a “modify” entry in the “Instance Actions” menu: This brings you to the main configuration page…

 
Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Securing the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked at how to bring up a PostgreSQL instance in the Amazon cloud. In this post I’ll look into how to restrict access to this instance to specific IP addresses to strengthen security. If you think about moving things to the cloud you for sure want to make the stuff accessible to restricted people or systems only. On the left side there is a “Security Groups” menu which sounds like…

 
Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Setting up the beast

By | Database Administration & Monitoring | No Comments

Today I had a look at PostgreSQL in Amazon RDS. Once you have an Amazon AWS account (this requires a valid credit card for verification) setup bringing up a PostgreSQL instance is pretty easy. The first 12 months are free but be sure to read about the restrictions and what is terminated by default and what is not. Once your AWS account is ready you can login to the management console which lists all the…

 
Read More
Franck Pachot

PDB snapshot copy for continuous integration testing

By | Database Administration & Monitoring | 4 Comments

By Franck Pachot . How do you manage your continuous integration tests on the database? You need to restart at the same state for probably a hundred of tests. Recreating the schema and test data takes too long. Oracle is not optimized for DDL. Restoring the database takes too long. Even if you data is small, there is those SYSTEM, SYSAUX tablespaces. Flashback database can help, but it still takes time because it requires to…

 
Read More