Home » RDBMS Server » Server Administration » ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect (Oracle Database 12.1.0.2)
ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667974] Mon, 29 January 2018 10:17 Go to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
Hi all,
I get the following error:

Error in cleaning up 2PC subordinates; error stack:
ORA-01012: not logged on
ORA-02063: preceding line from CAD_MGBR
ORA-02396: exceeded max Idle Time, please connect again

Can someone help?

Thanks

[Updated on: Mon, 29 January 2018 10:49]

Report message to a moderator

Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667976 is a reply to message #667974] Mon, 29 January 2018 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-02396: exceeded maximum idle time, please connect again
  *Cause:  as stated
  *Action:
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667977 is a reply to message #667976] Mon, 29 January 2018 12:25 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
I have a database link that connect a instance on another network.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667978 is a reply to message #667977] Mon, 29 January 2018 12:28 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
idle_time - UNLIMITED on profile.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667979 is a reply to message #667978] Mon, 29 January 2018 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not say, SHOW us.

Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667980 is a reply to message #667974] Mon, 29 January 2018 12:44 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I don't have much experience with distributed transactions, but this is a forum - so I won't let ignorance stop me from commenting Smile
You may need to sort out an in-doubt transaction,
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-distributed-transactions.html#GUID-79B20A57-048B-4C66- 9803-0C4FE81A4A84
I've had to do that a few times.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667981 is a reply to message #667980] Mon, 29 January 2018 13:28 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
select a.username,b.profile,b.RESOURCE_NAME,b.LIMIT from dba_users a, dba_profiles b where
b.resource_name='IDLE_TIME' and a.profile=b.profile and a.username='BIOMETRIA_DW';

Restult:
USERNAME PROFILE RESOURCE_NAME LIMIT
BIOMETRIA_DW PF_ESQUEMA IDLE_TIME UNLIMITED
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667982 is a reply to message #667981] Mon, 29 January 2018 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

On local or remote site?

Please read How to use [code] tags and make your code easier to read.

Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667984 is a reply to message #667982] Mon, 29 January 2018 14:09 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
select a.username,b.profile,b.RESOURCE_NAME,b.LIMIT from dba_users a, dba_profiles b where 
b.resource_name='IDLE_TIME' and a.profile=b.profile and a.username='BIOMETRIA_DW';

Local user.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667992 is a reply to message #667984] Tue, 30 January 2018 02:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
And at the remote site?
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #667996 is a reply to message #667992] Tue, 30 January 2018 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the DB link connection times out then it's the idle_time setting for the user you've connected to on the remote DB that's important.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668003 is a reply to message #667996] Tue, 30 January 2018 09:37 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
CREATE PUBLIC DATABASE LINK "CAD_MGBR"
   CONNECT TO "MGBR" IDENTIFIED BY VALUES ':1'
   USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remotedb)(PORT=1521)))(CONNECT_DATA=(SID=cad)))';

Database link configuration is like that.

Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668005 is a reply to message #668003] Tue, 30 January 2018 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SID= should NOT be used with V12 & replaced with SERVICE_NAME=
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668006 is a reply to message #667974] Tue, 30 January 2018 10:10 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
marcossantos wrote on Mon, 29 January 2018 10:17
Hi all,
I get the following error:

Error in cleaning up 2PC subordinates; error stack:
ORA-01012: not logged on
ORA-02063: preceding line from CAD_MGBR
ORA-02396: exceeded max Idle Time, please connect again

Can someone help?

Thanks
A bunch of error messages with no context is no better than simply copying text out of an error message manual and asking "what did I do wrong"? You should copy and paste the entire session - the entire command that produced the error and the entire output of that command. And enclose it in 'code' tags for better 'readability'.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668007 is a reply to message #668006] Tue, 30 January 2018 10:24 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
I ask at network administrator about tcp timeout on switch or firewall. I waiting the answer.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668009 is a reply to message #668007] Tue, 30 January 2018 10:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What you need to do is check what idle_time is set to for the user MGBR in the remote DB.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668010 is a reply to message #668009] Tue, 30 January 2018 11:25 Go to previous messageGo to next message
marcossantos
Messages: 123
Registered: June 2008
Senior Member
SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS where username like '%MGBR%'

USERNAME PROFILE        ACCOUNT_STATUS
MGBR	 UFBR_PROFILE	OPEN


 select a.username,b.profile,b.RESOURCE_NAME,b.LIMIT from dba_users a, dba_profiles b where 
 b.resource_name='IDLE_TIME' and a.profile=b.profile and a.username='MGBR';

USERNAME PROFILE        RESOURCE_NAME      LIMIT
MGBR	 UFBR_PROFILE	IDLE_TIME	   5
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668011 is a reply to message #668010] Tue, 30 January 2018 11:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you have diagnosed the problem. Better change that profile.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #668019 is a reply to message #668011] Wed, 31 January 2018 03:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Or if you can't change the profile set up a new user on the remote DB with grants and synonyms on all the objects you need and give it a different profile.
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #674427 is a reply to message #668019] Sun, 27 January 2019 03:51 Go to previous messageGo to next message
Vasim
Messages: 2
Registered: January 2019
Junior Member
Hi we are getting same error..

Have you got
solution??
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #674428 is a reply to message #668019] Sun, 27 January 2019 03:52 Go to previous messageGo to next message
Vasim
Messages: 2
Registered: January 2019
Junior Member
We are getting same issue.. What is final conclusion
Re: ORA-01012 ORA-02063 ORA-02396 Database Link Disconnect [message #674429 is a reply to message #674428] Sun, 27 January 2019 04:15 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The final conclusion is to first check the profiles on both databases.

Previous Topic: Find out which blocks included in which extent/datafile
Next Topic: Sqlplus freezing
Goto Forum:
  


Current Time: Thu Mar 28 11:31:31 CDT 2024