This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Nov 7, 2012 11:54 PM by kgronau RSS

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

887834 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    adding ORACLE_HOME environment variable resolved this issue.
  • 6. Re: SP2-0640: Not connected -- Create database link error Gateway
    887834 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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