Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How does pmon come to know if a user session has aborted abnormally.

user8896122Mar 21 2010 — edited Mar 22 2010
Hi All


How does pmon comes to know if a user session has aborted abnormally. Is it that pmon will keep on polling the user process regularly?

or the underlying TCP stuff informs pmon that user session has aborted and pmon needs to come into picture to tidy up the things.


Thanks in advance.

Comments

MsJ
PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally).

PMON also registers information about the instance and dispatcher processes with the network listener.
Hemant K Chitale
Is it that pmon will keep on polling the user process regularly ?
You must distinguish between the client process -- which is the user process, for example, SQLPLUS.EXE on a Windows client or sqlplus from another Linux/Unix host or even the database server itself -- and the server process which is attached to the SGA.

PMON monitors server processes , not client processes.
That is why we sometimes have the "runaway process" scenario where the client has abnormally disconnected but the server process is still "active". PMON and the server process do not "know" that the client has disappeared.

SQLNet based Dead Connection Detection may be used by some sites to handle such situations (not with 100% success always).

Hemant K Chitale
http://hemantoracledba.blogspot.com
user8896122
Thanks for reply Hemant sir

But my doubt is how does oracle comes to know if my system restarts while i was logged into sql plus.

Will some process detect it immediately or after some time?? if so what is the mechanism involved.


Thanks
sb92075
But my doubt is how does oracle comes to know if my system restarts while i was logged into sql plus.
Depends what you mean by "my system".

If you are logged into system using sqlplus to access "remote" DB, then you should realize if/when your system restarts.
If you are logged into system using sqlplus to access "remote" DB & if remote DB system restarts, sqlplus will throw error next time you issue command to DB.
user8896122
What i mean was i am using sql plus installed on my PC and is connected to remote server. Now i restart my PC(without closing sql session). Then how will the things go forward . How will pmon detect it?
mbobak
First, some terminology:
Background server processes: PMON, SMON, DBWR, LGWR, etc. These are the core instance processes. They must all be up and running to have an operating instance.

User server processes: These are dedicated or shared server processes that facilitate communication with client programs. If one of these dies, a user loses connection to the database, but the instance doesn't crash.

PMON checks for existence of the other background processes (DBWR, SMON, LGWR, etc) every 3 seconds. PMON checks for users server processes every 60 seconds. If it finds a dead background process, then it will crash the instance. If it finds a dead server process, it will go about cleaning in progress transaction, clearing locks and latches, etc.

That all happens on the database server side of the world.

Now, on the client side, say, SQL*Plus running on your local PC, the story is a bit different. SQL*Plus connects to a listener, listener spawns a dedicated server process (or hands off to a dispatcher in the shared server scenario) and the SQL*Plus process connects to the server process to request work to be done on behalf of the user. While the SQL*Plus program is idle, and not asking the server to do any work, the server process sits idle, waiting on a 'SQL*Net message from client' wait, which essentially means the server process is blocking on the network socket, waiting for the client program to make a request. If, in the meanwhile, the SQL*Plus client program goes away, there's no way for the server process to know that, nor is there any way for PMON to know that. So, yes, it's possible that the server process will be "orphaned". It will be sitting there, waiting for input from a client program that it has no idea has gone away. Now, in general, this should not be a big problem. If however, you work in an environment where this type of thing can happen frequently, you could consider enabling dead connection detection (DCD). However, DCD can put a load on the database server, so, should not normally be enabled unless it's really needed.

Hope that helps clarify things a bit,

-Mark
sb92075
Now i restart my PC(without closing sql session).
Then how will the things go forward .
You'll need to launch sqlplus after restart & connect to a new session to the DB.
How will pmon detect it?
As previously stated, PMON does not care in the least about the now "orphan" client sessions.
Without manual intervention or DCD, the old session continues to remain within the DB; without any subsequent changes to the DB by this old session.
If previously DML had occurred prior to client restart, the changes are neither COMMITed nor ROLLBACKed.
Eventually the DML above will be ROLLBACKed; possibly as late as next instance restart.
Hemant K Chitale
As has already been pointed out, if your client abnormally disconnects, the server process does not know of it.

You should use Dead Connection Detection to handle such cases.
See SQLNET.EXPIRE_TIME : http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref474


Hemant K Chitale
http://hemantoracledba.blogspot.com
user8896122
Ok suppose i disconnected my sql session abnormally while some large updates were being performed.
Eventually the DML above will be ROLLBACKed; possibly as late as next instance restart
So according to you and the post after yours (by sb92075) the tables will be locked for unlimited time or till instance restarts is'nt it?
Aman....
PMON waits for an idle session timeout. If it comes up, it won't wait for the instance to get restarted but would start clearing the processes and its related resources. If you would crash the instance, its the SMON who would take care of the cleaning.

If you would end your session abruptly, PMON would start clearing it right away when the session timeout would hit it. You would not need wait for the server to get restarted.

HTH
Aman....
user8896122
Thanks Aman. Is there any parameter which needs to be set after which session timeout occurs.
Pavan Kumar
Hi,

Refer to Hemanth reply,he provided the parameter for your reference.

- Pavan Kumar N
ORACLE 9i/10g - OCP
http://www.oracleinternals.blogspot.com
Hemant K Chitale
"idle session timeout" is not the same as "dead connection detection".

The former is configured as a limit in the profile configured for the database account (you can have the same limit or different profiles with different limits for different accounts).

The latter is configured as SQLNet parameter which applies to all SQLNet (i.e. remote) connections to the database.


Hemant K Chitale
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 19 2010
Added on Mar 21 2010
13 comments
4,118 views