Home » RDBMS Server » Networking and Gateways » Temporary Error ORA-12505/ORA-12521
Temporary Error ORA-12505/ORA-12521 [message #532534] Wed, 23 November 2011 04:43 Go to next message
Gogetter
Messages: 39
Registered: December 2009
Location: Cologne Germany
Member
Dear members

I have a problem with connecting to databases.

We have two Linux server rac_04_01 and rac_04_02.
On this machines there are 5 Oracle 11.2.0. databases - each has 2 instances.
Every day in the morning I have to connect to any instance checking the instance is running.
Once a week some errors occur and i can't connect to the instance 1 (e.g. sid=db32001)

Example 
(Using Insider / sid)
Listener refused the connection with the following error: ORA-12505, 
TNS:listener does not currently know of SID given in connect descriptor 
The Connection descriptor used by the client was: (description=(
address=(host=ora-rac4.promata.local)(protocol=tcp)
(port=1966))(connect_data=(sid=db32001)))

(Using Sql Worksheet / tns)
Connect rzugreif/*****@lrdev1
ORA-12521: TNS: Listener kann in Connect-Deskriptor angegebene Instance aktuell nicht auflösen

Connect rzugreif/*****@lrdev2
Connect durchgeführt.

I asked my colleagues to connect and one other person has the same problem, two others could connect to both servers.
Next week same error but with other persons/clients who can connect/ can't connect.
While I can't connect to the database I check the status of database directly on the server (using putty). All instances, listener and services seemed to be okay. Status is "running".

The error stays for 2-3 hours, then everything goes fine - without any changes.

Thanks for reading and helping

[Updated on: Wed, 23 November 2011 08:35] by Moderator

Report message to a moderator

Re: Temporary Error ORA-12505/ORA-12521 [message #532543 is a reply to message #532534] Wed, 23 November 2011 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
ORA-12521: TNS:listener does not currently know of instance requested in connect descriptor
 *Cause:  The listener received a request to establish a connection to a
 database or other service. The connect descriptor received by the listener
 specified in addition to the service name an instance name for an instance
 (usually a database instance) that either has not yet dynamically registered
 with the listener or has not been statically configured for the listener.
 This may be a temporary condition such as after the listener has started,
 but before the database instance has registered with the listener.
 *Action:
  - Wait a moment and try to connect a second time.
  - Check which instances are currently known by the listener by executing:
    lsnrctl services <listener name>
  - Check that the INSTANCE_NAME parameter in the connect descriptor specifies
    an instance name known by the listener.
  - Check for an event in the listener.log file.

Regards
Michel

Report message to a moderator

Re: Temporary Error ORA-12505/ORA-12521 [message #532552 is a reply to message #532543] Wed, 23 November 2011 07:56 Go to previous messageGo to next message
Gogetter
Messages: 39
Registered: December 2009
Location: Cologne Germany
Member
Michel,

thanks for your quick replay.
At this time all connects are successful, and I found now error in the listener.log file. Will try to catch the next "error time slot" and search for error entries.
But as i wrote the error appears once a week for 2-3 hours.
There was no restart of instance and all instances are known.
Some users can connect but others can't connect at the same time. Out of the "error time slot" anyone can connect to any instance.

Rudi

Report message to a moderator

Re: Temporary Error ORA-12505/ORA-12521 [message #533266 is a reply to message #532543] Tue, 29 November 2011 02:18 Go to previous message
Gogetter
Messages: 39
Registered: December 2009
Location: Cologne Germany
Member
Hi readers,

today I wasn't able to connect to some instances - same as last week.
I could connect to all instances on the second node, but only to 1 instance on first node. I ask 3 other persons to try connects. One has the same error, the 2 others could connect everywhere.

I searched in log-file /opt/oracle/diag/tnslsnr/ora-db-rac4-01/listener/alert/log.xml for entries, but there are only the successful connects. Is there another file with errors?

One of my successful connect (the instance I could connect)
<msg time='2011-11-29T08:12:05.589+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='ora-db-rac4-01.promata.local'
 host_addr='192.168.0.117'>
 <txt>29-NOV-2011 08:12:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db42001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1591)) * establish * db42001 * 0
 </txt>
</msg>


Connect of a colleague @ db32001 
<msg time='2011-11-29T07:46:06.914+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='ora-db-rac4-01.promata.local'
 host_addr='192.168.0.117'>
 <txt>29-NOV-2011 07:46:06 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dev.promata.local)(INSTANCE_NAME=db32001)(CID=(PROGRAM=D:\Oracle\product\10.1.0\Cli
ent_1\bin\sqlplus.exe)(HOST=PROMATA-CL-909)(USER=thomas.manshausen))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.79)(PORT=1651)) * establish * dev.promata.local
* 0
 </txt>
</msg>


This are connects by my workstation using insider. Insider tries to connect to every instance once per minute. At 8:14 and earlier there is only db42001 successful, later from 8:17 to now, all instances are connectible
 <txt>29-NOV-2011 08:14:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db42001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1658)) * establish * db42001 * 0

 <txt>29-NOV-2011 08:15:29 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db42001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1685)) * establish * db42001 * 0

 <txt>29-NOV-2011 08:16:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db42001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1711)) * establish * db42001 * 0

 <txt>29-NOV-2011 08:17:15 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db22001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1725)) * establish * db22001 * 0

 <txt>29-NOV-2011 08:17:16 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db12001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1727)) * establish * db12001 * 0

 <txt>29-NOV-2011 08:17:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db52001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1729)) * establish * db52001 * 0

 <txt>29-NOV-2011 08:17:19 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(sid=db32001)) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.18.1.51)(PORT=1731))


Any idea what happens here?

Regards
Rudi

[Updated on: Tue, 29 November 2011 02:19]

Report message to a moderator

Previous Topic: Problem with ORACLE_HOME
Next Topic: connecting Oracle to SQL server
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Feb 12 09:01:41 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.