plsql同时连接本地Oracle和远程Oracle的方法 遇到问题12514 12541

本文参考:https://blog.csdn.net/qq_26230421/article/details/79047527

后面有自己遇到的坑

step1 复制network文件

安装两个版本的Oracle

完整版本路径:C:\app\Administrator\product\11.2.0\dbhome_1

客户端随便解压到一个路径，我的是放在C:\app\Administrator\instantclient_11_1了。

然后把oracle中的C:\app\Administrator\product\11.2.0\dbhome_1里面的整个network文件夹复制，

粘贴到客户端文件夹C:\app\Administrator\instantclient_11_1下面.

step2 修改配置文件

附上我的两个目录下的4个ora文件:

C:\app\Administrator\instantclient_11_1\NETWORK\ADMIN 下的listener.ora

# listener.ora Network Configuration File: C:\app\Administrator\instantclient_11_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = jhorcl)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (GLOBAL_DBNAME = jhorcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\Administrator

C:\app\Administrator\instantclient_11_1\NETWORK\ADMIN 下的tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\Administrator\instantclient_11_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jhorcl)
    )
  )
 
LISTENER_JHORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
 
JHORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jhorcl)
    )
  )

#新增 @ 标识符，用于区分本地与远程oracle

@ESOP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jhorcl)
    )
  )

C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN 下的listener.ora

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = jhorcl)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (GLOBAL_DBNAME = jhorcl)
    )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = C:\app\Administrator

C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN 下的tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jhorcl)
    )
  )
LISTENER_JHORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
JHORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0UNT43NLBQ6C4P5)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jhorcl)
    )
  )

step3 配置OCI

不登录plsql，直接按Cancel，进入后在Tools--->Preferences---->Connection中配置OCI为32位客户端的OCI

C:\app\Administrator\instantclient_11_1\oci.dll

step4 环境变量配置

添加前2个系统变量：

1. 变量名：TNS_ADMIN   

  变量值：C:\app\Administrator\instantclient_11_1\NETWORK\ADMIN

2. 变量名：NLS_LANG

    变量值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

3. 变量名：Oracle_home

    变量值：C:\app\Administrator\product\11.2.0\dbhome_1

4. Path中配置加入：

C:\app\Administrator\product\11.2.0\dbhome_1\bin;C:\app\Administrator\instantclient_11_1;

step5 重启Oracle相关服务

重启服务OracleDBConsolejhorcl,OracleOraDb11g_home1TNSListener,OracleServiceJHORCL,如果登录失败则重启电脑

step6：登录plsql！！！

当看到Database下拉框中有本地的JHORCL以及远程的@ESOP就算成功了！！！