远程连接CentOS7.5.1804 Minimal中Oracle12c

目录

一、设置远程访问

1、备份listener.ora和tnsnames.ora文件

[root@oracle ~]# cd /opt/oracle/12c/network/admin
[root@oracle admin]# ll
总用量 16
-rw-r--r--  1 oracle oinstall 310 7月   8 22:09 listener.ora
drwxr-xr-x. 2 oracle oinstall  64 7月   1 01:09 samples
-rw-r--r--. 1 oracle oinstall 373 10月 31 2013 shrept.lst
-rw-r--r--  1 oracle oinstall 171 7月   8 22:06 sqlnet.ora
-rw-r-----  1 oracle oinstall 301 7月   8 22:36 tnsnames.ora
[root@oracle admin]# cp listener.ora listener.ora.bak
[root@oracle admin]# cp tnsnames.ora tnsnames.ora.bak
[root@oracle admin]# ll
总用量 24
-rw-r--r--  1 oracle oinstall 310 7月   8 22:09 listener.ora
-rw-r--r--  1 root   root     310 7月  19 21:08 listener.ora.bak
drwxr-xr-x. 2 oracle oinstall  64 7月   1 01:09 samples
-rw-r--r--. 1 oracle oinstall 373 10月 31 2013 shrept.lst
-rw-r--r--  1 oracle oinstall 171 7月   8 22:06 sqlnet.ora
-rw-r-----  1 oracle oinstall 301 7月   8 22:36 tnsnames.ora
-rw-r-----  1 root   root     301 7月  19 21:09 tnsnames.ora.bak
[root@oracle admin]# 

2、修改listener.ora

[root@oracle /]# vi /opt/oracle/12c/network/admin/listener.ora

将文件修改成

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

修改前

修改后

3、修改tnsnames.ora

[root@oracle /]# vi /opt/oracle/12c/network/admin/tnsnames.ora

将文件修改成

LISTENER_ORCL = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.129)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

修改前

修改后

4、查看下lsnrctl状态，看是否是lsnrctl监听的问题

[root@oracle admin]# su - oracle
上一次登录：四 7月 19 17:55:41 CST 2018pts/1 上
[oracle@oracle ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-JUL-2018 18:49:32

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.129)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                19-JUL-2018 17:52:43
Uptime                    0 days 0 hr. 57 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/12c/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@oracle ~]$ 

发现提示下列错误

The listener supports no services
The command completed successfully

5、依次执行下列命令

保证执行命令的用户是oracle用户，否则切换到oracle用户

[oracle@oracle ~]$ su - oralce

先关闭监听服务

[oracle@oracle ~]$ lsnrctl stop  #关闭监听服务

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-JUL-2018 18:59:49

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.129)(PORT=1521)))
The command completed successfully
[oracle@oracle ~]$ 

开启监听服务

[oracle@oracle ~]$ lsnrctl start  #开启监听服务

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-JUL-2018 19:01:32

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

Starting /opt/oracle/12c/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/oracle/12c/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.129)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.129)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                19-JUL-2018 19:02:05
Uptime                    0 days 0 hr. 0 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/12c/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.8.129)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle ~]$ 

登入

[oracle@oracle ~]$ sqlplus / as sysdba  #登入

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 19 19:51:02 2018

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

Connected to an idle instance.

SQL> 

开启数据库服务

SQL> startup  #开启数据库服务
ORACLE instance started.

Total System Global Area  763363328 bytes
Fixed Size          2929064 bytes
Variable Size         574623320 bytes
Database Buffers      180355072 bytes
Redo Buffers            5455872 bytes
Database mounted.
Database opened.
SQL> 

注册

SQL> alter system register;

System altered.

SQL> 

登出

SQL> quit;
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

查看监听状态

[oracle@oracle ~]$ lsnrctl status #查看监听状态

二、设置远程访问

1、下载Oracle Client

下载地址：http://www.oracle.com/technetwork/topics/winx64soft-089540.html

2、解压客户端

解压安装之后我的home目录是D:\tools\instantclient_12_1（可自己定义安装目录）
在该目录下创建文件夹network/admin

3、从CentOS7服务器中导出tnsnames.ora

4、添加环境变量

添加一个环境变量，名为TNS_ADMIN，值为tnsnames.ora文件所在路径D:\tools\instantclient_12_1\network\admin，plsql通过这个找到orcl连接字符串

添加一个环境变量
NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK

5、下载PL/SQL Developer

下载地址：https://www.allroundautomations.com/bodyplsqldevreg.html

6、安装PL/SQL Developer

安装步骤不复杂，自己去百度，在此不多加赘述

7、PL/SQL Developer连接设置

打开PLSQL,不用登陆,工具-首选项-ORACLE-连接（）:

勾选检查连接

Oracle主目录： D:\tools\instantclient_12_1

OCI库：D:\tools\instantclient_12_1\oci.dll

8、连接

9、连接成功后验证

至此远程连接CentOS7.5.1804 Minimal中Oracle12c成功完成