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.

Connect to the database as system and run the following :

SELECT directory_path
FROM dba_directories
WHERE directory_name = 'DATA_PUMP_DIR'
/

From this, we know that, using the DATA_PUMP_DIR database object should give us a dump file in :

C:\oraclexe\app\oracle\admin\XE\dpdump\

Whilst we’re in the database, it’s a good idea to make sure we have the appropriate privileges ( this is particularly important if you’ve connected as a user other than SYSTEM) :

SELECT 1
FROM session_privs
WHERE privilege = 'EXPORT FULL DATABASE'
/

Assuming we get a “1” back as the output to this query, we’re good to go.
The schema I want to export is called vcm_owner. So, open a Command Prompt and :

C:\Users\Mike> expdp system/pwd@XE DIRECTORY=data_pump_dir DUMPFILE=vcm_exp.dmp SCHEMAS=vcm_owner
Export: Release 10.2.0.1.0 - Production on Sunday, 12 December, 2010 21:29:10
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@XE DIRECTORY=data_pump_dir DUMPFILE=vc
wner
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 704 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "VCM_OWNER"."VCM_PROGRAM_VERSIONS"          163.6 KB     105 rows
. . exported "VCM_OWNER"."VCM_APPLICATIONS"              5.382 KB       3 rows
. . exported "VCM_OWNER"."VCM_LABELS"                    5.406 KB       4 rows
. . exported "VCM_OWNER"."VCM_PROGRAMS"                  8.445 KB      60 rows
. . exported "VCM_OWNER"."VCM_PROGRAM_TYPES"             5.859 KB      17 rows
. . exported "VCM_OWNER"."VCM_USERS"                     5.562 KB       1 rows
. . exported "VCM_OWNER"."VCM_VERSION_LABELS"            9.187 KB      87 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\VCM_EXP.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:29:33

The output of this command is also saved to the export.log file in the DATA_PUMP_DIR.

In order to make it easier to find files I want to transfer between the two partitions, I’ve created a directory under the root of C – win_linux_docs…

C:\Users\Mike>cd c:\oraclexe\app\oracle\admin\xe\dpdump
c:\oraclexe\app\oracle\admin\XE\dpdump>dir
 Volume in drive C has no label.
 Volume Serial Number is 43AD-644B

 Directory of c:\oraclexe\app\oracle\admin\XE\dpdump
12/12/2010  21:29    <DIR>          .
12/12/2010  21:29    <DIR>          ..
12/12/2010  21:29             2,534 export.log
12/12/2010  21:29           544,768 VCM_EXP.DMP
               2 File(s)        547,302 bytes
               2 Dir(s)  16,314,642,432 bytes free

c:\oraclexe\app\oracle\admin\XE\dpdump>copy VCM_EXP.DMP c:\win_linux_docs\.
        1 file(s) copied.

c:\oraclexe\app\oracle\admin\XE\dpdump>

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.

set serveroutput on size 1000000
spool schema_check.lis
--
-- schema_check.sql
-- PL/SQL script to check dependencies prior to a datapump schema export
--
accept schema prompt 'Enter Schema Name : '
DECLARE
    l_schema dba_users.username%TYPE;
--
-- First check for any object privileges granted to this user
--
CURSOR c_obj_priv( cp_schema IN dba_users.username%TYPE) IS
    SELECT dtp.owner, dtp.table_name, dbo.object_type, dtp.privilege
    FROM dba_tab_privs dtp, dba_objects dbo
    WHERE dtp.owner = dbo.owner
    AND dtp.table_name = dbo.object_name
    AND object_type != 'PACKAGE BODY'
    AND dtp.grantee = cp_schema;
--
-- Check if any roles have been granted privileges on objects owned
-- by this user
--
CURSOR c_role( cp_schema IN dba_users.username%TYPE) IS
    SELECT DISTINCT grantee
    FROM dba_tab_privs
    WHERE owner = cp_schema;
--
-- check what roles this role has been granted
--
CURSOR c_granted_role(cp_role IN dba_role_privs.grantee%TYPE) IS
    SELECT granted_role, admin_option
    FROM dba_role_privs
    WHERE grantee = cp_role;

BEGIN
    l_schema := DBMS_ASSERT.SCHEMA_NAME(UPPER('&schema'));
    DBMS_OUTPUT.PUT_LINE('Required Object Privileges for '||l_schema);
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
    FOR r_obj_priv IN c_obj_priv( l_schema) LOOP
        DBMS_OUTPUT.PUT_LINE( r_obj_priv.privilege||' On '||r_obj_priv.object_type
            ||' '||r_obj_priv.owner||'.'||r_obj_priv.table_name);
    END LOOP;
    --
    -- Checking for roles that have been granted privileges on objects in this schema
    --
    DBMS_OUTPUT.PUT_LINE('Roles dependent on '||l_schema||' objects.');
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
    FOR r_role IN c_role( l_schema) LOOP
        DBMS_OUTPUT.PUT_LINE( r_role.grantee);
        DBMS_OUTPUT.PUT_LINE('------------------------');
        FOR r_granted_role IN c_granted_role( r_role.grantee) LOOP
            DBMS_OUTPUT.PUT_LINE(r_role.grantee||' HAS role '
                ||r_granted_role.granted_role||' Admin Option = '
                ||r_granted_role.admin_option);
        END LOOP;
    END LOOP;
END;
/
spool off

When I run this on the source database I get :

Enter Schema Name : VCM_OWNER
old  28:     l_schema := DBMS_ASSERT.SCHEMA_NAME(UPPER('&schema'));
new  28:     l_schema := DBMS_ASSERT.SCHEMA_NAME(UPPER('VCM_OWNER'));

Required Object Privileges for VCM_OWNER                                        
----------------------------------------------------                            
EXECUTE On PACKAGE SYS.UTL_FILE                                                 
READ On DIRECTORY SYS.VCM_CHECKIN                                               
WRITE On DIRECTORY SYS.VCM_CHECKIN                                              
WRITE On DIRECTORY SYS.VCM_CHECKOUT                                             
READ On DIRECTORY SYS.VCM_CHECKOUT                                              

Roles dependent on VCM_OWNER objects.                                           
----------------------------------------------------                            
VCM_USER                                                                        
------------------------                                                        
VCM_ADMIN                                                                       
------------------------                                                        
VCM_ADMIN HAS role VCM_USER Admin Option = YES                                  

PL/SQL procedure successfully completed.

So, I’ll need to :

• grant execute on UTL_FILE to VCM_OWNER
• create two DIRECTORIES – VCM_CHECKOUT and VCM_CHECKIN
• create two roles – VCM_USER and VCM_ADMIN
• grant VCM_USER to VCM_ADMIN with Admin option

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 :

select object_name, object_type, status
from dba_objects
where owner = 'VCM_OWNER'
and object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER', 'VIEW')
order by object_type, object_name;

VCM_OPERATIONS	PACKAGE	VALID
VCM_OPERATIONS	PACKAGE BODY	VALID
CREATE_VCM_USER	PROCEDURE	INVALID
PR_VCM_UPLOAD	PROCEDURE	VALID
VCM_CHECKIN	PROCEDURE	VALID
VCM_CHECKOUT	PROCEDURE	VALID
VCM_GET	PROCEDURE	VALID
VCM_LABEL_BULK	PROCEDURE	INVALID
VCM_UNDO_CHECKOUT	PROCEDURE	INVALID

So, it looks like we won’t need to worry about the CREATE_VCM_USER, VCM_LABEL_BULK, and VCM_UNDO_CHECKOUT procedures for the moment.

Prepare the target database

Let’s start by creating the directories :

$ cd /usr/lib/oracle/xe
$ sudo su oracle

[sudo] password for mikes: 

$ pwd
/usr/lib/oracle/xe

$ mkdir vcm_checkin
$ mkdir vcm_checkout
$ ls -l

total 28

drwxr-xr-x 3 oracle dba  4096 2009-12-06 17:02 app
drwxr-xr-x 2 oracle dba  4096 2010-10-01 22:11 db_out_files
drwxr-xr-x 3 oracle dba  4096 2009-12-06 17:07 oradata
-rw-r--r-- 1 oracle dba   681 2010-09-20 13:04 sqlnet.log
-rwxr-xr-x 1 root   root   33 2010-08-13 15:11 test.sh
drwxr-xr-x 2 oracle dba  4096 2010-12-20 21:47 vcm_checkin
drwxr-xr-x 2 oracle dba  4096 2010-12-20 21:47 vcm_checkout

Now to create the directory objects in the database ( first off just check to make sure that they aren’t already there). Connect as SYSTEM and :

SQL> SELECT directory_name FROM dba_directories;

DIRECTORY_NAME
--------------------------------------------------------------------------------
DATA_PUMP_DIR
MYFILES

SQL> CREATE DIRECTORY VCM_CHECKIN AS '/usr/lib/oracle/xe/vcm_checkin'
  2  /

Directory created.

SQL> CREATE DIRECTORY VCM_CHECKOUT AS '/usr/lib/oracle/xe/vcm_checkout'
  2  /

Directory created. 

Now create the VCM_ADMIN and VCM_USER roles then grant VCM_USER to VCM_ADMIN with admin option :

SQL> CREATE ROLE vcm_admin;

Role created.

SQL> CREATE ROLE vcm_user;

Role created.

SQL>GRANT vcm_user TO vcm_admin WITH ADMIN OPTION
SQL> /

Grant succeeded.

Importing the Schema

As with the export, we need to do a couple of checks. Once again, connecting as SYSTEM is probably a pretty good move :

SELECT directory_path
FROM dba_directories
WHERE directory_name = 'DATA_PUMP_DIR';

In this case, the DATA_PUMP_DIR is mapped to :

/usr/lib/oracle/xe/app/oracle/admin/XE/dpdump/

This time, we want to make sure we have privileges to import :

SELECT 1
FROM session_privs
WHERE privilege = 'IMPORT FULL DATABASE';

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 :

$ cd /usr/lib/oracle/xe/app/oracle/admin/XE/dpdump
$ sudo cp /media/71*/win_linux_docs/VCM_EXP.DMP . 
[sudo] password for mikes: 
$ sudo chmod 777 VCM_EXP.DMP 
$ 

The ownership of the file is changed to root, but that shouldn’t be a problem now we’ve changed permissions back to the original settings.

$ impdp system/pwd@XE DIRECTORY=data_pump_dir DUMPFILE=VCM_EXP.DMP SCHEMAS=vcm_owner 
Import: Release 10.2.0.1.0 - Production on Monday, 20 December, 2010 21:56:13
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@XE DIRECTORY=data_pump_dir DUMPFILE=VCM_EXP.DMP SCHEMAS=vcm_owner 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "VCM_OWNER"."VCM_PROGRAM_VERSIONS"          163.6 KB     105 rows
. . imported "VCM_OWNER"."VCM_APPLICATIONS"              5.382 KB       3 rows
. . imported "VCM_OWNER"."VCM_LABELS"                    5.406 KB       4 rows
. . imported "VCM_OWNER"."VCM_PROGRAMS"                  8.445 KB      60 rows
. . imported "VCM_OWNER"."VCM_PROGRAM_TYPES"             5.859 KB      17 rows
. . imported "VCM_OWNER"."VCM_USERS"                     5.562 KB       1 rows
. . imported "VCM_OWNER"."VCM_VERSION_LABELS"            9.187 KB      87 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"VCM_OWNER"."VCM_GET" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"VCM_OWNER"."VCM_CHECKOUT" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"VCM_OWNER"."VCM_UNDO_CHECKOUT" created with compilation warnings
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"VCM_OWNER"."VCM_OPERATIONS" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at 21:56:23

I’m not going to worry about the four ORA-39082 errors…yet.
Now I need to grant execute on UTL_FILE. As I’ve mentioned before, you can only do this when connected as SYS as SYSDBA.

$ sqlplus sys as sysdba@XE

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 20 22:37:53 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> GRANT EXECUTE on utl_file TO vcm_owner
  2  /

Grant succeeded.

SQL>  quit

Now, back on as SYSTEM…

SQL> GRANT READ, WRITE ON DIRECTORY vcm_checkin TO vcm_owner
  2  /

Grant succeeded.

SQL> GRANT READ, WRITE ON DIRECTORY vcm_checkout TO vcm_owner
  2  /

Grant succeeded.

SQL> 

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) …

SQL> ALTER PROCEDURE vcm_get COMPILE;

Procedure altered.

SQL> ALTER PROCEDURE vcm_checkout COMPILE;

Procedure altered.

SQL> ALTER PACKAGE vcm_operations COMPILE BODY;

Package body altered.

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.