Club Oracle in London – 12th November 2014

Just a timely note to publicise the e-DBA and Jonathan Lewis #cluboracle event in London on 12th November 2014.

I’m a great fan of community events, as I hope my blog, tweets, presentations and support of the UKOUG, chairing many SIGs over the last few years, will attest. It’s the second best way to learn anything, talking with your peers who have already worked it out, and those who have the same problem to (more...)

When to use the NOLOCK hint in SQL Server

I frequently hear of, and see, developers and DBA’s using the NOLOCK hint within SQL Server to bypass the locking mechanism and return their data sets as soon as possible. There are times when this is OK, such as when you are running an ad hoc query and are only interested in approximate results. It is somewhat less OK to write this hint into application code and reports, unless you don’t actually care whether the (more...)

Extending an ACFS filesystem dynamically.

To extend an ACFS cluster filesystem dynamically, we need to use the acfsutil command:

node01:/u01/grid>/sbin/acfsutil size +10G /u02
acfsutil size: ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required
                           version (11.2.0.4.0) for unlimited volume expansions.

Oh dear, not 11.0.2.4, so you can only extend volumes dynamically a (more...)

Grid Infrastructure Disk Space Problem – CHM DB file: crfclust.bdb

The Grid Infrastructure filesystem was reporting that it was a bit full today (release 11.2.0.4). This was tracked down to the “crfclust.bdb” file, which records information about the cluster health for monitoring purposes. It was 26GB. It’s not supposed to get bigger than 1GB so this is probably a bug, but let’s explicitly resolve the size issue right now and search Oracle support later. Worst case, bdb (Berkerley Database) files get (more...)

Releasing to schemas the easy way

Sometimes we occasionally just miss the obvious, for years. Just noticed that an easy way to release code to a particular schema is to login as your normal DBA user (USER1) [as preferred by audit], use the alter session command to switch to point to the release schema (USER2) and run your DDL. Oracle behaves, from an object-owner perspective, as if you are logged-in as the schema owner without all the negative aspects of actually (more...)

Oracle Audit Control part 2 – purging records

Now that you have got your audit table somewhere a little more sensible (i.e. not in the SYSTEM tablespace), there’s probably a policy about how many audit records should be kept.

Thoughtfully, the DBMS_AUDIT_MGMT package provides some of what you need to keep the audit records in check. However, a little more thought by Oracle would have helped. Lets see what I mean.

 

First we need to initialise for audit control. You can check (more...)

Oracle Audit Control part 1 – location of the audit table

One of the oldest problems with the Auditing capabilities within Oracle is that the SYS.AUD$ table resides in the SYSTEM tablespace. Unless you are rigorous in ensuring that your audit records are routinely pruned to keep the table manageable, it can single-handedly make the SYSTEM tablespace enormous.

Historically, we used to move the table and its associated objects to a new tablespace ourselves. In Oracle 7 is was a drop and re-create. Later we performed (more...)

SCAN listener logs – where are they?

These are a bit of a pain as their location isn’t intuitive. So to remind me where they are:

Login to the server(s) as the grid owner and check the scan listener status. This will show you the location of the listener log. cd to just below the diag directory and you’re off!:

server-name:/u01/grid>ps -ef | grep SCAN
 grid 8542 8282 0 10:20 pts/0 00:00:00 grep SCAN
 grid 9349 1 0 Mar07 ? 00:07:33  (more...)

Ultracrepidarianism

Fridays word is ultracrepidarianism: the habit of giving opinions or advice outside ones sphere of knowledge or competence.

Use: today I shall be wading through a sea of ultracrepidarianism, just like yesterday, but today I shall have a collective noun to use. And a few less opinions to give.


Filed under: Uncategorized

Developers Killing Sessions

When you end up spending a far great a percentage of your day than seems sensible killing off Java connections that Developers have carelessly left lying around, locking objects all over the place, you need a solution to get them to go away. The solution is to let them do it themselves!

I’m not advocating granting ALTER SYSTEM to Developers! That way madness lies, or certainly some unintended consequences. I’m all for Dev’s having a lot (more...)