一. 概述
在oracle的学习中，我们通常使用虚拟机搭建oracle数据库环境，并且把同一个环境用作客户端（client）和服务端（server)，但是这样的情况基本上不可能出现在生产环境上。生产环境中，通常是配置多个客户端通过网络来访问oracle服务端。本文下篇使用了官方提供的客户端软件，配置了远程客户端和服务端的网络配置文件，通过相关实验操作来实现远程访问ORACLE。
1.1 实验环境
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/oracle2.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/oracle2.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_README2.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 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 successfullyLSNR2状态 [oracle@oracle1 admin]$ lsnrctl status LSNR2 LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 23-NOV-2022 00:26:41 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 [root@amogdb ~]# sqlplus scott/tiger@oradb SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 22 17:33:32 2022 上次成功登录时间: 星期三 11月 23 2022 00:33:12 +08:00 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production ----成功连接
这里的别名指的是客户端tnsnames.ora中对目标数据库的描述名，例如之前配置的tnsnames中oradb就是别名。使用语法：sqlplus &user_name/&password@tnsname使用轻松连接远程连接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 上次成功登录时间: 星期三 11月 23 2022 00:31:20 +08:00 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production4.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 已连接到空闲例程。注意一定要使用服务器静态监听对应的端口，否则会出现以下类似报错： [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 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