ORA-01017 ORA-02063: preceding 2 lines from SQLEMR

oracle连接多个sqlserver的链接，主要修改initxxx.ora（oracle安装用户下），listener.ora(rac环境修改grid下的),tnsnames.ora(oracle用户下的）

[oracle@rac1 admin]$ pwd
/oracle/app/oracle/product/19c/dbhome_1/dg4msql/admin
[oracle@rac1 admin]$ ll
total 36
-rw-rw-r-- 1 oracle oinstall 11120 Dec 17  2013 dg4msql_cvw.sql
-rw-rw-r-- 1 oracle oinstall   746 Jun  8  2007 dg4msql_tx.sql
-rw-rw-r-- 1 oracle oinstall   472 Dec 11 10:23 initdg4msql.ora
-rw-r--r-- 1 oracle oinstall   364 Dec 11 14:10 initemr.ora
-rw-r--r-- 1 oracle oinstall   365 Dec 11 13:32 inittestdb.ora
-rw-rw-r-- 1 oracle oinstall   411 Nov 26 13:17 listener.ora.sample
-rw-rw-r-- 1 oracle oinstall   244 Nov 26 13:17 tnsnames.ora.sample
[oracle@rac1 admin]$ cat initemr.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.52.128]:2433//emrtable
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

[oracle@rac1 admin]$ pwd
/oracle/app/oracle/product/19c/dbhome_1/network/admin
[oracle@rac1 admin]$ more tnsnames.ora
dg4msql =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
  # (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.186)(PORT = 1521)) ##使用scan-ip将报错，需用vip
    )
    (CONNECT_DATA =
      (SID=dg4msql)
    )
    (HS = OK)
  )
testdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=testdb)
    )
    (HS = OK)
  )
emr =
  (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=emr)
    )
    (HS = OK)
  )

---

tnsnames.ora使用scan-ip将报错

SQL> select * from t@sqlemr;
select * from t@sqlemr
                *
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 SQLEMR

---

[grid@rac1 admin]$ pwd
/oracle/app/19c/grid/network/admin
[grid@rac1 admin]$ cat listener.ora
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
DGLISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=DGLISTENER))))                # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM))))              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON               # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET         # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF             # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
      (PROGRAM = dg4msql)
    )
    (SID_DESC =
      (SID_NAME = testdb)
      (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
      (PROGRAM = dg4msql)
    )
    (SID_DESC =
      (SID_NAME = emr)
      (ORACLE_HOME = /oracle/app/oracle/product/19c/dbhome_1)
      (PROGRAM = dg4msql)
    )
  )

配置完成需启停监听，检查emr服务是否注册。

[grid@rac1 admin]$ lsnrctl status|grep emr
Service "emr" has 1 instance(s).
  Instance "emr", status UNKNOWN, has 1 handler(s) for this service...
[grid@rac1 admin]$ 

[oracle@rac1 admin]$ tnsping emr

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 11-DEC-2020 14:19:11

Copyright (c) 1997, 2020, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.183)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.184)(PORT = 1521))) (CONNECT_DATA = (SID=emr)) (HS = OK))
OK (0 msec)

[oracle@rac1 ~]$ sqlplus jyc/jyc@dbcenter

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 11 13:33:06 2020
Version 19.8.0.0.0

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

Last Successful login time: Fri Dec 11 2020 10:12:35 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
 

SQL> create public database link sqlemr connect to "sa" identified by "abcd1234" using 'emr';

Database link created.
注意：最好将用户名也加上双引号，避免大小写敏感问题。

SQL> select * from t@sqlemr; --此处报错是原先initemr.ora配置1433端口，并没有对应到实际的实例上。sqlserver有多个实例的时候需要注意的不同端口问题。
select * from t@sqlemr
                *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
[Oracle][ODBC SQL Server Wire Protocol driver][SQL Server] 'SA' ʧ
                                                                  {28000,NativeErr = 18456}
ORA-02063: preceding 2 lines from SQLEMR

重启emr数据库服务器后，可看到端口2433

再次查询正常
SQL> select * from t@sqlemr;

name
--------------------
aaa
aaa
bb