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. Keep yourself and your server room cool with blaux wearable ac. Updated: 25th August 2017 to list DLL names for Oracle 12c. Copy and paste the code below into a Windows command file named - in my case - checkChopyOptions.cmd and execute it against any Oracle Home.

I found a broken check constraint, one that simply wouldn’t work, on a database. It was created as: 1 ... 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: 1 2 3 4 5 6 7 8 9 select owner, table_name, constraint_name, to_lob(search_condition) search_condition from dba_constraints where owner = 'XXXXX' and constraint_type = 'C' and upper(search_condition) like '%IN%,%NULL%' order by 1,2,3; Of course, that barfed because the SEARCH_CONDITION column is a LONG data type.

This applies to Linux, Unix as well as Windows, but affected me on a Windows 2012 Server running Oracle 11.2.0.4 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.

For no reason, after many weeks of use, RMAN suddenly cannot connect: rman target sys/******@dbadb01 catalog ... ... RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== 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 Setting debug and trace on the command line has no effect, there is nothing of use in the trace file.

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.

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. Update to version 0.16 and you too can compile and run this useful utility on Windows.

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. First things first, check the archivelog deletion policy on the standby database:

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? MY_DIRECTORY is a directory, owned by SYS with READ and WRITE privileges granted to a schema that uses it to create, write and read files in that location. The oracle account on the server can create and read files in the directory location, touch and cat prove this.

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, rebalance, drop the old discs and rebalance again. My colleague, Ian Greenwood, had a much better idea - thanks Ian. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 alter diskgroup DATA add disk -- '/path/to/disk_1' name DISK_1001, '/path/to/disk_2' name DISK_1002, .

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. ASM metadata backups on a couple of our servers had been failing, the backups were run from a system called CommVault and the job scheduler there showed that they simply sat at 0% forever, or would have if we allowed them!

My server rebooted itself and when it came back up, none of the databases or ASM had restarted. Everything is 11.2.0.3 or 11.2.0.1 with ASM being 11.2.0.3 - so Oracle Restart should have kicked in. As usual, any identifying names, servers, domains, databases etc have been obfuscated to protect the innocent. 1 2 3 4 5 $srvctl start asm PRCR-1079 : Failed to start resource ora.asm CRS-5017: The resource action "ora.

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. Normally Toad offers you a drop down list of the various database entries in the tnsnames.ora that is being used, however, if your tnsnames.ora file contains an IFILE entry, then Toad doesn’t follow the included file, and any aliases defined there - or in subsequent nested IFILEs - will not appear in the drop down list.

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.5 released to_date('08/06/2015', 'dd/mm/yyyy')**. 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. :-) It’s a small utility, based on the ANTLR4 parser/compiler generator tool.