Navicat Premium 12连接Oracle数据库

Navicat Premium 12连接Linux搭建的Oracle数据库

环境说明：Centos 7.x、oracle 19c
设备ip：1921.68.120.139
目录说明：/opt/oracle/product/19c/dbhome_1/

步骤一、修改tnsnames.ora文件

# vim /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.139)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )

LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.139)(PORT = 1521))

步骤二、修改listener.ora文件

# vim /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.139)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = ORCLCDB)
  (SID_NAME = ORCLCDB)
  )
)

步骤三、新建一个oracle的连接


服务名需要与tnsnames.ora文件的SERVICE_NAME对应
如忘记密码可以重置密码：

# su - oracle
# sqlplus / as sysdba
SQL> alter   user  sys identified  by  123456;   
SQL> alter   user  system identified  by  123456; 

步骤四、报错说明
1、lsnrctl starus报错The listener supports no services

查看实例服务名称
SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
ORCLCDB

修改listener.ora文件
添加以下字段（上面步骤已添加）：
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
  (GLOBAL_DBNAME = ORCLCDB)
  (SID_NAME = ORCLCDB)
  )
)

2、lsnrctl starus报错Instance “ORCLCDB”, status UNKNOWN, has 1 handler(s) for this service…服务没有被注册上
其实是正常现象，实际状态是open

# vim /etc/hosts
#127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
#::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
127.0.0.1 localhost
192.168.120.139 localhost.localhost.localhostadmin localhost6 localhost6.localdomain6
# su - oracle
$ sqlplus / as sysdba
sql> alter system set local_listener='';
再重新注册服务
sql> alter system register;
SQL> select status from v$instance;
sql>exit;
$ lsnrctl status 

状态ready！

3、ORA-12541:TNS:no listener
（1）配置客户端tnsnames.ora（改为设备ip）
（2）配置服务端listener.ora（改为设备ip）
（3）重启一下监听：lsnrctl stop、lsnrctl start

4、数据库地址或其他信息突然换了，在旧的链接上编辑，一直提示 connection is being used
需要重新建立一个连接