Solved

Session in Remote database

Posted on 2010-09-22
22
414 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:Devinder Singh Virdi
  • 11
  • 8
  • 2
22 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>How many remote connection will be there at any given  point in time in Prod.

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.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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)?
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
>> 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%'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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...
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I would probably go ahead and leave it open until you can post a solution then have it accepted as the answer.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
Sure,

Thanks
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
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.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
I have no problem with you accepting your last post as the answer.  The correct answer for the knowledgebase is: can't do it, Oracle working on enhancement.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Uh, thanks for the points but...  That really isn't the correct solution.

You should have accepted your own comment.  Was that a mistake?
0
 
LVL 15

Author Comment

by:Devinder Singh Virdi
Comment Utility
No, that was not the mistake.
I selected your answer for right direction.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now