Switching Oracle Portal to a new database is easier than Oracle Tech Support says

If you do not have to support an Oracle Portal, consider yourself lucky. We recently ran into a problem that forced us to quickly move to a new metadata database. Once again, the lousy Oracle documentation, coupled with lousy Oracle Tech Support, had us believing it would be a week-long ordeal. Turns out, it's actually pretty simple.

First, I'll provide a quick overview of the pieces in an Oracle Portal to help you understand our situation. The Portal is essentially an Apache server connected to both a J2EE container (Oracle Container for Java, OC4J) and an Oracle database to house the metadata repository. The repository is a set of schemas where Portal content like pages and documents gets stored. Several key components of the Portal are written as PL/SQL packages that execute out of the metadata repository.

Adding complexity to the mix is that the Portal's user authentication and some of its internal configuration is done with a system called Oracle Internet Directory (OID). This is an LDAP server, but (surprise) you can't run with a standard LDAP because some pieces of the Portal system rely on the internal implementation of that LDAP instead of relying on the pure LDAP interface. And, that implementation happens to be (voila) yet another Oracle database schema.

So here's what happened to us: We had the metadata repositories for both our Test and Production portals running on the same physical hardware server. (I know that's bad, but I inherited this architecture -- apparently they were trying to build out the Portal on the cheap.) Our Portal has grown even larger and has been supporting an ever-increasing user base. Last week, it all started to fall apart; our server started having conniption fits from having both databases running the same hardware. Our DBA eventually got Production stabilized (with some help from Oracle Tech Support), but we couldn't risk spinning up the Test metadata repository on that same machine, so we had to move it quickly to a new server.

We still had the original Test metadata repository, just the database software was turned off. Our plan was to copy the datafiles and spin up a new Oracle instance on the new hardware. We figured it would be a snap to reconfigure the Portal's mid-tier (the Apache and J2EE and PL/SQL components) to point at the new database.

Here's where Oracle Tech Support failed us. Since we couldn't find anything describing this exact scenario in the docs, we asked Tech Support for some suggestions. Of course, they pointed us to this big scary process for migrating to a new metadata server.

I decided it couldn't possibly be that difficult and that many steps, so I went poking around in the Portal's Administration UI. It turns out there is a place where you can just select which metadata repository it loads from.

At first, I couldn't figure out where the Portal was coming up with the list of metadata repositories. On an Oracle system, there is usually a tnsnames.ora file that maps friendly names like "Test" and "Production" to the physical connection strings. Our Portal mid-tier didn't have these listed in its tnsnames.ora.

I realized that must be one of the bits it pulls down from the OID, so I logged onto our OID server and ran the Oracle Directory Manager (ODM), which is the admin interface for managing the LDAP directory stored in OID.

In ODM, open the "Entry Management" node and then open the "OracleContext" node. You should see a list of all the metadata repositories you've registered with the OID. Pick the one you want to edit (in our case, it was the one called "Test"). One of the attributes will be orclnetdescstring; it's exactly the kind of entry you would normally have in a tnsnames.ora file. Scroll through the text until you see HOST=old_test_server and replace the old_test_server hostname with the hostname for your new test server.

Open the "Test" node and drill into its sub-nodes. Find the sub-node DESCRIPTION_0 --> ADDRESS_LIST_0 --> ADDRESS_0 where you'll find an orclnetaddressstring attribute. Replace the HOST= entry with your new clone of the old database.

Save the entry and that's pretty much it. You might have to restart the OID service (even though I'm not sure why we had to do this step). Now restart the mid-tier, and it should pick up the new LDAP entry and log into the new metadata repository as if nothing happened.