Feed aggregator

Identifying mutex holder

Fairlie Rego - Tue, 2007-10-16 20:55
In my previous post on flushing a cursor we see that a process is waiting on a mutex. In this post we will try to identify the holder from a systemstate dump

• Find the process executing the purge API. In this case it was process 22
• Open the systemstate dump and go to PROCESS 22
• Under this (if the systemstate is at level 266) you will see the short stack of the process

ksdxfstk()+36<-ksdxcb()+2452<-sspuser()+176<-sigacthandler()+44<-__systemcall()+52<-semtimedop()+28<-sskgpwwait()+224<-ksliwat
()+988<-kslwaitns_timed()+48<-kskthbwt()+232<-kslwait()+116<-kksLockWait()+396<-kgxWait()+444<-kgxExclusive()+132<-kxsPurgeCur
sor()+476<-psdpur()+1444<-pevm_icd_call_common()+728<-pfrinstr_ICAL()+132<-pfrrun_no_tool()+72<-pfrrun()+832<-plsql_run()+696<
-peicnt()+260<-kkxexe()+824<-opiexe()+12736<-kpoal8()+1912<-opiodr()+1548<-ttcpip()+1284<-opitsk()+1432<-opiino()+1128<-opiodr
()+1548<-opidrv()+896<-sou2o()+80<-opimai_real()+124<-main()+152<-_start()+380


As can be seen from the above stack it includes the function kxsPurgeCursor() which contains this piece of functionality (to flush the cursor)

• Under the session state object of this process you will see that the session is waiting on

waiting for 'cursor: pin X' blocking sess=0x0 seq=9208 wait_time=0 seconds since wait started=0 idn=dbabc3c, value=1, where|sleeps=e00000629

wait_time=0 indicates the session is waiting at the time this process was dumped.

• If you scroll down further in the same process state object and search for idn=dbabc3c

KGX Atomic Operation Log 3d6e12a08
Mutex 3da75c7d0(0, 1) idn dbabc3c oper GET_EXCL


Hence this process is waiting to acquire the mutex in exclusive mode.

• To find the process holding the mutex search on the string "dbabc3c oper"

You will find something like the below

KGX Atomic Operation Log 3d6e671e0
Mutex 3da75c7d0(0, 1) idn dbabc3c oper SHRD


Search the process holding this.
In vi editor you can use ?PROCESS which will lead you to

PROCESS 15:
-------------------------------------------


Hence Process 15 is holding the mutex and this is the process doing the merge join Cartesian in Session 1.

We're a little disappointed about Doris Lessing

Fake Larry Ellison - Tue, 2007-10-16 01:53

My wife Melanie really thought she was going to win the Nobel Prize in Literature this year. Don't ask me why. I mean, all of her books have been out of print for years now. And even when they were still in print, the only people who read them were desperate stay-at-home-moms and gay men and horny teenage girls who kept vibrators under their mattresses. But she still thought she was going to win. I warned her. I told her she was cruising for a bruising. Before we got married I said, Melanie, only 11 women have won the Nobel Prize in Literature in 106 years. And she said that she knew that and she wasn't going to get her hopes up or anything. But did she listen to me? No, she did not. She started crying her eyes out last Friday when I was supposed to be closing the deal with BEA Systems. It sort of frigged everything up. Anyway, that's what I was this weekend -- a shoulder to cry on. You're jealous, I know.

Blog Action Day

Peter Khos - Mon, 2007-10-15 22:18
Today is Blog Action Day where each blogger is to blog about the environment related to their topic/theme.I am in IT so what can I blog about which would be relevant. Well, I recently attended Gartner Symposium ITxpo where "Green IT" is a key theme for the Symposium and Gartner expects this to be an ongoing theme for the IT industry for the next 3 to 5 years.Michael Dell as one of the keynote Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

Multi-Foundation on UNIX

Mark Vakoc - Mon, 2007-10-15 19:43
Today I wanted to setup multi-foundation on the same enterprise server on my Linux based server. I had an existing server that I wanted to "clone" to create two additional installs for testing various tools releases. The primary was installed using platform pack and was already upgraded to 8.97 using SM.

I wanted to take advantage of the multiple instance support in EnterpriseOne that allows multiple instances to run under the same OS user. Typical multi-foundation setups operate each enterprise server install as a different OS user. This works fine, but with the advent of server manager and it's management agents it would require installing and operating a separate managed home for each OS user. This setup works fine, but operating under the same OS user requires only a single agent install which seems like a good idea.

The first think I did was to stop the existing enterprise server using SM. In a shell I performed a recursive copy of the base directory (/u02/jdedwards/e812 in this case) to /u02/jdedwards/port6015 and /u02/jdedwards/port6016). Back in server manager I created two new instances by registering these new installations with unique instance names.

Using SM I went through and modified all the configuration items that were install specific. I went through each configuration category and looked for those settings that referenced the old install path and updated them accordingly. Off the top of my head I had to change the following settings
  • Install Path
  • Build Area
  • XTS Repository
  • JDE and JDEDEBUG log configurations
  • jdelog.properties log file configurations
  • Compiler and associated locations
There are also some settings that are not install location specific that I knew add to be modified. These include
  • The JDENET listen and connect ports (set to 6015 and 6016)
  • The IPC Start Key (set to unique, non-overlapping values)
Since these new servers were a copy of an existing install I had to modify some of the startup scripts to use the new install paths. These scripts were located in the SharedScripts folder found underneath the base install. This included changing the paths in
  • enterpriseone.sh
If you are on a release earlier than 8.11SP1 (and thus prior to the platform pack installer) the settings are instead contained in the .oneworld located in the user's home location.  Since this single file defines paths that would be different for each enterprise server instance it is better to use multiple OS users in this case.

Finally, since I'd be running these servers under the same OS user I had to modify the RunOneWorld.sh and EndOneWorld.sh scripts to set the variable "MULTIPLE_INSTANCE" to 1. This instructs the scripts to be aware that multiple foundations are running under the same OS user. I made this change for all servers, including the original server used as the source.

Back in SM I tried to start all my new servers. The new servers all failed to start. Looking at the SM logs and a little digging later I realized a problem in the logic in the RunOneWorld.sh script that determines if processes are already running. The script makes a series of calls to the ps application and filters based on OS user and the JDENET port. Since my new installs included the JDENET port in the path (6015 and 6016) it was incorrectly thinking the startup script itself was an existing E1 process and failed start the server. I changed the install locations so they didn't include the 6015 and 6016 values in the path and all worked.

Sidenotes
This is probably not a typical setup but once configured it works very well. There are a few notes I'd like to mention.

First, when performing my initial installation plan I had anticipated these additional ports and defined them initially. This is probably not normal, so one would have to modify their original plan to add the new ports. This creates many of the DB records required for the server such as the F965* tables and the package discovery tables (812 and later).

Secondly one must be aware of the changes made to RunOneworld.sh and EndOneWorld.sh to set the MULTIPLE_INSTANCE=1 line. Since these scripts are delivered with the tools release they will be overwritten with each new tools release I upgrade the servers to. I'll have to go back and modify those.

Although this isn't a true step by step guide to configuring multi-foundation I hope it helps the experienced CNCs with some guidance to configuring this sort of configuration.

Flow Demo at Oracle Open World

Chris Grillone - Mon, 2007-10-15 18:25
The famous Pen demo will be at OOW on Wednesday from 2:30-5:30 PM in Space 2 - Mascone West. Draft marketing collateral:This hands-on demonstration highlights Flow Manufacturing and Configurator in a Lean Manufacturing Enterprise. The demonstration begins with a customer, an audience member, ordering a configured-to-order pen using iStore. The sales order is then sent to Order Management and released to manufacturing where a Flow schedule is created. The Flow schedule is executed with two line operations using the HTML Flow Execution Workstation, demonstrating both simple attached documents and video for assembly instructions. Bar code scanning and RFID from Warehouse Management System are used in this complete manufacturing process demonstration. Although this demonstration is provided at an executive level, all manufacturing data is built to support the Flow schedule and will be available to allow the audience members to dive deeper into specific Flow Manufacturing features, such as line design and balance, mixed model map workbench and Kanban execution. Come marvel at our use of a forklift in the demo!

WF_DEFERRED Queue is having few million messages in "Ready" State

Madhu Thatamsetty - Mon, 2007-10-15 03:55
I would like to publish this blog post in the form of a set of questions and answers that will help you resolve a situation if "Ready" state messages in WF_DEFERRED queue are in few millions and Workflow Deferred Agent listener is down from years which caused the pile up of records in WF_DEFERRED Queue.Q1. How do I find out in first place WF_DEFERRED Queue is having millions of records inMadhu Sudhanhttp://www.blogger.com/profile/11947987602520523332noreply@blogger.com9

Payroll Reversal Pack K Rollup 2

RameshKumar Shanmugam - Sun, 2007-10-14 15:44
HRMS Family Pack RUP 2 was released on Jun 2007.
Due to current project schedule i didn't had much time to test the new functionality.
This week i had the chance to install the PF K RUP 2 to my test instance, the lot of functionality which i was expecting is here now...

The first thing which i would like to highlight is Payroll Reversal functionality.
If you want to reverse assignments that have been processed as part of a payroll run or QuickPay run, you can now reverse them as a group by assignment set instead of reversing by individual assignment only. Any assignment sets that you use for reversals can also use the normal assignment set features such as:
  • Include or exclude specific assignments
  • Make formula-based amendments

The original reversal by single assignment is still available, and you can continue to access it from the Assignment window.

Payroll Reversal Details http://ramesh-oraclehrms.blogspot.com/2007/09/payroll-reversal.html

Defining Assignment Set http://ramesh-oraclehrms.blogspot.com/2007/08/assignment-set.html

Try it out!!!

Categories: APPS Blogs

Flushing a single cursor

Fairlie Rego - Fri, 2007-10-12 19:33
In a very intensive OLTP environment plan stability is of utmost importance as a single sub optimal query can bring the system down to its knees.

Using sql outlines and sql profiles is always not possible if you have an application with more
than a million lines of code and any one of the thousands of sql statements could have a plan flip.

In 11g there is a new procedure in the DBMS_SHARED_POOL package which helps you flush out a single cursor.
There is a typo in the syntax portion of the documentation in
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_shpool.htm#sthref8578
which I will try to get corrected in the next week.

PURGE Procedure
This procedure purges the named object or specified heap(s) of the object.

Syntax

DBMS_SHARED_POOL.KEEP (
name VARCHAR2,
flag CHAR DEFAULT 'P',
heaps NUMBER DEFAULT 1)
Parameters

Table 118-4 KEEP Procedure Parameters

Parameter Description
name Name of the object to keep.
The value for this identifier is the concatenation of the address and hash_value columns from the v$sqlarea view. This is displayed by the SIZES procedure.

Currently, TABLE and VIEW objects may not be kept.

flag (Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.

Set to 'T' or 't' to specify that the input is the name of a type.

Set to 'R' or 'r' to specify that the input is the name of a trigger.

Set to 'Q' or 'q' to specify that the input is the name of a sequence.

In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

heaps Heaps to be purged. For example, if heap 0 and heap 6 are to be purged:
1<<0 | 1<<6 => hex 0x41 => decimal 65, so specify heaps =>65.Default is 1, that is, heap 0 which means the whole object would be purged

This feature was introduced via the fix in bug 5614566 and I actually know a customer who has this applied on top of 10.2.0.3.

E.g

SQL> exec dbms_shared_pool.purge('00000003DE576D40,353632309','C',65); ==> purge heap 0 and heap 6

PL/SQL procedure successfully completed.

This would actually not work against a cursor which is currently executing.(pinned)

Session 1:
=========
Do a massive Merge Join Cartesian
select * from dba_objects a, dba_objects b, dba_objects c;

Session 2:
=========
Identify the sql address and hash value and try to purge the cursor..

exec dbms_shared_pool.purge('00000003DE825198,3691928467','C',65); ==> This hangs

and this session is waiting on "cursor: pin X" requesting an exclusive mutex pin for the cursor object whilst it has already been pinned by session 1

Session 3
==========
select event,p1,p2 from v$session where username='SYS' and type='USER';
EVENT P1 P2
----------------------------------------- ---------- ----------
cursor: pin X 3691928467 1


The p1 value here is the Hash value of the cursor we are trying to flush.

From the short stack of the process which is executing the purge API a function called kxsPurgeCursor is called which would try to take a mutex (since _kks_use_mutex_pin is TRUE by default)
The purge completes only after you cancel the sql in session 1 and exit from the same
or kill the session executing the sql.

11g on Linux x86_64 is out!

Renaps' Blog - Fri, 2007-10-12 19:15

This morning, I was surprised to learn that the Linux x86_64 announcement release was not in this Oracle New Downloads RSS Feed. I got aware of the news reading Niall Litchfield Blog. I guess news travels faster via the Oracle blog community! , also, I thought Oracle would have normally released 11g for other o/s prior to releasing the Linux x86_64 version. In any case, I will download this release now.

It is now available here: Oracle 11g on Linux x86_64.

I have been waiting for this release for a while since two of my databases are stuck to 32 bits as they need to send and receive data using “hsodbc” (only available for 32bit) for an ERP software.

Heterogeneous systems are quite common, and I am pretty sure that many of the enhancement requests for Generic Connectivity were focused on making them available for 64-bit platforms.
The
Metalink Note:361676.1, Is Generic Connectivity Available On The LINUX Or Windows 64-bit Platforms? , explains that Generic Connectivity will be available on Linux Red Hat 64-bit and SUSE 64-bit with 11gR1.

Another reason that makes me appreciate this new release is that I ran through several issues while trying to run Oracle with larger SGA’s on 32 bits platforms. I used RAMFS and HugePages to enable the use of 8 to 10GB of SGAs. The following links helped me in this process:

Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases

Metalink Note: 317141.1, How to Configure RHEL 4 32-bit for Very Large Memory with ramfs and HugePages

I am hoping that “hsodbc” will work fine on 11g x86_64; I’ll keep you posted on my findings…


Categories: DBA Blogs

ORA-06502: PL/SQL: numeric or value error: host bind array too small

Mike Moore - Fri, 2007-10-12 16:12
ORA-06502: PL/SQL: numeric or value error: host bind array too small

With 10gR2 dbms_output has been improved to handle more that 255 characters. I ran into this problem when I forgot to consider that it is the CLIENT ( not necessiarily the db server) that must be 10gR2. The PL/SQL procedure would run fine for me because I happened to have the 10gR2 client installed on my PC. Our QA had an older version of the client and kept running into this ora-06502.

So, it's probably not a good idea to drop those string chunking procedures until you are sure that everybody is off of the older clients.

Friends, forgive me

Fake Larry Ellison - Fri, 2007-10-12 14:51
You woke up this morning and saw the news about our bid for BEA Systems and you thought, what the fuck? Larry lied to us a couple weeks ago when he said he didn't care about those Beaturds. He tricked us, didn't he? Well, yes. And friends, I know what you're thinking right now. Listen, I'm sorry about misleading you. Just believe me when I say that I really want to tell you what we're doing with BEA Systems. But all I can say right now is that I can't say much. It's just one of those things. So have a good weekend and try not to toilet paper my house or egg my Karmann Ghia, okay?

As if three Google jets weren't enough...

Fake Larry Ellison - Thu, 2007-10-11 23:17

Larry and Sergey and Eric just had to get another one. See here. I called Eric this afternoon to verify. He said, yes, the rumors are true. Apparently Larry wanted all three of them to have their own planes so they could each fly one and have dogfights over LA, and he wanted this new 757 so he could fly it from home with a special version of Microsoft Flight Simulator X. The best part about the 757 is that it runs on a special hybrid blend of dollar bills and ethanol. Larry's convinced that inflation is going to get so high in a couple months that the dollar won't be worth anything, so he wants to burn green backs to reduce the number of bills in circulation which will in turn help decrease inflation. Eric also said that passengers can actually fuel the aircraft while it's flying by sticking bills into the little plastic box right next to the stripper pole.

In the face of adversity, Red Hat tries to act natural

Fake Larry Ellison - Thu, 2007-10-11 13:58

It's no secret that Shit for Brains thinks he owns Linux, or at least part of it. And it's also no secret that Red Hat thinks SFB is full of shit. But every couple months Stevie B. hints at an intellectual property lawsuit just to keep everybody on their toes. This time around, however, Red Hat says it isn't going to push back. They say they're just going to smile and nod and act like everything is normal. My lawyers are following the whole thing very closely. They say that Novell caving in was a good sign and that soon enough we'll be able to sue Microsoft for violating our patents. No joke. And we're talking large sums of money here, people. When you consider the fact that every computer running Windows has a little relational database inside, you'll see the scale of what I'm talking about. So we're almost ready to start entering into license negotiations with Microsoft. We think they should pay us about $10,000 per Windows installation. That's fair, right?

Jimbo's coming to town

Fake Larry Ellison - Thu, 2007-10-11 09:46

Actually he's already been here for a while. But now he's moving the whole Wikimedia enchilada out here to the Valley. See here. I guess this means Wikipedia is out of the woods now, eh? Because last time I checked Jimbo was thinking about selling ads on encyclopedia pages. I think the real problem is that the dude can't get away from Wikipedia. He wants to be the next Bill Gates or whatever, but he just can't shake this rinky-dink non-profit he started. I hear that people always come up to the guy and call him the Wikipedia man. That and his rag-tag band of college student contributers always want him to speak at their schools. He must just hate that. And who can blame him? When you want to live on a sailboat with beautiful women hanging off each arm, you need money. Serious money. Money you're not going to be able to make with a non-profit organization. So I'm thinking about calling the guy. Maybe I'll invite him golfing and sailing and then offer him a job at Oracle. Either way, I'd like another friend. The guy's obviously smart, even if he does insist on using MySQL.

ZDNet's Michael Krigsman calls me out

Fake Larry Ellison - Wed, 2007-10-10 14:27
Dude left a comment on one of my posts earlier today. Not sure, but I think he wanted to gently remind me of "Oracle's little payroll problem over at Arizona State University." Only it wasn't our problem, Mike. It was ASU's. Seriously. Because here's the thing. Sure we sell people software and support, but we aren't going to walk customers through every single itty-bitty little step. If they go and drop all of their tables by accident, that's not our fault. It's called operator error, Mike. Read our EULA. It doesn't cover mistakes made by blonde bimbos working as ASU interns who have freak-out moments whenever they break one of their nails. So you aren't going to get us on this one, bro. Nuh uh. Not going to happen. Talk to the hand, girlfriend.

This would make a great April Fools' Day joke

Fake Larry Ellison - Wed, 2007-10-10 14:02
See here. Tom Yager over at InfoWorld says that we should all use Solaris when we can't use Mac OS X. He says that Solaris has all of the bloat of Windows Vista and none of the headaches associated with ease of use, and that's what makes it a great choice for people who aren't quite Mac people but don't want to be assimilated by Microsoft. Here's the best quote: "Solaris and OS X are both Unix, and if that's not enough, know that PowerBook, MacBook, and MacBook Pro are practically de facto choices among Sun's engineers." So if you're not sold on Solaris, the fact that Sun's engineers use it will seal the deal for sure.

Yes, I know what you're thinking. It doesn't make any sense to me either. And honestly, I'm kind of wondering whether Steve paid to have the article planted. He loves to fuck with the enterprise people.

Who needs an education anyway?

Fake Larry Ellison - Wed, 2007-10-10 11:48

So by now you've probably read all about how SAP's shitty software basically shut down the entire Los Angeles school district. If not, see here. Apparently the administrators aren't yet sure whether the problem started with the MySQL database or the SAP installation. Anyway, now there's anarchy on the streets. Seriously. Kids are out of the classrooms and in the skate parks. They're listening to live punk bands instead of those dreary teachers. And thousands of these kids are talking to our Oracle recruiters. That's right people. As I write this, our dedicated employees are hitting the streets to find the little turd-droppers. We're calling it the A.S.S. (Application Support Services) initiative. Goes like this. We go out and find young tech-savvy individuals who want to make a difference. If they know about Linux, we give them our A.S.S. kit. It's a box full of things like condoms, Playboy magazines, O'Reilly programming books, a couple of marijuana joints to help them concentrate on the O'Reilly programming books, and a bunch of other information about open source projects. Because here's the thing. Those kids don't want to go to school their entire lives. They want to change the world and help people and do something meaningful. And what better way to change the world than to work on an open source project like Tomcat? Or maybe Fedora. Anything we can use and/or support.

Gartner Day 2

Peter Khos - Wed, 2007-10-10 07:27
Sorry about the lack of updates but been busy with the Gartner Symposium. Last evening (Tuesday), it was the "Attendee Appreciation Event" which was held at Disney's Animal Kingdom where the park was partially opened for the attendees to partake. Obviously the event was "pitiful" compared to Oracle OpenWorld where last year, Oracle had Elton John as the headliner and Billy Joel for this year's Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

I love Linus

Fake Larry Ellison - Tue, 2007-10-09 22:50

But sometimes the dude can be as stubborn as a pack mule walking through Siberia in the dead of winter. Kind of like last week when he said, "You security people are insane," and then reiterated that he will add Smack to the Linux kernel. And that's cool. I mean, Linux is his dog and pony show. The problem is that Smack will probably bring a bunch of security vulnerabilities along with it.

People, this is exactly why I run OpenBSD on all of my servers at home. Those suckers are locked down. I keep them off the network, and I also have all the totally crazy shit to protect them. Fingerprint identification. Retinal scanners. Man traps. I'm telling you: The NSA has nothing on Larry's servers. Some people wonder why I go to the insane lengths I've gone to. Well, I'll tell you why. The truth is that you can never be too careful with your digital black book. Women's phone numbers are very, very important.

What's that sound coming from Facebook?

Fake Larry Ellison - Tue, 2007-10-09 21:57

BusinessWeek thinks it's the cha-ching sound coming from the cash register as developers sell their Facebook applications and head for the door. But in reality it's just the sound of thousands of ambitious geeks trying to suck from Mark Zuckerberg's teat. Seriously. Sex-deprived brainiacs beware. This Facebook App stuff has pyramid scheme written all over it. Better to become an Oracle DBA and do serious work that can change the world and make you a bundle of money.

But let me tell you about this one app called Make a Baby. I really think it has potential. Check it out. You hop on Facebook and look at people's pictures until you get a hard-on or the female equivalent. Then you hook up and make a little virtual baby that looks like the two of you. Easy, right? The best part is that you don't even need to virtually procreate with a member of the opposite sex. Man oh man. Just think of the possible combos here. Bill Gates and Mark Zuckerberg. Steve Ballmer and Jonathan Schwartz. (Sorry. Bad image. Talk about some fugly offspring.) Peter Burrows and Dan Lyons. Marten Mickos and Zack Urlocker. Or maybe even Jason Maynard and Rick Sherlund. Yes. Friends, I can see the positive energy flowing as I write this. More on these couples as they develop.

Pages

Subscribe to Oracle FAQ aggregator