Your reply gave me a way to force all connections from the
same box as the Oracle server is on, to use IPC. That is not
what I was asking for. That is why I came back to the newsgroup
with a differently-worded question.
I am seeking a way to have a choice of TCP or IPC when
I use SQL*Plus on the same box. Once automatic_ipc is
turned on, I lose that choice, and the only way to get
TCP is from a remote connection. If I could
discriminate
the protocol by use of an alias when I invoke SQL*Plus
from the command line, then I would have a stable environment
with two ways of connecting locally to the same database.
So again I ask whether it is possible without swapping out
config files and restarting the listener. If I can get it to work,
I want to show
- use of Java thin vs. thick drivers
- how much overhead TCP adds to communication vs. IPC
without any other intervening variables like network
connections, transit time etc. which could color
the results.
Some of the docs tell you that at 8.1.5 you should start the
listener before the database. I want to faithfully follow that
policy, although I would like a deeper understanding of the
reason for it. That means a lot of bouncing the
Oracle server that I'd prefer not to do.
I apologize for seeming dense, I sent email to you explaining
more details about trying to get the choice without having
to leave the physical machine or using listener/tnsnames files
in alternation.
Chris O'Connor
In article <940662677.19305.0.pluto.d4ee154e_at_news.demon.nl>,
"Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> Sorry to say so, but my directions in my previous reply where quite
> precise -works in 99 percent of the cases- but you seem to have
> misunderstood them or at least not followed them all. Anyway, now both
your
> tnsnames.ora and your listener.ora is in error.
> The correct listener.ora is
> LISTENER =
> (DESCRIPTION_LIST =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = IPC) (KEY = DIALTEST))
> # changed to existing SID, it must be a SID, ie an instance identifier
> (ADDRESS = (PROTOCOL = TCP)(HOST = pebble.bd.ans.net)(PORT =
> 1521))
> )
> )
> )
>
> # put a SID_DESC here for IPC as well as one for TCP
> #NO! the protocol stuff and the sid stuff at the listener sid is
completely
> unrelated.
> #If you define a protocol, automatically any database on that server
should
> be automatically capable of #using it
> #the choice is yours, via tnsnames.ora, with the exception of IPC
> # The first DESC is the TCP one
>
> SID_LIST_LISTENER =
> (SID_LIST =
> (SID_DESC =
> (GLOBAL_DB_NAME = DIALTEST)
> (ORACLE_HOME = /ans2/oracle/u01/app/oracle/product/8.1.5)
> (SID_NAME = DIALTEST )
> )
>
> #This is NOT an existing SID it should be removed
> #> (SID_DESC =
> #> (SID_NAME = DIALIPC)
> #> (GLOBAL_DB_NAME = DIALTEST)
> #> (ORACLE_HOME = /ans2/oracle/u01/app/oracle/product/8.1.5)
> #> )
> )
> You don't need to have references to IPC in tnsnames.ora. Any
connection on
> the server, and on the server ONLY, should be converted to IPC
automatically
> with AUTOMATIC_IPC.
> The tnsping utility ONLY test existence.
> You're most likely getting ORA-1034 now because of the incorrect KEY
in the
> IPC section of your listener.ora.
> I'm sorry if this all sounds a bit annoyed, we were quite close to a
> solution.
>
> Regards,
>
> --
> Sybrand Bakker, Oracle DBA
> <chrisoc_at_ans.net> wrote in message news:7uqd69$bvj$1_at_nnrp1.deja.com...
> >
> >
> > Sorry this is long. I have ideas from Tom Kyte's posts and
> > Sybrand Bakker's, and wonder if maybe I am trying something
> > that is not possible.
> >
> > I'm close to a solution but it still eludes me. I am trying
> > to make two aliases for one database instance (the real
> > SID is DIALTEST). The first alias I want is DIALIPC, for use on
> > the same machine as DIALTEST, to be deployed like this:
> >
> >
> > sqlplus system/manager_at_DIALIPC
> >
> > and another alias, DIALTEST, that will connect over TCP either
> > from the same box or a different one, as follows:
> > sqlplus system/manager_at_DIALTEST
> >
> > I only have one running Oracle database, DIALTEST. If I configure
> > for TCP only, it is connectable from everyplace. I'm
> > trying to control the access protocol by using
> > DIALIPC to make the listener look for IPC, and DIALTEST
> > to make it look for TCP. I'm not running ONAMES and not
> > looking for external procs to work (yet). I want to
> > to control my access protocol by means of which alias I use,
> > from the same machine.
> >
> >
> > I have both aliases TNS-pingable now. But I cannot use them
> > in the sqlplus command to get this control.
> > Furthermore, the lsnrctl status output is not showing the
> > TCP listener so I think I have a problem, there.
> >
> > I should be seeing both TCP and ICP when I start my listener.
> >
> > Here are the files and log snips. What am I doing wrong?
> >
> > I have not set the global_names to True in my initdialtest.ora.
> > It's commented out.
> >
> >
> > --- tnsnames.ora:
> > DIALTEST =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS =
> > (PROTOCOL = TCP ) (HOST = pebble.bd.ans.net ) (Port = 1521 )
> > )
> > )
> > (CONNECT_DATA =
> > (SID = DIALTEST)
> > (GLOBAL_NAME = DIALTEST)
> > )
> > )
> >
> > DIALIPC_CONNECTION_DATA=
> > (DESCRIPTION =
> > (ADDRESS= (PROTOCOL=IPC)(KEY = DIALIPC)
> > )
> > (CONNECT_DATA = (SID=DIALIPC)
> > )
> > )
> >
> > #TRY THIS
> > DIALIPC=
> > (DESCRIPTION =
> > (ADDRESS= (PROTOCOL=IPC)(KEY = DIALIPC)
> > )
> > (CONNECT_DATA = (SID=DIALIPC)
> > (GLOBAL_NAME = DIALIPC)
> > )
> > )
> >
> >
> > --- listener.ora
> >
> > LISTENER =
> > (DESCRIPTION_LIST =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = IPC) (KEY = DIALIPC))
> > (ADDRESS = (PROTOCOL = TCP)(HOST = pebble.bd.ans.net)(PORT =
> > 1521))
> > )
> > )
> > )
> >
> > # put a SID_DESC here for IPC as well as one for TCP
> > # The first DESC is the TCP one
> >
> > SID_LIST_LISTENER =
> > (SID_LIST =
> > (SID_DESC =
> > (GLOBAL_DB_NAME = DIALTEST)
> > (ORACLE_HOME = /ans2/oracle/u01/app/oracle/product/8.1.5)
> > (SID_NAME = DIALTEST )
> > )
> > (SID_DESC =
> > (SID_NAME = DIALIPC)
> > (GLOBAL_DB_NAME = DIALTEST)
> > (ORACLE_HOME = /ans2/oracle/u01/app/oracle/product/8.1.5)
> > )
> > )
> >
> >
> >
> >
> > -- from the sqlnet.ora file (thanks to Sybrand Bakker)
> > automatic_ipc= ON
> >
> > -- lsnrctl status shows this:
> >
> > LSNRCTL for Solaris: Version 8.1.5.0.0 - Production on 22-OCT-99
> > 18:03:52
> >
> > (c) Copyright 1998 Oracle Corporation. All rights reserved.
> >
> > Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=DIALIPC)))
> > STATUS of the LISTENER
> > ------------------------
> > Alias listener
> > Version TNSLSNR for Solaris: Version 8.1.5.0.0 -
> > Production
> > Start Date 22-OCT-99 16:08:34
> > Uptime 0 days 1 hr. 55 min. 17 sec
> > Trace Level off
> > Security OFF
> > SNMP OFF
> > Listener Parameter File
> > /ans2/oracle/u01/app/oracle/product/8.1.5/network/admin/listener.ora
> > Listener Log File
> > /ans2/oracle/u01/app/oracle/product/8.1.5/network/log/listener.log
> > Services Summary...
> > DIALIPC has 1 service handler(s)
> > DIALTEST has 3 service handler(s)
> >
> >
> >
> > my "env" has these two settings:
> > ORACLE_SID=dialtest
> > ORACLE_HOME=/ans2/oracle/u01/app/oracle/product/8.1.5
> >
> >
> > tnsping shows this:
> > $ tnsping dialtest
> >
> > TNS Ping Utility for Solaris: Version 8.1.5.0.0 - Production on
> > 22-OCT-99 18:06:01
> >
> > (c) Copyright 1997 Oracle Corporation. All rights reserved.
> >
> > Attempting to contact
> > (ADDRESS=(PROTOCOL=TCP)(HOST=pebble.bd.ans.net)(Port=1521))
> > OK (20 msec)
> >
> > $ tnsping dialipc
> >
> > TNS Ping Utility for Solaris: Version 8.1.5.0.0 - Production on
> > 22-OCT-99 18:06:23
> >
> > (c) Copyright 1997 Oracle Corporation. All rights reserved.
> >
> > Attempting to contact (ADDRESS=(PROTOCOL=IPC)(KEY=DIALIPC))
> > OK (10 msec)
> >
> >
> > Now I can issue sqlplus without the "@DIAL"+something and get in,
> > as follows:
> > $ sqlplus
> >
> > SQL*Plus: Release 8.1.5.0.0 - Production on Fri Oct 22 18:07:29 1999
> >
> > (c) Copyright 1999 Oracle Corporation. All rights reserved.
> >
> > Enter user-name: system
> > Enter password:
> >
> > Connected to:
> > Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> > With the Partitioning and Java options
> > PL/SQL Release 8.1.5.0.0 - Production
> >
> > and the listener.log shows nothing new entered.
> >
> > Using connect string "system/manager_at_DIALTEST"
> > gives ORA-01034: ORACLE not available
> > and the listener.log shows:
> >
> > 22-OCT-99 18:11:19 *
> >
>
(CONNECT_DATA=(SID=DIALTEST)(GLOBAL_NAME=DIALTEST)(CID=(PROGRAM=)(HOST=pebbl
> e
> > .bd.ans.net)(USER=oracle))) *
> > (ADDRESS=(PROTOCOL=tcp)(HOST=147.225.48.50)(PORT=38052)) * establish
*
> > DIALTEST *0
> >
> >
> > Using the one for DIALIPC is similar:
> >
> >
> > ERROR:
> > ORA-01034: ORACLE not available
> >
> > and the listener log shows: 22-OCT-99 18:14:08 *
> >
>
(CONNECT_DATA=(SID=DIALIPC)(GLOBAL_NAME=DIALIPC)(CID=(PROGRAM=)(HOST=pebble.
> b
> > d.ans.net)(USER=oracle))) * (ADDRESS=(PROTOCOL=ipc)(KEY=DIALIPC)) *
> establish
> > * DIALIPC * 0
> >
> >
> >
> > Maybe I am tangled up in the SID v GLOBAL_NAMEs v ALIAS
distinctions.
> >
> > I've lost the ability to use TCP, somehow.
> > The setting of automatic_ipc to OFF or ON makes no difference.
> >
> > Chris O'Connor
> >
> >
> >
> >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Oct 26 1999 - 09:28:19 CDT
