Infrastructure at your Service

All Posts By

Clemens Bleile

Clemens Bleile

ODA 19.11.: ORACLE_HOMEs on ACFS

By | Oracle | 4 Comments

Introduction Beginning with Oracle Database Appliance (ODA) 19.11. the ORACLE_BASE and ORACLE_HOMEs are created on ACFS. That’s good news, because there is much more space on ASM than on the internal disks, i.e. no space issues with /u01 anymore. So how does it work? ORACLE_BASE and ORACLE_HOME on ACFS When installing an ODA from scratch then ORACLE_BASE and ORACLE_HOME are already on ACFS. See an example of a freshly installed ODA X8-2M: [[email protected] ~]# odacli…

Read More
Clemens Bleile

Creating an Oracle Snapshot Copy PDB on top of ASM

By | Oracle | No Comments

Creating a snapshopt copy Pluggable Database (PDB) on top of Automatic Storage Management (ASM) is not possible, because you need to have a filesystem which supports sparse files. According the following MOS-Note sparse files are supported on Automatic Storage Management Cluster File System (ACFS) since 12.2.: 12.2 Oracle ACFS Sparse Files Enhancements (Doc ID 2200264.1) However, I thought it is more fun to create a PDB Snapshot Copy on a XFS-filesystem, which I wanted to…

Read More
Clemens Bleile

Parameterized Views with SYS_CONTEXT in Oracle. How can the optimizer peek values?

By | Oracle | No Comments

There was a dicsussion a couple of years ago about a mssing feature in Oracle that the Optimizer is not peeking values when SYS_CONTEXT is being used in a predicate. I.e. specifically when using SYS_CONTEXT in Views to simulate passing parameters to views. See the following links concerning this topic: https://connor-mcdonald.com/2016/10/20/taking-a-peek-at-sys_context https://blog.jooq.org/2016/10/20/be-careful-when-emulating-parameterised-views-with-sys_context-in-oracle https://community.oracle.com/ideas/15826 E.g. take the example of Connor McDonald here: [email protected]@PDB1> create table t ( x varchar2(10), y char(100)); Table created. [email protected]@PDB1> [email protected]@PDB1> insert…

Read More
Clemens Bleile

Adding nmon to Oracle OSWatcher

By | Oracle | No Comments

nmon (nmon is short for Nigel’s performance Monitor for Linux on POWER, x86, x86_64, Mainframe & now ARM (Raspberry Pi)) is a nice tool to monitor a Linux system. Originally it came from the AIX-platform, where it is very popular. I asked myself if I can add nmon to the OSWatcher framework to automatically gather nmon data. It is actually possible. Here’s what I did: First I installed nmon: In my case (Oracle Enterprise Linux…

Read More
Clemens Bleile

Oracle Blockchain Tables: COMMIT-Time

By | Oracle | No Comments

Oracle Blockchain Tables are available now with Oracle 19.10. (see Connor’s Blog on it), they are part of all editions and do not need any specific license. I.e. whenever we need to store data in a table, which should never be updated anymore and we have to ensure data cannot be tampererd, then blockchain tables should be considered as an option. As Oracle writes in the documentation that blockchain tables could e.g. be used for…

Read More
Clemens Bleile

Oracle autoupgrade on Windows and plugin to a Container DB with virtual accounts

By | Oracle | No Comments

In a project I recently had to upgrade an Oracle 12.2.-DB to 19.9. and at the same time migrate from the non-container architecture to the container architecture. The interesting part here is to do this on Windows. Actually both steps (upgrade and plugin) are possible using the autoupgrade tool , which is the preferred tool to do Oracle upgrades today. Before doing the upgrade at the customer, I performed 2 tests in my personal environment:…

Read More
Clemens Bleile

Handling unified auditing spillover files on the standby-site

By | Oracle | No Comments

Switching to Oracle Unified Auditing may produce lots of data when e.g. auditing activities of the SYS-user. I.e. according the documentation you can do the following to audit similarly as in traditional auditing with audit_sys_operations=TRUE: SQL> CREATE AUDIT POLICY TOPLEVEL_ACTIONS ACTIONS ALL ONLY TOPLEVEL; SQL> AUDIT POLICY TOPLEVEL_ACTIONS BY SYS; REMARK1: You may check the Blog on traditional SYS-auditing here REMARK2: Tests done in this Blog were done with Oracle 19.9. Auditing toplevel operations were…

Read More
Clemens Bleile

Automatic column formatting in Oracle sqlplus

By | Oracle | No Comments

Column formatting was always a pain in sqlplus when writing queries on the prompt. Most people use tools like SQL Developer or Quest TOAD which can scroll horizontally when running queries against a database, but as a consultant you are often still forced to use sqlplus. Here’s the issue: When running e.g. a query on a table T1 (which is a copy of ALL_OBJECTS) it looks by default as follows and is hard to read:…

Read More
Clemens Bleile

Oracle DML (DELETE) and the Index Clustering Factor

By | Oracle | No Comments

As a consultant working for customers, I’m often in the situation that I have an answer to a problem, but the recommended solution cannot be implemented due to some restrictions. E.g. the recommendation would be to adjust the code, but that is not feasible. In such cases you are forced to try to help without code changes. Recently I was confronted with the following issue: A process takes too long. Digging deeper I could see…

Read More
Clemens Bleile

Merge-Statement crashes with ORA-7445 [kdu_close] caused by Real Time Statistics?

By | Oracle | No Comments

In a recent project we migrated an Oracle database previously running on 12.1.0.2 on an Oracle Database Appliance to an Exadata X8 with DB version 19.7. Shortly after the migration a merge-statement (upsert) failed with an ORA-07445: exception encountered: core dump [kdu_close()+107] [SIGSEGV] [ADDR:0xE0] [PC:0x1276AE6B] [Address not mapped to object] [] The stack looked as follows: kdu_close – updThreePhaseExe – upsexe – opiexe – kpoal8 – opiodr – ttcpip – opitsk – opiino – opiodr…

Read More