As with any upgrade/major installation I've ever made (at least as far as I can remember), after upgrading the database to 10g the application have demonstrated some interesting errors. One of the problems is related to concurrents and the executables they spawn crashing in mid-air - promise to tell about this in more detail when I myself have any idea.
At some point I suspected that the problem was with custom code running cmd scripts. Since some of those scripts are called from within a PL/SQL code using a Java stored procedure I thought that maybe I should try and use a "less custom" way to do that.
Luckily (or unluckily), I just read the previous week about a new feature in 10g - the dbms_scheduler package that's supposed to replace dbms_job and to be much more powerful, for instance it enables you to run cmd scripts. So I thought I'll try this out since it sounds exactly like the built-in method I was looking for.
Well, I have only one thing I can say about this: it's better to leave a feature out of the release than to keep it in when it sucks, totally.
Really, my keyboard is still soaked with sweat from my efforts to run a single script that echoes some text.
I've already written about this but I guess the message didn't get through, if I'm supposed to start the Oracle Scheduler service to run jobs then that's exactly what I expect to be written in the error message I get when not doing so - certainly not a "file not found error". I also expect this to be written in any (well, at least some) documentation describing the new feature, that's not the kind of things one supposed to dig up only in forums. By the way, some hilarious problem with similar symptoms I read about while searching for a solution to my own issue - I've actually tried this out since at first I thought this was the problem I was experiencing - it appears that for some users (maybe in earlier 10g versions) just supplying a cmd script didn't work, they had to run cmd.exe with parameters. Can't even begin to understand how you manage to create this bug and release it.
Well, after completing the POC (if at a the cost of health) I got to the real thing running a script with parameters. In some document I saw something like the following example:
dbms_scheduler.create_job(job_name => 'JOB', job_type => 'EXECUTABLE',
job_action => 'script.cmd', number_of_arguments => n);
dbms_scheduler.set_job_argument_value(job_name => 'JOB',
argument_position => 1, argument_value => '...');
...
dbms_scheduler.set_job_argument_value(job_name => 'JOB',
argument_position => n, argument_value => '...');
dbms_scheduler.enable (name => 'JOB');
dbms_scheduler.run_job(job_name => 'JOB');
Well, maybe it's just my system that is a freak but apparently the enable procedure erases the job. Exactly, I create a job, I run dbms_scheduler.enable and no job in the table - no error message, no nothing, I might have run drop_job instead. Well, apparently I don't need that line anyway. After some more struggle with cryptic error messages I got the package to do what I wanted it to do, but that's really not good enough.
I can't even start to imagine what kind of efforts are needed to bootstrap all the advanced scheduling features - windows, chains etc..
I'm willing to bet money on the fact that most developers would have given up much earlier than I did saying this stuff just doesn't work. You can develop useful and cool new features all you like but if you can't cut/paste an example and see it just working no one would use it, if all users get when something is wrong are misleading error messages they'll just get frustrated (and you can see I am one such user) .
I first read about this feature in a "10g Top 20 New Features" document, and indeed it sounded great but if it's impossible to use it, it's not really a new feature at all.
I'm sometimes not sure if I should account all those funny errors I deal with to the fact that my system is on MS Windows - a less common platform, maybe I should. But that's not a good enough reason, if I'm in possession of a disk labeled 10.2.0.3 for Windows I expect it to work, I don't really mind having it released half a year later than the corresponding Linux version, I just want it to function.
As for me, I know about this feature, I can even make it work, but there's no chance I'll suggest it as a solution to any need unless as a last resort. Too bad.
Saturday, May 3, 2008
Thursday, April 24, 2008
Upgrading Part I: Human Error
In my previous post I've talked about a problem I experienced before upgrading my EBS database to 10g, well, the upgrade has been performed and I think it has supplied me with ideas for at least three posts. Of course the bug experienced on the test environment didn't show itself on production (I didn't make the fix intentionally to see if it re-occurs), and what I have now is a very "nice" variety of other bugs. But why rush to the aftermath? Lets start with the upgrade process itself.
I had all the upgrade procedure in soft and hard copies, have made about 4 upgrades on dev and test environments, was pretty sure I would successfully handle any problem that will pop up. Actually, many steps of the upgrade process came down to a Copy-Paste action from my upgrade document to the command-line. It's so frustrating that what eventually got me delayed for about five(!) hours is a totally stupid mistake on my behalf.
Of course, before such an upgrade a backup should be taken, I have a script that runs every night: it brings down the system, a backup is taken (we use EMC's BCV) and then the system is brought up, all I had to do is re-schedule this script. Being a smart ass I've decided to save some time and disable the startup script, I only need the database up for the upgrade process. I could think of plenty ways to do this but decided that emptying the startup script will be the most secure - this way I'm not touching the main backup script. So I've made a copy of the startup script and... emptied the shutdown script. Yes you read it right, no typo.
So the time comes, I'm watching the backup logs and WOW! that was fast, usually it takes about five minutes for the shutdown process to complete and now the system is up after one minute, great! Hmmm... maybe not so great after all. So I take a closer look at the logs and discover what I have just done, the meaning is that the BCV split was performed on an open database and it's worth nothing (well, maybe not nothing but that's a bad idea to start the upgrade with this kind of backup). Oh, I'm going to use some EMC terms but it's OK since many of them I myself discovered only during this upgrade.
Well, actually there's no reason for panic, the backup devices are mostly synchronized and all I have to do is shutdown the database and take the backup again. I've called the guy in charge of storage and asked him if I'm right, he said I'm right and guided me through the process of taking the backup from the command line. But, alas, That's not so simple!
Apparently when EMC tells you that a split is complete, well, it's lying. The split process (again, if you have no idea what I'm talking about, just imagine some backup process) continues in the background and until it's completed you can't take another backup. The more frustrating thing is that the storage guy didn't know about this (he's new, cut him some slack) and I didn't know how to check the progress of this background split. At some point - when only one disk was still splitting and the others were already re-synchronizing - I've discovered how to check the progress of the split. Great, I'm 99%, but boy this 1% took it's share of time to complete.
Phew! At least now I know where I'm standing. Well not exactly, EMC has another "great" feature, when it comes to synchronizing the last parts of disks it take a totally disproportional time (remember the 1%), so when there were only 50MB left to synchronize the machine had to work forever for every 0.1MB, no kidding. So I didn't really have any estimation as to when I'll finally start the upgrade process and I was already about 3.5 hours late, and this is a downtime we're talking about here, we usually like to minimize those.
So I waited. I guess this is what they call "paying for your mistakes".
Eventually the synchronization completed and I got to the upgrade process. The upgrade was perfect, no unexpected errors and the entire process was completed (long) before scheduled time - thank God for buffers.
Well, a nice prelude indeed...
I had all the upgrade procedure in soft and hard copies, have made about 4 upgrades on dev and test environments, was pretty sure I would successfully handle any problem that will pop up. Actually, many steps of the upgrade process came down to a Copy-Paste action from my upgrade document to the command-line. It's so frustrating that what eventually got me delayed for about five(!) hours is a totally stupid mistake on my behalf.
Of course, before such an upgrade a backup should be taken, I have a script that runs every night: it brings down the system, a backup is taken (we use EMC's BCV) and then the system is brought up, all I had to do is re-schedule this script. Being a smart ass I've decided to save some time and disable the startup script, I only need the database up for the upgrade process. I could think of plenty ways to do this but decided that emptying the startup script will be the most secure - this way I'm not touching the main backup script. So I've made a copy of the startup script and... emptied the shutdown script. Yes you read it right, no typo.
So the time comes, I'm watching the backup logs and WOW! that was fast, usually it takes about five minutes for the shutdown process to complete and now the system is up after one minute, great! Hmmm... maybe not so great after all. So I take a closer look at the logs and discover what I have just done, the meaning is that the BCV split was performed on an open database and it's worth nothing (well, maybe not nothing but that's a bad idea to start the upgrade with this kind of backup). Oh, I'm going to use some EMC terms but it's OK since many of them I myself discovered only during this upgrade.
Well, actually there's no reason for panic, the backup devices are mostly synchronized and all I have to do is shutdown the database and take the backup again. I've called the guy in charge of storage and asked him if I'm right, he said I'm right and guided me through the process of taking the backup from the command line. But, alas, That's not so simple!
Apparently when EMC tells you that a split is complete, well, it's lying. The split process (again, if you have no idea what I'm talking about, just imagine some backup process) continues in the background and until it's completed you can't take another backup. The more frustrating thing is that the storage guy didn't know about this (he's new, cut him some slack) and I didn't know how to check the progress of this background split. At some point - when only one disk was still splitting and the others were already re-synchronizing - I've discovered how to check the progress of the split. Great, I'm 99%, but boy this 1% took it's share of time to complete.
Phew! At least now I know where I'm standing. Well not exactly, EMC has another "great" feature, when it comes to synchronizing the last parts of disks it take a totally disproportional time (remember the 1%), so when there were only 50MB left to synchronize the machine had to work forever for every 0.1MB, no kidding. So I didn't really have any estimation as to when I'll finally start the upgrade process and I was already about 3.5 hours late, and this is a downtime we're talking about here, we usually like to minimize those.
So I waited. I guess this is what they call "paying for your mistakes".
Eventually the synchronization completed and I got to the upgrade process. The upgrade was perfect, no unexpected errors and the entire process was completed (long) before scheduled time - thank God for buffers.
Well, a nice prelude indeed...
Wednesday, April 9, 2008
The Persistent Bug
My system has a bug that has been around long before I became the sysadmin, it reincarnates each time in a slightly different form, but for me it's the same old bug.
I call it "the -107 bug" because it's main phenomena is an error message in the concurrent log that specifies a long number - -107... (I don't really remember all the digits, there are lots of them), it looks to me like a really big integer going over to the negative side or something like this, but I don't really know.
I was first introduced to this bug after installing some major patch (maybe ATG_PF.H) when QA alerts started failing in every execution, then, other (with no pattern I could pinpoint) concurrents started to fail with this error as well - but not all the time.
I've opened two separate SRs to handle those issues and over time some actions have been made to improve the situation and eventually a patch that was supposed to fix the problem for the concurrents had pretty much fixed the problem for the QA alerts. In the last half year or so those errors became really rare.
But then, yesterday it hit again. During the testing session before upgrading the production to a 10g database, QA alerts started to fail with this error (although sometimes doing their job), eventually the errors have stabilized themselves to target only specific transactions. Getting desperate I came to the point of telling my boss I believe the best action plan is to perform the upgrade anyway, I had several arguments:
1. This bug might skip the production.
2. We have a project that will alter the QA methodology to a new one in which this specific action won't be performed.
3. I know this long enough to know that it won't be solved in a reasonable time frame.
4. Schedule considerations.
But something was different this time, the pattern was too consistent, usually this bug is unpredictable and actually never was reproduced on a dev environment. Indeed, a few minutes after my conversation with the boss, one of the team members found the problem - total voodoo crap if you ask me.
The INSERT action this specific alert performs had a section that read "-1*&some_parameter" changing it to "-&some_parameter" solved the issue.
OK, let's say the new and shiny 10g doesn't know how to digest a -1*x expression, but the alert won't fail when it's the only one triggered as part of a transaction (I don't really know the technicalities of this whole process so I hope my explanations are clear enough), it only fails when other alerts are triggered as well.
Anyway, I guess that's another score for me in the endless struggle against this stubborn bug. On the other hand I'm pretty embarrassed, I did tell my boss there's no way to solve the issue...
I call it "the -107 bug" because it's main phenomena is an error message in the concurrent log that specifies a long number - -107... (I don't really remember all the digits, there are lots of them), it looks to me like a really big integer going over to the negative side or something like this, but I don't really know.
I was first introduced to this bug after installing some major patch (maybe ATG_PF.H) when QA alerts started failing in every execution, then, other (with no pattern I could pinpoint) concurrents started to fail with this error as well - but not all the time.
I've opened two separate SRs to handle those issues and over time some actions have been made to improve the situation and eventually a patch that was supposed to fix the problem for the concurrents had pretty much fixed the problem for the QA alerts. In the last half year or so those errors became really rare.
But then, yesterday it hit again. During the testing session before upgrading the production to a 10g database, QA alerts started to fail with this error (although sometimes doing their job), eventually the errors have stabilized themselves to target only specific transactions. Getting desperate I came to the point of telling my boss I believe the best action plan is to perform the upgrade anyway, I had several arguments:
1. This bug might skip the production.
2. We have a project that will alter the QA methodology to a new one in which this specific action won't be performed.
3. I know this long enough to know that it won't be solved in a reasonable time frame.
4. Schedule considerations.
But something was different this time, the pattern was too consistent, usually this bug is unpredictable and actually never was reproduced on a dev environment. Indeed, a few minutes after my conversation with the boss, one of the team members found the problem - total voodoo crap if you ask me.
The INSERT action this specific alert performs had a section that read "-1*&some_parameter" changing it to "-&some_parameter" solved the issue.
OK, let's say the new and shiny 10g doesn't know how to digest a -1*x expression, but the alert won't fail when it's the only one triggered as part of a transaction (I don't really know the technicalities of this whole process so I hope my explanations are clear enough), it only fails when other alerts are triggered as well.
Anyway, I guess that's another score for me in the endless struggle against this stubborn bug. On the other hand I'm pretty embarrassed, I did tell my boss there's no way to solve the issue...
Saturday, March 29, 2008
Wake Up Call
Growing up is the process of discovering that all the great stuff you believe in are just fictions and illusions. Santa isn't real, your daddy doesn't really know everything, guys who write Tetris(Tris) for the iPhone don't really block the possibility of moving the pieces upwards and real-world software isn't really developed and tested like it's described here.
Actually, I've just experienced an example of what I'm talking about in those few first lines of this post. How do you place a link in a post? Very simple, you mark some text, push the link button and enter the desired URL. But then, everything you type after the link is considered part of the link. My way of overcoming this obstacle is to edit the HTML code directly to get outside the link tag, I'll really appreciate simpler solutions. Now, I AM using Blogger and it's pretty good but this little bug is just pathetic.
When I was younger I really thought that these major software products (except Microsoft that always was a subject for mockery) are written and tested to the last bit and that they really do work - otherwise, why would someone use and even pay for them?
But when dealing with the internals of software like a sysadmin frequently does, you sometimes discover code sections that obviously weren't tested and maybe even not thought about for a single second. Just some of the examples I've encountered: code that exits instead of returning an error code, irrelevant localization code that slows down performance tremendously, code with zero error handling and much more.
It's a little sad, like every myth you discover is only a myth.
But in second thought, that's why guys like me have a job.
Actually, I've just experienced an example of what I'm talking about in those few first lines of this post. How do you place a link in a post? Very simple, you mark some text, push the link button and enter the desired URL. But then, everything you type after the link is considered part of the link. My way of overcoming this obstacle is to edit the HTML code directly to get outside the link tag, I'll really appreciate simpler solutions. Now, I AM using Blogger and it's pretty good but this little bug is just pathetic.
When I was younger I really thought that these major software products (except Microsoft that always was a subject for mockery) are written and tested to the last bit and that they really do work - otherwise, why would someone use and even pay for them?
But when dealing with the internals of software like a sysadmin frequently does, you sometimes discover code sections that obviously weren't tested and maybe even not thought about for a single second. Just some of the examples I've encountered: code that exits instead of returning an error code, irrelevant localization code that slows down performance tremendously, code with zero error handling and much more.
It's a little sad, like every myth you discover is only a myth.
But in second thought, that's why guys like me have a job.
Thursday, March 20, 2008
New Technology
Usually my tasks as a sysadmin are pretty standard: upgrade the database, fix the bug, deal with the backup issues and so on. While I might have not upgraded any production database yet, I still pretty much know what I'm dealing with.
But every once in a while I'm required to deal with something (almost) totally new. For the last couple of weeks my team leader and me have been working on implementing EUS - or more simply: database user authentication via OID using Kerberos. While we both pretty much know our OID, we haven't implemented this specific solution before (at least not one that worked :-)).
I really like those new challenges, I learn something new each day, but this kind of stuff makes you feel like you can add a new bullet in your CV. Like my team leader mentioned, it makes you want to work only on this new and shiny "toy" until you finally get it up and running and forget about the more mundane tasks. Unfortunately, those mundane tasks are often more pressing and so we end up working between other tasks and making little (if in a steady pace) progress each time. Well, life's tough. At least we ARE making progress...
But every once in a while I'm required to deal with something (almost) totally new. For the last couple of weeks my team leader and me have been working on implementing EUS - or more simply: database user authentication via OID using Kerberos. While we both pretty much know our OID, we haven't implemented this specific solution before (at least not one that worked :-)).
I really like those new challenges, I learn something new each day, but this kind of stuff makes you feel like you can add a new bullet in your CV. Like my team leader mentioned, it makes you want to work only on this new and shiny "toy" until you finally get it up and running and forget about the more mundane tasks. Unfortunately, those mundane tasks are often more pressing and so we end up working between other tasks and making little (if in a steady pace) progress each time. Well, life's tough. At least we ARE making progress...
Saturday, March 15, 2008
Overtraining
Metalink is one of the most useful tools for an Apps' DBA, you can search it for articles or upgrade notes, download patches and search for bug resolution instructions.
The first time you search an error message and get many results, you go over nearly every note, you don't really know to distinguish the relevant from the irrelevant and since you're probably new to the job you're afraid to miss the desired solution. Over time you develop a sense for what is relevant, even if the note doesn't mention the exact error message, you sometimes know that the issue is the same. On the other hand, just like with machine learning, you might get a bit too confident in your ability to distinguish relevant from irrelevant and miss valuable notes.
That's what happened to me this week. I was looking for a solution to a problem with an engineering form, I found a note that mentioned a patch that was supposed to install a new version of the form, but applying the patch didn't upgrade the form. So after doing a bit more searching I've asked from one of the analysts to log an SR (I wouldn't really know how to describe an engineering issue). Luckily, he was wise enough to search Metalink himself first, and indeed he've stumbled upon a similar issue in which it was suggested to make a full installation of the engineering application. Now that I think about it, it's trivial and probably the first thing I should've thought about - a form might not get installed if the application is only in shared installation mode.
Anyway, this fresh perspective have saved logging an SR and probably a couple of days work.
The first time you search an error message and get many results, you go over nearly every note, you don't really know to distinguish the relevant from the irrelevant and since you're probably new to the job you're afraid to miss the desired solution. Over time you develop a sense for what is relevant, even if the note doesn't mention the exact error message, you sometimes know that the issue is the same. On the other hand, just like with machine learning, you might get a bit too confident in your ability to distinguish relevant from irrelevant and miss valuable notes.
That's what happened to me this week. I was looking for a solution to a problem with an engineering form, I found a note that mentioned a patch that was supposed to install a new version of the form, but applying the patch didn't upgrade the form. So after doing a bit more searching I've asked from one of the analysts to log an SR (I wouldn't really know how to describe an engineering issue). Luckily, he was wise enough to search Metalink himself first, and indeed he've stumbled upon a similar issue in which it was suggested to make a full installation of the engineering application. Now that I think about it, it's trivial and probably the first thing I should've thought about - a form might not get installed if the application is only in shared installation mode.
Anyway, this fresh perspective have saved logging an SR and probably a couple of days work.
Saturday, March 8, 2008
Less is More
This week I had to bring back to life an environment I'd kept "frozen" for more than a year because of a revived project, kinda like Demolition Man. I've cleared a server to host this environment and thought I had a creative idea:
Nowadays I use NetApp storage (through iScsi) for this server, mainly because of the snapshot option, but this server has also a local disk that can accommodate the database. So I've decided to use the local disk - I get to keep the environment I had on this server before, the robocopy is much faster and I won't need snapshots for this environment anyway since it's for reference purposes only, not for development.
Do you know this guy Murphy?
A few days after the environment was up I've discovered that a couple of ApEx applications that were developed on this environment weren't kept anywhere else, so I had to export them and then import to the environment where the rest of project will be developed. Yes, I know, someone has real bad development practices, but, as my boss told me, part of my job is to clean the mess others make. So I've tried to open ApEx - nothing, a blank page. Now, you see, when those ApEx applications were developed ApEx was relatively new, actually, this old environment had an ApEx 2.0 (when it was still referred to as HTMLDB) schema as well as an ApEx 2.2 schema, and the only version to ever reach production was 2.2, so at first I thought there might be some misconfiguration of the iAS server or something like this. After exhausting the few ideas I had regarding misconfiguration I've tried to export the applications using APIs and got a strange error message about a missing datafile.
Well, you learn new things each day, it turns out that even if you can see the tablespace's definition and see the package specs for packages in it, it doesn't necessarily mean that the datafile for the tablespace is present.
It turns out that when I recreated the control file for the database I didn't have the datafile for the ApEx tablespace in the template script (remember it was new at the time). I'd expect the database to shout at me something like "man! you're missing a datafile" instead of just generating some nonexistent path for the missing datafile and going around acting like everything's ok.
God I wish I'd used the NetApp option.
I had two options:
1. Try to re-integrate the additional datafile into the database.
2. Restore the database again.
Now I get to the meaning of the title of this post. Re-integrating the datafile, if successful, will save precious copy time and giving up on a challenge is against my nature (and against this of another DBA from my team who tried to help me). But, during copy time I can do other things and this re-integration thing didn't look like it has high odds, and so we would probably end up with the second option anyway.
So, what I'm trying to say here is that being a good sysadmin doesn't always mean you have to solve any single problem, sometimes you just have to pick the more efficient course of action even if it is the more trivial one, even if it means you leave a problem unsolved. In life you have to pick your fights wisely.
Nowadays I use NetApp storage (through iScsi) for this server, mainly because of the snapshot option, but this server has also a local disk that can accommodate the database. So I've decided to use the local disk - I get to keep the environment I had on this server before, the robocopy is much faster and I won't need snapshots for this environment anyway since it's for reference purposes only, not for development.
Do you know this guy Murphy?
A few days after the environment was up I've discovered that a couple of ApEx applications that were developed on this environment weren't kept anywhere else, so I had to export them and then import to the environment where the rest of project will be developed. Yes, I know, someone has real bad development practices, but, as my boss told me, part of my job is to clean the mess others make. So I've tried to open ApEx - nothing, a blank page. Now, you see, when those ApEx applications were developed ApEx was relatively new, actually, this old environment had an ApEx 2.0 (when it was still referred to as HTMLDB) schema as well as an ApEx 2.2 schema, and the only version to ever reach production was 2.2, so at first I thought there might be some misconfiguration of the iAS server or something like this. After exhausting the few ideas I had regarding misconfiguration I've tried to export the applications using APIs and got a strange error message about a missing datafile.
Well, you learn new things each day, it turns out that even if you can see the tablespace's definition and see the package specs for packages in it, it doesn't necessarily mean that the datafile for the tablespace is present.
It turns out that when I recreated the control file for the database I didn't have the datafile for the ApEx tablespace in the template script (remember it was new at the time). I'd expect the database to shout at me something like "man! you're missing a datafile" instead of just generating some nonexistent path for the missing datafile and going around acting like everything's ok.
God I wish I'd used the NetApp option.
I had two options:
1. Try to re-integrate the additional datafile into the database.
2. Restore the database again.
Now I get to the meaning of the title of this post. Re-integrating the datafile, if successful, will save precious copy time and giving up on a challenge is against my nature (and against this of another DBA from my team who tried to help me). But, during copy time I can do other things and this re-integration thing didn't look like it has high odds, and so we would probably end up with the second option anyway.
So, what I'm trying to say here is that being a good sysadmin doesn't always mean you have to solve any single problem, sometimes you just have to pick the more efficient course of action even if it is the more trivial one, even if it means you leave a problem unsolved. In life you have to pick your fights wisely.
Subscribe to:
Posts (Atom)