Coskan’s Approach to Oracle

November 30, 2009

Do you check your script library?

Filed under: Diary, Security — coskan @ 5:52 pm

I’m wondering how do you secure your own script library ?

If you love to do things by calling scripts  isn’t there a big one way trust for security of your own machine ?

Have you ever think of  your own machine hacked by a  hacker who knows how to be invisible and how to write dangerous  sqls as well ?

what happens if your basic v$session query scripts  suddenly tries to drop, deletes or updates something ?

I start to double check every script I run but still worried and want to hear about your solutions ?

November 26, 2009

ORA-02291: integrity constraint (RMAN.RLH_F1) violated

Filed under: Backup/Recovery, Diary — coskan @ 4:38 pm

On one of our boxes, we start to get the error stack below when we want to do a crosscheck with catalog database in use.


RMAN>crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=357 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of crosscheck command on ORA_DISK_1 channel at 11/26/2009 15:08:05
ORA-19633: control file record 8403 is out of sync with recovery catalog

RMAN>resync catalog; 

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found

I unregistered the DB and register it but after it tries to resync the catalog during registration, error came up like below .

RMAN>unregister database;

database unregistered from the recovery catalog

RMAN>register database
database registered in recovery catalog
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
ORA-02291: integrity constraint (RMAN.RLH_F1) violated - parent key not found


I could not find a metalink note, but a working solution from

Basically, you need to run unregister it by running undocumented internal (Update: I did not check metalink it is documented in 1058332.6)!!!!! DBMS_RCVCAT.UNREGISTERDATABASE procedure on RMAN catalog DB. (ask Oracle support before you start doing something)



3   command, resetlogs_time
4  from rc_database
5  where name = '&db_name';
Enter value for db_name: XXXXXX

COMMAND                                                      RESETLOGS_TIME
------------------------------------------------------------ -----------------
EXEC DBMS_RCVCAT.UNREGISTERDATABASE(5626305,201074392);      20071130 11:57:12



Run the command if the resetlogs time is right and it will unregister your database from catalog.

Re-register your DB ( RMAN>register database) , if you are lucky as me it will register and sync the catalog without a problem.

November 23, 2009

Reminder for Oracle Performance Firefighting by Craig Shallahamer

Filed under: Basics — coskan @ 2:07 am

Just a reminder for the ones who are waiting for PDF version of Oracle Performance Firefighting by Craig Shallahamer, it  is now available here so you can avoid shipping costs. Click here for my review of the book if you haven’t read before.

November 22, 2009

Blogroll Report 13/11/2009-20/11/2009

Filed under: Blogroll Report — coskan @ 9:41 pm

<—- Blogroll Report 06/11/2009 – 13/11/2009

Here is your long reading list for starting a good week

1-How does compress parameter work with traditional export / import?

2-Why Isn’t Oracle Allocating More Parallel Slaves?
Kellyn Pedersen-Why Isn’t Oracle Allocating More Parallel Slaves?

3-How to set up external authentication via Radius on Active Directory?
Ronny Egner-Authentificate Oracle user passwords against Active Directory using Radius


5-Bug on Running Parallel Query with Order by on already sorted data causes problem?
Christo Kutrovsky-Oracle Parallel Query Sorting and Index Creation Performance Problems

6-How to set up OS Authentication ?
James Koopman-Securing Client Connections: OS Authentication

7-How to hammer your CPU ?
John Hallas-Maxing out CPUs – script

8-Chance of having index with blevel>=4
Martin Widlake-Depth of Indexes on VLDBs

9-How to disable unnecessary hints and keep the necessary ones only after upgrade?
Optimizer Team- What should I do with old hints in my workload?

10-How to convert controlfiles to be oracle managed files?
Neil Johnson-Converting Control Files to be Oracle Managed Files

11-How to add cached memory to free memory on linux ?
Kevin Closson-Linux Free Memory: Is It Free Or Reclaimable? Yes. When I Want Free Memory, I Want Free Memory!

12-How to overcome package owners defaults temp tablespace problem (authid current_user)?
Kellyn Pedersen-Utilizing Separate Temp Tablespace

13-”Explain plan for” reports wrong plan because of implicit conversion of bind variables
Tanel Poder-Explain Plan For command may show you the wrong execution plan – Part 1

14-How to change index hints with new index hint format automatically in sql profiles?
Kerry Osborne-Fixing Bad Index Hints in SQL Profiles (automatically)

15-How to promote partition pruning with applying predicates into using clause of merge statement
David Aldrid-Applying Predicates and Partition Pruning to MERGE Target Tables

16-If a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram.
Gary Myers-The amazing disappearing row trick

17-How to find reasons excessive LIO?
Tanel Poder-Finding the reasons for excessive logical IOs

18-How to configure direct NFS on Solaris
Glenn Fawcett-Direct NFS access to Sun Storage 7410 with Oracle 11g and Solaris… configuration and verifcation

19-Oracle and NFS compilation of Kevin Closson
Kevin Closson-Words About Oracle Direct NFS On Sun Storage 7410 And Chip Multithreading Technology (CMT) For Oracle Database 11g Release 2.

20-How to get report of PL/SQL codes using buggy exception
Timo Raitalaakso-PLSQL Warnings

21-How does changing password profile works when the password expire period is already activate?
Robert Geiger-Analysis of Oracle password expiry

22-How to configure FSFO(Fast Start Failover) for 11G Data Guard with Dataguard Broker?
Jim Czuprynski-Fast-Start Failover in Oracle 11g Data Guard

23-How to use transportable tablespaces for moving tablespace from 9i to 11G?
Luis Moreno Campos-How to transport a tablespace from 9i to 11g

24-Orion tool only works when full path is given-Bug on 11GR2
Kevin Closson-Oracle Database 11g Release 2 Includes The Orion I/O Test Tool, But You Better Get That Full Path Name Right.

25-Alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring
Lucas Jellama-Alternative for CONNECT_BY_ISLEAF function for Recursive Subquery Factoring

26-Action to take without applying CPU for vulnerable packages
Paul M Wright-DAMS for Post and PRE-CPU Change Management

27-Why revoking Public execute from a package is not enough for vulnarable packages?
Pete Finnegan-Revoking PUBLIC Execute on SYS.DMP_SYS

28-Online free MSSQL 2008 training for Oracle DBA’s
MSDN-SQL Server 2008 for Oracle DBA

Blogroll Report 20/11/2009 – 27/11/2009 –>

Crash recovery cannot be done automatically – [kcratr1_lastbwr]

Filed under: Bugs — coskan @ 2:30 pm

I hit the error below during startup of my RAC installation running on Solaris X86-64 on VMWare

ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],[], [], []

Basically none of the instances start-up with srvctl command and log this error to alert log.

Tried to run manual startup of instances but no chance same error.

Quick search on MOS (yes it was working :) ) take me to the note 393984.1 (no bug number)

It says this is a bug on Enterprise Edition – Version: to

Solution is manual recovery.

SQL> startup;
        ORACLE instance started.

Total System Global Area  369098752 bytes
Fixed Size                  2020864 bytes
Variable Size             138414592 bytes
Database Buffers          226492416 bytes
Redo Buffers                2170880 bytes
Database mounted.
ORA-00600: internal error code, arguments: [kcratr1_lastbwr], [], [], [], [],
[], [], []

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

November 17, 2009

Blogroll Report 06/11/2009-13/11/2009

Filed under: Blogroll Report — coskan @ 12:53 am

<—- Blogroll Report 30/10/2009 – 06/11/2009

1-How to upgrade to 11GR2?

Mike Dietrich-New version of “Upgrade to 11g – The whole Story” slides available

2-How to diagnose row cache lock?

Surachart Opun-Investigate row cache lock

3-Cases where Oracle Wait Interface is Useless?

James Morle-The Oracle Wait Interface Is Useless (sometimes) – Part One: The Problem Definition

4-How to do logging in PL/SQL ?

Dion Cho-Logging in PLSQL

5-Index freelist problem during mass delete insert

Jonathan Lewis-Index Freelists

6-How to change password of database link in 11GR2?- New manageability feature of 11GR2

Ittichai Chammavanijakul-11gR2 New Feature – Alter Database Link to Change Password

7-How querying GV$ dynamic views works?

Jacco H. Landlust-px-slaves and gv$session

8-How to troubleshoot network connection problems?

HP NZ Database Services-Troubleshooting network connection

9-How to automate sql_advisor tasks from ADDM reports in 10G?

John Hallas-Automatically running sql_advisor tasks from ADDM reports

10-Why performance gain of rebuilding index is minimum?

Richard Foote-An Index Only Performs How Much Work ???

11-How to use DBMS_METADATA to generate user creation scripts?

Alex Nuijten-Create Users with DBMS_METADATA

12-Possible reasons of slowness with same execution plan (check the comments)

Jonathan Lewis-No change

13-How to detect and empty CLOB column?

Alex Nuijten-An Empty Clob is not NULL, it’s NOT NULL

14-Solution to ORA-04030: with SGA multiple of 4GB and having high CPU count

John Hallas-Problems with SGA being a multiple of 4Gb (and high cpu count)

15-Index BLEVEL and CBO cost relationship

Martin Widlake-BLEVEL and Height of Indexes

16-Detecting real-time archive lag and running statspack with 11GR2 Active Dataguard

Robert Geiger-MAA for Active Dataguard

17-How to get dataguard related metrics from OEM?

Robert Geiger-Select metrics from the OEM repository

18-How does AUTO_SAMLE_SIZE and SIZE_AUTO differs from each other for DBMS_STATS?

Hemant K Chitale-Sample Sizes : Table level and Column level

19-How to use rsyslog to log syslog and database audit message to an Oracle database?

Ronny Egner-Logging syslog and database audit messages to an oracle database with rsyslog

Blogroll Report 13/11/2009 – 20/11/2009–>

November 10, 2009

Blogroll Report 30/10/2009-06/11/2009

Filed under: Blogroll Report — coskan @ 6:14 pm

<—- Blogroll Report 23/10/2009 – 30/10/2009

This one is written at Lisbon, after the most amazing concert I have ever seen at this very nice city (If you love Rammstein or you love fire shows, don’t miss their concert, I will definitely be at Wembley )

I am trying to write it on an Apple machine with Portuguese keyboard layout and it is a bit torture for a regular UK layout windows keyboard user.  I hope I did not make a mistake during html editing.

1-Different approach to BCHR

Martin Widlake-Buffer Cache Hit Ratio my guilty Confession

2-Become user privilege in Oracle ?

Paul M Wright-Oracle Identity Integrity

3-Automatic adjustment of CLUSTER_DATABASE_INSTANCES parameter after CLUSTER_DATABASE=FALSE


4-Unnecessary primary bounce step while Increasing the Dataguard Protection Level

Jason Arneil-Increasing the Dataguard Protection Level

5-How to use ACFSUTIL for Snapshots?

Ronny Egner-ACFS Snapshots

6-OCR Mirror Failover bug on NFS and OS Bonding bug with RACVIP

Freek D’Hooge-Two Oracle RAC bugs on the wall, two Oracle bugs. Take one down

7-How to choose passwords ?

Pete Finnigan-One more point on Oracle password crackers

8-How to configure OEM Blackouts ?

Dave Best-Setting EM Blackouts from the GUI and Command Line

9-Changes on Materialized View Logs in 11GR2 (timestamp-based and new commit SCN-based)

Alberto Dell’era-11gR2: materialized view logs changes

10-Using 11gR2 LISTTAG function for Tom Kytes Unindexed Foreign Keys script

Timo Raitalaakso-unindex 11.2

11-ORA-00942 with mixed case table names?

Anand-Table name silly but interesting

12-How to troubleshoot high redo generation growth?

Chen Shapira-The Senile DBA Guide To Troubleshooting Sudden Growth In Redo Generation

13-When foreign key indexes are not necessary?

Jonathan Lewis-Foreign Keys 2

14-Easy to read CRS_STAT output

Orhan Biyiklioglu-crs_stat for mere mortals

15-How to detect Migrated Chained Rows using v$sesstat and how to estimate the impact of them?

Tanel Poder-Detect chained and migrated rows in Oracle Part 1

16-How to choose right filesystem for Oracle on linux?

Ronny Egner-Which file system to choose for running Oracle on Linux

17-Cases where partitioning causes performance problems

Martin Widlake-Partitions are Still Not for Performance – Sometimes

18-How to use RDA Health Check utility for prerequisite check for installations?

Yossi Nixon-RDA – Health Check (HCVE)

19-How to use Open LDAP to store DB connection strings?

Ronny Egner-Storing Oracle database connection strings in OpenLDAP instead of OID

20-Revisited version of Upgrade to 11GWholo story to cover 11GR2

Mike Dietrich-New version of “Upgrade to 11g – The whole Story” slides available

21- stop system command on Redhat crashes DB

Frits Hoogland-Do you crash your database on machine shutdown?

22-How to solve manual kernel header update problem on OEL 5 version 4 update?

Vitaliy Mogilevskiy-OEL 5.4 Unresolvable chain of dependencies on kernel-headers

23-How to get client IP from AUD$?

Anand-IP in aud$ table

24-How to use EXP/IMP commands in Datapump with legacy mode?

Oracle Contractors-DataPump Legacy Mode in 11g Release 2

25-How to recover from lost online redo logs with dataguard?

Mahias Zarick-Recover from Lost Online Redo Logs with Data Guard

26-Factors affecting LIO

Harald van Breederode-Explaining the number of Consistent Gets

27-Index space allocation anomalies

Jonathan Lewis-Did you know?

28-FAQ about Flashback Database

André Araujo-Questions you always wanted to ask about Flashback Database…


Blogroll Report 06/11/2009 – 07/11/2009–>

November 3, 2009

Oracle 10g/11g Data and Database Management Utilities by Hector R Madrid

Filed under: Book Reviews — coskan @ 4:29 pm

Two months ago I got mail from PACKT Publishing to review Oracle 10g/11g Data and Database Management Utilities by Hector R Madrid under their free blogger review program.  Initially, I promised to finish it by beginning of October, but due to personal reasons, I can only finish it by today.  Till I do my review,  Hemant K Chitale and Anantha Narayan from  Oracle blogosphere  already mentioned about the book in their blogs so you can go and check their opinions as well (able to find different opinions, before buying something is definitely a good option)

After marketing and excuse section, now it is time to review the book,

This book promises us to learn (taken from the back cover);

  • to optimize time-consuming tasks efficiently by using Oracle DB utilities.
  • perform data loads on the fly and replace the functionality of the old exp/imp utilities using data pump or SQL*Loader
  • boost defences with Oracle Wallet
  • Improve Performance of  RMAN backups
  • Perform  more than just ETL process by taking advantage of the External Tables feature

Can it keep it’s promise ? I think  Hector did very well about keeping his promise.

I liked the way he wrote the book, it is again very well organized with very simple easy to understand language. (If you have read my old reviews probably you already know that, I like the books I can finish, so in my opinion a good book must be a page turner as well as being technically satisfactory)

Who should have this book ?

There is already a who is this book written for on the back cover but  I have to add some words on top of it.

If you don’t like to read Oracle Official Docs very much, if you can easily get bored or lost during your official docs journey and you want to have a reference on your desk about available Oracle Utilities, this book is definitely written for you.

If you are DBA at the same place more than 5 years (where things are stable enough) and started this job with version 7 or 8  and due to the nature of your company or yourself you don’t have to learn new things that much and can still pay your mortgage  this book is a MUST  for you.   At least you can ease your life, save gazillions of  time and look wiser to the new junior  DBA when you can able to compare exp with data pump because if you can’t do it they wont trust your experience at least I don’t. Market is very tight and this book gives you enough knowledge to cope with market conditions.

If you are a junior DBA who doesn’t know where to start Oracle Utilities, I strongly recommend  this book  for your personal development.

Do I happy to have the book ? Sure I do. I did not learn new things that much but it worth reading for just couple of tips and tricks and learning some of the topics I have no experience on.

Now,  is the time to go over the summary of topics. (What Hector did was he tried to cover every topic with average 30 pages and enough screenshots and code samples this make book easy to read and avoid boredom of long topics. )

1-Data Pump: This topic is well covered. There is  enough information for started. I wish Hector covered the compression and encryption options of 11G, with examples.  I liked tuning Data Pump performance section where he covers factors other than parallelism which affects datapump performance.

2-SQL*Loader: I have to confess that SQL*Loader is something very old in Oracle but I am kind a  new for it because I never needed to use it at job so my knowledge was depending on  simple tests. It was very nice to learn what it is capable of with loads of examples.

3-External Tables: I love and used external tables very much for mostly data and this section brought new areas of usage to my knowledge like reading listener and alert log via external tables.

4- Recovery Manager Advanced Techniques: I think this topic is named wrongly because when you see advanced you expect more but I did not get that much. It basically covers what RMAN can do instead of your old manual backup techniques. If you already an RMAN user  and don’t know the new feature called faster backups through intra-file parallel backup restore operations in 11G  it will be a good learning for you.

5- Recovery Manager Restore Recovery Techniques: This is the second part of  RMAN managed backups which is RMAN managed restore recovery. Nothing  new for me and I have to say TSPTR which is the most important feature is missed.

6- Session Management: Overall session monitoring  for wait and lock analysis is covered with addition of resource manager. Old school boys will definitely find something new in this chapter.

7- Oracle Scheduler: This chapter can convince you to use this tool more. Job Chains  should be covered with a little detail I think.

8- Oracle Wallet Manager: This chapter was new to me and probably new to most of you. I can say that is a good introduction to Oracle Wallet Manager.

9- Security Management: Again good introduction for encryption options available in Oracle. It is mostly practical usage of previous chapter.

10-Database Configuration Assistant: I use DBCA very much and this chapter was a bit fast reading but it is a must for guys who are not aware of what this tool can do other than creating a DB.

11- Oracle Universal Installer: Basically, Hector tried to mention that this tool is not just doing next next next. Cloning Oracle home using OUI was a new learning for me.

12- Enterprise Manager Configuration Assistant: There are nice tips and tricks about emca troubleshooting.  You will like this topic if you have problems with this tool and don’t know where to look.

13-Opatch: This chapter is very well detailed and it adds Enterprise Manager usage for opatch utility.  A must read for the ones who use opatch when applying patch.

Biggest problem of this book is lack of references. I really don’t like something without references. The author cannot know them by himself,  he should have learned them from somewhere and it would be very nice if he shared them with us too so the book can point the users to the right directions to finalize their journey about utility they look for.

Well that’s about it.  I want to thank PACKT Publishing for free review option and Hector for this nice work.

If I can find time to read, Next Book review probably will be Practical Oracle 8i :Building Efficient Databases , which I finally got my copy of it and willing to read it asap. Luckily Christmas coming and it will be quite at work :)

Blogroll Report 23/10/2009-30/10/2009

Filed under: Blogroll Report — coskan @ 12:48 pm

<—- Blogroll Report 16/10/2009 – 23/10/2009

1-Solution to ASMLIB creadisk problem with EMC Power Path

Orhan Biyiklioglu-ASMLIB createdisk problem on emcpower devices — solved

2-How to create a database link in another users schema ?

Marko Sutic-Create a Database Link in another user’s schema

3-How DST works for current running sessions?

Freek D’Hooge-Wintertime (again)

4-How join key effects CBO for multi-column joins ?

Randolf Geist-Multi-column joins

5-More on How Intra-Block Row chaining effects with tables with more than 255 columns?

Hemant K Chitale-Some MORE Testing on Intra-Block Row Chaining

6-pros/cons of linux distrubutions for Oracle from DBA perspective

Ronny Egners-Oracle on linux – yes of course – but what linux?

7-What happens when you drop table during select operation?

Uwe Hesse-Dropping a table during SELECT

8-How to tune kernel settings for Linux-x86_64 Error: 28: No space left on device?

Surachart Opun-Shared Memory Tuning: startup database – ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device

9-How to move datafiles in ASM with ASMCMD CP command?


10-Tom Kyte’s Favourites from 11GR2 New Features

Tom Kyte-Looking at the New Edition

11-How partitioning can cause bad performance on wrong partition implementations?

Martin Widlake-Partitions are Not for Performance

12-New semantic hints on 11GR2 ?

Rob Van Wijk-Three new “hints”

13-How to apply PSU for ?

John Hallas-PSU for

14-How to find waiters with oradebug in 11GR2 ?

Miladin Modrakovic-Find waiter with Oradebug 11gR2

15-How to install TOra on Ubuntu 9.10 ?

Brad Hudson-Installing TOra with Oracle Support on Ubuntu 9.10 (Karmic Koala)

16-Reasons to move 11GR2 ?

Kerry Osborne-11g Release 2 Technology Day – Dallas Cowboy Stadium

17-Netbackup 6.5.4 does not work with 11GR2

Jason Arneil-Netbackup 6.5.4 and Oracle 11gR2

18-Ways to copy schema stats from one schema to another schema?

Ronny Egner-How to copy schema stats from one schema to another schema

19-All about checkpoints

Harald Van Breedore-Checkpoint presentation presented at the RAC SIG

20-How to recover from crashed ASM disk in normal/high redundancy ?

Ronny Egner-ASM resilvering – or – how to recover your crashed cluster – Test no 5

21-Logging “log file parallel write” relationship and nologging with dataguard

Jonathan Lewis-logging


Blogroll Report 30/10/2009 – 06/11/2009–>

The Silver is the New Black Theme Blog at


Get every new post delivered to your Inbox.

Join 193 other followers