Error ora 12505 sql developer что делать

Hello guys, i know this question has been posted many times in the past but my issue includes sql oracle developer only. i can use the database and all features through sql*plus even execute sql queries. but when it comes to sql developer it will always return this error. (error:ORA-12505.)

windows 8 -64 oracle 11g r2

—————————————————————————————————————————————

Welcome to LSNRCTL, type «help» for information.

LSNRCTL> status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

————————

Alias                     LISTENER1

Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 — Production

Start Date                11-AUG-2014 13:08:08

Uptime                    0 days 0 hr. 14 min. 55 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:apporacleproduct11.2.0dbhome_1networkadminlistener.ora

Listener Log File         c:apporaclediagtnslsnrNeedo-pclistener1alertlog.xml

Listening Endpoints Summary…

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Needo-pc)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))

Services Summary…

Service «CLRExtProc» has 1 instance(s).

  Instance «CLRExtProc», status UNKNOWN, has 3 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…

The command completed successfully

——————————————————————————————————————

LSNRCTL> services

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Services Summary…

Service «CLRExtProc» has 1 instance(s).

  Instance «CLRExtProc», status UNKNOWN, has 3 handler(s) for this service…

    Handler(s):

      «DEDICATED» established:0 refused:0

         LOCAL SERVER

      «ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

         CLRExtProc

         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeNTN_122C_C6D0FCF3.ORA))

      «ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

         CLRExtProc

         (ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeNTN_122C_C6D0FCF3.ORA))

Service «orcl» has 1 instance(s).

  Instance «orcl», status READY, has 1 handler(s) for this service…

    Handler(s):

      «DEDICATED» established:22 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:1022 state:ready

         DISPATCHER <machine: NEEDO-PC, pid: 3172>

         (ADDRESS=(PROTOCOL=tcp)(HOST=Needo-pc)(PORT=52654))

The command completed successfully

——————————————————————————————

thanks!

ora-12505 error while connecting via SQL Developer

I’m trying to connect remotely to Oracle 12c database with SQL Developer. In order to connect remotely from another computer, on the computer running Oracle I opened a port in the Windows 7 Firewall. That part worked, but now the listener isn’t letting me in due to this error ORA-12505. It is saying it doesn’t recognize the SID I provided when I try to connect with SQL Developer in the remote computer. I even tried setting service name to «editor», but still nothing.

Following are the setting from SQL Developer on the remote computer:

On the server side, this is listener.ora:

You’ll notice that the default listener is set to localhost on port 1521. As long as that stays like that, I can connect on the server with SQL Developer. So in order to connect remotely, I setup a second listener set for port 1531 and entered the IP address of the server. The firewall has also been setup to allow a connection through port 1531. As you can see, I did edit the tnsnames.ora file a bit to allow for a connection to the Editor database, but my edit didn’t seem to fix anything. I still can’t connect with SQL Developer on the client side. On the server, I tried using the Oracle Net Configuration Assistant to test the Editor entry and wound up with error message:

ORA-12514 Listener does not currently know of service requested in connect descriptor.

UPDATE Sept. 9 2014:

I was asked to run lsnrctl status from the command prompt. Following is the output from that command:

Источник

SQL-Developer error:ORA-12505. TNS:listner does not currently know of SID given in connect desc

Hello guys, i know this question has been posted many times in the past but my issue includes sql oracle developer only. i can use the database and all features through sql*plus even execute sql queries. but when it comes to sql developer it will always return this error. (error:ORA-12505.)

windows 8 -64 oracle 11g r2

Welcome to LSNRCTL, type «help» for information.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 — Production

Start Date 11-AUG-2014 13:08:08

Uptime 0 days 0 hr. 14 min. 55 sec

Trace Level off

Security ON: Local OS Authentication

Listener Parameter File C:apporacleproduct11.2.0dbhome_1networkadminlistener.ora

Listener Log File c:apporaclediagtnslsnrNeedo-pclistener1alertlog.xml

Listening Endpoints Summary.

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 3 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.

The command completed successfully

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 3 handler(s) for this service.

«DEDICATED» established:0 refused:0

«ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

«ORACLE SERVER» established:0 refused:0 current:0 max:25 state:ready

Service «orcl» has 1 instance(s).

Instance «orcl», status READY, has 1 handler(s) for this service.

«DEDICATED» established:22 refused:0 state:ready

Service «orclXDB» has 1 instance(s).

Instance «orcl», status READY, has 1 handler(s) for this service.

«D000» established:0 refused:0 current:0 max:1022 state:ready

The command completed successfully

Best Answer

Try it with sid ORCL instead of XE.

If that doesn’t help, then choose SERVICE_NAME orcl instead of SID in sql developer.

Your listener seems to have an entry for that.

You can test it also in SQl*Plus. But what you did so far in SQl*plus was a «server connect» without providing the database.

Since in your case the client and the server is on the same machine, this works with sql*plus.

If you try a client coonnect by specifying the database too, then you would do the same as the Oracle SQL Developer is trying.

For testing purposes something like

sqlplus user/[email protected]:port/ORCL

also in the Sqlnet.ora file there needs to be an etry for local connect method EZCONNECT. I’m not sure if this is set during the default net installation.

You can try if there is a TNS-Alias name (configured in tnsnames.ora)

sqlplus user/[email protected]

If that works with sql*plus then you still need to tell the sql developer where the tnsnames.ora file is located on your computer.

The setting is in menu: extras/tools/database/enhanced

The tnsnames.ora file is usually in some folder like this:

C:Oracleproduct11.2.0client_1networkadmin

Источник

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

I have installed Oracle 11g Express Edition Release 2 in my windows 7 64 bit OS and tried to execute JDBC program, then I got the following error:

31 Answers 31

I fixed this issue by correcting my jdbc string.

For example, the correct jdbc string should be.

But the jdbs string I was using is .

(Note: between 1521 and XE should be a / )

This bad jdbc string give me a ORA-12505 error too.

There are a few things that can cause this problem, but before you get started with JDBC, you need to be sure that you can connect to the database using SQL*Plus. If you’re not familiar with SQL*Plus, it’s a command-line tool for connecting to Oracle databases that has been a standard part of Oracle for a long time and it is included with Oracle XE.

When connecting to an Oracle database using JDBC, you don’t connect to the database directly. Instead, you connect to a TNS listener, which then connects you to the database. The error ORA-12505 means that the listener was up and you could connect to it, but it couldn’t connect you to the database because it doesn’t know that that database is up. There are two reasons for this:

• the database has not been started up,
• the database has not registered with the listener, e.g. because the database was started before the listener. (When the database starts, it registers itself with a listener if it is already running. If the listener isn’t running, the database doesn’t register itself, and if the listener starts, it doesn’t go looking for databases that might register with it.)

ORA-12505 means that the listener knows about that database, but the listener hasn’t received a notification from the database that the database is up. (If you were trying to connect to the wrong database, using the wrong SID, you would get an ORA-12154 error «TNS: could not resolve the connect identifier specified».)

What Oracle services are running in the Services snap-in? (Open this from Control Panel > Administrative Tools > Services, or just Start > Run > services.msc .) You need the services OracleServiceXE and OracleXETNSListener to be running.

If the services have both been started, can you connect to the database in SQL*Plus using any of the following at a command prompt? (I’m assuming you’re running these on the machine you’ve installed Oracle XE on.)

(Replace system-password with the password you set for the SYS and SYSTEM users during the Oracle XE installation.)

The first of these three connect via the TNS listener, but the second two connect directly to the database without going via the listener, and only work if you’re on the same machine as the database. If the first one fails but the other two succeed, then JDBC connections will also fail. If so, connect to the database using either of the other two and run ALTER SYSTEM REGISTER . Then exit from SQL*Plus and try the first form again.

If the third one fails but the second one works, add your user account to the ora_dba group. Do this in Control Panel > Computer Management > Local Users and Groups.

Once you can get connections of the form

to work, you ought to be able to connect to Oracle XE via JDBC. (Incidentally, you haven’t shown us the JDBC code you’re using to connect to the database, but I would suspect that it is quite probably correct; there would be various other errors if parts of the connection string were wrong.)

Источник

ORA-12505 error Only in SQL Developer — 11g Express

I found alot of similar problems but none that fit mine.

I installed Oracle 11g Express version. I can connect to my database in command line when i try in SQL developer i get the following error code:

Status : Failure -Test failed: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor.

the database im trying to connect to is xe.

here is the content of my tnsnames.ora file:

(ADDRESS = (PROTOCOL = TCP)(HOST = Jon-Laptop)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

here is the output of «tnsping xe» :

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Jon-Lapt

op)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))

here is the output of LSNRTCL SERVICES:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

Service «CLRExtProc» has 1 instance(s).

Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.

«DEDICATED» established:0 refused:0

Service «PLSExtProc» has 1 instance(s).

Instance «PLSExtProc», status UNKNOWN, has 1 handler(s) for this service.

«DEDICATED» established:0 refused:0

Service «XEXDB» has 1 instance(s).

Instance «xe», status READY, has 1 handler(s) for this service.

«D000» established:0 refused:0 current:0 max:1022 state:ready

Service «xe» has 1 instance(s).

Instance «xe», status READY, has 1 handler(s) for this service.

«DEDICATED» established:0 refused:0 state:ready

The command completed successfully

NOTE: i tried connecting to ORCL with SQL Developer and was able to with the SYSTEM account.

Please let me know if you need more information. Thanks in advance.

Best Answer

you typed in the string «OracleServiceXE» & the listener does NOT know about any Service with that exact name.

you should only enter «XE»

Answers

Please check whether you are using SID or service name while connecting from SQL Developer.

Try giving service name instead of SID in the SQL Developer.

Also use the IP address for the hostname

Are you able to connect to sqlplus using the given tns? If it works fine then , when you create a connection in sql developer, are you selecting TNS as connection type? If so then contents of your tnsnames.ora will be listed under Network alias and once you select from there , it should work.

I was using the SID to connect I tried using the service name and i get the same error.

here’s a screenshots of my connection attempt and a list of my oracle services.

I do not understand what you mean by «Are you able to connect to sqlplus using the given tns?»

the reason i think i’m connecting to XE in sqlplus is because i can use the user i created, but when i connect to ORCL the user doesnt exist.

i was using basic as my connection type. I switched to TNS connection type and XE is not in the dropdown list here is a screenshot:

you typed in the string «OracleServiceXE» & the listener does NOT know about any Service with that exact name.

you should only enter «XE»

In your original post, you mentioned that your tnsnames.ora contains XE and not ORCL. If XE and ORCL are pointing to two different databases and the user you created is only in XE then certainly you wont find this user in ORCL database. Also, I do not see any ORCL tns entry in your tnsnames.ora file so obviously you wont see that in the drop down list. create the desired user in database being pointed to by XE and you will find it.

If you are not getting XE in the dropdown then the tnsnames used by sql developer may be from a different location. You might have to check that as well.

I was able to connect to the service XE. I was confused about the names because there in no service named XE when i run services.msc.

@onkar: I believe that you are right SQL Developer must be using a different tnsnames.ora file. Can anyone add a path to this file for my own interrest.

thank you all for your time.

Yes — YOU can do that.

In sql dev go to ‘Tools -> Preferences -> Database -> Advanced’ and enter the ‘Tnsnames Directory’ for the tnsnames.ora file that you want sql developer to use.

> I was confused about the names because there in no service named XE when i run services.msc.

Both Oracle & Microsoft have the term SERVICE; but they are separate & distinct from each other.

Windows OS Service is not directly related to any Oracle service as reported by the listener.

Two OS services, 1 for the database & one for the listener, must be started & running before start the Oracle DB or listener.

Источник

Listener refused the connection with the following error:12505

I am facing the problem for last two days when i am trying to connect the oracle database with sql developer and Jdeveloper. Previously it was running fing.

The detal problem description is

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 — Production on 25-AUG-2012 20:25:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary.
Service «CLRExtProc» has 1 instance(s).
Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.
Handler(s):
«DEDICATED» established:0 refused:0
LOCAL SERVER
The command completed successfully

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 — Production on 25-AUG-2012 20:26:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 — Production
Start Date 25-AUG-2012 19:04:12
Uptime 0 days 1 hr. 22 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:appAnutoshproduct11.2.0dbhome_1networkadminlistener.ora
Listener Log File d:appanutoshdiagtnslsnrMTECHlisteneralertlog.xml
Listening Endpoints Summary.
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary.
Service «CLRExtProc» has 1 instance(s).
Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.
The command completed successfully

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 — Production on 25-AUG-2012 21:35:44

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
d:appAnutoshproduct11.2.0dbhome_1networkadminsqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER =

DED
ED) (SERVICE_NAME = orcl)))
OK (20 msec)

# tnsnames.ora Network Configuration File: d:appAnutoshproduct11.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

# listener.ora Network Configuration File: d:appAnutoshproduct11.2.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = d:appAnutoshproduct11.2.0dbhome_1)
(PROGRAM = extproc)
(ENVS = «EXTPROC_DLLS=ONLY:d:appAnutoshproduct11.2.0dbhome_1binoraclr11.dll»)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

# Change ‘ ‘ to point to the oracle base (the one you specify at
# install time)

db_name=’ORCL’
memory_target=1G
processes = 150
audit_file_dest=’ /admin/orcl/adump’
audit_trail =’db’
db_block_size=8192
db_domain=»
db_recovery_file_dest=’ /flash_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’ ‘
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
open_cursors=300
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1′
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible =’11.2.0′

Best Answer

no listener.ora file is required for LISTENER to operate.
move, rename, remove, or delete listener.ora file & then reboot the system.

every Oracle DB tries once every 60 seconds to register itself with LISTENER as shown below

Answers

Rajat wrote:
Hello experts,

I am facing the problem for last two days when i am trying to connect the oracle database with sql developer and Jdeveloper. Previously it was running fing.

The detal problem description is

An error was encountered performing the requested operation:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Vendor code 12505

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 — Production on 25-AUG-2012 20:25:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary.
Service «CLRExtProc» has 1 instance(s).
Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.
Handler(s):
«DEDICATED» established:0 refused:0
LOCAL SERVER
The command completed successfully

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 — Production on 25-AUG-2012 20:26:13

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 — Production
Start Date 25-AUG-2012 19:04:12
Uptime 0 days 1 hr. 22 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:appAnutoshproduct11.2.0dbhome_1networkadminlistener.ora
Listener Log File d:appanutoshdiagtnslsnrMTECHlisteneralertlog.xml
Listening Endpoints Summary.
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary.
Service «CLRExtProc» has 1 instance(s).
Instance «CLRExtProc», status UNKNOWN, has 1 handler(s) for this service.
The command completed successfully

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 — Production on 25-AUG-2012 21:35:44

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
d:appAnutoshproduct11.2.0dbhome_1networkadminsqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER =

DED
ED) (SERVICE_NAME = orcl)))
OK (20 msec)

# tnsnames.ora Network Configuration File: d:appAnutoshproduct11.2.0dbhome_1networkadmintnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

# listener.ora Network Configuration File: d:appAnutoshproduct11.2.0dbhome_1networkadminlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = d:appAnutoshproduct11.2.0dbhome_1)
(PROGRAM = extproc)
(ENVS = «EXTPROC_DLLS=ONLY:d:appAnutoshproduct11.2.0dbhome_1binoraclr11.dll»)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

# Change ‘ ‘ to point to the oracle base (the one you specify at
# install time)

db_name=’ORCL’
memory_target=1G
processes = 150
audit_file_dest=’ /admin/orcl/adump’
audit_trail =’db’
db_block_size=8192
db_domain=»
db_recovery_file_dest=’ /flash_recovery_area’
db_recovery_file_dest_size=2G
diagnostic_dest=’ ‘
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)’
open_cursors=300
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1′
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible =’11.2.0′

Nothing in the posted details gives any indication that any Oracle DB is up & online.
Has OS Database service been started?
Has database been started?

open Command Window & issue following OS commands EXACTLY as below

sqlplus
/ as sysdba
startup
SELECT * FROM V$VERSION;
EXIT

COPY the results from above then PASTE all back here

SQL*Plus: Release 11.2.0.1.0 Production on Sat Aug 25 23:26:52 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 — Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
———————————————————————————
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 — Production
PL/SQL Release 11.2.0.1.0 — Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 — Production
NLSRTL Version 11.2.0.1.0 — Production

Additionally
SQL> startup
ORA-01081: cannot start already-running ORACLE — shut it down first

I think it states that the db is up and running

Edited by: Rajat on Aug 25, 2012 11:31 PM

it appears that you are unwilling or unable to follow simple directions.
🙁

do what is required to force the failure again, again.

Источник

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor error occurs when the listener cannot find any matched SID with the provided SID, preventing it from connecting to the database.The database connection Sid you used is either unavailable or wrongly configured. Oracle Listener discovers a discrepancy between your Sid and the connection descriptor settings. The error ORA-12505, TNS:listener does not currently know of SID given in connect descriptor is thrown when oracle fails to create a connection due to invalid SID provided in the configuration.

Please wait a moment while the Oracle service starts up before attempting to connect again. Run the command lsnrctl services to see what services the listener already has. Verify that the service specified by the SID parameter in the connect descriptor of the net SID used is one that the listener is familiar with. If you used a connect identifier, ensure sure the SID is one that the listener recognises. To resolve the error ORA-12505, TNS:listener does not currently know of SID given in connect descriptor, look for any events in the listener.log file.

The Problem

You’ll be provided the host name, port, user name, password, and service name or Sid when you try to connect to a database. Oracle will wait for connections on the same host name and port when it starts up. The SID is the name of the presently running Oracle instance. The alias for the instance that lets you to connect to it is the service name. You will be unable to connect to the running Oracle database instance if the SID setting is incorrect.The error message ORA-12505, TNS:listener does not currently know of SID given in connect descriptor will be displayed.

Host name : localhost
port      : 1521
sid.      : orclcdb
username  : hr
password  : hr

Error

Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
  (CONNECTION_ID=glCwDQzBSiScXNzmRhbuQg==)

Solution 1

It’s possible that the Oracle server is starting up. Retry the connection after a brief pause. It’s possible that a network outage will occur. This will prevent the connection to the Oracle database from being established. Make that the database is up and operating, and that there are no network issues with it. If you’re having trouble connecting to an Oracle database using the host name, consider using the IP address instead. Ensure that the port configuration and the operating port are same. If you’re connecting using an application, make sure the connection url follows the format.

jdbc:oracle:thin:@localhost:1521/sid

Solution 2

Check to see if the listen is working properly. You can pause and resume listening if there is a problem. The connection to the specified host and port will be refreshed as a result of this action. Multiple connections may cause the listener to hang. After restarting the listener, try connecting it again. The issue ORA-12514, TNS:listener does not presently know of service requested in connect descriptor, will be resolved as a result of this.

lsnrctl status

lsnrctl stop
lsnrctl start

Solution 3

Check to see if the specified SID corresponds to a valid SID in the Oracle database. The SQL statement below will query the Oracle database for the specified SID. Use the database’s SID if the SID specified in listener.ora varies from the database configuration. This will resolve the issue. ORA-12505: TNS:listener is unaware of the SID specified in the connect descriptor.

select value from v$parameter where name='service_names'

value
------
orclcdb

Solution 4

Check the listener.ora file for errors. This is how the configuration should appear. The SID LIST LISTENER will hold the Oracle instance settings. This setup will map GLOBAL DBNAME, SID NAME, and ORACLE HOME in the database. The service names from the preceding query should be the same as the SID NAME. The LISTENER setup determines the PROTOCAL, HOST, and PORT. This is how the LISTENER configuration waits for the database connection to establish.

/u01/app/oracle/product/version/db_1/network/admin/listener.ora

OR

[ORACLE_HOME]/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orclcdb)
      (SID_NAME = orclcdb)
      (ORACLE_HOME = /u01/app/oracle/product/version/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orclcdb)

Solution 5

The SID settings, as well as the listener host and port, are all stored in the tnsnames.ora file. Below is an example tnsnames.ora file that demonstrates SID configuration. Check the SID in tnsnames.ora. Make the modifications specified below if the SID is not configured or is incorrectly configured.

/u01/app/oracle/product/version/db_1/network/admin/tnsnames.ora

OR

[ORACLE_HOME]/network/admin/tnsnames.ora

ORCLCDB=localhost:1521/orclcdb
ORCL=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orclcdb)
    )
  )

Solution 6

Check the oracle database’s system environment configuration. The proper database that is operating will be displayed in the Oracle database settings. It’s possible that numerous Oracle database versions are installed on the server, causing conflicts. Then /.bash profile, /.bashrc, or /etc/..bashrc will be used to set the environment.

export ORACLE_UNQNAME=orclcdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/version/db_1
export ORACLE_SID=orclcdb
export PATH=/home/oracle/bin:/home/oracle/LDLIB:$ORACLE_HOME/bin:/usr/sbin:$PATH

Solution 7

Finally, while attempting to connect to the database, double-check the settings you gave. If you misspelt the configuration, an error message will appear. Check the configurations below to see whether they match your Oracle database configurations.

Host name : localhost
port      : 1521
sid       : orclcdb
username  : hr
password  : hr

ORA-12505 means that the SID you provided in the connect identifier does not match any SID registered with the listener. Basically, SID means $ORACLE_SID, which is the instance name by default.

SID in TNSNAMES.ORA

In some upgrade or migration cases, some users used to use SID to connect to the old database. The connect identifier in TNSNAMES.ORA may look like this:

ERP2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ERP2)
      (SERVER = DEDICATED)
    )
  )

When they connect to the new database which may be a pluggable database (PDB), they’re unable to connect to the database with the same connect identifier. This is because the new database adopts SERVICE_NAME instead of SID as the entry point of connection.

C:Usersedchen>sqlplus hr/hr@erp2
...
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

We saw the connection failed with ORA-12505.

Solutions to ORA-12505

In fact, in a multitenant environment, the used name of SID has become a service name belonging to the migrated DB or PDB. SID is no longer used. That’s why the listener refused to establish connections, because there’s no such SID registered with the listener.

There’re two types of solution that you can choose.

1. Use SERVICE_NAME on Client Side
2. Use SID as SERVICE_NAME on Server-Side

1. Use SERVICE_NAME on Client Side

SERVICE_NAME in TNSNAMES.ORA

You can change the content of every client’s connect identifier from SID into SERVICE_NAME in TNSNAMES.ORA.

ERP2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ERP2)
      (SERVER = DEDICATED)
    )
  )

But where we can find TNSNAMES.ORA file? You may take a look.

Without TNSNAMES.ORA

At times, you may not able to modify TNSNAMES.ORA. In such moment, you can still connect to the database without TNSNAMES.ORA.

SERVICE_NAME in JDBC

For JDBC programmers, you can change the connect string from:

<IP Address>:<Port Number>:<SID>
Ex.
192.168.0.153:1521:ERP2

Into this:

//<IP Address>:<Port Number>/<Service_Name>
Ex.
//192.168.0.153:1521/ERP2

The differences are:

• Leading by double slashes.
• Delimited by a single slash between port number and service name.
• The SID is replaced with the service name.

This should be able to prevent ORA-12505, and after this, I know you may see some other errors like ORA-28040 or ORA-01017 if you connect to the database from a plain old client version, e.g. Oracle 9i client.

2. Use SID as SERVICE_NAME on Server-Side

If you prefer to solve this issue on the server side, the potential solution is to make the listener treat the SID as SERVICE_NAME and establish connections for users. Luckily, Oracle provides a parameter USE_SID_AS_SERVICE_LISTENER for listener to do this.

USE_SID_AS_SERVICE_<Listener Name> = ON

For Single-instance DB

In our case, we add this feature to listener.ora.

[oracle@test ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
USE_SID_AS_SERVICE_LISTENER = ON

Then we restart the listener to make it work.

[oracle@test ~]$ lsnrctl stop
[oracle@test ~]$ lsnrctl start

For RAC DB

For cluster databases, we should go for listener.ora at grid-level on ALL nodes.

[grid@primary01 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
...
USE_SID_AS_SERVICE_LISTENER = ON

Then restart listeners on all nodes.

[grid@primary01 ~]$ srvctl stop listener
[grid@primary01 ~]$ srvctl start listener
[grid@primary01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): primary02,primary01

Restrictions

Please note that, such backward compatibility has some restrictions. For a cluster system, USE_SID_AS_SERVICE_LISTENER is only used for connecting databases directly through local listeners.

Which means, SCAN listener won’t translate SID into SERVICE_NAME, so don’t use SCAN IP, use public or virtual IP of specific node.

In other words, if you insist on using SCAN IP to connect to the database, you should change SID into SERVICE_NAME in your connection string.

3. Test Connection

Now we can test the connection again.

C:Usersedchen>sqlplus hr/hr@erp2
...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> show user
USER is "HR"

In practice, SID is usually used to register as a static service in the listener.

If you don’t see any error pattern of your case in this post, please check: How to Resolve ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. There’re more patterns of connection problems.

This error is received when the listener received a request to establish a connection to a database or other service but 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 therefore listener is not aware of the SID.

In Listener Static Registration the SID is mentioned in the listener.ora by which listener knows about the SID.

#Here orcl is the SID name which is registered in the listener.ora 
 LISTENER=
   (DESCRIPTION=
     (ADDRESS_LIST=
       (ADDRESS=(PROTOCOL=tcp)(HOST=myserver.example.com)(PORT=1521))
       (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
 SID_LIST_LISTENER=
   (SID_LIST=
     (SID_DESC=
       (SID_NAME=orcl)) 

In Listener Dynamic Registration, SID is not mentioned in listener.ora instead its the job of PMON process to register the database to the listener and then the listener is made aware of the SID.

By default, the PMON process contact the listener running on port 1521 to perform the registration. To instruct PMON process to register to a listener running on different port, we have to change the LOCAL_LISTENER parameter. Since its dynamic LOCAL_LISTENER parameter can simply be altered using ALTER SYSTEM command.

#Following command will set local_listener to port 1522
alter system set local_listener='(ADDRESS = (PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1522))'; 
#Following command will instruct PMON to register the SID on the listener defined by local_listener parameter
alter system register;

If the SID is registered with the listener, it should show the SID in the Services summary:-

> lsnrctl status
 LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-DEC-2018 14:17:04
 Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=ISM01)))
 STATUS of the LISTENER
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
 Start Date                10-AUG-2018 13:01:12
 Uptime                    133 days 2 hr. 15 min. 51 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Listener Parameter File   /apps/network/admin/listener.ora
 Listener Log File         /apps/network/admin/listener.log
 Listening Endpoints Summary…
   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=orcl)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1522)))
 Services Summary…
 Service "orcl" has 1 instance(s).
   Instance "orcl", status READY, has 1 handler(s) for this service…
 The command completed successfully

If the service summary in lsnrctl status does not show the SID given in the client’s tnsnames.ora (CONNECT_DATA) then ensure that the same SID is either dynamically or statically registered with the listener.