Session in Remote database
Hi,
I have one question, but have limited time to get answer.
There are two DB, say dev and Prod.
DB link is created in Dev pointing to Prod say schema S1 (Dev) to S2(Prod)
there are queries from Dev-S1 session like this
insert into table_1 select .... from table_1@to_prod where ...
commit;
insert into table_2 select .... from table_2@to_prod where ...
commit;
.
.
.
insert into table_400 select .... from table_400@to_prod where ...
commit;
assum there are 400 inserts.
Here is the question.
1. How many remote connection will be there at any given point in time in Prod.
2. How to find Prod session info when above query is running from Dev.
Thanks
I have one question, but have limited time to get answer.
There are two DB, say dev and Prod.
DB link is created in Dev pointing to Prod say schema S1 (Dev) to S2(Prod)
there are queries from Dev-S1 session like this
insert into table_1 select .... from table_1@to_prod where ...
commit;
insert into table_2 select .... from table_2@to_prod where ...
commit;
.
.
.
insert into table_400 select .... from table_400@to_prod where ...
commit;
assum there are 400 inserts.
Here is the question.
1. How many remote connection will be there at any given point in time in Prod.
2. How to find Prod session info when above query is running from Dev.
Thanks
ASKER
Basically we are getting the following error in Prod.
ORA-00018: maximum number of sessions exceeded
Basically there is one application which is quering Prod and insert prod data in seperate database over network. This error is logged in log file of that application.
We have one big SQL file containing all INSERT INTO .... SELECT statement followed by commit;
and we are using sqlplus -s @filename
I was thinking if all statement is making different connection to Prod, I thing this is not true.
I was trying the following query
select A.* from gv$session A, gv$session@PROD B
where a.taddr = b.taddr
and b.terminal is null
--and a.taddr = '00000003CFB7FC80'
order by a.taddr, a.sid
We have 830 sessions configured per instance. Right now we have only two node for online.
select inst_id, count(*) from gv$session group by inst_id;
INST_ID COUNT(*)
---------- ----------
3 586
1 154
4 619
It seems that oracle can accept many new connection, but we are getting that error. I dont know why.
ORA-00018: maximum number of sessions exceeded
Basically there is one application which is quering Prod and insert prod data in seperate database over network. This error is logged in log file of that application.
We have one big SQL file containing all INSERT INTO .... SELECT statement followed by commit;
and we are using sqlplus -s @filename
I was thinking if all statement is making different connection to Prod, I thing this is not true.
I was trying the following query
select A.* from gv$session A, gv$session@PROD B
where a.taddr = b.taddr
and b.terminal is null
--and a.taddr = '00000003CFB7FC80'
order by a.taddr, a.sid
We have 830 sessions configured per instance. Right now we have only two node for online.
select inst_id, count(*) from gv$session group by inst_id;
INST_ID COUNT(*)
---------- ----------
3 586
1 154
4 619
It seems that oracle can accept many new connection, but we are getting that error. I dont know why.
>>I was thinking if all statement is making different connection to Prod, I thing this is not true.
I would tend to agree but am at home away from any test databases and can't 'prove' it right now. one sqlplus 'connect' command, one session (unless you run parallel... parallel might take up more than one 'process').
I would look at the application. What is it written in?
Check out the program and machine columns of gv$session and get counts with those from the prod server. That should give you the 'proof' of where the connections are coming from.
I can't remember if I've warned you about this in the past but: It's a BAD idea to link a production database to any test/dev/play databases!!!!!!!!!!!!
They really shouldn't even be on the same networks to where the servers can even see each other.
Is there a reason you aren't using exp/imp (or datapump versions)?
I would tend to agree but am at home away from any test databases and can't 'prove' it right now. one sqlplus 'connect' command, one session (unless you run parallel... parallel might take up more than one 'process').
I would look at the application. What is it written in?
Check out the program and machine columns of gv$session and get counts with those from the prod server. That should give you the 'proof' of where the connections are coming from.
I can't remember if I've warned you about this in the past but: It's a BAD idea to link a production database to any test/dev/play databases!!!!!!!!!!!!
They really shouldn't even be on the same networks to where the servers can even see each other.
Is there a reason you aren't using exp/imp (or datapump versions)?
ASKER
>> Is there a reason you aren't using exp/imp (or datapump versions)?
Yes, All defects in Prod are tested in One particular database, if required, data is corrected in Prod. Its seperate process.
>>It's a BAD idea to link a production database to any test/dev/play databases!!!!!!!!!!!!
As per current setup, we have limited number of servers, but we have nearly 50 databases instances.
Its a State project and as per management, developers can work in Local/Dev databases and these env are very small. for testing we have diff database, then we have UAT, Load and Testing, Prodfix etc databases.
Whenever PSR team finds the problem in application data, Developers are copying the case from prod.
This is were they are getting the error.
This dblinks connects to Query account and query account contains synonyms to actual table.
therefore developers are not able to modify anything.
Also there are many layers, they don't need any username/password for prod etc, they just submit the request and request saved in seperate database, there are dba tools in server that creates db link, copy required tables and dropped the link. everything is totally invisible to users. Its serverside activity.
As per my research, query over dblink changes sql_id and hash_value in remote side.
Program over dblink changes to "oracle@servername (TNS...)" in Prod. that means its remote connection
Now I want to find from where these connections are comming. The machine it will show have lots of other Prod/ProdFix/SIT/App databases.
I believe above is not the problem because using the following query, I am just getting 13 rows.
select program, machine from gv$session
where terminal is null
and username <> 'SYS'
and machine like 'ser%'
Yes, All defects in Prod are tested in One particular database, if required, data is corrected in Prod. Its seperate process.
>>It's a BAD idea to link a production database to any test/dev/play databases!!!!!!!!!!!!
As per current setup, we have limited number of servers, but we have nearly 50 databases instances.
Its a State project and as per management, developers can work in Local/Dev databases and these env are very small. for testing we have diff database, then we have UAT, Load and Testing, Prodfix etc databases.
Whenever PSR team finds the problem in application data, Developers are copying the case from prod.
This is were they are getting the error.
This dblinks connects to Query account and query account contains synonyms to actual table.
therefore developers are not able to modify anything.
Also there are many layers, they don't need any username/password for prod etc, they just submit the request and request saved in seperate database, there are dba tools in server that creates db link, copy required tables and dropped the link. everything is totally invisible to users. Its serverside activity.
As per my research, query over dblink changes sql_id and hash_value in remote side.
Program over dblink changes to "oracle@servername (TNS...)" in Prod. that means its remote connection
Now I want to find from where these connections are comming. The machine it will show have lots of other Prod/ProdFix/SIT/App databases.
I believe above is not the problem because using the following query, I am just getting 13 rows.
select program, machine from gv$session
where terminal is null
and username <> 'SYS'
and machine like 'ser%'
>>query over dblink changes sql_id and hash_value in remote side.
probably. I was wanting you to only capture the prod side connections from v$session when the error occurs. This will show what is using them up.
>>where terminal is null
Why are you going after null terminal entries?
>>We have 830 sessions configured per instance
Is prod a RAC cluster? Again, I'm at home and can't confirm this but I believe the 'sessions' init parameter is derived. The main one in this equation is 'processes'. I might have it backwards...
probably. I was wanting you to only capture the prod side connections from v$session when the error occurs. This will show what is using them up.
>>where terminal is null
Why are you going after null terminal entries?
>>We have 830 sessions configured per instance
Is prod a RAC cluster? Again, I'm at home and can't confirm this but I believe the 'sessions' init parameter is derived. The main one in this equation is 'processes'. I might have it backwards...
ASKER
We have checked the count over instance id in gv$session and count is always less than 700 per instance
I think for remote entry, terminal should be null.
Yes our prod is 4 node cluster. We brought down node 2, because it started creating problem. We have open the ticket with oracle for this.
We have increased the process to 750 few days back.
Right now Batch team and PSR team are getting this error, I havn't heard from any online user. May be they are getting the error and they just refreshes the screen.
I think for remote entry, terminal should be null.
Yes our prod is 4 node cluster. We brought down node 2, because it started creating problem. We have open the ticket with oracle for this.
We have increased the process to 750 few days back.
Right now Batch team and PSR team are getting this error, I havn't heard from any online user. May be they are getting the error and they just refreshes the screen.
I just did an insert across a database link on my 11g Windows servers and the terminal/machine columns both showed the remote machine and the program column showed ORACLE.EXE.
Don't have a RAC config so I can't test that.
Can you create a unique username in both systems where you can experiment with? This would allow you to do some simple tests across the link and add username='' to your v$session queries to make sure you are only seeing your connections.
Once you get a handle on what is being placed where in v$session just remove the username= and you should be able to track the connections.
Don't have a RAC config so I can't test that.
Can you create a unique username in both systems where you can experiment with? This would allow you to do some simple tests across the link and add username='' to your v$session queries to make sure you are only seeing your connections.
Once you get a handle on what is being placed where in v$session just remove the username= and you should be able to track the connections.
ASKER
Yes it will show machine name.
But the problem is:-,There are lots of other databases running in those machine.
In my test scenario, I know what is source and target database.
Is oracle storing information about the process id/session of query running at remote database fired from source DB or vice versa?
Yesterday night we restarted all nodes and till now, we haven't seen max session exceeded issue again, which was my primary concern.
But the problem is:-,There are lots of other databases running in those machine.
In my test scenario, I know what is source and target database.
Is oracle storing information about the process id/session of query running at remote database fired from source DB or vice versa?
Yesterday night we restarted all nodes and till now, we haven't seen max session exceeded issue again, which was my primary concern.
ASKER
I opened TAR with oracle to find if there is any view from which we can capture the remote session info of current transaction if is using DB link.
There is a parameter OPEN_LINKS that controls the number of open database links allowed per session. The default is 4. Assuming you are running your 400 insert statements in one session, then there should never be more than 4 open connections on the remote database associated with the inserts.
It sounds to me more like you have an issue with one of the applications not closing connections properly. I don't think the database link is the source of the errors.
It sounds to me more like you have an issue with one of the applications not closing connections properly. I don't think the database link is the source of the errors.
ASKER
Actually I am interested in my 2nd question. I am working with Oracle to find the answer, but the solution they are provided is not perfect.
It seems that the question is very difficult and may be only Oracle can answer this.
I am going to close this question.
Thank you very much.
It seems that the question is very difficult and may be only Oracle can answer this.
I am going to close this question.
Thank you very much.
I would probably go ahead and leave it open until you can post a solution then have it accepted as the answer.
ASKER
Sure,
Thanks
Thanks
As I recall there was no way to do it directly. I used to do it with 2 sessions in the 2 different databases. I don't have a setup to check it, but there was a way to tie the 2 together. It may not have been perfect though. The way I was doing it certainly couldn't be done in one database though.
ASKER
Since I have open Oracle SR and I didn't come up with any solution from there side either. I am working on it to get best closest answer. I would request you not to close this question please.
Thanks
Thanks
ASKER
I got the reply from Oracle SR that Target database does't know if the generated session is from remote o local connection.
My question is then how target database is sending result back to correct session.
Do you have any idea?
My question is then how target database is sending result back to correct session.
Do you have any idea?
I have no idea how it does what it does at that level. That's probably another nuts and bolts question that only Oracle can answer.
I just wanted to post so you didn't think I've abandoned the question.
I just wanted to post so you didn't think I've abandoned the question.
ASKER
Since there is no way to find the session from where it was invoked from remote/source DB when using DB link.
Therefore Oracle Support engineer has put this request for enhancement for future release.
Down the line.
DB link uses separate OS and Oracle process, which is not saved in GV$ views for DBA verification.
@Slight / angelIII
I am not sure how to close this thread, because there is no direct answer.
@johnsone
I tried indirect methods but its not reliable, therefore I dropped the idea.
Anyway, Thanks guys.
Therefore Oracle Support engineer has put this request for enhancement for future release.
Down the line.
DB link uses separate OS and Oracle process, which is not saved in GV$ views for DBA verification.
@Slight / angelIII
I am not sure how to close this thread, because there is no direct answer.
@johnsone
I tried indirect methods but its not reliable, therefore I dropped the idea.
Anyway, Thanks guys.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Uh, thanks for the points but... That really isn't the correct solution.
You should have accepted your own comment. Was that a mistake?
You should have accepted your own comment. Was that a mistake?
ASKER
No, that was not the mistake.
I selected your answer for right direction.
I selected your answer for right direction.
Should be 1 since this is a single transaction.
>>2. How to find Prod session info when above query is running from Dev.
It there's not the one process, look for terminal and machine in v$session. If there's more, then look at the sql_address and use look at the sql_fulltext column v$sqlarea using the address column.