Wednesday, April 28, 2010

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)
  2. $ sudo yum install compat-libstdc++-33 libaio-devel sysstat unixODBC unixODBC-devel pdksh compat-db rlwrap
  3. The oracle user already exists, here's how it was previously created
  4. $ 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
  5. Increase the hard limit for max open file descriptors from 1024 to 65536 for the user oracle
  6. $ sudo vi /etc/security/limits.conf
    
    oracle soft nproc  2047
    oracle hard nproc  16384
    oracle soft nofile 1024
    oracle hard nofile 65536
  7. Add the following Oracle recommended OS kernel parameters to the sysctl.conf file
  8. $ 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
  9. Load the kernel parameters into the running kernel
  10. $ sudo /sbin/sysctl -p /etc/sysctl.conf
  11. Edit pam.d login parameters
  12. $ sudo vi /etc/pam.d/login
    
    # Added for Oracle 11gR2
    session    required     pam_limits.so
  13. Create the oracle directories
  14. $ sudo mkdir /opt/oracle
    $ sudo mkdir /opt/oraInventory
    $ sudo chown -R oracle:dba /opt/oracle
    $ sudo chown -R oracle:oinstall /opt/oraInventory
  15. Make some changes to oracle .bash_profile
  16. # .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'
  17. Remove the environment script that loads the XE settings
  18. $ sudo rm /etc/profile.d/oracle_env.sh

Create a Database Dump

  1. Disable automatic start of Oracle XE on boot up
  2. $ sudo /sbin/chkconfig oracle-xe off
  3. Shut down the spacewalk services (this can take a while, also shuts down Oracle XE
  4. $ 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.
  5. Start Oracle XE back up so that the database can be backed up / dumped
  6. $ sudo /sbin/service oracle-xe start
    Starting Oracle Net Listener.
    Starting Oracle Database 10g Express Edition Instance.
  7. Backup the database
  8. $ 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.
  9. Create a dump of the database for importation into Oracle 11
  10. $ 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
  3. $ 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
  4. Install displays a summary
  5. 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.
    
  6. 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.
  7. Add the EMDC port to the firewall
  8. # 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
  9. dit /etc/oratab to set the orcl database to start when dbstart is executed
  10. XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N
    orcl:/opt/oracle/112:Y
  11. 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
  12. Set permissions on the scripts and add the services to the runlevels
  13. $ 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
  2. $ sudo su - oracle
    $ cp /opt/oracle/admin/orcl/pfile/init.ora.3212010101939 /opt/oracle/112/dbs/initorcl.ora
  3. Start the orcl database instance and create the Oracle spacewalk user
  4. $ 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;
  5. Grant the necessary priviledges
  6. 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;
  7. Import the dumped database
  8. $ 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.
  9. Review the output and any warnings
  10. 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'
  11. $ sudo chmod -x /etc/init.d/oracle-xe
  12. Edit the /etc/rhn/rhn.conf file and replace any occurrence of 'xe' with 'orcl'
  13. default_db = spacewalk/XXXXXXXX@orcl
    db_name = orcl
    hibernate.connection.url=jdbc:oracle:thin:@spacewalk.mydom:1521:orcl
  14. Edit the /etc/rhn/cluster.ini and replace 'xe' with 'orcl'
  15. 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.

7 comments:

ifel said...

Thanks for excellent manual.

However, before starting migration of my spacewalk, I'd like to ask you, if everything works well since you migrated from XE? Did you get any other benefits other than remove DB size limit?

Thanks

Unknown said...

Howdy, Yes, following the migration everything is still functioning. The DB size limit was the primary reason for the migration. With OracleXE I was to the point where compacting the database wouldn't even work to get it below the limit.

At some point I'd like to take advantage of using a standby database on another server to allow for automatic block repair.

I haven't upgraded to Spacewalk 1.0 yet, but I believe some folks on the mailling list have who use Oracle 11gR2.

I plan to add a backup section to this entry at some point. For now I'm performing 'cold' backups each weekend.

ifel said...

Thanks again for the excellent manual, it's really helpful. I managed to switch my Spacewalk backend from Oracle XE to Oracle 11g following this manual.

Unknown said...

Your welcome, I'm glad you found it helpful :-)

Anonymous said...

I used this as well and it was very helpful. With spacewalk 1.0 I also had to edit /etc/tnsnames.ora and change
xe =
(SID = xe)

to
orcl =
(SID = orcl)

James said...

Thanks for the howto! I thought I'd add this as it had me seriously stumped for a while...

After moving the database (but not soon enough after for me to realise it was the cause) - the MONITORING done by nocpulse stopped working.

I was able to run the probes by hand just fine, but any probes I added to systems after moving the db resulting in them only ever showing the state as PENDING... same with doing the scout config push - it would sit there and never update...

so to cut a long story short I tracked down the cause of this to the NOCpulse.ini config file, there were two variables that were still set to XE - so basically I had to do:

NOCpulse-ini --group cf_db --set-param=name --new-value=orcl

NOCpulse-ini --group cs_db --set-param=name --new-value=orcl

then I did a "rhn-satellite restart" and bam - when I refreshed the page in spacewalk *all* of the probes in the status list went from PENDING to OK.

:)
unless I missed something really obvious or did something stupid it might be a good idea to add those two commands to the HOWTO - since it doesn't seem to update the monitor probe config in its current form.

cheers!

Anonymous said...

Hi
Thanks for detailed steps.
I used this as well.

But after moving 11gr2 enterprise edition, we are seeing system tab query taking about 6 seconds.
I gathered stats, turned on query caching.. still it did not improve that much.

Anyone experienced this please.

Jay