1 2 Previous Next 21 Replies Latest reply on Mar 7, 2019 5:22 AM by hemant_k

    multiple connection

    hemant_k

      hi

      is it possibel to have multiple connections of sql databases  from oracle?

       

      I am using express edition 11 .2

      i have multiple dtabases of vaious clients

      tried a entry in listner and tnsnames.ora

      but not able to connect ...

      may be a wierd question

      but please help

        • 1. Re: multiple connection
          EdStevens

          hemant_k wrote:

           

          hi

          is it possibel to have multiple connections of sql databases from oracle?

           

          I am using express edition 11 .2

          i have multiple dtabases of vaious clients

          tried a entry in listner and tnsnames.ora

          but not able to connect ...

          may be a wierd question

          but please help

          Please clearly define what you mean by "multiple connections of sql databases from oracle".

          Your question isn't weird.  It's simply too vague to know what you are asking.

          Please describe the business problem you are trying to solve.

           

          -- Edit

          Is this question somehow related to your two recent previous threads?

          connect sqlserver to oracle

          connect sql server

          • 2. Re: multiple connection
            hemant_k

            sir

            I have installed express edittion 11.2.0 on my machin .

            we are providing hosting service for various clients.

            have several clients' databases on one server.

            Now while trying to connect sql data through oracle i could access a   datbase  say db1

            now i want to establish connection to db2  for which i created ODBC separetely and added entries in lisner.ora and tnsnames.ora also created intsid.ora

            Now the situation is i am able to access data from db2 and not from db1

            My idea is to have both the connection live to access data from db1 and db2

            is this possible ?

             

            please help 

             

            hemu

            • 3. Re: multiple connection
              EdStevens

              hemant_k wrote:

               

              sir

              I have installed express edittion 11.2.0 on my machin .

              we are providing hosting service for various clients.

              have several clients' databases on one server.

              Now while trying to connect sql data through oracle i could access a datbase say db1

              now i want to establish connection to db2 for which i created ODBC separetely and added entries in lisner.ora and tnsnames.ora also created intsid.ora

              Now the situation is i am able to access data from db2 and not from db1

              My idea is to have both the connection live to access data from db1 and db2

              is this possible ?

               

              please help

               

              hemu

              Possible?

              I don't know. It's still not at all clear WHAT you are trying to do.

              Sounds to me like you might have defined a bunch of db_links in your XE database, which is running on your desktop.  And those links point to various non-Oracle databases in your data center.  Further, it sounds like some of those links work and other don't.  Do I have this correct?  If not, then please try again to explain your situation.  Your statement "trying to connect sql data from oracle" is itself very vague.  I know it seems clear to you, but we are not looking over your should to see exactly what it is you are doing, and with exactly what tools, etc. etc.

              • 4. Re: multiple connection
                hemant_k

                sir

                "Sounds to me like you might have defined a bunch of db_links in your XE database, which is running on your desktop.  And those links point to various non-Oracle databases in your data center.  Further, it sounds like some of those links work and other don't. "

                this is a situation exactly....

                I am Trying to deploy reports in Apex with different db_links  on different pages....

                 

                 

                • 5. Re: multiple connection
                  EdStevens

                  hemant_k wrote:

                   

                  sir

                  "Sounds to me like you might have defined a bunch of db_links in your XE database, which is running on your desktop. And those links point to various non-Oracle databases in your data center. Further, it sounds like some of those links work and other don't. "

                  this is a situation exactly....

                  I am Trying to deploy reports in Apex with different db_links on different pages....

                   

                   

                  Ok, so going back to your question "My idea is to have both the connection live to access data from db1 and db2 is this possible ?"

                  The answer is yes.  In using db links, it's not really a question of "opening a connection", like you do when you connect with sqlplus or some other database client.  In this case, your XE database is the client to the other databases. So you simply:

                   

                  select empid, depno from emp_tbl@db1;

                  select fname, lname, dob from emp_tbl@db2;

                   

                  Now, going back to your opening post, where you said simply "but not able to connect ...".  That is totally devoid of actionable information.  Oracle is very good about emitting error codes and messages.  If you want help resolving an error, you need to share the error message, in the context of what command created it.

                  • 6. Re: multiple connection
                    hemant_k

                    sir

                    thanks a lot

                    will post the error msgs ....

                     

                    nut will have to wait till tomorrow

                     

                     

                    thanks again

                     

                    hemu

                    • 7. Re: multiple connection
                      hemant_k

                      HELLO Sir

                      SQL> select count(*) from DB0090041@HRK1;

                      select count(*) from DB00xx41@HRK1

                                                     *

                      ERROR at line 1:

                      ORA-28545: error diagnosed by Net8 when connecting to an agent

                      Unable to retrieve text of NETWORK/NCR message 65535

                      ORA-02063: preceding 2 lines from HRK1

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                      SQL> select count(*) from DB00xx41@SIL_ABC_MUCS;

                       

                       

                        COUNT(*)

                      ----------

                           91842

                       

                       

                       

                       

                       

                       

                       

                       

                      PUBLIC SIL_ABC_MUCS sa sil_abc_mucs 28-02-19

                      PUBLIC SRIRAM support shriram 28-02-19

                      PUBLIC HRK1 support test 25-02-19

                      • 8. Re: multiple connection
                        hemant_k

                        Capture_x.JPG

                         

                        result of query

                        select * from dba_db_links

                        • 9. Re: multiple connection
                          EdStevens

                          hemant_k wrote:

                           

                          Capture_x.JPG

                           

                          result of query

                          select * from dba_db_links

                          Ok, so for problems like this we just logically follow a series of links in a chain.

                          The first link is your failing SQL statement, and we see that it is referencing db link HRK1.

                          The next link in the chain is db link HRK1 is referencing your tns net service name 'test'.

                          So for the next link in the chain, show us the tnsnames.ora entry for 'test'.  From there we will know what to look at next.

                          • 10. Re: multiple connection
                            EdStevens
                            1 person found this helpful
                            • 11. Re: multiple connection
                              hemant_k

                              hi

                              copy of tnsnames.ora

                               

                              XE =

                                (DESCRIPTION =

                                  (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1521))

                                  (CONNECT_DATA =

                                    (SERVER = DEDICATED)

                                    (SERVICE_NAME = XE)

                                  )

                                )

                               

                               

                              EXTPROC_CONNECTION_DATA =

                                (DESCRIPTION =

                                  (ADDRESS_LIST =

                                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                  )

                                  (CONNECT_DATA =

                                    (SID = PLSExtProc)

                                    (PRESENTATION = RO)

                                  )

                                )

                               

                               

                              ORACLR_CONNECTION_DATA =

                                (DESCRIPTION =

                                  (ADDRESS_LIST =

                                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                  )

                                  (CONNECT_DATA =

                                    (SID = CLRExtProc)

                                    (PRESENTATION = RO)

                                  )

                                )

                               

                               

                              test=

                                 (DESCRIPTION=

                                    (ADDRESS=

                                       (PROTOCOL=TCP)

                                       (HOST=SIL_SW215.saas.SILASPDC.com)

                                       (PORT=1521)

                                    )

                                    (CONNECT_DATA=

                                       (SID=test))

                                    (HS=OK))

                               

                               

                              sil_abc_mucs=

                                 (DESCRIPTION=

                                    (ADDRESS=

                                       (PROTOCOL=TCP)

                                       (HOST=SIL_SW215.saas.SILASPDC.com)

                                       (PORT=1521)

                                    )

                                    (CONNECT_DATA=

                                       (SID=sil_abc_mucs))

                                    (HS=OK))

                              • 12. Re: multiple connection
                                EdStevens

                                hemant_k wrote:

                                 

                                hi

                                copy of tnsnames.ora

                                 

                                XE =

                                (DESCRIPTION =

                                (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1521))

                                (CONNECT_DATA =

                                (SERVER = DEDICATED)

                                (SERVICE_NAME = XE)

                                )

                                )

                                 

                                 

                                EXTPROC_CONNECTION_DATA =

                                (DESCRIPTION =

                                (ADDRESS_LIST =

                                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                )

                                (CONNECT_DATA =

                                (SID = PLSExtProc)

                                (PRESENTATION = RO)

                                )

                                )

                                 

                                 

                                ORACLR_CONNECTION_DATA =

                                (DESCRIPTION =

                                (ADDRESS_LIST =

                                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                )

                                (CONNECT_DATA =

                                (SID = CLRExtProc)

                                (PRESENTATION = RO)

                                )

                                )

                                 

                                 

                                test=

                                (DESCRIPTION=

                                (ADDRESS=

                                (PROTOCOL=TCP)

                                (HOST=SIL_SW215.saas.SILASPDC.com)

                                (PORT=1521)

                                )

                                (CONNECT_DATA=

                                (SID=test))

                                (HS=OK))

                                 

                                 

                                sil_abc_mucs=

                                (DESCRIPTION=

                                (ADDRESS=

                                (PROTOCOL=TCP)

                                (HOST=SIL_SW215.saas.SILASPDC.com)

                                (PORT=1521)

                                )

                                (CONNECT_DATA=

                                (SID=sil_abc_mucs))

                                (HS=OK))

                                Ok, looking at the article I referred you to, what's the next link in the chain after tnsnames.ora?

                                • 13. Re: multiple connection
                                  hemant_k

                                  sir

                                   

                                  initest.ora

                                   

                                   

                                  initsid.ora

                                  # This is a sample agent init file that contains the HS parameters that are

                                  # needed for the Database Gateway for ODBC

                                   

                                   

                                  #

                                  # HS init parameters

                                  #

                                  HS_FDS_CONNECT_INFO = test

                                  HS_FDS_TRACE_LEVEL = on

                                   

                                   

                                   

                                   

                                  #

                                  # Environment variables required for the non-Oracle system

                                  #

                                  #set <envvar>=<value>

                                  ====================================initsil_abc_mucs.ora

                                   

                                  # This is a sample agent init file that contains the HS parameters that are

                                  # needed for the Database Gateway for ODBC

                                   

                                   

                                  #

                                  # HS init parameters

                                  #

                                  HS_FDS_CONNECT_INFO = sil_abc_mucs

                                  HS_FDS_TRACE_LEVEL = on

                                   

                                   

                                   

                                   

                                  #

                                  # Environment variables required for the non-Oracle system

                                  #

                                  #set <envvar>=<value>

                                   

                                  =======================listner.ora

                                   

                                  SID_LIST_LISTENER =

                                    (SID_LIST =

                                      (SID_DESC =

                                        (SID_NAME = PLSExtProc)

                                        (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                                        (PROGRAM = extproc)

                                      )

                                      (SID_DESC =

                                        (SID_NAME = CLRExtProc)

                                        (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                                        (PROGRAM = extproc)

                                      )

                                    )

                                   

                                   

                                  LISTENER =

                                    (DESCRIPTION_LIST =

                                      (DESCRIPTION =

                                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                        (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1521))

                                      )

                                    )

                                   

                                   

                                  #LISTENER1 =

                                  #  (DESCRIPTION_LIST =

                                  #    (DESCRIPTION =

                                  #      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                  #      (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1522))

                                  #    )

                                  #  )

                                   

                                   

                                  SID_LIST_LISTENER=

                                     (SID_LIST=

                                        (SID_DESC=

                                           (SID_NAME=test)

                                           (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)

                                           (PROGRAM=dg4odbc)

                                        )

                                     )

                                   

                                   

                                  SID_LIST_LISTENER=

                                     (SID_LIST=

                                        (SID_DESC=

                                           (SID_NAME=sil_abc_mucs)

                                           (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)

                                           (PROGRAM=dg4odbc)

                                        )

                                     )

                                  ===============sqlnet.ora

                                   

                                   

                                  # This file is actually generated by netca. But if customers choose to

                                  # install "Software Only", this file wont exist and without the native

                                  # authentication, they will not be able to connect to the database on NT.

                                   

                                   

                                  SQLNET.AUTHENTICATION_SERVICES = (tnsname)

                                  DEFAULT_SERVICE_LISTENER = (XE)

                                   

                                  regards

                                  hemu

                                  • 14. Re: multiple connection
                                    EdStevens

                                    hemant_k wrote:

                                     

                                    sir

                                     

                                    initest.ora

                                     

                                     

                                    initsid.ora

                                    # This is a sample agent init file that contains the HS parameters that are

                                    # needed for the Database Gateway for ODBC

                                     

                                     

                                    #

                                    # HS init parameters

                                    #

                                    HS_FDS_CONNECT_INFO = test

                                    HS_FDS_TRACE_LEVEL = on

                                     

                                     

                                     

                                     

                                    #

                                    # Environment variables required for the non-Oracle system

                                    #

                                    #set <envvar>=<value>

                                    ====================================initsil_abc_mucs.ora

                                     

                                    # This is a sample agent init file that contains the HS parameters that are

                                    # needed for the Database Gateway for ODBC

                                     

                                     

                                    #

                                    # HS init parameters

                                    #

                                    HS_FDS_CONNECT_INFO = sil_abc_mucs

                                    HS_FDS_TRACE_LEVEL = on

                                     

                                     

                                     

                                     

                                    #

                                    # Environment variables required for the non-Oracle system

                                    #

                                    #set <envvar>=<value>

                                     

                                    =======================listner.ora

                                     

                                    SID_LIST_LISTENER =

                                    (SID_LIST =

                                    (SID_DESC =

                                    (SID_NAME = PLSExtProc)

                                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                                    (PROGRAM = extproc)

                                    )

                                    (SID_DESC =

                                    (SID_NAME = CLRExtProc)

                                    (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

                                    (PROGRAM = extproc)

                                    )

                                    )

                                     

                                     

                                    LISTENER =

                                    (DESCRIPTION_LIST =

                                    (DESCRIPTION =

                                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                    (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1521))

                                    )

                                    )

                                     

                                     

                                    #LISTENER1 =

                                    # (DESCRIPTION_LIST =

                                    # (DESCRIPTION =

                                    # (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

                                    # (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1522))

                                    # )

                                    # )

                                     

                                     

                                    SID_LIST_LISTENER=

                                    (SID_LIST=

                                    (SID_DESC=

                                    (SID_NAME=test)

                                    (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)

                                    (PROGRAM=dg4odbc)

                                    )

                                    )

                                     

                                     

                                    SID_LIST_LISTENER=

                                    (SID_LIST=

                                    (SID_DESC=

                                    (SID_NAME=sil_abc_mucs)

                                    (ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)

                                    (PROGRAM=dg4odbc)

                                    )

                                    )

                                    ===============sqlnet.ora

                                     

                                     

                                    # This file is actually generated by netca. But if customers choose to

                                    # install "Software Only", this file wont exist and without the native

                                    # authentication, they will not be able to connect to the database on NT.

                                     

                                     

                                    SQLNET.AUTHENTICATION_SERVICES = (tnsname)

                                    DEFAULT_SERVICE_LISTENER = (XE)

                                     

                                    regards

                                    hemu

                                    So did you actually walk through all of the config files while reading the article, or did you just dump them out here?  I gave you the link so you could help yourself.  Without doing the detail check that you should be doing, I can tell you that your listener.ora file is messed up.  Why do you have two LISTENER sections and two SID_LIST sections?

                                    1 2 Previous Next