Old SANs Die Hard

If you, like me, hate throwing hardware away, you may find yourself repurposing a Dell MDx000/MDX000i SAN as a bulk device for dev/qa. If so, be sure you either have SATA interposers from your existing drives or can get them, because your MD will not like you until you do.

Oracle VM installation woes for VMWare veterans

I'll keep this simple:

If you are installing Oracle VM on a single host, without shared storage (say, for testing), do yourself a favor and create two virtual disks at the RAID controller level. OVM will not 'see' unused space on a partitioned volume.

If you have /dev/sda containing partitions sda1, sda2, and sda3 and gobs of free space left over, OVM will not mount the remaining space in /OVS, which is where your virtual machine files should go. So, save yourself some trouble:

1. Scrub the RAID config you just did on the host machine and create two virtual disks on the same array. Init the disks. It's not like you have data there anyway!
2. Run the OVM Server installer
3. Deselect the larger /sd? as a volume to install on.
4. Complete the installation. 
5. Log in to OVM Manager and create a server pool. UNCHECK 'Clustered Server Pool'. 
6. Create a repository. 
7. Set the location to 'Physcial disk.' Specify the desired pool.

I understand a single server environment is not Oracle's desired target. But, by contrast, once you learn to administer a single VMWare machine, exteding into vCenter is very natural. Take a hint, gang.

On FastCGI Error 258

Using IIS 6 on Server 2003 x86 Standard. 

Config that was working a few days ago started throwing:
 FastCGI Error The FastCGI Handler was unable to process the request. Error
Details: The FastCGI process exceeded configured activity timeout Error Number: 258 (0x80070102).
Error Description: The wait operation timed out.
HTTP Error 500 - Server Error. Internet Information Services (IIS)

Recycling the app pool didn't fix the issue, nor did restarting IIS. No config changes had been made. No errors were being logged by PHP becasue we weren't making it to PHP code. No errors other than the generic 500 were being logged by IIS.

I figured restarting the server would fix it because, hey, this is Windows. But, then - there were all these funny iexplore.exe processes running under NETWORK SERVICE. Why are those there?

Suffice to say - kill 'em. Your PHP will spring, magically, back to life. We all know IE sucks and this is just another nail in that coffin.

Fun with PGA

I've had a few weeks to decide that this is still a good idea. So as a light topic for my first "real" post, I chose to chronicle one of my recent experiences with Oracle's rules on PGA allocation and the pga_aggregate_target (PAT) parameter.

Full disclosure: I run Oracle on Windows. I know that makes me some kind of red-headed stepchild. I'm not at liberty to change platforms, so it is what it is. Although I further acknowledge that running Oracle on 32-bit Windows is a commonly recognized sign of masochism.

Server setup
The server in question has Windows Server 2003, Enterprise Edition, 32-bit. 2 quad-core processors. 16 GB RAM. VLM (Very Large Memory) is in use. The boot.ini has /PAE, /3GB, and /USERVA=2900. I'll discuss the boot.ini setup more in a later post on VLM.

Oracle setup
10.2.0.4

shared_pool_size=600M
java_pool_size=16M
large_pool_size=90M
streams_pool_size=8M
db_block_buffers=667347
db_block_size=16384
pat=200M
use_indirect_data_buffers = TRUE

In the registry, awe_window_memory is set to 1.2 GB.

Having VLM enabled requires that the sga components be set manually. The total SGA there is 1942 MB. That leaves under a gigabyte for everything else that needs to occur under oracle.exe - namely, the PGA required for 90 users. And, as it turned out, the PGA required for this particular set of 90 users was just too much.

Crashes. Oh, the crashes. 4030s. 4031s. The works. Restart after restart. We eventually told the site to just reboot first and then call second. We dropped the values on every parameter. We had the shared pool set so low I'm amazed the instance stayed up. We had PAT down around 50M. Not cool.

And then I noticed something 'strange.' Strange, in this case, means:

strange (adj.) - a quality that indicates someone smarter than yourself would have known about this in advance (or at least figured it out faster. Dummy.)


What was strange was that as we continued decreasing PAT, the total PGA allocation for the instance was not staying any lower than previously. We were buying some time between reboots, but the high water mark was roughly the same. Also, as a bonus, performance was crap. 


I started doing some research and discovered the following critically important information:


The PAT -> PGA relationship involves a set of rules that was likely drafted by Satan or some equally devious entity. Oracle hints subtly at the nefarious nature of PAT by the name of the parameter - PGA_AGGREGATE_TARGET. The English translation is "the desired allocation of PGA memory across all sessions." This is not a maximum. It's not a minimum. It's a polite request for the database to consider your wishes, ala the following vignette:


//
DBA: "Please, please, nice Oracle, could you keep this wonky, misunderstood pool of memory around this tiny number without knowing anything in advance about the work that will be attempted? Because I haven't migrated to a 64-bit platform yet and I don't really have enough memory to run this many users in this database."  


Oracle: "How dare you ask the mighty Oracle to cap PGA at this ludicrously low value?! I am angered by your incompetence! I shall allocate as I wish, and no mere DBA shall stop me lest he wield undocumented parameters!!!!"
//

And that, dear friends, is why v$pgastat has an 'over allocation count' statistic. That's how many times Oracle has pwn3d your feeble limit on PGA per session since the instance was restarted. Despite my best efforts to reduce the total memory usage the site was still hosed. Yes, we opened an SR. The analyst refused to see past the ORA-04031 in the alert log (which we had practically asked for when we slashed the shared pool size, we knew that) and wouldn't allow us to use the undocumented _pga_max_size parameter. 'Shared pool fragmentation' was all he would say.

I determined that the answer was ditching VLM. Calculating backwards, based on the PGA high water mark of roughly 800 MB (note: NOT THE SAME as PAT) and before adding AWE_WINDOW_MEMORY we were at 1500 MB (with a properly sized shared pool.) Tack on the window size and that's 2700 MB. Not much wiggle room, is there? Truth is, you gotta count for PGA first if at all possible since Oracle will try to take it anyway. Then you'll have a clearer picture of where you should set sga_target and sga_max_size. More on how PAT affects PGA in 10gR2 in a later post.

Oh, and the site is now running 64-bit. Go figure.

Welcome

SQL> SELECT 'hello world' AS greeting FROM DUAL;

I'll pretend, for the moment, that someone I'm not related to will actually find this and choose to spend time reading it. For that someone, welcome. My name's Brandon. I work for a software company. When something doesn't work, I fix it, or tell the customer what they need to fix, or I figure out where it's broken so some developer can fix it. I'm a Windows guy. And a hardware guy. And a sometimes Oracle guy. Outside of work, I'm a husband guy. And a father guy. And a music guy. And a church guy. And, and, and. And. Heck, sometimes I even try to fix my cars. We'll save those stories for days that need humor.


So why blog?

For a brain dump location. I spend a lot of time figuring odd things out. For example, when I started typing this post, I didn't know how to make the SQL statement appear in a code box. A short search later and I've 'discovered' the textarea tag. I know that I'll forget before too long. The blog will remember. I can use the power of Google's Ph.D.s to search it when I need the info again. All that to say, my brain has some very interesting retention policies. I'm sure my wife will chuckle when she reads that.


To share information with my industry peers. It's unlikely that I'll come up with an original solution, but I often need to aggregate data from different sources to come up with a holistic view of a problem and a complete solution. Also, my job has forced me to understand some things that are oft misunderstood. Maybe I'll be able to clarify something for somebody every now and then. This is partly selfish - I would like to see the overall quality of technician that I deal with on a daily basis raised, and I'd like to improve myself.


Because I suffer from 2 a. m. Syndrome. That's where you're up until 2 a. m. on a regular basis. This is typically because, at 11 p.m., you thought something like the following: 
"I wonder how hard it would be to add rndis support to the 2.6.25 kernel that runs Android on my HTC Vogue..." (Answer: Really hard if you don't know C.)


Because I'm constantly diving into new things. See blog title. In my book, the only thing worse than stumbling through something the first time is stumbling through it a second time. Keeping records is key.

To share my thoughts on any number of non-technical subjects. I suppose that's blogging 101. While the bulk of my posts here will be about things I encounter through work, I do have other interests and reserve the right to post about them at any time. You, of course, have the right not to read about them.  :)

I think this will be fun. With any luck it will be helpful and maybe, just maybe, entertaining at times. Thanks for reading!

HTH, HYEI, HYCFATSAIU.