Everything our consultants learn while running databases for customers: Oracle, PostgreSQL, SQL Server, MySQL, MariaDB, MongoDB and Delphix. Expect hands-on posts about installation, day-to-day administration, backup and recovery, performance tuning, replication and upgrades.
Today I had the pleasure to have Bruce Momjian in my session about PostgreSQL Upgrade Best Practices at the IT Tage 2017 in Frankfurt. While browsing through the various options you have for upgrading there was one slide where I claimed that the old cluster needs to be down before you run pg_upgrade in check mode as you will hit…
A few days ago, I conduct an audit to detect all orphan’s windows accounts in a database and I was surprise to have an error during the drop user query. The first step is to find all orphan’s windows accounts in a database USE [dbi_database] GO /*Step1: Search the orphan user */ SELECT * FROM sys.database_principals a LEFT OUTER…
DISCLAIMER: I know it exists other solutions to do it Pre-requisites: – a virtual machine (or not) with CentOS7 installed – a free disk or partition I use a VBox machine and I added a 5GiB hard disk We list the disk and partition to check if our new hard is added. [root@deploy ~]$ lsblk NAME MAJ:MIN RM SIZE RO…
In Oracle 12.2 an archive log directory is accepted, if top level directory does not exist: oracle@localhost:/u01/app/oracle/product/12.2.0/dbhome_1/dbs/ [DMK] ls -l /u02/oradata/DMK/ total 2267920 drwxr-xr-x. 2 oracle dba 96 Dec 6 05:36 arch … Now database accepts this non existing archivelog destination: SQL> alter system set log_archive_dest_3=’LOCATION=/u02/oradata/DMK/arch/arch2′; System altered. But not this: SQL> alter system set log_archive_dest_4=’LOCATION=/u02/oradata/DMK/arch/arch2/arch4′; alter system set log_archive_dest_4=’LOCATION=/u02/oradata/DMK/arch/arch2/arch4’…
As you probably noticed RedHat as well as CentOS switched to systemd with version 7 of their operating system release. This also means that instead of looking at /var/log/messages you are supposed to use journcalctl to browse the messages of the operating system. One issue with that is that messages before the last reboot of your system will not be…
If you store your databases on the cluster filesystem ACFS you may use the provided Perl-script gDBClone from OTN to clone databases or create snapshot databases. It is an interesting approach to create clones from the Production DB in minutes regardless of the production DB size. What you do is to create a standby DB from your production DB on…
By Franck Pachot . A quick post to show why you should always use LOGTIME=ALL METRICS=Y when using Data Pump. Just look at an example showing the timestamp in front of each line and a message about each task completed by the worker: 15-NOV-17 11:48:32.305: W-5 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA 15-NOV-17 11:48:34.439: W-13 Completed 28 PROCACT_SCHEMA objects in 1 seconds…
By Mouhamadou Diaw In oracle documentation we can find this about snapshot standby: A snapshot standby database is a fully updatable standby database that provides disaster recovery and data protection benefits that are similar to those of a physical standby database. The concept of snapshot standby is that we can temporary convert a physical standby to an updatable database for…
While giving the last PostgreSQL DBA Essentials workshop this question came up: When we create a table or an index: are the statistics available automatically? To be more precise: When we create and load a table in one step, create an index on that table afterwards: Do we have the statistics available by default or do we need to wait…
As I regularly work on Oracle and PostgreSQL, I decided to test the replication from Oracle to PostgreSQL using the Dbvisit Replicate tool. Dbivisit Replicate does not use Oracle logminer ot triggers but its own mining processes to get the changes when they are written to the redo logs. When a change appears in the redo log, an external file…
In PostgreSQL when you create an index on a table, sessions that want to write to the table must wait until the index build completed by default. There is a way around that, though, and in this post we’ll look at how you can avoid that. As usual we’ll start with a little table: postgres=# \! cat a.sql drop table…
By Franck Pachot . When I explain the multitenant internals, I show that all metadata about system procedures and packages are stored only in CDB$ROOT and are accessed from the PDBs through metadata links. I take an example with DBMS_SYSTEM that has nothing in SOURCE$ of the PDB. But I show that we can compile it from the PDB. This…
