Nazaudy, a spark in your curious mind

SuSe 10 running Oracle 11g migration project

This article covers a project I did in the previous IT infrastructure I was supporting, where we had SuSe 10 running Oracle 11g migration project, an old Oracle database running on an obsolete SuSe Linux virtual machine, together with a front web end, and we wanted to move the whole thing to a more up to date Operating System, moving it away at the same time from Oracle, if that was possible, because the support for it was too expensive. Furthermore, there was only one critical section of the Oracle database that we wanted to move, which data we did not want to lose at all, bur the rest of that chunky Oracle database could easily be discarded

Oh dear, where do we start? First of all, document what we have and make a diagram with the plan of where we want to be

 

  1. Documenting existing problem
  2. Creating the test environment
  3. Fixing the SuSe Oracle cloned VM
  4. Fixing the SuSe Apache Tomcat cloned VM
  5. Create snapshots for both VMS, and needed tools
  6. Create a VM with the free version of Oracle
  7. Configure the new Oracle database
  8. Connect to both Oracles using Oracle SQL Developer
  9. Updating the SID instance name in the new Oracle
  10.  

 

1.- Documenting existing problem

We were running VMware vSphere 6.5 on the infrastructure where those SuSe Linux Enterprise 10 were running, which extended support finished in July 2016 (https://www.suse.com/lifecycle/), and they were never gonna take on board the new versions of VMware tools. Besides, we were running the 32-bit version of SuSe Enterprise 10, a prime target for the SMB Wannacry vulnerability (https://www.suse.com/support/kb/doc/?id=7019508) and that was a major worry for us. Another condition that intensified the urgency of this project what that the Oracle version was also obsolete and no longer supported (https://n4stack.io/oracle-11g-end-of-life/), so we just could no longer have these systems in production anymore

What versions were we running on these old systems? These commands came handy to find that out:

cat /etc/issue
cat /etc/SuSE-release
cat /opt/tomcat/RELEASE-NOTES 

 

2.- Creating the test environment

Before embarking in the process of upgrading the whole system, I cloned both VMS (the Oracle and the Apache web-front GUI, and had to shut the Oracle down first otherwise it won't copy) using vCenter into one of our test ESXi servers, and created a unique VLAN for these machines to communicate among them in isolation, totally separated from the production environment network. I also created a Management VM with Windows 10 which I called "XYZ-MANAGEMENT", from where I'll SSH those machines without affecting their replicas who were still in production

Here you can see the 3 VMs to start with, in an "Isolated" network with a physical network card attached which has an unique non-routable VLAN, thus making it truly isolated

 Environment for SuSe 10 running Oracle 11g migration project

 

3. Fixing the SuSe Oracle cloned VM

After the restore, when you first turn on the SuSe Enterprise 10 running Oracle, I notice that the network card wasn't working. The VMware clone doesn't work well with such ancient Linux OS. To fix it I opened "yast" and deleted the dummy card that was listed as 'not connected', if you can't deleted it, first set a "No IP Address (for Bonding Devices)" to both cards, and then reboot

Network Card and configuration overview

Once the system boots, and before proceeding, ensure that IP address of the server is responding. If you managed to fix the network, then use SSH from the management computer and logon with the username of "oracle", and start executing the following to initialise the Oracle database:

#lsnrctl status listener  //**check status of the listener, then restart it
#lsnrctl stop listener  
#lsnrctl start listener  

#cat /etc/oratab          //**identify the SID of the database
#export ORACLE_SID=PROD   //**export the SID, on this example is "PROD"
#sqlplus / as sysdba      //**logon to Oracle, it will use WHICHEVER account you have on the shell to logon

SQL> select status from v$instance;  //**check status of the database
SQL> select instance_name from v$instance;

SQL> Shutdown
SQL> Startup

 

If you get the error The ORA-01031: "insufficient privileges" make sure you're logon to the shell with an account that has full access to Oracle, and not the root

For documentation purposes, this is a video of the SuSe Oracle server booting up:

 

4. Fixing the SuSe Apache Tomcat cloned VM

When booting up this other SuSe Enterprise cloned VM, running Apache Tomcat, I find out that I could not logon to it, the machine was giving "Error on service module" and refused to logon. Not a good way to start a Friday! This error is documented in this link by SuSe: https://www.suse.com/support/kb/doc/?id=7008866

Error in service module

To fix this problem you actually need to start the VM in running level 1, by inserting "1" in the boot options, then once you boot on single-user mode, open "yast" and remove the duplicated network card, then issue these commands:

cd etc/var
mkdir log
touch lastlog

For documentation purposes, this is another video of the SuSe Enterprise machine booting with Apache tomcat on it:

I needed to restart the Apache Tomcat service after the restore, enabling that way a right communication between the Oracle and Apache

cd usr/local/tomcat/bin
./restart.sh

This is a screenshot of the front end website that the application runs. I need to transfer all these systems to a Linux platform more up to data than SuSe 10 SP3, let's see how we'll do that

Issues Enquiries in SuSe 10 running Oracle 11g migration project

To change the setting of this IP address, edit the file: /share/www/webapps/helloacc/issues/DisplayFeature.jsp

The tomcat-users.xml configuration file for SuSe is located in /usr/local/apache-tomcat-5.5.23/conf folder

 

5. Create snapshots for both VMS, and needed tools

Before moving any further, create snapshots of the now working cloned VMs, so we can restore to them if things go awry, which most likely will be now that we are stepping into unknown territory

Recent Tasks

 For the Windows 10 Management machine, you need to download and install these tools:

 

6. Create a VM with the free version of Oracle

Install CentOS7 on a virtual machine using the Server with GUI option, without selecting anything else (don't do CentOS8, still not supported by the free version of Oracle XE). We'll use this new virtual machine as the destination for the new Oracle to migrated the data to from the old SuSe machine. Download the Oracle Express Edition for Linux (64-bit) from here, together with the pre-install package requirements : https://www.oracle.com/database/technologies/xe-downloads.html

Oracle database XE Downloads

 

Before installing anything, start by checking the status of SELinux by running "sestatus", and if the current mode is enforcing, disable SELinux for this project by doing the following. You could disable SELinux just for your current session by running "setenforce 0" ,but for the purpose of this project is better to have SELinux completely disabled.... and to disable the firewall too. I know, we are throwing security down the window, but we have to for this project

vi /etc/selinux/config
#on the above file, set SELINUX=disabled

systemctl disable firewalld
#disables the CentOS firewall

If you disabled SELinux, reboot your system, then execute these commands:

#Set the downloaded files from Oracle executable
cd /home/user/Downloads
chmod +x *

#open the Software Repositores tool using the Software app, and enable all repos
su -   #ensure you're root to install the applications below
yum -y install compat-libcap1
yum -y install compat-libstdc++-33
yum -y install glibc-devel
yum -y install ksh
yum -y install libaio-devel
yum -y install libstdc++-devel

cd /home/user/Downloads #switch back to the directory where the Oracle files are
rpm -ivh oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
rpm -ivh oracle-database-xe-18c-1.0-1.x86_64.rpm

Once the installation is completed, you'll be shown a window similar to this one below; notice these two important locations:

/etc/sysconfig/oracle-xe-18c.conf  #the conf file for Oracle

/etc/init.d/oracle-xe-18c (configure/delete/start)   

Suse Oracle updating

Finally, ensure that Oracle will start after each reboot by executing this command:

systemctl enable oracle-xe-18c 

#reboot the VM to ensure that Oracle starts okay, alternatively issue:
systemctl daemon-reload

At this time, do make a snapshot again of this virtual machine, then ensure that you connect the VM to the isolated network, together with the older Oracle VM, and remember to update the IP address accordingly

 

7. Configure the new Oracle database

Once the snapshot has completed successfully, start configuring the new Oracle database as follows:

#set the correct name that you want for the computer first!
vi /etc/hostname  
   oracle2

vi /etc/hosts  
  127.0.0.1      localhost
  ::1            localhost
  192.168.0.204  oracle2

After setting the name of your CentOS, run the configure file

cd /etc/init.d/
./oracle-xe-18c configure
#Password for SYS = Arnw3n40
#Password for SYSTEM = Arnw3n40
#Password for PDBADMIN = Arnw3n40

After the installation is completed, you'll presented with a window similar to this:

SuSe 10 running Oracle 11g migration project installation completed

To enable the webportal (that by default will listen to 127.0.0.1) run this command, and ensure you got Adobe Flash Player installed

SQL> select dbms_xdb_config.gethttpsport() from dual;

The installation creates an account called "oracle, which is the one we'll use NOT to logon to the CentOS machine but to logon to Oracle DB itself. Continue with the configuration as follows to add the correct path to the oracle account profile

#run the below commands with the root account
su oracle
cd ~
vi .bash_profile

#then copy the following entries into the bash profile of oracle
export ORACLE_BASE=/opt/oracle/ 
export LD_LIBRARY_PATH=/opt/oracle/product/18c/dbhomeXE/lib 
export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE 
export PATH=$ORACLE_BASE/product/18c/dbhomeXE/bin:$PATH 
export PATH=/usr/bin:$PATH 
export ORACLE_SID=XE

#execute the following to activate the profile
source ~/.bash_profile

We are going to be using the oracle account back and forth, so it would be a good idea to create a password for  the oracle account and then add it to the sudoers file, so that you can use any privilege commands with the oracle account by just adding 'sudo' at the front of them. Use root, do the following:

passwd oracle  #create a password for oracl
sudo visudo    #edit the sudoers file 

#add this entry below the existing root entry:
root     ALL=(ALL)     ALL
oracle   ALL=(ALL)     ALL 

Finally, run the below commands to ensure everything works okay and that you can connect to Oracle

ps -ef |grep smon
echo $ORACLE_SID
lsnrctl status

sqlplus sys as sysdba

SQL> select status from v$instance;  
SQL> select instance_name from v$instance;

 

 

8. Connect to both Oracles using Oracle SQL Developer

Before we proceed any further, run lsnrctl status on both Oracles, and take note of the port that they have open for listening. The following is a screenshot of that command run on the old Suse 10 Oracle, listing the default 1521 as the port where Oracle is listening, while the same command run on the new CentOS Oracle list port 1539 as the listening port, so ensure that you verify and take note of the correct port

 lsnrctl status command

Once you got the details of both ports, visit your Windows 10 machine where you previously installed Oracle SQL Developer, and add both databases using the username SYSADMIN. If you get the error message Connection to Oracle failed ORA-01017: invalid username/password; logon denied, first of all verify that the Oracle database is up and running (restart the listener and export the SID). If it is running okay, then issue this command on the Oracle Linux to fix the error:

SQL> ALTER USER SYSTEM IDENTIFIED BY PasswdOfYourChoice;

This will reset the SYSTEM account to a password of your choice, and that logon details should let you in

Setup the two connection using Oracle SQL Developer, and ensure that the test connections are sucessfull

 Connect to Oracle database

And this is the screenshot for the new Oracle, notice that the port is different

Oracle XE port connection

 

9. Updating the SID instance name in the new Oracle

The new Oracle comes by default with "XE" as the name for its instance. We need to change that name to "PROD", which is the name instance hard coded on the Apache legacy website... what can I say? We need to do this. To find out the name of the instance on the new Oracle, which you know it will be XE anyway, but just to double-check that, run this command:

SQL> SELECT instance FROM v$thread;

Before doing anything else, dismount the database like this:

SQL> Shutdown immediate

Update the new SID values

vi ~/.bash_profile
#edit the entry to read 'export ORACLE_SID=PROD'

vi /etc/oratab
#edit the instance to read PROD instead of XE
#also add a 'Y' at the end of the line instead of the 'N'

source ~/.bash_profile
#update the ORACLE_SID with the new value

 

Visit /opt/oracle/admin and copy the XE folder to a new folder called PROD, then execute "chmod -R 777 PROD"

Rename the corresponding files to the new SID value

#-#vi /opt/oracle/admin/XE/pfile/init.ora.xxxxxx
#-#update the entry 'db_names="PROD"'
#-#destination to point to /opt/oracle and not <oracle_home>
cd /opt/oracle/admin/XE/pfile
cp init.ora.xxxx initPROD.ora
vi initPROD.ora
local_listener=LISTENER_PROD


#-#mkdir /opt/oracle/fast_recovery_area

cd /opt/oracle/product/18c/dbhomeXE/dbs/
cp init.ora initPROD.ora
db_name='XE' and not ORCL


copy the control01/02 files to the dbs location

SQL> show parameters control_files;
show parameter pfile
show parameter compatible
SQL> create spfile from pfile;
SQL> create pfile from spfile;
SQL> alter system set control_files='/opt/oracle/product/18c/dbhomeXE/dbs/control01.ctl scope=spfile;

alter database mount;


mv spspfileXE.ora spfilePROD.ora
vi spfilePROD.ora
#### '*.db_name='PROD'
#### '*.local_listener='LISTENER_PROD'
#### *.compatible='11.2.0.3.0'
#### *.control_file='/opt/....control01.ctl'

 

vi /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
#edit the entry PROD =
#edit the entry to read '(SERVICE_NAME=PROD)
#edit the entry LISTENER_PROD

 

 

 

The installation of Oracle will have created a new account called "oracle", enable the password for it using the control panel in your CentOS machine

Proceed as below to configure Oracle by first of all editing the 'tnsnames.ora' file. If the file is not in the "admin" folder, copy the sample from the "samples" folder

vi /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
#set the IP address on your VM on your HOST ENTRY

##Example of the tnsnames.ora file:

XE = 
  (DESCRIPTION=
    (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=TCP)(HOST=oracle2)(PORT=1521))
    )
    (CONNECT_DATA=
       (SID=XE)
    )
  )

LISTENER_XE =
   (ADDRESS = (PROTOCOL=TCP) (HOST=oracle2) (PORT=1521))

 

 

 

To configure the Oracle dabatase to start automatically upon reboot of the system, do as follows:

systemctl daemon-reload
systemctl enable oracle-xe-18c 
#Now, use the oracle account to start the database
sqlplus sys as sysdba
SQl> Startup
lsnrctl start

If you get the error ORA-12505, TNS:listener does not currently know of SID given in connect descriptor, change the DEFAULT_SERVICE_LISTENER = PROD

An example of the listener file:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SECURE_REGISTER_LISTENER=(TCP)

 

To test that you can
#Don't change the ORACLE_SID from the default XE, otherwise it won't start

#Edit the PATH and add :/opt/oracle/product/18c/dbhomeXE/bin
#Finally, to logon to the DB use:

 

 

oracle@centos# export ORACLE_SID=XE

oracle@centos#./sqlplus "/as sysdba"

 

After a reboot, do the following to start the ORACLE2 database:

#Check the name of the running Oracle instances
ps -ef |grep smon
echo $ORACLE_SID

 Ensure the SELinux is off for the purpose of this project

vi /etc/selinux/config
#set SELINUX=disabled

---------------

 

8. Install Tomcat on the new CentOS virtual machine

Use WinSCP from the Windows 10 management, to move the website content from the SuSe Apache to the new CentOS Apache, then we should install Java and Tomcat on the new CentOS so we can run .jsp pages on it. Tomcat is a Java Serverlet container that will allow us to run java pages on the Apache server

yum install tomcat
yum install tomcat-webapps
yum install tomcat-admin-webapps
yum install tomcat-docs-webapp
yum install tomcat-javadoc
firewall-cmd --permanent --add-port=8080/tcp
firewall-cmd --permanent --add-port=443/tcp
firewall-cmd --reload

We need to create an user to manager Apache Tomcat. Edit the file /etc/tomcat/server.xml and ensure the GlobalNamingResources section is pointing to the right location of tomcat-users.xml

#extract of the file /etc/tomcat/server.xml
  <GlobalNamingResources>
    <!-- Editable user database that can also be used by
         UserDatabaseRealm to authenticate users
    -->
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="etc/tomcat/conf/tomcat-users.xml" />
  </GlobalNamingResources>

 

 

 Export SHIRE to a file, then open that .sql file and run it against the Oracle2 DB

 

 

6. Create a SQL database on Ubuntu Server

I figure out that the best thing to approach this project will be in stages, like all good projects do, so I decided the 1st objective to be to transfer the DB from Oracle to a SQL server, and for that I needed to know the size of the database in Oracle, which on my case was 4GB. Run this command on the Oracle SuSe to find out:

SQL> select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

Jumping now the new Linux virtual machine that will host SQL, the machine called XYZ-LINUXSQL, I installed Ubuntu Server 16.04.6, supported by Microsoft to run SQL server. Do install it with minimal configuration:

SuSe software selection

 

After the installation of Ubuntu (and obviously the run of 'sudo apt-get udpate'), execute the following commands to install dependencies, register a Microsoft depo and install and download Microsoft SQL server. Note that you have to install the "Microsoft SQL Evaluation Edition", otherwise you won't be able to transfer the data from Oracle, the free version of SQL Express doesn't support migration to/from Oracle

#Be careful when copying/pasting these 2 commands, pipe (|) don't paste well!
sudo wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
sudo apt-get update
sudo apt-get install -y --allow-unauthenticated mssql-server 
sudo systemctl restart mssql-server.service

Once you've done all the above bits, start the configuration of the SQL Server database, on my case I choose the Microsoft SQL Standard Edition... okay, we don't want to pay for it but at the minute that's the one we need to use to be able to migrate data from Oracle

sudo /opt/mssql/bin/mssql-conf setup

Standard RAID for SuSe 10 running Oracle 11g migration project

 After the configuration is done, run this command to check the SQL server is moment:

systemctl status mssql-server --no-pager

Finally, start the SQL Server Agent as below:

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 
sudo systemctl restart mssql-server

 Do a snapshot on that virtual machine now, then it is time to create a database to receive the data from Oracle as follows (warning! if you do copy/paste to a VM, ensure the symbol | 'pipe' and double quotes " are properly pasted)

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
#The above will export that path to your profile

 If the exported path does not work and you cannot run the sqlcmd command, then visit /opt/mssql-tools/bin and run this command to create a new database:

./sqlcmd -S localhost -U SA -P 'MyPassword123'
1> CREATE DATABASE ORACLE2
1> SELECT Name from sys.Databases
1> GO
After the GO, the 2 above commands will be executed, and a new DB called ORACLE2 created

Let's see now if we can connect to that newly created database from the Windows Management machine

 

7. Connect to the Oracle database

Following the instructions on this Microsoft KB (https://docs.microsoft.com/en-us/sql/ssma/oracle/installing-ssma-for-oracle-oracletosql?view=sql-server-ver15), download and install SSMA for Oracle on the XYZ-WIN10 virtual machine, we'll use this machine to convert the data from Oracle/SuSe to SQL Express/Ubuntu. On that same Windows machine, install the following:

SQL project

Once you have Microsoft Assistant for Oracle installed, launch it and create a new project to migrate to SQL 2017, as highlighted on the above screenshot. Click on the "Connect to Oracle" button and enter the details of the database to logon as below, notice that the username has to be SYSTEM

Specify paramenters for the new connection

 

 

 

After you successfully connect to Oracle, click on the button to "Connect to SQL Server", and ensure that the option to Trust the Certificated is selected

 Connect to SQL Server

--------

orapwd file=orapwdsid password=MyPassword123 force-y

 

 

https://www.theurbanpenguin.com/1013-change-runlevels-and-shutdown-or-reboot-system/

 

 

 

We used to have a massive VM of 2TB running SuSe Linux Enterprise 10 SP3, that was hosted on an Hyper-V cluster

We decommissioned that Hyper-V cluster and moved the SuSe VM to a vSphere environment running 6.0. In this article I'm just going to list the main points that we follow to achieve the completion of this project

 

Reduce the size of the SuSe VM

Personally, I believe that there is something wrong if you have a VM that is bigger than 1TB, this one was hosting 2TB of data which is totally out of order. We acquired an NFS storage server (you can use Nimble, NetApp, EMC, etc) to host those files running on the SuSe, and one that huge drives on the SuSe were empty we needed to detach them from the system without upsetting the kernel. This is what I did:

vi /etc/fstab   //**edit this file and comment out (with a #) the drives that you don't need

 

 

Handy command for the SuSe

ps -ef  //**list of the processes running on the system
ps -ef | grep tomcat
ENABLE ROOT TO LOGIN THROUGH SSH
vi etc/ssh/sshd_config //**edit this file and uncommet the following entry:
#PermitRootLogin yes
AFTER SUSE LINUX ENTERPRISE RESTART:
/usr/local/tomcat/bin# ./startup.sh
/var/log# chwon -R squid:nogroup squid
/var/log# chmod -R 755 squid
rcsquid start
rcsquid status
READ ROOT MAIL
/var/logs# cat root  

 

The oracle - from Hyper-V to VMware

First of all, shutdown the Oracle 11g do as follows:

//**to check status of the listener:
lsnrctl status listener
cat /etc/oratab //**identify the SID of the database
export ORACLE_SID=PROD  //**export the SID, on this example is "PROD"
sqlplus / as sysdba
shutdown immediate
//**to check status of the database do:
select status from v$instance;

 

At the time of running VMware converter for a SuSe 10 machine, follow this guides:

  1. Use VMware Coverter 5.5
  2. Copy the VM to a standalone ESXi host (not vCenter) with SSH enabled
  3. Define a static IP address in the VMware converter section of "Helper VM network configuration"; ensure that this new IP address is different from the static IP assigned to the source VM
  4. Edit the file /etc/udev/rules.d/70-persistent-net.rules and delete the conflicting eth0, renaming then the VMware nic to "eth0" instead of "eth1"
  5. Edit the file /etc/fstab and set " 1 1" at the end of the entry for every /dev/mapper/mpath mounted file, instead of the current "1 2"

 

 

 

Change IP address in CentOS

 

Make Linux a FTP server

If you need to add a new hard drive to your Linux VM to cope with the FTP transfer, follow these instructions:

Install the webmin as described on this link:

rpm -ivh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm
yum update
yum install proftpd

ProFTPD Server is installed in: /usr/libeexec.webmin/proftpd

cp /etc/vsftpd/vsftpd.conf /etc/vsftpd/vsftpd.conf.orig

 

Disable user list at login window:

https://www.centos.org/forums/viewtopic.php?t=54014 

 

References