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

Hi,
Migration of Data from One operating system to other is one of the common task going on in IT company and DBA's job in the current trend specially when Oracle 11g release2 planned to use by many organization because of huge advantages.Here I'm describing the steps to do it.The best way to do any migration task will be prepare
a plan in WORD document format and review and recheck with your Senior DBA members to know the best possible method and than going for the actual implementation.The actual
implemented steps are described below:


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

In my upcoming posts I will be posting the issues which I faced after doing migration and how I resolve them.Hope it helps


Best regards,

Rafi.