Installing Oracle XE 11gR2 on a Centos Cloud based VPS

This post describes the steps taken to install Oracle XE 11gr2 on a Centos based Virtual Private Server hosted by eapps.com.

These steps are necessary due to restrictions related to the limited swap space offered by the VPS.

So, these were the steps (executed as root):

1. Create a group and name it oinstall.
groupadd oinstal

2. Create a group and name it dba.
groupadd dba

3. Create a new user (oracle) with oinstall as its primary group. Also modify the .bash_profile to set an Oracle env as shown below.

useradd -g oinstall -G dba oracle

[oracle@cloud ~]$ more .bash_profile
# .bash_profile

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

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export PATH

4. Download the installation (a zip file containing the rpm). Unzip the downloaded file. A Disk1 folder is created.

5. Change to Disk1 and run the following:

rpm2cpio oracle-xe-11.2.0-1.0.x86_64.rpm > oracle-xe-11.2.0-1.0.x86_64.cpio

cpio -idv < oracle-xe-11.2.0-1.0.x86_64.cpio

This will result in the creation of the following folders:
[root@cloud Disk1]# ll
total 888924
drwxr-xr-x 3 root root      4096 Jan 11 09:46 etc
drwxr-xr-x 2 root root      4096 Aug 29  2011 response
drwxr-xr-x 3 root root      4096 Jan 11 09:46 u01
drwxrwxr-x 2 root root      4096 Aug 29  2011 upgrade
drwxr-xr-x 3 root root      4096 Jan 11 09:47 usr

6. Replace template values for the memory target:
find ./u01 -exec grep "%memory_target" '{}' \; -print
This gives two files:
memory_target=%memory_target%
./u01/app/oracle/product/11.2.0/xe/config/scripts/initXETemp.ora
memory_target=%memory_target%
./u01/app/oracle/product/11.2.0/xe/config/scripts/init.ora

. . . . . . . 
###########################################
# Miscellaneous
###########################################
. . . . . 
memory_target=268435456
. . . . . 

7. Copy/move folders to the appropriate locations:
cp -R ./usr /
cp ./etc/init.d/oracle-xe /etc/init.d/

8. Move the ./u01 folder to the root of the filesystem and assign ownership to the oracle user
mv ./u01/ /
chown -R oracle:oinstall /u01

9. Give execute permission to all configuration shell scripts
chmod +x /u01/app/oracle/product/11.2.0/xe/config/scripts/*.sh

10. Edit the  following scripts to replace #!/bin/sh with #!/bin/bash
/u01/app/oracle/product/11.2.0/xe/config/scripts/XE.sh
/u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh

11. Create the oradata directory:
cd /u01/app/oracle/
mkdir oradata
chown oracle:oinstall oradata

12. Make /etc/init.d/oracle-xe executable:
chmod +x /etc/init.d/oracle-xe

13. Run the configuration scripts
/etc/init.d/oracle-xe configure

14. Check status:
/etc/init.d/oracle-xe status

If the command above shows a BLOCKED status, then chances are that the creation of the control files has failed. Check the log.xml file in the diag directory of the XE rdbms.

In this case, create the control file manually:

1. Open a new shell as the oracle user

2. Connect vi sqlplus:
sqlplus / as sysdba

3. Create the control file yourself:
SQL > Create controlfile reuse set database "XE"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'/u01/app/oracle/oradata/XE/system.dbf',
'/u01/app/oracle/oradata/XE/undotbs1.dbf',
'/u01/app/oracle/oradata/XE/sysaux.dbf',
'/u01/app/oracle/oradata/XE/users.dbf'
LOGFILE
GROUP 1 SIZE 51200K,
GROUP 2 SIZE 51200K,
RESETLOGS;
. . .  
SQL> shutdown immediate
. . . 
SQL> startup mount
. . . . . 
SQL> alter database open resetlogs;

At this point you should able to connect remotely (if the 1521 port is open).

Recover the source code of a dropped Oracle Package

This was a life saver!!!

I have accidentally dropped a package.

Fortunately, I discovered it almost immediately and I was able to recover its source by using the following statements:

select text
from all_source as of timestamp sysdate-1/24
where name 'my_package_name' and type='PACKAGE'
and owner=;

select text
from all_source as of timestamp sysdate-1/24
where name 'my_package_name' and type='PACKAGE BODY'
and owner=;

Obviously, the next step was to commit it in SVN :-)