Re: Connect w/o Listener

On Mon, 02 Oct 2006 20:14:28 +0200, Frank van Bortel <frank.van.bortel_at_gmail.com> wrote:

>Jaap W. van Dijk schreef:
>> On Fri, 29 Sep 2006 19:43:21 +0200, Frank van Bortel
>> <frank.van.bortel_at_gmail.com> wrote:
>>
>>> hberry schreef:
>>>> 10g
>>>>
>>>> How to connect to a local DB (localhost) without needing to start & go thru
>>>> the Listener ?
>>>>
>>>> thx
>>>>
>>>>
>>> Use a BEQUEATH connection:
>>> MY_ALIAS =
>>> (DESCRIPTION=
>>> (ADDRESS = (PROTOCOL = BEQ)(PROGRAM=oracle)
>>> (ARGV0=oracleORCL)
>>> (ARGS='(DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ)))')
>>> )
>>> (CONNECT_DATA=(SID=ORCL)
>>> )
>>> )
>>> More on http://vanbortel.blogspot.com/2006/03/network-blues.html
>>> --
>>> Regards,
>>> Frank van Bortel
>>>
>>> Top-posting is one way to shut me up...
>>
>> Hi Frank,
>>
>> Can you also use this solution to connect a 9.2.0.6 database to a
>> 8.1.4.7 database on the same server through a database link? And
>> because these are two databases with two different oracle executables,
>> do I have to specify things differently?
>>
>> And if you can, could this affect the runtime considerably of the
>> following statement:
>>
>> INSERT /*+ APPEND */ INTO local_table
>> SELECT * FROM remote_table_at_dblink
>>
>> I noticed that this statement uses a lot of CPU for processing the
>> records that come over the database link with a TCP-protocol. I wonder
>> if this is due to the nature of the TCP connection and if CPU
>> consumption would be noticebly lower for a BEQ-protocol.
>>
>> Kind regards, Jaap.
>>
>
>Well... you can on Windows/2000:
>
>D:\>net start oracleserviceRCV
>De OracleServicercv-service wordt gestart...........
>De OracleServicercv-service is gestart.
>
>D:\>set oracle_sid=rcv
>*** The 10g Client does not work...
>D:\>sqlplus "/ as sysdba"
>SQL*Plus: Release 10.1.0.4.0 - Production on Mon Oct 2 19:58:06 2006
>Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>ERROR:
>ORA-12560: TNS:protocol adapter error
>
>*** Good old Server Manager in Line mode does...
>D:\>d:\oracle\817\bin\svrmgrl
>Oracle Server Manager Release 3.1.7.0.0 - Production
>Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.
>
>Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
>With the Partitioning option
>JServer Release 8.1.7.4.1 - Production
>
>SVRMGR> connect internal
>Connected.
>
>*** Now for the network part:
>*** Where are all my config files?
>D:\>set tns_admin=D:\oracle\ora92\network\ADMIN
>*** Add jaap as tns alias:
>D:\>notepad D:\oracle\ora92\network\ADMIN\tnsnames.ora
>*** Use the 8i tns ping to prove it's working...
>D:\>d:\oracle\817\bin\tnsping jaap
>TNS Ping Utility for 32-bit Windows: Version 8.1.7.4.0 - Production on
>02-OCT-2006 20:03:14
>(c) Copyright 1997 Oracle Corporation. All rights reserved.
>
>Attempting to contact
>(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracledb920)(ARGS='(DESCRIPTION=(LOCAL=yes)
>(ADDRESS=(PROTOCOL=BQ)))'))
>TNS-12560: TNS:protocol adapter error
>*** Oops!
>D:\>net start oracleservicedb920
>De OracleServiceDB920-service wordt gestart...................
>De OracleServiceDB920-service is gestart.
>
>D:\>d:\oracle\817\bin\tnsping jaap
>TNS Ping Utility for 32-bit Windows: Version 8.1.7.4.0 - Production on
>02-OCT-2006 20:03:57
>(c) Copyright 1997 Oracle Corporation. All rights reserved.
>
>Attempting to contact
>(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracledb920)(ARGS='(DESCRIPTION=(LOCAL=yes)
>(ADDRESS=(PROTOCOL=BQ)))'))
>OK (30 msec)
>
>*** So far, so good... let's check the connectivity:
>D:\>d:\oracle\817\bin\sqlplus scott/tiger_at_jaap
>SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 2 20:06:22 2006
>(c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>Connected to:
>Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
>With the Partitioning, Oracle Label Security, OLAP and Oracle Data
>Mining options
>JServer Release 9.2.0.6.0 - Production
>*** Okay - an 8i client can connect to 9i Release 2, patch level 6.
>*** So, the database should be able to connect, too.
>D:\>d:\oracle\817\bin\sqlplus scott/tiger
>SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 2 20:07:17 2006
>(c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>Connected to:
>Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
>With the Partitioning option
>JServer Release 8.1.7.4.1 - Production
>
>SQL> create database link jaap connect to scott identified by tiger
>using 'jaap.cs.nl';
>
>Database link created.
>
>SQL> select * from dual_at_jaap;
>select * from dual_at_jaap
> *
>ERROR at line 1:
>ORA-02085: database link JAAP.CS.NL connects to DB920.CS.NL
>*** Ah - my own pitfall: global db names is always true.
>
>SQL> drop database link jaap;
>Database link dropped.
>
>SQL> create database link db920 connect to scott identified by tiger
>using 'jaap.cs.nl';
>
>Database link created.
>
>SQL> select * from dual_at_db920;
>
>D
>-
>X
>--
>Regards,
>Frank van Bortel
>
>Top-posting is one way to shut me up...

Thanks for showing your working example.

Regards, Jaap. Received on Mon Oct 02 2006 - 15:06:18 CDT