http://tr.im/sKrG
Sokrates, July 17, 2009 - 3:51 am UTC
do not telnet to a listener. You don't know the protcol.
that's why I asked ...
If you have the software on your system, you can setup your listener.ora file to point to the remote site if need be.
thank you, that's what I was looking for
ORA-12514: troubling me....
A reader, June 10, 2011 - 3:53 am UTC
Tom, Below are the steps putting me into trouble. Please help...
I am trying to change the log mode to ARCHIVELOG
C:\>sqlplus sys/manager@test as sysdba
SQL> select log_mode from sys.v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
SQL>
A reader, June 11, 2011 - 12:31 am UTC
Tom, I am not try to login from a remote machine.
Both my server and client are on same machine. It was working fine till yesterday. I was trying some demo for Transporatible tablespace, created few DB's. I am not sure if that caused the issue.
Also, If I restart my service [i am on windows], I can successfully hook. Not sure what goes wrong when I shutdown and try to mount my database.
Please help
June 11, 2011 - 2:51 pm UTC
I'm sorry, but you ARE logging in using the network
C:\>sqlplus sys/manager@test as sysdba
see that @test - that shows you are. Don't use @test if you want to log in directly.
by restarting the service, you are starting the database instance (without using the network). Not sure what you mean by "hook" at all, not familiar with that term in this context.
A reader, June 14, 2011 - 6:03 am UTC
When I tried logging in as local I got following error.
C:\>sqlplus sys/manager as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error
C:\>sqlplus scott/tiger
ORA-12560: TNS:protocol adapter error
Listener status says there are no listener service running.
C:\>lsnrctl start
LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 14-JUN-2011 16:28:54
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
System parameter file is E:\...\11.1.0\db_1\network\admin\listener.ora
Log messages written to e:\....\diag\tnslsnr\Htipl-7090267\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=a.b.c)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production
Start Date 14-JUN-2011 16:28:56
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File E:\.....\11.1.0\db_1\network\admin\listener.ora
Listener Log File e:\....\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=a.b.c)(PORT=1521)))
The listener supports no services
The command completed successfully
June 17, 2011 - 11:43 am UTC
your database service is not started, use control panel to start up your databaseservices.
A reader, June 14, 2011 - 6:08 am UTC
In addition to above please not that my services are running fine
C:\>net start OracleServiceprats
The requested service has already been started.
C:\>net stop OracleServiceprats
The OracleServicePRATS service is stopping......
The OracleServicePRATS service was stopped successfully.
C:\>net start OracleServiceprats
The OracleServicePRATS service is starting...............
The OracleServicePRATS service was started successfully.
C:\>sqlplus scott/tiger
ERROR:
ORA-12560: TNS:protocol adapter error
Please help.........
June 17, 2011 - 11:44 am UTC
make sure your environment is set correctly. I cannot tell that you are actually trying to log into your prats database - set your oracle sid/oracle home in the environment manually
To: A reader
A reader, June 14, 2011 - 9:09 am UTC
You need to set ORACLE_SID before connecting.
set ORACLE_SID=<SID of database you want to connect to>
sqlplus / as sysdba
Then report back if successful or not. This is based on assumption that everything was working fine one day and broke after you added more databases to server.
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 14 10:06:41 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
C:\>set ORACLE_SID=ORCL1
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 14 10:06:58 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options
SQL>
A reader, June 15, 2011 - 2:36 am UTC
setting ORACLE_HOME solved the issue.
What I wuld like to know, why it just stopped working. I have more than 1 services running on my machine and I was able to log in to all of them by specifying SID while logging in.[scott/tiger@test]
How was it working earlier. Now I would have to reset ORACLE_HOME for logging into each SID.
please help....
June 17, 2011 - 12:09 pm UTC
You or someone you work with changed something on that machine.
scott/tiger@test
is NOT specifying a sid, it is specifying a sqlnet connection string, you were connecting over tcp/ip.
You can set your default oracle home/sid by setting up your environment using control panel to specify environment variables.
To: A reader
A reader, June 15, 2011 - 10:31 am UTC
Please copy/paste the actual screen output on how setting ORACLE_HOME resolved your problem.
In Windows, when you install Oracle, the path to ORACLE_HOME\bin is automatically added to your PATH environment variable. Now, lets say that after installing Oracle database server software, you installed another Oracle product (say EM agent). Now your PATH will have AGENT_HOME\bin first and then ORACLE_HOME\bin. If you type say sqlplus, Windows will look in the path and run sqlplus from AGENT_HOME\bin. This sqlplus could be a different version from your database server software. If you want to change the order of what appears first in your PATH, you can do it from within OUI. All this is relevant to 10g and above.
I still cannot see how setting ORACLE_HOME fixed your issue as ORACLE_HOME is rarely used in Windows. The only time I have to set ORACLE_HOME is when I want to run asmcmd.
June 17, 2011 - 12:42 pm UTC
we use the combination of the oracle_home and oracle_sid to 'attach' to an SGA.
If they changed the oracle home to another oracle home (that also had sqlplus in it), they would not be able to connect directly to the database started with a different oracle home.
A reader, June 16, 2011 - 12:25 am UTC
gotcha... There is a typo, I meant setting ORACLE_SID and not ORACLE_HOME. apologies....
My current query is ---
I have 3 services running and they have been working just fine till 3 days back. I guess I didn't change anything. Then why did it just stopped working.
Setting ORACLE_SID everytime would need me do it everytime I try to log in [scott/tiger@test] to different instance.
June 17, 2011 - 1:26 pm UTC
I guess I didn't change anything.
you or someone did, it is that simple. Software doesn't have a shelf life (although it might seem like it does). Something changed
To: A reader
A reader, June 16, 2011 - 9:41 am UTC
You do not have a default ORACLE_SID set correctly in your environment. At a command prompt window, type
echo %ORACLE_SID%
Most likely, you will either get
%ORACLE_SID%
or you will get the sid of a database which no longer exists (maybe you deleted the database).
In Windows, Oracle creates an environment variable ORACLE_SID with the SID of a database that you created. Sometimes, this value can get deleted or may point to a non-existent SID which would result in the TNS protocol adapter error.
My advise would be to unset any default ORACLE_SID if you have multiple database running on the server. You can accidentally connect to the wrong database and do something really bad.
Here is what happened to me once. The default ORACLE_SID was set to ORCL. I did:
set ORCALE_SID=TEST
connect / as sysdba
Which database did I connect to? In my mind, I was connected to TEST database. Notice that I misspelled ORACLE (as ORCALE) in setting the ORACLE_SID. Since default SID was set to ORCL, I connected to ORCL database instead of TEST database. I was searching for tables which did not exist till I realized what had happened.
If the default SID was not set, I would simply have got a TNS protocol adapter error (meaning I was trying to connect to a database which either does not exist or its Windows service had not been started). Much better than connecting to a wrong database and messing things up.
To: A reader
A reader, June 16, 2011 - 9:55 am UTC
More thoughts:
You are connecting as scott/tiger@test. This does NOT require you to set ORACLE_SID. As long as the TNS entry "test" points to the right database, you should be able to connect. Do a tnsping test to see what the TNS entry resolves to.
set ORACLE_SID=TEST
sqlplus scott/tiger
and
sqlplus scott/tiger@test
are two very different ways to connect. The first method can be used only on the server the database TEST is running on. It cannot be used from a remote client.
The second method can be used either on the database server or from a remote client provided:
a) The tnsnames.ora has a valid entry for test.
b) The listener on the host is running.
c) The database service is registered with the listener
d) There is no firewall blocking your connection.
These are just basic concepts. Please check Oracle documentation for more variations and details.
A reader, June 17, 2011 - 1:55 pm UTC
Tom,
You said:
we use the combination of the oracle_home and oracle_sid to 'attach' to an SGA.
If they changed the oracle home to another oracle home (that also had sqlplus in it), they would not be able to connect directly to the database started with a different oracle home.
Can you please elaborate on what you mean by "If they changed oracle home to another oracle home". Are you saying that if they set environment variable ORACLE_HOME to a new path they will not be able to connect to Oracle database running from a different ORACLE_HOME? or Are you referring to Oracle install in a new home while database is running from an existing home? I have tested both ways and had no trouble in connecting locally.
Thanks...
June 17, 2011 - 2:17 pm UTC
well, actually, I just tried it out - windows doesn't care so much about the oracle_home, just the oracle_sid for a direct connection.
Under Unix/Linux - we take the oracle_home PLUS oracle_sid and hash them together to get the key to the shared memory segment - so what I said was for Unix I guess - and in reviewing the above - it turned out not to be the oracle-home - but either a tns setting or oracle_sid setting (it is not entirely clear :) )
[ora11gr2@localhost 11.2.0]$ ls -l
total 12
drwxr-xr-x 75 ora11gr2 ora11gr2 4096 Mar 29 03:25 dbhome_2
lrwxrwxrwx 1 ora11gr2 ora11gr2 8 Jun 17 15:14 x -> dbhome_2
<b>so, my oracle home is really dbhome_2, but I have a symbolic link to it named x</b>
[ora11gr2@localhost 11.2.0]$ export ORACLE_HOME=/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_2
[ora11gr2@localhost 11.2.0]$ sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 17 15:15:28 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ops$ora11gr2%ORA11GR2> exit
<b>when my oracle home is set to the right thing, we get right in...</b>
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora11gr2@localhost 11.2.0]$ export ORACLE_HOME=/home/ora11gr2/app/ora11gr2/product/11.2.0/x
[ora11gr2@localhost 11.2.0]$ sqlplus /
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 17 15:15:39 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
<b>but now we cannot 'find' the database - even though 'x' contains all of the same files. We cannot attach the SGA</b>
HowTo - Failed Connection Attempts
Narendra, February 24, 2022 - 9:01 am UTC
Hello Connor/Chris,
I was wondering if there is a way to implement some kind of mechanism in place whereby a listener can block incoming connection requests from a specific server and/or user after N number of successive connection attempts using, say, incorrect service name.
I can think of one possible situation where this can be useful. When database changes roles (primary switched to standby), all clients will either change connection string or will continue to work if they are using MAA connection string. However, there can be one or two clients that are neither using MAA connection string nor have changed to point to new primary database. Such clients (especially applications) will continue to try to connect to standby database with primary service and will fail with ORA-12514, which gets logged in listener logs. If there is a mechanism to block such clients, even for a period of time at listener level then that would be helpful.
In theory, it needs to work like FAILED_LOGIN_ATTEMPTS profile option at user authentication level.
Can you please help?
Thanks in advance
March 02, 2022 - 2:35 am UTC
I suppose thats a chicken and egg scenario.
To be "blocked" from the listener, you have to reach the listener to be told you're blocked. From the listeners perspective it is really no different. Someone said: "Hey I'd like to connect" and it either said: "No such service" or "Hey, you're blocked".
You *still* have hit the listener.
Re: HowTo - Failed Connection Attempts
Narendra, March 02, 2022 - 2:31 pm UTC
Hello Connor,
Understood your point about "chicken and egg" situation.
I guess I did not elaborate my question properly so apologies for that.
If a "client" is trying to connect to a listener with invalid service name then it is right that listener is refusing the connection. But if a rogue client keeps trying to connect with invalid service name (10s of times per second) then the listener logs get flooded with these errors (which are effectively same) and despite having log rotation in place, it can overwhelm the listener log, which in turn can fill up the file system where listener logs are stored (typically the same file system used by Oracle Software Home).
I was wondering if any part of database or listener has any mechanism to prevent the listener logs getting flooded like this.
Thanks in advance,
March 03, 2022 - 8:33 am UTC