Re: Connect w/o Listener

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Mon, 02 Oct 2006 20:14:28 +0200
Message-ID: <efrkit$vvh$1@news6.zwoll1.ov.home.nl>

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...

Received on Mon Oct 02 2006 - 13:14:28 CDT