Step by step migration of schemas from Windows server Oracle10g Database to Linux Oracle 11g Database

Migration from SOURCEDB1 to TARGETDB1:
--------------------------------------
I'm going to use Datapump for migrating from SOURCEDB1(Oracle 10g) to TARGETDB1(Oracle 11g) Database.


General information:
---------------------

Source:
---------
OS version: Windows Server 2003 R2
Database version:10.2.0.4.0
Database size:33 GB


Target:
---------
OS version:RHEL 5.4
Database version:11.2.0.1.0

SCHEMAS TO MIGRATE:TESTTOOL,TESTTOOLMIG,TESTTOOLQA,TESTD,TESTD_TEST,TEST3CNS,TESTHP,TEST_USR


STEP 1: CREATE USER FOR OUR MIGRATION PURPOSE WITH ENOUGH PRIVILEGES
------

SQL> create user MIGTEST
2 identified by MIGTEST;

User created.

SQL> grant dba to MIGTEST;


SQL> select name from v$database;

NAME
---------
SOURCEDB1 

SQL> alter user MIGTEST identified by welcome123;

User altered.

SQL> alter user MIGTEST default tablespace users;

User altered.


Grant succeeded.

Step 2: CREATE DIRECTORY FOR DATAPUMP
-------
Create one directory for Datapump and point it to a directory where we have to
keep our dump files of datapump
Note: This location of keeping dump files should have enough space.

Directory Creation:
-------------------

SQL> CREATE DIRECTORY SOURCEDB1_MIG AS 'D:\ACEDBMIG';

Directory created.

GRANT Permission:
-----------------

SQL> GRANT READ,WRITE ON DIRECTORY SOURCEDB1_MIG TO MIGTEST;

Grant succeeded.


VERIFYING THE LOCATION:
---------------------------

SQL> select *from dba_directories;

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
---------------------------------------------------------------------
SYS SOURCEDB1_MIG
D:\SOURCEDB1 

STEP 3: EXPORT THE SCHEMAS WHICH WE WANT TO MIGRATE:
------------------
Here I'm create one parameter file for export where I'm specifying
schemas which I want to export.The details and usage of this parameter can
be obtained from command 'expdp help=y' and very much self explanatory.


a) create one parfile(.par):
----------------------------

mig_expdp_SOURCEDB1_12APR_2011.par:
________________________________

SCHEMAS=TESTTOOL,TESTTOOLMIG,TESTTOOLQA,TESTD,TESTD_TEST,TEST3CNS,TESTHP,TEST_USR
DIRECTORY= SOURCEDB1_MIG
DUMPFILE=mig1_expdp_SOURCEDB1_12APR_2011.dmp 
LOGFILE=mig1_expdp_SOURCEDB1_12APR_2011.log 
EXCLUDE=STATISTICS 
CONTENT=ALL 
JOB_NAME=mig_expdp_SOURCEDB1_12APR_2011
PARALLEL=2

b)Create one batch file(.bat):
----------------------------------

SOURCEDB1_MIG_12APR2011.bat:
__________________________

expdp MIGTEST/MIGTEST PARFILE=E:\TEST_work\SOURCEDB1 _MIG_UGALAXY\mig_expdp_SOURCEDB1_12APR_2011.par

We can execute the above batch file by double clicking it.Check the dump file location it
will be surely in the path 'D:\ACEDBMIG'(This is the path which you have used for creating
Datapump Directory).Check the log file for any errors or warnings.

STEP 4:COPY THE DUMPFILE FROM SOURCE DATABASE SERVER(WINDOWS) TO TARGET DATABASE SERVER(LINUX)
-------
For copying the dumpfile I used winscp to copy it to my windows machine and than transferred to linux target
server,however you can use ftp or any other technique.


STEP 5:CREATE THE DIRECTORY FOR THE DATAPUMP IMPORT:
------

create directory IMP_MIGRATE_UGALAXY as '/u04/UGALAXY/ACEDB_MIGRAT_DUMPS';

grant read,write on directory IMP_MIGRATE_UGALAXY to system;

STEP 6:IMPORT THE DUMP IN THE TARGET DATABASE:
------

The import scripts are:
-----------------------

vi impdp_job_Apr13_2011.sh

impdp system/manager DIRECTORY=IMP_MIGRATE_UGALAXY REMAP_SCHEMA=TESTTOOL:TESTTOOL,TESTTOOLMIG:TESTTOOLMIG,
TESTTOOLQA:TESTTOOLQA,TESTD:TESTD,TESTD_TEST:TESTD_TEST,TEST3CNS:TEST3CNS,TESTHP:TESTHP,TEST_USR:TEST_USR
DUMPFILE=MIG1_EXPDP_ACEDB_12APR_2011.DMP LOGFILE=MIG1_EXPDP_ACEDB_12APR_2011.log

To run impdp job:
------------------

nohup sh impdp_job_Apr13_2011.sh >a.out


Once the import is completed check the logfiles for errors and warnings.

STEP 7: Validation of Data:
------

Validating the Data: 
-------------------- 
Once the import is done successfully verify the object imported.Compare the Source and Target Databases: 


Source Database(Oracle 10g rel2): 
---------------- 
SQL>select count(*) from dba_objects where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');

Target Database(Oracle 11g rel2): 
---------------- 
SQL>select count(*) from dba_objects where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');


Check for the invalid objects in Target Database side: 
-------------------------------- 
SQL> select owner||' '||object_name||' '||status from dba_objects 
where STATUS='INVALID'; 


Compile the invalid object using the below script: 
------------------------------------------------- 

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

Recheck again and compile:
---------------------------

SQL> select owner||' '||object_name||' '||status from dba_objects 
where STATUS='INVALID'; 


Comparison of privs:
----------------------

Source:
----------
1)Object privs:
------------------
select grantee||' '||owner||' '||table_name||' '||privilege from dba_tab_privs
where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');

2)System privs:
----------------
select grantee||' '||privilege from dba_sys_privs
where grantee in ('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');


Target:
------------

1)Object privs:
------------------
select grantee||' '||owner||' '||table_name||' '||privilege from dba_tab_privs
where owner in('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');

2)System privs:
----------------
select grantee||' '||privilege from dba_sys_privs
where owner in('('TESTTOOL','TESTTOOLMIG','TESTTOOLQA','TESTD','TESTD_TEST','TEST3CNS','TESTHP','TEST_USR');