ora 12505 что делать
Ora 12505 что делать
# 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 = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rsud-orel)(PORT = 1521))
)
)
# 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_DESC =
(GLOBAL_DBNAME = RSUD)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = RSUD)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rsud-orel)(PORT = 1521))
)
)
������:
���������: 60
������:
���������: 60
QUERY_REWRITE_ENABLED to TRUE
QUERY_REWRITE_INTEGRITY to TRUSTED
NLS_LENGTH_SEMANTICS initialization parameter to CHAR
ALTER SYSTEM SET PROCESSES = 300 SCOPE = SPFILE.
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:
TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
I’m trying to connect to Oracle 10.2.0 from NetBeans, using the following connection string:
The weirdest part is that everything worked fine, until the one of the reboots. I started getting this TNS-12505 errors all the time; look at the final entries in my listener.log :
The first three entries are my attempts to connect from NetBeans. The fourth, the connection via SQL*Plus, worked just fine.
Here are the contents of my listener.ora :
And tnsnames.ora :
And output from > lsnrctl services
Both Oracle services are started and SQL*Plus connects fine.
Is there some workaround for this error?
17 Answers 17
You need to add the SID entry for XE in order to register the instance with the listener.
After installation of Oracle XE, everything looks good, but when you issue
the instance will not register with the listener.
So please edit your listener.ora like this:
This issue came up when I installed Oracle XE on Windows 7. I did not face this problem on Windows XP. In general, this entry should not be necessary, because the instance should register with the listener automatically. Running Oracle XE on Linux (Fedora), there is no need to add XE to the sid-list.
After a change of ip in our oracle virtual machine, the listener never worked again. Finally these command solved the problem (where 192.168.10.200 is the new ip)
[oracle@oracle admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue May 10 12:57:37 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.200)(PORT=1521))’ scope=both;
SQL> alter system register;
I ran into this problem after a firewall change to restrict access between our internal network and the database server reporting the error located in the DMZ. Communication was working fine until the change, and system and database restarts were of no help. In my case both Oracle XE11gR2 installations are on Windows.
it may be that this worked simply because of the ‘alter system register’ as suggested by ik_zelf. I did have the IP already set in relevant places in listener.ora and tnsnames.ora.
Step 1 – Check the DB listener status
Notice that the listener you want (in our case “orcl”) is not showing.
Step 2 – Login via sqlplus
Sqlplus gave us this error message:
Step 3 – Go into the Windows Event Viewer (eventvwr.exe)
Under “Windows Logs”, right click on Application and select “Clear Log”. Do the same for System.
It may also be wise to right click on Application and select Properties. Then, under “Log Size” select the following option under “When maximum log size is reached”: “Overwrite events as needed”. This should prevent the log from maxing out and causing the DB not to start.
In Windows Vista and higher, you can execute the following command to clear the Application log:
Step 4 – Login via sqlplus
You should now be able to login with no error messages.
You should now see your listener running.
UCM should now start up.
For a more in-depth answer to this question you can read my full blog post.
I’m running oracle xpress edition 11.2 on windows 8 and I had the same error when trying to connect to DB using sqldeveloper.
I’ve edited listener.ora as per Brandt answer above and even restarted my machine the issue wasn’t fixed.
I started it and tried to connect again, issue resolved.
As mentioned by removing the colon : and replacing with slash / before the sid worked for me.
I have had this issue before, too.
I had the same issue on Windows 7. The cause was, that I had been connected to VPN using Cisco AnyConnect Secure Mobility Client.
It worked for me So please check LSNRCTL> stat in command prompt and configure your connection url accordingly.
Hope it will help Someone.
Your database, that apparently has the ORACLE_SID XE, is not defined in the listener.ora. That is no problem, since when the database normally opens, it will register itself to the default listener, being the one on port 1521 so that is ok.
If the database starts before the listener, the database has no listener to register to. It will do so every few minutes so after a while, I expect the error will go away because of the registration has taken place. You can issue alter system register; to speed this. If the database is in restricted mode, the connections using a service will fail. You are using ORACLE_SID so that is not your problem.