Here’s a quick quiz.

According to the Oracle 12.1 Database SQL Tuning Guide the first stage of parsing a statement is the Syntax Check, which is followed by the Semantic Check, followed by the Shared Pool Check. So where you do think the statement text will be while the Syntax Check is going on ?




How can a single piece of SQL text – checked very carefully – end up with multiple SQL_IDs ? There are probably quite a lot of people who know the answer to this question but won’t think of it until they’re reminded and, thanks to a question that came up on the forum formerly known as OTN a couple of days ago, I was reminded about it recently and rediscovered an article I had drafted (more...)

Diagnosing Listener Issues in RAC

I’ve written a few “diagnostics” posts in the past (you can see them all under the “Troubleshooting” category). People really like these type of posts, and I got many comments about them in the past (good ones…), so here is another case that I had not too long ago. I’d like to thank Kadhir Velavan, … Continue reading Diagnosing Listener Issues in RAC

Rebuilding Indexes

One of the special events that can make it necessary to rebuild an index is the case of the “massive DML”, typically a bulk delete that purges old data from a table. You may even find cases where it’s a good idea to mark a couple of your indexes as unusable before doing a massive delete and then rebuild them after the delete.

Despite the fact that a massive delete is an obvious special (more...)

Fast Now, Fast Later

The following is the text of an article I published in the UKOUG magazine several years ago (2010), but I came across it recently while writing up some notes for a presentation and thought it would be worth repeating here.

Fast Now, Fast Later

The title of this piece came from a presentation by Cary Millsap and captures an important point about trouble-shooting as a very memorable aphorism. Your solution to a problem may look (more...)

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

How to speed up slow unicode migration of a table with xmltype columns

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

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

Hit by the ‘Tech Support’ Scammers

I got a call earlier today from the Tech Support Scammers. You’ve probably heard of this horribly unethical practice already, but the premise is that they cold-call seemingly randomly and try to convince you that there is a problem with your PC/router, and then attempt to get you to allow them remote access to your PC to ‘fix it’. Some then claim problems are due to expired warranties on the computer and demand payment, others (more...)

Slow full table scan due to row chaining

Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.

This was the statement:

select count(1)
from CLIENT k
where k.expires is null;

I've used fake names for table name and columns.
Database version:

Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)

Extended Stack Profiling – Ideas, Tools and Comments

Topic: This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.

Understanding the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent, which code paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)

Analyze database activity using v$log_history

The v$log_history view contains important information on how application’s users use the database , this view can help you define periods with the most activity in the database. v$log_history queries You can adapt the query to your needs, you just have to change the way you format the date to be able to drilldown to the … Continue reading Analyze database activity using v$log_history

The post Analyze database activity using v$log_history appeared first on Oracle (more...)

ORA-19599 block corruption when filesystemio_options=SETALL on ext4 file system using Linux

Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.

This was virtual machine running oldish Oracle database.

I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)

PeopleTools 8.54 Feature: Application Engine Trace File Enhancements

In this blog, we have been reviewing the new features of PeopleTools 8.54. Today is the turn of Application Engine, particularly on its troubleshooting. This release of PeopleTools include several enhancements on Application Engine tracing, which are outlined below:

  • The .AET trace file can now include the PeopleCode trace. This removes the need of checking the .AET file for the the non-PeopleCode steps and the .TRC file for the PeopleCode steps. Surely, .TRC files (more...)

Being Right and Proving It

One of the great things about working in IT is that you can often win an argument simply by being right. Not because of who you are or because you are more eloquent than others, but because the facts support your position. Almost every IT person I have ever met respects facts.

In order to win arguments this way, you of course need some facts to work with. And that’s where too many people fail. (more...)