Could your program be used for hang analysis, if the instance is on a state where you can't even log on? It would need to show columns such as EVENT and BLOCKING_SESSION.

Could your program be used for hang analysis, if the instance is on a state where you can't even log on? ...

select distinct to_number(addr,'XXXXXXXX')-to_number(lag(addr) over(order by addr),'XXXXXXXX') from x$ksuse;

John Watson wrote on Sun, 05 June 2016 16:28

Could your program be used for hang analysis, if the instance is on a state where you can't even log on? ...

No it can't as it requires to first connect as sysdba to retrieve some information like shmid and address of each session entry.

The first part can be workaround using sysresv instead of oradebug (and so without any connection).

Slightly unfair statement, so long as DSMA was setup prior to a database hang the tool could be used.

but then you might aswell go to /proc/[PMON_PID]/maps

If someone writes code that can do the same and doesn't need Enterprise Edition plus packs, it would be highly marketable. Let me know, people! My boss would be very interested in promoting it.

just how I see my clients working, they never allow a permanent program hacking the memory structures running

Quote:

Slightly unfair statement, so long as DSMA was setup prior to a database hang the tool could be used.

Not unfair, just how I see my clients working, they never allow a permanent program hacking the memory structures running (maybe the term hacking is not the one you want to hear but it actually is and this is not a term of abuse for me just a method to work as long as it is done transparently and with the agreement of the client). They allow them ONLY when a hang situation occurs (if they allow it at all). Carefully note that for the client if you can read the memory you can read their data with no possible audit.

Quote:

but then you might as well go to /proc/[PMON_PID]/maps

As far as I know, only if you are root. I mention sysresv because it is a standard Oracle program and nobody can blame you to use it (in the limits of your mission, of course).

Take no offense of what I say, it is just the thoughts of someone who has worked for a long time on this kind of subject (on both sides: the DBA and the security officer).

/proc permissions are 0555, anyone has read

Quote:

just how I see my clients working, they never allow a permanent program hacking the memory structures running

I think this is what Uncle Oracle relies upon. The OEM agent does just this to enable the emergency monitoring facility, but because it OEM, no-one objects. Any third party product would have to be validated by a reputable source. Larry, of course, would never validate it.

I see, thank you for explaining. I have never looked into the possibility, though I intend to do so. This is the motivation:

When Oracle introduced ASH, they cut the ground out from beneath all third party providers of administration tools because ASH can use a background process to go directly to the C data structures that make up the instance. This makes it possible to do per second sampling. Any third party product (such as Tanel Poder's Snapper, or the Quest product set) has to go through the SQL interface which can never be as efficient. Then in release 12 Oracle introduced Emergency Monitoring and Real-Time ADDM, which should let you query the instance even when you can't log on - if you have OEM running. That is also something no-one else can do.

If someone writes code that can do the same and doesn't need Enterprise Edition plus packs, it would be highly marketable. Let me know, people! My boss would be very interested in promoting it.

Neil, you'll have seen my answer to Michel. Can you compare your tool and where it might go against Tanel Poder's Snapper, http://blog.tanelpoder.com/files/scripts/snapper.sql ? Many people swear by Snapper (I don't use it myself) but I've always thought that it suffers from going through the SQL interface. Your product could be really good (and monetizable).