Tunneling X over SSH on Ubuntu 9.10

“Just a second”, you’re thinking, “I thought this blog was supposed to be about Oracle stuff ?”
This is true…broadly speaking. However, I’ve spent a fair chunk of the last week playing with Ubuntu 9.10 server working up to putting Oracle on it. This particular mini-adventure will come in handy when I come to do the actual install as Oracle uses a graphical interface as it’s main installation tool.

So, this tuneling X over SSH sounds pretty impressive. Tuneling itself sounds very technical, a fact augmented by the inclusion of a TLA in the phrase. And the X just serves to make it sound rather mysterious and exciting.

As is so often the case in such matters, the truth is rather more prosaic. Continue reading

Essaying a sashay with Samba

Just in case either of my kids are reading this, don’t panic. I’ve not suddenly decided to break the habit of a lifetime and indulge in a bit of Dad Dancing. No, this is a vain attempt to improve my geek-cred, rather than my street-cred. As promised in my last post, this is a wander through my adventures in configuring Samba to share files from my shiny new Ubuntu 9.10 Server installation.
Continue reading

Installing Ubuntu Server and SSH setup

It is said that the Devil makes work for idle hands. I’ve got a week off. I know, I’ll have a crack at setting up an Ubuntu server on a desktop PC.
Note to self – I really must get a life. Apparently they can be quite useful.

Right now, however, it’s probably a good idea to set out what I’m trying to achieve here.

This server is just something for me to play around with at home. I don’t need to worry too much about large amounts of traffic.
I want to keep my holiday snaps and my music on it so I can access them from the other machines in my house.

I also want to install Oracle 11g on it at some point, so I have somewhere to play with an up-to-date version of Oracle.
As 11g tends to get a bit sniffy if it’s got a GB or less of physical memory to play with, I need to make sure that the host machine has enough memory to accommodate it.

I’ll post about the Samba setup and Oracle installation separately. For now, I want to concentrate on the initial Server installation and SSH setup. Continue reading

Customising gedit for PL/SQL on Ubuntu 9.10

As I’ve said before, when it comes to doing serious Database programming, I prefer to work in SQL*Plus.

Yes, SQLDeveloper ( and Toad for that matter) are great for looking at stored program units, running ad-hoc queries etc, but I find there’s no substitute for being “close to the metal” when it comes to in-depth or complex programming in PL/SQL.

Apart from anything else, the feedback you get at the prompt is the actual error, and not masked by something the IDE is objecting to.
Line numbering also helps a lot. If the compiler reports an error at line 100, I want to be able to go to line 100 in my code directly, no messing. Text editors tend to do this stuff really well.

At work, being stuck on Windows, I have to rely on the trusty Textpad, which is perfectly adequate.
Fortunately, when I get home, it’s Ubuntu all the way. This means getting to play with another of those unobtrusive Linux gems – gedit. Continue reading

Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11

This is a follow up to my earlier post about SQLDeveloper being moody and suddenly refusing to connect to a database via TNS.
Having had a bit of a dig around, it would seem that this problem is not platform specific and affects Windows in the same way.
At this point, I’d like to say a big “thank you” to Grzegorz Wilczura, who referred me to this article by Sue Harper.
If you’re hitting this problem on Windows, then you may want to follow the instructions there to set up a TNS_ADMIN environment variable.

Remember, this problem had two primary symptoms :-

  1. Empty Network Alias list when defining a TNS connection
  2. When testing an existing TNS connection you get :
    Status: Failure – Test failed : no ocijdbc11 in java.library.path

In Sue’s article, it states that SQLDeveloper looks for a tnsnames.ora in the following places in this order :

  • $HOME/.tnsnames.ora
  • $TNS_ADMIN/tnsnames.ora
  • /etc/tnsnames.ora
  • $ORACLE_HOME/network/admin/tnsnames.ora

Only one of these places is has an absolute path. The rest all rely on environment variables being set. However, when I run SQLDeveloper from the Ubuntu desktop menu, I’m not starting a shell, so my .bashrc doesn’t get executed. Therefore, these variables are not set.
When I setup my first tns connection, I’d just installed sqldeveloper and ran it by executing the shell script ( sqldeveloper.sh) from a Terminal Window. Of course, the $ORACLE_HOME was set in this environment and SQLDeveloper could therefore see the tnsnames.ora in $ORACLE_HOME/network/admin.

All of this means that the cause of the problem is that SQLDeveloper cannot see, or can no longer see, the tnsnames.ora file.

Copying the tnsnames.ora to /etc will fix the problem. However, probably the best solution is to ensure that we’re only referencing one tnsnames.ora and don’t replicate it. That way, we only ever have to change it in one place, should the need arise.

So, the alternative I’ve chosen is to set the $ORACLE_HOME environment variable in sqldeveloper.sh – the script that gets called to start SQLDeveloper.
Start a terminal and go to the SQLDeveloper home directory ( in my case, I installed SQLDeveloper in /opt) :-

cd /opt/sqldeveloper
sudo gedit sqldeveloper.sh

Now amend the file so it looks something like this :

#!/bin/bash
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
cd "`dirname $0`"/sqldeveloper/bin && bash sqldeveloper $*

Now re-start SQLDeveloper from the Ubuntu menu. Remember, this menu item is simply executing the shell script we’ve just changed.
If you have an existing tns connection defined then you can test doing the following :

  1. Right-click the connection and select Properties from the pop-up menu.
  2. This will bring up the New/Select Database Connection window.
  3. Enter the password in the Password field
  4. Hit the test button.

The test should now succeed.

If you haven’t got a TNS connection defined currently, you should now be able to test by setting one up, with no problem.

Playtime…watching DVDs on Ubuntu 9.10

It’s not all sad geeky database type stuff here you know. Sometimes, I just want to kick back and watch a movie.
For some ( apparently legal) reasons, stuff like DVD and Flash doesn’t work on Ubuntu out of the box.
To access a veritable cornucopia of goodies, you need to do the following :

sudo apt-get install ubuntu-restricted-extras
sudo /usr/share/doc/libdvdread4/install-css.sh

Additionally, if you want to be able to access a DVD’s menu, you’ll need the totem-xine package :

sudo apt-get install totem-xine

Full details can be found in the Ubuntu Community Documentation on Restricted Extras – which is how they refer to this stuff.
I thought it was worth a post here because it’s something I stumbled across and, speaking to fellow Ubuntu ingenues it seems that it’s one of those things that isn’t particularly well publicised.

Installing SQLDeveloper 1.5.5 on Ubuntu 9.10

Continuing my adventures in this strange and (mostly) wonderful new world of Ubuntu…

Now I’ve managed to get Oracle XE to behave itself on Ubuntu, it’s time to install SQLDeveloper.

Note – if you’ve stumbled across this post whilst looking for instructions for installing the latest version of SQLDeveloper ( currently version 4), you can find instructions here.

The aim is to :-

1)Install SQLDeveloper so it’s working ( i.e. can connect to my XE database)
2)Setup a Menu item so I don’t have to mess about running a shell script to start it every time.

So, deep breath… Continue reading

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.

Ubuntu 9.10 – On the slippery slope to hardcore geekdom

I’ll admit that years of working with Oracle on a variety of platforms has left me with something of a soft spot for Unix.

More than any other OS, it’s a programmer’s environment. If you’ve ever had to write scripts coping with the verbosity of VMS, or had to come up with something inventive using only the DOS/Windows For Loop construct, I’m sure you’ll agree.

After taking the plunge and converting my desktop to Ubuntu 8.10 about a year ago, I’ve finally gone and dual-booted my laptop in a desperate attempt to escape the clutches of Vista.

On reflection, as Vista is so slow and cumbersome, I should have realised sooner that escaping it’s clutches wouldn’t be that difficult.

Sly dig at Microsoft there. All I need to do now is make some joke about how crap 9i AS was and then talk earnestly about the brilliance of…whatever the latest incarnation of Oracle Application Server is called this week and I can go and get a job with the Oracle Middleware Sales team.

Anyway, enough of this software parochialism, this post is about Linux.

Having a social life ( albeit a not entirely hectic one), I was enough of a wuss to wait to be prompted by the arrival of the latest Ubuntu version on a cover disk, before taking my laptop’s life in my hands and dual booting it.

At this point, it’s probably worth mentioning exactly what I used the laptop for before it got a proper operating system.

Well, not much out of the ordinary is the short answer

  • surfing the web; downloading e-mail; making use of the iPlayer
  • watching the occasional DVD
  • listening to music
  • uploading/downloading music and picture to/from my phone
  • downloading pictures from my camera
  • occasionally uploading maps to my Sat Nav

In terms of development tools, I have Oracle XE and SQL Developer running, together with Apache and PHP. And that’s it.

Before the installation, the main areas of trepidation were :-

  • will Ubuntu work with the wireless card ?
  • Will the DVD player work at all ?
  • What about iPlayer ?

From unhappy experiments with my desktop, I already knew that the Garmin software was a non-starter – even with Wine ( a Windows emulator for Linux) installed.

Fortunately, almost all of these fears proved unfounded.

In not much more than an hour, I was up and running in Ubuntu, connected wirelessly to the broadband router.

The DVD player works fine after doing a quick search on the forums and finding out the relevant packages to install (ubuntu-restricted-extras is the main one).

The BBC now lists Ubuntu as one of the platforms it supports for iPlayer and this works without a hitch.

Transferring files between the camera /phone and the laptop is relatively simple provided you can live without the niceties provided by the software suites that come with them. I must admit that I don’t tend to use these and just treat the devices as I would any other USB storage device. That’s handy really as Ubuntu tends to do the same.

And guess what ? The machine…

  • boots faster in Ubuntu ( 30 secs as opposed to over two minutes)
  • recognises the wireless card and connects to the router immediately ( as opposed to the aeons that Vista takes to even find out what wireless networks are available)
  • shuts down faster than Vista ( Windows has always been the last party guest that just takes ages to leave when all you want to do is shut the door and get in from the cold)

Not only that, but I’ve got my preferred browser and office suite configured and ready to go out of the box.

The bog-standard Gedit text editor recognises file types such as PHP and has syntax highlighting for them built in – more of a Textpad equivalent than Notepad.

Best of all, I can write proper shell scripts again. Hello top row of the keyboard, how I’ve missed you !

I know that Oracle XE runs on Ubuntu 8.10 ( although I need to drop into terminal to start and stop it), and SQLDeveloper should be fairly straightforward.

Some brief experimentation means that I’ve got samba shares on my desktop for my photos and music library that are accessible to the laptop. That was one of those happy occasions where what I feared would be a daunting multi-hour slog through the command line and various help pages turned out to be a 5-minute point-and-click exercise.

Whilst I’m not smitten enough to start referring various Ubuntu versions as “Fluffy Bunny” or whatever annoyingly twee sorbriquet it was given on release, I am now seriously considering a server installation on my other – currently unused and unloved desktop – as a prelude to sticking Oracle 11g on it…to sit alongside a LAMP installation.

Now, that would be some playground.

It’s happening already…by the time “Rampant Rhino” sees the light of day I’ll be wearing socks with my sandals and recounting amusing anecdotes about punch-cards.