Home
Oracle 11g TOP feature for
DBA
Oracle 11g Database new feature
VIRAG SHARMA BLOG
Database 10g Express Edition
- Introduction
- Oracle XE
installtion
- XE database tablespace and tempfiles
- ORACLE XE perfoamnce & Tuning
- What all option are enabled on XE
- Changing Port
- Enable remote http
- XE database troubleshooting
- Getting ORA-600
error after installtion
- Why my XE not start
automatically on Linux, why my Linux reboot hanged to starting listener.
- My Swap Space is not sufficient ( less then 1 GB
)
- 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