This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Mar 19, 2013 4:54 AM by EdStevens RSS

Connecting to 2 Oracle databases on different port

996800 Newbie
Currently Being Moderated
Hi,

Oracle Version : 11G
Database SIDs : test and testnew
Listener name: LISTENER
Port number: 1521
Net Services name: TEST and TESTNEW

I opened ./netca and I was able to test the service TEST successfully. But when i try to test the service TESTNEW, i get the error
ORA-03135: Connection Lost

How do i solve this.

Iam new to Database handling. Please help me...

Thanks in advance.
  • 1. Re: Connecting to 2 Oracle databases on different port
    User477708-OC Journeyer
    Currently Being Moderated
    explain what you mean by "different port"?
  • 2. Re: Connecting to 2 Oracle databases on different port
    Pavan DBA Expert
    Currently Being Moderated
    try to do tnsping testnew and let us know the output of the command. also the output of
    lsnrctl status
  • 3. Re: Connecting to 2 Oracle databases on different port
    moreajays Pro
    Currently Being Moderated
    Hi,

    Refer listener.ora .. to have more than one db services under single listener you need to have them registered something like below
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = PLSExtProc)
          (ORACLE_HOME = /rdbms/app/oracle/product/10.2.0/db_1)
          (PROGRAM = extproc)
          (ENVS="EXTPROC_DLLS=ANY, LD_LIBRARY_PATH=/retr/src/oxt")
        )
       (SID_DESC =
         (GLOBAL_DBNAME = RETINTST)
         (ORACLE_HOME = /rdbms/app/oracle/product/10.2.0/db_1)
         (PROGRAM = extproc)
         (ENVS="EXTPROC_DLLS=ANY, LD_LIBRARY_PATH=/index01/oradata/lib/oxt")
         (SID_NAME = RETINTST)
       )
      (SID_DESC =
         (GLOBAL_DBNAME = RETINDEV)
         (ORACLE_HOME = /rdbms/app/oracle/product/10.2.0/db_1)
         (PROGRAM = extproc)
         (ENVS="EXTPROC_DLLS=ANY, LD_LIBRARY_PATH=/index01/oradata/lib/oxt")
         (SID_NAME = RETINDEV)
       )
      (SID_DESC =
         (ORACLE_HOME = /rdbms/app/oracle/product/10.2.0/db_1)
         (SID_NAME = RTDASDEV)
       )
    
      )
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = retrdbdev)(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
      )
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 4. Re: Connecting to 2 Oracle databases on different port
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    moreajays wrote:
    Hi,

    Refer listener.ora .. to have more than one db services under single listener you need to have them registered something like below
    No, you don't have to have them configured in listener.ora file. A database is registered itself and by default to the port 1521.

    Nicolas.
  • 5. Re: Connecting to 2 Oracle databases on different port
    996800 Newbie
    Currently Being Moderated
    o/p: tnsping testnew

    TNS Ping Utility for Linux: Version 11.0.0.1.0 - Production on 19-MAR-2013 04:50:28
    Used Parameter files:
    /data/ora/oracledb/product/11.2.0/db_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = ----myip---)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = testnew)))
    OK (0 msec)


    o/p: lsnrctl status

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=---myip---)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
    Start Date 19-MAR-2013 02:17:22
    Uptime 0 days 2 hr. 39 min. 8 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File /data/ora/oracledb/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File /data/ora/oracledb/diag/tnslsnr/MA35ITLAB03/listener/alert/log.xml
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=---myip---)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=---myip---)(PORT=1522)))
    Services Summary...
    Service "testnew" has 1 instance(s).
    Instance "testnew", status READY, has 1 handler(s) for this service...
    Service "testnewXDB" has 1 instance(s).
    Instance "testnew", status READY, has 1 handler(s) for this service...
    Service "test" has 1 instance(s).
    Instance "test", status READY, has 1 handler(s) for this service...
    Service "testXDB" has 1 instance(s).
    Instance "test", status READY, has 1 handler(s) for this service...
    The command completed successfully
  • 6. Re: Connecting to 2 Oracle databases on different port
    996800 Newbie
    Currently Being Moderated
    I want database 'test' on port 1521 and 'testnew' listened on 1522.

    I just want to operate to the 2 databases without any conflict. If there is anyother way to achieve this with a single listener, please tell me...
  • 7. Re: Connecting to 2 Oracle databases on different port
    Pavan DBA Expert
    Currently Being Moderated
    with your output it is clear that your connection is good. tnsping is giving you output as "OK" with some milliseconds of time. this is the proof that its working and also your listener is listening to testnew database.

    try this

    sqlplus scott/tiger@testnew

    if it is connecting successfully, then everything is good. don't worry about testing a connection through netca in that case.
  • 8. Re: Connecting to 2 Oracle databases on different port
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    993797 wrote:
    I want database 'test' on port 1521 and 'testnew' listened on 1522.
    Why ?
    I just want to operate to the 2 databases without any conflict. If there is anyother way to achieve this with a single listener, please tell me...
    There's is absolutely no problem to have more than one database for one and only one listener (on one port) without 'conflict'.
    Stop your listener, take a backup of listener.ora file, then remove listener.ora and restart the listener. Wait for few seconds and run lsnrctl status.

    Nicolas.
  • 9. Re: Connecting to 2 Oracle databases on different port
    Pavan DBA Expert
    Currently Being Moderated
    in that case, you need to create another listener with a different port no. using netca create another listener with port no 1522.

    after that open tnsnames.ora and in tns entry change your port no for testnew to 1522.

    Note: when you create another listener it will listen to both test and testnew. if you want that to listen to only testnew, then you need to give a static entry as other friend suggested in the reply
  • 10. Re: Connecting to 2 Oracle databases on different port
    996800 Newbie
    Currently Being Moderated
    I gave the command and it after a long time it gave the following o/p

    ERROR:
    ORA-03135: Connection lost contact
    Process ID: 0
    Session ID: 0 Serial number: 0

    Enter user-name:
    (I even tried with the username sys@testnew as sysdba and my password for the db)
  • 11. Re: Connecting to 2 Oracle databases on different port
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    You seem not very familiar to Oracle networking, so at the moment I would advise to keep it the simplest as possible. Use one and only one listener using one and only one port.

    Nicolas.
  • 12. Re: Connecting to 2 Oracle databases on different port
    996800 Newbie
    Currently Being Moderated
    I created one more listener to run on port 1522.

    And changed the port for testnew in tnsnames.ora to 1522.
    But when i try connecting to testnew,
    i get the following error:

    ORA-12514: TNS: listener does not currently know of the service requested in connect descriptor
  • 13. Re: Connecting to 2 Oracle databases on different port
    996800 Newbie
    Currently Being Moderated
    Hi,

    When i use a single listener on port 1521 and run the service TESTNEW also on port 1521 and give a test connection,

    it says connection lost contact. How do i solve this...
  • 14. Re: Connecting to 2 Oracle databases on different port
    Pavan DBA Expert
    Currently Being Moderated
    usually listener will take some time in order to register instance information. can you paste output of below?
    lsnrctl status your_new_listener_name
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points