Oracle – for when it was like that when you got there

Main menu

Post navigation

Migrating Oracle Data from Windows to Linux using DataPump

It was a dark, stormy night in Redwood Shores. Only a single light burned at Oracle Towers. The Marketing Department was still locked in conference.
Countless flip-chart sheets littered the room, the result of thought-showers, story-boarding and numerous break-out imagineering sessions.
The challenge with which they had grappled all this time ? How to re-brand the long-time staple, but not particularly exciting export/import utility.
Suddenly, one nameless alpha-male ( and it must surely have been a man) rose to his feet, propelled by a lightning strike of inspiration. In a great, booming voice, dripping with testosterone, pelvis-thrusting beneath his ample girth for added emphasis, he announced to the room, “I know, let’s call it Data Pump !”

The name may have changed, the odd bell-and-whistle added, but the purpose remains unchanged. Export/Import ( Data Pump, if you must), is a utility for transferring objects and data from one Oracle instance to another, irrespective of the Operating System on which either the source or target database is running.

A while ago, I wrote a Source Control Application on Oracle XE in Windows, using PHP as a front-end. Yes, I have heard of Subversion. I just did it for my own amusement. You can do you’re own disparaging geeky/nerd comment.

Anyway, I’d like to revive this application ( partly to retrieve some of the code stored therin), and to start working on it in Linux.
I’ll still be using Oracle XE as the basis for this ( which is handy as DataPump can get a bit sniffy if you’re importing into a later version of Oracle than the one you exported from).

What this boils down to is that I need to export the objects and data in a single schema on the Vista partition and then import it onto the database in my Linux partition.
As both partitions reside on the same PC, using a database link isn’t an option. So, what I’m going to do is

On the Windows instance, create an export file and save it somewhere I can find it easily when I boot into Linux.

On the Linux partition, grab the export file and import it into my database.

Getting the Export

If our marketing friend was writing this, he’d probably title this section “Taking a Dump”. Yes, his sense of humour is a bit basic.

Now, unlike the Export utility, DataPump requires a directory object in the database. Fortunately, since the advent of 10g, this is created by default. No harm in checking though.

Checking dependencies

Now to shutdown Vista and startup again in a proper Operating System. Oooh, sly dig at Windows, our Oracle marketing guru would approve.
But hang on a second, if I run this import now, I’d get oh, I don’t know, 73 errors – mainly to do with grants to a role that doesn’t exist, so I won’t do that now because I’d never do something so silly. Ahem.

At this point it should be noted that I already have a fair idea of the nature of this application and I know what I should be looking for. Therefore, the following checks are probably not complete.

I’m going to check which object privileges this user has been granted ( system privileges should come across unchanged). Then I’m going to check which roles have been granted privileges – directly or otherwise – on the objects in this schema.

At this point it’s worth mentioning that we can include / exclude stuff from an export (using the include= or exclude= syntax).
The stuff in question is the object_path value in schema_export_objects.
Unfortunately for me, it looks like you can only grant object privileges on objects included in the export. Instead, I’ll do the Directory and Role creation prior to importing and then make the appropriate grants to VCM_USER after the import. I’ll then have to recompile any invalid objects.
At this point, it’s probably a good idea to check what stored program units in the schema are Invalid now :

The next step is to copy the dump file over to the DATA_PUMP_DIR.
Mounting the Windows partition using Nautilus is a case of :
On the Desktop Click on the Places Menu
click on xxGB Filesystem ( where xx is the size of your Windows partition)
Enter the admin password when prompted
When the Nautilus window opens in the root of this partition, click on the win_linux_docs folder.
The location bar should contain the path to this directory, in my case :

/media/71A36FA843AD644B/win_linux_docs

Copying the file over is now a case of opening a Terminal Session and :

OK, now we’re ready to attempt a recompilation. Of the four program units that threw up errors during the import ( VCM_GET, VCM_CHECKOUT, VCM_UNDO_CHECKOUT and VCM_OPERATIONS), VCM_UNDO_CHECKOUT was invalid to start with, so I won’t bother with that. As for the rest. Well, connecting as VCM_OWNER ( using the same password as in the source database) …

In this season of goodwill toward men, I probably shouldn’t be too hard on our nameless marketing man. After all, terms such as “hard-drive” and “RAM” were around long before the Oracle Marketing Department and must have contributed somewhat to the image of computer programmers as people who really need to get out more.