Skip to main content


Showing posts from 2016

Oracle 12cR2 Multiteant book is out!

After all the waiting, the book is out! Available for example on Amazon, both as Kindle ebook and as a paperback.
You can see the actual table of contents in the Amazon preview; in short, it covers the whole range of multitenant related topics. Both multitenant-specific, as well as the old and proven features, that were updated to work with a multitenant database.

With a bit of luck, you'll get the book sooner than me - my copy is still somewhere in the mail :-)

Small updates: Oracle ACE Associate; and the upcoming book

A short update - I am now an ACE Associate, spreading the good word on Oracle technology:-)

Another update is the upcoming book: I am co-authoring book on the Multitenant option in Oracle 12cR2. The book is for pre-order on Amazon (unfortunately Amazon seems to have troubles with putting my name there, grrr...).

The original planned date was sometime around Open World 2016, but apparently nobody knows where 12cR2 will actually come out (and due to NDA, the book can be published only after 12cR2 is released to public), so there is now no estimated date at all. We just have to wait.
However, the book is really taking shape, all of the contents is written and we are now finishing reading the proofs, catching last typos and mistakes.

So stay tuned for 13 chapters or 400 pages or so, focusing all on Multitenant - and there is a lot of things to cover!

PDB saving state does not save its state on shutdown

When came out, one of the gripes was that upon a CDB start, all the PDBs were in the mounted mode. The DBA had to open them manually, or use a database trigger to do that. introduced SAVE STATE - according to the docs:
For example, if a PDB is in open read/write mode before the CDB is restarted, then the PDB is in open read/write mode after the CDB is restarted; if a PDB is in mounted mode before the CDB is restarted, then the PDB is in mounted mode after the CDB is restarted.
The trouble is that this is simply wrong, it does not work like this. Oracle has a table externalized as  DBA_PDB_SAVED_STATES and this stores the state. The table is updated only by the SAVE STATE command - and reflects the status when the SAVE STATE was issued, not when the database goes down.
It simply stores the open mode of the database and the CDB will open the database in this mode when the CDB opens. Lack of a row implies MOUNTED mode, i.e. the CDB won't do anything.
The row is dele…

Small addendum to the lying (Data Guard) broker

A friend of mine (Deiby Gómez) pointed me to an interesting article on MOS 1956103.1 - Warning: standby redo logs not configured for thread on db_unique_name/db_unique_name.

It essentially describes the same issue I described in
Don't trust the lying (Data Guard) broker - the newly created SRLs are not assigned to a particular thread and the VALIDATE command does not like it, although the standby is perfectly happy and will grab the SRLs as necessary, as it always did before 12c.

The Metalink note adds a solution - to assign the the SRLs to the threads manually during creation. The syntax is
alter database add standby logfile thread 1 group 1 'file spec' size .... This thus disables the auto-assign to the thread that needs it, but that should not matter. We usually size all the threads uniformly and assign enough SRLs to all of them, in other words we expect even distribution of SRLs to threads. Thus doing it manually is not a bad thing.

How many columns in a query

Everybody knows that the limit for number of columns in an Oracle table is 1000. It is actually limit of all columns in the table, including internal ones, virtual, unused but not yet dropped and so on.

But what is the limit for a query?

Let's start with a simple table, called many_columns. It has 1000 columns, all NUMBERs, to make things easy. Columns are named COLUMN_0001 to COLUMN_1000.

And I insert 1 row into the table:

insert into many_columns(COLUMN_0001) values (1); commit;
So what happens with an innocent query?

select m.*, n.* from many_columns m, many_columns n;
Well, nothing special - SQL*Plus is happy to return 2000 columns.

Obviously, there must an upper limit, right? At the very maximum, OCI specifies value for column count as ub2, i.e. max 65535.
However, SQL*Plus complains much sooner: the limit seems to be 8150. I added one more table - many_columns2 with just then columns. The first query to go over the limit, with 8151, fails with:

select m01.*, m02.*, m03.*, m04…

Docker machine - wonderful idea, too many bugs?

When doing various experiments with docker, I painfully realized that btrfs keeps a lot to be desired.

Wonderful idea, terrible user experience. First of all, df lies, and you are supposed to run btrfs balance often. Maybe it's because of the way docker uses it - it creates a ton of large images.
Eventually you touch all chunks and rebalance stops working completely. Now you desperately delete things, hoping to get chunk free and let rebalance get thins back to order. Or not - and you end up nuking the server and reinstalling.
Or you perhaps end up crashing up the server and the btrfs won't mount anymore...

So after going through 5 servers (OL7.1 in VBox), I moved onto docker-machine. Wonderful idea - and it does not use btrfs, yay!

However, it also has it's bugs... and pretty ugly. First of all, the latest stable boot2docker 1.9 has a kernel bug that causes Java process to become zombies and docker container won't finish. See…

Docker: Handling multiple copies of the same database/container

Inspired by Frits Hoogland's excellent article on Oracle running in Docker, I started building a lot of Oracle containers. It's nice to have multiple different Oracle versions available at your fingertips for research, product testing and so on.

However, one thing annoys me with Docker: if you want any usable IPC, you need to use --ipc=host. This means that all the images share the same namespace and, furthermore, when a container exits it sometimes does not clean up the IPC entries.

As you probably know the IPC is used by Oracle for SGA memory and semaphore sets. It identifies which belong to which instance, by combining SID and ORACLE_HOME.

This in turn means that you cannot run two databases with the same SID and ORACLE_HOME at the same time... which is usually fine, but not so with Docker and --ipc=host. In this case we do want to run multiple containers built off the same image, or perhaps have multiple similar images with the same ORACLE_HOME, differing in minor details…