Connection Failed error message with PeopleTools Change Assistant on 64 Bit Windows

With PeopleTools 8.5, Oracle moved the app servers into 64-bit territory. That was welcome news. Unfortunately, the whole stack isn't quite there, yet, and we ran into an irritating problem when running Change Assistant to upgrade from 8.49 to 8.51. Specifically, when setting up the environment, we recevied a "connection failed!" error when testing the connection. Here's what we saw: No logs, no details. Just failed. But then, finally, a hint: Data mover (which shouldn't connect, since the database is still at 8.49) wouldn't even run: it failed with an error "missing or invalid version of sql library psora". Aha! Now that is something one can work with. It turns out it needs the 32-bit Oracle client. Install that, and everything is good, again.

Adding an Oracle home to an agent inventory

When an Oracle inventory is saved in a non-standard location, the Oracle Grid Control agent can be unable to enumerate the software that is in that Oracle Home. This is true even when it can find the home itself.
In OEM, you’ll run into an error like that below when you click on the home in the Targets list:

Error Could not find Oracle Home <ORACLE_HOME> in the inventory collected for <hostname>

We’ll use TESTSRV2 as a troubleshooting example.

Locate the oraInst.loc file

The oraInst.loc file contains the inventory for all of the Oracle software. Normally, Oracle maintains a single copy of this file, but when one is saved in a non-standard directory, it can get left out.

Oracle Database loses its OEM configuration after a Cold Backup

This was an annoying problem that took awhile to track down. In short: after our scheduled cold backups, an Oracle (11g) database would lose its configuration in Oracle Enterprise Manager. It would present a "Metric Collection Error" that would go away after reconfiguring the database. The fix, as it turned out, was pretty simple, but it took awhile to tease out. The problem was that the trace directory (bdump in 10g) was too full. Specifically, the metric collection (the process by which OEM gathers data about the database) was timing out. We ruled out performance problems on the database side; the system is not utilized much at all. Instead, we discovered that because there were a lot of files in the trace directory (> 31k), it was taking a long time for the OEM agent to get to the alert log, which is one of the metrics that it collects. This was hinted at in the emagent.trc file:

2010-01-04 13:01:53,087 Thread-47647632 ERROR TargetManager: TIMEOUT reached in computing dynamic properties for target TESTDB,
oracle_database::compute timings were [decideIncludeDB:0-0] [SystemTablespaceNumber:0-0] [SysauxTablespaceNumber:0-0 ...
[DeduceAlertLogFile:1-1] [GetCPUCount:1-1] [EnabledFeatures:1-1] [GetOSMInstance:1-1] [GetNLSParam:1-1] [GetAdrBase:1-(1)]

So you can see above that one of the things it was trying to do was get at the alert log. It took a long time to enumerate all of the small files in the trace directory, so we shut down the instance, cleared out the trace directory, and restarted the instance. That took care of the problem. In troubleshooting this problem, we also increased the dynamic properties timeout setting (dynamicPropsComputeTimeout_oracle_database) in the emd.properties file (in [agent_home]/sysman/config), changing the value from the default (120) to a larger setting (240). That did not help, though it's a good troubleshooting step, should you run into a similar problem.

Installing Oracle Enterprise Manager 10.2 on Windows Server 2008

One would suppose that installing OEM on Windows Server 2008 would be like installing it pretty much in any other Windows environment; Oracle did a pretty good job of making it easy to install and run out-of-the-box under earlier versions of Windows, so it should be easier with the latest version of OEM and Windows, correct?
Wrong. There are a variety of reasons, and we’ll run through them in this exercise, as we install Oracle Enterprise Manager 10.2.0.5 on Windows Server 2008 (NOT R2; this is important!).
As an added bonus, we’ll get the OEM repository database up to 11g.

ORA-29275 when Accessing a UTF8 Database with SSIS

So when I first started looking into SQL Server Integration Services (SSIS), I was told that the learning curve was steep, and that it was worth it to learn what you're doing with it. Truly said. SSIS presents a myriad of possibilities for data, and once you get your head around some of the terminology, creating simple transformations and data imports is a snap. But what about when the problems you encounter span two different products? What if one is Oracle? What if it's 64-bit, on Linux? UTF8? That's really what SSIS is for, and it's possible. But. Lots of Googling. Here's one roadblock I encountered in importing data from an Oracle database into SQL server, along with an unsatisfying workaround. But it does work, and it's easy. If you know of a better solution, I'd love to hear from you!

The Problem

So here's the problem in a nutshell: the Oracle database uses a utf8 character set. SSIS, when connecting with either the OLEDB or ADO.NET data sources, would use something else. What, precisely, I'm unable to discern. I can say that the Unicode setting was set to true in the data manager. This was manifest by a startling Oracle error during the load from the data source: ORA-29275, which is a "partial multibyte character" error. This means that the data doesn't fit the database's character set, which (one assumes) is terrible: such data is almost by definition corrupted, and getting it back reliably is a tricky proposition. Oracle says, basically, that you've got bogus data when you see this error. I was prepared to believe that, as this error occurred even in a simple select statement from the DB server itself.

The Clue

The curious thing about this situation is that, in trying to figure out what was going on, another user had logged into the server using a different OS username. When he connected to the database using the same Oracle user ID, he didn't get any errors. Aha. Environment. NLS_LANG, to be exact. Setting that to American_America.UTF8 took care of the error on the server, and on clients running SQLPLUS, to boot. All should be well, correct? No. The SSIS package continues to fail. Oh, yes: the registry. Don't forget that Oracle stores client NLS data there, as well: HKLM/Software/Oracle/$ORACLE_HOME/NLS_LANG Watch out for any dangling NLS_LANG settings in HKLM/Software/Oracle That surely will fix it, right? Sadly, it didn't, and a desperation reboot didn't help.

The Work-Around

So Google leads me to hints that ADO.NET and OLEDB from Oracle don't really pay much attention to the local NLS_LANG settings. That appears to be the case, or, at least, they don't get their settings from the same place everything else does. So, I return, sadly, to ODBC. And it works! It works well. But it's so non-portable, and, let's face it: ODBC is not anything new and shiny; it'd sure be nice to have all of our SSIS packages all new, self-contained, and .NET-ed. So if you, like me, run into this problem, know that ODBC can be your friend. If you, unlike me, know of a better solution, please let us know! I'll post updates as I encounter them.

Installing Oracle on RHEL 5 (32 and 64 bit) - Part 3

In part one of this series, we got the operating system ready for the installation of Oracle. In part two, we got Oracle installed and running. In this, our final (for now) post on Oracle and Linux, we'll look at some of the tweaks that make it better, as well as some of the surprising bumps one encounters when using Oracle on Linux.

Installing Oracle on RHEL 5 (32 and 64 bit) - Part 2

In part one of this series, we got the operating system ready for the installation of Oracle. In this post, we'll actually install Oracle and get it up and running. In part three, we'll look at some of the things you can do to make Oracle a bit more usable on the Linux platform. Oracle recommends using the GUI installer. I think they're right: it's the most accessible way to get Oracle installed. If you only have remote access to your system, and SSH is the only remote access you've got, you can set up VNC reasonably quickly to have remote GUI access to your system.

Installing Oracle on RHEL 5 (32 and 64 bit) - Part 1

With the then-hyped Oracle Enterprise Linux rollout, and Ellison's evident disdain for Microsoft, you'd have thought that Oracle would go out of their way to make it straightforward to install and use Oracle database on Linux. It's not so, unfortunately. Not that it can't--or shouldn't--be done, mind you: it works great; you just need some persistence. This is part one of a three-part series on Oracle on Linux. This post looks at getting the OS ready for Oracle. Part 2 looks at installing the database software, and Part 3 talks about some of the things that can be done to make running and maintaining Oracle easier on Linux. Oracle has a document here that does a pretty good job of outlining the steps necessary to install Oracle on linux. I quibble with some of their instructions, particularly their directions to use the package rpms from the Oracle install CD. If they're going to go to the trouble of providing the requisite rpms, it seems like they'd also install them during the installation process, if they're needed. Instead, it seems like a *much* better option to install them from your repository (CENTOS or RedHat are most pertinent to this guide). They also have you using rpm, which is fine, but it won't find dependencies for you like yum will. In any case, I do recommend that document for additional information. It's my hope that this series will help navigate the installation process and make it easier. I've broken this into several posts so as to keep the length of each one a little smaller, anyway. In this post, we'll get the OS prepped and ready for the

ORA-00911 when writing a query for SSIS

Coming from the Oracle world, I'm in the habit of putting a semicolon at the end of all of my SQL queries. Not a good idea when you're writing an Oracle query for import/export in SSIS (SQL Server Integration Services).

The problem is that SSIS takes care of that for you, and you'll get an entirely unhelpful "ORA-00911: invalid character" error message.
Just remove that final semicolon, and you should be in OK shape.

If you find yourself needing to do multiple SQL statements in one execute SQL task (for instance), you can use the GO separator instead of semicolons.

Note that GO has to be on its own line:

select 'this is sql statement 1' from dual
go
select 'this is sql statement 2' from dual
go

For the curious, GO is a SQL Server-specific batch keyword that is used to separate different SQL batches. It wouldn't normally be something you'd consider using in an Oracle query, but it works in this case.