ORA-02085: database link GOLD_TO_SILVER connects to SILVER

This post describes how to resolve "ORA-02085: database link GOLD_TO_SILVER connects to SILVER" issue.


Error:

SQL> select count(*) from dual@GOLD_TO_SILVER;
select count(*) from dual@GOLD_TO_SILVER
                          *
ERROR at line 1:
ORA-02085: database link GOLD_TO_SILVER connects to SILVER


SQL>

Solution:

This error may occurs when global_names set to "TRUE"

SQL> show parameter global_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     TRUE

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      GOLD

SQL> alter system set global_names=FALSE scope=both;

System altered.

SQL> show parameter global_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     FALSE

SQL> select count(*) from dual@GOLD_TO_SILVER;

  COUNT(*)

----------

         1

SQL>

Thanks for going through this post..................

TNS-01151: Missing listener name, GOLD, in LISTENER.ORA

This document describes how to resolve "TNS-01151: Missing listener name, GOLD, in LISTENER.ORA" error.

Error:

[oracle@ebsdb GOLD_ebsdb]$ lsnrctl start GOLD
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 03-OCT-2019 14:03:58
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Starting /u01/app/GOLD/12.1.0//bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Log messages written to /u01/app/GOLD/12.1.0/log/diag/tnslsnr/ebsdb/gold/alert/log.xml
TNS-01151: Missing listener name, GOLD, in LISTENER.ORA

Listener failed to start. See the error message(s) above...
[oracle@ebsdb GOLD_ebsdb]$

This error occurs when service name or SID is incorrect or missing in listener.ora file which is under $TNS_ADMIN($ORACLE_HOME/network/admin).

                                                               (OR)

Incorrect environment settings of $TNS_ADMIN

Solution:

In my case listener.ora having correct entries. But got error due to incorrect settings of TNS_ADMIN.

listener.ora:

GOLD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ebsdb.apps.com)(PORT = 1521))
       )
     )

SID_LIST_GOLD =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/GOLD/12.1.0)
      (SID_NAME = GOLD)
        )
     )

STARTUP_WAIT_TIME_GOLD = 0
CONNECT_TIMEOUT_GOLD = 10
TRACE_LEVEL_GOLD = OFF

LOG_DIRECTORY_GOLD = /u01/app/GOLD/12.1.0/network/admin
LOG_FILE_GOLD = GOLD
TRACE_DIRECTORY_GOLD = /u01/app/GOLD/12.1.0/network/admin
TRACE_FILE_GOLD = GOLD
ADMIN_RESTRICTIONS_GOLD = ON
SUBSCRIBE_FOR_NODE_DOWN_EVENT_GOLD = OFF

Set TNS_ADMIN to correct path to fix the issue.

Ex:

$export TNS_ADMIN= /u01/app/GOLD/12.1.0/network/admin





Thanks for going to this post......