SQL*Plus远程访问ORACLE (下）

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

648

一. 概述

在oracle的学习中，我们通常使用虚拟机搭建oracle数据库环境，并且把同一个环境用作客户端（client）和服务端（server)，但是这样的情况基本上不可能出现在生产环境上。生产环境中，通常是配置多个客户端通过网络来访问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包

二. 软件安装(远程客户端）

2.1 创建解压目录

[root@amogdb opt]# mkdir -p /opt/software/oracle

2.2 上传安装包

total 33040
-rwxrw-rw-. 1 root root 32917466 Nov 14 16:11 instantclient-basiclite-linux.x64-12.2.0.1.0.zip
-rwxrw-rw-. 1 root root   904309 Nov 14 16:27 instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
[root@amogdb software]# pwd
/opt/software/oracle

2.3 解压

[root@amogdb oracle]# unzip instantclient-basiclite-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-basiclite-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/adrci
  inflating: instantclient_12_2/BASIC_LITE_README
  inflating: instantclient_12_2/genezi
  inflating: instantclient_12_2/libclntshcore.so.12.1
  inflating: instantclient_12_2/libclntsh.so.12.1
  inflating: instantclient_12_2/libipc1.so
  inflating: instantclient_12_2/libmql1.so
  inflating: instantclient_12_2/libnnz12.so
  inflating: instantclient_12_2/libocci.so.12.1
  inflating: instantclient_12_2/libociicus.so
  inflating: instantclient_12_2/libocijdbc12.so
  inflating: instantclient_12_2/libons.so
  inflating: instantclient_12_2/liboramysql12.so
  inflating: instantclient_12_2/ojdbc8.jar
  inflating: instantclient_12_2/uidrvci
  inflating: instantclient_12_2/xstreams.jar
[root@amogdb software]# unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
Archive:  instantclient-sqlplus-linux.x64-12.2.0.1.0.zip
  inflating: instantclient_12_2/glogin.sql
  inflating: instantclient_12_2/libsqlplusic.so
  inflating: instantclient_12_2/libsqlplus.so
  inflating: instantclient_12_2/sqlplus
  inflating: instantclient_12_2/SQLPLUS_README

2.4 配置环境变量

[root@amogdb oracle]# cd ~
[root@amogdb ~]# vi .bash_profile

export ORACLE_HOME=/opt/software/oracle/instantclient_12_2
export ORACLE_SID=oradb
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
#export NLS_LANG='simplified chinese_china'.ZHS16GBK
export NLS_LANG='simplified chinese_china'.AL32UTF8
export PATH=$ORACLE_HOME:$PATH

使环境变量生效

[root@amogdb ~]# source .bash_profile
[root@amogdb ~]# echo $ORACLE_HOME
/opt/software/oracle/instantclient_12_2

三.配置文件

3.1 远程客户端

上篇提到过，远程客户端需要配置tnsnames.ora文件，来完整的描述需要连接的oracle服务器的详细信息，配置如下：

[root@amogdb oracle]# mkdir -p network/admin
[root@amogdb oracle]# vim tnsnames.ora
[root@amogdb oracle]# cat tnsnames.ora
oradb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.150)(PORT = 1522)) #通信协议，远程数据库所在主机，端口信息
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb) # 远程服务器上oracle的服务名
    )
  )

需要注意的是，我们要把tnsnames.ora以及sqlnet.ora放在ORACLE_HOME/network/admin下（配置了环境变量），如果环境变量没有配置TNS_ADMIN，系统会默认到ORACLE_HOME/network/admin文件下寻找tnsnames.ora文件。

3.2 ORACLE服务端

服务器端需要配置listener.ora文件，使用监听来注册oracle服务
配置文件如下：

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

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

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


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.1/dbhome_1)
       (SID_NAME=oradb)
     )
    )

其中LISTENER是oracle默认动态监听器，端口为1522；LSNR1是新建动态监听器，端口为1522；LSNR2是新建静态监听器，端口号为1523

3.3 查看ORACLE服务端监听状态

LSNR1状态

[oracle@oracle1 admin]$ lsnrctl status LSNR1

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-NOV-2022 00:25:39

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                23-NOV-2022 00:24:16
Uptime                    0 days 0 hr. 1 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

LSNR2状态


[oracle@oracle1 admin]$ lsnrctl status LSNR2

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-NOV-2022 00:26:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle1)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     LSNR2
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                23-NOV-2022 00:16:43
Uptime                    0 days 0 hr. 10 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/lsnr2/alert/log.xml
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

四. 远程连接ORACLE

4.1 使用动态监听远程连接oracle

使用别名远程登录oracle
这里的别名指的是客户端tnsnames.ora中对目标数据库的描述名，例如之前配置的tnsnames中oradb就是别名。使用语法：sqlplus &user_name/&password@tnsname


[root@amogdb ~]# sqlplus scott/tiger@oradb

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 22 17:33:32 2022

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

上次成功登录时间: 星期三 11月 23 2022 00:33:12 +08:00

连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
----成功连接

使用轻松连接远程连接oracle

注意的是当客户端存在tnsnames.ora的时候，需要在sqlnet.ora文件中添加轻松连接关键字才能使用轻松连接。配置方法见上篇。


[root@amogdb ~]# sqlplus scott/tiger@192.168.101.150:1522/oracle

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 22 17:28:20 2022

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

上次成功登录时间: 星期三 11月 23 2022 00:31:20 +08:00

连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

4.2 使用静态监听远程启动数据库

在上篇提到过，动态监听必须要实例启动到mount阶段才能远程登录。但是实例不需要启动,静态监听也能注册，所以可以利用这一点实现，远程启停示实例（需要使用sys明文登录）

4.2.1 服务器端关示例

SYS@oradb> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

4.2.2 配置（修改）tnsnames.ora文件

之前配置的静态监听是对应的1523端口，那么tnsnames.ora文件中对实例的描述也必须要端口对应，否则会报错。
修改或配置tnsnames.ora示例：


[root@amogdb admin]# cat tnsnames.ora
oradb =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.150)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracle)
    )
  )

4.2.3 远程连接示例


[root@amogdb admin]# sqlplus sys/oracle@oradb as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 22 18:00:37 2022

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

已连接到空闲例程。

注意一定要使用服务器静态监听对应的端口，否则会出现以下类似报错：


[root@amogdb ~]# sqlplus sys/oracle@192.168.101.150:1522/oracle as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 22 18:02:19 2022

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


请输入用户名:

4.2.4 远程启动数据库


SQL> startup
ORACLE 例程已经启动。

Total System Global Area 1577058304 bytes
Fixed Size                  8621136 bytes
Variable Size            1207960496 bytes
Database Buffers          352321536 bytes
Redo Buffers                8155136 bytes
数据库装载完毕。
数据库已经打开。
SQL> select instance_name,status from v$instance;

INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
oradb
OPEN


SQL>
--启动成功

最后修改时间：2022-11-24 10:15:40

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

关注作者

文章被以下合辑收录

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

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