V$LOCK Missing Information

In our first BCOUG Tech Day conference, I presented my session “Look Inside the Locking Mechanism”. I presented this topics before a few times and prepared a few demos to show different locking scenarios. During the BCOUG Tech Day I did the same, while the only difference was that for the demo I used Oracle … Continue reading V$LOCK Missing Information

Restore Standby Database

Over the years I ran into all kind of weird and wonderful backup and restore scenarios. This case has challenged me for a while now and I finally had the chance to check it properly and figure out what’s going on. So here is the story. The environment This client has a few database, some … Continue reading Restore Standby Database

Did You Know #25 – Archives in FRA

One of my clients is restoring their database backup to another server for some testing. They do it periodically so we can also verify that the backup is good (which is great!). In a few cases, after the restore I saw that the restored database has an incarnation that the original database doesn’t have (and … Continue reading Did You Know #25 – Archives in FRA

SQL Monitor

I’ve mentioned the SQL Monitor report from time to time as a very useful way of reviewing execution plans – the feature is automatically enabled by parallel execution and by queries that are expected to take more than a few seconds to complete, and the inherent overheads of monitoring are less than the impact of enabling the rowsource execution statistics that allow you to use the ‘allstats’ format of dbms_xplan.display_cursor() to get detailed execution (more...)

My First Real GitHub Project

This is a short note just to say I’m happy to have my first GitHub project. A few months ago I wrote a blog post and a python script (on gist) to analyze the listener log file. A few weeks later I got a message from Adric Norris who asked me to create a proper … Continue reading My First Real GitHub Project

Screenshots and more

One of my pet peeves is a sloppy screenshot. I shudder if I’m sent a screenshot in a professional document that’s blurry, too zoomed out or stretched out of ratio. I cringe if I see a presentation with a screenshot that includes a stack of irrelevant extra info – it’s not necessary to include your Amazon shopping browser tabs or to advertise how many unread Yahoo emails you have.

As a result, I like a (more...)

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

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...)

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...)

root.sh fails with CRS-2101:The OLR was formatted using version 3

Got this while trying to install RAC on Redhat Linux 7.2. root.sh 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 root.sh.

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...)