Infrastructure at your Service

Category Archives: Oracle

Mouhamadou Diaw

Oracle Database Vault: Realm in a Pluggable Database

By | Database Administration & Monitoring, Oracle | No Comments

Database Vault can also be used in a multitenant environment. In a multitenant environment we must register Oracle Database Vault in the root first, then after in the PDBs. In this blog we will see how we can use realms to protect data in a pluggable database 12.1. In CDB$ROOT we have to create common accounts that will be used for the Database Vault Owner (DV_OWNER role) and Database Vault Account Manager (DV_ACCTMGR role) accounts….

Read More
Oracle Team

Data Guard: always set db_create_file_dest on the standby

By | Oracle | No Comments

By Franck Pachot . The file name convert parameters are not dynamic and require a restart of the instance. An enhancement request was filled in 2011. I mentioned recently on Twitter that it can be annoying with Active Data Guard when a file on the primary server is created on a path that has no file name conversion. However, Ian Baugaard mentioned that there is a workaround for this specific case because db_create_file_dest is dynamic:

Read More
Oracle Team

My tmux scripts to script demos

By | Oracle | No Comments

By Franck Pachot . When I did my first demo using tmux, I have written the following blog post to explain it: https://blog.dbi-services.com/using-tmux-for-semi-interactive-demos/. Since then I’ve done all my demos with this technique. My scripts and configuration files have evolved now I have uploaded the on GitHub to share them: https://github.com/FranckPachot/scripts/tree/master/tmux-demo

Read More
Oracle Team

18c: Order by in WITH clause is not preserved

By | Oracle | One Comment

By Franck Pachot . For a previous post I’ve run on 18c a script of mine to get the V$MYSTAT delta values between two queries. This script (new version available on GitHub) generates the queries to store some values and subtract them on the next execution. But I had to fix it for 18c because I relied on some order by in a CTE which is lost in 18c. The idea was to get the…

Read More
Oracle Team

18c: some optimization about redo size

By | Oracle | One Comment

By Franck Pachot . Some years ago, at the time of 12.1 release, I published in the SOUG newsletter some tests to show the amount of redo generated by different operations on a 10000 rows table. I had run it on 12.2 without seeing the differences and now on 18.1 I get the statistics from mystat using a script that displays them as columns, with the value being the difference from the previous run. I’ve…

Read More
Oracle Team

Event Sourcing: CQN is not a replacement for CDC

By | Oracle | One Comment

By Franck Pachot . We are in an era where software architects want to stream the transactions out of the database and distribute them, as events, to multiple microservices. Don’t ask why, but that’s the trend: store inconsistent eventually consistent copies of data in different physical components, rather than simply using logical views in the same database, where the data is ACIDely stored, processed and protected. Because it was decided that this segregation, in CQRS…

Read More
Oracle Team

Statspack installation scripts

By | Oracle | 9 Comments

By Franck Pachot . When Diagnostic Pack is disabled, either because you don’t have Diagnostic Pack or you are in Standard Edition, I highly recommend to install Statspack. When you will need it, to investigate on an issue that occured in the past, you will be happy to have it already installed and gathering snapshots.

Read More
Mouhamadou Diaw

DataGuard and Transparent Data Encryption

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

Setting up a DatagGard environment for a database with Transparent Data Encryption requires some tasks concerning the encryption keys. Otherwise the steps are the same than for an environment without TDE. In this blog we will present the tasks we have to do on both primary and standby servers for the keys. We will not describe the procedure to build the standby database. We will just talk about tasks for the wallet and we will…

Read More
Oracle Team

PGIO, PG_STAT_ACTIVITY and PG_STAT_STATEMENTS

By | Oracle, Postgres | No Comments

By Franck Pachot . For an Oracle DBA, we are used to join active sessions (from V$SESSION where status=’ACTIVE) with active statements (from V$SQL where users_executing>0) on the SQL_ID. V$ACTIVE_SESSION_HISTORY also displays the TOP_LEVEL_SQL_ID to get the entrypoint of the usercall if we need it. With Postgres it is a bit more difficult because it seems that PG_STAT_ACTIVITY do not show the active statement but only the top-level one. But pg_stat_statement collects statistics for the…

Read More