Oracle XE

From GeilThings

Jump to: navigation, search
Oracle XE
General
Version 11.2.0-1.0
Stable 11.2.0-1.0
Function Database
Provider Oracle
SQL SiSQL
Port 1521
Log File /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log, /u01/app/oracle/diag/tnslsnr/servername/listener/trace/listener.log
Scripts
Start /etc/init.d/oracle-xe start
Stop /etc/init.d/oracle-xe stop
Cli /u01/app/oracle/product/11.2.0/xe/bin/sqlplus

Contents

General

  • Everything here refers to the Oracle XE Database.
  • Getting started with Oracle (sqlplus):
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-intro.html
  • Commands to manage the database, start, stop, deinstall:
http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm
  • Start: /etc/init.d/oracle-xe start
  • Stop: /etc/init.d/oracle-xe stop
  • Force restart:
su - root
/etc/init.d/oracle-xe force-reload

Download

2011-08:

  • Download it using a web browser and sftp the file to the server.
  • I also followed what was mentioned in some forums, but it did not work:
wget --no-check-certificate -vv --post-data="globalId=&redirectUrl=http://download.oracle.com/otn/beta/xe/linux.x64_11gR2_OracleXE.zip&username=myusername&password=mypassword&submit=Continue" https://profile.oracle.com/jsp/reg/loginHandler.jsp
  • The cookies method did not work either, probably too old:
http://laurentschneider.com/wordpress/2006/09/wget-from-otn.html
wget --load-cookies=cookies.txt http://download.oracle.com/otn/linux/oracle10g/xe/10201/oracle-xe-10.2.0.1-1.0.i386.rpm
  • The lynx method was also a failure:
http://www.pythian.com/library/papers_and_books/downloading_from_otn_directly_to_your_database_server/

Installation

10g

# CentOS 5.6 64 Bit
 
# To avoid some errors (see under the section "Errors"),
# install libaio.i386 and glibc.i686.
 
# Following:
# http://db24all.wordpress.com/2011/04/17/oracle-xe-10g-installation-on-the-virtual-image-of-%E2%80%9Copensuse%E2%80%9D-linux-oracle-vm-virtualbox/
# This guy saved my day
 
# Downloaded from the oracle web site using a web browser.
 
# Install:
rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm
## You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
 
/etc/init.d/oracle-xe configure
 
# Note about ports:
# In linux are port numbers < 1024 reserved for the root user.
 
## Oracle Database 10g Express Edition Configuration
## Specify the HTTP port that will be used for Oracle Application Express [8080]:8090
## Specify a port that will be used for the database listener [1521]: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: mypassword
## Confirm the password: mypassword
## Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y
 
# Define the password for the linux user named "oracle".
passwd oracle
su - oracle
 
# Set environment variables for the user "oracle".
ORACLE_BASE=/usr/lib/oracle/xe/app/oracle;
export ORACLE_BASE;
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/server;
export ORACLE_HOME;
ORACLE_SID=XE;
export ORACLE_SID;
PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
export PATH
 
# As root, in the /etc/profile and /root/.bash_profile files, add (without the # characters):
su - root
### ORACLE_BASE=/usr/lib/oracle/xe/app/oracle
### export ORACLE_BASE
 
### ORACLE_HOME=$ORACLE_BASE/product/10.2.0/server
### export ORACLE_HOME
 
### ORACLE_SID=XE
### export ORACLE_SID
 
### PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
### export PATH
 
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
./createdb.sh
 
# Check that oracle daemons are running:
ps aux
...
oracle   ... /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr LISTENER -inherit
oracle   ... xe_pmon_XE
oracle   ... xe_psp0_XE
oracle   ... xe_mman_XE
oracle   ... xe_dbw0_XE
oracle   ... xe_lgwr_XE
oracle   ... xe_ckpt_XE
oracle   ... xe_smon_XE
oracle   ... xe_reco_XE
oracle   ... xe_cjq0_XE
oracle   ... xe_mmon_XE
oracle   ... xe_mmnl_XE
oracle   ... xe_d000_XE
oracle   ... xe_s000_XE
oracle   ... xe_s001_XE
oracle   ... xe_s002_XE
oracle   ... xe_s003_XE
oracle   ... xe_qmnc_XE
oracle   ... xe_q001_XE
oracle   ... xe_q002_XE
...
 
# Check that the oracle listener is working properly:
su - root
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin
./lsnrctl status

Errors while installing Oracle XE 10g

  • Error 1. Trying to run sqlplus: "bad ELF interpreter: No such file or directory"
    • Solution: Install glibc.i686, even for a 64-Bit system
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/
./sqlplus
## "/lib/ld-linux.so.2: bad ELF interpreter: No such file or directory"
 
# Install glibc.i686.
wget http://mirror.centos.org/centos/5/updates/i386/RPMS/glibc-2.5-58.el5_6.4.i686.rpm
rpm -ivh glibc-2.5-58.el5_6.4.i686.rpm
  • Error 2: Trying to run sqlplus: SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
    • Solution: Set ORACLE_HOME
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/
./sqlplus
## Error 6 initializing SQL*Plus
## Message file sp1<lang>.msb not found
## SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
 
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
  • Error 3: Trying to run sqlplus: I cannot authenticate: "ORA-12162: TNS:net service name is incorrectly specified"
    • Solution: Install libaio 386.
Going through the installation log files found in /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/log,
the error is everywhere:
ORA-12547: TNS:lost contact
SP2-0640: Not connected
My libaio: Arch  : x86_64, Version  : 0.3.106
wget http://mirror.centos.org/centos/5/os/i386/CentOS/libaio-0.3.106-5.i386.rpm
rpm -ivh libaio-0.3.106-5.i386.rpm

11g

Installing Oracle XE 11g Server

yum install libaio bc flex unzip
unzip -q oracle-xe-11.2.0-1.0.x86_64.rpm.zip
cd Disk1
rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
# If Error message: "This system does not meet the minimum requirements for swap space..." see below under Errors.
 
/etc/init.d/oracle-xe configure
# App Express Port: 8100 (8080: tomcat)
# Listener Port: 1521
 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
ORACLE_SID=XE
export ORACLE_SID
 
# Add same to /etc/profile:
# ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
# ORACLE_SID=XE
# export ORACLE_HOME ORACLE_SID
 
cd /u01/app/oracle/product/11.2.0/xe/bin
 
# Check.
./lsnrctl status
# Everything looks fine.
 
# sqlplus.
sqlplus sys as sysdba
# Enter password.
 
# Add ORACLE_HOME/bin to the PATH variable in /etc/profile.
PATH=$PATH:$ORACLE_HOME/bin
export PATH

Installing Oracle XE 11g Client

# From http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
# download the files:
# oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
# oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm 
# For installation, see
# http://www.oracle.com/technetwork/articles/technote-php-instant-084410.html
 
rpm -Uvh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm
# Libraries are created under /usr/lib/oracle/11.2/client64/lib.
 
rpm -Uvh oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm
# Headers are created under /usr/include/oracle/11.2/client64.
 
export OCI_INCLUDE_DIR=/usr/include/oracle/11.2/client64/
export OCI_LIB_DIR=/usr/lib/oracle/11.2/client64/lib/
 
# Add also the 2 previous lines to /etc/profile.

Errors while installing Oracle XE 11g

After "rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm", if Error message appears: "This system does not meet the minimum requirements for swap space..."

# For 4 GB swap:
# dd if=/dev/zero of=/swapfile bs=1024 count=4194304
# For 8 GB:
dd if=/dev/zero of=/swapfile bs=1024 count=8388608
mkswap /swapfile
swapon /swapfile
 
# For the swap file to survive a reboot, edit /etc/fstab adding the line:
/swapfile          swap            swap    defaults        0 0

Configuration

Disable access to the Oracle XE web interface

About the Oracle XE web interface:

su - oracle
sqlplus sys as sysdba
 
select all DBMS_XDB.GETHTTPPORT() from dual;
## 8090
# Change the port to 0 to use the database without allowing access via http or ftp,
# essentially disabling the web interface.
SQL> begin
  2  dbms_xdb.sethttpport('0');
  3  end;
  4  /
# Just in case:
# From http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm
# After you install Oracle Database XE Server, its graphical user interface is only available from the local server, not remotely.
# Enable remote access to the web interface with FALSE, disable access with TRUE.  
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(TRUE);
exit;
 
# Restart database
su - root
/etc/init.d/oracle-xe force-reload

Add history to sqlplus

yum install rlwrap
 
# At the end of the file /etc/bashrc, add:
alias sqlplus="rlwrap sqlplus"
 
# reload the file without leaving the shell or login out:
source /etc/bashrc
 
# The equals sign cannot have spaces before or after, this won't work: alias sqlplus ="rlwrap sqlplus"
# The error "-bash: alias: sqlplus: not found" is thrown.

Running

# Lines starting with REM are interpreted as comments by sqlplus.
 
su - oracle
sqlplus sys as sysdba
 
# Oracle XE does not has the "database" concept but the "schemas" concept, 
# essentially the collection of database objects belonging to a user.
# "Show all databases" is then like "show all users".
SELECT USERNAME FROM ALL_USERS;
 
REM Show user
SHOW USER;
 
CREATE TABLE HelloWorld 
(
oraclekey varchar(50) not null primary key, language varchar(50), message_text varchar(255),
msgDate timestamp(8)
);
 
REM Table created.
 
REM Here at Oracle the values are delimited by ' and not by "
REM '
 
INSERT INTO HelloWorld (oraclekey, language, message_text, msgDate) VALUES ( 'java', 'Java', 'Hello World from Java, Tomcat, and Oracle XE', Current_Timestamp );
INSERT INTO HelloWorld (oraclekey, language, message_text, msgDate) VALUES ( 'node.js', 'node.js', 'Hello World from node.js and Oracle XE', Current_Timestamp );
INSERT INTO HelloWorld (oraclekey, language, message_text, msgDate) VALUES ( 'php', 'PHP', 'Hello World from PHP and Oracle XE', Current_Timestamp );
INSERT INTO HelloWorld (oraclekey, language, message_text, msgDate) VALUES ( 'perl', 'Perl', 'Hello World from Perl and Oracle XE', Current_Timestamp );
INSERT INTO HelloWorld (oraclekey, language, message_text, msgDate) VALUES ( 'scala', 'Scala', 'Hello World from Scala, Tomcat, and Oracle XE', Current_Timestamp );
 
REM Each command returns "1 row created".
 
REM Create Read-only user in the Oracle database.
REM From http://www.tek-tips.com/viewthread.cfm?qid=1344022&page=34
 
CREATE USER armesauuser IDENTIFIED BY armesauuserpassword
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;
REM User created.  
GRANT CONNECT, CREATE SESSION to armesauuser;
REM Grant succeeded.
CREATE ROLE readonlyrole;
REM Role created.
GRANT readonlyrole to armesauuser;
REM Grant succeeded;
GRANT SELECT on HelloWorld to readonlyrole;
REM Grant succeeded;
QUIT;
 
# sqlplus commits before exit. The user does not need to enter "commit".
 
# Test Read-only user.
sqlplus armesauuser/armesauuserpassword@XE
 
select * from sys.HelloWorld;
 
quit;

Backup

Upgrade

Uninstall

  • If installed using rpm, uninstall it also using rpm
# Stop the oracle server.
/etc/init.d/oracle-xe stop
 
# Uninstall oracle, remove directory if still there.
rpm -e oracle-xe
rm -rf /usr/lib/oracle/
 
# Remove all references to Oracle (e.g. ORACLE_HOME) in /root/.bash_profile and in /etc/profile,
# check the path variable.
 
# Remove the "oracle" user if still there. "-r" will delete its home directory as well.
userdel -r oracle

Programming Oracle XE


Comments

blog comments powered by Disqus