No, no, not the Brangelina twins announcement, the numactl output.
$ ps -ef | grep oracle
oracle 13771 2418 0 Jun24 pts/0 00:00:00 -ksh
oracle 13772 13771 0 09:24 pts/0 00:00:00 ksh -i
oracle 13775 13772 0 09:24 pts/0 00:00:00 ps -ef
oracle 13776 13772 0 09:24 pts/0 00:00:00 grep oracle
$ numactl --hardware
available: 4 nodes (0-3)
node 0 size: 10239 MB
node 0 free: 7854 MB
node 1 size: 8191 MB
node 1 free: 5725 MB
node 2 size: 8191 MB
node 2 free: 6757 MB
node 3 size: 8191 MB
node 3 free: 6671 MB
OK, so maybe not everybody was waiting for that. Oracle Support requested an strace of the startup command, so I had to bring the db down anyway. The strace was a good idea, they'll be able to see the system calls being made and such. Maybe we'll get some progress yet...
If you're following along at home: Part I, Part II, Part III
More info from the comments:
I shutdown everything again:
available: 4 nodes (0-3)
node 0 size: 10239 MB
node 0 free: 7854 MB
node 1 size: 8191 MB
node 1 free: 4846 MB
node 2 size: 8191 MB
node 2 free: 6744 MB
node 3 size: 8191 MB
node 3 free: 6646 MB
No surprises there (except, of course, for the strangeness KC pointed out before). Then I decided to startup nomount with a 10.2.0.3 $OH and a 16g SGA:
available: 4 nodes (0-3)
node 0 size: 10239 MB
node 0 free: 7660 MB
node 1 size: 8191 MB
node 1 free: 4315 MB
node 2 size: 8191 MB
node 2 free: 6708 MB
node 3 size: 8191 MB
node 3 free: 6646 MB
Very unexpected. It looks like whatever this is showing is not affected by a 16g SGA.
I changed my $OH back to 10.2.0.4 and started a 12g SGA:
available: 4 nodes (0-3)
node 0 size: 10239 MB
node 0 free: 25 MB
node 1 size: 8191 MB
node 1 free: 257 MB
node 2 size: 8191 MB
node 2 free: 6740 MB
node 3 size: 8191 MB
node 3 free: 6642 MB
This is kind of more what I expected. You see a 12g SGA is basically leaving nodes 0 and 1 free at next to nothing. If we believe the previous output, that's my 12g of RAM?
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
Wednesday, July 02, 2008
Tuesday, June 24, 2008
Part III
I escalated my issue to a duty manager and explained that in my environment those types of changes are very difficult. He kicked it back to development for alternate solutions.
In the background, I managed to secure a test box for a couple hours that I could play with. While researching how to turn numa=off, I ran across this on Redhat's site:
Hmm, that's fishy. Why would they ask me to turn it off if the default value is off? So I did it anyway.
Same thing, good old ORA-27102: out of memory.
As long as I had the box booted with numa=off, I might as well try _enable_numa_optimization=false, right? No dice, same error.
Last, but not least, I tried their suggestion of setting vm.nr_hugepages=0 in the sysctl.conf. But I found out that's already my value:
sysctl -a | grep vm.nr_huge
vm.nr_hugepages = 0
OK, so now what?
Maybe 11g is an option...
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
In the background, I managed to secure a test box for a couple hours that I could play with. While researching how to turn numa=off, I ran across this on Redhat's site:
The Red Hat Enterprise Linux 4 Update 1 kernel automatically disables NUMA optimizations (numa=off) by default on systems using the AMD64 dual core processor. This ensures stable operation across multiple systems where each has a different system BIOS implementation for reporting dual core processors.
Hmm, that's fishy. Why would they ask me to turn it off if the default value is off? So I did it anyway.
Same thing, good old ORA-27102: out of memory.
As long as I had the box booted with numa=off, I might as well try _enable_numa_optimization=false, right? No dice, same error.
Last, but not least, I tried their suggestion of setting vm.nr_hugepages=0 in the sysctl.conf. But I found out that's already my value:
sysctl -a | grep vm.nr_huge
vm.nr_hugepages = 0
OK, so now what?
Maybe 11g is an option...
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
Monday, June 23, 2008
Something to be aware of, Part II
We've passed the one month anniversary for Something to be aware of (10.2.0.4) and still no meaningful results. I've been going back and forth with Oracle Support about switching NUMA off. In a synopsis:
My next step is talking to a Duty manager. I really don't want to do that.
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
OS: Switch NUMA off and retest.
Me: But that never changed.
OS: Switch NUMA off and retest.
Me: We take kernel changes very seriously. It will take approximately 2 months to re-certify that configuration. Besides, that NUMA didn't change.
OS: Switch NUMA off and retest.
Me: Please direct me to the note that says 10.2.0.4 requires NUMA changes.
OS: Switch NUMA off and retest.
Me: I've already proven 10.2.0.3 works as expected in this configuration, NUMA settings never changed.
OS: Switch NUMA off and retest.
My next step is talking to a Duty manager. I really don't want to do that.
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
Thursday, June 19, 2008
Backing up at Home
I'm pretty particular about my backups at work. In fact, we have a setup where if a nightly backup fails, we get a page in the middle of the night so we can address the problem.
At home, it's a different story. I've got a Windows 2000 PC that I haul out the CD-R's a couple times a year and backup what I think are the important files. At first it wasn't so bad, a couple CD's and I was done. But pictures from a digital camera tend to eat up space at a pretty decent rate. The last backup I did was 18 CD-R's.
About the same time, I got the opportunity to help someone with their own backup issues. They were getting ready to retire a computer and wanted a way to move about 60G of data from one machine to another. So I investigated some options and found a nice 320G SimpleTech USB drive at Costco. We setup the drive and the ArcSoft TotalMedia Backup in about 20 minutes and completed a test backup in another hour or so.
I was so impressed, that I bought one myself. For $89, I just couldn't go wrong.
So far, my impression is TotalMedia can get the basics done. However, as a backup professional I need the ability to tweak things. I'm still doing a backup every day, but looking at other options. Stay tuned for updates.
At home, it's a different story. I've got a Windows 2000 PC that I haul out the CD-R's a couple times a year and backup what I think are the important files. At first it wasn't so bad, a couple CD's and I was done. But pictures from a digital camera tend to eat up space at a pretty decent rate. The last backup I did was 18 CD-R's.
About the same time, I got the opportunity to help someone with their own backup issues. They were getting ready to retire a computer and wanted a way to move about 60G of data from one machine to another. So I investigated some options and found a nice 320G SimpleTech USB drive at Costco. We setup the drive and the ArcSoft TotalMedia Backup in about 20 minutes and completed a test backup in another hour or so.
I was so impressed, that I bought one myself. For $89, I just couldn't go wrong.
So far, my impression is TotalMedia can get the basics done. However, as a backup professional I need the ability to tweak things. I'm still doing a backup every day, but looking at other options. Stay tuned for updates.
Monday, June 16, 2008
Microsoft Vista Oy Vey!
I needed a new laptop for my home. OK, maybe "need" is not the correct word, but you get my picture.
So I did what I always do. I navigated to dell.com and purchased a Inspiron 1525 with a 15" screen, 4G of RAM and Microsoft Windows Vista Home. I went through all the normal setup things and had it on my wireless network in about 15 minutes.
So far, so good. Then I tried to add a network printer [Accept or Deny] that was attached [Accept or Deny] to my W2K desktop. Since W2K doesn't serve the drivers like XP, I had to install the drivers [Accept or Deny] for my printer before I could print. Then I had to tweak my firewall settings [Accept or Deny] so that I could print to my network printer. I used to think that the Apple Commercial was exaggerating, but it's not.
Then, to add insult to injury, I tried to print a document. First I had to activate Microsoft Word, which was simple. I typed in some text and...
Wait a second, where's the "save" button...
And what happened to the "File" menu...
Arg, even "Help" isn't on the menu any more.
I finally figured out that you have to click that stupid looking button at the top before you can "Save", "Print", "Open" or do anything you are used to.
Great way to enhance my productivity. Now it takes me 5 minutes to open and print a document instead of two mouse clicks. Word 2007 is a step backwards.
So I did what I always do. I navigated to dell.com and purchased a Inspiron 1525 with a 15" screen, 4G of RAM and Microsoft Windows Vista Home. I went through all the normal setup things and had it on my wireless network in about 15 minutes.
So far, so good. Then I tried to add a network printer [Accept or Deny] that was attached [Accept or Deny] to my W2K desktop. Since W2K doesn't serve the drivers like XP, I had to install the drivers [Accept or Deny] for my printer before I could print. Then I had to tweak my firewall settings [Accept or Deny] so that I could print to my network printer. I used to think that the Apple Commercial was exaggerating, but it's not.
Then, to add insult to injury, I tried to print a document. First I had to activate Microsoft Word, which was simple. I typed in some text and...
Wait a second, where's the "save" button...
And what happened to the "File" menu...
Arg, even "Help" isn't on the menu any more.
I finally figured out that you have to click that stupid looking button at the top before you can "Save", "Print", "Open" or do anything you are used to.
Great way to enhance my productivity. Now it takes me 5 minutes to open and print a document instead of two mouse clicks. Word 2007 is a step backwards.
Tuesday, May 13, 2008
Something to be aware of (10.2.0.4)
My standard install of 10.2.0.3 included 23 patches at last count. When the 10.2.0.4 patchset came out a few weeks ago I checked it and decided that since my 23 patches were included in 10.2.0.4, it would be a good idea to upgrade to that version.
I first got underway in my development environment on x86_64. I have several small dbs with 2G SGAs on a single box. The patch/upgrade process went flawless and I encountered no issues at all during the upgrade.
I usually like to run the patch in development for a month or so before I apply it to my production enviornment. However, I was fighting a nasty bug on one of my 10.2.0.3 production dbs that was fixed in 10.2.0.4 but a backport wasn't available to 10.2.0.3 at the time. So after a week in development, I decided to upgrade that production db to 10.2.0.4. That db usually runs with a 16G SGA and when I tried to start the instance with the 10.2.0.4 software, I got the venerable:
I first got underway in my development environment on x86_64. I have several small dbs with 2G SGAs on a single box. The patch/upgrade process went flawless and I encountered no issues at all during the upgrade.
I usually like to run the patch in development for a month or so before I apply it to my production enviornment. However, I was fighting a nasty bug on one of my 10.2.0.3 production dbs that was fixed in 10.2.0.4 but a backport wasn't available to 10.2.0.3 at the time. So after a week in development, I decided to upgrade that production db to 10.2.0.4. That db usually runs with a 16G SGA and when I tried to start the instance with the 10.2.0.4 software, I got the venerable:
SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device
OK, so I know that usually means that my kernel parameters are off somewhere. It was a Saturday and I didn't have a sysadmin available, so I bumped the SGA down to 12G and the instance started right away and upgraded fine. I figured there was some shared memory parameter that I would research on Monday and we'd be back in business the next week.
I looked at Note 301830.1 thinking maybe my shmall paramter was off, but sure enough I had enough configured. I submitted a TAR and basically let Oracle Support stew on it for a few days.
In the meantime, another DBA in my group got the task to setup a new x86_64 box with 10.2.0.4 so we can move a db. He got the software installed OK, but couldn't start an instance with an SGA of more than about 2G of memory. The kernel parameters were exactly the same as my other box, but he still couldn't start an instnace with any decent amount of memory.
This was just too co-incidental. So we played around with the 10.2.0.4 installation a little more and found that 10.2.0.4 was allocating multiple shared memory segments instead of just one big segment. I used ipcs to find my shmid for the SGA and then used pmap to find out that to allocate a 2G SGA, 10.2.0.4 used about 260 shared memory segments of between 15M and 5M.
Then we installed 10.2.0.3 and tried to start with the SAME EXACT parameter file, and sure enough, one shared memory segment of 2G. In fact, we could allocate almost all the way up to our shmall in one segment.
I'm bug 7016155 and I know others have run into this problem as well. I'm sure bug 7019967 think's he's alone in this, but he's not. As usual, my bug has been sitting out there for about 12 days and nobody has looked at it.
Part II
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
Then we installed 10.2.0.3 and tried to start with the SAME EXACT parameter file, and sure enough, one shared memory segment of 2G. In fact, we could allocate almost all the way up to our shmall in one segment.
I'm bug 7016155 and I know others have run into this problem as well. I'm sure bug 7019967 think's he's alone in this, but he's not. As usual, my bug has been sitting out there for about 12 days and nobody has looked at it.
Part II
Click the 10.2.0.4 label below to follow all threads on this issue and the eventual solution.
Thursday, April 10, 2008
RMAN-08137 Followup
Before I got a chance to apply my 10.2.0.4 patch, my problem fixed itself! Well, maybe not fixed itself, but certainly went away.
As I was reviewing my backup log from last night, I noticed that my RMAN-08137 error was gone. I didn't do anything different nor did I change anything. In fact, yesterday's backup had the same ORA-08137 error just as I had on the last 12 days.
So what was different? My control_file_record_keep_time was set to 1, so any record of my archived log would surely be gone way before now, right?
No, the control_file_record_keep_time parameter specifies the minimum number of days the recyclable data will stay in the controlfile, not an absolute limit. Sure enough, although my control_file_record_keep_time was set to 1, the latest log from my v$archived_log was from 12 days ago. All the records from the day I had a problem were gone.
Theoretically, I guess I could have switch my logfile a couple thousand times after a day and that would have cleared the issue up.
As I was reviewing my backup log from last night, I noticed that my RMAN-08137 error was gone. I didn't do anything different nor did I change anything. In fact, yesterday's backup had the same ORA-08137 error just as I had on the last 12 days.
So what was different? My control_file_record_keep_time was set to 1, so any record of my archived log would surely be gone way before now, right?
No, the control_file_record_keep_time parameter specifies the minimum number of days the recyclable data will stay in the controlfile, not an absolute limit. Sure enough, although my control_file_record_keep_time was set to 1, the latest log from my v$archived_log was from 12 days ago. All the records from the day I had a problem were gone.
Theoretically, I guess I could have switch my logfile a couple thousand times after a day and that would have cleared the issue up.
Wednesday, April 02, 2008
RMAN-08137: WARNING: archive log not deleted as it is still needed
A couple days ago my network link to my standby databases went dark in the middle of the night. My dbs took notice and started generating log shipping errors in the alert.log. About 10 minutes later, I got a page from my standby db that said his gap was more than the acceptable level and could I please do something. No problem, I just turned log_shipping off until the link came back up. The link came back up later in the day, I resumed log shipping, and most of my dbs recognized the gap and started resending logs.
The operative word being "most".
One of my dbs was shipping an archived redo log when the link went down. When I resumed log shipping, Oracle thought he was still shipping a log and thought the gap started at the next log sequence number. He shipped the current logs just fine, but just wouldn't resolve the gap.
"No worries", I thought, "I'll just rcp the missing file and register them with the standby control file and I'll be on my merry way."
Sure enough, the standby started recovering immediately and caught up in a short time. I thought everything was dandy until I got a page on Monday morning (2 AM, thank you very much) that my log_archive_dest was about to fill up on the primary.
Our standard protocol for freeing up the log_archive_dest is to run an archivelog backup with DELETE INPUT specified. I kicked off the backup, killed the monitor that watched the log_archive_dest, and went back to bed. I set the alarm for 1 hour later just to make sure the backup was done.
When I got back up, the archivelog backup wasn't done and the log_archive_dest was at 99%. I then looked in the directory and saw logs that were 3 and 4 days old which I knew was not correct. I killed the backup, deleted the 4 day old logs, crosschecked, and restarted the backup.
When I got into the office, my main task was to figure out why this happened. When I looked at my RMAN message log, I noticed about every log that was being backed up was accompanied by a warning:
RMAN-08137: WARNING: archive log not deleted as it is still needed
Hmm, that's not cool. Maybe my standby was really out of sync and I just didn't know it. Sometimes the primary will ship logs and the standby will accept but not apply them because it is missing a log. But that wasn't my case, the standby was within one or two logs from the primary.
When I looked at my v$archivelog_history, the "applied" flag indicated that the primary thought a bunch of logs were not applied to the standby. I knew different, as the standby was somewhat current.
At that point, I needed assistance. I filed a TAR and within about 2 hours found out it is a bug (bug 4538727) that is fixed in the 10.2.0.4 patchset. Since the patch just came out, I haven't gotten a chance to apply it to anything so I asked for workarounds.
"None", they say.
Theoretically, I supposed I could recreate my control files and my standby control files. I'm not really excited about doing that.
Until I figure out what to do, I'll just have to use RMAN to delete my logs "backed up 1 times" and crosscheck to manage my log_archive_dest space.
My Resolution, sort of.
The operative word being "most".
One of my dbs was shipping an archived redo log when the link went down. When I resumed log shipping, Oracle thought he was still shipping a log and thought the gap started at the next log sequence number. He shipped the current logs just fine, but just wouldn't resolve the gap.
"No worries", I thought, "I'll just rcp the missing file and register them with the standby control file and I'll be on my merry way."
Sure enough, the standby started recovering immediately and caught up in a short time. I thought everything was dandy until I got a page on Monday morning (2 AM, thank you very much) that my log_archive_dest was about to fill up on the primary.
Our standard protocol for freeing up the log_archive_dest is to run an archivelog backup with DELETE INPUT specified. I kicked off the backup, killed the monitor that watched the log_archive_dest, and went back to bed. I set the alarm for 1 hour later just to make sure the backup was done.
When I got back up, the archivelog backup wasn't done and the log_archive_dest was at 99%. I then looked in the directory and saw logs that were 3 and 4 days old which I knew was not correct. I killed the backup, deleted the 4 day old logs, crosschecked, and restarted the backup.
When I got into the office, my main task was to figure out why this happened. When I looked at my RMAN message log, I noticed about every log that was being backed up was accompanied by a warning:
RMAN-08137: WARNING: archive log not deleted as it is still needed
Hmm, that's not cool. Maybe my standby was really out of sync and I just didn't know it. Sometimes the primary will ship logs and the standby will accept but not apply them because it is missing a log. But that wasn't my case, the standby was within one or two logs from the primary.
When I looked at my v$archivelog_history, the "applied" flag indicated that the primary thought a bunch of logs were not applied to the standby. I knew different, as the standby was somewhat current.
At that point, I needed assistance. I filed a TAR and within about 2 hours found out it is a bug (bug 4538727) that is fixed in the 10.2.0.4 patchset. Since the patch just came out, I haven't gotten a chance to apply it to anything so I asked for workarounds.
"None", they say.
Theoretically, I supposed I could recreate my control files and my standby control files. I'm not really excited about doing that.
Until I figure out what to do, I'll just have to use RMAN to delete my logs "backed up 1 times" and crosscheck to manage my log_archive_dest space.
My Resolution, sort of.
Wednesday, March 26, 2008
Tuesday, March 11, 2008
Maybe I'm starting to get it
Yes, another R12 post. It's all I've been doing lately.
When I first installed 11.5.3, I just went with the default two-tier architecture; Web & Forms on the middle-tier and Reports & CCM on the db host. After a while, I realized that was a mistake for my environment. I should have put everything but the db on the middle-tier. Live and learn, I guess.
R12 was my chance to partially correct that mistake. I had a grand plan to migrate my middle-tier services to a hot x86_64 box and leave my db on Solaris. I figured that would give me an easier path if I needed to scale out either tier.
Except Oracle doesn't have a supported upgrade path on multiple platforms yet.
So I bounced a couple ideas off a very helpful Oracle Support Tech and he prefaced each one with "This isn't supported, but you might try...". I finally settled on a plan and executed it.
First, I collapsed my 11.5.9 tiers on to a single Solaris box. I used the Metalink whitepaper and rapidclone to get everything on my db box.
Then, I upgraded to 12.0.0 and applied the 12.0.4 RUP. At that point, I had a working 12.0.4 system with all my middle tier services on Solaris.
Next, I did a fresh install of 12.0.0 on a Linux box telling the configuration that I was going to have two tiers and my db was going to be on Solaris. Then I deleted my network topology and reran autoconfig that populated the db piece of my topology.
The next step was to apply the 12.0.4 patch to the Linux box. I did this with adpatch, but used the "nodatabaseportion" option because I knew I had already applied the object changes to my db.
The final step was running autoconfig on the middle-tier.
Except for a few glitches here and there, it was a surprisingly straight-forward split. Either I'm starting to get this Oracle Apps stuff or Oracle is finally getting serious about their configuration management.
When I first installed 11.5.3, I just went with the default two-tier architecture; Web & Forms on the middle-tier and Reports & CCM on the db host. After a while, I realized that was a mistake for my environment. I should have put everything but the db on the middle-tier. Live and learn, I guess.
R12 was my chance to partially correct that mistake. I had a grand plan to migrate my middle-tier services to a hot x86_64 box and leave my db on Solaris. I figured that would give me an easier path if I needed to scale out either tier.
Except Oracle doesn't have a supported upgrade path on multiple platforms yet.
So I bounced a couple ideas off a very helpful Oracle Support Tech and he prefaced each one with "This isn't supported, but you might try...". I finally settled on a plan and executed it.
First, I collapsed my 11.5.9 tiers on to a single Solaris box. I used the Metalink whitepaper and rapidclone to get everything on my db box.
Then, I upgraded to 12.0.0 and applied the 12.0.4 RUP. At that point, I had a working 12.0.4 system with all my middle tier services on Solaris.
Next, I did a fresh install of 12.0.0 on a Linux box telling the configuration that I was going to have two tiers and my db was going to be on Solaris. Then I deleted my network topology and reran autoconfig that populated the db piece of my topology.
The next step was to apply the 12.0.4 patch to the Linux box. I did this with adpatch, but used the "nodatabaseportion" option because I knew I had already applied the object changes to my db.
The final step was running autoconfig on the middle-tier.
Except for a few glitches here and there, it was a surprisingly straight-forward split. Either I'm starting to get this Oracle Apps stuff or Oracle is finally getting serious about their configuration management.
Tuesday, February 19, 2008
I'm In!
I went round and round with Oracle Support creating spin-off TARs and transferring it from one group to another. I finally found an article that said one of the 11.5.x versions was really executing fnd_web_sec.validate_login() under the covers.
Might as well give that a try, SINCE I'M STILL WAITING.
So I ran the function on my GUEST user and sure enough, it came back with a "Y" which indicated the GUEST user and password were correct.
I then tried the same for my SYSADMIN user and the function returned "N".
Hmm, that's interesting, let me try it again.
Sure enough, my old SYSADMIN password didn't work for some reason. I posted the results in the TAR and the analyst suggested I change my SYSADMIN password with FNDCPASS.
I changed it, and was in!
If this little tidbit of an upgrade is published somewhere, it sure wasn't obvious to me.
Of course, now I have to figure out why anything using the forms servlet won't come up. And how to use WebADI. And why my printers aren't defined.
But at least I'm in!
Might as well give that a try, SINCE I'M STILL WAITING.
So I ran the function on my GUEST user and sure enough, it came back with a "Y" which indicated the GUEST user and password were correct.
I then tried the same for my SYSADMIN user and the function returned "N".
Hmm, that's interesting, let me try it again.
Sure enough, my old SYSADMIN password didn't work for some reason. I posted the results in the TAR and the analyst suggested I change my SYSADMIN password with FNDCPASS.
I changed it, and was in!
If this little tidbit of an upgrade is published somewhere, it sure wasn't obvious to me.
Of course, now I have to figure out why anything using the forms servlet won't come up. And how to use WebADI. And why my printers aren't defined.
But at least I'm in!
Friday, February 08, 2008
Fun & Games, Part III
The main TAR had been in "Review" status for 24 hours, so I duty managed it. I usually don't like to duty manage things because if you cry wolf enough, eventually nobody will listen.
The analyst updated the TAR and wanted the results from about a dozen URLs along with another half dozen log files.
Aha, trying to bury me, are you?
After an hour of trying the URLs (which 90% of them were from 11.5.9 and didn't work in R12), I assembled the output and packaged in a nice neat .tar file. As expected, it went back to "Review" status.
Yesterday I had something else to do, so I wasn't very diligent in following up. Which was OK, because there was nothing to follow up with. My TAR remained in "Review" status for the whole day.
This morning I had some free time, so I duty managed my TAR again. Within 20 minutes, the analyst handed the TAR off to another group by creating a "spinoff" and marked the original TAR as "Awaiting Internal Response".
It will end up being a 2 second fix, I just know it. A week later, and I'm still waiting for the slightest bit of help.
The analyst updated the TAR and wanted the results from about a dozen URLs along with another half dozen log files.
Aha, trying to bury me, are you?
After an hour of trying the URLs (which 90% of them were from 11.5.9 and didn't work in R12), I assembled the output and packaged in a nice neat .tar file. As expected, it went back to "Review" status.
Yesterday I had something else to do, so I wasn't very diligent in following up. Which was OK, because there was nothing to follow up with. My TAR remained in "Review" status for the whole day.
This morning I had some free time, so I duty managed my TAR again. Within 20 minutes, the analyst handed the TAR off to another group by creating a "spinoff" and marked the original TAR as "Awaiting Internal Response".
It will end up being a 2 second fix, I just know it. A week later, and I'm still waiting for the slightest bit of help.
Wednesday, February 06, 2008
The Fun & Games Continued
Eventually my TAR from R12 Upgrade Fun & Games suggested that I rerun autoconfig for my DB Tier and I ran into another problem. Since it was not related to the original TAR, I opened a new one and put the original TAR in "Waiting for Customer" state until my new problem could be solved. Interestingly enough, the root cause of the new problem was that I had my $ORACLE_HOME in a different location than rapidWiz thought it should be, so rapidWiz put my newly created context file in the wrong location. I was trying to run adconfig.sh with an 11.5.9 context file, which kept giving me an error about version compatibility. Fair enough, I was back on track in about two days.
So I went back to my original TAR and posted that while I ran autoconfig on my db tier and bounced all my services, that still didn't fix my issue.
And I wait...
While waiting, I found Stephen Chan's blog that pointed me to Oracle Application Server Diagnostic Tools and Log Files in Applications R12 (ml note 454178.1). Definitely something to test, SINCE I'M JUST WAITING.
...to be continued... (hopefully)
So I went back to my original TAR and posted that while I ran autoconfig on my db tier and bounced all my services, that still didn't fix my issue.
And I wait...
While waiting, I found Stephen Chan's blog that pointed me to Oracle Application Server Diagnostic Tools and Log Files in Applications R12 (ml note 454178.1). Definitely something to test, SINCE I'M JUST WAITING.
...to be continued... (hopefully)
Monday, February 04, 2008
R12 Upgrade Fun & Games
So I've been toying around with an Oracle R12 upgrade lately. The first attempt went miserably wrong with all sorts of issues when I applied RUP3.
Then I decided that upgrading my middle-tier to Linux x86_64 sounded like a good idea. Except it wasn't. You see, first off 11.5.9 isn't supported on x86_64, so you can't add a node to your existing 11.5.9 instance. And you can't install an R12.0 base on x86_64 and then upgrade your 11.5.9 db, because that too is not supported. I ended up collapsing my two-tier environment to a single tier so I can upgrade to R12 and then add an R12 node that runs x86_64 later on. That, I double checked, is supported.
I performed the 11.5.9 to R12.0.0 upgrade again on my test system and applied the RUP4. Surprisingly, I only encountered about four problems. Two of the problems were not really problems because I wasn't running those modules and two problems that I researched on Metalink and quickly resolved.
When I brought the whole thing up (on a single tier), all the middle-tier services came up quite fine. Yes, I was surprised too.
The first time I tried to login is when my problems started. The ominous "Login failed. Please verify your login information or contact the system administrator" message comes up. Hey, I am the system administrator, WTF I am supposed to do from here? No errors, no messages, no nothing.
No problem, I'll just go to my standard diagnostic tests that I used in 11.5.9 to see where my problem is. Wait, there's no FND_WEB.Ping anymore. Nor is there any isItWorking servlets.
Finally, I find http://hostname/OA_HTML/jsp/fnd/aolj_conn_test.jsp and run through the tests. Surprisingly, everything but the AOL/J connection test works.
"Aha, I'll provide that information to Oracle Support and they'll be able to tell me where to look right away.", I thought to myself.
"Please upgrade to the latest RUP" was their response.
Me: "Errr, I am on RUP4."
Them: "Please rerun autoconfig."
Me. "Errr, done, but that doesn't solve my problem. Besides, my AOL/J connection test doesn't work. What does AOL/J do and why would it fail with this error?"
Wait.
Wait.
Wait.
Me: "Um, this is kind of urgent. When I try to login to the forms server directly I get 'APP-FND-01542: This Applications Server is not authorized to access this database.'"
Wait.
Wait.
Me: "I found that if I change my s_appserverid_authentication to OFF in the context file I get a different error logging into the forms server. Now I'm getting 'Error: validateLogin returned false; failed to login as XXX, please try again'"
Wait.
Wait.
Me: "Is anyone looking at this TAR?"
Them: "Sorry for the delay, I am looking at your log files"
Wait.
Wait.
...to be continued.
Then I decided that upgrading my middle-tier to Linux x86_64 sounded like a good idea. Except it wasn't. You see, first off 11.5.9 isn't supported on x86_64, so you can't add a node to your existing 11.5.9 instance. And you can't install an R12.0 base on x86_64 and then upgrade your 11.5.9 db, because that too is not supported. I ended up collapsing my two-tier environment to a single tier so I can upgrade to R12 and then add an R12 node that runs x86_64 later on. That, I double checked, is supported.
I performed the 11.5.9 to R12.0.0 upgrade again on my test system and applied the RUP4. Surprisingly, I only encountered about four problems. Two of the problems were not really problems because I wasn't running those modules and two problems that I researched on Metalink and quickly resolved.
When I brought the whole thing up (on a single tier), all the middle-tier services came up quite fine. Yes, I was surprised too.
The first time I tried to login is when my problems started. The ominous "Login failed. Please verify your login information or contact the system administrator" message comes up. Hey, I am the system administrator, WTF I am supposed to do from here? No errors, no messages, no nothing.
No problem, I'll just go to my standard diagnostic tests that I used in 11.5.9 to see where my problem is. Wait, there's no FND_WEB.Ping anymore. Nor is there any isItWorking servlets.
Finally, I find http://hostname/OA_HTML/jsp/fnd/aolj_conn_test.jsp and run through the tests. Surprisingly, everything but the AOL/J connection test works.
"Aha, I'll provide that information to Oracle Support and they'll be able to tell me where to look right away.", I thought to myself.
"Please upgrade to the latest RUP" was their response.
Me: "Errr, I am on RUP4."
Them: "Please rerun autoconfig."
Me. "Errr, done, but that doesn't solve my problem. Besides, my AOL/J connection test doesn't work. What does AOL/J do and why would it fail with this error?"
Wait.
Wait.
Wait.
Me: "Um, this is kind of urgent. When I try to login to the forms server directly I get 'APP-FND-01542: This Applications Server is not authorized to access this database.'"
Wait.
Wait.
Me: "I found that if I change my s_appserverid_authentication to OFF in the context file I get a different error logging into the forms server. Now I'm getting 'Error: validateLogin returned false; failed to login as XXX, please try again'"
Wait.
Wait.
Me: "Is anyone looking at this TAR?"
Them: "Sorry for the delay, I am looking at your log files"
Wait.
Wait.
...to be continued.
Thursday, January 10, 2008
Patch 4502962
Seems as though there's no version of the R12.AD.A minipack for Linux X86_64. Yet, since there's no binaries that get changed for this patch, you can run the 32bit version without a problem.
Friday, January 04, 2008
R12 Upgrade, Attempt #2
My first upgrade was a total bomb. The R12 portion went OK, only three minor problems that I corrected as the patching was taking place. Took about three days, but it finished with about 40 or so objects invalid (which I consider pretty good considering the umpteen thousand that are in the database).
Instead of just going with the 12.0.0, I decided to apply the RUP3. Might as well be up-to-date when you upgrade, right? While applying the RUP3 patch, it filled up my filesystem. I started deleteing some logfiles so I could continue, but couldn't keep up with adpatch. It left my db in a state of total chaos. Did I mention I didn't backup after my R12.0.0 upgrade was complete?
I noticed that during the R12.0.0 that I was using a decent amount of CPU on the middle-tier and my db tier was busy but not pegged. I swapped out my Solaris middle-tier for an X86_64 middle-tier to see if that speeds up the process.
One thing that came up during the initial install was adrunias.sh was trying to start Java from /tmp/RapidInstall/jre/Linux. The rapidwiz kept throwing an error of RW-50010 which was totally non-descript. I finally ran adrunias.sh with ksh -v to see what it was really doing and found it was looking for the jre in /tmp/RapidInstall/jre/Linux but the only directory was /tmp/RapidInstall/jre/Linux_x64. A soft link cured that problem and I am continuing on my way. Lets see where this takes us...
Instead of just going with the 12.0.0, I decided to apply the RUP3. Might as well be up-to-date when you upgrade, right? While applying the RUP3 patch, it filled up my filesystem. I started deleteing some logfiles so I could continue, but couldn't keep up with adpatch. It left my db in a state of total chaos. Did I mention I didn't backup after my R12.0.0 upgrade was complete?
I noticed that during the R12.0.0 that I was using a decent amount of CPU on the middle-tier and my db tier was busy but not pegged. I swapped out my Solaris middle-tier for an X86_64 middle-tier to see if that speeds up the process.
One thing that came up during the initial install was adrunias.sh was trying to start Java from /tmp/RapidInstall/jre/Linux. The rapidwiz kept throwing an error of RW-50010 which was totally non-descript. I finally ran adrunias.sh with ksh -v to see what it was really doing and found it was looking for the jre in /tmp/RapidInstall/jre/Linux but the only directory was /tmp/RapidInstall/jre/Linux_x64. A soft link cured that problem and I am continuing on my way. Lets see where this takes us...
Thursday, December 27, 2007
Film is dead, long live film
Have you ever witnessed a piece of technology so grand that you knew it would change the world eventually? When digital cameras hit the mass market, I knew your traditional 35mm camera was dead. Sure, the quality of film photography was better in the beginning, and I still think film takes better pictures. But for everyday snapshots, you just can't beat that little digital camera.
During Christmas, my Mother-in-Law mentioned that she couldn't find 35mm film at the grocery store anymore. I thought nah, people buy film for the Holidays, they're just out. So on our shopping adventures yesterday, I stopped in the photography section of my local Wal-Mart to pick her up some film. They had a couple rolls of Advantix film and a four pack of 800 speed 35mm and that was it. Lots of throw-away cameras, but no film. Three do-it-yourself kiosks for downloading your pictures, but no film.
Huh, maybe the mega-grocery store would have some. Nope, same deal. Lots of disposable cameras, a couple rolls of Advantix, and a few four-packs of 800 speed 35mm. Not even an empty peg where the normal speed film would go.
Now we were on a mission to find either 200 or 400 speed film. Our next stop took us to our local mega-drug store and sure enough they had film. Not a wide selection, mind you, but they had enough to choose.
So what's the significance? Certainly film's not going away. But in the very near future, I'm expecting that the only place you'll be able to pickup film is at a camera shop. I think everything film related becomes a botique item and may actually revive many camera shops that have been wondering about how to survive in a digital world. In the mean time, I wonder if there is a market for Cannon AE-1's on ebay....
During Christmas, my Mother-in-Law mentioned that she couldn't find 35mm film at the grocery store anymore. I thought nah, people buy film for the Holidays, they're just out. So on our shopping adventures yesterday, I stopped in the photography section of my local Wal-Mart to pick her up some film. They had a couple rolls of Advantix film and a four pack of 800 speed 35mm and that was it. Lots of throw-away cameras, but no film. Three do-it-yourself kiosks for downloading your pictures, but no film.
Huh, maybe the mega-grocery store would have some. Nope, same deal. Lots of disposable cameras, a couple rolls of Advantix, and a few four-packs of 800 speed 35mm. Not even an empty peg where the normal speed film would go.
Now we were on a mission to find either 200 or 400 speed film. Our next stop took us to our local mega-drug store and sure enough they had film. Not a wide selection, mind you, but they had enough to choose.
So what's the significance? Certainly film's not going away. But in the very near future, I'm expecting that the only place you'll be able to pickup film is at a camera shop. I think everything film related becomes a botique item and may actually revive many camera shops that have been wondering about how to survive in a digital world. In the mean time, I wonder if there is a market for Cannon AE-1's on ebay....
Thursday, December 20, 2007
Picking a WYSIWYG Editor for Drupal
I've been doing a lot of research lately into setting up Drupal as a Content Management System. One of Drupal's shortcomings (by design) is the current production version doesn't have a WYSIWYG editor. The lack of a visual editor is not a big deal since there are several editors you can just plug-in (more or less) as a module.
The first editor I tried was TinyMCE by Moxicode. I originally got this editor setup in short time with help from Howard Rogers. It was easy to install and did most of what I wanted. However, as I got a little more sophisticated, I started using grids and images more and more. Most of the time my entries came out OK, but every now and then my theme was all messed up because of a misplaced HTML tag. Not a major hassle for me, but if I was going to open my website up to non-technical users, I couldn't risk them unintentionally screwing it up.
So I searched a little more and came up with FCKEditor. It's a little more difficult to install than TinyMCE as you have to go in and modify a PHP file, but the instructions are clear and simple. It also comes with everything you need out of the box and there is little extra configuration that you have to do. However, with that ease of use comes a lack of granularity. So it's a trade-off. Best of all, the grids and images work great with my chosen theme.
For now, I'm sticking with FCKEditor.
The first editor I tried was TinyMCE by Moxicode. I originally got this editor setup in short time with help from Howard Rogers. It was easy to install and did most of what I wanted. However, as I got a little more sophisticated, I started using grids and images more and more. Most of the time my entries came out OK, but every now and then my theme was all messed up because of a misplaced HTML tag. Not a major hassle for me, but if I was going to open my website up to non-technical users, I couldn't risk them unintentionally screwing it up.
So I searched a little more and came up with FCKEditor. It's a little more difficult to install than TinyMCE as you have to go in and modify a PHP file, but the instructions are clear and simple. It also comes with everything you need out of the box and there is little extra configuration that you have to do. However, with that ease of use comes a lack of granularity. So it's a trade-off. Best of all, the grids and images work great with my chosen theme.
For now, I'm sticking with FCKEditor.
Wednesday, December 19, 2007
Upgrading to R12
Upgrading Oracle Applications is never an easy process. I like to test my upgrades both for completeness as well as performance so I can give my users a semi-accurate picture of how long things will take.
I'm doing a direct upgrade from 11.5.9.CU2 to R12 (probably CU3). I got to the point where the db objects are upgraded and received the following message:
Any guesses on how long this upgrade run will take?
I'm doing a direct upgrade from 11.5.9.CU2 to R12 (probably CU3). I got to the point where the db objects are upgraded and received the following message:
There are now 126521 jobs remaining (current phase=A3):OK, then. When I upgraded from 11.5.3 to 11.5.9, the object upgrade portion took about 26 hours, but I was on a slower box (Sun 2x900 with 8G RAM) and Oracle 9.2.0.7. Now I'm on a Sun 4x1200 with 16G of RAM and Oracle 10.2.0.3. This is only my first attempt at upgrading and I'll look at the AWR stats after to see where my bottlenecks were during the upgrade.
6 running, 951 ready to run and 125564 waiting
Any guesses on how long this upgrade run will take?
Monday, December 10, 2007
A properly constructed voicemail, Part I
"Hi, this is Fred, the DB is down. Call me.", is not an appropriate message.
First of all, Fred who? Fred in accounting or Fred in customer service. It makes a difference to me.
Which DB is down? OK, so you don't know the exact database, tell me the application you are using.
Oh, and Fred, leave me a number where I can get back to you. It's 2:00 in the morning and I'm nowhere near a computer to lookup your number.
A properly constructed message would have been:
"Hi, this is Fred in accounting. I'm having a problem logging into Oracle Financials and am getting a 404 error message. Please call me at extension 1234."
First of all, Fred who? Fred in accounting or Fred in customer service. It makes a difference to me.
Which DB is down? OK, so you don't know the exact database, tell me the application you are using.
Oh, and Fred, leave me a number where I can get back to you. It's 2:00 in the morning and I'm nowhere near a computer to lookup your number.
A properly constructed message would have been:
"Hi, this is Fred in accounting. I'm having a problem logging into Oracle Financials and am getting a 404 error message. Please call me at extension 1234."
Friday, November 02, 2007
Bugs, bugs, and more bugs
You may have noticed that my blog postings have been pretty sparse lately. I get that way when I have to work on Oracle Applications. Whenever I am doing an Apps project I end up having to read about 2000 pages of documentation that point to another 100 documents on Metalink. Not much to blog about there.
By the time I am done reading, the whole process is so utterly complex that I end up putting my own process together and pulling the parts relavent to my installation into a single document.
So I've been working on my master document for about two weeks now and I'm finally ready to install a fresh version of R12 on my pre-test box. Now, I fully expect something to crash and burn in this environment and I'll have to wipe it out and start over in a couple days.
I fired up the installer and right away it encountered a java exception; "java.lang.NullPointerException".
Hmm, maybe I have the CD's staged in the wrong location. So I take another two hours and restage the CDs.
"java.lang.NullPointerException" again.
So I check my DISPLAY and make sure I can start xclock. No problem.
Then I look on Metalink and sure enough, I ran into a bug. The install software that shipped has a bug in it. Good old bug 5972626.
Did anyone even test this stuff before it went on CD? It's bad enough that my standard 10gR2 install has 23 patches applied, but a bug so it doesn't even install?
Why am I surprised?
By the time I am done reading, the whole process is so utterly complex that I end up putting my own process together and pulling the parts relavent to my installation into a single document.
So I've been working on my master document for about two weeks now and I'm finally ready to install a fresh version of R12 on my pre-test box. Now, I fully expect something to crash and burn in this environment and I'll have to wipe it out and start over in a couple days.
I fired up the installer and right away it encountered a java exception; "java.lang.NullPointerException".
Hmm, maybe I have the CD's staged in the wrong location. So I take another two hours and restage the CDs.
"java.lang.NullPointerException" again.
So I check my DISPLAY and make sure I can start xclock. No problem.
Then I look on Metalink and sure enough, I ran into a bug. The install software that shipped has a bug in it. Good old bug 5972626.
Did anyone even test this stuff before it went on CD? It's bad enough that my standard 10gR2 install has 23 patches applied, but a bug so it doesn't even install?
Why am I surprised?
Thursday, November 01, 2007
Posting Pictures to a phpBB forum
I run across this situation almost daily. Somebody needs to post either a screen shot or a picture (same thing) to a phpBB forum and they can't figure out how to do it.
First, you need a third party picture hosting service. I personally prefer Flickr, but there are others like Photobucket and SmugMug out there that allow you to host your pictures for free. You can even host it on your blog on blogger.com! My example will be using Flickr, but really all you need is a URL to the actual image file.
Upload your photo to Flickr. If you don't have a Yahoo account, you can create one for free.
Click "Upload Photo" and either type in the name of your picture file (complete path) or use the "Browse" button to find it. Make sure the "Public" radio button is selected and click "Upload".
Your picture will be uploaded and displayed when it is done. Now, click "Save this Batch".
All your pictures are now displayed.
Click on the picture you just uploaded and then click on the "All Sizes" button:
At the bottom of that page, you will see "2. Grab the photo's URL". Copy this URL into your buffer by highlighting it and pressing CTRL+C.
Now, you're done with Flickr. Next, go to the forum you want to post in and compose a message. Type in the text you wish. When you get to the point you want to insert your image, you will enclose the URL from the step above within [img][/img] tags:
Post your message and the picture will show up inline:
First, you need a third party picture hosting service. I personally prefer Flickr, but there are others like Photobucket and SmugMug out there that allow you to host your pictures for free. You can even host it on your blog on blogger.com! My example will be using Flickr, but really all you need is a URL to the actual image file.
Upload your photo to Flickr. If you don't have a Yahoo account, you can create one for free.
Click "Upload Photo" and either type in the name of your picture file (complete path) or use the "Browse" button to find it. Make sure the "Public" radio button is selected and click "Upload".
Your picture will be uploaded and displayed when it is done. Now, click "Save this Batch".
All your pictures are now displayed.
Click on the picture you just uploaded and then click on the "All Sizes" button:
At the bottom of that page, you will see "2. Grab the photo's URL". Copy this URL into your buffer by highlighting it and pressing CTRL+C.
Now, you're done with Flickr. Next, go to the forum you want to post in and compose a message. Type in the text you wish. When you get to the point you want to insert your image, you will enclose the URL from the step above within [img][/img] tags:
Post your message and the picture will show up inline:
Wednesday, September 12, 2007
There's more than one way to skin a cat
We're off on an adventure trying to figure out why one of our 9.2.0.5 databases has poor response time during a certain period. During the investigation, I came across a process that was executing a query that had a function call in it. It wasn't our problem, but was interesting none the less.
I started tracing the user's session and found that for every row in table T the query was executing function getCount (the names have been changed and the function simplified to clarify the example).
OK, seams reasonable enough, right. But inside getCount() was a query:
Ah, here's the query that showed up in the trace as being the top dog.
"Hmm, let me just fix this and let the developer know how to fix his problem" I thought to myself, "he'll appreciate it."
So I suggested the following query for the fix:
The number of logical reads was much better and the wall clock response time was much better as well. Perfectly suitable solution.
Well, proving that DBAs don't know anything about how to write code, the fix ended up being:
OK, that works. No reason to do something the easy way when you can write code and do it the cool way.
Addition: As Gary points out in the coments, there is a potential in the new function for records with both a '3' AND a '7' to be picked up twice. In my actual data, there is a business rule that makes sure that doesn't happen, but in general it is an issue.
I started tracing the user's session and found that for every row in table T the query was executing function getCount (the names have been changed and the function simplified to clarify the example).
SELECT x, getcount()
FROM t
OK, seams reasonable enough, right. But inside getCount() was a query:
create or replace function getcount return integer
as
c integer;
begin
select count(*) into c from xyz where (code1='7' or code2='3');
return c;
end;
Ah, here's the query that showed up in the trace as being the top dog.
SELECT COUNT(*)
FROM
XYZ WHERE (CODE1='7' OR CODE2='3')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.47 8.55 57428 63059 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.47 8.55 57428 63059 0 1
"Hmm, let me just fix this and let the developer know how to fix his problem" I thought to myself, "he'll appreciate it."
So I suggested the following query for the fix:
select count(*) from (
select id from xyz where code1='7'
union
select id from xyz where code2='3'
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.05 0 666 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.05 0 666 0 1
The number of logical reads was much better and the wall clock response time was much better as well. Perfectly suitable solution.
Well, proving that DBAs don't know anything about how to write code, the fix ended up being:
create or replace function getcount return integer
as
cursor c1 is select count(*) cnt from xyz where code1='7';
cursor c2 is select count(*) cnt from xyz where code2='3';
t integer := 0;
begin
for i in c1 loop
t := t+i.cnt;
end loop;
for i in c2 loop
t := t+i.cnt;
end loop;
return t;
end;
/
SELECT COUNT(*) CNT
FROM
XYZ WHERE CODE2='3'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.03 0 387 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.03 0 387 0 1
SELECT COUNT(*) CNT
FROM
XYZ WHERE CODE1='7'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 279 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.02 0 279 0 1
OK, that works. No reason to do something the easy way when you can write code and do it the cool way.
Addition: As Gary points out in the coments, there is a potential in the new function for records with both a '3' AND a '7' to be picked up twice. In my actual data, there is a business rule that makes sure that doesn't happen, but in general it is an issue.
Monday, September 10, 2007
Inexcusable Response
Back in April I filed a TAR on an ORA-00904 error I was getting when I called dbms_stats.gather_index_stats() in 9.2.0.8:
No problem, I already figured out that if I used ANALYZE TABLE I could work around the error. After about 3 weeks of traces and going back and forth, Oracle acknowledge it was bug 3078144 and was fixed on 10.2.
I requested a backport to my version. After all, I was on a supported version so I figured it wouldn't even be an issue. While I was planning on going to 10.2 eventually, it wasn't in the immediate plans. After the first month, I pinged support to see where my backport was.
"Oh, development denied it because it is a Level 3 SR."
Hmm, that's the game we're playing, eh?
"Well, it's happening every day now and affecting my business", I replied. OK, maybe a little fib, but lets see where it goes.
We agreed to raise it to a Level 2 SR.
Another month went by so I followed up again. Still with development. At this point we started planning the migration to 10.2 (not because of this TAR, but we are gluttons for punishment). It would be a race to see if I got my bug fix or my db upgraded to 10.2 first.
About two weeks ago, I decided to ratchet up the pressure; I "duty managed" the TAR. I got the nicest duty manager who knew how to talk the talk, but I wasn't buyin' what he was sellin'.
"I will give you a call within three business days to let you know the status of the bug fix." was his final reply. A week went by with no update.
Well, this past weekend I won - the database was upgraded to 10.2.0.3+. While I was tempted to keep the TAR open just to see how long it took to resolve, I decided honor was the better part of valor and closed the tar indicating we had upgraded.
I bet I don't get a satisfaction survey on this one.
SQL> exec dbms_stats.gather_index_stats('SCOTT','TIGER_CK');
BEGIN dbms_stats.gather_index_stats('SCOTT','TIGER_CK'); END;
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 8557
ORA-06512: at "SYS.DBMS_STATS", line 8577
ORA-06512: at line 1No problem, I already figured out that if I used ANALYZE TABLE I could work around the error. After about 3 weeks of traces and going back and forth, Oracle acknowledge it was bug 3078144 and was fixed on 10.2.
I requested a backport to my version. After all, I was on a supported version so I figured it wouldn't even be an issue. While I was planning on going to 10.2 eventually, it wasn't in the immediate plans. After the first month, I pinged support to see where my backport was.
"Oh, development denied it because it is a Level 3 SR."
Hmm, that's the game we're playing, eh?
"Well, it's happening every day now and affecting my business", I replied. OK, maybe a little fib, but lets see where it goes.
We agreed to raise it to a Level 2 SR.
Another month went by so I followed up again. Still with development. At this point we started planning the migration to 10.2 (not because of this TAR, but we are gluttons for punishment). It would be a race to see if I got my bug fix or my db upgraded to 10.2 first.
About two weeks ago, I decided to ratchet up the pressure; I "duty managed" the TAR. I got the nicest duty manager who knew how to talk the talk, but I wasn't buyin' what he was sellin'.
"I will give you a call within three business days to let you know the status of the bug fix." was his final reply. A week went by with no update.
Well, this past weekend I won - the database was upgraded to 10.2.0.3+. While I was tempted to keep the TAR open just to see how long it took to resolve, I decided honor was the better part of valor and closed the tar indicating we had upgraded.
I bet I don't get a satisfaction survey on this one.
Wednesday, September 05, 2007
Cat with Bow GolfTuesday, September 04, 2007
A dying breed?
I usually get a feel for "what's hot" in the marketplace by the number of books on the shelves at my favorite bookstore. A couple years ago there was six shelves at my Borders store filled with books on Oracle. Oracle for Dummies, PL/SQL, and scores of Certification books. I was quite surprised to find only three books on Oracle at the same store this past weekend.
MySQL still has some respect with about a 1/2 shelf. .Net clocked in with about 5 or 6 shelves with a smattering here and there of Java and the associated technologies. Good old Perl shared half a shelf with PHP. But I feel left behind because I don't know Excel Macros (about 12 shelves).
Maybe Oracle has really achieved a self-tuning, self-managing database and we don't need books anymore.
Please excuse me while I apply four more patches to my "up-to-date" 10.2.0.3 installation.
MySQL still has some respect with about a 1/2 shelf. .Net clocked in with about 5 or 6 shelves with a smattering here and there of Java and the associated technologies. Good old Perl shared half a shelf with PHP. But I feel left behind because I don't know Excel Macros (about 12 shelves).
Maybe Oracle has really achieved a self-tuning, self-managing database and we don't need books anymore.
Please excuse me while I apply four more patches to my "up-to-date" 10.2.0.3 installation.
Wednesday, August 22, 2007
Deadlocks
Good old ORA-00060: deadlock detected while waiting for resource. There's really two main causes for an ORA-00060 error; bad application logic or unindexed foreign keys.
The classic example of a deadlock is userA updating table X and then table Y while userB updates table Y and then table X. Oracle kills one of the users, rolls back his transaction, throws an error to the alert.log and everybody else moves on. As DBAs, we look at these errors, forward them to the appropriate developer and forget about them.
The second most prevalent cause of ORA-00060 errors is unindexed foreign keys. You can check out Oracle's explanation and there are a ton of scripts that will help you diagnose this situation. I personally use a variation of Tom Kyte's script but there are others such as this one.
I know, you're asking yourself "So What? Everybody knows about deadlocks."
Be patient grasshopper, there's more to the story.
A couple weeks ago we started getting ORA-00060 error messages every now and then on a 10.2.0.3 database that rarely received this type of message before. And then some of our critical processes slowed down. And then we started getting about 12 ORA-00060 errors a day.
At first, we attacked it as two separate problems; the ORA-00060 error and performance. One DBA started decoding the trace files from the deadlocks informing the developers of which objects were involved and the data involved. The weird part was the deadlock was occurring on an INSERT statement.
At the same time, I started tracing the slow processes and found that a great majority of time was being spent on "enq: TM - contention" waits. We then ran an AWR report and found that the top wait was "enq: TM - contention" and we knew we had a problem.
The wait event and the ORA-00060 together lead us to an unindexed FK. However, all the scripts we used indicated the fks were indexed. We started disabling the FKs one by one (for the two tables involved in the majority of the deadlocks). After one particular FK was disabled, all our "enq: TM - contention" waits went away and things were back to normal.
Oracle support directed us to bug 6053354 which isn't really a bug because of bug 6066587. Sure enough, the conditions in 6053354 existed in our database. We turned the tablespaces to read-write and re-enabled the FK with no adverse effects.
The moral of the story is follow your instincts up to a point, but every now and then something really freaky is going on.
The classic example of a deadlock is userA updating table X and then table Y while userB updates table Y and then table X. Oracle kills one of the users, rolls back his transaction, throws an error to the alert.log and everybody else moves on. As DBAs, we look at these errors, forward them to the appropriate developer and forget about them.
The second most prevalent cause of ORA-00060 errors is unindexed foreign keys. You can check out Oracle's explanation and there are a ton of scripts that will help you diagnose this situation. I personally use a variation of Tom Kyte's script but there are others such as this one.
I know, you're asking yourself "So What? Everybody knows about deadlocks."
Be patient grasshopper, there's more to the story.
A couple weeks ago we started getting ORA-00060 error messages every now and then on a 10.2.0.3 database that rarely received this type of message before. And then some of our critical processes slowed down. And then we started getting about 12 ORA-00060 errors a day.
At first, we attacked it as two separate problems; the ORA-00060 error and performance. One DBA started decoding the trace files from the deadlocks informing the developers of which objects were involved and the data involved. The weird part was the deadlock was occurring on an INSERT statement.
At the same time, I started tracing the slow processes and found that a great majority of time was being spent on "enq: TM - contention" waits. We then ran an AWR report and found that the top wait was "enq: TM - contention" and we knew we had a problem.
The wait event and the ORA-00060 together lead us to an unindexed FK. However, all the scripts we used indicated the fks were indexed. We started disabling the FKs one by one (for the two tables involved in the majority of the deadlocks). After one particular FK was disabled, all our "enq: TM - contention" waits went away and things were back to normal.
Oracle support directed us to bug 6053354 which isn't really a bug because of bug 6066587. Sure enough, the conditions in 6053354 existed in our database. We turned the tablespaces to read-write and re-enabled the FK with no adverse effects.
The moral of the story is follow your instincts up to a point, but every now and then something really freaky is going on.
Tuesday, August 21, 2007
Back when the internet was fun...
Do you remember way back to 2005 when the internet was fun?
Wanted to check the progress of the NCAA tournament during the day? Just go to http://www.espn.com and your browser refreshes automatically as the action happens.
Read some industry chit-chat during your lunch hour? Open up your favorite news aggregator and read away.
Two years ago I wouldn't have thought twice about forwarding a funny email to friends & family.
Try to go to http://www.espn.com today and you'll likely have to "justify the business need" to three levels of management to get your scores.
My RSS newsfeeds quit working a couple weeks ago. Doesn't matter that 90% of them are industry related, they're not business appropriate.
I stopped forwarding emails to some friends and family because I was tired of getting the bounce messages from their email filter. It's just not the same when you have to tell the joke over the phone and explain why 18 goes into 54 more times than 54 goes into 18.
Ah, the price of progress.
Wanted to check the progress of the NCAA tournament during the day? Just go to http://www.espn.com and your browser refreshes automatically as the action happens.
Read some industry chit-chat during your lunch hour? Open up your favorite news aggregator and read away.
Two years ago I wouldn't have thought twice about forwarding a funny email to friends & family.
Try to go to http://www.espn.com today and you'll likely have to "justify the business need" to three levels of management to get your scores.
My RSS newsfeeds quit working a couple weeks ago. Doesn't matter that 90% of them are industry related, they're not business appropriate.
I stopped forwarding emails to some friends and family because I was tired of getting the bounce messages from their email filter. It's just not the same when you have to tell the joke over the phone and explain why 18 goes into 54 more times than 54 goes into 18.
Ah, the price of progress.
Wednesday, July 18, 2007
Materialized View Tricks, NOT
While working on another problem, I found this interesting little tidbit with Materialized Views querying views on Oracle 10.2.0.3.
One one db, I have a table called xyz_rtab. The table structure doesn't really matter, it's just any table with a primary key. Then, I create a view on top of the table:
Why do that? Well, my table is not really a table in my environment. The view on top of the table is really refactoring a business object that is used by several systems but whose time has come to change. But it doesn't matter, for the purposes of this demonstration.
Next, I change dbs and create the database link I'll use to get this data.
Now, I query across that dblink to verify that I can actually see the data:
If I can query the data I should be able to create a mview, right? Lets try from the base table:
No problem, that's what we expect. Now lets try from the view:
Nice.
One one db, I have a table called xyz_rtab. The table structure doesn't really matter, it's just any table with a primary key. Then, I create a view on top of the table:
db2> create view xyz_v1 as select * from xyz_rtab;
View created.
Why do that? Well, my table is not really a table in my environment. The view on top of the table is really refactoring a business object that is used by several systems but whose time has come to change. But it doesn't matter, for the purposes of this demonstration.
Next, I change dbs and create the database link I'll use to get this data.
db1> create database link db2 using 'db2';
Database link created.
Now, I query across that dblink to verify that I can actually see the data:
db1> select count(*) from xyz_v1@db2;
COUNT(*)
----------
10
db1> select count(*) from xyz_rtab@db2;
COUNT(*)
----------
10
If I can query the data I should be able to create a mview, right? Lets try from the base table:
db1> create materialized view xyz_rtab_mview as select * from xyz_rtab@db2;
Materialized view created.
db1> exec dbms_mview.refresh(list=>'xyz_rtab_mview',method=>'C',atomic_refresh=>true);
PL/SQL procedure successfully completed.
No problem, that's what we expect. Now lets try from the view:
db1> create materialized view xyz_mv1 as select * from xyz_v1@db2;
create materialized view xyz_mv1 as select * from xyz_v1@db2
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960
ORA-06512: at line 1
Nice.
Tuesday, July 10, 2007
No soup for you!
Have you read about Sprint cutting people off?
Seems as though Sprint is taking on the attitude that if you call them too much, you'll get dropped. Sounds like they think they're an insurance company not a phone carrier. I wonder if you only call twice in the life of your contract they'll give you a refund.
I deal with customer service departments every day. Lets face it, most customer service agents are great reading off a script, but the second you go off script they're clueless. And that's if you can even understand them.
Another point is that the way most customer service departments are setup, there's no incentive for the Customer Service Agent to actually solve your problem. They get financial incentives for closing problems, not solving problems. Ever open a trouble ticket with a certain database vendor and they work on it for a while and then say you have to open a new trouble ticket because they can't solve the problem?
Everybody knows (I know Tom, there are exceptions, but in general) that it costs more to attract customers than to keep them. I can't see many people actually wanting to talk to somebody half a world away on the phone 50 times a month. But that's just me.
Extra credit: Where does "No soup for you!" come from?
Seems as though Sprint is taking on the attitude that if you call them too much, you'll get dropped. Sounds like they think they're an insurance company not a phone carrier. I wonder if you only call twice in the life of your contract they'll give you a refund.
I deal with customer service departments every day. Lets face it, most customer service agents are great reading off a script, but the second you go off script they're clueless. And that's if you can even understand them.
Another point is that the way most customer service departments are setup, there's no incentive for the Customer Service Agent to actually solve your problem. They get financial incentives for closing problems, not solving problems. Ever open a trouble ticket with a certain database vendor and they work on it for a while and then say you have to open a new trouble ticket because they can't solve the problem?
Everybody knows (I know Tom, there are exceptions, but in general) that it costs more to attract customers than to keep them. I can't see many people actually wanting to talk to somebody half a world away on the phone 50 times a month. But that's just me.
Extra credit: Where does "No soup for you!" come from?
Monday, July 02, 2007
I think the database is broken
Below is a somewhat fictional somewhat realistic view of upgrades.
"I think the database is broken" was my first call this morning.
"OK, what error message are you getting?" was my reply. Since I knew we changed some things over the weekend, I figured something got left out and needed fixing.
"My process that normally takes 38 minutes is done in 3."
"OK, does it error out?"
"No."
"Do the results look right?"
"Yes."
"So why do you think it's broken?"
"Because it finished in 3 minutes."
"Well, we upgraded the database version this past weekend and put the database on a faster host, so that's probably the difference."
"But it used to take 38 minutes."
...long pause on the phone by me, expecting something else...
"I can slow it back down if that's what you want...."
On the flip side, I go to my developers to see how things are going and they report increases anywhere from 4-10 times faster depending on how much the db is being used. One savvy developer corners me and says something along the lines of "Can't we move this db to a faster host?"
"Huh? This is the almost latest, greatest, fastest host there is. You've got 3.0Ghz of Dual Core processing power underneath your db where before you had some clunky Sparc processors."
"You know there's 3.2Ghz processors out, right?"
"So, let me get this straight. You're 10 times faster than Friday, and you want to be 10.1 times faster?"
"Yup, that extra speed will really help."
"I need to get back to my office, call me if you have any problems."
"I think the database is broken" was my first call this morning.
"OK, what error message are you getting?" was my reply. Since I knew we changed some things over the weekend, I figured something got left out and needed fixing.
"My process that normally takes 38 minutes is done in 3."
"OK, does it error out?"
"No."
"Do the results look right?"
"Yes."
"So why do you think it's broken?"
"Because it finished in 3 minutes."
"Well, we upgraded the database version this past weekend and put the database on a faster host, so that's probably the difference."
"But it used to take 38 minutes."
...long pause on the phone by me, expecting something else...
"I can slow it back down if that's what you want...."
On the flip side, I go to my developers to see how things are going and they report increases anywhere from 4-10 times faster depending on how much the db is being used. One savvy developer corners me and says something along the lines of "Can't we move this db to a faster host?"
"Huh? This is the almost latest, greatest, fastest host there is. You've got 3.0Ghz of Dual Core processing power underneath your db where before you had some clunky Sparc processors."
"You know there's 3.2Ghz processors out, right?"
"So, let me get this straight. You're 10 times faster than Friday, and you want to be 10.1 times faster?"
"Yup, that extra speed will really help."
"I need to get back to my office, call me if you have any problems."
Wednesday, June 27, 2007
Why is it always the database?
When something is slow, why do we always point to the database? A computer system is so much more than "the application" and "the database". It's networks, routers, disks, memory, array controllers, middle-tiers, JDBC Drivers, Operating Systems, and client versions (which, usually comes round to "It's Oracle/MySQL, fix it"). Come to think of it, it's pretty amazing that we get decent performance at all.
Everybody is guilty of it from the novice to the experience person who knows better. Heck, I am sometimes guilty of it myself.
I'll get a call, "Application XYZ is slow, is the database OK?"
"Let me check."
A couple minutes later I indicate everything is fine on my end. I hear three days later that "XYZ was slow because of the database". Argh. Next time that person needs help tuning a query he'll get pointed to the documentation for EXPLAIN PLAN.
Why do we always point to the database first? Is it because the database is usually the guilty party? Or do we point fingers at what we don't understand?
Everybody is guilty of it from the novice to the experience person who knows better. Heck, I am sometimes guilty of it myself.
I'll get a call, "Application XYZ is slow, is the database OK?"
"Let me check."
A couple minutes later I indicate everything is fine on my end. I hear three days later that "XYZ was slow because of the database". Argh. Next time that person needs help tuning a query he'll get pointed to the documentation for EXPLAIN PLAN.
Why do we always point to the database first? Is it because the database is usually the guilty party? Or do we point fingers at what we don't understand?
Friday, June 15, 2007
Spike
Got a spike in hits on the blog in the last couple of days. I dug through the hits and found that a lot of people were coming from SearchOracle.com. I followed the most frequent URL and discovered that my post on Multiple Listeners had been linked to an article by Bill Cullen.
Thursday, May 24, 2007
What is going on in Oracle's QA department?
I've been an Oracle DBA since the days of 7.0.16 (on Netware, but that's a story for a different day). I'd like to think I have a decent amount of experience with Oracle and it's nuances. Sure, some things are quirky and don't work the way you think they should, but that's just getting used to the software. I can count on one hand the number of one-off patches I had to install over a base patchset in 7.x to 9.2. (I'm talking RDBMS here, not Oracle Apps.)
I specifically waited to move to 10g until it had been out a while. Our move to 10.2.0.3 has been quite disappointing. Sure, we did a lot of testing. But production is different than testing. After about 30 days of one db being live, we've had to install three one-off patches. And we just got slammed with two more in the last couple days to bring the total up to five. If we were on 10.2.0.0, I'd understand, but 10.2.0.3? Five freaking one-off patches.
C'mon Oracle, get your act together. We're trying to run a business here.
I specifically waited to move to 10g until it had been out a while. Our move to 10.2.0.3 has been quite disappointing. Sure, we did a lot of testing. But production is different than testing. After about 30 days of one db being live, we've had to install three one-off patches. And we just got slammed with two more in the last couple days to bring the total up to five. If we were on 10.2.0.0, I'd understand, but 10.2.0.3? Five freaking one-off patches.
C'mon Oracle, get your act together. We're trying to run a business here.
Friday, May 18, 2007
Oracle 10g upgrade gotcha
Everybody knows the Cost Based Optimizer needs decent statistics to figure out what his query plan should be. Sure, it takes time, but everybody does it. Some estimate, some compute, some think they have a better way.
After upgrading from 9.2.0.7 to 10.2.0.3, one of our systems suddenly slowed down. Not directly after the upgrade, but three days later. After some investigation, we found that some of the statistics had been estimated and not computed. That was pretty much a shock because my analyze method usually specifies estimate_percent=>null to make sure we compute. (Note, I already figured out that the default estimate_percent changed from 9i to 10g, so that wasn't it.)
I checked my custom analyze method and it reported that the table was never analyzed by me.
Hmmm, now it's getting real interesting. Just for kicks, I reran dbms_stats.gather_table_stats without an estimate_percent parameter and it estimated at about 8.3%. Aha! Must be using the AUTO_SAMPLE_SIZE for some reason.
So why?
Then I looked at the last_analyzed time of the tables that had been estimated. They all were run between 22:00 and 01:00. Aha, a schedule. Then I looked in dba_scheduler_jobs and there it was. A new job that Oracle automatically sets up to analyze stale objects.
When I checked in the morning, the objects didn't report as being stale. Then, after the day's data, Oracle decided the statistics were stale and analyzed the tables. When I went to gather statistics for stale objects the next morning, they had already been analyzed and weren't stale anymore. Except the statistics weren't optimal which made our queries "go bad".
Fortunately, there is a way you can disable this job just by tweaking the schedule.
After upgrading from 9.2.0.7 to 10.2.0.3, one of our systems suddenly slowed down. Not directly after the upgrade, but three days later. After some investigation, we found that some of the statistics had been estimated and not computed. That was pretty much a shock because my analyze method usually specifies estimate_percent=>null to make sure we compute. (Note, I already figured out that the default estimate_percent changed from 9i to 10g, so that wasn't it.)
I checked my custom analyze method and it reported that the table was never analyzed by me.
Hmmm, now it's getting real interesting. Just for kicks, I reran dbms_stats.gather_table_stats without an estimate_percent parameter and it estimated at about 8.3%. Aha! Must be using the AUTO_SAMPLE_SIZE for some reason.
So why?
Then I looked at the last_analyzed time of the tables that had been estimated. They all were run between 22:00 and 01:00. Aha, a schedule. Then I looked in dba_scheduler_jobs and there it was. A new job that Oracle automatically sets up to analyze stale objects.
When I checked in the morning, the objects didn't report as being stale. Then, after the day's data, Oracle decided the statistics were stale and analyzed the tables. When I went to gather statistics for stale objects the next morning, they had already been analyzed and weren't stale anymore. Except the statistics weren't optimal which made our queries "go bad".
Fortunately, there is a way you can disable this job just by tweaking the schedule.
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
Monday, May 14, 2007
Keywords
One thing that Statcounter provides me is how often certain keywords are found in my blog. Some of the more popular are:
- ORA-27054: I assume this is by people trying to backup using RMAN over NFS. I hope they found my problem and most importantly, my solution.
- Some combination of DB Links and ORA-01017: I'm sure people found my distributed issues with 9i and 10g working together. But that's not always the issue. If you are using what I call "unauthenticated" database links (no password in the definition) then the user authentication on both the source and remote database need to be the same. If we're talking an OS Authenticated user, then that user has to be OS Authenticated on both. If it's a regular db user, the passwords have to match.
- Lots of people looking for some varient of backup and methodology. I hope they found lots of info in the Backup Top 11, but I don't think I've ever described my backup methodology for Oracle. Maybe in a blog post in the future.
- I get lots of hits on the Quick and Dirty Backup for MySQL. Seems like a lot of people are looking for an easy solution to MySQL without having to fork over some cash for backup software.
- I also get lots of hits on The MySQL to an Oracle DBA series, but not too many through keyword searches.
- For the guy who's searching "mysql php round differently", I'd look at your datatype in the database to make sure you're using the proper number type.
- Looking for information on "_db_writer_flush_imu"? Better know what you're doing.
- Oh, and the person looking for "ingres porn server"...good luck on that.
Wednesday, May 09, 2007
Recognize your mistakes
I was reading an article in Business Week by Jack and Suzy Welch last night about the qualities of successful managers. Of the various qualities, one in particular stood out:
That's pretty good advice. Admit your mistakes, correct them, and move on. In my company, that's just the way it works. We don't waste time finger-pointing. If there was a failure, we put appropriate measures in place so it doesn't happen again and move on.
Gary Kaltbaum frequently says "Recognize your mistakes early." If you make a mistake, understand how you got into this situation and correct it as quickly as possible. Don't let your mistake fester and become a bigger problem down the road.
We would also add two other qualities to the must-have list. One is a heavy-duty resilience, a requirement because anyone who is really in the game messes up at some point. You're not playing hard enough if you don't! But when your turn comes, don't make the all-too-human mistake of thinking getting ahead is about minimizing what happened. The most successful people in any job always own the failures, learn from them, regroup, and then start again with renewed speed, vigor, and conviction.
That's pretty good advice. Admit your mistakes, correct them, and move on. In my company, that's just the way it works. We don't waste time finger-pointing. If there was a failure, we put appropriate measures in place so it doesn't happen again and move on.
Gary Kaltbaum frequently says "Recognize your mistakes early." If you make a mistake, understand how you got into this situation and correct it as quickly as possible. Don't let your mistake fester and become a bigger problem down the road.
Sunday, April 29, 2007
Two Years of So What?
I started this blog about two years ago. Never in my wildest dreams would have I thought that I would have over 200 regular subscribers and about 300 page views a day. In celebration of the Anniversary, I change the blog template to one of new standard blogger templates.
If only my IRA portfolio looked like this...
Friday, April 27, 2007
MySQL Pseudo-Partitioning, Part II
On advice of the MySQL She-BA, I wanted to see what performance I could get with MERGE tables. In my particular application I'm going to need the RI I get with InnoDB, so it's not a solution, but as long as I am investigating anyway...
Seems simple enough, let me the MERGE table over my existing tables:
Doh! That's right, got to change to MyISAM.
Lets see how it works now.
Whoa, that's pretty good, I could live with that. Lets compare the inline view of MyISAM tables vs. InnoDB tables:
Slightly faster than InnoDB, but not what I would call Significant. Definitely a viable option for partitioning in v5.0. Thanks to Sheeri for the top.
Seems simple enough, let me the MERGE table over my existing tables:
mysql> create table xyz_mt (
-> `id` bigint(10) NOT NULL default '0',
-> `report_dt` date default NULL,
-> `acct_id` varchar(8) default NULL,
-> `some_text` varchar(222) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MERGE UNION=(xyz_2007_01,xyz_2007_02,xyz_2007_03) ;
Query OK, 0 rows affected (0.08 sec)
mysql> select id, report_dt, acct_id
-> from xyz_mt
-> where report_dt ='2007-02-17'
-> and acct_id = 'X0000741';
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exists
Doh! That's right, got to change to MyISAM.
mysql> alter table xyz_2007_01 engine=MyISAM;
Query OK, 1000000 rows affected (25.42 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> alter table xyz_2007_02 engine=MyISAM;
Query OK, 1000000 rows affected (17.82 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> alter table xyz_2007_03 engine=MyISAM;
Query OK, 1000000 rows affected (18.84 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
Lets see how it works now.
mysql> select id, report_dt, acct_id
-> from xyz_mt
-> where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id | report_dt | acct_id |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (2.43 sec)
Whoa, that's pretty good, I could live with that. Lets compare the inline view of MyISAM tables vs. InnoDB tables:
mysql> select * from (
-> select id, report_dt, acct_id from xyz_2007_01
-> union all
-> select id, report_dt, acct_id from xyz_2007_02
-> union all
-> select id, report_dt, acct_id from xyz_2007_03) t
-> where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id | report_dt | acct_id |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (15.17 sec)
Slightly faster than InnoDB, but not what I would call Significant. Definitely a viable option for partitioning in v5.0. Thanks to Sheeri for the top.
Thursday, April 26, 2007
MySQL Pseudo-partitioning
Got a MySQL project that needs a partitioned table. It's the ideal candidate really. Historical data that naturally would be partitioned by date. I need to keep an active window of data and delete old data quickly without doing DML.
Except partitioning is in 5.1 (more on that later) and I'm on 5.0.
One way I would overcome this limitation in Oracle Standard Edition would be to have multiple tables that hold a month worth of data and put a view on top of them. My users might notice a slight performance degradation, but it would be worth it to drop the data quickly.
So, I tried the same experiment in MySQL. I created three tables of this format:
And then I stuffed them with a million rows each. If I query a particular piece of data on a particular date, I expect to use the "xyz_rdt_acct*" index.
OK, I'm looking for two rows in the 2007_01 "partition". Lets create a view on top of those tables to present one interface for queries.
And now we query from the view.
Hmmm, that's not cool. 44 seconds to retrieve two rows. Lets try a sub-query just for kicks.
About the same, but a little bit better. Lets see what is going on.
Ah, three full table scans. Hmm, wonder why it's doing that. Lets do the same for the inline view.
Basically the same thing. Lets see what happens if I only get the fields I want in the sub-query.
Well, look at that. 16 seconds still isn't great, but at least the query is using the indexes.
Lets create a view using the same logic.
Performance is consistent at least.
Plan is consistent as well. Still, not a solution for me, but thought you might be interested in the findings.
The more I learn, the more questions I have. Tomorrow, I test partitioning on 5.1 beta.
Except partitioning is in 5.1 (more on that later) and I'm on 5.0.
One way I would overcome this limitation in Oracle Standard Edition would be to have multiple tables that hold a month worth of data and put a view on top of them. My users might notice a slight performance degradation, but it would be worth it to drop the data quickly.
So, I tried the same experiment in MySQL. I created three tables of this format:
CREATE TABLE `xyz_2007_01` (
`id` bigint(10) NOT NULL default '0',
`report_dt` date default NULL,
`acct_id` varchar(8) default NULL,
`some_text` varchar(222) default NULL,
PRIMARY KEY (`id`),
KEY `xyz_rdt_acct_2007_01` (`report_dt`,`acct_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
And then I stuffed them with a million rows each. If I query a particular piece of data on a particular date, I expect to use the "xyz_rdt_acct*" index.
mysql> select id, report_dt, acct_id
-> from xyz_2007_01
-> where report_dt ='2007-02-17'
-> and acct_id = 'X0000741';
Empty set (0.00 sec)
mysql> select id, report_dt, acct_id
-> from xyz_2007_02
-> where report_dt ='2007-02-17'
-> and acct_id = 'X0000741';
+----------+------------+----------+
| id | report_dt | acct_id |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (0.01 sec)
mysql> select id, report_dt, acct_id
-> from xyz_2007_03
-> where report_dt ='2007-02-17'
-> and acct_id = 'X0000741';
Empty set (0.00 sec)
OK, I'm looking for two rows in the 2007_01 "partition". Lets create a view on top of those tables to present one interface for queries.
mysql> create view xyz as
-> select * from xyz_2007_01
-> union all
-> select * from xyz_2007_02
-> union all
-> select * from xyz_2007_03
-> ;
Query OK, 0 rows affected (0.00 sec)
And now we query from the view.
mysql> select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id | report_dt | acct_id |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (44.36 sec)
Hmmm, that's not cool. 44 seconds to retrieve two rows. Lets try a sub-query just for kicks.
mysql> select id, report_dt, acct_id from (
-> select * from xyz_2007_01
-> union all
-> select * from xyz_2007_02
-> union all
-> select * from xyz_2007_03) t
-> where t.report_dt ='2007-02-17'
-> and t.acct_id = 'X0000741';
+----------+------------+----------+
| id | report_dt | acct_id |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (33.47 sec)
About the same, but a little bit better. Lets see what is going on.
mysql> explain select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 3000000 | Using where |
| 2 | DERIVED | xyz_2007_01 | ALL | NULL | NULL | NULL | NULL | 788670 | |
| 3 | UNION | xyz_2007_02 | ALL | NULL | NULL | NULL | NULL | 1014751 | |
| 4 | UNION | xyz_2007_03 | ALL | NULL | NULL | NULL | NULL | 1096175 | |
|NULL | UNION RESULT | union2,3,4 | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
5 rows in set (43.18 sec)
Ah, three full table scans. Hmm, wonder why it's doing that. Lets do the same for the inline view.
mysql> explain select id, report_dt, acct_id from (
-> select * from xyz_2007_01
-> union all
-> select * from xyz_2007_02
-> union all
-> select * from xyz_2007_03) t
-> where t.report_dt ='2007-02-17'
-> and t.acct_id = 'X0000741';
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 3000000 | Using where |
| 2 | DERIVED | xyz_2007_01 | ALL | NULL | NULL | NULL | NULL | 788670 | |
| 3 | UNION | xyz_2007_02 | ALL | NULL | NULL | NULL | NULL | 1014751 | |
| 4 | UNION | xyz_2007_03 | ALL | NULL | NULL | NULL | NULL | 1096175 | |
|NULL | UNION RESULT | union2,3,4 | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+--------------+------+---------------+------+---------+------+---------+-------------+
5 rows in set (35.51 sec)
Basically the same thing. Lets see what happens if I only get the fields I want in the sub-query.
mysql> explain select id, report_dt, acct_id from (
-> select id, report_dt, acct_id from xyz_2007_01
-> union all
-> select id, report_dt, acct_id from xyz_2007_02
-> union all
-> select id, report_dt, acct_id from xyz_2007_03) t
-> where report_dt = '2007-02-17'
-> and acct_id = 'X0000741';
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 3000000 | Using where |
| 2 | DERIVED | xyz_2007_01 | index | NULL | xyz_rdt_acct_2007_01 | 15 | NULL | 788670 | Using index |
| 3 | UNION | xyz_2007_02 | index | NULL | xyz_rdt_acct_2007_02 | 15 | NULL | 1014751 | Using index |
| 4 | UNION | xyz_2007_03 | index | NULL | xyz_rdt_acct_2007_03 | 15 | NULL | 1096175 | Using index |
|NULL | UNION RESULT | union2,3,4 | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
5 rows in set (16.09 sec)
Well, look at that. 16 seconds still isn't great, but at least the query is using the indexes.
Lets create a view using the same logic.
mysql> drop view xyz;
Query OK, 0 rows affected (0.01 sec)
mysql> create view xyz as
-> select id, report_dt, acct_id from xyz_2007_01
-> union all
-> select id, report_dt, acct_id from xyz_2007_02
-> union all
-> select id, report_dt, acct_id from xyz_2007_03;
Query OK, 0 rows affected (0.00 sec)
mysql> select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----------+------------+----------+
| id | report_dt | acct_id |
+----------+------------+----------+
| 20000741 | 2007-02-17 | X0000741 |
| 20967398 | 2007-02-17 | X0000741 |
+----------+------------+----------+
2 rows in set (16.70 sec)
Performance is consistent at least.
mysql> explain select id, report_dt, acct_id from xyz where report_dt ='2007-02-17' and acct_id = 'X0000741';
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 3000000 | Using where |
| 2 | DERIVED | xyz_2007_01 | index | NULL | xyz_rdt_acct_2007_01 | 15 | NULL | 788670 | Using index |
| 3 | UNION | xyz_2007_02 | index | NULL | xyz_rdt_acct_2007_02 | 15 | NULL | 1014751 | Using index |
| 4 | UNION | xyz_2007_03 | index | NULL | xyz_rdt_acct_2007_03 | 15 | NULL | 1096175 | Using index |
|NULL | UNION RESULT | union2,3,4 | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+--------------+-------+---------------+----------------------+---------+------+---------+-------------+
5 rows in set (15.99 sec)
Plan is consistent as well. Still, not a solution for me, but thought you might be interested in the findings.
The more I learn, the more questions I have. Tomorrow, I test partitioning on 5.1 beta.
Wednesday, April 11, 2007
Why aren't there more of us?
I started reading Frank Hayes' piece on H-1Bs and Students last night with doubt because the two groups represent two different experience levels. Students are entry level workers and workers on H-1B visa's are typically experienced professionals. But those entry level workers eventually want to move on to experienced positions where they will compete on an uneven playing field with H-1B workers.
Now, it's no surprise that a view in Computerworld is slanted towards importing more workers. It's a genuine concern in our industry and therefore an appropriate subject to report on. Yet, they consistently fall on the side of the debate that advocates more H-1B workers.
Frank examines the trends of Computer Science graduates and correlates it to the rise and fall of the DotCom Boom and Bust. Personally, I dismiss those cycles because they just enticed people who weren't passionate about the profession and were chasing the almighty dollar. I think kids that want to get into this business because they think they will like it are scared. Scared they will be in a six-figure job with a mortgage and two kids when some C level executive decides to offshore the whole operation to China. Scared that they'll end up being 40 and be replaced by some guy willing to take 1/2 of what they make.
Where do these crazy kids get these ideas? The national media as well as several trade publications shout it from the rooftops daily. As someone who hires IT workers regularly, I can tell you that's not the case. I'll typically go through a couple dozen resumes to fill one spot. I have a spot that went unfilled because I just couldn't find someone to fill it. Maybe you won't spend 20 years at the same company, but the work is there if you want it.
I think there is a flaw in Mr. Hayes' numbers as well. While CS graduates are most likely destined for the IT industry, there are so many other disciplines that turn out IT workers. Hiring managers need to look beyond the engineering programs and open their search up to all the majors. Some of my best people have come from respected science or business programs.
I don't think it's all gloom and doom for the IT industry in this country. The press may lead you to think that everything is being outsourced to somewhere else, but the fact is there's a lot of work out there for the taking.
Now, it's no surprise that a view in Computerworld is slanted towards importing more workers. It's a genuine concern in our industry and therefore an appropriate subject to report on. Yet, they consistently fall on the side of the debate that advocates more H-1B workers.
Frank examines the trends of Computer Science graduates and correlates it to the rise and fall of the DotCom Boom and Bust. Personally, I dismiss those cycles because they just enticed people who weren't passionate about the profession and were chasing the almighty dollar. I think kids that want to get into this business because they think they will like it are scared. Scared they will be in a six-figure job with a mortgage and two kids when some C level executive decides to offshore the whole operation to China. Scared that they'll end up being 40 and be replaced by some guy willing to take 1/2 of what they make.
Where do these crazy kids get these ideas? The national media as well as several trade publications shout it from the rooftops daily. As someone who hires IT workers regularly, I can tell you that's not the case. I'll typically go through a couple dozen resumes to fill one spot. I have a spot that went unfilled because I just couldn't find someone to fill it. Maybe you won't spend 20 years at the same company, but the work is there if you want it.
I think there is a flaw in Mr. Hayes' numbers as well. While CS graduates are most likely destined for the IT industry, there are so many other disciplines that turn out IT workers. Hiring managers need to look beyond the engineering programs and open their search up to all the majors. Some of my best people have come from respected science or business programs.
I don't think it's all gloom and doom for the IT industry in this country. The press may lead you to think that everything is being outsourced to somewhere else, but the fact is there's a lot of work out there for the taking.
Wednesday, April 04, 2007
10gR2/9iR2 Distributed bugs
Our environment is highly distributed on a bunch of nodes partitioned out by business area. When a business area needs information from another business area, we create a Materialized View and pull the data across a database link. Most systems don't need up-to-the-minute data and when they do, there's other ways to get it.
Now, we're in a somewhat unique position because we're migrating Solaris 9iR2 dbs to Linux 10gR2. In fact, we just migrated one this weekend and experienced anywhere from 100% to 500% increase in performance depending on how much database work was actually being done. However, we did encounter some issues with db links that you might want to be aware of when migrating.
The master in this case was a 9.2.0.5 db on Solaris. We upgraded it to 10.2.0.3 and moved to Linux at the same time (good old export/import). There were several "client" dbs that ran the gamut across Oracle versions and OS Platforms.
The first problem was with 9.2.0.8/Solaris trying to get data from the 10.2.0.3/Linux db. We kept running into an error while trying to refresh the Mviews:
Here, the problem stems from bug 5671074. In this bug, 9.2.0.8 has a problem using dblinks when the "Endianess" doesn't match between sites. Also check out ML Note 415788.1. There is a one-off patch for the 9.2.0.8 software that needs to be applied and it worked fine for us.
The second issue was a little more straight forward, but unfathomable how it didn't get caught in testing. Here, we have two 10.2.0.3 dbs trying to use an unauthenticated database link (ie. no username/password hard-coded into the definition). An OS Authenticated user can connect to both dbs, but when he tries to access an object across a database link, he is presented with ORA-01017: invalid username/password. This functionality has worked since 8i and it escapes me how it slipped through the test cycle. This too resulted in a bug, but in 10.2. Check out bug 4624183 and ML Note 339238.1 for the current status. As of this post, the bug is fixed in 11.0 and has been backported to some platforms (not mine, of course). The only work around that worked for us was to use authenticated db links with the username/password embedded in the db link. Yuck.
Now, we're in a somewhat unique position because we're migrating Solaris 9iR2 dbs to Linux 10gR2. In fact, we just migrated one this weekend and experienced anywhere from 100% to 500% increase in performance depending on how much database work was actually being done. However, we did encounter some issues with db links that you might want to be aware of when migrating.
The master in this case was a 9.2.0.5 db on Solaris. We upgraded it to 10.2.0.3 and moved to Linux at the same time (good old export/import). There were several "client" dbs that ran the gamut across Oracle versions and OS Platforms.
The first problem was with 9.2.0.8/Solaris trying to get data from the 10.2.0.3/Linux db. We kept running into an error while trying to refresh the Mviews:
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-04052: error occurred when looking up remote object MYUSER.MVIEW_NAME@DBLINK_NAME
ORA-00604: error occurred at recursive SQL level 2
ORA-03106: fatal two-task communication protocol error
ORA-02063: preceding line from DBLINK_NAME
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
Here, the problem stems from bug 5671074. In this bug, 9.2.0.8 has a problem using dblinks when the "Endianess" doesn't match between sites. Also check out ML Note 415788.1. There is a one-off patch for the 9.2.0.8 software that needs to be applied and it worked fine for us.
The second issue was a little more straight forward, but unfathomable how it didn't get caught in testing. Here, we have two 10.2.0.3 dbs trying to use an unauthenticated database link (ie. no username/password hard-coded into the definition). An OS Authenticated user can connect to both dbs, but when he tries to access an object across a database link, he is presented with ORA-01017: invalid username/password. This functionality has worked since 8i and it escapes me how it slipped through the test cycle. This too resulted in a bug, but in 10.2. Check out bug 4624183 and ML Note 339238.1 for the current status. As of this post, the bug is fixed in 11.0 and has been backported to some platforms (not mine, of course). The only work around that worked for us was to use authenticated db links with the username/password embedded in the db link. Yuck.
Sunday, April 01, 2007
My DST Nightmare
I had to be somewhere this morning at 9:00. It would take a little over an an hour to get there, so I figured I better leave by 7:30 just to be safe. That meant setting the alarm clock to 6:45.
6:45 came and the alarm went off as expected. I jumped into the shower, got dressed, and as I was heading out the door my wife says "Where are you going so early?" I told her and she rolled back over and went back to sleep.
Then I jumped into a car I usually don't drive. "Hmm, the clock says 6:32", I thought to myself, "must be I haven't driven this car for three weeks."
I reset the clock to the correct time and was off.
I got to my destination at 8:50 and there were only a couple people there. I was expecting about 60 or 70!
I approached the guy in charge and said "I must have read they flyer wrong..."
"Oh, that's today, but starts at 9:00" he says.
I looked at my phone and it said 7:50. 7:50, WTF?
Ah, yes, Daylight Savings Time. Not the "new" Daylight Savings Time, but the "old" Daylight Savings Time.
I have one of those "fancy" clocks that automatically adjusts for Daylight Savings Time. Except, I got it a couple years ago and it still thinks that the first Sunday in April is the right time to "spring forward".
Sure enough, I got home and my clock was an hour ahead of everything else. I promptly went back down to the car and reset the clock. Interestingly enough, my W2K box that was supposed to be patched with all the latest updates was also an hour ahead.
6:45 came and the alarm went off as expected. I jumped into the shower, got dressed, and as I was heading out the door my wife says "Where are you going so early?" I told her and she rolled back over and went back to sleep.
Then I jumped into a car I usually don't drive. "Hmm, the clock says 6:32", I thought to myself, "must be I haven't driven this car for three weeks."
I reset the clock to the correct time and was off.
I got to my destination at 8:50 and there were only a couple people there. I was expecting about 60 or 70!
I approached the guy in charge and said "I must have read they flyer wrong..."
"Oh, that's today, but starts at 9:00" he says.
I looked at my phone and it said 7:50. 7:50, WTF?
Ah, yes, Daylight Savings Time. Not the "new" Daylight Savings Time, but the "old" Daylight Savings Time.
I have one of those "fancy" clocks that automatically adjusts for Daylight Savings Time. Except, I got it a couple years ago and it still thinks that the first Sunday in April is the right time to "spring forward".
Sure enough, I got home and my clock was an hour ahead of everything else. I promptly went back down to the car and reset the clock. Interestingly enough, my W2K box that was supposed to be patched with all the latest updates was also an hour ahead.
Friday, March 30, 2007
The final nail for shared servers
I've been running shared servers and dedicated servers in a mixed mode for a number of years. In theory, there are a lot of advantages to shared servers; connection pooling, connection multiplexing, and pre-established connections.
I like to setup one database alias for dedicated connections (middle-tiers, batch jobs, etc.) and one for shared connections (CGI scripts, mod_plsql, and Client/Server sessions that have a lot of "think" time). Each alias then points to a specific listener that is either setup as a local listener or is a dispatcher. Granted, tracing is never easy on a session that is connected to a shared server. But, in general, it worked pretty good.
About a year ago I had a problem where my multiplexed connections periodically got disconnected. We tracked it down to an issue with multiplexing (implemented in shared servers) on 9.2 and Linux. For this particular application, shared servers weren't critical, so we just rerouted the alias to a dedicated listener.
Over the past 6 months or so, we've been experiencing in-memory block corruption in 9.2 dbs on 64-bit Linux. It was on multiple hosts, multiple dbs, and different patch levels of Oracle. Since the problem wasn't consistent and disguised itself as multiple ORA-00600/07445 errors we, nor Oracle Support, could ever track the root cause. Time would go by without error, the TAR would get closed, and we would experience a different ORA-00600 error.
After much prodding, we finally got an analyst at Oracle that understood what we were up against and looked at ALL our TARs, not just the current problem. They pieced together everything and pointed to bug 5324905; memory corruption from shared servers.
While they are great in theory, the disadvantages of shared servers outweigh the advantages at this point.
I like to setup one database alias for dedicated connections (middle-tiers, batch jobs, etc.) and one for shared connections (CGI scripts, mod_plsql, and Client/Server sessions that have a lot of "think" time). Each alias then points to a specific listener that is either setup as a local listener or is a dispatcher. Granted, tracing is never easy on a session that is connected to a shared server. But, in general, it worked pretty good.
About a year ago I had a problem where my multiplexed connections periodically got disconnected. We tracked it down to an issue with multiplexing (implemented in shared servers) on 9.2 and Linux. For this particular application, shared servers weren't critical, so we just rerouted the alias to a dedicated listener.
Over the past 6 months or so, we've been experiencing in-memory block corruption in 9.2 dbs on 64-bit Linux. It was on multiple hosts, multiple dbs, and different patch levels of Oracle. Since the problem wasn't consistent and disguised itself as multiple ORA-00600/07445 errors we, nor Oracle Support, could ever track the root cause. Time would go by without error, the TAR would get closed, and we would experience a different ORA-00600 error.
After much prodding, we finally got an analyst at Oracle that understood what we were up against and looked at ALL our TARs, not just the current problem. They pieced together everything and pointed to bug 5324905; memory corruption from shared servers.
While they are great in theory, the disadvantages of shared servers outweigh the advantages at this point.
Thursday, March 22, 2007
The beauty of having your business logic in the database...
You may recall that I banged together a small application using Apex (nee HTMLDB) a few months ago. I got a call from a user the other day that something wasn't working quite right. Seems as one of the pages was giving them an incorrect result. I hadn't touched this particular application in about 2 months, but I knew it pulled information from a view I created just to make the Apex interface easy. The view called a piece of PL/SQL code that calculated a particular metric on-the-fly and due to some business rules, that calculation changed. It was a business-wide change, so I consulted the parties that use this package and we all agreed on the change to be made. I made the change and everybody was happy. I didn't have to get back into Apex or anything, just a simple PL/SQL change and I was done.
For those people that thought they had a better idea and rewrote the business logic in their Java middle-tier, good luck.
For those people that thought they had a better idea and rewrote the business logic in their Java middle-tier, good luck.
Tuesday, March 20, 2007
Monday, March 12, 2007
ORA-01009 During Gap Resolution
Encountered an interesting error with an Oracle 10.2.0.3 physical standby database.
When the standby database encounters a gap in the archivelog sequence, it tries to resolve the gap but encounters an ORA-01009: missing mandatory parameter. The interesting thing is that the standby eventually resolves the gap and gets caught up.
At first, my DB's are in sync. The standby db is applying logs as they come in without issue:
Then, a gap is artificially generated by deferring log_archive_dest_2 log shipping at log 2045. When log shipping is enabled again, my standby recognizes the gap, but generates the ORA-01009 error.
Somehow, though, the gap is resolved and the logs are applied.
The mrp0.trc file doesn't give me very many clues either:
At first I thought this might be NLS related, but both primary and standby hosts are configured similarly. The oerr tends to indicate some OCI/Precompiler issue, but I would think Oracle knows how to use their own libraries. Still trying to figure this one out...
When the standby database encounters a gap in the archivelog sequence, it tries to resolve the gap but encounters an ORA-01009: missing mandatory parameter. The interesting thing is that the standby eventually resolves the gap and gets caught up.
At first, my DB's are in sync. The standby db is applying logs as they come in without issue:
Completed: ALTER DATABASE RECOVER managed standby database disconnect
Fri Mar 9 15:27:59 2007
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 17076
RFS[1]: Identified database type as 'physical standby'
Fri Mar 9 15:27:59 2007
RFS LogMiner: Client disabled from further notification
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/u01/oraarch/db1/1_2043_612798267.dbf'
Fri Mar 9 15:28:08 2007
Media Recovery Log /u01/oraarch/db1/1_2043_612798267.dbf
Media Recovery Waiting for thread 1 sequence 2044
Fri Mar 9 15:28:14 2007
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/u01/oraarch/db1/1_2044_612798267.dbf'
Fri Mar 9 15:28:22 2007
Media Recovery Log /u01/oraarch/db1/1_2044_612798267.dbf
Media Recovery Waiting for thread 1 sequence 2045
Then, a gap is artificially generated by deferring log_archive_dest_2 log shipping at log 2045. When log shipping is enabled again, my standby recognizes the gap, but generates the ORA-01009 error.
Media Recovery Waiting for thread 1 sequence 2045
Fetching gap sequence in thread 1, gap sequence 2045-2045
FAL[client, MRP0]: Error 1009 fetching archived redo log from db1.us
Fri Mar 9 15:28:29 2007
Errors in file /oracle/app/oracle/admin/db1/bdump/db1_mrp0_16203.trc:
ORA-01009: missing mandatory parameter
Fri Mar 9 15:28:30 2007
Somehow, though, the gap is resolved and the logs are applied.
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/u01/oraarch/db1/1_2045_612798267.dbf'
Fri Mar 9 15:28:43 2007
The mrp0.trc file doesn't give me very many clues either:
/oracle/app/oracle/admin/db1/bdump/db1_mrp0_16203.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
ORACLE_HOME = /oracle/app/oracle/product/10.2.0.3
System name: Linux
Node name: dev101
Release: 2.6.9-42.0.3.EL
Version: #1 Mon Sep 25 17:14:19 EDT 2006
Machine: i686
Instance name: db1
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 16203, image: oracle@dev101 (MRP0)
*** SERVICE NAME:() 2007-03-09 15:26:53.586
*** SESSION ID:(153.1) 2007-03-09 15:26:53.586
ARCH: Connecting to console port...
*** 2007-03-09 15:26:53.586 61283 kcrr.c
MRP0: Background Managed Standby Recovery process started
*** 2007-03-09 15:26:58.586 1102 krsm.c
Managed Recovery: Initialization posted.
*** 2007-03-09 15:26:58.586 61283 kcrr.c
Managed Standby Recovery not using Real Time Apply
Recovery target incarnation = 1, activation ID = 207497211
Influx buffer limit = 2245 (50% x 4491)
Successfully allocated 2 recovery slaves
Using 543 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 39582826471 logseq 2043 block 2
*** 2007-03-09 15:26:58.663
Media Recovery add redo thread 1
*** 2007-03-09 15:26:58.663 1102 krsm.c
Managed Recovery: Active posted.
*** 2007-03-09 15:26:58.930 61283 kcrr.c
Media Recovery Waiting for thread 1 sequence 2043
*** 2007-03-09 15:28:08.945
Media Recovery Log /u01/oraarch/db1/1_2043_612798267.dbf
*** 2007-03-09 15:28:12.150 61283 kcrr.c
Media Recovery Waiting for thread 1 sequence 2044
*** 2007-03-09 15:28:22.151
Media Recovery Log /u01/oraarch/db1/1_2044_612798267.dbf
*** 2007-03-09 15:28:24.353 61283 kcrr.c
Media Recovery Waiting for thread 1 sequence 2045
*** 2007-03-09 15:28:29.353 61283 kcrr.c
Fetching gap sequence in thread 1, gap sequence 2045-2045
*** 2007-03-09 15:28:29.470 61283 kcrr.c
FAL[client, MRP0]: Error 1009 fetching archived redo log from db1.us
ORA-01009: missing mandatory parameter
*** 2007-03-09 15:28:59.471
Media Recovery Log /u01/oraarch/db1/1_2045_612798267.db
At first I thought this might be NLS related, but both primary and standby hosts are configured similarly. The oerr tends to indicate some OCI/Precompiler issue, but I would think Oracle knows how to use their own libraries. Still trying to figure this one out...
Tuesday, March 06, 2007
Targeted Marketing
You know those coupons you get at the end of your register receipt at the grocery store? You know the kind, you buy Charmin and they give you $.25 of Scotts. I went to CVS the other day to pickup a bag of M&Ms for an afternoon snack. I check the bottom of the coupon since occasionally they'll give you a $.50 off your next purchase. What was on the bottom but a coupon for $1.00 of my next purchase of Playtex Tampons. Methinks somebody needs to tune their Datawarehouse...
Monday, March 05, 2007
If it's good enough for the DOT...
Make no mistake about it, I'm not running out to "upgrade" to Windoz anything as long as my heart is pumping. However, if you're stuck in a Windoz environment, here's a unique take on Windows Vista upgrades; just don't do it.
On a side note, I can see six network administrators standing over a PC with one PC-Tech replacing the hard drive at the DOT. Or maybe that's just stereotypical....
On a side note, I can see six network administrators standing over a PC with one PC-Tech replacing the hard drive at the DOT. Or maybe that's just stereotypical....
Sunday, March 04, 2007
Oracle's January CPU update
Hi all!
Thought I'd share a potential problem solver for the latest Jan CPU patch for Oracle. If you're running Solaris 10, the patch could possibly fail because Solaris 10 adds "a few bytes"to the size of the objects and opatch bombs during the validation phase.
Two ways to solve this... upgrade your opatch version to 1.0.0.0.56 (or better) or Set the environment variable OPATCH_SKIP_VERIFY=TRUE before you start. Personally I'd opt to go get the latest version of OPatch Patch 4898608 (considering Oracle just updated it 14-feb-2007)
Check out note:397342.1 for further details.
Yeah, I know it's March already I don't want to hear it...
Thought I'd share a potential problem solver for the latest Jan CPU patch for Oracle. If you're running Solaris 10, the patch could possibly fail because Solaris 10 adds "a few bytes"to the size of the objects and opatch bombs during the validation phase.
Two ways to solve this... upgrade your opatch version to 1.0.0.0.56 (or better) or Set the environment variable OPATCH_SKIP_VERIFY=TRUE before you start. Personally I'd opt to go get the latest version of OPatch Patch 4898608 (considering Oracle just updated it 14-feb-2007)
Check out note:397342.1 for further details.
Yeah, I know it's March already I don't want to hear it...
Subscribe to:
Posts (Atom)
