Oracle SQL Developer: New window, new session. Bad behavior?

Yay, it’s there. SQL Developer 4 is usable. It brings a new session for a new window (“unshared worksheet” in Oracle terms) – I was told lately SQL Developer 3 already had this. Did you know? Now we are kind of stable in 4: The 4.0.2 build 1521 (hehe) works good, so it’s time to see how “Unshared Worksheet” works here. That’s what we alwas had – one sheet, one session:

That's what we alwas had: One sheet, one session

 

Read more…



Oracle 11.2 PSU2 Grid Infrastructure stack start … failed to complete at crsconfig_lib.pm line 11814

Oh, sometimes it’s just (own or close-people’s) PEBKAC that costs you time and gray hair… Patching 11.2.0.4.x to PSU2 on Linux was such an issue.

Problem

“opatch auto” with all comfort worked well, applied the patch, but re-starting the clusterware failed with:

Starting CRS... 
Installing Trace File Analyzer 
CRS-4123: Oracle High Availability Services has been started. 
Oracle Grid Infrastructure stack start initiated but failed to complete at 
/u01/app/11.2.0/grid_2/crs/install/crsconfig_lib.pm line 11814.

What happens here? OHASD tries to start up CRSD, but it fails with an exception: permission check failed. Mh, might come from most $GRID_HOME/bin/*.bin files have no executable (“x”) flags set. WTF?
So what.. who’s the culprit? Spent a whole weekend on investigating and re-trying, just to learn that the scripts in the PSU won’t change a flag in bin directory. What do the file parameters look like in the unzipped patch directory. The same. Grml.

Wait… The PSU2 README.html says: “unzip the patch as the grid home owner”. How did the patch directory get to the server…? Oh yes, it was unzipped on the downloading machine (automatically). Woe …

Solution

  1. Download the PSU in zipped format
  2. Copy the ZIP container to the system you’d like to patch
  3. “Unzip the patch as the grid home owner”
  4. Don’t find a “failed to complete at crsconfig_lib.pm line 11814″ in your console output
  5. Have an early beer for reading correctly, and not a late one for finding your own mistakes

Be an extremely careful reader and stay safe
Martin



Oracle on Linux: How to hide your password when using a wrapper script

Sometimes, a DBA has to write an externally called wrapper script for various Oracle-supplied commands accepting password inputs. A prominent and simple example is SQL*plus (sqlplus).

The Problem

The process list shows all parameters of a command that’s currently executed.

wrapper1.sh

Accepts all connection infos on the command line:

#!/bin/bash
 cmdstring="sqlplus ${1}/${2}@${3}"
 echo "Executed command: $cmdstring"
 sleep 999

Called by:

$ ./wrapper1.sh system manager mydb
 Executed command: sqlplus system/manager@mydb

But now, the password is visible in the process list:

$ ps aux | grep wrapper1.sh | grep -v grep
 1000     20769  0.0  0.0  13808  1444 pts/1    S+   15:19   0:00 /bin/bash ./wrapper1.sh system manager mydb Read more...


Slides for COLLABORATE 2014 IOUG forum talk #C14LV

Hi folks,

here’s my slides for “YOUR machine and MY databases – a performing relationship?!” at Collaborate 2014 Las Vegas. Any feedback is greatly appreciated!

collaborate14-logo

2014_141_Klier_odp_v1

2014_141_Klier_v1_doc

Some pictures to come soon!

You may want to follow the #C14LV hashtag to see what’s going on. :)

 



SQL Server Analysis Look At’s

End of March, I had a chance to dig a bit deeper into MS SQL Server analysis. The german chapter of PASS (http://www.sqlpass.org) organized one of their “Essentials”, a condensed training day with a specific topic. This one was named “SQL Server Analysis” and was held by Andreas Wolter (MCSM) in Nuremberg. Here comes my “look-at’s” – the usual public scratchpad of what I want to review as follow-up of an event.

  • There are two SQL Server related IO benchmark tools (like ORION that I’m familiar with, by Oracle): SQLIOSIM and SQLIO.
  • Event Tracing for Windos (ETW) is supported for MSSQL since Version 2008, use Windows Performance Recorder for that.
  • Try “perfmon /report”
  • Windows Server 2008R2 and above supports “mountpoints” in addition to drive letters. Nice, finally aligning POSIX?
  • Profiler can load PERFMON data and correlate them (old style / deprecated)
  • SQL Server reports: Disk Usage Report shows, for example, resizing operations. So check your file sizing policy this way.
  • Extended Events (XEvents) take 2µs, Profiler events take 4ms (=> Profiler is factor 2000 slower; source: SQL Server & BI blog)
  • SQL Server index growth is different from Oracle: They have no 90/10 split on the growth end of the leaf list, SQL Server adds empty leaf nodes
  •  Extended Events editor to be found under “Administration” in SQL Server Managament Studio (SSMS)
  • Setting up a Performance Data Warehouse brings historical performance data. Do the following:
    1. Create a Data Collection Set
    2. Create a Performance DWH
    3. Create a data Collector
  • Look at tools:
    1. Event Notifications
    2. SQL Diag (Log/Trace collector)
    3. RML Utilities

So for sure this is not everything – the more you dig into a RDBMS, the more surprises and fields of knowledge-to-learn you will find. Let’s go ahead, next time.

Good luck
Martin Klier

Edit: Version supporting ETW



DOAG Würzburg: “Resolving child cursor issues resulting in mutex waits”

I feel honored that DOAG asked me once more for being a speaker at an event – this time it’s DOAG Regionalgruppe Würzburg. I will give my (updated) C12LV and Oracle Open World 2012 talk “Resolving child cursor issues resulting in mutex waits” there, supported by my new business Performing Databases GmbH.

My abstract:
In special situations, the Oracle Database generates too many child cursors for particular SQL-IDs. This results in high CPU load on the DB server, coming from heavy mutex access. This is visible as mutex wait events. The lecture will show how this situation arises, how the DBA can try to quick-fix it and how long-term solutions can be found. Additionally, we will have a closer look on the Oracle internal situation: Why does the DB use mutexes here, and how?

When?
Thursday (Donnerstag) 27.03.2014
5 p.m. (17 Uhr)

Where?
Würzburger Versicherungs AG
Bahnhofstraße 11
97070 Würzburg
(Map)

The presentation will be available after the talk at www.performing-databases.com and on the DOAG online-resource page.



Oracle Dataguard: ORA-00600 [krsu_upi_atc.7] – crash when primary ships the first time

After setting up a new Oracle Dataguard system (primary plus one standby DB), everything looked promising.

But after activating the log shipping from primary, and after archiving a redo log for the first time, the primary instance crashed with ORA-00600 [krsu_upi_atc.7]. Without the standby system available (DB idle or listener off), no error occurred.

******************************************************************
 LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
 ******************************************************************
 LGWR: Archival destination is a Primary RAC instance: 'ITWMT2'
 Errors in file /u01/app/oracle/diag/rdbms/itwmt/ITWMT/trace/ITWMT_lgwr_22151.trc  (incident=18089):
 ORA-00600: internal error code, arguments: [krsu_upi_atc.7], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /u01/app/oracle/diag/rdbms/itwmt/ITWMT/incident/incdir_18089/ITWMT_lgwr_22151_i18089.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Errors in file /u01/app/oracle/diag/rdbms/itwmt/ITWMT/trace/ITWMT_lgwr_22151.trc:
 ORA-00600: internal error code, arguments: [krsu_upi_atc.7], [], [], [], [], [], [], [], [], [], [], []
 LGWR (ospid: 22151): terminating the instance due to error 470
 Wed Mar 19 15:27:07 2014
 ORA-1092 : opitsk aborting process
 Wed Mar 19 15:27:07 2014
 System state dump requested by (instance=1, osid=22151 (LGWR)), summary=[abnormal instance termination].
 System State dumped to trace file /u01/app/oracle/diag/rdbms/itwmt/ITWMT/trace/ITWMT_diag_22141_20140319152707.trc
 Dumping diagnostic data in directory=[cdmp_20140319152707], requested by (instance=1, osid=22151 (LGWR)), summary=[abnormal instance termination].
 Instance terminated by LGWR, pid = 22151

Instance terminated by LGWR did not look promising. Plus no search-engine-of-choice hits, no MOS search result. But re-reading the configuration unveiled a very basic mistake: The DB_UNIQUE_NAME of the two databases (primary and standby) was the SAME – not exactly the purpose of a UNIQUE name…. Changing it on standby side, and off it went.

Let me tell you, read carefully.
Martin Klier



DOAG Database Conference Düsseldorf: “YOUR machine and MY database” was accepted

DOAG just informed me that my talk, “YOUR machine and MY database – a performing relationship!?” was accpeted for DOAG Database Conference Düsseldorf. Last year, the first Database Conference at Düsseldorf was a great success, I enjoyed it very much. I’m glad to be part of the speaker’s corps this year.

Hope to see you there!

June 3rd, 2014
Van der Valk Airport Hotel
Düsseldorf

Event: http://www.doag.org/de/events/konferenzen/doag-2014-datenbank.html

 

 

2014-DB-Banner-600x160



Klug GmbH integrierte Systeme wins Oracle Excellence Award Germany 2013 category “ISV”

Klug wins Oracle Excellence Award

I’m proud to announce that my employer, Klug GmbH integrierte Systeme, Teunz (Germany), won the Oracle Excellence Award Germany 2013 in the category of Independent Software Vendors (ISVs).

Read more…



Oracle Clusterware root.sh issue: USM driver install actions failed (oracleoks.ko)

As I already said in my last post about “Can’t install ohasd service“, setting up Oracle Clusterware 11.2.0.4 on SuSE Linux Enterprise Server (SLES) SP2 should work flawlessly, but sometimes it does not. :) This time, it was about the USM drivers.

USM driver install actions failed
/u01/app/grid/11.2.0/perl/bin/perl -I/u01/app/grid/11.2.0/perl/lib 
-I/u01/app/grid/11.2.0/crs/install
/u01/app/grid/11.2.0/crs/install/rootcrs.pl execution failed

USM drivers are components (Kernel object files, extension .ko) enabling ACFS – I don’t use it on this system, but root.sh (in fact, rootcrs.pl) needs a decent directory structure related to the Linux Kernel version: Again, the log file “$GRID_HOME/cfgtoollogs/crsconfig/rootcrs_<hostname>.log” was my friend: It unveiled, that the problem was somewhat related to loading oracleoks.ko. And this file is in directory “$GRID_HOME/install/usm/Novell/SLES11/x86_64/<your-kernel-version>/default/bin”. Trouble is, that good old SLES 11 SP2 has a Kernel that was not foressen by the Oracle folks implementing this piece of software.

Read more…