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.