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.

Monday, April 26, 2010

iPhone 3G Stopped Syncing with iTunes "unable to load provider data"

The other day, my iPhone 3G and iTunes (9.1) decided to stop communicating.

Since Apple refuses to release a Linux version of iTunes, I keep it synced with my Windows 7 x86_64 system. The other day iTunes started popping up a message
"unable to load provider data from sync services"

There are a number of suggested remedies for this error, from removing / reinstalling iTunes, downgrading versions, deleting the SyncServices directory, disabling startup items etc...

Here's what worked for me, renaming C:\windows\SysWOW64\sqlite3.dll

Now, for the debugging process that finally produced the pointer to sqlite3.dll:

First, I searched the web for the error displayed by iTunes. This is either a generic error that covers a range of failures, or it's horribly worded. The search didn't help in my case, although other users reported success using the suggestions.

Next, I dug around until I found the iTunes log files. Perhaps there's a way to view them within iTunes, but I located them under C:\Users\MyId\AppData\Roaming\Apple Computer\Logs

Look for the log with the most recent time stamp and open in your favorite text editor (Geany or Notepad++ are good choices).

I spent time debugging the errors listed at the bottom of the log file to no avail. Those messages were similar to these:
[4368 @ Mon Apr 26 20:05:48 2010] [ASL ASL] windir=C:\Windows
[4368 @ Mon Apr 26 20:05:48 2010] [(unknown facility) iTunes.exe] _receive_message: Could not securely receive message size: SSL_ERROR_ZERO_RETURN
[4368 @ Mon Apr 26 20:05:48 2010] [(unknown facility) iTunes.exe] AMDeviceStopSession: Could not stop session with device: kAMDInvalidResponseError
[4368 @ Mon Apr 26 20:05:48 2010] [(unknown facility) iTunes.exe] _send_message: Could not securely send message size 282: SSL_ERROR_SYSCALL errno (File exists)

[4368 @ Mon Apr 26 20:06:00 2010] [(unknown facility) iTunes.exe] receive_message: Could not receive secure message: -1
[4368 @ Mon Apr 26 20:06:00 2010] [(unknown facility) iTunes.exe] _read_thread: Could not receive message

Then I started reading back in the log file until I found this line:
[728 @ Mon Apr 26 20:05:48 2010] [_ISDVLog AppleMobileDeviceHelper.exe] SyncServer is unavailable: timed out trying to connect

This got me digging around in Windows Services applet (services.msc) looking for a service that controls Apple Mobile Device Helper. The service was started, I stopped it, restarted iTunes and plugged in the phone. A new message appeared almost immediately indicating that the iPhone can't sync without the Mobile Device service. I started it, resynced and was back to the original error.

The files for this service are located under C:\Program Files (x86)\Common Files\Apple\Mobile Device Support

I navigated to that directory and found an executable named AppleSyncPref.exe

Hmm, perhaps my sync prefs got corrupted? I double click that executable and get a message box with the following error:
"The procedure entry point sqlite3_file_control could not be located in  the dynamic link library SQLite3.dll"

Interesting! A search for this error revealed this page on Apple Support message board. In particular, user 'johnporcaro' posted this short helpful comment:
On my HP Pavilion, Windows 7, deleted sqlite3.dll c:\Windows\SysWOW64. WORKED!

Rather than delete the dll, I renamed it to ~sqlite3.dll, restarted iTunes and sync worked right away.

Hope this helps other frustrated iPhone synch'rs!