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 -- drop disk -- DISK_0001, DISK_0002, ... DISK_000N -- -- 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 18.104.22.168 or 22.214.171.124 with ASM being 126.96.36.199 – so Oracle Restart should have kicked in.
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
*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
While checking out a dataguarded database prior to being handed over into production, I needed to test that both
dgmgrl could carry out a switchover and failover from the (stand-alone) primary db (ORCL_PDB) to the physical standby database (ORCL_SBY), and back again. Continue reading Interesting Data Guard Problem
The Oracle database allows the users to change their passwords as follows: Continue reading So, How Do You Change a User’s Password
In this, the second part of the Introduction to Oracle Datapump mini-series, we take a look at importing dump files using
impdp. If you missed the first part which concentrated on exporting with expdp, have a read of it here. Once again, the following is a quick introduction for people like me – running Oracle on Linux and slightly averse to change! 😉 Continue reading Introduction to Oracle Datapump – Part 2