Migrating Oracle Database from Windows vmware server to Linux vmware server

Hi,
I have migrated Oracle 10g Database running on Windows vmware server to Oracle 11g Database on linux vmware server.It was great learning experience with lot of mistakes,ofcourse mistakes are the building block for learning.Here I'm explaining the
steps which I have followed and the steps which we should follow to do migration task
successfully.Plan for each of the below steps so that you can complete your task on time.

Step 1: Installing RHEL 5.4(64 bit) on empty vmware machine.
-------
Vmware machine you can create by following the below link:

http://www.vmware.com/support/ws55/doc/ws_newguest_setup_simple_steps.html

I just go with the default installation steps,This we should not do,Please prepare the plan of each and every thing,starting from OS installation to Verifying the data after migration and make word document for easy reference..

Installation of Linux should be done with proper planning about the below things:
1)Space we need to give for /(root) mount point. (Recomended 2 GB ).
2)Space we need to give for software location path,where our Oracle Database resides.This mount point name we can give name like /u01 (Recommended 5 GB for Oracle11g)
3)Space we need to give for Database files,where all Datafiles,Redolog files,control file will resides,This is mainly dependent on your Database size.This mount point name we can give name like /u02 .We are planning to give 30 GB in future for this particular mount point.
4)Space we need to give for Archives logs,Where Oracle Database Archive logs can be present so that for recovery of Database,Whenever datafiles are corrupted we can make use of archive logs and do 100% recovery.This mount point name we can give /u03.

Starting Linux installation:
For accessing or managing multiple vmware machine install vsphere client.I have installed vsphere client version 4.0.0.For downloading vsphere client use the below link:

http://vmware-vsphere-client.software.informer.com/4.0/

On vmware machine Right click and open console and click on CD icon to istall from the DVD/CD of RHEL 5.4 by default it is pointed to E: Drive for DVD on your machine. While installing Linux(RHEL 5.4) please make sure you customise the options and do proper installtion,By setting proper ip address,Choosing the mount points as mentioned above based upon your requirement.Please try to install all the rpms(Red hat packages) while doing installation itself by customize option in order to avoid later problems.Please make sure firewall is disabled so that you can connect
from other machine or do file transfer.Current IP address,subnet mask you should ask
all the network details from your system admin for smooth flow of installation.Make sure Secure linux feature should also be disable to avoid some issues.

Below is the link you can refer while doing RHEL 5.4(Linux installation):

http://builddocs.com/server_os_builds/installing-redhat-5-4-64-bit/


Step 2: Verify the Installation.
------

[root@node2 ~]# cat /etc/redhat-release ->for checking the RHEL version installed
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
You have new mail in /var/spool/mail/root

Check the network:
------------------
Make sure the ipaddress is correct otherwise change the ip-address.

[root@node2 ~]# ifconfig -a
eth0 Link encap:Ethernet HWaddr 00:50:56:A5:16:DD
inet addr:10.47.33.208 Bcast:10.47.35.255 Mask:255.255.252.0
inet6 addr: fe80::250:56ff:fea5:16dd/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:2723271 errors:0 dropped:0 overruns:0 frame:0

Make sure the mount points,IP address and other OS related details are correct.

Below are some troubleshooting steps :

Network related troubleshooting:

Setting up a Static IP address:

Method 1:Command line

Login as 'root' user.
Change your Direcotory to:
#cd /etc/sysconfig/network-scripts/
#ls
You will find the configuration file by name ifcfg-eth0
#vi ifcfg-eth0

We have to set parameters given by network/system admin.

# Intel Corporation 82545EM Gigabit Ethernet Controller (Copper)
DEVICE=eth0
BOOTPROTO=none
HWADDR=00:50:56:A5:16:DD
ONBOOT=yes
DHCP_HOSTNAME=Hostname.abc.com
TYPE=Ethernet
USERCTL=no
IPV6INIT=no
PEERDNS=yes
IPADDR=10.47.33.108
NETMASK=255.255.252.0
GATEWAY=10.47.32.1

Save the configuration file.

For network reboot.
From root user
#service network stop
#service network start
OR
#ifdown etho
#ifup etho0

Method 2:Go to the Vmware server directly where linux is installed.

Right Click on the Vmware server and click open console
Go to system=>administration=>network
Click the DNS specify the
Hostname:node2.server.com
Primary DNS:10.47.33.108
Secondary DNS:192.168.1.110
DNS path:in.domain.com

Save the changes
Click on the eth0 to make it active.
Make sure eth0 is active.

Step 3:Perform the prerequisite before launcing the Software
------
a)Create groups & Oracle software installation user Oracle :
su - root
#groupadd dba => group of users to be granted SYSDBA system privilege
#groupadd oinstall => group owner of Oracle files
#useradd -m -c -g oinstall -G dba oracle
passwd oracle

(OR)
If we have the vmware server access where linux OS is installed
Go to system=>Administration=>Users and Groups ->for create groups and user directly and assigning them permission

b)Setting Shell Limits for the Oracle User:

vi /etc/security/limits.conf

# - nofile - max number of open files
# - rss - max resident set size (KB)
# - stack - max stack size (KB)
# - cpu - max CPU time (MIN)
# - nproc - max number of processes
# - as - address space limit
# - maxlogins - max number of logins for this user
# - maxsyslogins - max number of logins on the system
# - priority - the priority to run user process with
# - locks - max number of file locks the user can hold
# - sigpending - max number of pending signals
# - msgqueue - max memory used by POSIX message queues (bytes)
# - nice - max nice priority allowed to raise to
# - rtprio - max realtime priority
#
#
#

#* soft core 0
#* hard rss 10000
#@student hard nproc 20
#@faculty soft nproc 20
#@faculty hard nproc 50
#ftp hard nproc 0
#@student - maxlogins 4
hard nofile 65536
soft nofile 65536
# End of file
oracle hard nofile 65536
oracle soft nofile 4096
oracle hard nproc 16384
oracle soft nproc 2047

:wq =>save and quit

To see all the settings:
# ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 24576
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 24576
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

c)Creating Oracle Directories and giving permissions.

Create the directory for $ORACLE_BASE(u01/app/oracle):
su - root
mkdir -p /u01/app/oracle
chown -R oracle.oinstall /u01

Step 4:Download the oracle11g Software from the OTN (Oracle technoloy network) site from the below mentioned link.I downloaded 11g rel2 software for linux 64 bit.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Copy this software to the your OS location by using WinSCP or ftp.I used WinSCP.
Once you copied this to any mounted location /u01.Unzip the software using 'unzip' command.

Step 5:Install the Oracle11g Software and set the environment
------
Go to the runInstaller path and launch the Installation.
$./runInstaller


- Welcome Screen:
- Basic Installation: Checked it which is the default
- Oracle Home Location: Use default:/u01/app/oracle/product/11.2.0/Db_1
- Installation Type: I used the default: Enterprise Edition
- Global Database Name: TESTDB
- Database password: Type in the password for SYS, SYSTEM .

-Click next

If find some rpms missing,Download the rpms as they are open source and execute them in your operating system from root user
I had to install the following RPMs and dependencies to meet the above software requirements

To install:
#rpm -Uvh libaio-devel-0.3.106-32
#rpm -Uvh libaio-devel-0.3.106-32
#rpm -Uvh unixODBC-2.2.11 -32
#rpm -Uvh glibc-devel-2.3.4-23.i686.rpm

To verify this rpm already present
#rpm -qa liba*
or
#rpm -qa unix*
or
#rpm -qa
Once the Software is installed and Database is created with DBCA perform the below steps of Setting Oracle Environments:
Login as Oracle user
$bash
echo $SHELL
/bin/bash
[oracle@node1~]$ pwd
/home/oracle
vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=TESTDB

:wq => save and quit
Run the profile file:
#. .bash_profile
Note: Once you save this run the .bashprofile file to set the environment variable one time later on in linux OS it will run automatically,xo you can direct connect to Database using SQL*plus.

Step 6: Exporting the Data from the Source 10g Database.
------
I have to migrate 3 users data from Oracle 10g rel2 Database(on Windows VM server) to Oracle 11g rel2(on Linux VM server).I used expdp (Datapump export)

Use system user or create user with suitable privilege for doing

SQL> conn /as sysdba
Connected.
SQL> create user exportuser identified by exportuser;

User created.

SQL> grant connect,resource,exp_full_database,imp_full_database to exportuser;

Grant succeeded.

SQL> create directory expdbtestdir as 'D:\Rafi_docs';

Directory created.


SQL> grant read,write on directory expdbtestdir to exportuser;

Grant succeeded.

SQL> set linesize 121
SQL> col owner format a15
SQL> col directory_name format a20
SQL> col directory_path format a70
SQL> select *from DBA_DIRECTORIES;

OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- -------------------- ----------------------------------------------------------------------
SYS EXPDBTESTDIR D:\Rafi_docs
SYS SUBDIR D:\Oracle10g\demo\schema\order_entry\/2002/Sep
SYS XMLDIR D:\Oracle10g\demo\schema\order_entry\
SYS MEDIA_DIR D:\Oracle10g\demo\schema\product_media\

Create the .par(Parameter file) for windows server
Testusers_expdp_TESTDB_10Mar2011.par:

This .par file is our parameter file which is use by .bat file for windows server.

-- Testusers_expdp_TESTDB_10Mar2011.par
DIRECTORY=expdbtestdir
DUMPFILE=Testusers_expdp_TESTDB_10Mar2011.dmp
LOGFILE=Testusers_expdp_TESTDB_10Mar2011.log
SCHEMAS=TEST1,TEST2,TEST3
EXCLUDE=STATISTICS
CONTENT=ALL
JOB_NAME=testuser_Expdp_Testdb_01

Create the .bat(batch file) for windows server:

Userstest_backup_script.bat

set ORACLE_SID=TESTDB
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
set PATH=C:\oracle\product\10.2.0\db_1\bin;%path%
set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
expdp "'exportuser/exportuser'" parfile=E:\oracle10g\oradata\backup_scripts\export\plas_expdp_ebsora.par

For executing just double click the batch file.

Verify the logfile after installation.Check for any errors or warnings.

Step 7: Importing the Data to the Target 11g Database(On linux Vmware server) and validating the data.

Use the impdp utility for importing the 3 test users data.Copy the dumpfile from windows server to linux server by using winscp utility.

Note:Download winscp and install in your Windows Server or Windows Desktop.Winscp is a graphical utility for copying files from windows to linux.

SQL> create user importuser identified by importuser;

User created.

SQL> grant connect,resource,exp_full_database,imp_full_database to importuser;

Grant succeeded.

SQL> create directory impdbtestdir as '\u02\IMPDPTEST';

Directory created.

SQL> grant read,write on directory impdbtestdir to importuser;

Grant succeeded.

SQL> set linesize 121
SQL> col owner format a15
SQL> col directory_name format a20
SQL> col directory_path format a70
SQL> select *from DBA_DIRECTORIES;

OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- -------------------- ----------------------------------------------------------------------
SYS IMPDBTESTDIR \u02\IMPDPTEST


Note: Make sure you copy the export dump file in the location from where you are running impdp
In the above case '\u02\IMPDPTEST'. i.e Testusers_expdp_TESTDB_10Mar2011.dmp is copied in '\u02\IMPDPTEST' path in linux Vmware server where Oracle11g Database is up and running created with DBCA or using any manual script.

[oracle@host2 u02]$vi impdptest.sh
impdp importuser/importuser DIRECTORY=IMPDBTESTDIR DUMPFILE=Testuser_Mar_2011.dmp
SCHEMAS=TEST1,TEST2,TEST3 LOGFILE=Testuser_Mar10_2011.log

:wq =>save and quit

For executing in linux(unix environment) we use nohup command to run without any intervention.Use & to run in backgroud.

[oracle@host2 u02]$nohup sh impdptest.sh>a.out &

For checking this impdp job is running or not:
[oracle@host2 u02]$ps -eaf|grep impdp

For checking the running logs in linux(unix environment):
[oracle@host2 u02]$tail -f Testuser_Mar10_2011.log

It took around 1 hour since I was importing around 15 GB data.Once the import job is completed,verify the import log file for any errors or warnings.

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 like '%TEST%';

COUNT(*)
----------
80

Target Database(Oracle 11g rel2):
----------------
SQL> select count(*) from dba_objects where owner like '%TEST%';

COUNT(*)
----------
80

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

OWNER||''||OBJECT_NAME||''||STATUS
----------------------------------------------------------------------------------------
---
PUBLIC DBA_HIST_FILESTATXS INVALID
PUBLIC DBA_HIST_SQLSTAT INVALID

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

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

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

no rows selected
Once you make sure there are no invalid objects,inform the Team about the successful completion of Migration from Oracle 10g to Oracle 11g Database.Ofcourse you might get many issues or change in your applications when you do migration of Database,This issues and possible solution I will be posting in separate post.

Hope it helps.


Best regards,

Rafi.