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!

Before proceeding complete pre-requisites on Source Database

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;

Check Size for Source Database

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;

Compile Invalid Objects in Source Database

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

Check the invalid count in Source Database

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

Now Proceed with the Database Export.

Create a directory for export on source database in OS and database

SQL> create or replace directory expdata as 'C:\expdata';
SQL> grant read,write on directory expdata 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

mkdir -p /u01/expdata

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

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';

Check the CONSTRAINTS count in both source & target

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

Done!