OPatch 11.2.0.3.0 issues

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

msvcr71.dll
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.  

%ORACLE_HOME%\oui\jlib\srvm.jar
 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 11.2.0.3.0 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 11.2.0.3.0 LOCKS SRVM.JAR WHEN APPLYING ANY 11.2.0.3 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.

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 initialization parameters.

When the dataguard broker was queried about the status, it showed the following results:

DGMGRL> show configuration verbose

Configuration - OMCDG1120

  Protection Mode: MaxPerformance
  Databases:
    OMCPRO - Primary database
    OMCADG - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
WARNING


The parameter involved in this particular case was  db_file_name_convert, the paths defined at the spfile were different from those known to the Dataguard Broker. db_file_name_convert is a static parameter which means the standby database had to be restarted so the parameter was effective.  The actual actions that took place to solve this issue were to set the parameter at the database level and modify the value at the dgmgrl prompt.

Step 1. Modify the instance parameter.

alter system set db_file_name_convert='C:\Oracle11g\Oradata\OMCPro', 'J:\Oracle11g\Oradata\OMCDG', 'D:\Oracle\Oradata\OMCPro', 'D:\Oracle\Oradata\OMCDG' scope=spfile;

Step 2. Modify the parameter at the DGMgrl prompt

C:\> dgmgrl

DGMGRL> edit DATABASE "OMCADG" SET PROPERTY DbFileNameConvert = 'C:\Oracle11g\Oradata\OMCPro, J:\Oracle11g\Oradata\OMCDG, D:\Oracle\Oradata\OMCPro, D:\Oracle\Oradata\OMCDG' ;

Property "dbfilenameconvert" updated

Once the parameter is consistent both at the spfile and the Dataguard broker it updates its status to SUCCESS.

DGMGRL> SHOW CONFIGURATION VERBOSE;

Configuration - OMCDG1120

  Protection Mode: MaxPerformance
  Databases:
    OMCPRO - Primary database
    OMCADG - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

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 for it.  The funny thing is that it only replied with a kind of progress bar that read RSL Error 1 of 3 or RSL Error 2 of 3 and a small caps error message at the bottom of the display where the graph was supposed to be shown that read Error #2032.  Nothing else ... so after a long Google search that lasted the whole morning, digging for a clue on what was going on  I finally came up with a diagnostics and a possible solution.

Every time I tried to load a page with flash player contents it crashed, and in the case of YouTube it just mentioned the contents couldn't be played at this time and it asked to retry later (some sort of insanity, it doesn't matter how many times I'd have tried nor how later I'd retried the result would have been always the same). So none of the contents that required the flash player plugin could be played.

The solution was found in this Firefox support article "Flash 11.3 doesn't load video in Firefox"
I had installed the Real Player and the feature to download and save flash contents was making the Flash 11.3 plugin to crash.  The solution, just as it was described in the above article was to start the RealPlayer and from the main menu / Preferences / Download & Recording submenu, I unchecked the "Enable web download & recording for these installed browsers" option.  I restarted Firefox and everything came back to normal.  Since this is my work machine, I'm not too much worried about not being able to download my favorite videos by means of the Realplayer plugin.  I'm much more concerned about being able to display my Performance graphs and accessing the flash version of My Oracle Support.

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: 11.1.0.7
This problem can occur on any platform.
Oracle Server Enterprise Edition - Version: 11.1.0.7

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 (11.1.0.7.0) 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 showing up for a while. It may happen to be annoying.

The root reason is the Bug 7025700 , and some other related bugs, Bug 7757533, Bug 8224438, Bug 7643188, the arguments of the ORA-00700 may be slightly different: [KESQSMAKESQL-INVSTAT:CPUTIME], or [KESQSMAKESQL-INVSTAT:ELPSTIME], and the solution is to apply the patch 7643188 or apply the PSU patch to upgrade to 11.1.0.7.2.

Applying the patch will fix the problem from occurring in the future, but it won't fix the current corrupted data at the internal statistics tables, but eventually those corrupt rows will be flushed away, so in the mean time you'll have jut to get used to acknowledge it and manually clear the errrors at your E.M. Console.

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
Error - OCI_NODATA
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 procedure)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at X.SasquatchEngine.ProcessingUtilities.DataLayerRetrieval.RetrieveTaskData(Int32 orderTaskID, Int32 summitGroupID)



According to Oracle Support Note: Ora-13236 Internal Error In R-Tree Processing During Heavy DML [ID 443422.1] this error is due to a Bug (4570769) The solution procedure is described in the note. This bug was first seen in Oracle 10g, and so far it has not been updated.

The effects of this bug was the impossibility of insertion to a table containing an geometry element. Some geometries could be queried, some others on a very particular region couldn't be queried, so it was a symptom that a specific geographic region was corrupted inside the R-Index.

Actually we didn't try the workaround defined in the previously referred M.O.S. note, we rebuilt the R-Index instead.

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 to the hardware he was displaying on the stage he pointed at it and he said ... "we have a lot of hardware on the desktop". These keywords were the elements that defined what Larry Ellison wanted to show to the world this year.

The main goal of the hardware is to deliver the power of the hardware with highest performance and the lowest price. As usual the comparison against the IBM P795. Larry Ellison seems to enjoy these comparisons. The Exadata and Exalogic are aimed at achieve the maximum performance with the minimum cost, he referred the price of the servers is cheaper than the x86 servers world's cheapest servers.

What's the magic behind the servers ? The magic word is Parallelism and compression. The philosophy is to parallelize everything, this way data can be moved faster. The X machines parallelize both hardware and software. Since the current hardware cannot be made faster due to physical limitations, unless new technology arrive, in the mean time we have to use what's available and the challenge is to configure it so the best performance possible can be taken out of it.

Oracle is not creating a faster hardware, you cannot make the hardware faster due to physical constraints, so how can the hardware be made faster? Using the same hardware in a more efficient configuration. Not one big machine, not one big storage server, but a fundamental parallel everything architecture. If properly configured this increases throughput, performance, reduces energy and space consumption. The Exadata and Exalogic machines architecture probably is well known by now, infiniband connectors parallel infiniband network pipe connections moving data in and out. Infiniband is the fastest network. Faster, more reliable, and with a better performance than that of the internet.

"Performance is about moving data, not about processor", So if you think technology from this perspective, your goal will be to reduce the amount of data and increase the speed of moving it, if you are able to move less data at a faster speed the result will be the eXtreme performance.

The OS for the X machines was engineered to exploit the advantages of the parallelism, with all the advantages parallelism provides, such as no single point of failure and increased throughput and performance.

How can the amount of data be reduced? Well, from the hardware perspective the magic is created by compression. 10x data compression which means 10x less data to move, 10x data to store and another power related indirect consequences which makes this machine to require less physical space and have a small carbon dioxide footprint.

And what other surprises has revealed the big boss of Oracle? ... the Exanalytic machine (another X) and the Sun Supercluster (S), ... and I'll be talking about it in my next post.

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 was mixed with a high humidity level in the air and the consequence was that you could hear a lot of people sneezing and coughing in the conference rooms.

On the other hand, taking pictures of the architecture is always an amazing experience. The reflecting windows of the cylindrical shaped buildings that in my personal opinion emulate the physical shape of hard disks built with steel, concrete and glass. The blue colored windows disguise the buildings with the sky. Always an amazing landscape.

I don't know why the OOW doesn't start on Saturday, it would be great for me to use two weekend days and three vacation days rather than using one weekend day and four vacation days to attend the event. Most probably the logistics and the convenience for most of the people who attend the event.

The OOW is always a great experience that is absolutely worthy.

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;
SYSDATE-TO_DATE('08-OCT-75')
----------------------------
13084.4081

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

define DateDay = 13084.4081

Then the query

SELECT
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
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
+ &DateDay,'YYYY MM DD HH24:MI:SS') A
FROM DUAL);


and you've got your formatted output.

YEARS MONTHS DAYS HO MI SE
---------- ---------- ---------- -- -- --
35 9 27 09 47 40