OraFAQ Forum: Networking and Gateways » multiple ports, same SID

Home » RDBMS Server » Networking and Gateways » multiple ports, same SID

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

multiple ports, same SID [message #136069]

Mon, 05 September 2005 21:23

awfief
Messages: 5
Registered: September 2005

Junior Member

Hi there,

I'm attempting to be able to connect to the same database through 2 different ports. I've looked around on all the different documentation, and it seems like you can configure the listener for 2 different ports, but only for 2 different SIDs. I want the same SID, the same database, but to be able to connect with 2 different ports. I have tried many listener.ora configurations, and none of them work.

(I'm using oracle 10g in this example, but I have many databases and would like to be able to do this for oracle 8i, 9i and 10g)

If I set up my listener.ora in the following manner:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DNAME = foo)
      (ORACLE_HOME = /home/oracle10)
      (SID_NAME = foo)
    )
  )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 20000))
      )
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
    )
  )

Then I can connect to sqlplus scott/tiger@foo, which connects through port 1521, but I cannot connect through port 12344. When starting LISTENER1 I get a message that says "The listener supports no services".

If I do the following:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DNAME = foo)
      (ORACLE_HOME = /home/oracle10)
      (SID_NAME = foo)
    )
  )

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DNAME = bar)
      (ORACLE_HOME = /home/oracle10)
      (SID_NAME = foo)
    )
  )

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 20000))
      )
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
    )
  )

Then both LISTENER and LISTENER1 start the service 'foo', and if I connect to scott/tiger@foo, I go through port 1521, and not 20000.

If I change the SID_NAME to bar, I can start LISTENER and LISTENER1 just fine, tnsping works for both, pinging 1521 and 20000, but when I try to connect scott/tiger@bar, it gives me the "shared memory realm does not exist" -- not surprising, since there is no oracle running with SID bar.

How can I set up the same database and SID to use 2 different ports? (it doesn't have to be the same SID, but the requirements are: connect to the same database on 2 different ports at the same time. so 2 different SIDs wouldn't work, I believe, because you have to stop one instance, then start another).

Thank you for your time,

-Sheeri

Report message to a moderator

Re: multiple ports, same SID [message #136113 is a reply to message #136069]

Tue, 06 September 2005 03:48

Frank Naude
Messages: 4579
Registered: April 1998

Senior Member

Try something like this -

LISTENER =
 (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1526))
 )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DNAME = foo)
      (ORACLE_HOME = /home/oracle10)
      (SID_NAME = foo)
    )
  )

Best regards.

Frank

Report message to a moderator

Re: multiple ports, same SID [message #136114 is a reply to message #136113]

Tue, 06 September 2005 03:51

awfief
Messages: 5
Registered: September 2005

Junior Member

Hi Frank,

Thanx! What connect strings can I use to test that out? When does the server use port 1521 and 1526?

-Sheeri

Report message to a moderator

Re: multiple ports, same SID [message #136118 is a reply to message #136114]

Tue, 06 September 2005 04:15

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

hi ,
As i know u need a service ( configure tnsnames.ora) also, that is going to connect through the second listener.

Cant we do it like this, i tried on my machine and it worked :

Listener.ora on my machine

Quote:

# LISTENER.ORA Network Configuration File: D:\oracle\ora92\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Tarun)(PORT = 1522))
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Tarun)(PORT = 1521))
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = tanudb1)
)
)

SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tanudb1)
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = tanudb1)
)
)

And corresponding tnsnames.ora

Quote:

# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

TANUDB1_TARUN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tarun)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = tanudb1)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Tharun)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

TEST_SRVC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tarun)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tanudb1)
(SERVER = DEDICATED)
)
)

Now if both i.e. listener1 ( port 1522) , listener (port- 1521) are running on the machine then u can connect to the same database using two different services, like --

SQL> conn scott/tiger@test_srvc

{ this will connect to the database using listener i.e. port 1521 }

and

SQL> conn scott/tiger@tanudb1_tarun

{ this will connect to the database using listener i.e. port 1522 }

Now i think i m able to make it clear.

regards,
tarun

Report message to a moderator

Re: multiple ports, same SID [message #136360 is a reply to message #136118]

Wed, 07 September 2005 14:00

awfief
Messages: 5
Registered: September 2005

Junior Member

Hi there,

Thank you for your help. I did not respond immediately because I wanted to carefully try your suggestion.

I tried what you said:

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME = /db/ora)
    (SID_NAME= foo)
    )
  )

SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
    (GLOBAL_DNAME = foo)
    (ORACLE_HOME = /db/ora)
    (SID_NAME= foo)
    )
)

LISTENER =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521))
)
LISTENER1 =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 12344))
)

where foo is a real database, and host is a real hostname

and

tnsnames.ora

on0goose =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 1521)))
    (CONNECT_DATA = (SID = foo)(SERVICE_NAME = foo))
  )

bar =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 12344)))
    (CONNECT_DATA = (SERVICE_NAME = bar))
  )

sqlplus scott/tiger@foo

works, but

sqlplus scott/tiger@bar

does not -- it gives this error:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

for whatever reason, I think it wants bar as a SID and database name, too. . .

Please let me know what I have gotten wrong.

Thank you,

-Sheeri

[Updated on: Wed, 07 September 2005 14:01]

Report message to a moderator

Re: multiple ports, same SID [message #136370 is a reply to message #136360]

Wed, 07 September 2005 15:03

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>(PORT = 12344)))
are you sure this is your port?

Report message to a moderator

Re: multiple ports, same SID [message #136372 is a reply to message #136370]

Wed, 07 September 2005 15:07

awfief
Messages: 5
Registered: September 2005

Junior Member

Yes, I am sure that's the port I want. Must I use 1522? I know there is nothing using that port; even if there was something using it, I'd get an error when I started up the listener (I tried this). Is there something wrong with using port 12344 if nothing else is using it?

Maybe that's my problem.

Report message to a moderator

Re: multiple ports, same SID [message #136432 is a reply to message #136372]

Thu, 08 September 2005 00:27

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

hey what is the problem there , i tried the same and i m able to connect.

see my listener.ora file first,

[ quote]

LISTENER2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Tarun)(PORT = 12344))
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Tarun)(PORT = 1521))
)

SID_LIST_LISTENER2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = tanudb1)
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = tanudb1)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = d:\oracle\ora92)
(SID_NAME = tanudb1)
)
)
[/quote]

Now see my tnsnames.ora file,

Quote:

# TNSNAMES.ORA Network Configuration File: D:\oracle\ora92\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

SRV_TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tarun)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = tanudb1)
)
)

TANUDB1_TARUN1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tarun)(PORT = 12344))
)
(CONNECT_DATA =
(SERVICE_NAME = tanudb1)
)
)

Now see what happen when i try to connect.

Quote:

SQL> conn scott/tiger@srv_test
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T1 TABLE
T2 TABLE
T3 TABLE
TAB_CHK TABLE
TEST_Q TABLE

9 rows selected.

SQL> conn scott/tiger@tanudb1_tarun1;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
T1 TABLE
T2 TABLE
T3 TABLE
TAB_CHK TABLE
TEST_Q TABLE

9 rows selected.

Did u check both of ur listeners are started & running?

or i think it might be the problem.

[ quote]

bar =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host)(PORT = 12344)))
(CONNECT_DATA = (SERVICE_NAME = bar))
)

[/quote]

This service_name should refer to the SID of ur datbase & i think the SID of ur DB is foo. Change & try once more.

regards,
tarun

Report message to a moderator

Re: multiple ports, same SID [message #136524 is a reply to message #136432]

Thu, 08 September 2005 09:07

awfief
Messages: 5
Registered: September 2005

Junior Member

Aha! It was the last one, the SID. THANK YOU. However, I have a bigger problem.

Basically, I'm trying to login by sending traffic to port 12344, where I have a wrapper script running (as a daemon listening for traffic). However, I cannot run a listener on port 12344 if another application is using it. Is there a way to tell oracle to send traffic to a port, even if there's no listener running on it? 'cause that's my goal here.

(If I try to start the listener I get:

TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

which makes sense, because port 12344 is already in use. When I try to connect, I get

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

which makes sense, because there's no listener running on that port.

Thanks in advance. Everyone here has been so helpful.

-Sheeri

Report message to a moderator

Re: multiple ports, same SID [message #138044 is a reply to message #136069]

Mon, 19 September 2005 10:16

oracle_techie
Messages: 24
Registered: July 2005
Location: Munbai

Junior Member

Can u more elaborate on "sending traffic to port 12344"?

Report message to a moderator

Re: multiple ports, same SID [message #139946 is a reply to message #138044]

Fri, 30 September 2005 02:42

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

@awfief says:

Is there a way to tell oracle to send traffic to a port, even if there's no listener running on it?

Can't think of any. Infact, if I am not wrong, if listener is not listening on a particular port say 1553, we can't connect to oracle using that port 1553.

Report message to a moderator

Previous Topic:	ORA 2049 error on Oracle 9i2 Solaris 9
Next Topic:	Solaris 9, 10g how to start OEM

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:18:10 CDT 2024