Wednesday, June 29, 2011

Ensure CELL_OFFLOAD_PROCESSING is FALSE in Non-Exadata

A colleague of mine recently brought to my attention a curious wait event that they were experiencing for 'ASM file metadata operation' which occurs when doing operations such as a DROP TABLESPACE, or in this case when using Data Pump. Though the server was basically not doing anything the CPU usage was 100% and there was the strange wait event on 'ASM file metadata operation' with high waits on 'ksv master wait'. So to Oracle Support they went via opening an SR.

It seems that upon applying a PSU to 11.2.0.2 (as of this writing PSU 2 is that latest so likely this will not occur in PSU 3 but that is only my guess) the parameter cell_offload_processing is set to TRUE. In an Exadata environment this is the appropriate setting, however, in a non-Exadata environment, which it was in this case, this causes performance issues to arise as processes on the RDBMS side await on a reply from the ASM side which is trying to delivery smart-scan results.

The quick fix is of course to simply reset the parameter to FALSE, i.e. 'ALTER SYSTEM SET cell_offload_processing = FALSE'. If you prefer you can instead apply patch 11800170. Per the MOS note, "High 'ksv master wait' And 'ASM File Metadata Operation' Waits In Non-Exadata 11g [ID 1308282.1]" this issue is fixed in 11.2.0.3 and 12.1.

Of course 11.2.0.3 is the likely next patch set which is not yet released but is likely in testing. I've been seeing this 12.1 designation on MOS recently which is a good indication that this is the next version for the database, which is likely in early testing. I think that is an entry for a good blog, namely what features I'd like to see in the next database release, and what is likely to be included by Oracle. Attending OpenWorld 2011 would certainly help with that posting, come on Oracle, where is my Blogger approval ;-).

Monday, June 27, 2011

Oracle OpenWorld 2011

I recently signed up for Oracle OpenWorld 2011 (Blogger registration) just last week. So now I am awaiting to be approved (or not as the case may be). I found out about this additional registration process last year when a friend of mine got through.The requirements are pretty basic, i.e. a blog about Oracle products and/or solutions and updates within the last month. Your application will then get sent through to Oracle Corporate Communications team for approval. My friend signed up again, so we are both hoping to get approved and attend together though with a new baby on the way he is more likely to not attend.

I've been to two OpenWorld events (both in San Francisco), and in all honesty it should be a must visit event for anyone in the industry (at least once). The full attendance fee might be a bit prohibitive for some at over $2,000 (for regular folk) but is well worth the investment, which is exactly how it should be seen. But, in case you don't have the budget for the full attendance there are a few options for attending such as the Blogger registration, or a Discover pass ($50 - $125 depending on registration date) to name a few. I'm not too keen on the Discover pass since you don't get to attend any sessions (or the parties!), but it is an entry way to the other invaluable opportunities such a networking.You can find a comparison of the different packages on the Oracle OpenWorld site here. If you do get approved, there is still of course, the travel and accommodation considerations which many book a full year in advance to avoid the rise in prices, and also availability. My first attendance was on a pass from our Oracle solution provider in 2006, with hotel and flight covered by the company. I was lucky enough to find a surprisingly good motel just 3 blocks away which is walking distance and where you want to be unless you like commuting. Last year I stayed in the Oakland area with a friend and had to commute via a mile walk, train, and then 2 blocks. Lots of good exercise but with all the walking you will do at OpenWorld, your feet will not be thanking you regardless of good shoes!

If you think selling your attendance is difficult (so you can get the company to pay for something to help you out, even if it is just for your time), think again. Oracle is very thoughtful in helping you to write a return on investment letter to your managers or whomever. You can find a link here to that helpful page. I have similar points:

- great educational opportunity to learn about Oracle products and solutions from experts, actual customers with experience, and Oracle themselves. You do need to plan your sessions based on your level of expertise and what you are trying to get out of your trip in order to get the full benefit. From my experience too many of the sessions are not appropriately labeled for the correct expertise level and their contents, so I often find myself moving from room to room within the same time period to catch something of interest based on my schedule (which is why I register for a few concurrent sessions).

- networking with peers, experts, and Oracle staff from all areas including development, support, consulting, managerial, and 'C' levels. This is a big sell since you can get really good contacts within Oracle to aid in addressing immediate or future issues such as the developers that work on GoldenGate, Enterprise Manager, ASM, or the Oracle kernel.

- contact existing and new vendors. While you do get the usual song and dance, there are really good contacts to be made as some vendors (mostly the larger companies) bring out some of their hidden internal talent to answer questions. Seeing the various offerings, especially for something you need but didn't know a company existed that offered a solution is golden.

Of course, the parties are incredible, and the overall experience overwhelming. I do think that for anyone that has ever attended the feedback is the same. You need to know what you want from the event, plan accordingly, and send enough people to get the job done. At least 3 people should be sent, as only one will simply not work unless your needs are really narrow. There is simple too much ground to cover for one person.

The event is from Oct 2 - 6 at the Moscone Center in San Francisco. Do yourself a favor and register now to attend. You will not regret the experience.

Monday, June 13, 2011

RMAN is not removing my logs, why?

I'm not going to go into the details and various scenarios of RMAN and archive logs not being removed. The purpose of this entry is (other than a filler) to provide a head's up, if you will, as to what you are likely to encounter now in an 11g environment using RMAN and GoldenGate (or Streams). I've made mention in my prior post on the relationship between GoldenGate and Streams here.


So it seems that again, due to our usage of GoldenGate, that Streams is also being enabled under the covers. It seems also that there is a 6 hour delay in archive logs being removed via RMAN even if they are not required! Once again, doing the requisite research, we found that this is actually expected behavior. RMAN is looking at the MIN_REQUIRED_CAPTURE_CHANGE# in v$database and not at DBA_CAPTURE which only gets updated every 6 hours. There is a workaround which involves forcefully deleting the archive logs using an RMAN statement similar to:

delete noprompt force archivelog all completed before 'sysdate-10/1440';

I'm not sure why the change in behavior which now leads to this situation but I can't say were too thrilled. Seems like a bug or perhaps not yet fully thought out or completed perhaps? In any case, we've got the backup scripts and everything thought out so no harm, no foul. Moving on with the project...

Oh, you can reference the MOS article: "RMAN-08137 When deleting archivelogs even when Streams CAPTURE does not require them [ID 1079953.1]"

Oracle GoldenGate and Streams Integration?

We are still doing our Exadata migration, apologies for not posting more on this as it progresses but I find it hard to determine what is, and is not acceptable to blog (confidentially, NDA and all that stuff). I'm also quite busy and hardly find the time to blog generally. In any case, we are now on the 3rd environment, that being production support or QA, and have noticed that even though we do not have any Streams configured, we are seeing miscellaneous Streams events as one of the top wait events. It turns out that is not only expected but can be regarded as an idle wait. This happens because we are running GoldenGate Extract with the THREADOPTIONS DBLOGREADER parameter which enables the use of the OCIPOGG module. The wait (OTHER) is being reported while GoldenGate waits for additional logfile data to be made available.

So it would seem with the latest GoldenGate (11.1.1.1) that there is some integration with Streams. This makes sense since they are both Oracle products and per Oracle's Goldengate Statement of Direction GoldenGate is the strategic direction where the best of Streams will be integrated into GoldenGate.

For further information please reference MOS article: "Why do I see Streams Miscellaneous Event in AWR as a top event when Goldengate Extract is running [ID 1317122.1]".