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.

Thursday, August 02, 2012

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.

Friday, June 22, 2012

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
    OMCPRO - Primary database
    OMCADG - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

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

Fast-Start Failover: DISABLED

Configuration Status:

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.


Configuration -

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

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

Fast-Start Failover: DISABLED

Configuration Status:

Monday, June 18, 2012

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.