Oracle Challenge #2 Solution

Apparently, quite a few people ran into this situation before, so I got a few correct answers  (some on Twitter, some on LinkedIn, and some as comments to the original post). The answer is that Oracle uses the ORACLE_HOME value to attach to the SGA, so a different ORACLE_HOME can cause this strange behavior. The … Continue reading Oracle Challenge #2 Solution

Histogram Threat

Have you ever seen a result like this:

SQL> select sql_id, count(*) from V$sql group by sql_id having count(*) > 1000;

------------- ----------
1dbzmt8gpg8x7	   30516

A client of mine who had recently upgraded to RAC, using DRCP (database resident connection pooling) for an application using PHP was seeing exactly this type of behaviour for a small number of very simple SQL statements and wanted to find out what was (more...)

Case Study – 1

It has been some time since I wrote an article walking through the analysis of information on an AWR report, but a nice example appeared a few weeks ago on Twitter that broke a big AWR picture into a sequence of bite-sized chunks that made a little story so here it is, replayed in sync with my ongoing thoughts. The problem started with the (highly paraphrased) question – “How could I get these headline (more...)

Histogram Hassle

I came across a simple performance problem recently that ended up highlighting a problem with the 12c hybrid histogram algorithm. It was a problem that I had mentioned in passing a few years ago, but only in the context of Top-N histograms and without paying attention to the consequences. In fact I should have noticed the same threat in a recent article by Maria Colgan that mentioned the problems introduced in 12c by the option (more...)

ASSM tangle

Here’s a follow-on from Tuesday’s (serious) note about a bug in that introduces random slowdown on large-scale inserts. This threat in this note, while truthful and potentially a nuisance, is much less likely to become visible because it depends on you doing something that you probably shouldn’t be doing.

There have always been problems with ASSM and large-scale deletes – when should Oracle mark a block as having free space on (more...)

Weird Logon Redirect Issue

We hit a perplexing issue over the Christmas break at one of our clients. They had a user who could not log in and was getting a redirect issue that we’d not encountered before.

We checked it out, made sure his roles were valid etc and found:

  • all other users could log in without any problems
  • this one account was not working via Single sign-on
  • after resetting his Oracle password and trying that method it (more...)

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. Environment Windows 2012R2 Oracle Grid Infrastructure 2 […]

“Collection iterator pickler fetch”: pipelined vs simple table functions

Alex R recently discovered interesting thing: in SQL pipelined functions work much faster than simple non-pipelined table functions, so if you already have simple non-pipelined table function and want to get its results in sql (select * from table(fff)), it’s much better to create another pipelined function which will get and return its results through PIPE ROW().

A bit more details:

Assume we need to return collection “RESULT” from PL/SQL function into SQL query “select (more...)

Impdp Resumable Mystery

Resumable operations are operations that can be suspended instead of failing, allowing us to fix the problem and resume the operation. When importing data using impdp, if (for example) a tablespace gets full, instead of failing the entire import (and force us to restart it from the beginning), we can identify that and add some … Continue reading Impdp Resumable Mystery

Oracle issues after upgrade to 12.2

Sometimes it’s really hard even to create reproducible test case to send it to oracle support, especially in case of intermittent errors.
In such cases, I think it would be really great to have access to similar service requests or bugs of other oracle clients.
So while my poll about knowledge sharing is still active, I want to share a couple of bugs we have faced after upgrade to 12.2 (and one bug from (more...)

Garbled display while running FMW installer on Linux

A colleague faced this while running FMW installer on a Linux machine. The display appeared like this








This thread gave a clue that it could have something to do with fonts. So I checked what all fonts related stuff was installed.

[root@someserver ~]# rpm -aq |grep -i font
xorg-x11-fonts-ISO8859-9-100dpi-7.5-9.el7. (more...) fails with CRS-2101:The OLR was formatted using version 3

Got this while trying to install RAC on Redhat Linux 7.2. fails with a message like

ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2017-11-09 15:43:37.883:
[client(37246)]CRS-2101:The OLR was formatted using version 3.

This is bug 18370031. Need to apply the patch before running

Easy(lazy) way to check which programs have properly configured FetchSize

  ,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
from v$sql s
and s.executions    >1
and s.fetches       >1
and s.module is not null
and s.command_type  = 3    -- SELECTs only
and s.program_id    = 0    -- do not account recursive queries from stored procs
and s.parsing_schema_id!=0 -- <> SYS
group by s.module
order by rows_per_fetch desc nulls last

TNS-12543: TNS:destination host unreachable

Scenario : Setting up a physical standby from Exadata to a non-Exadata single instance. tnsping from standby to primary works fine but tnsping from primary to standby fails with:

TNS-12543: TNS:destination host unreachable

I am able to ssh standby from primary, can ping as well but tnsping doesn’t work.  From the error description we can figure out that something is blocking the access. In this case it was iptables that was enabled on the standby (more...)

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 […]

Exadata Upgrade to – The Missing Step

I decided this week to be a little brave and upgrade one of the Enkitec Exadata racks to  I installed the Exadata image a few weeks ago, and have been waiting for a chance to upgrade clusterware to 12.2.  Thankfully, Oracle provides a very good note for this, but I did hit one large snag that should be documented.

The process for upgrading GI to 12.2 (more...)

Exadata Cell Patching – USB I/O Errors

I was playing around with the Exadata X2-2 in the Enkitec lab this weekend, and hit an interesting issue when patching the storage servers.  We were taking the system up to version for testing purposes.  I fired off the patchmgr script, and one of the storage servers failed when beginning the first phase of the patching cycle:

[root@enkdb03 patch_12.]# ./patchmgr -cells cell_group -patch -ignore_alerts

Reduce Hard Parse time using SQL Profile

Few days ago we had concurrency problem with "cursor: pin S wait on X" wait event. This wait event is mostly associated with parsing in some form.

After quick diagnosis I’ve found problematic query. It was fairly complex query which was executed very often with average 0.20 seconds of execution time. As this query was using bind variables, Oracle reused existing plan and problems with "cursor: pin S wait on X" wait (more...)

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

           While dropping a tablespace, getting error ORA-38301: can not perform DDL/DML over objects in Recycle Bin .

SQL> drop tablespace DATA including contents and datafiles;
drop tablespace DATA including contents and datafiles
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin



Before dropping the tablespace, disable recyclbin . 

1. Disable recyclebin:

SQL> ALTER SYSTEM SET recyclebin (more...)

RMAN-20005: target database name is ambiguous

                          While running rman commands from the catalog database, you might get the error RMAN-20005: target database name is ambiguous. , if the database was restored from another database.


rman target sys/oracle#51234@RMANBKP catalog rman_b2ccrmpd/rman@catdb

Recovery Manager: Release – Production on Fri Oct 14 10:34:05 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to (more...)