Thursday, August 11, 2016

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 Enterprise Manager:

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
order by tablespace;

Sessions still reported using the target temporary tablespace have to be killed.

After it is unlocked the process runs immediately.

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 by default, it is activated after setting a parameter in the spfile (pfile) parameter file.

alter system set "_ktb_debug_flags"=8 ;


Tuesday, December 09, 2014

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 index INDEX_OWNER.INDEX_NAME modify partition PART_NAME shrink space
ERROR at line 1:
ORA-00600: internal error code, arguments: [kdiRelocateCommitted:DBAupd], [2063617647], [], [], [], [], [], [], [], [], [], []

The error is not documented, and so far Oracle Support doesn't have information about this error

The workaround was to rebuild the index.  After rebuilding it the same operation produced no error.

Wednesday, January 22, 2014

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 one of the schedule maintenance tasks has invalid values.
The related table is DBSNMP.BSLN_BASELINES. 

After taking a look at the job log we see the failed execution attempts:

SQL> select log_date,status from dba_scheduler_job_run_details 
     where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                              STATUS
------------------------------------- ------------------------------
11-JAN-14 PM -06:00   FAILED
04-JAN-14 PM -06:00   FAILED
18-JAN-14 PM -06:00   FAILED
28-DEC-13 PM -06:00   FAILED
22-JAN-14 PM -06:00   FAILED

5 rows selected.

FIX Procedure:
Look for the values on the table, detect the invalid values and remove them.


      DBID INSTANCE_NAME    BASELINE_ID BSLN_GUID                        TI A STATUS           LAST_COMPUT
---------- ---------------- ----------- -------------------------------- -- - ---------------- -----------
3038864366 orcl                       0 0A03A0424F06F7E6B3841C6CC0999F7C NW N ACTIVE           131027:0000
3038864366 ORCL2                      0 A396EEB2AB3A39AF477DC4A1AEE70CC9 ND Y ACTIVE           131228:2300
3038864366 ORCL1                      0 176C36D62D62855075AD0BBD90B2DA30 ND Y ACTIVE           131228:2300

You must know exactly which row you're deleting, in my very particular case I know which rows do not make sense since this database was recently involved in a fail over scenario and the former name is still being held in the table.

The corrupt row is removed with a simple DELETE command:


1 row deleted.

SQL> commit;

Commit complete.

Manually re-execute the job and check the execution log, it must show the job executed successfully.  It takes a couple of minutes after execution to show the results in the log table.

SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);
PL/SQL procedure successfully completed.

The issue was fixed, validate it by querying he DBA_SCHEDULER_JOB_RUN_DETAILS view.

SQL> select log_date,status from dba_scheduler_job_run_details 
     where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                              STATUS
------------------------------------- ------------------------------
11-JAN-14 PM -06:00   FAILED
04-JAN-14 PM -06:00   FAILED
18-JAN-14 PM -06:00   FAILED
28-DEC-13 PM -06:00   FAILED
22-JAN-14 PM -06:00   FAILED
22-JAN-14 PM -06:00   SUCCEEDED

Sunday, September 22, 2013

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 Applications.  The product list every year grows and keeps on growing to the point that it's becoming difficult to even number them.  
The database features every new release are increased, over 100 new fetures can be listed every new major release.  
The Oracle Open World is the main Oracle event that shows the trends, new products, where partners, customers and peers converge to exchange knowledge, show new products, and where Larry Ellison unveils new surprises.
The name of the event is where you can get an idea about what will be the main attraction of the Oracle Open World.

This #OOW13 is about "Hardware and Software Engineered to Work Together".  The trend since Oracle aquired Sun a few years ago has been to make the hardware work with the software, a home made whole system under a single name.  Nobody in the industry has the same capability to offer the complete set at the high end level under a real single brand, but Oracle.

Tuesday, September 17, 2013

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 time I try to log on to any of my Linux servers the same message appears, asking for confirmation and then showing the error, which prevents me from performing batch tasks and scripts from my workstation against the Linux servers. 

The way to solve this basically has to do with an environment variable that has to be declared, OpenSSH doesn't know the value of $HOME, which is equivalent to the %USERPROFILE% windows variable. So it has to be declred.

Set a Windows User environment variable named HOME that points to the %USERPROFILE%:
  1. Open the Control Panel > System and Security > System
  2. Click on the Advanced System Settings
  3. Click the Environment Variables
  4. In the User variables box click on the "New" Button
  5. Declare a variable named HOME and set its value to %USERPROFILE%
  6. Accept and click OK
Open a new DOS windows and retry, this time instead of the error you should see a warning letting you know the host has just been added:

C:\Users\HMadrid\.ssh>ssh oracle@myHost

The authenticity of host 'myHost (192.168.XX.XX)' can't be established.
RSA key fingerprint is xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'myHost,192.168.xx.xx' (RSA) to the list of known hosts.
oracle@MyHost's password:
Last login: Mon Sep 16 17:33:58 2013 from 192.168.xx.xx
[oracle@stwrac1 ~]$ exit

Connection to myHost closed.

Thursday, October 04, 2012

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 I am not doomed to repeat my past.

Another saying I remember, "You receive what you give", very true, by giving away knowledge somehow life has rewarded me without looking for it, as if life would have kept some sort of invisible balance sheet where it records everything, and it pays back when the time is right.

I am grateful and happy this effort has had a positive echo with the Oracle Community and has been a reference to help others around the globe.

A Big Thank You All !!!

Thursday, September 27, 2012

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 to have in my iPod.  And the maps application, an absolute divorce decree for Apple, they have their own.   The addition of the location services (optional), which sounds to me more like an Android like behavior.  Is Apple secretly overtly trying to emulate the Google's success formula?

What it came to my attention was that the YouTube application had disappeared.  I looked for it all around, and it was gone.  I thought I may have accidentally removed it.  I tried to look for it at the apple store, without any luck and finding more than 200 hits related with youTube like applications. And ... after "googling" it for a while I found that this is "normal".  The YouTube application was removed from it because Apple is planning to design one of their own that will replace the traditional YouTube app.   May be Apple didn't have his own iYouTube ready on time with the release of iOS6, and this could have made the users more anxious than not being able to play their cool playlists.

I've tracked the Apple share in the stock market (NASDAQ:AAPL) for a while, extrapolating the behavior of it correlated to the release of the iPhone5 and the future marketing strategies of Apple, and this made me remember the story of the Tulips in Holland ( ) where there was a huge euphoria about the Viceroy Tulip bulbs to the point that a trader could make a profit of an equivalent of $60,000 USD today a month, and the price of a Tulip could be worth the price of a house.  And when this temporary madness was replaced by the reasoning the bubble was bursted and the price of the Tulips felt all the way down creating a big depression in the Dutch economy.

After Apple released the iPhone5 its share reached the all time top value of $700.00 USD, as soon as it was announced that even though the sales of it were good but not as good as expected the price retreated to $665.00 per share.  If Apple cannot be innovative and creative once more, people may see the Apple bubble burst.  So what's the next move? why is Apple divorcing from specific apps? Is their marketing strategy focused towards emulating the discrete giant steps of Google? Is the Steve Job's iCar something that is going to surprise the market once more? ( ), is the new iYouTube app going to be available to watch movies in the built in retina touchscreen in the car? will the aux audio device allow compatibility with pre-iWhatever devices? or you'll have to buy an adapter for it too?

In my personal opinion, Apple has reached a historic top level where it has to reinvent itself once more and after the iPhone5 the next step cannot be an iPhone6, 7 ... 20, cause otherwise the market will be saturated.  So to prevent the investors from looking elsewhere they'll have to open new markets.  May be it's about time to change the way we watch T.V.  In the mean time as long as the common saying reads "I'll buy it if it is shiny and made by Apple" keeps on being a valid success formula, Apple will keep on being a growing giant, no matter if they "forgot" to warn about the YouTube omission.

Thursday, September 13, 2012

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, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

The reason is because after applying the PSU according to the MOS note: "Data Pump Export Started Failing After Applying CPU Patch [ID 453796.1]" some tables required by Data Pump are not loaded and the XML Style sheets for DBMS_METADATA are not loaded either.

The dictionary tables required by Data Pump are not loaded. 
If you execute this query:

SQL> select count(*) from metanametrans$;


and if there are no rows returned then you'll have to proceed as stated in the MOS note.

The procedure to fix this issue is listed in that MOS Note, so since you found this error after applying a PSU or a CPU, then it means you have access to My Oracle Support, just look for the previously referred MOS Note and you'll see what's the procedure to fix this issue.

Once the procedure has been applied, the above query returns the following result:

SQL> -- Oracle 11g Rel.2 ( PSU9)
SQL> select count(*) from metanametrans$;


And both, the DBMS_METADATA and Data Pump work like a charm.

Wednesday, September 12, 2012

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 to the C:\Windows directory.  

 The second issue has to do with a file, srvm.jar, which after the OPatch evaluation, it says this file is in use.  It looks like the version of the OPatch utility cannot identify it is the same utility who is using the file.  This is a known issue and is documented in the M.O.S. Note "OPATCH LOCKS SRVM.JAR WHEN APPLYING ANY BUNDLE PATCH ON WINDOWS X64 [ID 1446273.1]"  This will be fixed in future OPatch versions, and the workaround is to look for a jar file, %ORACLE_HOME%\OPatch\jlib\oracle.opatch.classpath.jar, rename it and retry.  Once the OPatch utility is done rename back the file to its original name.

You can use the Microsoft utility Process Explorer to see who is using these files. Just make sure that you exit the utility before relaunching OPatch, otherwise it will misinterpret the locks of the Process Explorer on several oracle related dll's and the list of Oracle dynamic linked libraries in use will be longer.