Migrate Red Hat Spacewalk Server from Oracle XE to Oracle 11gR2 backend database

We have been running Spacewalk server to manage package deployment to our CentOS 4 and 5 systems.

The initial deployment was done using the free Oracle XE as the backend database. This database works great for small deployments where you are synchronizing packages for a single release (say CentOS 5 x86_64). Start adding 3rd party repositories (EPEL, RPMFusion, ELRepo) and the size of the database can grow to approach the Oracle XE 4GB limit.

We hit the barrier after approximately 6 months of operation. Spacewalk will let you know by sending "traceback" errors via email (sometimes spamming you with them :-)

These are the notes that I took while migrating from Oracle XE to Oracle 11gR2.

Caveat: I'm a Linux system administrator, not an Oracle DBA, so please view the Oracle DBA steps in that light (feedback is appreciated).

I used this Spacewalk mailing list post as a reference.

Current Configuration

• Operating System: CentOS 5.4 i386
• Spacewalk Version: 0.8
• Hardware: ESXi 4 virtual guest running on Dell blade M600
• Spacewalk Channels: 4 base, 36 child
• Total Packages: 41,986

Prerequisites

1. Install prerequisite packages (rlwrap is handy for adding a history functionality to sqlplus, among other things)

$ sudo yum install compat-libstdc++-33 libaio-devel sysstat unixODBC unixODBC-devel pdksh compat-db rlwrap

2. The oracle user already exists, here's how it was previously created

$ sudo /usr/sbin/groupadd -g 400 oinstall
$ sudo /usr/sbin/groupadd -g 401 asmdba
$ sudo /usr/sbin/groupadd -g 402 asmadmin
$ sudo /usr/sbin/useradd -g oinstall -G dba,asmdba,asmadmin -u 101 oracle

3. Increase the hard limit for max open file descriptors from 1024 to 65536 for the user oracle

$ sudo vi /etc/security/limits.conf

oracle soft nproc  2047
oracle hard nproc  16384
oracle soft nofile 1024
oracle hard nofile 65536

4. Add the following Oracle recommended OS kernel parameters to the sysctl.conf file

$ sudo vi /etc/sysctl.conf

# Begin Oracle 11gR2
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
# End Oracle 11gR2

5. Load the kernel parameters into the running kernel

$ sudo /sbin/sysctl -p /etc/sysctl.conf

6. Edit pam.d login parameters

$ sudo vi /etc/pam.d/login

# Added for Oracle 11gR2
session    required     pam_limits.so

7. Create the oracle directories

$ sudo mkdir /opt/oracle
$ sudo mkdir /opt/oraInventory
$ sudo chown -R oracle:dba /opt/oracle
$ sudo chown -R oracle:oinstall /opt/oraInventory

8. Make some changes to oracle .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 ORACLE_UNQNAME=orcl
ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/112
#ORACLE_SID=ORCL
ORACLE_SID=orcl
LD_LIBRARY_PATH=$ORACLE_HOME/lib
#PATH=$PATH:$ORACLE_HOME/bin
PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH

alias rlsqlpus='rlwrap sqlplus'
alias rlrman='rlwrap rman'

9. Remove the environment script that loads the XE settings

$ sudo rm /etc/profile.d/oracle_env.sh

Create a Database Dump

1. Disable automatic start of Oracle XE on boot up

$ sudo /sbin/chkconfig oracle-xe off

2. Shut down the spacewalk services (this can take a while, also shuts down Oracle XE

$ sudo /usr/sbin/rhn-satellite stop
Shutting down rhn-satellite...
Stopping RHN Taskomatic...
Stopped RHN Taskomatic.
Stopping cobbler daemon:                                   [  OK  ]
Stopping rhn-search...
Stopped rhn-search.
Stopping MonitoringScout ...  
[ OK ]
Stopping Monitoring ...  
[ OK ]
Stopping httpd:                                            [  OK  ]
Stopping tomcat5:                                          [  OK  ]
Shutting down osa-dispatcher:                              [FAILED]
Shutting down Oracle Database 10g Express Edition Instance.
Stopping Oracle Net Listener.

Terminating jabberd processes ...
Stopping router:                                           [  OK  ]
Stopping sm:                                               [  OK  ]
Stopping c2s:                                              [  OK  ]
Stopping s2s:                                              [  OK  ]
Done.

3. Start Oracle XE back up so that the database can be backed up / dumped

$ sudo /sbin/service oracle-xe start
Starting Oracle Net Listener.
Starting Oracle Database 10g Express Edition Instance.

4. Backup the database

$ sudo su - oracle
$ /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/backup.sh

Backup in progress...
Backup of the database succeeded.
Log file is at /usr/lib/oracle/xe/oxe_backup_current.log.

5. Create a dump of the database for importation into Oracle 11

$ sudo su - oracle
$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
$ exp spacewalk/spacewalk owner=spacewalk consistent=y statistics=none file=spacewalk.oracleXE.dmp log=spacewalk.oracleXE.log

Export: Release 10.2.0.1.0 - Production on Mon Apr 19 17:09:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

...

. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Install Oracle 11gR2

I used this page as a reference for installing Oracle 11gR2:
http://ivan.kartik.sk/oracle/install_ora11gR2_elinux.html

1. Stop Oracle XE and Spacewalk (if either are still running)
2. Install Oracle 11gR2 by logging in with X forwarding as the user oracle

$ cd /home/oracle/oracle-11gR2/database
$ ./runInstaller

• Create and configure a database
• Server Class
• Single instance database installation
• Typical Install
• English
• Oracle Base: /opt/oracle
• Oracle Home (Software Location): /opt/oracle/112
• Storage Type: File System
• Database File Location: /opt/oracle/oradata
• Database edition: Enterprise Edition
• OSDBA Group: dba
• Global database name: orcl.mydom
• Admin Password: a good strong password for SYSTEM
• Inventory Dir: /opt/oraInventory
• Inv Group Name: oinstall
3. Install displays a summary

Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/orcl

Database Information
  Global Database Name:      orcl.mydom
  System Identifier(SID):    orcl
  Server Parm File:          /opt/oracle/112/dbs/spfileorcl.ora
  
The Database Control URL is https://spacewalk.mydom:1158/em

Management Repository has been palced in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /opt/oracle/112/spacewalk.mydom_orcl/sysman/config/emkey.ora.  Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

Note: All database accounts except SYS, SYSTEM, DBSNMP, and SYSMAN are locked. Select the Password Management button to view a complete list of locked accounts or to manage database accounts (except DBSNMP and SYSMAN). From the Password Management window, unlock only the accounts you will use.

4. Run the following two scripts as root
• /opt/oraInventory/orainstRoot.sh
• /opt/oracle/112/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/oracle/112

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

5. Add the EMDC port to the firewall

# Oracle 11gR2 Enterprise Manager Database Control
-A RH-Firewall-1-INPUT -s 192.168.1.101 -m state --state NEW -m tcp -p tcp --dport 1158 -j ACCEPT
# End Oracle 11gR2

6. dit /etc/oratab to set the orcl database to start when dbstart is executed

XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N
orcl:/opt/oracle/112:Y

7. Create the Oracle db and listener startup init script
• /etc/init.d/oracle

#!/bin/bash
#
# oracle Init file for starting and stopping
# Oracle Database. Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Oracle Database startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/opt/oracle/112"

case "$1" in
start)
  echo -n $"Starting Oracle Listener:"
  su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
  echo -n $"Starting Oracle DB:"
  su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
  echo "OK"
  ;;
stop)
  echo -n $"Stopping Oracle DB:"
  su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
  echo -n $"Stopping Oracle Listener:"
  su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
  echo "OK"
  ;;
*)
  echo $"Usage: $0 {start|stop}"
esac

• /etc/init.d/oraemctl

#!/bin/bash
#
# oraemctl Starting and stopping Oracle Enterprise Manager Database Control.
# Script is valid for 10g and 11g versions.
#
# chkconfig: 35 80 30
# description: Enterprise Manager DB Control startup script

# Source function library.

. /etc/rc.d/init.d/functions

ORACLE_OWNER="oracle"
ORACLE_HOME="/opt/oracle/112"

case "$1" in
start)
  echo -n $"Starting Oracle EM DB Console:"
  su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl start dbconsole"
  echo "OK"
  ;;
stop)
  echo -n $"Stopping Oracle EM DB Console:"
  su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/emctl stop dbconsole"
  echo "OK"
  ;;
*)
  echo $"Usage: $0 {start|stop}"
esac

8. Set permissions on the scripts and add the services to the runlevels

$ sudo chmod 750 /etc/init.d/oracle
$ sudo chkconfig --add oracle --level 0356
$ sudo chmod 750 /etc/init.d/oraemctl
$ sudo chkconfig --add oraemctl --level 0356

Populate the Database

1. Copy the pfile generated during Oracle install

$ sudo su - oracle
$ cp /opt/oracle/admin/orcl/pfile/init.ora.3212010101939 /opt/oracle/112/dbs/initorcl.ora

2. Start the orcl database instance and create the Oracle spacewalk user

$ sudo su - oracle
$ . oraenv

ORACLE_SID = [ORCL] ? orcl
ORACLE_HOME = [/home/oracle] ? /opt/oracle/112
The Oracle base for ORACLE_HOME=/opt/oracle/112 is /opt/oracle

$ startup

SQL> startup
ORACLE instance started.

Total System Global Area 1052233728 bytes
Fixed Size      2220032 bytes
Variable Size    608174080 bytes
Database Buffers   436207616 bytes
Redo Buffers      5632000 bytes
Database mounted.
Database opened.


$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 20 17:16:29 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create user spacewalk identified by XXXXXXXX default tablespace users;

3. Grant the necessary priviledges

grant create session to spacewalk;
grant alter session to spacewalk;
grant unlimited tablespace to spacewalk;
grant create table to spacewalk;
grant create synonym to spacewalk;
grant create view to spacewalk;
grant create sequence to spacewalk;
grant create procedure to spacewalk;
grant create trigger to spacewalk;
grant create type to spacewalk;

4. Import the dumped database

$ sudo su - oracle
$ export NLS_LANG=english.AL32UTF8

$ imp \'/ as sysdba\' fromuser=spacewalk touser=spacewalk file=spacewalk.oracleXE.dmp log=spacewalk.oracleXE.imp.log ignore=y

Import: Release 11.2.0.1.0 - Production on Wed Apr 21 15:16:31 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SPACEWALK, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SPACEWALK's objects into SPACEWALK
. . importing table                     "DEMO_LOG"          0 rows imported
. . importing table            "HTMLDB_PLAN_TABLE"          2 rows imported
. . importing table                  "PXTSESSIONS"          0 rows imported
. . importing table                    "RHNACTION"        140 rows imported

...

. importing SPACEWALK's objects into SPACEWALK
About to enable constraints...
Import terminated successfully with warnings.

5. Review the output and any warnings
6. Remove execute permissions from the oracle-xe starter script to prevent rhn-satellite script from starting it. The rhn-satellite script first checks for an executable 'oracle' script, followed by an executable 'oracle-xe' script, if it finds both, it sets the DB_SERVICE to 'oracle-xe'

$ sudo chmod -x /etc/init.d/oracle-xe

7. Edit the /etc/rhn/rhn.conf file and replace any occurrence of 'xe' with 'orcl'

default_db = spacewalk/XXXXXXXX@orcl
db_name = orcl
hibernate.connection.url=jdbc:oracle:thin:@spacewalk.mydom:1521:orcl

8. Edit the /etc/rhn/cluster.ini and replace 'xe' with 'orcl'

LocalConfig.0.dbname=orcl

Start the Red Hat Satellite service

Start up the Spacewalk services to see if all of the components come up successfully.

$ sudo /usr/sbin/rhn-satellite start
Starting rhn-satellite...
Initializing jabberd processes ...
Starting router:                                           [  OK  ]
Starting sm:                                               [  OK  ]
Starting c2s:                                              [  OK  ]
Starting s2s:                                              [  OK  ]
Starting Oracle DB:Processing Database instance "orcl": log file /opt/oracle/112/startup.log
OK
Starting osa-dispatcher:                                   [  OK  ]
Starting tomcat5: /usr/bin/rebuild-jar-repository: error: Could not find xml-commons-apis Java extension for this JVM
/usr/bin/rebuild-jar-repository: error: Some detected jars were not found for this jvm
                                                           [  OK  ]
Starting httpd:                                            [  OK  ]
Starting Monitoring ...  
[ OK ]
Starting MonitoringScout ...  
[ OK ]
Starting rhn-search...
Starting cobbler daemon:                                   [  OK  ]
SERVING!
Starting RHN Taskomatic...
Done.