AlderPump 2.2 released

First update to publicly available release 2.1 is shipping now. While its official number is 2.2, the release has a major new feature: file management. Upgrade from 2.1 is free, existing licenses continue to work.

Oracle DataPump is a server component; hence it can't handle files on user machines or other servers. File Manager closes the gap by allowing files transfer between user workstation and database server machine. Other basic capabilities such as file deletion, renaming, etc are also present.

The biggest problem was directory listing. None of Oracle releases to date support directory browsing. One can read, write, delete, or rename files - but only whose names were known from elsewhere. There is no rational explanation to that. Security reasons - one might say? Nonsense. Limit listings to folders exposed via Directory objects, add BROWSE privilege to already existing READ and WRITE - and let us be as secure as we want.

Until this is available, there are few workarounds:

Listing server-side directories

• First is Java stored procedure. Apparently Java code can browse files via java.io.File interfaces. We must ensure Java is installed on the database, compile a piece of Java code, create a PL/SQL wrapper around it, and finally grant JAVAUSERPRIV to the interested users. This is doable, but not so straightforward for developers of shrinkwrap software considering number of points of possible failure.

• Another method is undocumented, but simpler. Oracle 10g+'s package DBMS_BACKUP_RESTORE has a procedure to list contents of directory. The package is not accessible by public, EXECUTE privilege must be explicitly granted to use it. The procedure populates an in-memory table which we can read. Interestingly, it not only lists contents of requested directory, but recursively dives into subdirectories and lists them too. This is better explained at Christopher Poole's page.
  
  
• One may also consider capability of DBMS_SCHEDULER to execute OS commands. We could run dir and redirect its output to temp file, then parse it for file names. Again, from shrinkwrap software point of view, this is hell. Think about points of failure starting with scheduler jobs stuck (say, because job_queue_processes is 1 and job it is currently running got stuck - a real situation witnessed), recall all the OS-es out there and their variations of ls or dir, then mediate on where to write the temp file, finally think about formats of output. The method may work on a particular database with particular OS, but supporting any platform? Forget it.

AlderPump equally supports the first two methods i.e. Java and PL/SQL. Java has little advantage in terms of operations as it returns files sizes along with names; PL/SQL is simpler to configure, manage, and remove. It also runs on databases where Java is not present. Both methods with their advantages and disadvantages explained in greater detail on AlderProgs site.

But again, the entire idea of installing something on server side sucks. We live with it, but we are less than happy about it.

Reading and writing files

Another challenge was file copying. We couldn't read the entire file into big BLOB and transfer it to the client; DataPump files can easily span to gigabytes and reading them all to memory is not a good idea. So, slash them to chunks. But to read chunk, one must have file handle, and how to preserve the handle between calls? Pass it as a parameter you've said? Well, in 10g+ handle is not a single number it used to be in 9i, it is 3-field structure (see utl_file package). Worse yet, it is PL/SQL type, not Oracle type. Passing PL/SQL structures is not easy, especially with limitations of Microsoft provider for Oracle.

The only robust method is to pass file name and open/close file every time next chunk is read or written. Chunk size is limited to less than 32K. Reading 2G file would result in 64000 open/close operations not considering reads/writes themselves. Not very efficient.

AlderPump is using a hack: we pass PL/SQL in string unpacking it prior to any file operation. Should Oracle change the structure we are doomed, but the risk is measured.

As a side note, it is sad Oracle file handls are not atomic any more. There could be pretty complex structures behind them (and they are), but values exposed to users should be as simple as possible. Making it structure, especially one which can't be easily passed to client, effectively kills either performance or compatibility.

Working with remote databases

One of the features considered for implementation was working via database links. Indeed, if we can access a database, and that database has links to other databases - why can't we run AlderPump jobs there or at least manage files? As it tuned out, we can't.

What killed it was Oracle policy about types. Simply put, the fact that two types on different databases have the same name does not guarantee they are same type. Sounds logical, right? Yes, but implementation lacks forethought. Even though both types belong to SYS schema and database versions are the same up to patch level - stubborn type system still considers them different. DataPump uses types for job status, dumpfile info, and other purposes, unfortunately this effectively kills remote capabilities.

This type compatibility problem is very common, Oracle should really do something about it. Simplest coming to mind is hash, or checksum, or other sort of signature. "Sign" type with a key, then compare keys to ensure types are the same.

Installer

There was major rework on the installer. It can now install fresh version, upgrade 1.x and 2.1 to 2.2, or repair existing 2.2 installation. Uninstaller allso got smarter. Most of unsinstallers out there only clean out files they've created. AlderPump uninstaller also wipes out temporary ones such as sqlnet.log created by SqlNet on connection failure. It hunts down and removes saved job templates too - although this may be too obsessive. Finally, there is option to remove license, say to transfer it to another machine. Preserved licenses are picked up automatically on next install, they remain valid for all 2.x versions free of charge. Owners of 1.x versions can upgrade their licenses for free.

Next release

We are mostly done planning features for next release. It is scheduled to ship in 4 to 6 months, the rate at which AlderPump version are normally shipped. Like with this release, all 2.x licenses will continue to work and owners of 1.x (should any remain) may contact sales for free license upgrade. More details will be posted closer to release date.

AlderPump Lite will remain free for everybody although with limited features.

AlderPump is shipping

After 7 months of development, a year of beta testing, and 3 more months of building around infrastructure, AlderPump is shipping. Its official site is http://alderprogs.com.

Publicly available version comes in two flavors: Professional and Lite. In Professional mode with all features are enabled. The mode is available for first 30 days for evaluation or after buying a license. Lite mode with limited functionality is free. In this mode only current user's jobs can be monitored and managed. For job creation, four single-page wizards are enabled. They are to create table and schema mode export and import jobs. Command line generation for expdp/impdp is also there.

Looking at this in retrospective I must say choosing DataPump for automation wasn't very bright idea. DataPump is a new product and has ahead long way to evolve. Its interface changed quite a bit from 10.1 to 10.2 - this is why AlderPump is not really supporting 10.1 beyond checking for some quirks. Some promised functionality didn't work till later patches. Oracle 11.1 brought in new changes although not too revolutionary.

DataPump interface is quite obscure. Say, from developer's perspective division into modes is purely artificial. What is the difference between export in SCHEMA and FULL mode with schema filter? Why prefer one to another? Can one perform FULL mode import from dump taken in TABLE mode? (the answer is btw yes). Why there is a parameter to replace tables but not other objects?

Fortunately for AlderPump, expdp and impdp also suffer from artificial limitations - such as inability to mix INCLUDE and EXCLUDE filters (perfectly allowed by the API), specifying more than one expression filter (again, no limit), or applying metadata remaps basing on object types.

Needless to say, these restrictions are no subject for AlderPump which allows anything the API has exposed. Very fortunate for AlderProgs :)

Anyways, AlderPump has sailed. It is surprising how much work shipping takes, but the work was [almost] always fun so far. And ahead lies the best part: drafting plans for the next release. The time to throw in wild ideas with no real obligations, time to try new things without real need to make them working, time to travel away and claim this boosts creativity.

Obscurities in DataPump API: OPEN procedure

A couple of weeks ago, I ranted on Oracle DataPump API. The post wasn't published because the wording needed polishing; I wanted emotions to calm down before posting it. Today I'm glad it wasn't published at that time: Oracle released version 11g and I'm happy to see some problems were addressed there. I'll publish the original post followed by 11g comments.

The original 10g post:

Don't know who projected Oracle DataPump API, but obviously these people didn't invest much brain in their work. Feels like they started with a robust vision, but as project's deadline approached, something has changed. Maybe their chief architect got replaced with a summer intern. Or perhaps they strengthened their team with a bunch of unexperienced new hires. Or maybe a desperate manager decided to keep team's spirit high by stuffing their fridges with beer.

Whatever the reason was, the results were demolishing. I'm trying to summarize today's findings, updating the series as new "discoveries" come up.

OPEN function

The definition is as follows:

DBMS_DATAPUMP.OPEN (
operation    IN VARCHAR2,
mode         IN VARCHAR2,
remote_link  IN VARCHAR2 DEFAULT NULL,
job_name     IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE'
compression  IN NUMBER DEFAULT KU$_COMPRESS_METADATA
) RETURN NUMBER;

"If you have a procedure with more than 5 parameters, you're probably missing some". Indeed. Parameter "compression" is so important, that programmers shall think of it every time they create a job. The choices are so broad, that we can't make up our mind. Yes, we want compression! No, we don't! Yes, we do! Developers spend hours on meetings and management schedules a golf session to decide whenever they want to use such an important option.

Get real. In 99.(9)% of the cases the fraction of metadata is so small, that nobody gives a dime. Everybody wants compression. Just turn it on and put it to a dusty corner - such as SET_PARAMETER() procedure.

Oh, wait - is it here because 11g will offer new compression mode - data compression? Still, everybody loves compression. Turn it on and move it away.

I will not rant on "mode" much. Perhaps implementation difficulties make it necessary to decide early in the game what kind of 5 exports we want. Perhaps the paradigm was inherited from old exp/imp. I don't know. All I know is that choosing mode imposes limitations on other API calls. More on this in metadata_filter section.

Comments after 11g release:

The comments are based on documentation published on OTN. Maybe the real package is different (this was the case with some API calls in the past) - 11g installation is still being downloaded, but specification of OPEN has changed: parameter COMPRESSION now belongs to SET_PARAMETER procedure.

<paranoid mode>Oracle is reading my mind !</paranoid mode>

Not quite. The default is still to compress metadata only.

Hope Oracle left old version of OPEN in the package to preserve compatibility. Removing it would break existing code (my code will be broken, good thing it is not yet released).

Later, after installing 11.1.0.6/Linux:
Parameter "Compression" is still in OPEN, it just gone undocumented.