1 2 Previous Next 24 Replies Latest reply: Nov 8, 2012 1:54 AM by Kgronau-Oracle RSS

    SP2-0640: Not connected -- Create database link error Gateway

    887834
      While trying to connect through SQL Gateway using "Create database link", am getting "SP2-0640: Not connected" error. below are the details.

      ====================================================================
      TNSNAMES.ora
      dg4msql =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
      (SERVICE_NAME = dg4msql))
      (HS = OK)
      )
      ====================================================================
      LISTENER.ora
      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = C:\product\11.2.0\tg_1)
      (PROGRAM = C:\product\11.2.0\tg_1\bin\dg4msql)
      )
      )

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

      ADR_BASE_LISTENER = C:\product\11.2.0\tg_1
      ====================================================================
      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= (NTS)

      NAMES.DIRECTORY_PATH= (TNSNAMES)

      ====================================================================
      initdg4msql.ora
      HS_FDS_CONNECT_INFO=sqlserverip,port//PMDB
      HS_FDS_TRACE_LEVEL=OFF
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER

      ====================================================================
      outupt of lsnrctl start
      The application "dg4msql" state UNKNOWN, has 1 handler for this service ...
      The command was executed

      ====================================================================
      output of tnsping

      Parameter file used:
      C: \ product \ 11.2.0 \ tg_1 \ network \ admin \ sqlnet.ora

      was used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS = OK))
      OK (20 msec)

      ====================================================================

      output of db link creation

      SQL*Plus: Release 11.2.0.2.0 Production on Lun Ott 8 12:03:08 2012

      Copyright (c) 1982, 2010, Oracle. All rights reserved.

      SQL> CREATE PUBLIC DATABASE LINK dblink CONNECT TO sa IDENTIFIED BY Manager1 USI
      NG 'dg4msql';
      SP2-0640: Not connected.
        • 1. Re: SP2-0640: Not connected -- Create database link error Gateway
          Mkirtley-Oracle
          Hi,
          The error in the create database link looks like a SQL*Net or RDBMS problem as the gateway is not involved during the actual link create and is only involved when you actually try to use the link.
          I cannot find any obvious reasons for this so can you make sure the user trying to create the link has all the correct privileges ?
          Do you get the same error if you try to create a database link that is not for the gateway ?

          One other point, when you get round the SP2-0640 erro you will need to specify the create link as -

          CREATE PUBLIC DATABASE LINK dblink CONNECT TO "sa" IDENTIFIED BY "Manager1" USING 'dg4msql';

          The double quotes preserve the case when the details are sent to SQL*Server.

          Check if the error happens for none Gateway links and we can follow up.

          Regards,
          Mike
          • 2. Re: SP2-0640: Not connected -- Create database link error Gateway
            Kgronau-Oracle
            SP2-0640: Not connected. is commonly being raised when you didn't log into an Oracle database. Can you please post your command line how you started SQL*Plus and before creating a database link just perform a select: select user from dual";
            Then please provide the complete output.
            • 3. Re: SP2-0640: Not connected -- Create database link error Gateway
              887834
              Thank you. I was not connected to any db and trying to create the link. So, I was getting that not connected error.

              I've connected and could successfully create a link using ---- SQL> CREATE PUBLIC DATABASE LINK db8 CONNECT TO "sa" IDENTIFIED BY "Manager1" USING 'dg4msql'; But, while trying to fetch data using -- select count(*) from ACCOUNT@db8; -- am getting an error ORA-12154: TNS:could not resolve the connect identifier specified

              I also tried---- SQL> CREATE PUBLIC DATABASE LINK db3 CONNECT TO "sa" IDENTIFIED BY "Manager1" USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SID = dg4msql))(HS = OK))' -- to create a db link. when i queried select count(*) from ACCOUNT@db3 am able to see the results.

              tnsnames.ora file is placed at ORACLE_HOME\network\admin\. I also tried placing the file at ORACLE_HOME\dg4msql\admin



              Regards..
              • 4. Re: SP2-0640: Not connected -- Create database link error Gateway
                Kgronau-Oracle
                Just to clarify, in which Oracle_Home is your tnsnames.ora file that contains the dg4msql alias?


                the gateway is being called by the Oracle database, so the dg4msql alias needs to reside in the Oracle_home of the database in network/admin, not in the gateway home.
                • 5. Re: SP2-0640: Not connected -- Create database link error Gateway
                  887834
                  adding ORACLE_HOME environment variable resolved this issue.
                  • 6. Re: SP2-0640: Not connected -- Create database link error Gateway
                    887834
                    I am facing another issue while querying the sql DB using database.

                    I created a link
                    SQL> create public database link ln connect to "sa" identified by "Manager1" using 'dg4msql';


                    I am able to query the table.
                    SQL> select count(*) from test@ln;

                    COUNT(*)
                    ----------
                    1

                    But if i try to access any column inside the table, am getting error.

                    SQL> select id from test@ln;
                    select id from test@ln
                    *
                    ERROR at line 1:
                    ORA-00904: "ID": invalid identifier

                    Please help me in resolving this.
                    • 7. Re: SP2-0640: Not connected -- Create database link error Gateway
                      Kgronau-Oracle
                      most foreign databases are case sensitive and Oracle by default shifts everything to upper case. To preserve the name of an object you should always surround it by double quotes and make sure tzhat you write the name as it is defined in the foreign database, for example:
                      select "id" from "TEST"@ln;
                      • 8. Re: SP2-0640: Not connected -- Create database link error Gateway
                        887834
                        I am getting errors while accessing sqldb using gateway link.

                        Creation of link is successful but, select query is returning errors.



                        SQL> select * from @ dblink1 prefer;
                        select * from @ prefer dblink1
                        *
                        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 DBLINK1
                        • 9. Re: SP2-0640: Not connected -- Create database link error Gateway
                          Mkirtley-Oracle
                          Hi,
                          What's the actual name of the database link being used ? The syntax you are using isn't correct.

                          You have used -

                          select * from @ dblink1 prefer;

                          but it should be -

                          select * from "object_name"@db_link ;

                          - If the database link is called dblink1 then it should be -

                          select * from "object_name"@dblink1 ;

                          Is 'prefer' the table name on the non-Oracle database ? If so, you should use -

                          select * from "prefer"@dblink1 ;

                          Regards,
                          Mike

                          Edited by: mkirtley on Nov 7, 2012 12:39 PM
                          • 10. Re: SP2-0640: Not connected -- Create database link error Gateway
                            887834
                            I am sorry for the typo. Query which i executed was

                            select count(*) from prefer@dblink1;


                            dblink1 is the link which is created by me.


                            Also, the query, select count(*) from "prefer"@dblink1; is also throwing the same error.
                            • 11. Re: SP2-0640: Not connected -- Create database link error Gateway
                              Mkirtley-Oracle
                              Hi,
                              No problem.
                              The ORA-28545 error is usually a configuration problem so we need to see -

                              - gateway listener.ora
                              - gateway init<sid>.ora
                              - tnsnames.ora used for th db link create
                              - the dblink create statement

                              Make sure the gateway listener is running and has a service name available that is used in the configuration.

                              Regards,
                              Mike
                              • 12. Re: SP2-0640: Not connected -- Create database link error Gateway
                                887834
                                Here are the details you requested for.

                                ====================================================================
                                TNSNAMES.ora
                                dg4msql =
                                (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.176.111.127)(PORT = 1521))
                                (CONNECT_DATA =
                                (SERVICE_NAME = dg4msql))
                                (HS = OK)
                                )
                                ====================================================================
                                LISTENER.ora
                                SID_LIST_LISTENER =
                                (SID_LIST =
                                (SID_DESC =
                                (SID_NAME = dg4msql)
                                (ORACLE_HOME = C:\product\11.2.0\tg_1)
                                (PROGRAM = C:\product\11.2.0\tg_1\bin\dg4msql)
                                )
                                )

                                LISTENER =
                                (DESCRIPTION_LIST =
                                (DESCRIPTION =
                                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.176.111.127)(PORT = 1521))
                                )
                                )

                                ADR_BASE_LISTENER = C:\product\11.2.0\tg_1
                                ====================================================================
                                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= (NTS)

                                NAMES.DIRECTORY_PATH= (TNSNAMES)

                                ====================================================================
                                initdg4msql.ora
                                HS_FDS_CONNECT_INFO=sqlserverip,port//PMDB
                                HS_FDS_TRACE_LEVEL=OFF
                                HS_FDS_RECOVERY_ACCOUNT=RECOVER
                                HS_FDS_RECOVERY_PWD=RECOVER

                                ====================================================================
                                outupt of lsnrctl start
                                The application "dg4msql" state UNKNOWN, has 1 handler for this service ...
                                The command was executed

                                ====================================================================
                                output of tnsping

                                Parameter file used:
                                C: \ product \ 11.2.0 \ tg_1 \ network \ admin \ sqlnet.ora

                                was used TNSNAMES adapter to resolve the alias
                                Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.176.111.127) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = dg4msql)) (HS = OK))
                                OK (20 msec)

                                ====================================================================

                                output of db link creation

                                SQL*Plus: Release 11.2.0.2.0 Production on Lun Ott 8 12:03:08 2012

                                Copyright (c) 1982, 2010, Oracle. All rights reserved.

                                SQL> CREATE PUBLIC DATABASE LINK dblink CONNECT TO sa IDENTIFIED BY Manager1 USI
                                NG 'dg4msql';

                                database link created
                                • 13. Re: SP2-0640: Not connected -- Create database link error Gateway
                                  Mkirtley-Oracle
                                  Hi,
                                  SQL*Server is case sensitive for user and password so do you ge the same error with -

                                  CREATE PUBLIC DATABASE LINK dblink CONNECT TO "sa" IDENTIFIED BY "Manager1" USING 'dg4msql';

                                  the double quotes preserve the case when sent to SQL*Server.
                                  Also, change the 'program' entry in the listener.ora from -

                                  (PROGRAM = C:\product\11.2.0\tg_1\bin\dg4msql)

                                  to

                                  (PROGRAM = dg4msql)

                                  then stop and start the listener and try again.

                                  Regards,
                                  Mike
                                  • 14. Re: SP2-0640: Not connected -- Create database link error Gateway
                                    887834
                                    Here is the result of what you told me. I've also changed program=dg4msql in listener.ora file.

                                    C:\Users\Administrator>sqlplus /nolog

                                    SQL*Plus: Release 11.2.0.2.0 Production on Mer Nov 7 13:11:35 2012

                                    Copyright (c) 1982, 2010, Oracle. All rights reserved.

                                    SQL> conn system/Manager1@obiee
                                    Connesso.
                                    SQL> create public database link dblink connect to "sa" identified by "Manager1"
                                    using 'dg4msql';

                                    Creato database link.

                                    SQL> select count(*) from "prefer"@dblink;
                                    select count(*) from "prefer"@dblink
                                    *
                                    ERRORE alla riga 1:
                                    ORA-28545: errore diagnosticato da Net8 durante la connessione a un agente
                                    Unable to retrieve text of NETWORK/NCR message 65535
                                    ORA-02063: precedente 2 lines da DBLINK


                                    SQL>
                                    1 2 Previous Next