Turning off Password expiration on Oracle XE and Apex

It is a strange time in the UK.
I’m not referring to England being 2-0 up after 2 tests in the Ashes (something that happens about as often as a Briton winnng the Men’s Singles at Wimbledon), nor the fact that a Briton has won the Tour de France for the second year running.
Stranger even than that is the bright yellow ball in the sky which has replaced the traditional warm rain of the British Summer.

This phenomenon has had a strange effect on the cat. Her animal instincts obviously alerted by the unfamiliar change in the climate, she currently spends almost all of her time out in the garden.
Unfortunately, she seems to have decided to treat said garden as something of a litter tray. This will necessitate something of a mine-sweeping exercise before I next mow the lawn.

It’s frustrating when you’re anxious to start something but then hit an unpleasant roadblock.
For example, you may have decided to have a play with that Oracle XE/Apex installation on your laptop that you haven’t used for a little while. However, when you come to connect, you realise that you can’t remember the password.

Health Warning – Before I go any further, I should point out that doing this is not something I’d recommend on anything other than a system that’s being used simply as a playground for you to experiment and which contains no sensitive data.

Continue reading

ORA-00845: MEMORY_TARGET error installing Oracle XE on Mint and Ubuntu

It was my turn to “cook” tonight. Deb was quite emphatic on that point. Continuing the fine and long-held tradition, sustained through generations of British manhood, I duly trudged down to the chippy.
Fish and chips, with that unique and exquisite smell of malt vinegar. Never mind all those fancy aftershaves, for us Brits it’s Sarsons…pour homme.
Except that, when I get to the shop, I find that I have no cash on me and they don’t accept cards.
No, not even “Chip and Pin”.
Eventually, the hunter gatherer returns ( having made a short detour to an ATM) to be greeted by the now ravenous family. Honestly, this cooking lark is all go.

It could be worse I suppose. I mean, the recipe for Victoria Sponge doesn’t suddenly stop working for no readily apparent reason, unlike, to take a random example, installing Oracle XE on Mint and Ubuntu.

When I wrote the original post, all was working perfectly. Mint 11, Oracle XE 11g, job done.
However, Mint 13 ( or Maya, if you prefer) is a bit of a different story. So, for that matter is Ubuntu 11.10 and above.

At this point, I’d like to say a big thanks to Gil Standen, whose comment on the original post was spot on in pin-pointing and solving this issue.

So, if you’ve found your way here having been frustrated in your installation attempts by this pesky error, what follows is an explanation of the issue, together with the steps that I used to resolve it on Mint 13. Continue reading

Installing Oracle 11gXE on Mint and Ubuntu

Things have been a bit hectic lately. What with putting in a new kitchen, being insanely busy at work, and trying not to come out with embarrassing sheep jokes, I’ve ended up with quite a long list of things to do blog-wise.
Top of the list, until now, was installing the long-awaited Oracle 11gXE Release 2 onto one of my Linux machines.
Yes, the free version of Oracle’s RDBMS has finally had an upgrade from 10g and I really want to get my hands on it and have a good nose around.
As well as being based on the latest release of the RDBMS, the Express Edition has had one or two other improvements added. Maybe the most significant of these is that the limit for the amount of user data that XE can hold has been increased from 4GB to 11GB.
What I’m going to do here is :

  • Go through the package conversion process
  • Install the database using steps applicable both to Mint and Ubuntu ( and any other Debian based distro)
  • Apply some finishing touches so that the menu items work as intended
  • Along the way, we’ll find out just why Oracle can’t speak English (and lots of other languages), where Mint has hidden the .bashrc, and how Aliens can be friendly.

Because I’m trying to cover both distros in this post, the installation process will be done entirely on the command line. Don’t worry, it’s not as bad as it sounds.

But first…a small morsel of Linux history. Debian, the distro upon which both Mint and Ubuntu are based, was named after a Deb. I had to mention that as this will cause my beloved to think that I’m writing about her ( again), and thus give me enough time to finish writing this !

What are we waiting for then ? Let’s get going. Continue reading

Oracle, bind variables and SQL Injection – Keeping out unwanted guests

My son, Michael ( yes, it is the only name I can spell), is currently following in his father’s footsteps and studying Computer Science.
As is only natural, he does occasionally have the urge to rebel against all that his parents hold dear. In his case he’s rejected the path of light and Linux and has become … a Microsoft Certified Professional. Oh the shame. Where did I go wrong ?
All of which links, if somewhat tenuously, to the subject at hand. When he took his first steps into the world of programming, we had a look at PHP ( as part of a LAMP set-up, naturally).
In one of the introductory manuals, we came across an example of how to authenticate web users against a database.
The author was clearly trying to introduce various language concepts and would certainly not claim that his example was intended for production use. However, with a bit of tweaking for use against an Oracle database, it does offer a very clear illustration one area of the potential vulnerabilities of web applications to SQL Injection attacks. It also offers the opportunity to illustrate a major benefit of using bind variables in queries against Oracle – i.e. protection against SQL Injection.

I know that a fair few people who stumble across this site are new to Oracle and want to play around with Oracle XE. These people are also usually pretty experienced in other technologies (hi Wayne, hope you’re still enjoying all that sunshine).

So, the purpose of this post is to :

  • Illustrate the way in-line SQL statements can be injected
  • Show how this can be countered in an Oracle database by use of bind variables
  • Have a look at letting Oracle handle user authentication
  • Celebrate the visionary genius of Messrs Young, Young and Johnson. “For Those About to Rock” was not merely an album of raucous Blues-based Heavy Metal, but a prophecy about the potential pitfalls of Web Application development.
  • Oh, and give you the chance to laugh at my PHP prowess ( or lack thereof)

Continue reading

Oracle XE – Speeding up Startup and getting Nofications on the Desktop in Ubuntu

Oh the tension. 2-0 up at half-time. Visions of last-day drama and an eleventh-hour escape floating tantilisingly before my eyes. Then, grim reality. A second-half collapse and the prize is snatched away once more. So, this is what it must be like to be an Arsenal fan.
I know that they say misery loves company, but it’s not really any consolation. Yes, my beloved West Ham have been relegated from the Premiership.

In an effort to rouse myself from the resultant depression, I decided to have a look at addressing one of those minor annoyances that I’m always intending to get around to but somehow never quite do. In this case, it’s how to get confirmation that my Oracle XE database has started before trying to connect to it ( and that it’s shut down before I turn off my computer). Continue reading

Why setting Default Tablespace on the Database affects some Oracle Internal Users

Just following on from my previous post, I’ve done a bit of investigation to find out why issuing ALTER DATABASE DEFAULT TABLESPACE tsname affects some Oracle Internal users.
Remember, in the case of my standard Oracle XE installation, the offending users were DIP, MDSYS and TSMSYS.

Apparently, this command will only apply to users that have been created without a default tablespace being specified. This is how Oracle creates these users when it creates the XE database.

So, once you’ve changed the default tablespace on the database in this way, it’s advisable to go through the Oracle internal users and set the default tablespace back to what it was previously.

Installing Oracle XE on Ubuntu 9.10

Right, time to install XE on Ubuntu 9.10. Here goes….

Before we go any further, I think I should point out that I’m running the 32-bit desktop version of Ubuntu – Ubuntu Server is for another machine on another day.

My reasons for installing Oracle are simply to have a database to play around with, so I don’t have to worry about multiple concurrent connections, or even setting up the Oracle client on machines I want to be able to connect from. I’ll be connecting from the machine I’m running the database on.

Update - since I wrote this post, Oracle have released 11gXE. You can find installation instructions for this newer version for Mint and Ubuntu here.

Something else worth mentioning is that I’ve got a bit of “previous” on this score.

I installed XE on Ubuntu 8.10 a while back, and hit a few issues with being able to run the menu options that get added to the Applications menu.

Something else I noticed about the process was the need to run lots of commands that were doing stuff I wasn’t necessarily up to speed on. This meant lots of referencing various man pages to make sure I wasn’t doing anything rash.

So this time, I’m going to try and do as much as possible through GUI tools…and try to get everything working properly.

One of the big pluses in getting Oracle to run on Linux is that you can then mess around doing clever things with shell scripts. However, interacting with the database via a script does require that you’ve configured everything to allow SQL*Plus to be invoked from the command line.

Bearing all this in mind, I’m approaching the task this time with the following objectives :-

  • Get Oracle XE up and running with minimal command-line intervention
  • Get the menus working from the Ubuntu desktop
  • Get SQL*Plus up and running from the shell

Getting the software

Right, first thing we need is the software itself. Rather than following the usual routine of going to the oracle site and downloading the software, I thought I’d see if I could take advantage of the Linux software package management utilities.

We’re in luck. Oracle does indeed provide a repository containing a variety of goodies, including XE.

So, what we’ll need to do first is to add the repository to the package repository database.

In 9.10, there’s this new utility called the Ubuntu Software Centre, so I thought I’d start there.

  1. From the Ubuntu Applications menu, select Ubuntu Software Centre
  2. From the Edit menu, select Software sources…At this point you’ll be prompted for your password
  3. In the Software Sources window, select the Other Software Tab.
  4. Click Add

In the pop-up window, type in the following as the APT line :-

deb http://oss.oracle.com/debian unstable main non-free

and click Add Source

You should now see this repository in the list.

  1. Check the box next to the repository and click Close
  2. Close the Ubuntu Software Centre window

If you want to double-check that the repository is now added, just open a Terminal session and type :-

cat /etc/apt/sources.list

You should see the new repository as the last entry in this list.

Unfortunately, the Ubuntu Software Centre doesn’t recognise this repository , so in order to actually obtain the software, we’ll need to use Synaptic Package Manager
To actually download the software from the repository :-

  1. From the Ubuntu System menu select Administration and then Synaptic Package Manager. Enter your password when prompted.
  2. In the Quick search field, type oracle-xe
  3. Remember, I’m going to be running the database and connecting to it all from one machine, so I don’t need the oracle-xe-client package. So, as I just want the Western European Edition, I just select the oracle-xe package.
    NOTE – if you want a language other than English, I think you need to choose the oracle-xe-universal package instead.
  4. Once you’ve marked the appropriate package, hit the Apply button.
  5. Synaptic now downloads two packages – libaio and oracle-xe.

Once the download is complete, we’re ready to install the software.

Installation

At this point, we really do need to go to the command line.

In a new Terminal session type the following :-

sudo /etc/init.d/oracle-xe configure
You will now be prompted for 4 bits of information :-

  • the Oracle Application Express (APEX) http port ( defaults to 8080)
  • the port for the database (TNS) listener ( 1521)
  • the password to be used for the SYS and SYSTEM database accounts
  • whether or not you want the database to start on boot (y)

NOTE – I’ve made a couple of changes to the default – I already have Apache installed so I need to use a different port. Also, I want the database to start when I choose, rather than automatically.

The TNS listener port can safely be set to 1521 ( or 1526) as these ports are known as defaults for the TNS listener so no other automatically configured software on your system should be using it.

The session looks something like this.

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

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:8081/apex"

To check that the database is up and running, try accessing your database home page via a web browser.
If the page comes up, you’re up and running.

Getting the Menu Options to Work

You’ll also notice that the Ubuntu Applications Menu now has an entry called Oracle Database 10g Express Edition which has a number of options in it’s sub-menu…none of which will work right now.

In order to run these options, you’ll need to make yourself a member of the os DBA group that’s been created as part of the installation.

To do this :-

  1. Go to the Ubuntu System menu and select Administration and then Users and Groups
  2. Click the button with the picture of the keyring and enter your password
  3. Go to the dba group and then click Properties
  4. Click the checkbox next to your user
  5. Exit the Users and Groups tool.

Hey presto, the menu options will now work.

To test this :-

  1. Go to Applications / Oracle Database 10g Express Edition and click Stop Database This will stop the tns listener, shutdown the database, and the http listener.
  2. To verify this, try accessing the Database Home Page again. This time you should get a Page Not Found error.
  3. Now go back to the menu and this time click on Start Database. Give it a few seconds then try to access the Database Home Page again.
    This time, it should come up. If it doesn’t, it’s possible that the database is still starting up, so give it a bit more time and then try again.

Getting SQL*Plus to run from the shell

There is a menu option in the Oracle Database 10g Express Edition menu that lets you connect to the database via SQL*Plus.

All you need to do is click on this then at the SQL prompt type :-

connect system/password_you_set_during_install@XE
What I’m trying to do here is a bit different. I want to be able to invoke SQL*Plus from a Terminal session ( or a batch script).

Once again, we have to go to the command line to achieve this.

Remember, Oracle “sort of” supports Ubuntu as an OS for XE, but only sort of. The oracle supplied shell script that sets the environment variables to allow it’s command line utilities ( including SQL*Plus) to run, isn’t especially happy in the bash shell. So, you need to make a minor change to one line to get it to execute.

The file in question is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

Open this file in whatever editor takes your fancy and change the line :-

NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`

To :-

NLS_LANG=`. $ORACLE_HOME/bin/nls_lang.sh`

Assuming you’re using the default Ubuntu setup ( bash is your shell), you can either copy the contents of this file wholesale into the .bashrc file in your home directory, or you can point to it in this file. Either way, after saving the change to .bashrc the next time you start a terminal session, you’ll find three new environment variables set on startup. To check this, open a Terminal session and type

echo $ORACLE_HOME

This should return /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/

echo $ORACLE_SID

This should return XE

echo $NLS_LANG

This will return the character set for your database setup ( in my case ENGLISH_UNITED KINGDOM.AL32UTF8 )

And finally, you can now invoke SQL*Plus from the command line :-

$ sqlplus uid/pwd

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 7 01:16:38 2009

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

Connected to:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

Conclusion…and an acknowledgement

Oracle XE is now up and running on Ubuntu. Now I can do some real damage :-D

My initial ( mostly) successful installation attempt on 8.10 was done with reference to the guide at http://mediakey.dk/~cc/ubuntu-howto-install-oracle/

If you prefer your installations done on the command line, this is worth checking out, but I hope I’ve brought this a bit more up to date.

One final note of caution – Oracle XE is pretty much delivered “as is”. Whilst it is wonderfully useful for playing around with an Oracle database, it does have some limitations. Not least of these is that, unlike it’s big brother (currently Oracle 11g Release 2), Oracle don’t produce quarterly security patches for it. This means that you should think carefully before you go and use it in a production environment.

I was lucky enough to attend a presentation by Alexander Kornbrust at UKOUG in 2006 in which he graphically demonstrated these limitations. You can find his presentation here.

Edit – Cannot reach Database Home Page after restart

Hmmm….I thought that was too easy !
After re-starting my computer, I found I could no longer get the Database Home Page coming up.
After much cursing and puzzlement, I’ve tracked down the problem to the listener.ora file.

This file is used to control the configuration of the TNS Listener which process traffic to APEX, which sits inside the database.

To fix this annoying glitch…

Open up a Terminal session

cd $ORACLE_HOME/network/admin
sudo gedit listener.ora

change the line

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

To

# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))

Effectively you are commenting out this line.

Save and exit.

Go to the Start Database option in the Oracle Menu (under applications).

You need to give it a couple of minutes because the TNS listener gets started after the database does.

You should now be able to get to the Home Page.

Please let me know if this works for you.

UPDATE – I’ve now written an Installation Troubleshooting Guide, which you may want to check out if things still aren’t working as they should.
There’s also an introduction to PL/SQL here if you’re interested.