• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

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
0
Devinder Singh Virdi
Asked:
Devinder Singh Virdi
  • 11
  • 8
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>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
 
Devinder Singh VirdiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Devinder Singh VirdiAuthor Commented:
>> 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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Devinder Singh VirdiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Devinder Singh VirdiAuthor Commented:
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
 
Devinder Singh VirdiAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Devinder Singh VirdiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
I would probably go ahead and leave it open until you can post a solution then have it accepted as the answer.
0
 
Devinder Singh VirdiAuthor Commented:
Sure,

Thanks
0
 
johnsoneSenior Oracle DBACommented:
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
 
Devinder Singh VirdiAuthor Commented:
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
 
Devinder Singh VirdiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Devinder Singh VirdiAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
Devinder Singh VirdiAuthor Commented:
No, that was not the mistake.
I selected your answer for right direction.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now