Installing Oracle 11g Database Express on Ubuntu 13.10

Oracle 11g Database XE is a free edition of oracle database. XE uses only a single CPU and only one instance with the maximum memory of 1GB. The maximum user data in Oracle Database XE is 11GB.

$ sudo apt-get install alien libaio1

$ unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
$ cd Disk1
$ sudo alien --scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm
$ sudo dpkg --install oracle-xe-11.2.0-1.0.x86_64.deb

It creates oracle user and installs oracle binaries in /u01/app/oracle

Add oracle user to sudo group.
$ sudo adduser oracle sudo

Create password for oracle.
$ sudo passwd oracle

On Ubuntu, /run/shm is mounted and /dev/shm is a link to it. But /dev/shm should be mounted to avoid ORA-00845: MEMORY_TARGET not supported on this system.

$ sudo vi /etc/rc2.d/S01shm-mount

#! /bin/sh
case "$1" in
       start)
                rm /dev/shm 2>/dev/null
                mkdir /dev/shm 2>/dev/null
                mount -t tmpfs shmfs -o size=2048m /dev/shm
                ;;
       *)
                echo error
                exit 1
                ;;
esac

$ sudo chmod 755 /etc/rc2.d/S01shm-mount

$ sudo mkdir /var/lock/subsys

Change the path for awk in /etc/init.d/oracle-xe

if [ -z "$AWK" ]; then AWK=/bin/awk; fi
to
if [ -z "$AWK" ]; then AWK=/usr/bin/awk; fi

Create XE database.
$ su - oracle
$ sudo /etc/init.d/oracle-xe configure

Press to accept the defaults
Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n

Starting Oracle Net Listener...Done

Configuring database...Done

Starting Oracle Database 11g Express Edition instance...Done

Installation completed successfully.

You can find the database creation logs in $ORACLE_HOME/config/log

The password for the INTERNAL and ADMIN Oracle Application Express user accounts is initially the same as the SYS and SYSTEM user accounts.

For Bash shell, enter the environment variables into the .bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$ORACLE_HOME/bin:$PATH

To start listener and database as oracle user.
$ su - oracle
$ lsnrctl start
$ sqlplus / as sysdba
SQL> startup

To stop database and listener as oracle user.
$ su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate
$ lsnrctl stop

Migrating the database to ASM Disk Group

If Block Change Tracking is configured, disable it.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Set CONTROL_FILES to the ASM locations and start the instance.

SQL> ALTER SYSTEM SET CONTROL_FILES='+DATADG','+FRADG' SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

Restore controlfile into the new ASM location.
RMAN> CONNECT TARGET
RMAN> RESTORE CONTROLFILE FROM '<old_controlfile_path>';

Backup the database into the ASM disk group.
RMAN> ALTER DABASE MOUNT;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATADG';

Switch all datafiles and tempfiles to the new ASM location.

RMAN> SWITCH DATABASE TO COPY;

Backup the read only tablespaces into the ASM disk group.
SQL> ALTER TABLESPACE <tablespace_name> OFFLINE;
RMAN> BACKUP AS COPY TABLESPACE <tablespace_name> FORMAT '+DATADG';
RMAN> SWITCH TABLESPACE <tablespace_name> TO COPY;
SQL> ALTER TABLESPACE <tablespace_name> ONLINE;

RMAN> RUN {
          SET NEWNAME FOR TEMPFILE 1 TO '+DATADG';
          ...
          SWITCH TEMPFILE ALL;
          }


Open the database.

RMAN> ALTER DATABASE OPEN;

Drop online redo log groups and re-create them in ASM disk group.

SQL> select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('+DATADG','+FRADG') SIZE 500M;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM CHECKPOINT;

Repeat the above step for other logfile groups.

Set archive log destination to the ASM location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'location=+FRADG' SCOPE=BOTH;

Relocate spfile to an ASM disk group.

SQL> CREATE PFILE='$ORACLE_HOME/dbs/initORCL.ora' FROM spfile;
SQL> CREATE SPFILE='+DATADG/ORCL/spfileORCL.ora' FROM PFILE='$ORACLE_HOME/dbs/initORCL.ora';

Shutdown database.
SQL> SHUTDOWN IMMEDIATE

Set ASM environment and modify the oracle restart configuration for spfile.
$ srvctl modify database -d ORCL -p '+DATADG/ORCL/spfileORCL.ora'