- 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! 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!