目录
一、设置远程访问
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成功完成