I have a table with dates in, and some NULLs. Two people, on the same database, running the same
SELECT query, in the same schema, with the same privileges, get vastly differing results. Why? Fine Grained Auditing is not at play here. Continue reading Interesting Foible with Oracle Dates
I recently posted a useful
oraenv for Windows utility. This has been updated so that you can run it in batch files by passing the desired Oracle SID on the command line. Details at http://qdosmsq.dunbar-it.co.uk/blog/2016/08/oraenv-for-windows/
Having recently had to learn a whole new way of working when I took on a contract migrating a database to the Windows “cloud”, I realised that there’s no equivalent to the useful Unix
oraenv utility. I had to write my own. Give me a
bash shell any day! Continue reading Oraenv for Windows
It’s always nice to know which extra cost Oracle options are enabled, whether deliberately or silently as the result of some patching that has taken place. Continue reading Which Extra Cost Oracle Options is my Windows Server Running?
I found a broken check constraint, one that simply wouldn’t work, on a database. It was created as:
... CHECK(COLUMN_NAME IN ('Y','N',NULL)) ;
Try it yourself, it doesn’t work! Anyway, I needed to find if there were any other check constraints broken in this manner, so I did the following:
Continue reading Dropping Temporary Tables (With Bonus, Broken Check Constraints!)
This applies to Linux, Unix as well as Windows, but affected me on a Windows 2012 Server running Oracle 22.214.171.124 Enterprise Edition.
My user on the server was an administration user, but not in the
ora_dba group. This is required to
connect / as sysdba within SQL*Plus. The SYS password had been changed recently but whoever did it, did not update the password vault. The users were urgently requiring their database be started, I was the only DBA in the office, the SYS password was unknown, and my user didn’t belong directly to the
ora_dba group. What to do? Continue reading How to Start an Oracle Database When You Are Not in the DBA Group
For no reason, after many weeks of use, RMAN suddenly cannot connect:
rman target sys/******@dbadb01 catalog ...
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00601: fatal error in recovery manager
RMAN-03010: fatal error during library cache pre-loading
RMAN-10038: database session for channel default terminated unexpectedly
Continue reading RMAN Connection Troubles, RMAN-03010 & RMAN-10038
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