Home » RDBMS Server » Enterprise Manager » ORA-12505 (Oracle 10g on Windows 2003 Server)
ORA-12505 [message #408433] Tue, 16 June 2009 03:43 Go to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Hi,

I have Oracle 10g installed on a Windows 2003 Server Virtual Machine and I have the following problem that I hope someone can help me with.

All the Oracle services seem to start ok on OS startup, but I am unable to access my database, so I run up Enterprise Manager in my Browser. I get the following statuses:

Database Instance Down
Listener Up
Agent has the error message. ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)

So I click on the Startup button and I enter my OS username and password and the database starts and I'm able to access it.

I don't understand why the Database starts manually but not automatically on startup. Its not a major problem to do this as its just a test environment its just annoying.

If you require any more info let me know.

Regards
Martin
Re: ORA-12505 [message #408451 is a reply to message #408433] Tue, 16 June 2009 04:50 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

ORA-12505:
	TNS:listener does not currently know of SID given 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 a SID 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 SID parameter in the connect descriptor specifies an instance known by the listener.

- Check for an event in the listener.log file.
Re: ORA-12505 [message #408457 is a reply to message #408433] Tue, 16 June 2009 05:17 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Thank you, I had already seen that article and tried the suggestions but it still doesn't explain why I'm getting the problem.

After logging in to the OS I ran lsnrctl services LISTENER as suggested and I get the following results:

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-JUN-2009 11:06
:37

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

and in the listener.log file I get these lines:

16-JUN-2009 11:06:39 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1250)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
16-JUN-2009 11:07:07 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1255)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
16-JUN-2009 11:07:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1258)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
16-JUN-2009 11:07:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1259)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

After going into em and doing a startup I run lsnrctl services LISTENER again and I get:

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-JUN-2009 10:54
:57

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:99 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: TEAMCENTER07, pid: 172>
(ADDRESS=(PROTOCOL=tcp)(HOST=teamcenter07.tisuk.thales)(PORT=1279))
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:99 refused:0 state:ready
LOCAL SERVER
The command completed successfully

And the log file has these lines:

16-JUN-2009 11:09:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1543)) * establish * orcl * 0
16-JUN-2009 11:09:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1544)) * establish * orcl * 0
16-JUN-2009 11:09:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1545)) * establish * orcl * 0
16-JUN-2009 11:09:56 * service_update * orcl * 0
16-JUN-2009 11:10:01 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1546)) * establish * orcl * 0
16-JUN-2009 11:10:02 * service_update * orcl * 0
16-JUN-2009 11:10:11 * service_update * orcl * 0
16-JUN-2009 11:10:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1549)) * establish * orcl * 0
16-JUN-2009 11:10:13 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.92.128)(PORT=1551)) * establish * orcl * 0
16-JUN-2009 11:10:14 * service_update * orcl * 0
16-JUN-2009 11:10:32 * service_update * orcl * 0

I still don't understand why I have to startup manually and it doesn't start automatically.

Cheers
Martin
Re: ORA-12505 [message #408517 is a reply to message #408457] Tue, 16 June 2009 09:04 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Can you check Oracle Services starting auto or manual method in your windows machine (services.msc).

Thanks
Re: ORA-12505 [message #408520 is a reply to message #408517] Tue, 16 June 2009 09:24 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
All the Oracle services are set to Auto and seem to start normally, there seems to be no difference in the state of the services after doing a startup in EM.

Cheers
Martin
Re: ORA-12505 [message #408521 is a reply to message #408520] Tue, 16 June 2009 09:40 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Post your LISTENER.ora & TNSNAMES.ora

Thanks
Re: ORA-12505 [message #408527 is a reply to message #408521] Tue, 16 June 2009 10:10 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = teamcenter07)(PORT = 1521))
)
)



# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = teamcenter07)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
Re: ORA-12505 [message #408532 is a reply to message #408527] Tue, 16 June 2009 10:38 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


LISTENER.ORA

SID_LIST_LISTENER =
	(SID_LIST =
		(SID_DESC =
			(SID_NAME = PLSExtProc)
			(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
			(PROGRAM = extproc)
		)
	)

LISTENER =
	(DESCRIPTION_LIST =
		 (DESCRIPTION =
	 		 (ADDRESS = (PROTOCOL = TCP)(HOST = teamcenter07)(PORT = 1521))
			 (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
		 )
	)

TNSNAMES.ORA

ORCL =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST = teamcenter07)(PORT = 1521))
		)
		(CONNECT_DATA =
			(SERVICE_NAME = orcl)
		)
	)


Confirm your database service name.

Babu
Re: ORA-12505 [message #408535 is a reply to message #408433] Tue, 16 June 2009 10:44 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Do you mean the Oracle Windows service name? If so its OracleServiceORCL
Re: ORA-12505 [message #408537 is a reply to message #408535] Tue, 16 June 2009 10:46 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Connect Sys user.

show parameter service

Babu
Re: ORA-12505 [message #408538 is a reply to message #408537] Tue, 16 June 2009 10:48 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
- Check which instances are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SID parameter in the connect descriptor specifies an instance known by the listener.
Re: ORA-12505 [message #408540 is a reply to message #408537] Tue, 16 June 2009 10:50 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Sorry Babu, I'm not sure what you mean by that. What are you asking me to do?
Re: ORA-12505 [message #408541 is a reply to message #408540] Tue, 16 June 2009 11:05 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Try to check

1/ lsnrctl services <listener name>

2/ Check SERVICE_NAME parameter (Check from sys user)

Thanks

[Updated on: Tue, 16 June 2009 11:23]

Report message to a moderator

Re: ORA-12505 [message #408544 is a reply to message #408541] Tue, 16 June 2009 11:52 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
gentlebabu wrote on Tue, 16 June 2009 17:05

Try to check

1/ lsnrctl services <listener name>

2/ Check SERVICE_NAME parameter (Check from sys user)

Thanks


1/ Already ran this and posted the results earlier, I get different results before and after doing a manual startup.

2/ The SERVICE_NAME is orcl

Cheers
Re: ORA-12505 [message #408942 is a reply to message #408433] Thu, 18 June 2009 07:16 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Any other ideas anyone?
Re: ORA-12505 [message #409011 is a reply to message #408433] Thu, 18 June 2009 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 23184
Registered: January 2009
Senior Member
>Any other ideas anyone?
Post the content & location of the script that is supposed to start the Oracle database after the OS boots up.
Re: ORA-12505 [message #409016 is a reply to message #409011] Thu, 18 June 2009 14:24 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
BlackSwan wrote on Thu, 18 June 2009 19:57
>Any other ideas anyone?
Post the content & location of the script that is supposed to start the Oracle database after the OS boots up.


I don't know if there is a script, the problem is that the Oracle services appear to start normally but I am unable to connect to the database. When I open the em console in ie it says that the Database instance is down, the Listener is up and the Agent is down with the ORA-12505 error. I can then click the Startup button and I can then connect to the database. I shouldn't have to do this, on other installations I can just connect to the database on OS startup. I was hoping somebody would be able to tell me why I am getting this error.

Many thanks
Martin
Re: ORA-12505 [message #409018 is a reply to message #409016] Thu, 18 June 2009 14:39 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Do you have different ORACLE HOME ??

Babu
Re: ORA-12505 [message #409082 is a reply to message #409018] Fri, 19 June 2009 05:09 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
The ORACLE_HOME environment variable is set to C:\oracle\product\10.2.0\db_1 that is the only oracle home I have on the server.

Cheers
Martin
Re: ORA-12505 [message #409178 is a reply to message #409082] Fri, 19 June 2009 19:01 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Hello,

Let me complete your issue.

Please post below details.

lsnrctl services <listener name>
Re: ORA-12505 [message #409358 is a reply to message #409178] Mon, 22 June 2009 05:13 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Here are the results of running lsnrctl

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-JUN-2009 11:06
:37

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully

Regards
Re: ORA-12505 [message #409442 is a reply to message #409358] Mon, 22 June 2009 13:56 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


No; This is not we are expecting.

Listener is running but there is no listener releated services. Can you check this..

Thanks
Re: ORA-12505 [message #409449 is a reply to message #409442] Mon, 22 June 2009 14:26 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Sorry, I don't know what you want me to check can you give me a bit more info please?

Thanks
Re: ORA-12505 [message #409451 is a reply to message #409449] Mon, 22 June 2009 14:32 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member



Ok.

set your ORACLE_SID & ORALCE_HOME

lsnrctl status <<listener name>>

lsnrctl service <<listener name>>

Note: Please copy and paste here all the content from your session.

Thanks
Re: ORA-12505 [message #409586 is a reply to message #409451] Tue, 23 June 2009 03:47 Go to previous messageGo to next message
westen30
Messages: 13
Registered: June 2009
Junior Member
Here are the results of the lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-JUN-2009 09:44
:54

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date 23-JUN-2009 09:42:09
Uptime 0 days 0 hr. 2 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=teamcenter07.tisuk.thales)(PORT=1521
)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


And the results of lsnrctl service

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-JUN-2009 09:46
:12

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: TEAMCENTER07, pid: 1736>
(ADDRESS=(PROTOCOL=tcp)(HOST=teamcenter07.tisuk.thales)(PORT=1033))
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully
Re: ORA-12505 [message #409709 is a reply to message #409586] Tue, 23 June 2009 10:13 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Here are the results of the lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-JUN-2009 09:44
:54

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date 23-JUN-2009 09:42:09
Uptime 0 days 0 hr. 2 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=teamcenter07.tisuk.thales)(PORT=1521
)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


And the results of lsnrctl service

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-JUN-2009 09:46
:12

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=teamcenter07)(PORT=1521)
))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: TEAMCENTER07, pid: 1736>
(ADDRESS=(PROTOCOL=tcp)(HOST=teamcenter07.tisuk.thales)(PORT=1033))
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully 

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = teamcenter07)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)



As per your update; The above TNS Entry should work; Suppose if you have any problem; Try to re-create your listener.

Thanks

Previous Topic: dbconsole problem
Next Topic: Unable to determine local host from URL REPOSITORY_URL
Goto Forum:
  


Current Time: Thu Dec 25 11:52:03 CST 2014

Total time taken to generate the page: 0.11474 seconds