Good one
Gururaj Kulkarni, July 18, 2001 - 2:04 pm UTC
Thakns Tom
Problem with save_config
Gururaj Kulkarni, July 18, 2001 - 2:16 pm UTC
Hi Tom,
I tried using the commands which you have mentioned. While
using the save_config, I got the error "TNS-01169: The
listener has not recognized the password ".
I tried to stop the service and it is giving the same
problem. How do I fix this ?
Thanks in Advance
-Gururaj
To Check Status of Listener
Suresh, July 18, 2001 - 5:27 pm UTC
Tom,
Sometimes when I try to connect to Oracle, I get a message 'Listener not up' or something..I am not sure of exact wordings. How do I check the status of the Listener? If it is not up, how do I bring it Up.
Respnse to previous post
Connor, July 19, 2001 - 9:21 am UTC
"lsnrctl status" to show the status
"lsnrctl start" to start the listener
What's that One Extra Listener "Handler" ?
Robert, June 16, 2003 - 10:48 am UTC
9iR2/Linux
My lsnrctl status :
Services Summary...
Service "OEMREP.localdomain" has 1 instance(s).
Instance "OEMREP", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ora1.localdomain" has 2 instance(s).
Instance "ora1", status UNKNOWN, has 1 handler(s) for this service...
Instance "ora1", status READY, has 1 handler(s) for this service...
Service "ora1XDB.localdomain" has 1 instance(s).
Instance "ora1", status READY, has 1 handler(s) for this service...
The command completed successfully
-------------------
Why is there one EXTRA "handler" for "ora1" instance (my test instance) that's of status UNKNOWN ? :
"Instance "ora1", status UNKNOWN, has 1 handler(s) for this service..."
Thanks
ORA-01034: ORACLE not available
atul, December 29, 2003 - 12:39 pm UTC
Hi,
When i try to connect to my database using tnsentry it gives
error "ORA-01034: ORACLE not available"
sqlplus sys/prereldb@ieeceu51_622.world
ERROR:
ORA-01034: ORACLE not available
But without that it's working fine
sqlplus sys/prereldb
connected
Oracle is owned by user oracle,and listener is stared with username "porb0804".
Will that is the cause of problem?
Should listner process owner be same as oracle owner?
Thank.
Atul
December 29, 2003 - 1:08 pm UTC
well, no one could say for sure that the database you access via "ieeceu51_662.world" is even remotely the same as you access via "nothing"
but basically, you've misconfigured something either in your tnsnames.ora file (wrong oracle home, wrong oracle sid) or your listener.ora file.
ORA-27121 unable to determine size of shared memory segment
P.S.Vinodh, January 02, 2004 - 6:11 am UTC
Hi Tom ,
iam facing problem while connecting to database using sqlplus.
ORA-01034: ORACLE not available
ORA-27121 unable to determine size of shared memory segment
SVR4 Error :13: Permission denied.
i checked with metalink but nothing is working .. what would be the problem...
can you please throw me some light on it tom
thanks in advance
January 02, 2004 - 9:37 am UTC
[tkyte@localhost tkyte]$ oerr ora 27121
27121, 00000, "unable to determine size of shared memory segment"
// *Cause: shmctl() call failed
// *Action: check permissions on segment, contact Oracle support
sounds like your permissions are set wrong. did you run root.sh on install? are the perms on the oracle binary set right (they should be setuid). if you use ipcs, do the segments already exist and are they owned by the right guy.
i do assume that since you said you hit metalink, you read this note right:
Article-ID: <Note:235469.1>
Permission Problem
P.S.Vinodh, January 06, 2004 - 7:42 am UTC
Hi tom,
the problem is i have tried with
1)changed the permission of the binary files .
2)setuid enabled.
Other thing is in that same server all other database are working actually we created the test database. but now we could not able to login but all other database are working fine.. with that..
can you help me out in this.
January 06, 2004 - 8:59 am UTC
so, show us the output of:
$ env
$ ls -l $ORACLE_HOME/bin/oracle
$ sqlplus u/p
a DIRECT cut and paste -- no edits please.
permission problem
vinodh, January 07, 2004 - 1:17 am UTC
Hi tom,
as you said i have given below in that oracle binary file sticky bit i have reverted back to orginal permission becoz if it has sticky bit then sysdba is not getting connected .
: bash-2.03$ env
PWD=/db/rgprd
TZ=US/Pacific
ORACLE_SID=rgstgnew
HZ=
HOSTNAME=nplsjstgdb01
MACHTYPE=sparc-sun-solaris
EDITOR=vi
ORACLE_HOME=/oracle/9.2.0
LOGNAME=rgprdora
SHLVL=1
SHELL=/bin/bash
HOSTTYPE=sparc
OSTYPE=solaris
HOME=/db/rgprd
TERM=vt100
PATH=/usr/local/bin:/usr/bin::/db/rgprd/bin:/obacktrack-3.4.00-64-o8lib/bin:/usr
/ccs/bin:/oracle/9.2.0/bin:/usr/ccs/bin
_=/usr/bin/env
bash-2.03$ ls -l $ORACLE_HOME/bin/oracle
-rwxr-xr-x 1 oracle dba 68208176 May 28 2003 /oracle/9.2.0/bin/oracle
bash-2.03$ sqlplus test/test@rgstgnewp
SQL*Plus: Release 9.2.0.3.0 - Production on Tue Jan 6 22:12:24 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
SVR4 Error: 13: Permission denied
January 07, 2004 - 7:47 am UTC
then it is owned by the entirly wrong person (eg: whoever installed this messed up big time in a fashion I've not seen before)
do an ipcs -a and see who owns this shared memory segment.
the oracle binary must be owned by that user.
the oracle binary must be setuid for that username.
thanks
P.S.Vinodh, January 12, 2004 - 2:24 am UTC
Hi Tom,
Our Apps DBA installed that..
Thanks for your time tom,
ORA-01034,ORA-27101 and Linux Error :2: No such file or directory
Lawrence, March 04, 2004 - 2:10 pm UTC
I tried it on my Redhat Enterprise 3 and Oracle10g,but sqlplus still failed:
ORA-01034 ORACLE not available
ORA-27101 share memory realm does not exist
Linux Error :2: No such file or directory
I searched on metalink.oracle.com,asktom.oracle.com and others website,peoples said maybe ORACLE_HOME, ORACLE_SID or kernel parameters are not correct,I tried in different ways,unfortunately, is not helpful. There are so many people encounter the problems, why Oracle does not consider to fix it???
thanks,
Lawrence
here are some files:
1.tnsname.ora:
SALES =
( DESCRIPTION =
( ADDRESS = ( PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
2.listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/home/oracle/product/10.1.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=localhost.localdomain)
(POST = 1521))
)
)
)
3.kernel parameters:
kernel parameters:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
add following line to:
/etc/security/limits.conf:
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
add following line to:
/etc/pam.d/login
add following line to:
session required /lib/security/limits.so
add following line to:
/etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
4.bash_profile
PATH = $PATH:$HOME/bin
umask 022
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=sales
export ORACLE_TERM=xterm
LD_LIBRARY_PATH=$ORACLE_HOME/jdk/fre/lib/i386:$ORACLE_HOME/jdk/fre
export LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
5.part of env:
SSH_AGENT_PID=2697
HOSTNAME=localhost.localdomain
SHELL=/bin/bash
TERM=xterm
NLS_LANG=AMERICAN
USER=oracle
LD_LIBRARY_PATH=/home/oracle/product/10.1.0/db_1/jdk/jre/lib/i386:/home/oracle/product/10.1.0/db_1/jdk/jre
ORACLE_SID=sales
ORACLE_BASE=/home/oracle
USERNAME=oracle
PATH=/USR/kerberos/bin:/usr/local/bin:/usr/bin:/lib:/usr/X11R6/bin:/home/oracle/bin:/home/oracle/product/10.1.0/db_1/bin
ORACLE_TERM=xterm
HOME=/home/oracle
LOGNAME=oracle
ORACLE_HOME=/home/oracle/producr/10.1.0/db_1
XAUTHORITY=/home/oracle/.xauthority
OLDPWD=/home/oracle/product/10.1.0/db_1/network/admin
March 04, 2004 - 2:44 pm UTC
there is nothing to fix, except something in your configuration is why....
and unfortunately you have none of the needed stuff here.
I cannot see what command line you used to try and connect (eg: was it over the network, local, how)
I cannot see what ORACLE_HOME and ORACLE_SID were used to start the database.
I can see an obvious typo in your ORACLE_HOME environment variable -- producr. Hmmm....
connection problems
John, May 10, 2004 - 8:37 pm UTC
May 11, 2004 - 8:02 am UTC
contact support for that. appears to be a doc bug
Hdr: 2207736 9.0.1.1.1 LISTENER 9.0.1 PRODID-115 PORTID-453
Abstract: "LISTENER LOG ANALYZER SCRIPT" DOES NOT EXIST
Oracle Developer
Dawar, July 27, 2004 - 12:29 pm UTC
Server: Operating System SunOS 5.5.1
Client: Windows NT 4.0
---select banner from v$version (for Server)
Oracle7 Server release 7.3.4.5.0 -Production
PL/SQL Release 2.3.4.5.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for Solaris: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 -Production
---------------------------------------------------
Tom,
We have two instances/environment Production & Staging.
We can logon to the Oracle Database through telnet in both environments.
We can also log on to Production Database through client by using SQL Plus or Toad.
But we are not able to logon to Staging Database through client.
Following error message comes up:
ORA-12505: TNS :listener could not resolve SID given in connect descriptor
Action 1
---------
Staging (I logon to staging box through telnet)
--------
ps -ef|grep LISTEN
get no values than
Then I run “lsnrctl start"
ps -ef|grep LISTEN
oracle 230 1 0 Jul 09 ? 0:00 /oracle/rev/7.1.3/bin/tnslsnr LISTNER -inherit
Note: Here is the Oracle version of Listner is 7.1.3.
But my Oracle Server Database version is 7.3.4.5
For the Production we have same version 7.3.4 in both scenarios.
Production:
-----------
oracle 7 22084 22057 0 08:27:02 pts/3 0:00 grep LISTEN
oracle 7 310 1 0 Ju; 09 ? 0:00 /disk/oracle/product/7.3.4/bin/tnslsnr LISTNER -inherit
Action 2
---------
At client machine/PC under Orant/bin we have two sqlplus file. +Plus80w & +Plus33w
I logon from there as follows:
Note: for Production its works fine but I am getting errors for Staging.
For Staging: I am getting errors as below
sys/manager@witstage
SQL*Plus: Release 3.3.4.0.0 - Production on Mon Jul 26 17:53:56 2004
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
+Plus80w.
Here is the result from +Plus80w
sys/manager@witstage
SQL*Plus: Release 8.0.5.0.0 - Production on Mon Jul 26 17:46:21 2004
(c) Copyright 1998 Oracle Corporation. All rights reserved.
ERROR: ORA-12505: TNS:listener could not resolve SID given in connect
descriptor
Action 3
--------
I used telnet to go to directories as below
Staging
/disk/app/oracle/product/7.3.4/network/admin
wits2 44: tnsping witstage
TNS Ping Utility for Solaris: Version 2.3.4.0.0 - Production on 26-JUL-04
16:33:
36
Copyright (c) Oracle Corporation 1995. All rights reserved.
Attempting to contact
(ADDRESS=(COMMUNITY=dhs.world)(PROTOCOL=TCP)(Host=wits2)(P
ort=1521))
OK (120 msec)
wits2 45:
Regards,
Dawar
July 27, 2004 - 12:59 pm UTC
fairly simple (although your environment is a mess -- you should be using the listener of the HIGHEST version -- not the lowest)
[tkyte@tkyte-pc tkyte]$ oerr ora 12505
12505, 00000, "TNS:listener could not resolve SID given in connect descriptor"
// *Cause: The SID in the CONNECT_DATA was not found in the listener's tables.
// *Action: Check to make sure that the SID specified is correct.
// The SIDs that are currently registered with the listener can be obtained by
// typing "LSNRCTL SERVICES <listener name>". These SIDs correspond to
// SID_NAMEs in TNSNAMES.ORA, or db_names in INIT.ORA.
// *Comment: This error will be returned if the database instance has not
// registered with the listener; the instance may need to be started.
Oracle Developer
Dawar, July 27, 2004 - 2:52 pm UTC
Tom,
Thanks for your feed back.
Problem has been solved.
I need to stop LISTENER from 7.1.3 directory (disk/rev/7.1.3) and start from 7.3.4 dir ($ORACLE_HOME) and its works.
Now I am getting correct version for staging environment.
ps -ef|grep LISTEN
oracle 19352 1 0 11:03:44 ? 0:00 /disk/app/oracle/product/7.3.4/bi
n/tnslsnr LISTENER -inherit
cheers,
Dawar
Hi,
A reader, August 11, 2004 - 11:22 am UTC
The Ascential ETL tool while running its job, generated ORA-12505: TNS:listener could not resolve SID given in connect descriptor.
Before this error happened, the same job connected to the database successfully and did select, truncate and insert on two other tables. After running for 1.5 hours, it generated the ORA error. In fact, the process truncated the third table and failed to insert due to the error.
I am not sure how this error had happened.
I checked the alert log file and the listener.log file and I didn't get any particular information.
Any idea about this?
Thanks,
August 11, 2004 - 1:36 pm UTC
any idea why this tool would connect more than once?
sounds like someone might have restarted your database or listener. if you restart the listener, it loses it's services until the databases re-register in a minute or two if you are using dynamic registration. if you shutdown the database, same thing.
but why would a tool use separate connections for something like this -- it should connect and stay there. do they have a "use smart logic for connections" option ?
A reader, August 11, 2004 - 2:32 pm UTC
This happened in the mid-night and no one has restarted the listener or the database.
Actually the datastage connects to the database only once, but everystage (Oracle table) has its own username,password and SID to connect. In this particular job we have 4 different oracle table stages and it raised this error to only one particular stage.
We also noticed that in the post-job, there are three different sql connections each connecting to the same username/password@sid. The first connection failed with ora-12505 error and the other two completed successfully. The time duration between the first connection and the second connection is seconds.
This made us to problem more complex.
thanks,
August 12, 2004 - 7:42 am UTC
can you "prove" that no one restarted something? (eg: ps and show that the tnslistener was continously up, v$database to show the database was up)
seconds is all anything takes.
and why did the job blindly continue onwards? no error handling in there?
change_password and dbstart/dbshut
jc, August 31, 2004 - 6:10 pm UTC
Thank you Tom for all of your insights.
One variation on the questions above. In 9iR2 on Solaris 8, if the listener's encrypted password is set, is there a way to allow stopping and restarting of the listener in the dbshut and dbstart scripts if the db server is rebooted or goes down? So for example, on server reboot, will the dbstart process fail because the listener password has been set or will it bypass the listener password protection? I appreciate that you're very busy, and thank you for your time on this Tom.
Regards.
September 01, 2004 - 7:26 am UTC
dbstart /shut don't do the listener (you need/want but one listener per server, you need/want one database instance per server but people seem to want to run more than one instance for whatever reason...)
so, in whatever script you use to start the listener, you can also script the "supplying of the password"
Security risks without listener password?
Harri, January 05, 2005 - 10:54 am UTC
Is there any critical security risks if listener is configured without password? Yes, someone can shut down listener but I think that's not so bad thing. "Hacker" have to have access to the server anyway if he/she want to shut down the listener, right?
Anyway DB connections are still working so the only harm is that new connections are not available until listener is up again.
-Harri
January 05, 2005 - 11:26 am UTC
can you spell DOS, that is what this would be called.
"hacker" doesn't need access to "server" to shutdown listener. "hacker" just needs lsnrctl on their machine.
hacker can issue "save" (hmm, where did my configuration file go?)
in 10g, there is "local OS authentication" so you cannot remotely administer. so in 10g -- you may have some points.
Thanks
Harri, January 05, 2005 - 12:54 pm UTC
Oops, that remote adminisration changes my policy little bit - a couple of hundred listeners needs to be reconfigured...
Thanks for the quick resp!
-Harri
January 05, 2005 - 12:59 pm UTC
(you could use the 10g listener...)
see <Note:260986.1> on metalink
9i DB Connection Problem on HP-UX!
A reader, January 19, 2005 - 2:10 pm UTC
Hi Tom,
I read this page and tried to fix my problem but to no avail!
I have two Oracle Home on a HP-UX box:
ORACLE_HOME=/opt/oracle/product/9.0.1 db1=nmdb01 db2=nmdb02
ORACLE_HOME=/opt/oracle/product/9.2.0 db1=dbo01
As you see, 9.01 has two DBs and 9.2 has one.
I stopped old listener and started the new one:
oracle@hera> lsnrctl stat
LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 19-JAN-2005 13:42:35
Copyright (c) 1991, 2001, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hera.nm.cbc.ca)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.1.0 - Production
Start Date 19-JAN-2005 12:53:04
Uptime 0 days 0 hr. 49 min. 31 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hera.nm.cbc.ca)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dbo01" has 1 instance(s).
Instance "dbo01", status READY, has 1 handler(s) for this service...
Service "nmdb01.cbc.ca" has 1 instance(s).
Instance "nmdb01", status READY, has 1 handler(s) for this service...
Service "nmdb02.cbc.ca" has 1 instance(s).
Instance "nmdb02", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@hera>
As you see, listener has an instance for all DBs.
I also added DBs to tnsname.ora on both OracleHome:
dbo01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbo01)
)
)
nmdb01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nmdb01)
)
)
nmdb02 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = nmdb02)
)
)
Almost everything is set. However, when I connect to the old DB using SQLPLUS, I can not connect to the new DBs from there and vice versa. I mean when ORACLE_HOME is …./9.0.1 using SQLPLUS, I just can connect to ‘nmdb01’ and ‘nmdb02’ using ‘export ORACLE_SID=nmdb02’. I mean even the following failed:
nmdb01> connect system@nmdb02
Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name
Could you please tell me why it is like this? Why can’t I connect to 9.2 DB when Oracle Home is …./9.2.0 or vice versa? and also why can’t I use ‘connect system@foo’ in sqlplus?
Best regards,
- Arash
January 20, 2005 - 9:58 am UTC
you are using the wrong listener software, you always use the HIGHEST version listener -- 9.2 in this case.
make sure your TNS_ADMIN is not pointing to a different location.
test with
connect system/(description=........)
(eg: remove the tnsnames.ora from the equation for a moment)
do this after using the correct listener.
A reader, January 19, 2005 - 2:13 pm UTC
A typo in last message:
Why canÂ’t I connect to 9.2 DB when Oracle Home is "Â…./9.0.1 ...."
A reader, January 20, 2005 - 10:12 am UTC
Tom,
I didn't understand exactly what you mean. How can I check TNS_ADMIN on HP-UX?
oracle@hera> echo $TNS_ADMIN
su: TNS_ADMIN: Parameter not set.
and for listener:
oracle@hera> lsnrctl status
LSNRCTL for HPUX: Version 9.0.1.3.0 - Production on 20-JAN-2005 10:02:23
you mean it shouldn't be 9.0.1.3.0? But I used the new one which is located at new Oracle Home. I mean
/opt/oracle/product/9.2/bin/lsnrctl
Could you please explain it more?
Thanks a lot,
- Arash
January 20, 2005 - 10:52 am UTC
echo $TNS_ADMIN
you should use the 9ir2 listener
your tnsnames.ora is either in the wrong place or you have tns_admin set.
I won't be around for the rest of the day -- support is always there for configuration/installation issues.
A reader, January 20, 2005 - 10:16 am UTC
You are right Tom. I checkd with the other server which is working ok and listener is:
LSNRCTL for HPUX: Version 9.2.0.1.0 - Production on 20-JAN-2005 10:12:36
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Now my question is how can I set that it uses the right listener?
Thanks again,
- Arash
January 20, 2005 - 10:53 am UTC
stop the wrong one
configure the right one
start it
A reader, January 20, 2005 - 11:03 am UTC
Sorry sir for asking simple questions!
What do you mean by configure the right one? I have never done that! I always installed oracle and started listener. Even on the other HP-UX I didnÂ’t do anything more than this.
Is there any parameter file? How should I configure it properly?
Thanks,
- Arash
January 20, 2005 - 7:05 pm UTC
configure 9.2, not 9.0
do you have a DBA?
A reader, January 20, 2005 - 11:12 am UTC
Tom,
If you mean listener.ora, it is pointing to the right Oracle Home, but still showing the old version!
# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.nm.cbc.ca)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
)
- Arash
A reader, January 20, 2005 - 2:40 pm UTC
Thanks Tom. I fixed it.
A reader, January 21, 2005 - 11:25 am UTC
i am sorry sir
aman, March 16, 2005 - 12:10 pm UTC
sir i know i am not supposed to ask any question over here.but i am just stuck from 2 days and have no other option.
I am using Oracle815EE on WinXP professional with DatabaseSid=>nick815.I have listener(LISTENER on 1521).I had to connect 12 clients to the server.I installed and did that.But from 2 days every client is giving operation timed out error.Sever is working fine.there is no problem over there.Even after setting CONNECT_TIME to 0 (as given the action for the error) i am still getting this error.I tried to create another listner(LISTENER1 on 1522)Its nothing that was changed.
Please suggest what should i do sir.
with regards
Aman
March 17, 2005 - 7:26 am UTC
well, many problems -- first and foremost using software that was written before the OS was published. second and as important, using software that hasn't been supported or supportable for years.
If you want to use really old software, you have to go entirely old (OS included).
Not sure what to tell you here.
i tried on Win98 too sir
aman, March 17, 2005 - 7:32 am UTC
sir yes i know but i tried on installing the clients on window 98 too.it worked well for some days but then they all started too giving the same error.I know that Oracle815 is not supported and supportable but I someway am stuck with it only at this client,Isnt there any way out sir?should i deinstall and try again?will it have some benefit?
with warm regards
aman
March 17, 2005 - 9:25 am UTC
the *server* is windows xp
the server software post dates the database software by some time.
the server is most likely causing the issue here, not the client. (how can the server be deemed "fine" if no one can connect to it?)
somehow done the network sir!
aman, March 18, 2005 - 9:15 am UTC
hi sir
i have used a different system ansd installed Oracle815EE over there.then connected all the clients to it.Everything is working fine.However sir i saw a strange behavior with respect to N/W.I was not able to ping one client called Store to the server called NICKS.the vice-versa was happening means the NICKS was able to ping the STORE fine and there was no loss of packet.I thought that this client wont be able to connect as it cant get through the network but it got connected and is working fine too.I mean to say i wasnt(i am not ) able to ping the Store client to the Nicks Server but still the scott/tiger@nicks815 is working very fine.How is this happening sir?is there someting that i dont know about Cleint/Server connectivity of Oracle?
Please explain
with warm regards
aman
March 18, 2005 - 10:08 am UTC
firewalls commonly block ICMP messages. perhaps your clients are using them.
LISTENER or NET8 or something else.
Aamir Abbas, March 30, 2005 - 8:29 am UTC
I'm installing Oracle 10g on Windows 2000 Advanced Server, P4, 512MB RAM....
Installation completed, and during database configuration, it ended with a series of following errors..
ORA-28547: connection to server failed, probable Net8 admin error
ORA-01041: internal error. hostdef extension doesn't exist.
Services of Instance and TNSListener are running...
I deleted the incomplete database config using Database confignuration Assistant and tried to create a new database.. It gave the following error and couldn't continue.
ORA-28547: connection to server failed, probable Net8 admin error
================
My LISTENER.ORA:
================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\Oracle\Ora10g\local)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = aabbas.tsdz.dns.com)(PORT = 1521))
)
)
)
================
My TNSNAMES.ORA
================
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
===============
My LISTENER.LOG
===============
Started with pid=2100
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aabbas.tsdz.dns.com)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
30-MAR-2005 10:08:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=aabbas))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821248)) * status * 0
===============
My SQLNET.LOG
===============
Fatal NI connect error 12631, connecting to:
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
VERSION INFORMATION:
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.2.0 - Production
Time: 30-MAR-2005 17:47:29
Tracing not turned on.
Tns error struct:
ns main err code: 12631
TNS-12631: Username retrieval failed
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Am I doing something wrong or is it a bug?
Your help and guidance would be highly helpful.
Thanks in advance.
March 30, 2005 - 9:56 am UTC
please contact support for configuration and installation assistance.
LISTENER or NET8 or something else.
Aamir Abbas, March 30, 2005 - 11:59 pm UTC
I'm an individual and have installed Oracle 10g (10.1.0.2) for learning and experiencing purpose. I don't have any support access.
A patch (10.1.0.3) is available in patchset but again I don't have access to MetaLink.
Why isn't MetaLink available to public? Is there any way to get the patch?
Best Regards.
March 31, 2005 - 7:35 am UTC
the free software is what it is. in order to get support and access to the support site, you need to have support. I cannot answer "why", only tell you what is.
compatibility of listener?
Dawar, March 31, 2005 - 7:29 pm UTC
Is it possible that listener can shutdown by itself?
If yes, what can be the reasons?
Is there any way to do performance testing or to check compatibility of listener?
Other than lsrnctl start, lsrnctl status .
Regards,
Dawar
March 31, 2005 - 8:06 pm UTC
not normally, if the listener disappears, that would be a product "issue". Support would be called for.
lsnrctl is the way to interface with and interact with the listener.
Listener
Anupam, April 11, 2005 - 6:41 am UTC
We are using Oracle 9i(9.2) server in Win2003 OS. It is running in dedicated server mode. Application server is IBM Websphere.
My problem is :
1) Listener automatically stops frequently, some times got the message "Listener couldn't start the dedicated server process", sometimes "There is NO LISTENER".
2) Oracle Management Server is configured. OMS also getting down automatically.
Please suggest me why this problem occurs frequently.
April 11, 2005 - 9:05 am UTC
Please contact support.
Was this response helpful to you?
Ashutosh, April 11, 2005 - 12:54 pm UTC
Was this response helpful? *Very*. One DOES learn a lot by going through your responses. An highly commendable job! Oh by the way Tom, you seem to have the patience of a saint!
port#
Samuel, April 23, 2005 - 2:57 pm UTC
My understanding is that when the listener spawns a server process for a user session, the listener assigns a port number for the server process and sends the port number to the user process to connect directly to the server process. Is this a fair understanding? If so, how many "unused" ports are there on the box to use? If I have a hundred thousand user session connecting at the same time, does oracle assign hundred thousand different ports? Thanks.
April 23, 2005 - 3:54 pm UTC
what normally happens is:
a) you connect to the listener over a connected tcp ip socket on port 1521 (you pick the port)
b) the listener spawns (forks and execs in unix land) a child process, it inherits this connection
c) the dedicated server you connect to uses that connection. the "port" is all 1521
Now different configurations -- like windows without shared sockets did a redirect trick and there are 32k or 64k "ports" that can be used.
You won't have 100,000 inbound tcp-ip connections simultaneously, you would be using advanced networking features way before you ever got close to that -- shared server would be set up, CMAN (connection manager) probably too, connection pooling and/or concentration and so on.
TNS
Alex, April 29, 2005 - 3:56 pm UTC
TNS = Transparent Networking Substrat. What the heck is "Substrat"?
April 29, 2005 - 6:43 pm UTC
sub·strate Audio pronunciation of "substrate" ( P ) Pronunciation Key (sbstrt)
n.
1. The material or substance on which an enzyme acts.
2. Biology. A surface on which an organism grows or is attached.
3. An underlying layer; a substratum.
4. Linguistics. An indigenous language that contributes features to the language of an invading people who impose their language on the indigenous population.
listener
abc, May 06, 2005 - 1:55 am UTC
Hi Tom,
Its really very serious matter ( you can say questio of my job)
I created 2 databases 8.1.7.4 in SUN OS and HP UX and upgraded to 10.1.0.2.
Now these database service name get register with oracle default listeners.
SUN OS and HP UX both have 8i and 9i instances too.
8i Listener in SUN os getting down again and again ( there is one databse of apps)
Now my colleauge is raising isssues that " listener in sun os was getting down because
I didnt mention local_listener parameter in 10g init file.")
I didnt add any tnsnames and listener enteries
HP UX is working very fine.
Tar
UPDATE
========
by default 8i and higher databases will try to register with any listener on port 1521.
9i instances registering with a 8i listner can crash it
as well as 32 bit and 64 bit version cross registering. to make sure that no dat
abase are registering with the listner make sure that ALL database have the loca
l_listner parameter set in the init.ora for each instance and point to the corre
ct listener for that version.
local_listener="(address=(protocol=tcp)(host=<hostname>)(port=1525))"
May 06, 2005 - 7:37 am UTC
I don't see a "question" here?
but you should always use the listener of the highest release if you are using a listener for multiple versions.
listener fails on connection every time
anurag, July 16, 2005 - 1:09 am UTC
Hi Tom
I am having oracle9i on windows as 24x7. Well, it was running perfectly. Suddenly, I got a complaint from users, that they were unable to connect. I checked and found listener was not running. I started, and tested connection, found "memory reference error 'some hexadedcimal codes' with no listener message" at application level; checked listener status again - found stopped. Can you help me to solve this problem.
regards
Anurag
July 16, 2005 - 9:17 am UTC
please contact support (but I'd be tempted to reboot windows first).
more information for previous connection
anurag, July 16, 2005 - 8:45 am UTC
Dear Tom,
Please find more information about previous question. It says, Ora-12500 Listener is unable to start dedicated process. Server capacity is 1 GB RAM, Dual CPU and Database size is 10 GB with GIS data. Mostly used by ARCVIEW software and very minimum transactions, only queries are performed. I got some hint- that my server is lacking of resources. But, when I stopped all other services, and has shut the web-server to disallow request from outside. Its again giving same error with single session too. I've restarted database also,and even created new listener on different port. It again generate same error, the moment connection request is made. We are using local naming method with TNSNames.ora. Please provide some useful clue.
thanks,
Anurag
July 16, 2005 - 9:37 am UTC
sounds like windows has plug run out of memory on your system. I don't know how many users you have, but they all have to fit in less than 2 gig of ram unless you bend over backwards and contort your system in funny ways with 32 bit windows.
Fatal NI connect error 12545
dost, October 05, 2005 - 12:57 pm UTC
Tom,
Can you please suggest on following why it is happening?
Fatal NI connect error 12545, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/oracle/product/9.2.0/bin/oracle)(ARGV0=oracleDPRD)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DET
ACH=NO))(CONNECT_DATA=(CID=(PROGRAM=)(HOST=scdwdb01)(USER=brioods))))
VERSION INFORMATION:
TNS for Solaris: Version 9.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 9.2.0.1.0 - Production
Time: 04-OCT-2005 08:19:44
Tracing not turned on.
Tns error struct:
nr err code: 0
ns main err code: 12545
TNS-12545: Connect failed because target host or object does not exist
ns secondary err code: 12560
nt main err code: 515
TNS-00515: Connect failed because target host or object does not exist
nt secondary err code: 2
nt OS err code: 0
October 05, 2005 - 1:44 pm UTC
that generally means "dns lookup failed for scdwdb01"
[tkyte@dellpe ~]$ oerr tns 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name. Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.
Not connected
Dawar, December 07, 2005 - 7:58 pm UTC
Hello Tom,
OS: AIX 5.2
DB: 10.1.0.4.0
Suddenly I am not able to use any oracle tool such as sqlplus, EM console, dbconsole url, lsrnctl stop/start/status.
But i can logon to sqlplus from command propmpt as
> sqlplus
oracle:>sqlplus
SQL*Plus: Release 10.1.0.4.0 - Production on Wed Dec 7 16:41:03 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
username/password
> sql
oracle:>lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production on 07-DEC-2005 16:36:54
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP ADDRESS)(PORT=1521)))
cheers,
DN
December 08, 2005 - 1:55 am UTC
looks like it worked to me?
review
sven, January 04, 2006 - 11:26 am UTC
Hi Tom,
Could you please elaborate your statement:
---
"hacker" doesn't need access to "server" to shutdown listener. "hacker" just
needs lsnrctl on their machine.
---
I am not shure I understant it.
Thanks.
sven
January 04, 2006 - 12:11 pm UTC
If you leave your listener unprotected (no passwords, allow for remote control of it), all "hacker" needs is a copy of lsnrctl on their machine - they don't need to use "your" lsnrctl. Having access to lsnrctl is OK - I get can one from otn anytime I want.
run root.sh on patch installation
Dawar, May 01, 2006 - 10:15 pm UTC
Tom,
Oracle DB Version 10.1.0.4.0
Applying Patch for 10.1.0.5.0
OS: Unix
I am appliing patch 10.1.0.5.0 on my DB.
At one stage of the installation of the new patch Universal Installer prompt message to run root.sh as a root user and then click on continue to continue.
When we run root.sh
message prompt as below:
/usr/local/bin already exists do you want to replaceY/N: ??
/oranenv is alreday exists do you want to replace Y/N:>??
/dbhome is alreday exists do you want to replace Y/N:> ??
what should I give answers to above questions?
Y or N?
Dawar
May 02, 2006 - 3:41 am UTC
did you want to replace them or not? use that answer.
I generally do not use oraenv or dbhome or any of those, so I always answer "no".
Listener hangs up
Dawar, June 14, 2006 - 6:25 pm UTC
OS AIX/IBM : 5.2
DB: 10.1..0.4.0
Hello Tom,
We are running third part application on our database.
Suddenly all users session hang up.
Listener was also hangup.
I tried to see status,start, stop and tnsping but no ouput back.
But suddenly my other DBA informed that its up again.
*******************************************************************
oracle:>lsnrctl status
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production on 14-JUN-2006 14:14:30
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
IBM/AIX RISC System/6000 Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP: ADDRESS)(PORT=1521)))
**************************************************************************************************************************
oracle:>lsnrctl start
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production on 14-JUN-2006 14:24:21
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Starting /u01/ora10g/ABC/db/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production
System parameter file is /u01/ora10g/ABC/db/network/admin/listener.ora
Log messages written to /u01/ora10g/ABC/db/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP: ADDRESS)(PORT=1521)))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
IBM/AIX RISC System/6000 Error: 67: Address already in use
Listener failed to start. See the error message(s) above...
****************************************************************************************************************************
oracle:>lsnrctl stop
LSNRCTL for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production on 14-JUN-2006 14:23:57
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
IBM/AIX RISC System/6000 Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=IP: ADDRESS)(PORT=1521)))
******************************************************************************************************************
oracle:>tnsping sid
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.1.0.4.0 - Production on 14-JUN-2006 14:15:04
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
/u01/ora10g/ABC/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = IP: ADDRESS)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sid)))
Please give me your feed back. thanks
Regards,
Dawar
California, USA
June 15, 2006 - 8:28 am UTC
please utilize support for something like this. You have a configuration issue (you were very much likely using the wrong listener.ora file OR you have a named listener in there and you were trying to use the default listener)
listener on local db
A reader, September 29, 2006 - 2:48 pm UTC
I can connect to my local db as system/password, but I cannot connect as system/password@orcl
I get the ORA-12541: TNS:no listener error
Can you please teach the basics behind the above.
September 30, 2006 - 7:58 am UTC
This would be the DBA's job, to set this up.
you don't have a listener configured - configure listener.
home database
A reader, October 01, 2006 - 2:03 pm UTC
Tom
I dont have a DBA at home :) , this is for the oracle DB on my home computer. Can you help with the listener.ora?
Thanks in advance
October 01, 2006 - 5:23 pm UTC
well, given that I know not your os or anything - and I'm not really "dba for home", I'll gladly point you to the documentation
the listner.ora should have been setup when you installed Oracle XE for you already? if you are just trying to learn, I'd suggest:
</code>
http://otn.oracle.com/xe <code>
they have lots of "2 day" docs there that will get you "started"...
Multiple Listeners?
A reader, October 01, 2006 - 6:44 pm UTC
Hi Tom,
In an enviornment, where there are multiple databases, can we have multiple listeners? one for each DB, this will help us to avoid front end users to log in during the maintenance of specific Database.
Regards
October 02, 2006 - 7:51 am UTC
but a listener doesn't care of a database is
a) up
b) down
you only need one. It can service them all.
And you do know the only correct number of instances on a machine running in production is precisely "one"
You really don't want more than one per machine.
ENVS and GLOBAL_USERNAME parameter
Dawar, November 18, 2006 - 12:25 pm UTC
Can we add ENVS and GLOBAL_USERNAME parameters to SID_DESC section of listener.ora?
Could you tell me the list of parameters,
we can add to the SID_DESC section of listener.ora?
cheers,
Dawar
listnere configuration
Dawar Naqvi, December 05, 2006 - 12:15 pm UTC
OS: AIX
DB: 10.2
Tom,
we have a Database running without tnsnmaes.ora & listener.ora files.
This database was setup by consultant.
Who left the company.
Some how we were able to access DB remotely but not any more.
Now I want to setup listener so we can access database remotely.
* Do I need to create tnsnames.ora file prior to create listener.ora file?
* I am thinking to use netca utility to setup listener. what protocol should be TCP or IPC?
* Which option should I select from the first screen of netca?
listnere configuration
Naming Methods configuration
Local Net service Name configuration
Directory Usage Configuration
cheers,
Dawar
December 05, 2006 - 10:15 pm UTC
tnsnames are for CLIENTS - server, doesn't need one
listener, only needed if you want to change defaults.
so, is you listener RUNNING?
missing listener !
ian gallacher, December 06, 2006 - 6:22 am UTC
Hi
Set up 10g2 without creating database at installation
Created database via dbca
Accessed Database ok via std sql but couldn’t access Forms client or Forms web – no listener ! - first time seen this effect !
Needed to create listener manully and start, accessing from Forms fine
Sample listener in network/admin for HS connections as well
# 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)
)
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = ADD_DR)
(PROGRAM = hsodbc)
)
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = EXTPAT)
(PROGRAM = hsodbc)
)
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = TOMCAT)
(PROGRAM = hsodbc)
)
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = MDPAT)
(PROGRAM = hsodbc)
)
(SID_DESC =
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = DCS)
(PROGRAM = hsodbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = IANSDEV)(PORT = 1522))
)
)
DEFAULT_SERVICE_LISTENER = (ORCL)
Once started should pick up instance
Corresponding tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = IANSDEV)(PORT = 1522))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IANSDEV)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
M =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IANSDEV)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Hope this helps
Ian
December 07, 2006 - 8:34 am UTC
umm, did you start a listener.
creating a database does NOT A LISTENER START.
listerner and create database
ian gallacher, December 07, 2006 - 11:21 am UTC
Hi Tom,
Just to clarify
Install 10g2 – ticking “Create Database” automatically creates appropriate listener and tnsnames for <your instance>
Can then run sqlplus user/password or sqlplus user/password@<your instance>
However, if install 10g2 – software only – no listener or tnsnames setup as may be expected BUT when use dbca to create <your instance>, listener and tnsnames are not automatically setup !
Thus Can then run sqlplus user/password BUT cant run sqlplus user/password@<your instance>
Need to use netca to set up listener and tnsnames or manually copy from existing ones !
Not a problem but just a note on difference between create database at 10G install time and later on via dbca
Ian
PS are you replying to New Ask Tom "bugs" or queries!
December 07, 2006 - 1:19 pm UTC
the new ask tom beta thing is a beta thing, Marco (the developer) is responding to issues filed on the sticky thread on the home page.
Dawar, March 10, 2007 - 1:24 pm UTC
tom,
OS: AIX 5.2
DB: 10.2.0.3.0
When I installed Oracle DB, some how port 1523 was installed instead of port 1521.
From the client by using port 1523, I can connect to the databse.
But soon I cahnged port to 1521.
I got following error when I tryied to connect to the DB.
"SID given in connect descriptor could not be resolved."
Here are the steps accomplish before change port from 1523 to 1521.
$ emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
http://hostname.domainname:1158/em/console/aboutApplication Oracle Enterprise Manager 10g is not running.
$ isqlplusctl stop
iSQL*Plus 10.2.0.3.0
Copyright (c) 2003, 2006, Oracle. All Rights Reserved.
iSQL*Plus instance on port 5560 is not running ...
*******************************************************************************************************************
$ lsnrctl stop
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 10-MAR-2007 08:37:34
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC3)))
The command completed successfully
$
**********************************************
Then I made changes in tnsnames.ora & listener.ora files. (change port to 1521).
Start listener as below:
$ lsnrctl start
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production on 10-MAR-2007 08:40:32
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Starting ORACLE_HOME/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
System parameter file is ORACLE_HOME/network/admin/listener.ora
Log messages written to ORACLE_HOME/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC3)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sid.domainname)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC3)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Production
Start Date 10-MAR-2007 08:40:32
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File ORACLE_HOME/network/admin/listener.ora
Listener Log File ORACLE_HOME/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC3)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sid.domainname)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
$
*************************************************
But when I tried to connect to DB from the client I got error as below:
"SID given in connect descriptor could not be resolved."
But if I put back port # to 1523 ,
I can connect to the database from the client.
March 12, 2007 - 5:55 pm UTC
you have local or remote_listener set in your init.ora and your databases are not registering with the listener on port 1521, they are trying on 1523 still.
hence when your clients asked for a registered database - that isn't regiestered - they get that message.
Very Helpful
satyanarayana reddy, April 12, 2007 - 6:02 pm UTC
Very Interesting discussion on listener
Listener error
Anne, September 27, 2007 - 5:16 pm UTC
Hi Tom,
We are running 11i (11.5.9) on a 9.2.0.6 RDBMS.
We are planning for a server move and would like to minimize the host name changes wherever possible - looks like we have several Jdbc connections where the host will have to change. Just figuring out if there is a way to do this once and for all, and then any future changes should be seamless...
The server name is Itchy and there is only one database listener for the 11i database instance wiresprd. In the DNS entry, we have "Itchy" registered as an A-record and "wires" registered as an alias to Itchy - so basically just one ip address). Users login from url "wires.dnr.state....:8000/....jsp" and after login it translates itself to "itchy...."
If we could set the jdbc connection to the database to connect as "wires.dnr.state.mn.us" instead of "itchy.dnr.state.mn.us", then the change would be seamless.
For this, I was trying to sqlplus using "wires" as the host, but I got the error :
ORA-12541: TNS:no listener
My current tnsnames.ora entry is (HOST=itchy) WIRESPRD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = itchy)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wiresprd)
(SERVER = dedicated)
)
)
I can connect now :
idle> @connect apps@wiresprd
Enter password: ******
Connected.
apps@wiresprd.itchy>
If I change my tnsnames entry (HOST = wires) :WIRESPRD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = tcp)(HOST = wires)(PORT = 1521))
)
(CONNECT_DATA =
(SID = wiresprd)
(SERVER = dedicated)
)
)
Now I cannot connect :
idle> @connect apps@wiresprd
Enter password: ******
Connected.
apps@wiresprd.itchy> @connect apps@wiresprd
Enter password: ******
ERROR:
<b>ORA-12541: TNS:no listener</b>
listener.ora on server :wiresprd =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCwiresprd))
(ADDRESS= (PROTOCOL= TCP)(Host= itchy.dnr.state.mn.us )(Port= 1521))
)
lsnrctl stat:LSNRCTL for Linux: Version 9.2.0.6.0 - Production on 27-SEP-2007 16:00:25
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias wiresprd
Version TNSLSNR for Linux: Version 9.2.0.6.0 - Production
Start Date 23-SEP-2007 14:10:46
Uptime 4 days 1 hr. 49 min. 39 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /r04/oracle/wiresprddb/9.2.0/network/admin/wiresprd_itchy/listener.ora
Listener Log File /r04/oracle/wiresprddb/9.2.0/network/admin/wiresprd.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCwiresprd)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=itchy)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "wiresprd" has 1 instance(s).
Instance "wiresprd", status UNKNOWN, has 1 handler(s) for this service...
Service "wiresprd.world" has 1 instance(s).
Instance "wiresprd", status READY, has 3 handler(s) for this service...
The command completed successfully
tnsnames.ora on server:wiresprd=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ITCHY.dnr.state.mn.us)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=wiresprd.world)
(INSTANCE_NAME=wiresprd)
)
)
wiresprd=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=ITCHY.dnr.state.mn.us)(PORT=1521))
)
Could you please advise on how we can accomplish this ? I would really appreciate any help. Thanks so much!
September 28, 2007 - 5:02 pm UTC
i'm somewhat confused.
can you ping itchy and wires?
can you show us the ping
and can you show that there is a listener running on both the host known as itchy and the host known as wires, which I presume should be resolving to the same ip address.
Listener
Anne, October 09, 2007 - 5:17 pm UTC
Hi Tom,
I got cought up with stuff and totally forgot to get back to you. So Sorry about that.
There was a mistake in what I had sent to you in the statement "In the DNS entry, we have "Itchy" registered as an A-record and "wires" registered as an alias to Itchy - so basically just one ip address)."
What it should have been was "In the DNS entry, we have "Itchy" registered as an A-record and "wires" registered as an alias to itchy1 which is our Apps server
. Users login from url "wires.dnr.state....:8000/....jsp" and after login it translates itself to "itchy1...."
So of course, since the database is NOT on the apps server, it won't work - there is no listener!
My mistake - sorry for wasting your time.
Thanks!
Listener.ora - different configuration method
Alex, November 26, 2007 - 5:55 pm UTC
Tom
Can you please tell me if there is any difference between the following 2 methods of specifying the listener.ora file ?
I had been using Listener.ora method B for quite a while on one of my Oracle databases, when suddenly one Monday morning, the listener failed to connect requests to the database.
I then switched to using Listener.ora method A, and uncomment the one line in the init.ora file, restart database and it works.
What, in your opinion, could have happened on the network that all of a sudden caused Listener.ora method B to fail ?
Listener.ora A
--------------
LISTENER = (ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=hostsrv3)
(PORT=1592)))
SID_LIST_LISTENER = (SID_LIST=
(SID_DESC=(SID_NAME=ARONDW)(ORACLE_HOME=D:\oracle\product\10.2.0\db_1))
)
Listener.ora B
--------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostsrv3)(PORT = 1592))
)
)
init.ora
--------
*.local_listener='LISTENER_ARONDW'
November 27, 2007 - 3:11 pm UTC
entirely insufficient data to make a comment about anything.
what if you HADN'T changed a thing - would it have worked again (yes, likely)
the first approach is a static registration (needed if you want to remotely startup this database)
the second is dynamic registration - preferred if you don't have the need to remotely startup.
Listener not starting
Milind Shinde, March 18, 2008 - 1:22 am UTC
Hi Tom,
The problem i faced during logging into the Toad.it showed error like no listener.
so when i checked the listener status.
it showed the result something like below
hpit1[ECMUAT]/oracle/data1: lsnrctl stat
LSNRCTL for HPUX: Version 9.2.0.5.0 - Production on 18-MAR-2008 15:57:46
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=146.62.33.169)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for HPUX: Version 9.2.0.5.0 - Production
Start Date 03-MAR-2008 09:57:11
Uptime 15 days 6 hr. 0 min. 35 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /oracle/920_64/app/oracle/product/9.2.0.2/network/admin/listener.ora
Listener Log File /oracle/920_64/app/oracle/product/9.2.0.2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=146.62.33.169)(PORT=1521)))
Services Summary...
Service "ASBASE" has 1 instance(s).
Instance "ASBASE", status UNKNOWN, has 1 handler(s) for this service...
Service "ECMTRN" has 1 instance(s).
Instance "ECMTRN", status UNKNOWN, has 1 handler(s) for this service...
Service "ECMUAT" has 1 instance(s).
Instance "ECMUAT", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
but when i try to start the listener it gives error like below.
LSNRCTL> start
Starting /oracle/920_64/app/oracle/product/9.2.0.2/bin/tnslsnr: please wait...
TNSLSNR for HPUX: Version 9.2.0.5.0 - Production
System parameter file is /oracle/920_64/app/oracle/product/9.2.0.2/network/admin/listener.ora
Log messages written to /oracle/920_64/app/oracle/product/9.2.0.2/network/log/listener.log
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=146.62.33.169)(PORT=1521)))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
HPUX Error: 226: Address already in use
Listener failed to start. See the error message(s) above...
Please let me know as it is urgent and i am not able to connect to the toad.
March 24, 2008 - 9:10 am UTC
umm, well the listener won't start the second time because it is already started.
so, don't try to start it again - much like a car, if you start it when already started - it makes a really nasty noise.
your tnsnames.ora entry on the client hosting TOAD is the ultimate culprit here - try it with sqlplus on the same machine and it will fail as well - you need to fix that up.
Listener not starting up
Milind Shinde, March 25, 2008 - 12:03 am UTC
Thanks Tom,
yes i checked it up and as you have suggested i got sqlplus too not connecting on client machine and gives TNS ORA-12560 error.Though i am some what confuse and not getting what to do fix this issue and to connect my toad to the particular instance on remote server.
Regards,
Milind
March 25, 2008 - 6:39 am UTC
your tns entry is wrong apparently, but you give no information about it.
your tnsnames.ora should have an entry that uses (HOST=146.62.33.169)(PORT=1521) and one of the services listed above in your status.
I don't know why you keep writing "listener not starting up", you started by proving the listener is running!
hpit1[ECMUAT]/oracle/data1: lsnrctl stat
LSNRCTL for HPUX: Version 9.2.0.5.0 - Production on 18-MAR-2008 15:57:46
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=146.62.33.169)(PORT=1521)))
STATUS of the LISTENER
your listener is - well - obviously started, running, servicing requests.
it fails to start the SECOND TIME - for obvious reasons (you cannot start something that is already started)
LISTENER
Himanshu, October 01, 2009 - 3:07 am UTC
Hi, TOM,
We have recently upgraded our database from 9i to 10G.The O/S that we are using is Solaris 10.What I have observed in 10G , stopping the listener takes more time compare to that of 9i. Is it usual behavior in 10G or is there specific problem with the configuration. If it is a specific problem then How can I correct that problem ?
October 07, 2009 - 9:05 am UTC
likely - you are having long dns/hostname lookups.
Is that the case? else it should the same.
Connection only through listener
A reader, November 27, 2009 - 5:49 am UTC
Hi Tom,
I was asked to allow connection to the database only through listner.
I thought about using logon trigger.
How can i find out which session are trying to connect not through the listener to the database ?
Thanks
November 28, 2009 - 1:26 pm UTC
why not restrict access to the OS? No one should really be logging into the server - and if they are able, they should not have the ability to access the oracle account (so they cannot connect directly as the software by default doesn't have the permissions for them to do so).
That is, fix the problem - people should not be logging onto the database server itself and if they have the ability to log in as the Oracle account - you wont be able to stop them from doing anything anyway...
A reader, November 28, 2009 - 3:07 pm UTC
Hi Tom,
"why not restrict access to the OS?" ==>
For a PCI regulations i need also to restric my DBAs.
From one hand i need the allow them to do there jobs and from the other hand , i need to prevent them from seeing sevsitive data.
There are few 3rd party products that allow masking sensitive information on-the-fly . They catched every thing that goes through the LISTENER.
But if the DBA connect from the server he/she can see all the sensitive information...
I need to know if it possible to verify if the session did not connect to the database using LISTENER .
Thanks Again
November 28, 2009 - 4:19 pm UTC
did you know that a DBA cannot 'fail' a logon trigger, if they have DBA - they get in regardless of whether a trigger 'fails' or not.
Audit - auditing would tell you how people connect, beat them soundly about the head if they violate the terms.
there are free features in the database that do what you want already - it is called fine grained access control.
You have not thought this through all of the way, if you have a DBA, and they have access to the machine - the probably have sysdba and with that - all bets are off
forgetting for a moment that just being a dba would allow them to get around your logon trigger.
fogetting for a moment that a creative dba would just setup their own listener if it didn't let them get around it.
that "filter by intercepting the network" - geez - if you were really wanting to be PCI compliant - you would be encrypting network traffic - else, every thing is in clear text.
You have miles and miles to go before you become anywhere near PCI - and stopping the DBA's from seeing stuff is the least of your problems right now I think.
You have to secure the machine, limit access. (what is to stop a dba from stealing your database otherwise?)
You have to encrypt the sensitive information on disk and protect it from prying eyes on the network.
You would use fine grained access control for most filtering
and if you needed it - database vault to limit the realm of data the dba's can see.
but push comes to shove, if the dba has access to the OS the database is on and the oracle account that owns it all - all bets off on anything you try to do, they are "in"
ops$tkyte%ORA10GR2> edit
Wrote file afiedt.buf
1 create or replace trigger fail_the_logon after logon on database
2 begin
3 raise_application_error( -20000, 'you have to use the network' );
4* end;
ops$tkyte%ORA10GR2> /
Trigger created.
ops$tkyte%ORA10GR2> connect scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: you have to use the network
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> revoke dba from ops$tkyte;
Revoke succeeded.
sys%ORA10GR2> connect /
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: you have to use the network
ORA-06512: at line 2
Warning: You are no longer connected to ORACLE.
sys%ORA10GR2> connect / as sysdba
Connected.
sys%ORA10GR2> grant dba to ops$tkyte;
Grant succeeded.
sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> drop trigger fail_the_logon;
Trigger dropped.
so, in short, my original answer definitely stands - and the logon trigger - just ignore it, it won't do anything for you