Oracle Database Migration from Windows to Linux Using Data Pump

There are multiple ways to migrate an Oracle database from one platform to another platform. One of the famous ways of database migration is using RMAN. In this article, we will be using Oracle data pump to perform database migration from windows to Linux.

• Data Pump migration from Windows to Linux (Same version)

• Data Pump migration from Windows to Linux (higher version)

Data Pump migration from Windows to Linux (Same version)

Both source & target database version is same - 12c

The biggest benefit of Data Pump is you can upgrade, migrate and optimize the database in one shot! Create a directory for export on source database

SQL> create or replace directory expdata as 'C:\expdata';
SQL> grant read,write on directory exdata to sys;

Use data pump to export database with full=y parameter

expdp directory=expdata dumpfile=fulldb.dmp logfile=fulldb.log full =y

On target host create the directory for impdp

SQL> create or replace directory expdata as '/u01/expdata';
SQL> GRANT read, write ON DIRECTORY expdata to sys;

Check tabelspaces on source database and verify if all tablespaces are existing on target database too. If not, create source tablespaces on target database

run on source & target
----------------------
SQL> select tablespace_name from dba_tablespaces;

Copy the dump files from source server to target server using WinSCP.

Start the import process on Target

impdp dumpfile=FULLDB.DMP logfile=fulldb.log directory=expdata full=y

Check whether non-default schemas/users were migrated or not

SQL> select username from dba_users where ORACLE_MAINTAINED='N';

Run utlrp.sql to compile invalid objects

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Data Pump migration from Windows to Linux (higher version)

Source database is 12c and target database is 19c version

The exporting of database is same as described in the previous scenario. The import process is little different when the target database version is higher.

On target database, create the directory for impdp

SQL> create or replace directory expdata as '/u01/expdata';
SQL> GRANT read, write ON DIRECTORY expdata to sys;

Start the import process on Target

impdp dumpfile=FULLDB.DMP logfile=fulldb.log directory=expdata full=y

The error which is shown here is due to the some of the objects are already presented. Compile Invalid Objects in TARGET

SQL> @?/rdbms/admin/utlrp.sql

Once done with the above steps execute the below command to verify the target database

SQL>select count(*) from dba_objects where status='INVALID';

Query V$OPTION to get currently installed database options

SQL> select * from V$OPTION where value='TRUE' order by 1;

Query DBA_REGISTRY to get currently installed database components

SQL>  select * from DBA_REGISTRY;

Execute the below scripts in target and verify the count of objects manually

set pages 990
col "size MB" format 999,999,990
col "Objects" format 999,999,990
select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

Check the CONSTRAINTS count in both source & target

SQL> SELECT constraint_type, count(*) AS num_constraints
FROM dba_constraints
GROUP BY constraint_type;

Done!