Practical Tips for Working with Oracle 11g

We all know this happens. No matter how hard we try at some point we have to work with a product that we don’t really like, but a customer or employer does.  Usually it is not worth quitting over (unless it is a Microsoft product of course – lol). You bite the bullet and keep telling yourself that learning something new is always a good thing, right?

In this case I’ve been playing around with Oracle 11g RDBMS. Oracle has always been a bit of a PITA type product.  It is the industry “gold standard”, but it can also be  “developer hell” because it can be a bit quirky, eats resources on a development box compared to other solutions, and tends to have loads of odd “features”. It is also a pig on a Win32 machine and the developer tools are $$$ and they tend to demand serious resources too. Well here I am once again having to deal with it – really it is fun…really 🙂 . I’ve not used 11g but I found 10g to be a pretty boring upgrade from 9i. Will 11g be better? Here’s some tips and things I’ve learned from using 11g for a couple of weeks.

I’m not going to go over the basics of Oracle. This is a complex and difficult set of tools – they have a decent web presence and lots of stuff over at oracle.com. I’m really going to talk about things I’ve learned with the tools that are not apparent from the docs. I’ll assume that you are familiar with Oracle to some extent.

You can grab the Win32 binaries from the Oracle site for testing purposes – I’m using the 11g Enterprise edition. My goal is to test the installation of the tool and the performance of a few products with it – namely Alfresco, JBoss Portal, and Drupal. As usual I’m running on my T61P Thinkpad Win XP Pro machine. I’m running a ton of other crap on my machine so I’m a bit concerned about memory that Oracle will require.

I installed the Oracle 11g binaries to my machine at c:oracle.  That keeps paths short – XP can get confused with long paths 🙂 I took all the defaults including creating a starter database. The install went great. There are a bunch of services created, but you only need the database and the TNS listener service – gone is the annoying http deamon of 10g – yeah!

The Oracle installer places some significant new things on the start menu. Oracle JDeveloper is a nice, if a bit basic, SQL tool and then there is the totally re-vamped OEM now as a web based tool OEM is a dramatic improvement over 10g. I’ll talk about that later on.

Once installed I created a bunch of different schemas and loaded them up. Things went pretty smoothly and I did not find any issues with drivers or installers. JDBC drivers are packaged here:

C:oracleproduct11.1.0db_1jdbclib

The directory structure is pretty much the same for this version of Oracle.  The base directory where everything for the database is installed is located here:

C:oracleproduct11.1.0db_1

The most common file that you will need to mess around with is the tnsnames.ora file. This file is what the native client will use to connect to your database or other Oracle databases on other machines. As you create new databases you’ll see new entries in this file.  Look for the file in NETWORK/ADMIN.

A typical entry should look like this:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = your hostname.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

As you create more local databases or want to connect ot databases on other machines you will need to add them here. Of course if you are using a JDBC driver then you don’t have to do this.

Working with Oracle, or any other database, is pretty inefficient without a decent set of SQL programming tools (ok I suppose all the DBAs just rolled their eyes). There are three basic tools required:

• a SQL prorgamming IDE
• a modeling and diagramming tool with support for reverse engineering
• a management and configuration tool for the database platform

Of course I don’t really want to break the bank on these tools…so what to do? JDeveloper is supplied with your Oracle install. It is a decent tool. There are a couple of other tools, but you gotta pay for them. My favorite tools are Toad and SQL Navigator from Quest.

Modeling is a bigger challenge. The main requirement for my work is reverse-engineering of existing databases. I tried a bunch of tools: SQL Navigator, Toad for Oracle, Toad Data Modeler, ConceptDraw Pro, MS Visio 2003, and several other freeware/open-source tools. The tools from Quest have some limited reverse-engineering capabilities, but they cannot handle an entire schema. The function is buried under the DatabaseReportER Diagram option. Once you pull up that screen then hit the New icon and you’ll see this screen:

You can select a table and the depth of relations to diagram. If you put a big number in there then you’ll get most of the schema, but beware that the performance is not that great. You can see in the screen shot below that it does generate a diagram with all the keys and relations correctly identified.

The best part of the Quest tool is that you can transfer your generated model directly to their query editor screen.  The Quest tools are not really designed for creating models. These are practical tools for programmers and DBAs. The ER functions are focused on giving a user the ability to quickly model a piece of a running system and then using that model to do something – like design a query or diagnose a performance issue. Definitely useful.

I tried ConceptDraw, but was not able to get that tool to reverse engineer any database. I tried Oracle and MySQL 5.1 with no luck. I posted some questions to their community area, but the moderator has not actually approved my thread after 24 hours. I’ll post back an update on this tool since it does look interesting.

MS Visio 2003 Enterprise (aka super expensive) can perform reverse engineering of databases. This was the tool I initially tried, however every time I tried the function, Visio would crash. Naturally I just chocked up another one to the guys in Redmond. Having no luck with these other tools I decided to try Visio again. This time instead of using the Oracle drivers from Oracle for 11g, I decided to try a generic OLE DB driver. Sure enough this worked.

Visio RE’d the entire schema and correctly got all the keys and relations mapped out. Now that the diagram is in Visio you have the full layout engine so that I can arrange the model in a nice compact format.

Managing an Oracle database has always been a bit of a black art. DBA tools have pretty much been tied into your knowledge of Oracle command syntax and parameter file knowledge. In just a few days of seeing 11g, this is an obvious are of big improvement. There is now a comprehensive and easy to use manager console that runs in a web browser. Start up the service for the manager and then browse to this url on the machine:

https://localhost:1158/em

In this console you can pretty much do anything from see performance statistics on the OS and DB, monitor alerts, change configurations in all the property files, configure and manage clusters, and just about anything else. If you are running Oracle on your own development machine you might want to use this console to tweak your memory settings. A default Oracle install will take as much memory as it can. I went into the memory advisor panel and reduced the maximum memory to 512 MB of RAM. Oracle still runs fine and now the rest of my tools do as well.

To do this load up the console and then click on the server tab and then under Database Configuration click on the Memory Advisors. On this screen you can see and change various things about the memory allocations. Even more interesting than this is the full page that shows all the initialization parameters.

Go back to the server tab and click the last link under initialization parameters. You should see a complete list of parameters.  This new tool really is huge for the non-DBA developer. For example I was just testing the warehouse builder tool (another interesting new menu item) and ran into trouble with a user account that was locked. In past versions of oracle I would have to dig around until I could find the right tool to mess with that account. Now it is right in the same tool with the memory settings and general monitoring. Very nice.

So that’s it for now. I’m going to get my models generated and then start to work more with getting hibernate working with 11g for Alfresco and maybe I’ll get a bit more work with this new warehouse builder tool.