1 2 Previous Next 21 Replies Latest reply: Mar 19, 2013 6:54 AM by EdStevens RSS

    Connecting to 2 Oracle databases on different port

    996800
      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
          oralicious
          explain what you mean by "different port"?
          • 2. Re: Connecting to 2 Oracle databases on different port
            Pavan DBA
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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