Posts Tagged ‘Oracle’

OpenSuse got a bit of a facelift a few weeks ago and is now available in version 11.2. The installer looks quite gorgeous (just a shame you don’t tend to install distros that many times, I guess, so you’ll only appreciate the emerald green stuff once or twice!). I was surprised to discover that KDE is selected as the default desktop… Suse’s been Gnome-preferred so long, and KDE 4 has been so beta for so long, it seems a bit of a brave move to me. I am not, in any case, very used to KDE (I barely used 3.5 back in the day; 4.3 seems just very, very peculiar to me, though I guess I could acquire a liking for it eventually), so I found things a bit heavy-going, to be honest (not being able to drag-and-drop a file to the desktop took a bit of getting used to, for example!). And it’s all rather a lot of graphical overkill for an Oracle server, I suppose, in any case!

Anyway, my GOAL instructions say to perform a strictly default OS installation, so just for once I stuck with the KDE suggestion… and watched as GOAL completely failed to run at all! The reason is simple: GOAL depends on the zenity package to do its graphical stuff, and zenity is not installed as part of the default 11.2 KDE installation. So, I had to do a zypper -n in zenity as root, before anything else. Once that was done, though, GOAL did its thing and 64-bit 11g release 2 installed without a hitch (the usual pile of ‘these packages need to be installed’ messages can simply be dismissed with a wave of the ‘Ignore All’ option, of course). The GOAL download page has been amended slightly to reflect the fact that the new distro version has been tested and works fine.

Update: If you select the Gnome desktop option during the Opensue 11.2 install, zenity *is* included by default, so no extra, manual installation steps are required before GOAL can be run properly.

Fedora 12 was released about three weeks or so ago. It’s taken me that long to verify that my GOAL prerequisite installation script works correctly with the new distro. Happily, it does (I needed to make one small adjustment to let the script allow the use of the higher distro version number), so the GOAL download page has been modified accordingly.

I must say, the new Fedora is the first one I’ve ever seen that doesn’t immediately make me want to vomit: the desktop graphics are subdued and subtle, eschewing the balloons, DNA helices and God knows what other nonsense they’ve gone in for in the past. I still wouldn’t pick it as my first distro, however -from the Oracle perspective, for example, it’s the only distro that doesn’t give you the option to switch off SELinux and the firewall as part of the installation process. You have to remember to do that after the installation completes and you’ve performed your first log-in -which is precisely the time you are least likely to remember such niggly details!

Still, Fedora 12 and 64-bit 11g Release 2 work perfectly together, with a completely flawless installation -apart from a list of supposedly missing packages (which aren’t missing at all) which can be dismissed with a click of the ‘Ignore All’ checkbox!

Suppose I have an 8 million row master table sitting in a database next to my desk in sunny Sydney. I stick a materialized view log on it, and create a fast refreshable materialized view on that master table in a database sitting thousands of kilometres away somewhere. Imagine further that the link between my desk and this remote is not exactly brilliant: copying the original 8 million rows across when creating the materialized view in the first place would have taken several hours. Let us say, therefore that it’s not a copying exercise I would be keen to do again, if I could help it!

Finally, imagine that on attempting a fast refresh on the remote materialized view you see the dreaded error message:

  ERROR at line 1:
  ORA-12034: materialized view log on "LOCALDB"."BIGTABLE" younger than last refresh

There are several and varied reasons you might get that particular error message: if you drop and re-create the local materialized view log as part of a migration of that particular database from Windows to Linux, for example; so would purging the log. Whatever the reason, the usual advice at this point is to simply drop the remote materialized view and re-create it from scratch. But in this case, that course of action would result in a multi-hour data-copying-over-a-flakey-network nightmare writ large, so it’s not advice I would particularly want to follow.

Happily, there is a way around the drama that doesn’t involve multi-Gigabyte network copying.

1. Create a table as a copy of the current materialized view in the remote database. Since the copying of a table is entirely taking place within the remote database, no network traffic is involved:

  create table mvbackup as select * from actual_mv;

2. Drop the existing materialized view in the remote database:

  drop materialized view actual_mv;
  drop table actual_mv purge;

The drop table command is in there, just in case the materialized view was originally created on a prebuilt table. If it wasn’t, then the command will produce an error because no such table exists. Just ignore it if that happens, then.

3. Create a new table with the ‘right’ name as a copy of the backup taken at step 1:

  create table actual_mv as select * from mvbackup;

4. Create a new materialized view on this newly created table, using the ‘on prebuilt table’ clause:

  create materialized view actual_mv
  on prebuilt table
  refresh fast on demand
  as select * from master_table@australian_db

Now, at this point, there is a reference to the remote database -and there’s no doubt that the remote database goes across that link to check for the existence of the master table and so on. But the ‘on prebuilt table’ clause means that it doesn’t fetch any data across the link, because it knows that all its required data is stored locally. The net result is that the new materialized view is created in less than a couple of seconds, even with that reference to the remote database.

Of course, this technique only really works because I happen to know for certain that the master table is not undergoing any DML as I’m doing all this mucking about. It also helps that I know for a fact that no DML has taken place on the master table since the last refresh of the remote materialized view. I checked that was indeed the case with this simple query:

  select * from master_table@australian_db
  minus
  select * from mvbackup;

Had anything been returned by that query, I would have inserted it into my backup table before starting step 2:

  insert into mvbackup select * from master_table@australian_db where primary_key_col in
  (select primary_key_col from master_table@australian_db
  minus
  select primary_key_col from mvbackup);

That would have made the MV built at step 4 as up-to-date as its master, and would therefore have involved fetching data across the wire, slowly. However, at least it would only have been a tiny subset of the total data -a matter of a few tens of minutes at most, and not the multiple hours required to fetch the entire data set as would have been the case if I’d dropped and re-created the materialized view in its entirety.

I’ve been slack.

When it comes to using Oracle-on-Linux, there’s only ever really been one game in town for me -at least, by way of habit over the past five years or so- and that’s Centos. As a compiled version of Red Hat’s own source code, with only a few logo changes to keep the trademark lawyers happy, it’s as close to running a “proper’ Red Hat Enterprise system as you can get without parting with large dollops of real cash. So it’s done the job admirably and I’ve never thus needed to look over the parapet and see what else might be part of the Oracle/Linux landscape.

I’ve never felt any great need or desire, in other words, to muck about with Oracle’s own Oracle Enterprise Linux (OEL). OEL is another of those ‘clones of Red Hat’ along the exact same lines as Centos, Scientific Linux, Tao Linux, Lineox and many, many more… except that it’s provided by a Megacorp and not a band of disputatious volunteers. It also happens to be a supported OS for Oracle installations, whereas Centos is not. So, in a fair fight between Centos and OEL, those two points of differentiation might very well make you think OEL just has the merest smidgen of advantage over Centos. However, to install Oracle on Linux, you tend to need to install rather a lot of ‘prerequisite’ packages -and the quickest, easiest, most convenient way of doing that is to do a few yum install commands. Yum only works, though, if you’ve got access to the required yum repositories. Centos makes a fist-full of such repositories available for zilch… and (the show-stopper) OEL requires that you part with hard cash before you can start downloading patched and upgraded software.

It’s this fact that Centos can yum for free whereas OEL cannot that has tipped the balance for me hitherto: no matter that OEL is supported and backed by the same company that makes the database software you want to run on it, Centos is about AU$144 cheaper to use, which makes all the difference in the world!

Except that that’s not true. In fact, as far as I can tell, it’s not been true since about March 19th this year, when Oracle made available several yum repositories for OEL, completely free of charge. I didn’t notice it at the time, even though people like Frits Hoogland wrote about it then; and as I said earlier, as a happy Centos user, I’ve not had occasion to notice it since! A freely-available yum repository, however, makes all the difference: if you can download what you need for a successful Oracle Database installation on OEL for free, why wouldn’t you use it rather than Centos? I know I would! And indeed, from this point on, that’s exactly what I’ll be doing: OEL becomes the distro of choice chez Rogers for running Oracle databases and goodbye Centos.

There are still just a couple of gotchas with OEL to mention, however.

First, the Oracle yum repository does not supply patches and upgrades to software: it’s really not much more than an online version of the installation DVD.So, if it’s not on the original installation media, you can’t get it via yum. That means you can’t install the latest versions of (say) Firefox via yum. Rather more seriously, if a major security vulnerability is ever found in a program that was supplied on the original installation media, you won’t be able to patch the program to fix it. For that sort of capability, you really do still have to pay Oracle some money and join the Unbreakable Linux Network (or stick to running Centos, I guess!) An Oracle installation on completely-free-OEL, therefore, is not something you’d want to do in a production environment, but for disposable self-learning environments, it’s just fine.

Secondly, OEL ships with no yum repositories enabled by default. So, before you can start yumming, you have to manually add the Oracle freebie repositories to the yum setup yourself. You then have to edit the relevant configuration file to make sure the right repositories are enabled and the wrong ones aren’t. Additionally, you have to switch off the ‘gpgcheck’ functionality for the repositories, otherwise yum will only ever complain that you’re trying to make it download software that’s unsuitable for the platform it’s running on and never actually get around to downloading anything.It’s all rather more complicated than Centos, which has a fistfull of enabled repositories by default, all of which work without manual reconfiguration of any sort. However, because I can’t find a way of telling whether you’re running Centos, real Red Hat, OEL or any other of the Red Hat respins (the /etc/redhat-release file is not a reliable indicator, for example, and I don’t have confidence in the /etc/enterprise-release file always being a sufficient differentiator, either), I’ve had to rejig things a bit on the GOAL download page to accommodate these changes: OEL, basically, gets its own , unique download script, complete with cute (ish) armoured penguin icon -and if you run that OEL-specific GOAL script, everything yum-wise gets configured for you automatically. The eventual Oracle installation then just sails through to completion without a problem.

Anyway: I have been slack and rather “out of it” of late, but I’m ‘with it’ now!

You will probably have realised by now that a new version of Ubuntu (9.10, ‘Karmic Koala’) was released a couple of days ago. Whenever a new distro version hits the streets, I am compelled to work out whether or not my GOAL script has been broken: is it still a couple of mouse-clicks to install Oracle 10g or 11g on a 64-bit Linux distro, or has the change of distro version introduced problems for the Oracle installation process?

Well, it’s taken me a couple of days to work out the answers, but here they are…

GOAL and 11g Release 2 is fine. I had to make a minor adjustment to the bit where new repositories are added, because otherwise Karmic would have been updated with Jaunty packages. But that was trivial, and the end result is still that 11gR2 installs on 64-bit Karmic without linking errors of any kind. You still get the enormous list of ‘required packages not found’ displayed at one point, but a quick ‘Ignore All’ sorts all that 0ut.

But the bad news is that I cannot get 10gR2 to install on Karmic at all. I get the sort of linking errors that suggests the 32-bit compatible libc libraries haven’t been installed, but since they’re definitely in the list of packages to be installed, I’m assuming they are not yet in the Karmic repositories. Maybe it’ll all come good in a couple of weeks… and I’ll certainly be testing. But for now, 64-bit Karmic and 10g just don’t mix and GOAL doesn’t make them mix any better.

Yesterday, Centos 5.4 was made available. Visually, there’s nothing at all to distinguish it from 5.3, but quite a lot of low-level changes have taken place under the hood (as you might expect). That always raises the spectre that Oracle won’t work on the new release, of course.

Well, my GOAL script has had to be changed very slightly, because it used to check only for version ‘5.3′. It now checks for ‘5.3′ or ‘5.4′, so the revised version now works as intended.

After that, it’s plain sailing: 10.2.0.1 installed without a problem, and upgraded to 10.2.0.4 smoothly, too. I also installed 11g Release 2 via GOAL, and similarly encountered no dramas whatever. So the increment to the distro release number really doesn’t make any difference at all to the subsequnet Oracle experience, which is good news, I guess.

Here’s a funny thing. I build a Centos 5.3 x86_64 virtual machine running on my Citrix Xen Server. All works well. I enable the graphical user interface, as explained in an earlier post, and all continues to work well. I can reboot the machine as often as I like, logging in between reboots: all fine and dandy. I then download my Redhat GOAL script, to automate the preparation steps needed to get Oracle running on the new VM. The script executes perfectly well, and the ensuing Oracle installation goes swimmingly, too, as expected. Then I reboot the VM once more and get this at log on:

That is, no matter how often or correctly I type in root’s password, I merely get a “module is unknown” error. In fact, it turns out to be completely impossible to log on (as root!) in this VM ever again, which is a problem I’ve never seen before, under any circumstances. Had this been a physical machine, I’d have booted with a rescue CD and fiddled around to try and fix it, but since this was only virtual, I deleted it and built a new one. However, that doesn’t alter the fact that I could reboot and log in fine before I ran GOAL and the Oracle installation, and couldn’t afterwards. So where’s the problem?

Well, the only bit of GOAL that has anything to do with ‘modules’ as mentioned in the error message is around line 443 to 446, where it alters a configuration file in such a way that it switches on the pam_security.so pluggable authentication module, so that assorted security limits for users are enforced. It does this because that is what the Oracle installation guides tell you to do, so it’s not that GOAL is doing something weird or unexpected!

To test if those lines alone were causing all the trouble, I built a fresh Centos 5.3 64-bit VM and ran an edited version of GOAL that had just those 4 lines commented out. The result: perfectly normal reboot-and-login behaviour. Then I did manually what those four lines of bash script do: edit /etc/pam.d/login and add in the lines

session    required     /lib/security/pam_limits.so
session    required     pam_limits.so

Save the edited file and reboot. Result: module is unknown. Bingo!

So it’s definitely one or other (or maybe both!) of those two lines causing all the trouble. A couple of further edit-and-reboot experiments proved (to my satisfaction, at least!) that it was the first of these lines causing the problem. If I add just the ’session required pam_limits.so’ one, then the machine would reboot and let me log in perfectly normally. Add the line that specified the full path to the pam_limits.so file, however, and the ‘module is unknown’ error was back immediately.

Why that should be so, I have no idea -because a check before adding that line showed that the file did exist in that location:

If anything, therefore, you might have thought the line which was vague on the specifics of a file’s location would have caused more trouble than one which was precise and accurate. But apparently not!

Now, I started to wonder about this: GOAL has been working perfectly on physical servers (and virtual servers as guests in VMware Workstation) for a very long time with both these lines being added to the relevant file. Why should it suddenly stop working just because we’re suddenly using XenServer? Was it some weird interaction between a xen-enabled kernel and these pluggable authentication modules? I wasn’t even sure how to go about working out what particular element of the interaction was causing all the grief. So then I paused, backed up and had another look at the problem from a slightly different angle: maybe GOAL had been wrong all these months in the first place?

A quick review of Oracle-on-Linux installations around the Interweb at least showed I wasn’t insane: plenty of other people use the fully-qualified-path version of the pam_limits.so file as well as me. Here’s Tim Hall’s guide to a 10.2 installation on Red Hat 5, for example:

Note that he uses /lib/security/pam_limits.so, not just plain-vanilla “pam_limits.so” with no path. He does exactly the same in his installation guide for 10.1 on Red Hat 4, too. By the time he documents 11.1 and 11.2 installations onto Red Hat-like OSes, he’s telling people to use exactly the same two-versions configuration as GOAL has used all this time:

Lest it be thought I’m picking on Tim Hall’s documentation, however, let me say at once: everyone else does this, too. Here’s an article by John Smiley on Oracle’s very own website:

Spot the use of the fully-qualified filename!

The same author’s ‘install 11g‘ article was also wrong:

The funny thing about that is, as you’ll note, I can only show you the error these days by referencing the Google cache, because the original article has been corrected very recently (which is a good thing, of course).

Or there’s BDerzhavets 2007 blog post, which even appears to be a description of installing Oracle on Xen! Nevertheless, here’s the same instruction as everyone else has been giving all this time:

Once more, the use of the fully-qualified filename.

And so on and on. Pick almost any guide (see footnote 1 below!) to installing Oracle on Linux written in the past 5 years or so, and I’ll pretty much guarantee that it tells you to either include the path-specified version of pam_limits.so on its own, or to add two lines to the configuration file, one of which will be the path-specified version. So GOAL is in good company! (See footnote 2 below, too!)

The only trouble with all this good company is that we’ve all been wrong! Because if you look at the official Oracle installation guide for 10g Release 2 on Linux, it says this:

You’ll note that step 2 says nothing at all about adding two different versions of pam_limits.so -and uses the one version that does not mention a full path. And how about the official installation document for 11g Release 1 on Linux? Exactly the same thing: at the end of Section 2.8, you’re told simply to add the one path-less reference to pam_limits.so. And the same is true of the 11g Release 2 official documentation: just one reference to pam_limits.so, and no path required.

It’s interesting to think about how these documentation glitches arise in the first place -but more interesting to speculate about how they propagate themselves over the course of many years and vast numbers of installation guides! It’s the same mechanism that still has loads of guides telling you to stick a command to start the listener in your dbora script, even though that’s not been needed to automate listener startups since version 9, I guess: borrowing without referring back to the original, official documentation source, plus an understandable tendency to cut-and-paste instead of writing from first principles. Which is quite a separate issue from the question as to why advice made by GOAL and so many others to use a fully-qualified path-and-name version of the pam_limits.so file to your /pam.d/login file worked fine until it met Xen, of course, and I’m afraid I have no answer for that one. The fact is, however, that it’s GOAL and the other installation guides that are wrong, not the Xen-enabled environment. Do it the way the Oracle documentation tells you to do it and there are no problems with ‘modules not found’ at all.

All of which is an extremely long-winded way of saying that GOAL has been modified so that it now only makes the one, correct addition to the /pam.d/login file, regardless of whether you’re running virtualised or not. Which means it gets it correct on physical and virtual servers alike, as it always should have done. Apologies for not spotting my error much earlier. The correction has been applied to all four GOAL scripts, not just the Red Hat/Centos one.

Footnotes

1. I won’t list them all. But a quick bit of trivial Googling found this, this, this, this, this, this …and, well, you get the idea. All show (as at the time of writing) the use of a fully-qualified filename at the point of editing /pam.d/login. Incorrectly, as per the official doco. Naturally, a number of the authors of those sites might ’silently’ edit their work now to correct things. But the original screenshots exist, even if I haven’t bothered to show them here!

2. There are exceptions. For example, this site offers a 10g installation guide that is very non-standard in some respects… but it does manage to specify only the path-less version of the relevant edit. It was about the only guide I could find that did, though!

I’ve mentioned the fiddly nature of graphical Linux VMs on Citrix Xen Server… but I wouldn’t want you to think that graphical loveliness was particularly difficult to achieve per se on this platform. It just depends on your choice of guest operating system: pick Windows, for example -an OS that has a hard time doing anything but be graphical!- and you get a GUI straight off the installation CD or DVD:

And, as you can see, an Oracle 32-bit installation (it happened to be the only version of Vista/2008-compatible Oracle I had to hand) onto a graphical Windows 2008 64-bit Server running via Citrix Xen Server, accessed via my Windows 7 desktop is plain sailing, with no surprises. (Honestly: the mind boggles at the combination of technologies here, and the thought that not so many years ago, this would all just have been a pipe-dream!)

The significance of this particular screenshot is that I am about to convert the dev and test environments at work to virtualised servers… running on Citrix Xen Server, because it’s the only bare metal virtualisation solution that seems able to promise to come to the party with what we need to do. Our production systems are currently Windows 64-bit affairs, so it’s important to know that whatever virtualisation technology we adopt can take a direct copy of a production system and do something with it. Converting the files (of which there are many) via RMAN or doing an export/import so that we can get the data into databases running on Linux VMs are not practical options. On the other hand, I’m pushing (at an ever-opening door) to migrate all our production DB servers from Windows to Linux anyway… so our virtualisation solution for the dev and test environments has to be able to cope with running Oracle-on-Linux VMs at some point in the future, too… which Citrix Xen Server does with ease, as I’ve mentioned previously.

OK, I give in. The flood of emails has persuaded me (I call 28 “a flood”, anyway). So, if you go to the GOAL home page, you’ll find that there is now a GOAL script for 64-bit Fedora 11. Usual rules apply: default installation of the OS, except that you must choose a static IP address, switch off SELinux and disable the firewall (standard Oracle requirements, nothing to do with GOAL per se).

• The 10g Release 2 installation results in an ins_emdb.mk linking error, but it’s ignorable and everything seems to work OK afterwards.
• The 11g Release 1 prerequisites check complains that compat-libstdc++-33.i386 doesn’t exist, but it’s user-verifiable (that is, you can ignore it) because GOAL has already installed compat-libstdc++-33.i586. There are no linking errors.
• The 11g Release 2 prerequisite check complains about various missing packages… but none of them are actually missing, so an ‘Ignore All’ is all that’s required to deal with that. There are no linking errors.

One unique problem I had with Fedora is that it seems to be very aggressive in checking for updates in the background, regardless of what’s happening in the foreground! Thus, having launched GOAL and gotten it to start downloading the various package prerequisites, I was suddenly confronted with a pile of these sorts of errors:

Another app is currently holding the yum lock; waiting for it to exit...
 The other application is: PackageKit
 Memory : 152 M RSS (397 MB VSZ)
 Started: Sun Sep 13 11:07:17 2009 - 00:13 ago
 State  : Sleeping, pid: 4852

GOAL can’t download the right software packages if Fedora’s own PackageKit application is busy in the background checking to see what system updates and so on are available. One slow fix to this problem is to wait until everything’s settled down: let PackageKit do it’s thing and be patient before even thinking about running GOAL. But the fix I deployed was, perhaps, a little ‘aggressive’ in its own way: open another terminal, become root, and then kill -9 4852 (or whatever pid is listed in the last line of that error message). Messy, but effective. Another reason I don’t like Fedora, though.

And therefore this is definitely it: no more distros! Ever!! Probably.

Incidentally, it is quite OK to email me, and I’m a little surprised if anyone ever thought differently. That I don’t post to forums, other people’s blogs or give access to my old technical advice articles is a result of the behaviour of a tiny handful of people. My problems with those few individuals (unfortunately, some of the loudest in the Oracle community, though not often the most technically able) don’t mean I’ve become a hermit or blown up my email server. So long as you’re not one of the problem ones, emailing me is just fine… though I don’t do technical support by email!

I have tidied up GOAL and moved things around on the site a little in order to accommodate the changes/improvements. Thus GOAL acquires an entire page all of its own under the ‘Oracle Articles’ banner you see above. If you prefer a simple hyperlink, that’s here!

When you get there, you’ll notice that there are now three GOAL variants: the original for Red Hat (and its equivalents, like Centos); a version just for doing the same thing on openSuse 11.1; and an Ubuntu-specific version (I guess that last one will work for Kubuntu, Xubuntu and the entire *buntu family, come to that… but Ubuntu is the only one I’ve tested it on).

There are still a couple of glitches and gotchas with the two new variants, but nothing terminal as far as getting a functional database at the end of the installation process is concerned. I’ll continue to work on those minor issues over the next few days until (hopefully) they are ironed out.

Hope it all makes sense!