Temporary tablespace takes ages to drop

A simple command like 'DROP TABLESPACE TEMP;' in a regular database can become excessively slow.  The reason behind this is quite simple, even if the users defaults to a different temporary tablespace there could still be sessions holding temporary segments there.  It's just a matter of finding them and killing them  to free the blocks on the temporary segments and you're good to go.

The operation of dropping the temporary tablespace looks like this on (more...)

ORA-00600 [2663], [12], ….

After not having spotted ORA-00600 for a while a new one showed up.

ORA-00600: internal error code, arguments: [2663], [12], [3665664941], [12], [3884717804], [], [], [], [], [], [], []

This happened out of the blue after a switchover back from a Datagurad environment.  Basically this is due to a bug, the patch is already included in and patch sets, but it is not enabled (more...)

ORA-00600 [kdiRelocateCommitted:DBAupd]

This error recently showed up in a Enterprise Edition RDBMS on Oracle Enterprise Linux: ORA-00600: internal error code, arguments: [kdiRelocateCommitted:DBAupd], [2126573183], [], [], [], [], [], [], [], [],
[], []
Incident details in: /u01/app/oracle/diag/rdbms/ ....._ora_5642_i10412.trc

After the trace file I was able to identify which operation produced the error and identify the specific partition that had problems.  The error was triggered during a partitioned index maintenance operation:

alter (more...)

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

Note: This particular scenario happened on an Oracle 11g Rel.2 ( RAC on OEL6 x64 with Active Dataguard.

The following error shows on a scheduled basis

Sat Jan 18 23:00:06 2014
Errors in file /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j008_6097.trc:
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073
ORA-06512: at line 1

The underlying table required as part of (more...)

The event has started

Oracle has been an industry leader for decades already, the two key factors, innovation and aquisitions have been the elements that Larry Ellison has known well how to manage. Oracle (ORCL) is a company who has the complete set of products, ranging from Hardware, Operating System, Database, Middle Tier, and (more...)

OpenSSH failed to add the hosts to the list of known hosts

Error message:  OpenSSH failed to add the hosts to the list of known hosts

Workstation: Windows 7 x64 Authenticated with domain server
OpenSSH: OpenSSH for Windows v3.8.1p1-1 x64

This issue has been going on for a while in my machine, what is boring is that every (more...)

100,000 + hits for Oracle by Madrid

Recently this professional effort known as "Oracle by Madrid" reached 100,000+ hits, I'm grateful to the Oracle community for making this possible, this blog has created a positive synergy in many ways.

Once I heard someone who said "When you teach you learn twice", and it is very true, helping others through this blog has in the long run helped me too.  This blog has been a valuable reference for me too, while "googling" for a specific issue I'm facing sometimes I find the answer in a post I made in the past that I didn't remember, so (more...)

iOS6 removes the YouTube app

I recently received a notice about the availability of the iOS6 upgrade for my iPad.  I thought it was cool to see the upgrade and install it.  After a warning related to the battery level (at least 50% or plugged in to the electric outlet) in my iPad I proceeded to download it and have it installed.

Everything was just fine, in my opinion there's not too many new functionality I could take advantage of at this time, and there were some new apps installed and yes, a very cool world clock similar to that one I used (more...)

Oracle PSU 9 Post-Install issues

Platform: Windows 2003 R2 x64
Version : Oracle PSU 9

After applying the PSU the DBMS_MEATADA didn't work.

ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

A side effect of this, when trying to execute DataPump it failed, since this requries DBMS_METADATA to extract the object metadata.

C:\> expdp aUser/aPassword schemas=SCOTT dumpfile=SCOTT logfile=SCOTT job_name=SCOTT

Export: Release - Production on Thu Sep 13 11:15:16 2012

Copyright (c) 1982, (more...)

OPatch issues

Recently I have applied the PSU5 and PSU9, in both cases a couple of issues happened when executing the OPatch utility. 

The first one, has to do with a runtime error during the execution of the OPatch utility.  Suddenly a window pops up saying it cannot find the msvcr71.dll library, this is a module containing standard C library functions such as asprintf, memcpy and cos, it is part of the Microsoft C Runtime Library.  The workaround for it was to look for it at one of the previously installed Oracle Homes and copy it (more...)

Justin Kestelyn announces new professional goals.

Justin Kestelyn, a key element for the Oracle community announced he will leave Oracle, I do personally want to publicly thank Justin for his remarkable and inspiring contribution throughout the years to the Oracle community, the Oracle Technology Network, the Oracle ACE program, the Oracle Magazine, the Oracle Open World events, among many other numerous and highly valuable technical and administrative efforts which make a huge difference.

Thank you Justin and Good Luck with your new professional goals.

ORA-16792 configuration property value is inconsistent with database setting

As per the reference this error literally means:

Cause: The values of one or more configuration properties were inconsistent with database in-memory settings or server parameter file settings. This may happen by altering initialization parameters directly instead of altering property values using Data Guard broker.

Actions: Query property the InconsistentProperties on the database or check the Data Guard broker log to find which properties are set inconsistently. Reset these properties to make them consistent with the database settings. Alternatively, enable the database or the entire configuration to allow the configuration property settings to be propagated to to the (more...)

Flash Player 11.3 kept crashing with Firefox 13.01

The browser I use the most is Firefox, a personal reason that started back at the time when the way to avoid internet worms from infecting the computer was to either endlessly keep on updating the I.E. or by using low maintenance reliable browser.  Ever since Firefox became my preferred choice.

Recently something weird happened, one of those auto updates that bring down the system and ask for restart of applications, and suddenly  my Firefox stopped displaying the Enterprise Manager performance graphs. I had to temporarily switch to Internet Explorer while I was able to find a solution (more...)

ORA-00600 [kgiinb_invalid_obj]

Another ORA-00600 error:

This error was recently reported to me,

ORA-00600: internal error code, arguments: [kgiinb_invalid_obj], [0x102BE8B38],
[0x12CF3EED8], [], [], [], [], [], [], [], [], []at
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode,
OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object
src, String procedure ....

Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
Oracle Server Enterprise Edition - Version:

After some research I found that basically this is due to a bug (7420394) which can be fixed by applying the patch for it, or upgrading to 11.2.0.

ORA-00700 [kesqsMakeSql-invstat:cpuTime]

ORA-00700: soft internal error, arguments: [kesqsMakeSql-invstat:cpuTime], [], [], [], [], [], [], [
] ... hmmm ... it looks impressive, overall considering that an ORA-00600 is not one that probably is going to make your DBA day.

Actually it is not as tragic and epic as it could seem to be at first glance, it has to do with a bug. In this particular case it showed up in an Oracle 11g Rel.1 ( version on a x64 windows platform. This error is triggered when the SYS_AUTO_SQL_TUNING_TASK runs, and it may spontaneously show up and keep on (more...)

How ORA-13236 was about to steal Christmas

Recently an error was reported to me ...

Message: ORA-00604: error occurred at recursive SQL level 1
ORA-13236: internal error in R-tree processing: [insertion at root (mdrbin_mem_ins_rt)]
ORA-13236: internal error in R-tree processing: [partition and pair bucket (mdrbin_optmz_mem_ins_node)]
ORA-13236: internal error in R-tree processing: [pair buckets (mdrbin_partition_pair_bckts)]
ORA-13236: internal error in R-tree processing: [mdrugnd - getting a node (mdrbin_pair_bckts)]
ORA-13234: failed to access R-tree-index table [MDRT Table]
ORA-29400: data cartridge error
ORA-06512: at "MDSYS.SDO_IDX", line 149
ORA-06512: at line 1

StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String (more...)

The Oracle Open World, Day 1 – Key Conference I

This year the slogan is ... "Engineered for Innovation" According to the slogan is the kind of surprise Larry Ellison will have prepared for the Oracle community, so if you have some intuition and track the recent history of Oracle you may guess what the surprise from the big boss is about.

Larry Ellison entered in his own personal style, and the first phrase that he pronounced gave the guideline to what this was about ... he said "Sometime back when we bought Sun the customers said that Oracle was already into the hardware business, but we didn't receive the memo" ... referring (more...)

The Oracle Open World, Day 0

Oracle Open World Day 0. Arrival to SFO.

Visiting the Oracle Open World in my personal experience is the opportunity to meet with peers, networking, updating knowledge, interact with people, meet the gurus, and have fun. My OOW started on Saturday, and the visit to the holy Oracle Headquarters is a must for me. It's the reminder of the long journey I've walked so far along with the company.

The temparature was nice, a typical autum californian day, beautiful, I love this weather. However, you never know, I remember some years back San Francisco had a severe cold front that (more...)

Display a formatted time difference

This is a simple query to display the difference between two dates in a formatted way.

Let's assume a given date in a default DD-MON-RR format like this:

select sysdate - to_date('08-OCT-75') from dual;

Define a Variable which will hold the number in days and fraction of days

define DateDay = 13084.4081

Then the query

TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
+ &DateDay,'YYYY MM DD HH24:MI:SS') A

and you've got your formatted output.


Moving a datafile Online

A datafile cannot be completely moved online in a user transparent way, but what it can be done is to minimize the time required to perform the operation, and avoid the traditional procedure executed when the database is mounted, which permits more availability for the non affected production tablespaces.

The operation can be performed by creating a datafile copy, either with the traditional commands or by means of recovery manager. In this case the following script was prepared to move a datafile.

run {
copy datafile ''
to '';
set newname for datafile (more...)