oracle xe 설치후 휴지인스턴스로 시작하지 못할때

Frits Hoogland Weblog
Linux, oracle and other technical stuff…
--------------------------------------------------------------------------------
10.2.0.1 Annoying message “Memory Notification: Library Cache Object loaded into SGA” ?Oracle XE on ubuntu (debian) - first impressions
Oracle recently shipped the production version of Oracle XE, the free version of Oracle’s 10g release 2 database. This is an investigation from a DBA’s point of view. Is it comparable to a ‘normal’ 10g database, or is it something completely different?

Oracle Technet Link for more information and free download: Oracle XE Home

For linux, oracle ships the default gzipped cpio for the normal database, but a rpm (in the beta version), and a rpm and a deb (!!) file for the XE production version. I use ubuntu on my testing machine, so I am very pleased I can use a deb file (because ubuntu uses the debian package manager), and do not have to use ‘alien’ to convert the rpm to deb.

Each package manager checks the environment to see if it meets the requirements. the oracle XE package checks the amount of swapspace quite drastic. I have 512M, and a swapspace of 1G, but it needed 1008 M. I have gotten around this by adding some filesystem swap:

root@bedrock:~# dd if=/dev/zero of=/extraswap bs=1k count=1024000
root@bedrock:~# mkswap /extraswap
root@bedrock:~# swapon /extraswap
root@bedrock:~# swapon -s
Filename Type Size Used Priority
/dev/mapper/vg00-swap partition 1048568 138096 -1
/extraswap file 1023992 0 -4

Well, with this in mind, the install should work:

root@bedrock:~# dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb
(Reading database ... 70694 files and directories currently installed.)
Unpacking oracle-xe (from oracle-xe_10.2.0.1-1.0_i386.deb) ...
Setting up oracle-xe (10.2.0.1-1.0) ...
Executing Post-install steps...

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

root@bedrock:~#

This is easy! The software is installed, we only need to configure the database now, in order to use it!

root@bedrock:~# /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 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]:

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

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”

My first question is: what do we have installed now?

Well, it appears we have a running database, together with listener. Just that! The application (formerly known as ‘HTMLDB’, now it is called ‘application express’) runs entirely in the database:

oracle@bedrock:~$ ps -ef | grep oracle
oracle 20226 20225 0 13:13 pts/6 00:00:00 -su
oracle 22384 1 0 14:51 ? 00:00:00 /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr LISTENER -inherit
oracle 22515 1 0 14:53 ? 00:00:00 xe_pmon_XE
oracle 22517 1 0 14:53 ? 00:00:00 xe_psp0_XE
oracle 22519 1 0 14:53 ? 00:00:00 xe_mman_XE
oracle 22521 1 0 14:53 ? 00:00:00 xe_dbw0_XE
oracle 22523 1 0 14:53 ? 00:00:00 xe_lgwr_XE
oracle 22525 1 0 14:53 ? 00:00:00 xe_ckpt_XE
oracle 22527 1 0 14:53 ? 00:00:00 xe_smon_XE
oracle 22529 1 0 14:53 ? 00:00:00 xe_reco_XE
oracle 22531 1 0 14:53 ? 00:00:00 xe_cjq0_XE
oracle 22533 1 0 14:53 ? 00:00:00 xe_mmon_XE
oracle 22535 1 0 14:53 ? 00:00:00 xe_mmnl_XE
oracle 22537 1 0 14:53 ? 00:00:00 xe_d000_XE
oracle 22539 1 3 14:53 ? 00:00:05 xe_s000_XE
oracle 22541 1 1 14:53 ? 00:00:02 xe_s001_XE
oracle 22543 1 0 14:53 ? 00:00:01 xe_s002_XE
oracle 22545 1 0 14:53 ? 00:00:00 xe_s003_XE
oracle 22550 1 0 14:53 ? 00:00:00 xe_qmnc_XE
oracle 22579 1 0 14:54 ? 00:00:00 xe_q000_XE
oracle 22581 1 0 14:54 ? 00:00:00 xe_q001_XE
oracle 22622 20226 0 14:56 pts/6 00:00:00 ps -ef
oracle 22623 20226 0 14:56 pts/6 00:00:00 grep oracle

Where is the software installed?

If we examine the startup scripts, or list the contents of the oracle XE package, we see the software gets installed in /usr/lib/oracle, just as many other debian packages. That’s the debian default. I can live with that.

What’s the oracle home?

The ORACLE_HOME is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server. That’s hidden quite far away from the root.

And how about the database of oracle XE?

The oracle XE database is called ‘XE’. That’s the db_name and the instance_name. The database is startup with a spfile, which is located in $ORACLE_HOME/dbs. The controlfile is located in the directory with the datafiles, which is: /usr/lib/oracle/xe/oradata/XE. The online redologfiles are located in: /usr/lib/oracle/xe/app/flash_recovery_area/XE/onlinelog and are called o1_mf_20onfoz2_.log and o1_mf_20onfnc0_.log.

Besides the fact that only one database can be run with XE (starting up a second instance results in: ORA-44410: XE edition single instance violation error), I still would love to see all files related to an oracle instance be kept in a single place. This eases administration, makes transportability easy, and makes backup and restore very much easier. Now the files are quite scattered among the oracle tree.

It is a bad idea to have the spfile (or init.ora file) (which is critical for starting up a database, it *must* be present) in the oracle software tree. I strongly advise to have a symlink in the dbs directory, instead of the real file.

The online redologfiles are reasonable sized for small installations without any batch ever. For normal database use (yes, I know, this is express, and NOT a normal database) I would start at a size of 200M. Also the number of redolog groups is quite small (2). Online redologfiles are *critical* to database transaction performance, and therefore need to be easily accessible for a DBA and need to have a reasonable names. The current names (o1_mf_20onfoz2_.log and o1_mf_20onfnc0_.log) are weird, and the place is odd to (somewhere in the flash_recovery_area). I would suggest placing the online redologfiles somewhere close to the datafiles, but on a separate filesystem. The online redologfile names are randomly choosen during the install.

How is the default database sized?

oracle@bedrock:~$ export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
oracle@bedrock:~$ export PATH=$PATH:$ORACLE_HOME/bin
oracle@bedrock:~$ export ORACLE_SID=XE
oracle@bedrock:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 5 15:20:54 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>show parameter sga_max_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 140M
SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 41216K

Well, it is small, but the memory usage can be altered to 1G memory, according to the documentation.

Can we break it?

In essence, XE is a normal 10gr2 database, just limited. So all means to break it is also applicable to non-XE oracle databases! I very easily could get an unstartable database. Look at the following activity:

oracle@bedrock:~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 5 15:24:51 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shut
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00439: feature not enabled: Real Application Clusters

This completely deadlocks the normal way to alter the spfile. Even a startup nomount can not be issued, because the database can not get to the nomount stage. The workaround is quite simple, though:

oracle@bedrock:~$ cd $ORACLE_HOME/dbs
oracle@bedrock:~/app/oracle/product/10.2.0/server/dbs$ strings spfileXE.ora | grep -v cluster_database > initXE.ora
oracle@bedrock:~/app/oracle/product/10.2.0/server/dbs$ rm spfileXE.ora
oracle@bedrock:~/app/oracle/product/10.2.0/server/dbs$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 5 16:31:44 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 146800640 bytes
Fixed Size 1257668 bytes
Variable Size 67112764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL>

This entry was posted on Sunday, March 5th, 2006 at 5:07 pm and is filed under Oracle XE. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

13 Responses to “Oracle XE on ubuntu (debian) - first impressions”
Ruud Schilders Says:

March 6th, 2006 at 4:32 pm
Nice and clear review.
I will try installing and running it too, just out of curiosity

Howard J. Rogers Says:

March 7th, 2006 at 6:25 am
Yikes. Please don’t go around telling people to ’strings’ their spfile! That is not how you recover from the act of stuffing your spfile. The correct approach is to export the spfile to a plain vanilla init.ora (which requires no instance), edit the init.ora, and then export back to being an spfile. That is:

SQL> create pfile from spfile;
SQL> host;
# vi $ORACLE_HOME/dbs/initSID.ora
# exit
SQL> create spfile from pfile;
SQL> startup

ORACLE_HOME/dbs is the entirely *correct* location for these configuration files, and I’d advise people not to get into the habit of symlinking to elsewhere as you suggest, because that approach won’t work on (whisper it quietly) Windows -and you ought, wherever possible, to do things in as cross-platform a way as possible.

If you must store your spfile somewhere non-standard, leave behind an initSID.ora in the ORACLE_HOME/dbs directory that contains one line: SPFILE=/somewhere/else/spfileSID.ora

I am dubious about XE, I must say, but it is good to have an rpm/deb install of an Oracle product on Linux at last, and it’s also good to know that non-Red Hatish platforms are finally catered for.

Administrator Says:

March 7th, 2006 at 10:11 am
Howard, I understand your reluctance about using strings. In reality there is no real difference between using ’strings’ and ‘create pfile from spfile’, except that you are using sqlplus instead of a linux/unix executable.

About cross-platform using oracle: I see too much difference between the windows platform and the unix/linux platform. Oracle on windows requires a total different approach in administration (drives instead of mountpoints, no symlinks, services, difficult scripting, absence of a full-blown shell, rdesktop/terminal server, database directory instead of dbs directory, debugging problems on windows is problematic because of the absence of tools like strace and gdb/adb/dbx/etc, painful te detect usage patterns of processes, oracle organized in threads instead of process which makes usage even hard to detect, etc)

This is the reason we keep a strict seperation between oracle on unix/linux and oracle on windows.

Yuri van Buren Says:

March 28th, 2006 at 6:49 pm
Nice testing.

One comment on the redolog files: o1_mf_20onfoz2_.log
These are Oracle managed files.

From 10.x oracle stores these in the flash_recovery_area.
Indeed we like to have more sensible names.

Can you add redologs and drop the two with the OMF names?

Have you tested the maxsize of USERS data ( Oracle says a customer may use XE up to 4Gb of User data, is this checked?).

Oracle says XE runs only on one CPU? Is this checked?

Administrator Says:

March 28th, 2006 at 7:13 pm
I haven’t done any further testing yet.

It should be possible to recreate the redologfiles with more sensible names (and on a sensible place). I can not imagine this is restricted.

The documentation (http://download-uk.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm#BABIECJA) quite technically explains what the limits are, and how they are enforced.

Bonifacius Says:

April 7th, 2006 at 4:40 pm
Great article. I am just sad I dont know how to reply properly, though, since I want to show my appreciation like many other.

skynyrd Says:

April 27th, 2006 at 3:34 pm
Congratulations for the article.

Im strugling with Oracle XE on SUSE LInux. Let me tell you why.

I first installed oracle XE on my machine with a SUSE 10 OS and everything went smoothly. The database is working like a charm.

Then I did exactly the same instalation on my friends machine (in the same network) and it didnt work!

Conecting to the web interface I got:
connection refused

and in SQLPLUS I got:
ORA-01034: ORACLE not avaliable
ORA-27101: shared memory realm does not exist
Linux error: 2: No such file or directory

After almost going crazy, we figured out that, if we disconected the machine from the network, and rebooted the machine, it started working!

Well, I asumed oracle did that for making sure that there is only one instance in the network.

But here is something strange: If I disconected my machine from the network and connected my friends, it was supose to work. Becouse I’ll be running only one instance, right?

Well, it doesnt.
Cant understand why.

What happens is that, while the machine is out of the network, the oracle xe works fine. I can connect to the web interface. SQLPLUS without any problem.

Once I plug the network back to the machine… ORACLE falls!

In SQLPLUS I can connect, but the instance is turned to idle.

I try to run

startup pfile=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/init.ora

and it says SHARED POOS SIZE is to small.

I set it to a larger size and I got a ORA-00600 error…

Maybe you could help me to understand.

Regards

Administrator Says:

April 27th, 2006 at 6:57 pm
The ORA-27101 means the listener configuration is not correct, or the database is not functioning. (it actually means you are pointed to a shared memory location where no database exists)

You seem to be using a pfile. Default, the XE database got a spfile. Can you try to use the one which came with the installation?

skynyrd Says:

April 27th, 2006 at 7:07 pm
problem solved:

I just changed my IP setting to manual insted of using a DHCP server

Thanks anyway!

Administrator Says:

April 28th, 2006 at 7:35 am
Your database should start, regardless of the IP setting. The listener is the networking component.

Rodrigo Says:

May 17th, 2006 at 12:29 am
Hi, i’ve a question, where is the web administrator tool, because i was installed the oracle-xe-universal in a debian machine but i don’t know where is that tool, i try to http://localhost:8080/apex but this page not found in my server :S please help me

Rodrigo Says:

May 17th, 2006 at 1:06 am
sorry, but the port is 5500; we need another configuration? by example in apache?, the display error is: conection refused…. not connect whit site in localhost. thank for u help

Marcelo Says:

May 17th, 2006 at 3:37 am
i have installed oracle on debian (unstable), the steps will be completed succesfully, but, when insert the url of web administration ( in this case http://localhost:5500/apex/ ) don’t run the aplication on the browser, don’t know the error because php and apache it’s run nice, but are not conected whit de web interface.
thanks for you help

Leave a Reply
Name (required)

Mail (will not be published) (required)