Moving from 9i on Windows to XE on Redhat Linux

For the past month we’ve been moving from a 9i database in a Windows environment to an Oracle XE database on Redhat Linux. A bit of a difference. As a software house we use Oracle products under a development license but because we wanted to go live with in-house developed APEX applications that fall outside the development license we faced moving to XE or paying for an Oracle license. What would you do?

Not surprisingly the powers-that-be opted for the free option. Over the past month or so I’ve been preparing for the move to XE. This has involved installing and configuring the XE database on a Linux server, upgrading to 3.0.1, checking the applications still worked correctly in the new environment and making sure the switch over was as smooth as possible. As part of this change I took the opportunity to change the logins to network logins. I had to create new users anyway so it wasn’t much more work.

Frustratingly the work I had to do only took a few days, the rest of the time was spent trying to get our tech cons to set up regular backups of the new database. We work with hundreds of databases here and so there are processes we have to go through when requesting a new database is backed up. First I had to wait for a techie to be available; internal applications aren’t the highest priority here so frequently the work was scheduled but not completed due to other higher priority work. They then had to find space on the Unix backup server, then the date on the Linux server was wrong which meant the database wasn’t being backup up on the right days. There seemed to be no end of issues.

This is in total contrast to the work I did, which went rather well. The old applications were set to unavailable and half an hour later the new applications were up and running. So what did I do? Cloning the database wasn’t an option as we’d be cloning a 9i database into an XE environment, I searched the forums and couldn’t find anyone else that had done this or what the impact on the license would be. My company wanted to ensure that we were fully licensed so didn’t want to risk importing a database and invalidating the license, so I took the option to use a database link and scripts.

Using the APEX facility to generate ddl scripts I created the sequences, functions, procedures, packages and triggers on the XE database and using the database link I created the tables. I also exported and imported the applications, which couldn’t have been easier and makes how we work in other products a joke. This was then used for testing. Today I made the old applications unavailable and regenerated the sequence script from the old database, then dropped and recreated the tables and sequences on the XE database. I then wrote a procedure to create the new users and email them with their login details. This script, detailed below, meant I didn’t need to email anyone this information. It’s just a shame that other technologies aren’t this simple!
PROCEDURE "DAP_CREATE_USERS"
is
begin
DECLARE
CURSOR c_developers IS
SELECT id,
name,
full_name,
email,
lower(username) username
FROM developers
WHERE nvl(removed,'N') = 'N';
v_new_username varchar2(100);
BEGIN
FOR a IN c_developers LOOP
v_new_username := UPPER(replace(a.email,'@company.com'));
HTMLDB_UTIL.CREATE_USER
(P_USER_NAME => v_new_username,
P_WEB_PASSWORD => a.username,
P_EMAIL_ADDRESS => a.email);
HTMLDB_MAIL.SEND(
p_to => a.email,
p_from => 'your.name@company.com',
p_body => 'Hi '||a.name||chr(10)||chr(10)||'We have created a new database for the APEX Internal applications. This has meant that new users needed to be created on this database, your new login details are provided below. When you log in you will be able to change your password by clicking on the link in the top right-hand corner of the screen. All data from the old database has been moved across so you don't need to do anything other than note your new login and URL. If you have any problems or queries then please let me know.'||
chr(10)||chr(10)||'Username : '||v_new_username||chr(10)||'Password : '||a.username||chr(10)||'URL : http://171.11.1.11:8080/apex/f?p=intapps'||chr(10)||chr(10)||'Regards',
p_subj => 'APEX Internal Applications Login Changes');
UPDATE developers
SET username = v_new_username
WHERE id = a.id;
END LOOP;
HTMLDB_MAIL.PUSH_QUEUE('170.10.1.100','25');
END;
end;

Sara

Sara Blair is a freelance Oracle developer with over 10 years Oracle experience. She has been working with Oracle APEX for nearly 4 years since htmldb 1.5, and is available to hire through Silvercore Solutions.

Comments cannot be submitted.