Oracle XE Made Easy

(Database 10g Express)


By Virag Sharma  virag123@gmail.com                   VIRAG SHARMA BLOG

Home

Oracle 11g TOP feature for DBA

Oracle 11g Database new feature

VIRAG SHARMA BLOG

Database 10g Express Edition

  1. Introduction
  2. Oracle XE installtion
  3. XE database tablespace and tempfiles
  4. ORACLE XE perfoamnce & Tuning
  5. What all option are enabled on XE
  6. Changing Port
  7. Enable remote http
  8. XE database troubleshooting
  9. Getting ORA-600 error after installtion
  10. Why my XE not start automatically on Linux, why my Linux reboot hanged to starting listener.
  11. My Swap Space is not sufficient ( less then 1 GB )
  12. Reference

Introduction

Oracle XE is free product from oracle for free to develop, deploy, and distribute.

Database XE based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute

Developers, educators, across world and various industries are using Oracle Database XE to easily develop applications. Oracle Database XE is good database for following communicating

  • Educational institutions
  • DBA Training
  • Developer like PHP , XML .NET java
  • Free software Vendor
  • Students

Limitations

  •   1GB memory (max)
  •   4GB disk space (max)
  •   one database (max)

Check Users Love Oracle Database 10g Express Edition!

Oracle XE installtion

Installation Presentation

Oracle XE installtion on Linux

Download software from otn.oracle.com and run following command as root user

# rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm


Preparing... ########################################### [100%]
1:oracle-xe-univ ########################################### [100%]
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.

# rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm


Preparing... ########################################### [100%]
1:oracle-xe-univ ########################################### [100%]
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.

Configuring XE /etc/init.d/oracle-xe configure

# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press <Enter> to accept the defaults.
Ctrl-C will abort.

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]:1525

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 10g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...
Done
Configuring Database...
Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://127.0.0.1:8080/apex"

[root@apps001 ~]#

XE database tablespace and tempfiles

SQL> select df.tablespace_name "Tablespace",
2 totalusedspace "Used MB",
3 (df.totalspace - tu.totalusedspace) "Free MB",
4 df.totalspace "Total MB",
5 round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
6 "Pct. Free"
7 from
8 (select tablespace_name,
9 round(sum(bytes) / 1048576) TotalSpace
10 from dba_data_files
11 group by tablespace_name) df,
12 (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
13 from dba_segments
14 group by tablespace_name) tu
15 where df.tablespace_name = tu.tablespace_name ;

Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ----------- ----------- ----------- ----------
SYSAUX 447 3 450 1
USERS 2 98 100 98
SYSTEM 336 4 340 1
UNDO 12 78 90 87

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------

1 213548 28-MAY-07 3 1 ONLINE READ WRITE
20971520 2560 20971520 8192
C:\ORACLE\ORACLEXE\ORADATA\XE\TEMP.DBF


Enabling Remote HTTP Connection

On SQL Command Line prompt, enter the following command:

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);

Change HTTP port for XE

Default port is 8080 , and you want to change port to say 8090 then On sql prompt run following command

SQL> EXEC DBMS_XDB.SETHTTPPORT(8090);

Now for home page use URL htttp://127.0.0.1:8090/apex

ORACLE XE perfoamnce


Run following command when you start database

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
-----
-- run your sqls
--again run snapshot
-----
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;


Once done run awrrpt.sql from oracle_home\rdbms\admin as sysdba, it will give you details about your system performance in txt/html format

What all option are enabled on XE

SQL> select * from v$option
SQL> /

PARAMETER VALUE
------------------------------------------------------- -----------
Partitioning FALSE
Objects TRUE
Real Application Clusters FALSE
Advanced replication FALSE
Bit-mapped indexes FALSE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery FALSE
Parallel execution FALSE
Parallel load TRUE
Point-in-time tablespace recovery FALSE
Fine-grained access control FALSE
Proxy authentication/authorization TRUE
Change Data Capture FALSE
Plan Stability TRUE
Online Index Build FALSE
Coalesce Index FALSE
Managed Standby FALSE
Materialized view rewrite FALSE
Materialized view warehouse refresh FALSE
Database resource manager FALSE
Spatial FALSE
Visual Information Retrieval FALSE
Export transportable tablespaces FALSE
Transparent Application Failover TRUE
Fast-Start Fault Recovery FALSE
Sample Scan TRUE
Duplexed backups FALSE
Java FALSE
OLAP Window Functions TRUE
Block Media Recovery FALSE
Fine-grained Auditing FALSE
Application Role FALSE
Enterprise User Security FALSE
Oracle Data Guard FALSE
Oracle Label Security FALSE
OLAP FALSE
Table compression FALSE
Join index FALSE
Trial Recovery FALSE
Data Mining FALSE
Online Redefinition FALSE
Streams Capture FALSE
File Mapping FALSE
Block Change Tracking FALSE
Flashback Table FALSE
Flashback Database FALSE
Data Mining Scoring Engine FALSE
Transparent Data Encryption FALSE
Backup Encryption FALSE
Unused Block Compression FALSE

54 rows selected.


XE database troubleshooting


1) check database is up or not "ps -aef |egrep "xe|pmon"
2) check listener is up or not "lsnrctl status"
3) Check/Run "tnsping xe" and sqlplus system/<password>@xe is working or not
4) run "netstat -a" port is in use or not!! .
5) Once http start working "lsnrctl status" will show http entry like

[b](DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
(HOST=hostname)(PORT=8080))
(Presentation=HTTP)(Session=RAW))
[/b]

6) for home page use link http://127.0.0.1:8080/apex
(use port what you configured, Default is 8080)

7) Check sqlnet.ora , some time domain entry create problem for connect string

8) Check alert.log/alert<SID>.log for any error.

Getting ORA-600 error after installtion

Some people get following ORA-600 in alert.log, after install

Errors in file /usr/lib/oracle/xe/app/oracle/admin/XE/udump/xe_ora_6574.trc:
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
USER: terminating instance due to error 600

To over come this issue make sure /etc/host have 127.0.0.1 and host entry like given below

127.0.0.1 localhost.localdomain localhost localhost.domain
xxx.xxx.xxx.xxx hostname hostname.domain

Also ping and verify all the host and IP mentioned in /etc/hosts

ping localhost

ping localhost.domain

ping host

ping host.domain

ping 127.0.0.1

Why my XE not start automatically on Linux, why my Linux reboot hanged to starting listener.

I have installed XE on AS 4 and found it not start automatically even entry is there
in rc2.d , anyway, When checked, also got same error


Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
The open() system call failed for the file /proc/self/exe
Linux Error: 13: Permission denied


I usually start manually.


Login as oracle user , from root

su - oracle
cd /usr/lib/oracle/xe/app/oracle/product/10.2.0./server/bin
bash (or ksh)
. ./oracle_env.sh

sqlplus "/ as sysdba"
shutdown immediate;
startup
exit
lsnrctl start

Then connect to http://127.0.0.1:8080/apex

Looks like there is issue with shell script for starting XE database , let us run manually and check why

# sh +x ./oracle-xe start

/etc/init.d/functions: line 227: `/etc/redhat-lsb/lsb_killproc': not a valid identifier

Open /etc/init.d/oracle-xe and edit it

# vi oracle-xe

# Source fuction library

if [ -f /lib/lsb/init-functions ]

then

# . /lib/lsb/init-functions <- Commaneted this line

echo " " <- Added new line

elif [ -f /etc/init.d/functions ]………..

Now check start and stop is working or not

# sh +x ./oracle-xe start

test

Starting Oracle Database 10g Express Edition Instance.

# sh +x ./oracle-xe stop

test

Shutting down Oracle Database 10g Express Edition Instance.

Stopping Oracle Net Listener.

#

My Swap Space is not sufficient ( less then 1 GB )

Oracle XE installtion need 1 GB swap space , and i have Only 98MB (Approx)
Used following work around to add addition space to swap area

# free
total used free shared buffers cached
Mem: 1001916 210836 791080 0 10840 113700
-/+ buffers/cache: 86296 915620
Swap: 983032 0 983032

# dd if=/dev/zero of=/swapfile bs=1M count=400
400+0 records in
400+0 records out

# ls /swapfile
/swapfile

# ls -l /swapfile
-rw-r--r-- 1 root root 419430400 Jun 4 16:21 /swapfile

# mkswap /swapfile
Setting up swapspace version 1, size = 419426 kB

# swapon /swapfile

# free
total used free shared buffers cached
Mem: 1001916 625012 376904 0 11516 522524
-/+ buffers/cache: 90972 910944
Swap: 1392624 0 1392624


Reference

Oracle Database Express Edition 10g Release 2 (10.2) Documentation Library

Oracle Database 10g Express Edition Tutorial