It’s that time of year. Slay bells ringing, children singing…and the UKOUG Conference.
This year, I was lucky to get along to attend the last day in the company of my good friend Alan.
I love going to the Conference. You get the chance to see lots of great presentations about all sorts of things in the Oracle world.
Takeaways from this year? Well, apart from the stress-ball and the cuddly Rhino ( yes, we did have a wander through the exhibition hall as well), I learned quite a bit about Application Express.
Just in case they’re struggling for an angle for APEX in the Oracle marketing department, how about :
“Application Express – Forms 3.0 for the Internet Age”
I suppose I’d better do some explaining fairly quickly before I am taken to task by any APEX aficionados who happen to be reading.
Back in the good old days, when I still had hair, Forms 3 was the character based interface for the Oracle database. A major advance on Forms 2.3, you were able to code actual PL/SQL right into the triggers. Of course, everything ran on the server back then. Forms, the database ( we don’t talk about SQL*Reportwriter…ever !)
APEX has certain similarities to it’s ancestor. The code is stored in the database itself and you can write PL/SQL in it. Of course, it is also “web-aware”. It could easily be thought of as a UI for SQL and PL/SQL…without all that mucking about with Java.
Enough of this Oracle Tech naval gazing. The point of this post is that, if you’ve downloaded Oracle 11g XE, you will have APEX4.0 included. Due to the tiresome reluctance of software vendors to use major release numbers, you may have been under the misapprehension that APEX 4.1 was just a minor tweak. The truth is a rather different.
APEX is maturing rapidly. So, if you’re running XE 11g on a Debian OS ( or even 10g XE), you may very well be interested in getting the latest version of APEX to have a play with…
NOTE – I ran this installation on 11g XE running on Mint.
I’ve tried to highlight any differences you may get when installing on 10gXE, but I haven’t actually done the installation on this database version.
Pre-Installation Checks
There are a few bits and pieces to check here :
- shared pool size
- Oracle XDB version
- PL/SQL Web Toolkit version
Shared Pool Size
The shared pool size needs to be at least 100 MB. Shouln’t be a problem. In SQL*Plus you can check this by :
show parameter shared_pool_size
Alternatively, you can just run this query :
SELECT value FROM v$parameter WHERE name='shared_pool_size'; VALUE -------------------------------------------------------------------------------- 0
Hmmm, not quite so straight forward then.
When this parameter is set to 0 then Oracle automatically determines the size of the shared pool.
If you’re running on a 64-bit OS then this should ( according to the documentation) equate to 128 MB. If you’re on a 32-bit OS however, this will default to 64MB.
If you need to change this parameter, then you can simply :
ALTER SYSTEM set shared_pool_size=100M;
If you do need to make this change then it’s probably best to stop and re-start the database at this point to make sure that the additional memory is utilized.
Oracle XDB
The Oracle XDB should be there by default on XE.
Just to check :
SELECT comp_name, version, status FROM dba_registry WHERE comp_id = 'XDB';
Provided you get a row back from this query where the status is VALID, you’re good to go.
PL/SQL Web Toolkit
You need to have version 10.1.2.0.6 or later. Once again, this should already be there on XE. If you want to check then :
SELECT owa_util.get_version FROM dual;
On 11gXE you should get version 10.1.2.0.8.
On 10g XE you’ll probably be on version 10.1.2.0.4.
The APEX 4.1 download does come with files to install version 10.1.2.0.6 and you should be able to run this upgrade by means of the owainst.sql script which will be in the apex/owa directory after you’ve unzipped the download.
If you are going to do this, then I’d suggest you do a full backup of the database beforehand because (a) it’s just good practice and (b) I’ve no idea if this will do what it claims without error on 10gXE.
In fact, even if everything is fine at this point, it’s still a good idea to backup your database before you run the installation… unless you don’t mind losing what you have on there already.
Download Apex 4.1
Next step is to download the Apex4.1 zip file from OTN.
NOTE – if I’m starting to bore you at this point and you’re tempted to follow the “official” installation instructions, the ones for XE are a lot more concise and less confusing than the full-blown APEX installation guide ( which is here).
Once you’ve downloaded the file – apex_4.1_en.zip to give it it’s full name ( I’ve just downloaded the English Language version), you need to copy it somewhere sensible.
In my case, I did the following :
cp apex_4.1_en.zip /u01/app/oracle
Wherever you choose to drop the file, you need to make sure that there are no spaces in the path. Otherwise, the script to install the icons isn’t very happy and you end up with a blank screen…er..so I’ve heard. I mean, I’ve got this friend who tried it. No obviously, I wasn’t dumb enough to do that myself. Ahem.
Anyway, next step is to unzip the file. To save lots of messing about with file permissions and such like, I just switched to the oracle user :
cd /u01/app/oracle sudo su oracle sudo unzip apex_4.1_en.zip cd apex
That last cd was to the newly created apex directory. From this point on, this is where we’ll be running the apex upgrade scripts themselves.
There is one more bit of admin before we get to that, but as the rest of this stuff happens on the database, we can connect now.
At last – the installation
You need to connect to Oracle as SYS as SYSDBA. If you’re the oracle user on the OS then – after making sure that your $ORACLE_SID is set to XE, you should simply be able to do :
sqlplus / as sysdba
If you want to be a bit more boring and conventional ( and haven’t su’d to oracle) then :
sqlplus sys as sysdba@xe
Now, whenever I’m in a multi-database environment, I do like to make sure that I’ve connected to the database I think I have. This makes it so much easier to avoid those hilarious situations where you mistakenly drop a production database or similar.
Anyway, if you’re feeling equally paranoid then :
SELECT name FROM v$database;
This will either set your mind at ease ( in my case by returning ‘XE’) or cause you to quit the SQL session with unseemly haste.
Once we’re on the correct database as SYS then we first need to unlock the APEX_PUBLIC_USER:
ALTER USER apex_public_user ACCOUNT UNLOCK;
Next, we need to find the default and temporary tablespaces for the APEX user in the database ( it should be SYSAUX and TEMP respectively) :
SELECT default_tablespace, temporary_tablespace FROM dba_users WHERE username = 'APEX_040000';
NOTE – if you’re running this on 10g XE with APEX 2 installed ( the version that 10gXE shipped with) then the username will be FLOWS_020100. There will also be a FLOWS_FILES user and you will need to make a note of the default tablespace for this user.
Now we can get on with running the scripts.
First up is apexins.sql, which takes three arguments :
- default tablespace of the APEX user (usually SYSAUX)
- the same as the first argument, unless you have the FLOWS_FILES user, in which case it’s the default tablespace of the FLOWS_FILES user( this is also SYSAUX by default)
- the APEX user’s temporary tablespace (usually TEMP)
- a virtual directory for the APEX images ( I used “/i/” like it says in the installation notes)
So, assuming your setup is like the one I’m using :
@apexins.sql SYSAUX SYSAUX TEMP /i/
You’ll be pleased to know that this script creates a logfile in the apex directory with a name in the format installYYYY-MM-DD HH24:MI:SS.log.
You’ll be slightly less pleased to know that it’s a bit big (over 700K). Yes, it doesn’t sound that huge…until you have to wade through it.
Anyway, this script takes a little while to run, especially on my asthmatic, ageing laptop with a whopping 1GB RAM and 2GHz Celeron Processor.
Eventually, I got the cheery message :
“Now beginning upgrade. This will take several minutes”
This did cause me to wonder vaguely what on earth the script had been doing for the previous 25 minutes.
Finally, the script completed and logged me off.
Now to load the images – by means of apxldimg.sql.
This script accepts one parameter – the directory you unzipped apex into ( in my case, /u01/app/oracle).
Reconnect to the database as SYS as SYSDBA and run the script
@apxldimg.sql /u01/app/oracle
The final step is to set the password for the APEX ADMIN user :
@apxchpwd.sql
Enter the new password when prompted (it’s hidden so you won’t see anything when you type it in).
You’ll be asked to reset the password as soon as you use it for the first time so it doesn’t need to conform to the databases password complexity rules at this stage.
Post Installation steps
If you now click on the link on your desktop to the database start page, you may get a bit of a shock.
Instead of the start page, you’ll get the rather unfriendly “Error – ERR-1014 Application not found”.
Don’t worry, you haven’t missed a step ( necessarily). We’ll fix this in a minute.
For now, point your browser at http://localhost:8080/apex/apex_admin.
You will now be presented with a login screen.
Enter admin as the username and the password you’ve just set for the admin user.
At this point, you’ll be asked to set a new password, which will have to conform to the password complexity rules ( unless you’ve disabled them) – i.e. at least one uppercase, and one punctuation character. The password will also need to be a minimum of 8 characters.
Once the password change has been processed, you’ll be asked to connect again using the new password.
You should now see :
Fixing the desktop link
When you install 11gXE, a link to the APEX home page is automatically created on your desktop.
The link calls a script called gettingstarted.sh in $ORACLE_HOME/config/scripts.
In order to fix the link, you need to edit this file.
The line :
/usr/bin/$ihttp://localhost:8080/apex/f?p=4950
needs to change to :
/usr/bin/$ihttp://localhost:8080/apex/
Deb is starting to fret that we haven’t quite got enough alcohol in the house to see us over the festive period, so it looks like I’m going to have to venture out into the cold winter evening and brave the crush at the all-night supermarket. Christmas – it’s for the kids really.
Hello,
It seems to have an error here:
1 @apexldimg.sql /u01/app/oracle
On My PC, it’s
1 @apxldimg.sql directory_where_apex_was_unzipped
LikeLike
Fernand,
thanks for the comment.
As I said in the post, I decided to unzip APEX in /u01/app/oracle.
When you run apxldimg.sql, it will create the directory apex/images in the directory you specify using this line :
I did notice however, that I’d spelled the filename incorrectly – which I’ve now put right.
Honestly, I think I may be getting dyslexic in my old age !
Thanks again,
Mike
Mike
LikeLike
Thanks for great guidance; I have successfully installed apex 4.1 and XE11g on windows XP through this blog.
LikeLike
Thanks for this thread, very useful!
but i think that is:
alter user xxxx ACCOUNT unlock;
and not
alter user xxx unlock;
Best regards
LikeLike
David,
thanks for pointing that out. I’ve now corrected it in the post.
Mike
LikeLike
again a great help. thank you
LikeLike
Great help. Thanks.
One thing should probably be mentioned though. It does appear that the “default” XE administration application gets blown away, at least as far as I can tell.
Not a big deal but something to note.
LikeLike
Brian,
very true – the default application does indeed disappear.
Fortunately, you can do most of your database admin via the latest version of SQLDeveloper.
You can also re-create some of the monitoring stuff in APEX. I’m working on that now and will post something on the subject soon(ish).
Mike
LikeLike
Struggled a lot to clear the blank page after new install. Your blog post saved my day. Many thanks.
My install was on Windows. The blank page was cleared by running the command @apxldimg.sql
I had Apex extracted to My Documents, and the above command didn’t work.
So, I had to create a folder c:\apex\images, copy over the images here.. and run the command as follows.
@apxldimg.sql C:\
Thanks again for the post.
Rgds,
Gokul
LikeLike
Hi to all,
everybody at this blog and the main worker too a big “Thanks to you, thanks to all” about this informations. Its funny that there is no document to tell you: simple upgrade from 4.0 to 4.1. So all other guys like you have to do the job.
I had the problems at least with loading the images but with Gokul’s approach it worked too.
So to all: its summer in germany now and have a good time.
Bernd
LikeLike
Thanks a lot for this, and you didn’t bored me 🙂 Readding the official documetnation is bored, but this not, Everything worked fine, just had a problem when i was logging by firt time with the admin user to change the password, I got a 404 error, but I think it was due to few memory or something like that, I restarted the computer and it worked again.
Thanks a Lot, greetings from Colombia.
LikeLike
I have referenced this page in blog posting. It’s nice and concise and covers what is necessary.
By the way, how’s the database monitoring APEX piece coming along?
LikeLike
Thanks, appreciate the link. As for the APEX database monitoring thing, it’s still in the pipeline and will see the light of day soon(ish).
LikeLike
Thanks a lot for this, your blog post saved my day.
I have successfully installed apex 4.1 and XE11g on windows XP
LikeLike
Hi Mike
.. works fine ! Thank you very much
Michael
LikeLike
Hi mike,
I followed the instructions of your post and I got a perfect installation at the first time.
I installed on an Intel Pentium D CPU 3.00Mhz and version of linux fedora 17 (Linux 3.5.2-1.fc17.x86_64) to 64bits and 3GB ram.
Thank you.
Jordi
LikeLike
Thank you for your instructions
LikeLike
It took me two DAYS to figure out apxldimg.sql gave me errors because I had unzipped the install in a directory where the oracle user had no access. Finally, when I found your tutorial, it clicked. Thank you very much!
LikeLike
Thanks Mike. Saves me remembering all the things to do in each upgrade..
LikeLike
Thanks a bunch!
This is the best guide I came into.
Great job, Mike!
LikeLike