Oracle Database Result Cache Troubleshooting

I was troubleshooting an issue where the Oracle Database Result Cache did not get used when the RESULT_CACHE hint was specified inside a subquery. As the main query has a sysdate inside, the RESULT_CACHE Hint can not be specified for the main query, but only for the subquery.

1
2
SELECT SQ.*, sysdate FROM
(SELECT * FROM tableX, ....) SQ;

When using “/*+ RESULT_CACHE */ inside of Subquery, result cache was not (more...)

How to speed up slow unicode migration of a table with xmltype columns

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update   (more...)

Truncate 12c

Here’s one of those little improvements in 12c (including 12.1) that will probably end up being described as “little known features” in about 3 years time. Arguably it’s one of those little things that no-one should care about because it’s not the sort of thing you should do on a production system, but that doesn’t mean it won’t be seen in the wild.

Rather than simply state the feature I’m going to demonstrate it, (more...)

Band Join 12c

One of the optimizer enhancements that appeared in 12.2 for SQL is the “band join”. that makes certain types of merge join much more  efficient.  Consider the following query (I’ll supply the SQL to create the demonstration at the end of the posting) which joins two tables of 10,000 rows each using a “between” predicate on a column which (just to make it easy to understand the size of the result set)  happens (more...)

Building OSB 12c releases on resource level using Maven

A while ago I published a Whitebook (in Dutch) about building OSB 12c releases on resource level using Maven. In the Whitebook, you could read which functionality we were missing in the regular Oracle Service Bus plugin for Maven and how we were able to create this functionality using a custom Maven plugin.

I have added the code of this custom Maven plugin to the following public repository: https://bitbucket.org/whitehorsesbv/servicebusplugin

Used settings

The custom Maven plugin (more...)

Progress with the Oracle Integration Cloud Adapter SDK

In the past few days, I have been making some progress with using the ICS Cloud Adapter SDK. 
Today, I created my first shell adapter - the design time views can be seen below!




The journey so far: 
 * Reading through the documentation [Ongoing]
 * Developing the empty adapter and deploying it for design time and runtime [Check]

There are a number of integration use-cases that (more...)

Fix “BEA-000394: Deadlock detected” for Weblogic with ADF Applications 12.2.1.2

If you try the newest version 12.2.1.2 (or maybe earlier versions too) you see, that your managed server does not restart, when

  • you have deployed your ADF application(s)
  • you try to restart the managed server with ADF
  • you use intentionally or not shared libraries in your weblogic-application.xml (see example below)
<?xml version = '1.0' encoding = 'UTF-8'?>
<weblogic-application xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                      xsi:schemaLocation="http://www.bea.com/ns/weblogic/weblogic-application http://www.bea.com/ns/weblogic/weblogic-application/1.0/weblogic-application. (more...)

#DOAG2016 wrap up

doag_wegweiser

Another DOAG is over and here are my summary.

Day 1

Unfortunately the early train from duesseldorf arrives not before 9:30, so I missed the first morning slot. But on the other hand I this was the slot where I had to decide between 8 presentations, which I’m interested in.

The first soft skill presentation was about creative techniques beyond brain storming. The mentioned 3 techniques was all unknown for me, maybe I give it (more...)

Problems with big SGAs (>200G) on Linux

I recently had an issue where a database with 240GB SGA (1 huge shared memory segment) configured with hugepages on a system with 512G RAM was suddenly becoming instable and new logons were denied with these error message:


ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Linux-x86_64 Error: 22: Invalid argument
Additional information: 2667
Additional information: 1736718
Additional information: 215016800256

This was strange because ipcs -a showed all shared memory segments (more...)

Synchronise Sequence value with 12c Identity Column

My journey into 12c continues with the use of identity columns, this time regarding data that had been imported from another database, but sequences haven't been updated.

Ensuring the next number returned from a sequence matches the current value from the table appears to be a common problem, my thoughts are described here. The biggest trouble is linking up the sequence to the column so we could automate the process.

TL;DR

We can now (more...)

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.


Well, it's one thing to say 'create all new tables like this', (more...)

Easy SOA releases with JGitFlow

If you use GIT as your source control system and if you use maven, the jgit-flow plugin is a massive time-saver, especially when we release a slightly large application with multiple modules (Each with it's own pom file). 

Two steps: 
 mvn external.atlassian.jgitflow:jgitflow-maven-plug in:release-start
and 
 mvn external.atlassian.jgitflow:jgitflow-maven-plug in:release-start

do the job. 

The above sequence basically updates the pom file versions to a release version (e.g. from 1. (more...)

Test Driven SOA – citrus for powerful SOA test coverage

Reading parts of Test-Driven Development for Embedded C" by James W. Grenning inspired me to take another look at this area and look for something new,  fresh and powerful.

I don't think we need much convincing on the importance of  automated test coverage (if someone does, please read the first chapter of the book mentioned above, especially the section on "Physics of TDD" that tries to quantify the high long-term costs of "Debug later programming" (more...)

Oracle 12c: Indexing JSON in the Database Part III (Paperback Writer)

In Part I and Part II, we looked at how to index specific attributes within a JSON document store within an Oracle 12c database. But what if we’re not sure which specific attributes might benefit from an index or indeed, as JSON is by it’s nature a schema-less way to store data, what if we’re not entirely sure […]

Digging into ADD SCHEMATRANDATA … what is #GoldenGate doing?

In my post on the differences between ADD TRANDATA and ADD SCHEMATRANDATA, I highlighted the differences between the two ways of adding supplemental logging to tables. One of the things I pointed out was that ADD SCHEMATRANDATA doesn’t add any supplemental log groups (SLG). Without SLGs then how does ADD SCHEMATRANDATA work? That was the question I was left with. So I started digging around to find the answer and I think you may be (more...)

rlwrap with #GoldenGate GGSCI

Since I published posts on how to retrieve command history within GGSCI using the Oracle provided commands of “history” and “fc”, I was pinged a couple of times by some friends asking about “rlwrap” usage with GoldenGate. The answer is a simple yes, rlwrap can be used with Oracle GoldenGate.

What exactly is “rlwrap”?

According to the readme file at http://utopia.knoware.nl/~hlub/uck/rlwrap/#rlwrap, rlwrap is a ‘read-one wrapper’. (more...)

Edit #GoldenGate commands from GGSCI

Ever get tired of typing the same command over and over again in Oracle GoldenGate GGSCI? Well, Oracle has provided a history recall function into GGSCI, I talked about his in an earlier post. This post on history recall can be found here.

In this post, lets take the command recall and how these commands can be edited. In Oracle GoldenGate 12c, Oracle has provided an GGSCI command called “FC”. The “FC” command is used (more...)

To TRANDATA or To SCHEMATRANDATA? … That is the #GoldenGate questions of the day!

If you are familiar with using Oracle GoldenGate, you know that on the source side of the equation you have to enable supplemental logging and sometimes force logging on the database. I traditionally do both just to make sure that I capture as much as I can into the redo stream from the transactions on the database. For Oracle GoldenGate purposes, this is not the only thing you need to turn on to ensure all (more...)

Oracle 12c: Indexing JSON in the Database Part II (Find A Little Wood)

In Part I, we looked at how you can now store JSON documents within the Oracle 12c Database. For efficient accesses to JSON documents stored in the Oracle database, we can either create a function-based index based on the JSON_VALUE function or on JSON .dot notation. These indexes are useful for indexing specific JSON attributes, […]

Oracle 12c: Indexing JSON In The Database Part I (Lazarus)

One of the very cool new features introduced in Oracle Database 12c Rel 1 is the ability to store JavaScript Object Notation (JSON) documents within the database. Unlike XML which has its own data type, JSON data can be stored as VARCHAR2, CLOB or BLOB data types, but with a JSON check constraint to ensure the […]