Oracle Midlands Presentation

Ever wonder why your SQL execution plan has changed? Wondering why your boss is shouting something about “traders can’t trade” or “shoppers can’t spend” or “well that’s the Airport shut!” ?

When you write SQL, Oracle runs it through the Optimiser to determine the fastest way to access the data. This access path sometimes changes without anyone noticing, sometimes with sub-optimal results. I’m giving a presentation at an OracleMidlands even (more...)

12C Paging through Data and Restricting Result Sets

Prior to Oracle 12C, if you wanted to page through result sets only returning a bit at a time, or only wanted the first dozen lines of data, it was a right pain. Writing your SQL, then having it as an in-line view with order by, and a rownum on the outer view. And the whole or that in an in-line view with another named rownum around that to create a sliding window. Horrid. Horrid. (more...)

Oracle 12C Problem with datapatch. Part 2, the “fix”

so after much too-ing and fro-ing with Oracle Support, we finally have a solution to my datapatch problem. Prevent the timeout from happening when running datapatch! We know which part of the code was timing out, and we can do this by setting an undocumented event: 18219841.

Here’s a show test to show you how that was done:

SQL: select dbms_sqlpatch.verify_queryable_inventory from dual;

ORA-20008: timed out. Job Load_opatch_inventory_1execution time is more than  (more...)

Oracle 12C Problem with datapatch

I was minding my business, patching an Oracle 12C RAC Cluster when the darned patch failed

./datapatch -verbose

SQL Patching tool version on Mon Aug  3 20:47:31 2015
Copyright (c) 2015, Oracle.  All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_9841_2015_08_03_20_47_31/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual (more...)

Oracle Management Database in Grid Infrastructure: -MGMTDB


Why? Why is is now mandatory to have the cluster management database as an Oracle CDB, with a PDB having the same name as the cluster? It’s not that I object to having another 1GB of memory lost to this DB, and up to 10GB of disk in the initial ASM Disk Group. It’s this:

You have called it -MGMTDB

That means I now have directories all over my Linux / Unix structure called (more...)

Oracle on 4096 (4k) sector disks don’t work (ish)

I recently came across 4K (4096 byte) sector drives. They are a fairly new thing and have come about so drives can exceed the 2TB limit imposed by having 512byte sectors. The details behind this can be found here, in much greater detail than I need to understand.

What I do understand is that Oracle doesn’t deal with 4K sectors (4Kn) very well and it shows up in a couple of ways. Don’t get me (more...)

Locking Privileges in Oracle

What permissions do you need to lock rows on an Oracle table?
What about to lock the whole table?

It’s not quite as much as you may think!

Lets have a couple of users; schema_owner and user1

SQL> show user
SQL> create user schema_owner identified by schema_owner;
User created.
SQL> grant connect,resource to schema_owner;
Grant succeeded.
SQL> grant unlimited tablespace to schema_owner;
Grant succeeded.
SQL> create user user1 identified by user1;
User  (more...)

Oracle Cluster Health Monitor – changes in

From Oracle, the Oracle Cluster Health Monitor becomes an Oracle database by default [replacing the old Berkley DB], and it’s called “-MGMTDB” (note the leading “-“)

cat /etc/oratab

+ASM1:/u01/app/grid:N # line added by Agent
-MGMTDB:/u01/app/grid:N # line added by Agent

It lives on one of the nodes on your RAC cluster and occupied space on Disk Group provisioned during install.

The DB will take about 750MB, so even more (more...)

Oracle ACFS on Linux 7 don’t work

I installed OEL 7.1…

uname -a
Linux rac12c01 3.8.13-55.1.6.el7uek.x86_64

Then I installed Grid Infrastructure and Database and looked to configure ACFS for the database files, ready for DB create. And it won’t let me. In asmca, the tabs are greyed-out.

Lets just investigate that:

acfsdriverstate supported
ACFS-9459: ADVM/ACFS is not supported on this OS version: 'unknown'

Unknown! Great. This means…patching. After a (more...)

Linux Annoying Defaults

So why does Linux have an alias for “ls” which turns on colour, by default, making some text impossible to read? eh?

alias ls=’ls –color=auto’

To stop this temporarily, you can “unalias ls”, but to stop it permanently for everyone:

vi /etc/profile.d/

comment out the line:

alias ll='ls -l --color=auto' 2>/dev/null
alias l.='ls -d .* --color=auto' 2>/dev/null
# alias ls='ls --color=auto' 2>/dev/null

And that’s it. Cured for life.


While I’m on, why (more...)