Datapump Bug: Scheduler Jobs Not Imported in 11.2

Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP.

It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window.

Here’s an excerpt of a Data Pump Logfile:

ORA-39083: Object type PROCOBJ failed to create with error:
ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job attribute

Actually, the correct attribute name for this procedure would be “stop_on_window_CLOSE” instead of “stop_on_window_EXIT”. The latter is an attribute of the JOB_DEFINITION object type which in my eyes is inconsistently named in respect to the SET_ATTRIBUTE procedure.

The bug is fixed, but as of Nov. 2014 there is no one-off patch for version 11.2.0.4. The existing workaround is to ignore the error and recreate the job after the import .

If you run into this bug and it bugs you a lot, the other option is to ask for a patch for this issue.

ORA-01722 (invalid number) over and over again

This is a translation of my German article which was requested through “Google Translate” many times. It seems that throughout the years this error and its mostly simple causes haven’t lost their significance.

Here’s a sample scenario:

  • A table’s VARCHAR2 column shall be searched for numeric entries;
  • We use a query with a WHERE clause that constrains the scanned rows to only those containing numeric values.
  • Despite that, “ORA-01722″ is thrown.

So,  what happened?

  • The Oracle documentation states: “When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.” Think of an implicit TO_NUMBER being applied to the column.
  • This implicit TO_NUMBER might be applied before the filter predicates of your WHERE clause.
  • This in turn leads to an error, when an alphanumeric entry is hit — even when this entry wouldn’t be part of the scanned set if the filters were applied first.

Continue reading

Security Fix Breaks Recovery

Oracle’s “Security Alert Advisory for CVE-2012-3132” issued a warning about an attack vector that once again was discovered by security expert David Litchfield. The vulnerability allows to execute SQL code with SYS privileges by using object names containing quotation marks, if the attacker

  1. has authorized access to the database,
  2. has CREATE TABLE and CREATE PROCEDURE privileges and
  3. is allowed to execute DBMS_STATS.

A fix for this issue was published in July 2012 but there’s also Oracle’s advisory on how to cope with the threat without patching the RDBMS. This recommendation, however, has implications when such a database is recovered or cloned:

Continue reading

Monitoring Oracle Performance with AppDynamics

One of my customers uses AppDynamics as their tool of choice for Application Performance Management of a multi-tier, B2B/B2C application environment with several Oracle Databases at the back end. This tool gives a graphic overview of the application infrastructure, discovers (JDBC or .net) database queries automatically and provides metrics on the response time for those database calls as well as metrics from the servers where the application components run on.

To my surprise, I found out that there are so-called “Machine Agents” that can be run on the servers to collect performance and other metrics, but there wasn’t any component for collecting metrics from the Oracle RDBMS itself. Thus, born was the idea to create such a component (“Monitor” in the AppDynamics terminology)!

Continue reading

Now blogging in English!

Starting with my first public post 11 years ago, I focussed on publishing in German only, as there were many English resources but only very few in German.

But since the move from my homepage to wordpress.com, statistics showed that many of my posts were requested from non-German-speaking countries and several articles were often called through Google Translate (to English).

Therefore, I decided to translate those popular articles to English. And for starters, the next article will be published in English first!

I’m quite excited and anxious to see if this will be of value to the community. Let’s find out!