SQL*Plus远程访问ORACLE (上）

原创张均云和恩墨 2022-11-22

670

一. 概述

在oracle的学习中，我们通常使用虚拟机搭建oracle数据库环境，并且把同一个环境用作客户端（client）和服务端（server)，但是这样的情况基本上不可能出现在生产环境上。生产环境中，通常是配置多个客户端通过网络来访问oracle服务端。在实现远程访问oracle之前，本文上篇会首先简单介绍了oracle网络的相关知识，包括三个重要的配置文件的内容和作用。关于实验操作可查看下篇。

1.1 实验环境

角色	操作系统	IP	软件版本
CLIENT	CentOS 7 x86	192.168.101.128	instantclient 12.2
SERVER	CentOS 7 x86	192.168.101.150	oracle 12c

1.2 客户端软件

远程连接需要借助软件，这里选择oracle官方提供的sqlplus客户端，下载链接如下：
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

需要下载对应版本的basic和sqlplus两个包，本文下载的是zip包

1.3 ORACLE NET

ORACLE NET是建立在通用的TCP和IPC之上的网络协议：

服务器端的listener（监听器）负责注册oracle服务
远程客户端通过oracle net访问oracle服务端的服务
以tcp协议为基础的ORACLE NET必须描述三个基本要素：
a.协议是TCP；
b.服务器的IP地址或主机名；
c.端口号

二. 配置文件

ORACLE NET配置文件都是文本形式的，可以通过netca实用程序生成、修改，也可以使用文本编辑器生成和修改
ORACLE NET配置文件默认路径: $ORACLE_HOME/network/admin
配置文件分三种

1.listner.ora --在服务器端的配置文件
2.tnsnames.ora --在客户端的配置文件
3.sqlnet.ora --描述连接方式配置文件

2.1 listener.ora

listener.ora是在服务器段的监听配置文件

2.1.1 查看监听器状态

[oracle@oracle1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 11:21:54

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                06-SEP-2022 17:19:44
Uptime                    68 days 18 hr. 2 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully

2.1.2 默认动态监听

当服务器端不存在listener.ora文件时，也可以启用默认的动态监听

移除/重命名监听参数文件

[oracle@oracle1 admin]$ mv listener.ora listener.ora.bak
[oracle@oracle1 admin]$ ls
listener.ora.bak  samples  shrept.lst  sqlnet.ora  tnsnames.ora  tnsnames.ora_CDB

重启、注册监听服务

[oracle@oracle1 admin]$ lsnrctl stop

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 11:28:52

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

 
[oracle@oracle1 admin]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 11:29:13

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-NOV-2022 11:29:37
Uptime                    0 days 0 hr. 0 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521)))
The listener supports no services
The command completed successfully


SYS@oradb> alter system register;

System altered.

再次查看监听状态

[oracle@oracle1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 11:31:02

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-NOV-2022 11:29:37
Uptime                    0 days 0 hr. 1 min. 49 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully

2.2 动态监听注册和静态监听注册

2.2.1 动态监听注册

在实例启动之后，LREG进程每分钟自动将服务名（service_name）注册到监听器中，也可以通过alter system register命令通知LRGE立刻注册
系统有一个默认的监听器叫做LISTENER，默认端口号时1521；使用它，不需要配置listener.ora配置文件也可以
动态注册要求实例至少启动到mounted，listner监听器才能注册成功

2.2.1.1 配置非默认端口的动态监听LSNR1

在listener.ora配置文件中描述自定义监听器

[oracle@oracle1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@oracle1 admin]$ vi
listener.ora      samples/          shrept.lst        sqlnet.ora        tnsnames.ora      tnsnames.ora_CDB
[oracle@oracle1 admin]$ vi listener.ora ---追加以下信息
LSNR1 =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1522))
     )

启动监听程序LSNR1

[oracle@oracle1 admin]$ lsnrctl start lsnr1


SYS@oradb> alter system register
;

[oracle@oracle1 admin]$ lsnrctl status lsnr1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 14:09:13

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     lsnr1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-NOV-2022 14:07:38
Uptime                    0 days 0 hr. 1 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/lsnr1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1522)))
The listener supports no services
The command completed successfully
---实例没有注册到新的lsnr1上 “The listener supports no services”

2.2.1.2 修改local_listener参数

使用非默认端口的动态监听程序的时候，需要修改local_listener参数

SYS@oradb> alter system set
  2  local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1)(PORT=1522))';

System altered.

SYS@oradb> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=o
                                                 racle1)(PORT=1522))
SYS@oradb> alter system register;

System altered.

查看监听器状态

[oracle@oracle1 admin]$ lsnrctl status lsnr1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 14:17:00

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     lsnr1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-NOV-2022 14:07:38
Uptime                    0 days 0 hr. 9 min. 46 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/lsnr1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1522)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully
--成功注册，但之前的默认1521的端口监听器没有注册

2.2.1.3 同时注册两个（多个）监听器

同时注册多个监听器需要使用到network_name:network_name被写在tnsnames.ora文件中,也被当作oracle实例的别名

在tnsnames.ora文件中添加信息：

[oracle@oracle1 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )




LISTENER_ORADB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1521))

LSNR1 =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle1)(PORT = 1522))

修改local_listener参数


System altered.

SYS@oradb> alter system register;

System altered.

SYS@oradb> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      ORACLE, LSNR1

查看状态–默认1521监听和新添加的1522监听都能成功注册

[oracle@oracle1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 14:29:52

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-NOV-2022 11:34:40
Uptime                    0 days 2 hr. 55 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully

[oracle@oracle1 admin]$ lsnrctl status lsnr1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 14:30:18

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle1)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     lsnr1
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-NOV-2022 14:07:38
Uptime                    0 days 0 hr. 23 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle1/lsnr1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1522)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
Service "oradbXDB" has 1 instance(s).
  Instance "oradb", status READY, has 1 handler(s) for this service...
The command completed successfully

2.2.2 静态监听注册

特点

1.静态注册必须要在listener.ora中描述
2.服务器启动了静态监听以后，可以远程登录启动数据库

内容

静态注册主要分为两部分内容：
1.网络部分： Protocal、host、port
2.服务名描述：GLOBAL_DBNAME:全局数据库名（静态注册特征）–其实就是service_names参数

示例：配置1523端口的静态监听程序LSNR2，实现对实例的静态注册

修改listener.ora文件–追加信息

LSNR2=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1523))
    )
  )
SID_LIST_LSNR2=
   (SID_LIST=
     (SID_DESC=
       (GLOBAL_DBNAME=oracle)
       (ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1)
       (SID_NAME=oradb)
     )
    )

启动LSNR2静态监听


LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-NOV-2022 15:13:43

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

....
....
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1523)))
Services Summary...
Service "oracle" has 1 instance(s).
  Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--成功注册静态监听

2.3 sqlnet.ora (ezconnect)

ezconnect实际上是easy connect，是连接数据库的一种方式
当数据库不存在sqlnet.ora文件的时候，默认支持ezconnect
当客户端存在sqlnet.ora文件的时候，使用轻松连接方式必须要配置sqlnet.ora文件，要加入ezconnect配置
优点是不需要网络配置文件描述，缺点是登录不方便

ezconnect使用语法：sqlplus &user_name/&password@&IP:&PORT/&service_name

示例：

[oracle@oracle1 ~]$ sqlplus scott/tiger@oracle1:1521/oracle

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 14 11:41:14 2022

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

Last Successful login time: Thu Nov 10 2022 09:33:46 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sqlnet.ora配置如下

[oracle@oracle1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

2.4 tnsname.ora

静态注册可以实现远程启动数据库
tnsnames.ora文件的作用就是让远程客户端通过该文件来识别连接服务器（包含了数据库服务器的信息）
示例：

oracle=
　(DESCRIPTION=
　　(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.101.150)(PORT=1521))
　(CONNECT_DATA=
　　(SERVER=DEDICATED)
　　(SERVICE_NAME=oracle)
　)
　)

三. 总结

实例是以service的形式对外提供服务的，监听器负责注册service
service_name对外屏蔽了实例和数据库的复杂描述
客户端tnsnames.ora的service_name与服务端listner.ora的静态监听中GLOBAL_DBNAME等同（show parameter service_names）
listener.ora中监听器的SID_NAME就是instance_name
一个$ORACLE_HOME下只能对应一个listener.ora和tnsname.ora
一个listener.ora可以描述多个网络连接符
一个tnsnames.ora可以描述多个网络连接符
一个listener.ora可以为多个数据库描述监听器
一个实例可以对应多个service_name

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle日常管理/体系学习（共5篇）

本合辑主要包含了oracle日常管理知识及本人对oracle体系的学习笔记