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
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
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.
A process that called ”UTL_FILE” was failing in the test system, but worked fine with exactly the same set up in production. Why? The error was ORA-29283: invalid file operation. How do we find out exactly why it was failing? Continue reading UTL_FILE Operation fails with ORA-29283
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
-- 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’. 🙂
My server rebooted itself and when it came back up, none of the databases or ASM had restarted. Everything is 126.96.36.199 or 188.8.131.52 with ASM being 184.108.40.206 – so Oracle Restart should have kicked in.
Continue reading How to Fix a Broken ASM SPFILE, held within ASM
Sometimes an ASM instance hangs for no apparent reason and this causes problems when backing up the ASM Metadata. Running queries against
V$ASM_DISK and similar views may also hang. This blog post should go some way to helping diagnose the problem, and providing a fix. Continue reading Asmcmd or ASM Instance Backups or Queries Hang
Have you ever wanted a quick and easy way of converting all those database entries in your tnsnames.ora file, into something that Toad can use to populate the “sessions” grid? Read on. Continue reading Convert a Tnsnames.ora File to a Toad Session Import File
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
- Version 0.3 released
*Oracle joke! 🙂
*Also avoids Date confusion for my American readers. 🙂
Continue reading Tnsnames Checker Utility
Ever wanted to set a variable to the name of another variable, and from there, somehow get the value of the other variable? I did, recently, and this is what I had to do. Continue reading Shell Variable Indirection in a Database Build Script
There is, out there in Oracle Land, a silent database killer. You never know when it will strike and it affects all databases right up to and including 12c. When it strikes, it does so silently, there is no evidence of its passing, until it is far too late. Continue reading Beware of the Silent Database Killer!
The following error popped up in an RMAN backup which was attempting to delete archived logs that had been backed up twice, at least, and were created more than two days ago:
Continue reading RMAN Error ORA-15028: Archived Log Not Dropped.
It was a simple enough request, flashback this particular database to a guaranteed restore point. What could possibly go wrong? Continue reading Oracle RAC – Flashback Database
Have you ever wanted to use a tool to parse the manually typed up “stuff” that lives in a tnsnames.ora file, to be absolutely certain that it is correct? Ever wanted some tool to count all the opening and closing brackets match? I may just have the very thing for you. Continue reading Tnsnames.ora Parser
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