Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Sep 2009
    Posts
    10

    Unanswered: Database Link Problem

    Hi there,

    a strange database link problem.

    I have two hosts (for this thread, "ora01" and "ora02", both in the same subnet, no routers or firewalls between them) operating Oracle. On each host, an instance is running ("db1" and "db2"). Both instances are healthy and fully usable.

    Now i want to replicate "db1" onto "db2" using a database link and materialized views. Before creating the link, i checked the connectivity and SQL*Net config:

    • PING on hostname from ora01 to ora02 - OK
    • PING on hostname from ora02 to ora01 - OK
    • PING on ip from ora01 to ora02 - OK
    • PING on ip from ora02 to ora01 - OK
    • TNSPING from ora01 to instance db2 on ora02 - OK
    • TNSPING from ora02 to instance db1 on ora01 - OK


    The output of the second TNSPING...

    Code:
    SQL> !tnsping db01
    
    TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 25-SEP-2009 13:56:04
    
    Copyright (c) 1997, 2005, Oracle.  All rights reserved.
    
    Used parameter files:
    /local/oracle/product/network/admin/sqlnet.ora
    
    
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora01.mydomain.de)(PORT = 1521))) (CONNECT_DATA = (GLOBAL_DBNAME = db01.mydomain.de) (GLOBAL_NAME = db01.mydomain.de) (ORACLE_HOME = /local/oracle/product) (SERVICE_NAME = db01) (INSTANCE_NAME = db01) (SID_NAME = db01) (SID = db01)))
    OK (0 msec)
    ... the service descriptor in tnsnames.ora ...

    Code:
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ora01.mydomain.de)(PORT = 1521))
        )
        (CONNECT_DATA =
          (GLOBAL_DBNAME = db01.mydomain.de)
          (GLOBAL_NAME = db01.mydomain.de)
          (ORACLE_HOME = /local/oracle/product)
          (SERVICE_NAME = db01)
          (INSTANCE_NAME = db01)
          (SID_NAME = db01)
          (SID = db01)
        )
      )
    ... and finally the corresponding listener description:

    Code:
        (SID_DESC =
          (GLOBAL_DBNAME = db01.mydomain.de)
          (GLOBAL_NAME = db01.mydomain.de)
          (ORACLE_HOME = /local/oracle/product)
          (SERVICE_NAME = db01)
          (INSTANCE_NAME = db01)
          (SID_NAME = db01)
          (SID = db01)
        )
    Then i created the link on "ora02":

    Code:
    SQL>  create public database link db01 connect to db01user identified by db01password using 'db01';
    
    Database link created.
    But:

    When i try to describe, select etc. tables in "db01" on "ora01" via SQL*Plus on "ora02", i. e.:

    Code:
    SQL> describe some_table@db01
    i always get:

    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor


    Weird. Has anyone of you had the same problems?

    Best & thanks in advance,
    - Wulf
    Last edited by wkaiser; 09-29-09 at 06:28.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What are the versions of the databases. I have seen this between 8i and 10g
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2009
    Posts
    10
    Hi Bill,

    both Oracle Installations are 10g, Release 2 (10.2.0.1.0), under Solaris 10 SPARC.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL> DESC TABLE1@REMOTE -- can respond badly
    I test using SQL below
    SQL> SELECT COUNT(*) FROM USER_OBJECTS@REMOTE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2009
    Posts
    10
    Hi,

    first, thanx for replying. select count(*) also raised ORA-12514...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While logged onto system ora1 as owner of Oracle s/w CUT the following lines:

    set
    listener status
    listener service

    CUT commands & results and PASTE back here

    Paste here the tail end (30 - 40 lines) from listener.log file from ora1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2009
    Posts
    10
    1. SET on ora01

    Code:
    oracle@ora01<28> set
    GLOBAL_DBNAME   db01.mydomain.de
    GLOBAL_NAME     db01.mydomain.de
    LD_LIBRARY_PATH /local/oracle/product/lib:/usr/local/lib:/usr/lib/:/lib:/usr/sfw/lib
    NLS_LANG        german_germany.UTF8
    ORACLE_BASE     /local/oracle/product
    ORACLE_HOME     /local/oracle/product
    ORACLE_HOME_LISTNER     /local/oracle/product
    ORACLE_OWNER    oracle
    ORACLE_SID      db01
    ORA_SVC db01
    SERVICE_NAME    db01
    SID_NAME        db01
    TNS_ADMIN       /local/oracle/product/network/admin
    _       
    
    addsuffix       
    argv    ()
    autologout      60
    cwd     /home/filer1/oracle
    dirstack        /home/filer1/oracle
    echo_style      bsd
    edit    
    gid     200
    group   oinstall
    history 100
    home    /home/filer1/oracle
    killring        30
    loginsh 
    owd     
    path    (/usr/bin /usr/local/bin /local/oracle/product/bin /usr/sfw/bin /usr/sbin /sbin /usr/openwin/bin /usr/ccs/bin /usr/ucb /usr/bin /usr/local/bin /local/oracle/product/bin)
    prompt  oracle@ora01<!> 
    prompt2 %R? 
    prompt3 CORRECT>%R (y|n|e|a)? 
    shell   /bin/tcsh
    shlvl   1
    status  0
    tcsh    6.12.00
    term    vt100
    tty     pts/1
    uid     200
    user    oracle
    version tcsh 6.12.00 (Astron) 2002-07-23 (sparc-sun-solaris) options 8b,nls,dl,al,kan,rh,color,dspm,filec
    2. LISTENER service on ora01

    Code:
    oracle@ora01<24> $ORACLE_HOME/bin/lsnrctl service
    
    LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 25-SEP-2009 18:18:41
    
    Copyright (c) 1991, 2005, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01.mydomain.de)(PORT=1521)))
    Services Summary...
    Service "db02.mydomain.de" has 1 instance(s).
      Instance "db02", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    Service "db01.mydomain.de" has 2 instance(s).
      Instance "db01", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
      Instance "db01", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:130 refused:0 state:ready
             LOCAL SERVER
    Service "db01XDB.mydomain.de" has 1 instance(s).
      Instance "db01", status READY, has 1 handler(s) for this service...
        Handler(s):
          "D000" established:0 refused:0 current:0 max:992 state:ready
             DISPATCHER <machine: ora01, pid: 14039>
             (ADDRESS=(PROTOCOL=tcp)(HOST=ora01.mydomain.de)(PORT=37175))
    Service "db01_XPT.mydomain.de" has 1 instance(s).
      Instance "db01", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:130 refused:0 state:ready
             LOCAL SERVER
    The command completed successfull
    3. LISTENER status on ora01

    Code:
    oracle@ora01<25>  $ORACLE_HOME/bin/lsnrctl status
    
    LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 25-SEP-2009 18:23:53
    
    Copyright (c) 1991, 2005, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01.mydomain.de)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
    Start Date                25-SEP-2009 13:11:58
    Uptime                    0 days 5 hr. 11 min. 54 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /local/oracle/product/network/admin/listener.ora
    Listener Log File         /local/oracle/product/network/log/listener.log
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora01.mydomain.de)(PORT=1521)))
    Services Summary...
    Service "db02.mydomain.de" has 1 instance(s).
      Instance "db02", status UNKNOWN, has 1 handler(s) for this service...
    Service "db01.mydomain.de" has 2 instance(s).
      Instance "db01", status UNKNOWN, has 1 handler(s) for this service...
      Instance "db01", status READY, has 1 handler(s) for this service...
    Service "db01XDB.mydomain.de" has 1 instance(s).
      Instance "db01", status READY, has 1 handler(s) for this service...
    Service "db01_XPT.mydomain.de" has 1 instance(s).
      Instance "db01", status READY, has 1 handler(s) for this service...
    The command completed successfully

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    3 out of 4 is OK

    Paste here the tail end (30 - 40 lines) from listener.log file from ora1

    It could be a service name mismatch
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Sep 2009
    Posts
    10
    Sorry i forgot

    4. The listener log tail

    Code:
    oracle@ora01<26> tail -30 /local/oracle/product/network/log/listener.log
    25-SEP-2009 18:16:33 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01)(PORT=1521)))(VERSION=169869568)) * status * 0
    25-SEP-2009 18:16:43 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44641)) * establish * db01 * 0
    25-SEP-2009 18:18:41 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * services * 0
    25-SEP-2009 18:19:26 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44667)) * establish * db01 * 0
    25-SEP-2009 18:20:35 * service_update * db01 * 0
    25-SEP-2009 18:21:32 * ping * 0
    25-SEP-2009 18:21:33 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01)(PORT=1521)))(VERSION=169869568)) * status * 0
    25-SEP-2009 18:21:43 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44692)) * establish * db01 * 0
    25-SEP-2009 18:23:44 * service_update * db01 * 0
    25-SEP-2009 18:23:53 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    25-SEP-2009 18:24:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01)(PORT=1521))))(VERSION=169869568)) * services * 0
    25-SEP-2009 18:24:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01)(PORT=1521)))(VERSION=169869568)) * status * 0
    25-SEP-2009 18:25:15 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44731)) * establish * db01 * 0
    25-SEP-2009 18:26:05 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44738)) * establish * db01 * 0
    25-SEP-2009 18:26:33 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01)(PORT=1521)))(VERSION=169869568)) * status * 0
    25-SEP-2009 18:26:43 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44748)) * establish * db01 * 0
    25-SEP-2009 18:31:13 * service_update * db01 * 0
    25-SEP-2009 18:31:32 * ping * 0g
    25-SEP-2009 18:31:33 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora01)(PORT=1521)))(VERSION=169869568)) * status * 0
    25-SEP-2009 18:31:44 * (CONNECT_DATA=(SID=db01)(CID=(PROGRAM=perl@ora01)(HOST=ora01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=44795)) * establish * db01 * 0
    25-SEP-2009 18:31:46 * service_update * db01 * 0

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The listener.log shows every connect request it gets.
    The right most value is the status code for that request.
    status=0 for successful completion.
    What you post shows only requests from ORA1 & all were OK.

    I need you to reproduce problem from ORA2 & PASTE back here lines before, after, and including the failure from listener.log from ORA1.

    It will show actual SERVICE NAME being requested.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by wkaiser

    Code:
    SQL> !tnsping nps
    
    ...
    OK (0 msec)


    Code:
    SQL>  create public database link db01 connect to db01user ... using 'db01';
    
    Database link created.
    So, what is the service name for db01 as defined in tnsnames.ora on db02?
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Sep 2008
    Posts
    41
    Hi,

    Do you have db01 entry on your tnsnames on db02?

  13. #13
    Join Date
    Sep 2009
    Posts
    10
    Hi,

    yes, i have. And sorry for answering so late, but i was out-of-office for the last three days.

    Here are the outputs of set & Co. from host "ora02", the second host, where "db01" on "ora01" should be replicated to:

    1. set

    Code:
    oracle@ora02<1> set
    DB_DOMAIN       mydomain.de
    GLOBAL_DBNAME   db02.mydomain.de
    GLOBAL_NAME     db02.mydomain.de
    JAVA_HOME       /usr/java
    LD_LIBRARY_PATH /local/oracle/product/lib:/usr/lib:/lib/:/usr/sfw/lib:/usr/local/lib
    MANPATH /usr/man:/usr/sfw/man:/usr/local/man
    NLS_LANG        german_germany.UTF8
    ORACLE_BASE     /local/oracle/product
    ORACLE_HOME     /local/oracle/product
    ORACLE_HOME_LISTNER     /local/oracle/product
    ORACLE_OWNER    oracle
    ORACLE_SID      db02
    ORA_SVC db02
    SERVICE_NAME    db02.mydomain.de
    TNS_ADMIN       /local/oracle/product/network/admin
    _       
    addsuffix       
    argv    ()
    autologout      60
    cwd     /usr/local/oracle
    dirstack        /usr/local/oracle
    echo_style      bsd
    edit    
    gid     200
    group   oinstall
    history 100
    home    /usr/local/oracle
    killring        30
    loginsh 
    owd     
    path    (/usr/bin /usr/local/bin /local/oracle/product/bin /usr/java/bin /usr/java/jre/bin /usr/sfw/bin /usr/sbin /sbin /usr/openwin/bin /usr/ccs/bin /usr/ucb /usr/bin /usr/local/bin /local/oracle/product/bin /usr/java/bin /usr/java/jre/bin)
    prompt  oracle@ora02<!> 
    prompt2 %R? 
    prompt3 CORRECT>%R (y|n|e|a)? 
    shell   /usr/bin/tcsh
    shlvl   1
    status  0
    tcsh    6.12.00
    term    vt100
    tty     pts/1
    uid     200
    user    oracle
    version tcsh 6.12.00 (Astron) 2002-07-23 (sparc-sun-solaris) options 8b,nls,dl,al,kan,rh,color,dspm,filec
    2. listener service

    Code:
    oracle@ora02<2> $ORACLE_HOME/bin/lsnrctl service
    
    LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 29-SEP-2009 10:19:09
    
    Copyright (c) 1991, 2005, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))
    Services Summary...
    Service "db02.mydomain.de" has 2 instance(s).
      Instance "db02", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
      Instance "db02", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:18 refused:0 state:ready
             LOCAL SERVER
    Service "db02XDB.mydomain.de" has 1 instance(s).
      Instance "db02", status READY, has 1 handler(s) for this service...
        Handler(s):
          "D000" established:0 refused:0 current:0 max:992 state:ready
             DISPATCHER <machine: ora02, pid: 1005>
             (ADDRESS=(PROTOCOL=tcp)(HOST=ora02.mydomain.de)(PORT=32804))
    Service "db02_XPT.mydomain.de" has 1 instance(s).
      Instance "db02", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:18 refused:0 state:ready
             LOCAL SERVER
    Service "db01.mydomain.de" has 1 instance(s).
      Instance "db01", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:0 refused:0
             LOCAL SERVER
    The command completed successfully
    3. listener status

    Code:
    oracle@ora02<3> $ORACLE_HOME/bin/lsnrctl status
    
    LSNRCTL for Solaris: Version 10.2.0.1.0 - Production on 29-SEP-2009 10:21:44
    
    Copyright (c) 1991, 2005, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Solaris: Version 10.2.0.1.0 - Production
    Start Date                29-SEP-2009 09:53:44
    Uptime                    0 days 0 hr. 28 min. 0 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /local/oracle/product/network/admin/listener.ora
    Listener Log File         /local/oracle/product/network/log/listener.log
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora02.mydomain.de)(PORT=1521)))
    Services Summary...
    Service "db02.mydomain.de" has 2 instance(s).
      Instance "db02", status UNKNOWN, has 1 handler(s) for this service...
      Instance "db02", status READY, has 1 handler(s) for this service...
    Service "db02XDB.mydomain.de" has 1 instance(s).
      Instance "db02", status READY, has 1 handler(s) for this service...
    Service "db02_XPT.mydomain.de" has 1 instance(s).
      Instance "db02", status READY, has 1 handler(s) for this service...
    Service "db01.mydomain.de" has 1 instance(s).
      Instance "db01", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully
    4. tail listener log

    Code:
    oracle@ora02<11> tail -30 /local/oracle/product/network/log/listener.log
    29-SEP-2009 10:10:38 * (CONNECT_DATA=(SID=db02)(CID=(PROGRAM=perl@ora02)(HOST=ora02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.47.75)(PORT=33033)) * establish * db02 * 0
    29-SEP-2009 10:12:44 * service_update * db02 * 0
    29-SEP-2009 10:13:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521))))(VERSION=169869568)) * services * 0
    WARNING: Subscription for node down event still pending
    29-SEP-2009 10:13:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))(VERSION=169869568)) * status * 0
    29-SEP-2009 10:14:54 * service_update * db02 * 0
    29-SEP-2009 10:15:26 * ping * 0
    WARNING: Subscription for node down event still pending
    29-SEP-2009 10:15:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))(VERSION=169869568)) * status * 0
    29-SEP-2009 10:15:38 * (CONNECT_DATA=(SID=db02)(CID=(PROGRAM=perl@ora02)(HOST=ora02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.47.75)(PORT=33084)) * establish * db02 * 0
    29-SEP-2009 10:18:09 * service_update * db02 * 0
    29-SEP-2009 10:19:09 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * services * 0
    29-SEP-2009 10:20:26 * ping * 0
    WARNING: Subscription for node down event still pending
    29-SEP-2009 10:20:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))(VERSION=169869568)) * status * 0
    29-SEP-2009 10:20:38 * (CONNECT_DATA=(SID=db02)(CID=(PROGRAM=perl@ora02)(HOST=ora02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.47.75)(PORT=33135)) * establish * db02 * 0
    WARNING: Subscription for node down event still pending
    29-SEP-2009 10:21:44 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
    29-SEP-2009 10:22:28 * service_update * db02 * 0
    29-SEP-2009 10:23:12 * (CONNECT_DATA=(SID=db02)(CID=(PROGRAM=perl@ora02)(HOST=ora02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.47.75)(PORT=33160)) * establish * db02 * 0
    29-SEP-2009 10:23:13 * service_update * db02 * 0
    29-SEP-2009 10:24:09 * (CONNECT_DATA=(SID=db02)(CID=(PROGRAM=perl@ora02)(HOST=ora02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.47.75)(PORT=33169)) * establish * db02 * 0
    29-SEP-2009 10:25:26 * ping * 0
    WARNING: Subscription for node down event still pending
    29-SEP-2009 10:25:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))(VERSION=169869568)) * status * 0
    29-SEP-2009 10:25:38 * (CONNECT_DATA=(SID=db02)(CID=(PROGRAM=perl@ora02)(HOST=ora02)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.47.75)(PORT=33186)) * establish * db02 * 0
    29-SEP-2009 10:26:50 * service_update * db02 * 0
    29-SEP-2009 10:28:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521))))(VERSION=169869568)) * services * 0
    WARNING: Subscription for node down event still pending
    29-SEP-2009 10:28:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=ora02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=ora02.mydomain.de)(PORT=1521)))(VERSION=169869568)) * status * 0

  14. #14
    Join Date
    Sep 2009
    Posts
    10
    An update notice:

    when i run something like

    Code:
    SQL> describe mytable@db01;
    ERROR:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    on "ora02" while tailing -f its listener.log, there's absolutely no corrsponding entry, no error, no warning, no notice.

  15. #15
    Join Date
    Sep 2009
    Posts
    10
    Another update:

    Code:
    SQL> select * from table_events@db01;
    select * from table_events@db01
                               *
    ERROR at line 1:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    
    
    SQL> select * from "table_events"@"db01";
    select * from "table_events"@"db01"
                                 *
    ERROR at line 1:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    
    
    SQL> select * from table_events@db01.mydomain.de;
    select * from table_events@db01.mydomain.de
                                        *
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
    
    
    SQL> select * from "table_events"@"db01.mydomain.de";                                                       
    select * from "table_events"@"db01.mydomain.de"
                                 *
    ERROR at line 1:
    ORA-12514: TNS:listener does not currently know of service requested in connect
    descriptor
    The dblink was originally named "db01", but the same thing happens when i drop the link and re-create it as "db01.mydomain.de". The TNS entry was and is

    Code:
    DB01 =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = ora01.mydomain.de)(PORT = 1521))
        )
        (CONNECT_DATA =
          (GLOBAL_DBNAME = db01.mydomain.de)
          (GLOBAL_NAME = db01.mydomain.de)
          (ORACLE_HOME = /local/oracle/product)
          (SERVICE_NAME = db01)
          (INSTANCE_NAME = db01)
          (SID_NAME = db01)
          (SID = db01)
        )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •