Scenario is that we would like to export a schema called SWX residing on a Oracle 11.2.0.2 database running on Windows 32- Bit to a Oracle 10.2.0.4 database running on SPARC 64-bit. The source database has transparent database encryption running. The tablespace where the schema resides is fully encrypted using TDE. The target database is not running TDE.
To do this complex export import scenario. We have to use expdp and impdp. Since exp and imp has limitations and is not as effecient as impdp expdp (Data Pump) . Exp and IMP should be used by database administrators only if they are running a 9i database and want to export it to a higher version database.
First thing on the Source database, where “SWX” schema resides. We will do the following steps :
1. Create a directory for the export backups and grant read write permissions for it to the public (all users)
SQL> create or replace directory export_dir as ‘E:\ORA_BACKUPS’;
SQL> grant read, write on directory export_dir to public;
2. expdp system/sys directory=export_dir dumpfile=exp_win_11th_sep_11.dmp logfile=export.txt schemas=swx encryption_password=ora11g version=10.2
or alternately you can create a parameter file called export.par with below parameters and run expdp
directory=export_dir
dumpfile=exp_win_11th_sep_11.dmp
logfile=export.txt
schemas=swx
encryption_password=ora11g
version=10.2
expdp system/sys parfile=export.par
3. FTP the file to the target database using bin mode
4. If the user already exists on target server, you can drop the users. If it doesnt exist proceed like below.
5. create a import directory like we created export directory on source server. The location where you FTP’ed the backupfile should be defined as import_dir
SQL> create or replace directory import_dir as ‘/u2’;
SQL> grant read, write on directory import_dir to public;
6. Create a parameter file called import.par with below parameters
directory=import_dir
dumpfile=EXP_WIN_11TH_SEP_11.DMP
remap_tablespace=swx_enc:users
logfile=import.txt
# Do not use encryption_password since this database does not have a wallet defined. If there was already a wallet for this database. You should first open the wallet and then use encryption_password parameter
# the original tablespace in source database where the schema SWX orginated was called “swx_enc”. The tablespace where it will be imported on target database is called “users”
remap_tablespace=sourcetbs:targettbs
#If you want to import to a different schema you can use the option remap_schema=sourcename:targetname
7. impdp system/system parfile=import.par
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/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:”SWX”.”SPUPDATECYCLE” created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW01” prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.
Job “SYSTEM”.”SYS_IMPORT_FULL_02″ stopped due to fatal error at 12:18:44
Do not care for this error. The schema has been imported succesffuly into the 10g database.
So as you can see how efficient datapump is as a tool for doign cross platform cross version import and export of data. If you want to do full database restores then RMAN is the best way to do it. But in crossplatform scenario, Transportable tablespace becomes overtly complicated and also it has the limitations of little-endian and big-endian. But datapump has no such restrictions. You can practically export and import from any version to any version , to any platform to any platform.
