Listener log missing when Oracle Clusterware starts listener resource script

This post could be interesting for those who are running active/passive failover clusters with Oracle Clusterware used for managing resources. In this case owner of the Clusterware installation is not the same as owner of the Oracle RDBMS installation. Among others, Clusterware is assigned for managing listener resource (start,stop,check).

Till 11g version listener logs were by default written in “$ORACLE_HOME/network/log” but with 11g ADR (Automatic Diagnostic Repository) is introduced and location for listener log is changed. From 11g default location is “$ORACLE_BASE/diag/tnslsnr/<hostname>/listener_<dbname>/trace”.

I’ve noticed that when you start listener as owner of Oracle RDBMS everything is working fine. Listener log (more...)

Workaround for ORA-00600: internal error code, arguments: [kzdlk_zt2 err]

To create database link in another schema you must execute “create database link” logged in that schema or you can use workaround noted in this post:

How to Create a Database Link in Another User’s Schema

Neil Johnson wrote nice post on that subject and I’m using his method for creating database links in another user’s schema. So check it out - link is above.

Recently I had situation when I didn’t know password of the user on the local and remote database. But I had to create database link to compile some views and procedures.

In that case my (more...)

Wrong result after using join predicate push into a view with a GROUP BY

Yesterday colleague reported that he’s receiving wrong/odd result from query so we decided to examine closely what's happening. Query had more then 150 lines with inline view, group by clause, several outer joins, function, decodes and cases included. So the first step was to exclude all the stuff that wasn’t important for clearer diagnosis.

Environment: Oracle EE on OEL 5.7

I will post shortened version of the query with fake table names and columns.

SELECT p_name,
   r.status r_status
   FROM table_1 r,
       SELECT  ri.r_id,            
       MAX ( DECODE (ri.c_name,  'SPACE', ri. (more...)

Perform Oracle Block Recovery without having valid backup

My DBA friend Dejan from Baze Podataka database related site wrote post about Oracle Bug 8943287 which reminded me how dangerous bug this is. I was completely unaware about this bug till few months ago.

For this blog post I want to write about “ORA-01578: ORACLE data block corrupted” error and recovery. It is easy to recover from such error if you have RMAN backup - you, of course, have RMAN backup? (if not, create one) Even if you don’t have RMAN backup you can perform block recovery from OS based backups (datafile copies).

But can you perform block recovery (more...)

How to move SYSTEM tablespace with minimum downtime

Two weeks ago a colleague DBA asked me what would be the best way to move SYSTEM tablespace with minimum downtime. There are several ways to perform that task and I will note what would be the best way in my opinion.

Messing with SYSTEM tablespace is always tricky so my goal would be minimum risk and minimum downtime.

My testing environment is 11gR1 database on Linux 32bit OS.

After quick search on that subject I’ve noticed that DBA’s are comfortable with moving/renaming other datafiles but SYSTEM or UNDO are making problems. Some tablespaces are essential and cannot be taken (more...)

Tuning query with database link using USE_NL hint

I’ve just realized that I’m never fully satisfied after tuning queries with database links. Yes, I manage to improve performance significantly but I’m always missing some answers.

Case explained in this blog post is not exception also.

Source DB (Oracle on Solaris 64bit)
Remote DB (Oracle on Solaris 64bit)
(Table names and values are changed )

One of the developers reported me problem with this simple query:

To get better output click "View Source".
  FROM tab1 cc, 
             tab2@db2 cfp
 WHERE cc.c_id = cfp.b_id 
 AND cc.code =  (more...)