• Arun Kumar

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)


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!




286 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir

Oracle Database Migration from Windows to Linux Using RMAN

We all know that RMAN is a powerful tool for database backup, recovery, cloning and migration. In this article, we will be perform database migration from Windows to Linux using RMAN with two scenario