# Disaster Recovery using Merge

Wait, WHAT?

Does that headline sound fishy? Actually, Diff and Merge (WinMerge, to be exact) were my last resort in this disaster scenario. The final outcome could be told quite shortly, though this scenario serves as a good example how Murphy might hit you anytime… but first things first:

# asmcmd “connected to an idle instance” – not

This is more a note to myself in case I’ll encounter a similar environment. But maybe it helps others – at least my search results weren’t suitable to Windows in the first place.

# Issue

C:\> set ORACLE_HOME=C:\path\to\grid\home
C:\> set ORACLE_SID=+ASM1
C:\> asmcmd
connected to an idle instance.

# JDBC, Linux and Entropy

Some troubles — especially those happening only sporadically — are not so easy to shoot and call for a deeper understanding of the matter. In the following real-world example this means: SQL*Net Tracing and some knowledge about the inner workings of the server’s operating system, particularly random number generation.

This case was suited well to demonstrate an approach to trouble-shoot connections to Oracle databases.

# Historical SQL Plan from Statspack using DBMS_XPLAN in 12c

Up to Oracle 11.2 it was possible to display archived SQL plans from Statspack using DBMS_XPLAN. I make use of this in some of my scripts and SQL Developer Reports since I first saw this in Christian Antognini’s Book “Troubleshooting Oracle Performance“.
But in 12c (here: 12.1.0.1 on Linux), there’s a piece missing now:

select * from table(dbms_xplan.display(
table_name   => 'perfstat.stats$sql_plan', statement_id => null, format => 'ALL -predicate -note', filter_preds => 'plan_hash_value = '|| &&phv ); ERROR: an uncaught error in function display has happened; please contact Oracle support Please provide also a DMP file of the used plan table perfstat.stats$sql_plan
ORA-00904: "TIMESTAMP": invalid identifier


So it looks like STATS$SQL_PLAN wasn’t synchronized to the changes in 12c’s PLAN_TABLE. Maybe because the timestamp wouldn’t make much sense there, anyway, maybe simply because Oracle forgot. ==> Quick and most certainly unsupported workaround: ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));


Another workaround could be to create a separate view with an additional dummy timestamp column an reference the view. I chose to stick with the invisible column solution so I won’t have to create new objects in the DB and change scripts to use these objects.

Hopefully, this will be solved in 12.2 – at least, I had filed an SR / Enhancement Request with Oracle Support.

# Data Pump and AQ_TM_PROCESSES

First things first:

Thou shalt not explicitly set AQ_TM_PROCESSES=0 !

Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process.

However, if you don’t reset this parameter afterwards, you might run into the following scenario the next time you do a Data Pump export or import:

# Datapump Bug: Scheduler Jobs Not Imported in 11.2

Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP.

It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window.

Here’s an excerpt of a Data Pump Logfile:

ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job attribute

Actually, the correct attribute name for this procedure would be “stop_on_window_CLOSE” instead of “stop_on_window_EXIT”. The latter is an attribute of the JOB_DEFINITION object type which in my eyes is inconsistently named in respect to the SET_ATTRIBUTE procedure.

The bug is fixed, but as of Nov. 2014 there is no one-off patch for version 11.2.0.4. The existing workaround is to ignore the error and recreate the job after the import .

If you run into this bug and it bugs you a lot, the other option is to ask for a patch for this issue.

# ORA-01722 (invalid number) over and over again

This is a translation of my German article which was requested through “Google Translate” many times. It seems that throughout the years this error and its mostly simple causes haven’t lost their significance.

Here’s a sample scenario:

• A table’s VARCHAR2 column shall be searched for numeric entries;
• We use a query with a WHERE clause that constrains the scanned rows to only those containing numeric values.
• Despite that, “ORA-01722” is thrown.

So,  what happened?

• The Oracle documentation states: “When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.” Think of an implicit TO_NUMBER being applied to the column.
• This implicit TO_NUMBER might be applied before the filter predicates of your WHERE clause.
• This in turn leads to an error, when an alphanumeric entry is hit — even when this entry wouldn’t be part of the scanned set if the filters were applied first.