Infrastructure at your Service

All Posts By

Clemens Bleile

Clemens Bleile

Elapsed time of Oracle Parallel Executions are not shown correctly in AWR

By | Database Administration & Monitoring | 2 Comments

As the elapsed time (time it takes for a task from start to end, often called wall-clock time) per execution of parallel queries are not shown correctly in AWR-reports, I thought I setup a testcase to find a way to get an elapsed time closer to reality. REMARK: To use AWR (Automatic Workload Repository) and ASH (Active Session History) as described in this Blog you need to have the Oracle Diagnostics Pack licensed. I created…

Read More
Clemens Bleile

Creating archived redolog-files in group dba instead of oinstall

By | Database management, Oracle | No Comments

Since Oracle 11g files created by the database belong by default to the Linux group oinstall. Changing the default group after creating the central inventory is difficult. In this Blog I want to show how locally created archived redo can be in group dba instead of oinstall. One of my customers had the requirement to provide read-access on archived redo to an application for logmining. To ensure the application can access the archived redo, we…

Read More
Clemens Bleile

Oracle 19c

By | Database Administration & Monitoring | No Comments

Oracle 19c has been released quite a while ago already and some customers already run it in Production. However, as it is the long term supported release, I thought I blog about some interesting information and features around 19c to encourage people to migrate to it. Download Oracle 19c: https://www.oracle.com/technetwork/database/enterprise-edition/downloads or https://edelivery.oracle.com (search e.g. for “Database Enterprise Edition”) Docker-Images: https://github.com/oracle/docker-images/tree/master/OracleDatabase Oracle provides different offerings for 19c: On-premises: – Oracle Database Standard Edition 2 (SE2) –…

Read More
Clemens Bleile

Direct NFS, ODM 4.0 in 12.2: archiver stuck situation after a shutdown abort and restart

By | Database Administration & Monitoring, Database management | 2 Comments

A customer had an interesting case recently. Since Oracle 12.2. he got archiver stuck situations after a shutdown abort and restart. I reproduced the issue and it is caused by direct NFS since running ODM 4.0 (i.e. since 12.2.). The issue also reproduced on 18.5. When direct NFS is enabled then the archiver-process writes to a file with a preceding dot in its name. E.g. .arch_1_90_985274359.arc When the file has been fully copied from the…

Read More
Clemens Bleile

Technical and non-technical sessions at the DOAG 2018

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

The amazing DOAG 2018 conference is over now. As every year we saw great technical as well as great non-technical sessions. What impressed me was the non-technical presentation “Zurück an die Arbeit – Wie aus Business-Theatern wieder echte Unternehmen werden” (back to work – how business theatres become real business company again) provided by Lars Vollmer. It was very funny, but also thought-provoking. Lars started with the provocative sentence that people do work too less….

Read More
Clemens Bleile

Running PLSQL as SYSDBA through DEFINER-rights?

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

Recently I got an interesting request: The customer wanted to allow the application installation routine to create a guaranteed restore point without giving it all required privileges to do so. So the idea was to encapsulate creating and dropping a guaranteed restore point in a PLSQL package and granting the application owner the permission to execute the package. The problem with that approach is that SYSDBA-privileges are required to create a guaranteed restore point and…

Read More
Clemens Bleile

Restarting a failed transportable tablespace metadata import

By | Database Administration & Monitoring | 3 Comments

I’m currently working in a project to migrate a Datawarehouse-database from Solaris to Linux Intel (Endian-change). We do use the cross platform incremental backups method as described in My Oracle Support Note 1389592.1 for that. I.e. incremental backups are applied to database files and during the migration the recovered datafiles are attached to the target database via the transportable tablespace method. When testing the transportable tablespace metadata import I got an error for a table:…

Read More
Clemens Bleile

sqlplus and its column output

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

During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here): Most people address this by using other tools like sqldeveloper (or sqlcl with sqlformat ansiconsole). However, a lot of people still use sqlplus. How can you address…

Read More
Clemens Bleile

Connect-times to the DB suddenly become very slow using sqlcl

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

I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html), because there is no need to “install” something then. All I needed was an installed JRE on the Linux-machine. Fortunately that was available. So I downloaded the newest version of sqlcl and…

Read More
Clemens Bleile

CPUs: Cores versus Threads on an Oracle Server

By | Database Administration & Monitoring, Database management | 4 Comments

When doing a performance review I often do talk with the DBA about the CPU utilization of the server. How reliable is the server CPU utilization with tools like top or the host CPU utilization in the AWR-report? E.g. on an Linux Intel x86-64 server with 8 Cores and 16 logical CPUs (Intel Hyperthreading), what does a utilization of 50% mean? As I had an ODA X7-M in a test lab available, I thought I’ll…

Read More