Search

Top 60 Oracle Blogs

Recent comments

linux

What’s new with Oracle database 19.4 versus 19.3

The most notable thing here is an “official” (non-underscore) parameter has been introduced with 19.4, “ignore_session_set_param_errors”. The description is: ‘Ignore errors during alter session param set’. I did a quick check to see if I could set it to true or false, which I couldn’t (resulted in an error).

With the Oracle database version 19.3 patched to 19.4 on linux, the following things have changed:

orachk can now warn about unwanted cleanup of files in /var/tmp/.oracle

Some time ago @martinberx mentioned on twitter that one of his Linux systems suffered from Clusterware issues for which there wasn’t a readily available explanation. It turned out that the problem he faced were unwanted (from an Oracle perspective at least) automatic cleanup operations in /var/tmp/.oracle. You can read more at the original blog post.

The short version is this: systemd (1) – successor to SysV init and Upstart – tries to be helpful removing unused files in a number of “temp” directories. However some of the files it can remove are essential for Clusterware, and without them all sorts of trouble ensue.

What’s new with Oracle database 18.7 versus 18.6

With the Oracle database version 18.6 patched to 18.7 on linux, the following things have changed:

What’s new with Oracle database 12.2.0.1.190416 versus 12.2.0.1.190716

There are a couple of underscore parameters changed from spare to named ones.
It’s interesting to see that in sysstat, ‘spare statistic 2’ changed to ‘cell XT granule IO bytes saved by HDFS tbs extent map scan’. This obviously has to do with big data access via cell servers. What is weird is that this is the only version where this had happened.

What’s new with Oracle database 12.1.0.2.190416 versus 12.1.0.2.190716

There are a couple of undocumented spare parameters changed to named undocumented parameters, this is quite normal to see.

With the Oracle database version 12.1.0.2.190416 patched to 12.1.0.2.190716 on linux, the following things have changed:

Ansible tips’n’tricks: executing a loop conditionally

When writing playbooks, I occasionally add optional tasks. These tasks are only executed if a corresponding configuration variable is defined. I usually define configuration variables either in group_vars/* or alternatively in the role’s roleName/default/ directory.

The “when” keyword can be used to test for the presence of a variable and execute a task if the condition evaluates to “true”. However this isn’t always straight-forward to me, and recently I stumbled across some interesting behaviour that I found worth mentioning. I would like to point out that I’m merely an Ansible enthusiast, and by no means a pro. In case there is a better way to do this, please let me know and I’ll update the post :)

Before showing you my code, I’d like to add a little bit of detail here in case someone finds this post via a search engine:

Dead Connection Detection (DCD) and the Oracle database

Dead Connection Detection is a useful feature of the Oracle database: it allows for the cleanup of “dead” sessions so they don’t linger around consuming memory and other system resources. The idea is simple: if the database detects that a client process is no longer connected to its server process, it cleans up. This can happen in many ways, in most cases this kind of problem is triggered by an end user.

A dead connection shouldn’t be confused with idle connections: an idle connection still maintains the network link between client and server process, except that there is no activity. Idle connections aren’t maintained/controlled via DCD, there are other tools in the database handling such cases.

As a by product, DCD can also help with overly eager firewalls forcibly removing seemingly idle network connections. I found the following posts and the references therein very useful:

Linux Scripting, Part IV- Scripting for Longevity

We’ve learned a lot about commands, utilities and how to create a script, but we need to discuss the importance of scripting for longevity.

What is Scripting for Longevity?  We have a tendency to focus on scripting to automate something WE might not want to perform manually, but avoid what we think might void our value.  We may try to ensure there is necessity for our role or our knowledge as we create scripts.  This can be built into the execution process, scheduling, arguments, pre-run or post-run steps.  This doesn’t make us an asset, but a liability and against what I call, “the Code of Conduct” when automating.

Questions

The questions you have to ask yourself as you script out solutions are:

sqlldr, direct path loads and concurrency in 12.2 and later

In my previous post I showed you that Oracle’s SQL loader (sqlldr) utility has a built-in timeout of 30 seconds waiting for locked resources before returning SQL*Loader-951/ORA-604/ORA-54 errors and failing to load data. This can cause quite some trouble! Before showing you the enhancement in 12.2 and later, here is the gist of the previous post.

Concurrency in Oracle sqlldr 12.1 and earlier

To show you how sqlldr times out I need to simulate an exclusive lock on the table in sqlplus for example. That’s quite simple:

sqlldr, direct path loads and concurrency in 12.1 and earlier

I have recently come across an interesting issue related to concurrent data loading into the Oracle database using sqlldr’s direct path mode. Although I investigated the situation on 12.1.0.2, I found that the same holds true in 19.4 as well when using the defaults. I reconstructed the case, although it is simplified a little to bring the point home.

The environment I used to put this post together is Oracle Restart 19.4.0 on Oracle Linux 7.6.

Test overview

For this test I am running concurrent sqlldr sessions to demonstrate the case. I am conscious of that fact that I could have used external tables, but then I wouldn’t have been able to write this post :)

Assume there’s a table named t2: