Tnsnames.ora, IFILE and Network Drives on Windows

I’ve recently begun a new contract migrating a Solaris 9i database to Oracle 11gR2 on Windows, in the Azure cloud. I hate windows with a vengeance and this hasn’t made me change my opinion!

One of the planned improvements is to have everyone using a standard, central tnsnames.ora file for alias resolution. A good plan, and the company has incorporated my own tnsnames checker utility to ensure that any edits are valid and don’t break anything.

I found that the tnsnames.ora in my local Oracle Client install, was not working. Here’s what I had to do to fix it. Continue reading Tnsnames.ora, IFILE and Network Drives on Windows

TraceMiner – An Oracle Utility to Mine 10046 Trace Files

Have you ever needed to trawl through an Oracle Trace file to extract the SQL statements executed and found a whole load of bind variables have been used, so you need to find the BINDS section, extract the values, and virtually paste them into the parsed SQL statement?

No? This utility isn’t for you then. Continue reading TraceMiner – An Oracle Utility to Mine 10046 Trace Files

Archivelog Deletion Policy Changes Don’t Always Take Immediate Effect.

The standby database had the RMAN archivelog deletion policy set to ‘NONE’ instead of being ‘APPLIED ON ALL STANDBY’ and the FRA filled up to within an inch of its life, or 99% of its allocated quota! Not a major problem as this database was not in production, but still, an alert is an alert and has to be dealt with. However, things did not go quite as expected. Continue reading Archivelog Deletion Policy Changes Don’t Always Take Immediate Effect.

Add and Drop Discs From ASM in a Single Command

Fax Online    Send article as PDF   

Recently I was tasked to do something that I hadn’t done before. I was required to swap out all the existing discs in the two diskgroups +DATA and +FRA, with minimal downtime. Almost all the places I looked seemed to indicate that I had to add the new discs, re-balance, drop the old discs and re balance again. My colleague, Ian Greenwood, had a much better idea – thanks Ian.

alter diskgroup DATA add disk
'/path/to/disk_1' name DISK_1001,
'/path/to/disk_2' name DISK_1002,
'/path/to/disk_n' name DISK_100N
drop disk
-- This is Spinal Tap!
rebalance power 11;

Then the same again for +FRA and we were done. Well, I say done, once the rebalance had finished we were done, and the Unix team could then remove completely, the old discs. That did need ASM to be bounced though, which was a bit of a nuisance for the (one) database on the server, but the users were happy to let us take it down.

Job done and very little messing around. Sometimes, it’s helpful to look at the Oracle Manuals before hitting MOS or Google (other web search engines are available – but they are not as good!) for hints when you have new stuff to do.

Yes, I spell disc with a ‘c’ while Oracle spell it with a ‘k’. 🙂

Tnsnames Checker Utility

I have made available for free a utility that will parse a tnsnames.ora file and report back on anything that it doesn’t like such as duplicate entries, invalid characters, redefinitions of parameters, errors etc etc.

  • Version 0.4 released to_date('07/11/2015', 'dd/mm/yyyy')*.
  • Version 0.3 released to_date('06/12/2014', 'dd/mm/yyyy')*.

*Oracle joke! 🙂
*Also avoids Date confusion for my American readers. 🙂
Continue reading Tnsnames Checker Utility

Impdp Hangs Importing Materialized Views

A simple exercise to refresh a schema in a test database caused no end of problems when it hung at 99% complete. The last message on screen indicated that it was importing the Materialized Views (yes, with a ‘Z’). After a long time, the DBA running the import killed it, cleaned out, and restarted the whole process. Exactly the same happened. Continue reading Impdp Hangs Importing Materialized Views