Upgrading APEX to 4.0 on XE

No point in writing what other have written perfectly. http://www.webxpert.ro/andrei/2010/09/09/upgrade-oracle-apex-3-2-1-to-4-0-1-on-oracle-database-10g-express-edition-xe/

Following this guide works with my apex on slicehost guide: http://mfro.blogspot.com/2010/04/xe-and-apex-on-slicehost.html

OBIEE 11g: Import metadata, connection problems.

Importing metadata aught to be a simple: Hit File - > Import Metadata, punching in db and user name and finish it of with password then select what you want to import. It is if your lucky. The pitfall that got me was that OBIEE 11g install it's own Oracle client.
Fine, so what? It is in this clients tnsnames.ora that it looks up servicenames. Which means that you have to have the correct tnsnames entries in {Oracle_BI1}\network\admin\tnsnames.ora and not that of your database.

OBIEE 11g the first disappointment.

After about an hour of fiddling about and reading documentation I finally got the OBIEE 11g image to work. Even before I log into analytics I meet my first disappointment. Really it is a very small thing, but still annoying that they haven't fixed it.

So here it goes. When you come to the login page, you naturally start filling it from the top towards the bottom. So you enter user id and password. Then you notice that the server has a different default language than what you prefere. So you switch from default to what ever your preference is. The problem is that this trigger a reload of the page, so that your user id and password field is empty and you have to fill it out again. This is the kind of things that annoy user, and it is a bad first meeting with a product.

Deployment errror in Work Flow

RPE-02012: Internal error: Function P83_NAME_OF_SOME_MAPPING cannot be created or updated. Please try again. If the problem persists then please contact Oracle Support with the stack trace and details on how to reproduce it.

- ORA-00001: unique constraint (OWF_MGR.WF_ACTIVITIES_TL_U2) violated

ORA-06512: at "OWF_MGR.WF_LOAD", line 1402

ORA-06512: at line 1

This seems to be nothing more than a error telling that the workflow name i too long and when it is truncated it causues a name that is not unique

OWB 10.2.0.4 joiner errors

Deleting a in-group in a joiner
If you have a joiner and for some reason has to delete one of the in groups OWB can corrupt the join conditions. After a delete the columns in the conditions have moved around or been substituted for other (apparently random) columns. If not corrected manually afterwards this will cause you trouble.

java.lang.ArrayIndexOutOfBoundsException: -1
When deploying a mapping you might get the following error

VLD-1141: Internal error during mapping generation.

java.lang.ArrayIndexOutOfBoundsException: -1
at oracle.wh.service.impl.mapping.component.JoinSqlDelegate.prepareOutputContext(JoinSqlDelegate.java:530)
at oracle.wh.service.impl.mapping.generation.WBMappingGenerator.generate(WBMappingGenerator.java:239)
at oracle.wh.service.impl.mapping.generation.PlSqlGenerationMediator.assembleSetBasedInternal(PlSqlGenerationMediator.java:2150)
at oracle.wh.service.impl.mapping.generation.PlSqlGenerationMediator.assembleSetBased(PlSqlGenerationMediator.java:2132)
at oracle.wh.service.impl.mapping.generation.PlSqlGenerationMediator.assemble(PlSqlGenerationMediator.java:554)
at oracle.wh.service.impl.mapping.generation.WBMappingGenerator.generate(WBMappingGenerator.java:807)
at oracle.wh.service.impl.mapping.generation.WBMappingGenerator.generate(WBMappingGenerator.java:334)
at oracle.wh.service.impl.mapping.generation.WBDeployableMappingGenerator.generate(WBDeployableMappingGenerator.java:104)
at oracle.wh.service.impl.generation.common.WBGenerationService.generateCode(WBGenerationService.java:433)
at oracle.wh.service.impl.generation.common.WBGenerationService.generateCode(WBGenerationService.java:311)
at oracle.wh.service.impl.generation.service.WhValidationGenerationTransaction.run(WhValidationGenerationTransaction.java:251)

This happens if you have a join condition referencing outgrp1. Why would you reference outgrp1 in a join statement, you wouldn't. However if your deleting one of the in-group, in particular the last in-group, Oracle might insert outgrp1 as part of the join condition. If you get this error start by looking through all your join conditions to see if Oracle have messed one up.

OracleXE and Apex 3.2 on Ubuntu 9.0.4

Introduction
I'm a huge fan of Apex. It is a very quick and good way to develope data driven applications. With it you can make extreamly affordable applications. Especially if it is compined with OracleXE and a free of charge Linux like Ubuntu. This is what I'm going to do in this short how-to. I'm going to install OracleXE (10g) on Ubuntu 9.0.4. Apex is bundled with OracleXE, so no installation is needed for that. How ever I'm going to upgrade the Apex installation from 2.1 to 3.2

Requirements
First of all you need 32bit os. Install Ubuntu 9.0.4, the desktop version. I had some problems in previous version of Ubuntu with the server release in regards to OracleXE, thats why I'm using the desktop version. Just make a default installation. I did mine on VmWare, running of my Windows XP gaming pc.

Then grab Oracle Database 10g Express Edition (Universal), Download oracle-xe-universal_10.2.0.1-1.0_i386.deb from oracle.com. For the uninitiated this is Oracles free database. You can use it with 1gb memory, 1CPU and 4gb of data for (almost) any purpose free of charge. The full lisence can be found her.

Get Apex 3.2 for oracle.com

Installing OracleXE

sudo aptitude install libaio1
sudo dpkg -i oracle-xe-universal_10.2.0.1-1.0_i386.deb

When running the above the installer might abort and say that you do not have enough SWAP. If it does run the following four lines. Where 700 is changed for the amount of SWAP you are missing.

sudo dd if=/dev/zero of=/SWAP.img bs=1M count=700
sudo losetup /dev/loop7 /SWAP.img
sudo mkswap /dev/loop7
sudo swapon /dev/loop7

Note that after the first reboot this SWAP image is no longer going to be used, which is fine. We only need it during the install. However, ff you want to keep it you either have to add it in fstab or run the following after each boot.

sudo swapon /dev/loop7

When then installer finishes it tells you to run the post install, do that:

sudo /etc/init.d/oracle-xe configure

It will ask you a bunch of question, if your unsure of the answer just hit enter and you'll get the default. Well you need to enter a password though.

http://127.0.0.1:8080/apex is usually the url for your new database. From this web interface you can manage some aspects of the database, but I'm a sqlplus fan. Note the 127.0.0.1 address is only valid from your computer. If you need to reach the database from a different computer substitute 127.0.0.1 for the ip or domain of your computer where OracleXE is running.

Note that the installer created a database with SID = XE for you. This might come as a surprise to some experienced DBA's as Oracle usually does not do this. You have to do it your self after the install.

Upgrading Apex 2.1 -> 3.2
Unzip the software you downloaded from Oracle.com

unzip apex_3.2.zip
sudo mv apex /usr/lib/oracle/xe/app/oracle/product/
sudo chown oracle:dba -R /usr/lib/oracle/xe/app/oracle/product/apex

The installer for the databse created a oracle user, so lets login with it and get sqlplus working:

sudo su - oracle

Set the path and oracle environment variables:

export PATH=$PATH:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/
export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE

It's time to upgrade apex. When you installed OracleXE it came with a apex installation, but we want to have the newest, Apex 3.2.

This will take hand of the pre-install requirement:

cd /usr/lib/oracle/xe/app/oracle/product/apex
sqlplus / as sysdba
ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=spfile;
shutdown immediate
startup
@apexins SYSAUX SYSAUX TEMP /i/

The last line starts the update scripts. It will run for a while, so be patient.

Since we are running apex on OracleXE we have to do a couple of special steps, these are the ones below. Note that password should be substituted for what ever you want the admin password to be.

@apxchpwd
@apxldimg.sql /usr/lib/oracle/xe/app/oracle/product/
@apxxepwd.sql password

If you want your applications to be available from other computers run:

exec dbms_xdb.setListenerLocalAccess(l_access => FALSE);

There are a few more settings you might want to change, they can be found her.

The administration page for your apex can be found at: http://127.0.0.1:8080/apex/apex_admin

Username is admin, and password is the one you sett when runing @apxchpwd

The application interface that user are going to use when developing applications are at:
http://127.0.0.1:8080/apex/apex

When the application is done the final applications will be available at:
http://127.0.0.1:8080/apex/f?p=#APPNUMBER#
Where #APPNUMBER# is the number of the application, for instance 100, http://127.0.0.1:8080/apex/f?p=100


More documentation and lots of good information can be found at:

• Documentation
  
• Apex home page
  
• Apex forum
• OracleXE forum

[DBA] Master Thesis

I wrote a my master thesis about porting a system from PostgreSQL to Oracle, the complete thesis can be found here.

Abstract

Network monitoring is an important task in the Internet. It is also a very complex task. Especially managing the measurement data, since the amount of data involved makes it difficult to organise and store. InTraBase was introduced to solve the data management problem experienced with other network monitoring tools. InTraBase stores all the information inside a database and perform the analysis directly in the database.

Our hypothesis is that a system like InTraBase will perform better if implemented in Oracle. We base this on the claims of the database vendors, along with the fact that Oracle has a collection of tuning tools and advisors. These tools are easy to use because they can be accessed through the GUI in Enterprise Manager. ;

pgInTraBase is a PostgreSQL-based implementation of InTraBase. During the porting of pgInTraBase we identified a small set of differences in PostgreSQL and Oracle's syntax. For instance Oracle does not have 'LIMIT' or 'OFFSET' clause for SQL. We have found simple ways to work most of these clauses. In most other cases we found equivalent functions in Oracle. In pgInTraBase the queries were concatenated as a string to include tablename and various variables and then the string was given as a parameter to be executed. In oraInTraBase we used a synonym to include tablename, and bind variables for other variables. This was done in order for the parser to be able to reuse the execution plans.

We see the process of analysing data as a two phases process:

1. Upload – upload packet headers into the database from a flat file.

2. Analysis – analyse the data to find the root cause for throughput.

Systematically tuning of the procedure that populates the database with packet headers, reduced the running time of the Oracle prototype to approximately 20% of the original time when using 1GB trace. Compared to PostgreSQL the execution time of the tuned process is almost cut to half. The Oracle version performs significantly better than the PostgreSQL version in the upload phase.

The process of analysing the packet headers was also tuned in a systematic way. After tuning, the analysis of a 50MB trace takes 2% of the time it used before tuning. Compared to PostgreSQL a 50MB trace analysed in Oracle takes only about 42% of the time. Comparing the running time of traces of 10MB, 50MB, 100MB and 1GB show the same results, while using a 10GB trace Oracle needs 182% of the time PostgreSQL does. However, Upload is much faster in Oracle than in PostgreSQL. So much that if we add the time Oracle use for both Upload and Analysis this takes less time than the Upload alone in PostgreSQL. This highlights the very different performances of the two DBMS depending on the task.