Oracle12cR2远程连接及常见问题处理

Oracle12cR2远程连接及常见问题处理

1. Oracle远程连接方式

1. 命令行连接

1. 方式一：先sqlplus/nolog,然后conn

sqlplus/nolog

conn 用户名/密码@//IP/数据库名 [as sysdba]

sqlplus sys/123456@//192.168.1.246/main.domain as sysdba

# sqlplus/nolog
C:\Users\jinshengyuan>sqlplus/nolog

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 23 15:48:20 2021

Copyright (c) 1982, 2016, Oracle.  All rights reserved.
# conn 命令以sys用户身份进行连接
SQL> conn sys/123456@//192.168.1.246/main.domain as sysdba
Connected.
SQL> select sysdate from dual;

SYSDATE
------------
23-11月-21

SQL>

2. 方式2

sqlplus 用户名/密码@//IP/数据库名 [as sysdba]

sqlplus sys/admin@//192.168.1.246/main.domain as sysdba

C:\Users\jinshengyuan>sqlplus sys/123456@//192.168.1.246/main.domain as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 23 15:50:50 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
------------
23-11月-21

SQL>

2. PL/SQLDeveloper使用IP及数据库名连接

3. PL/SQLDeveloper配置tnsnames.ora连接

1. 在客户端的tnsnames.ora文件中配tns
2. 然后使用PL/SQL进行连接，如下

tnsnames.ora内容如下:

main_domain_246=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.246)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = main.domain)
    )
  )

4. 其他客户端连接

如Intellij IDEA、DataGrip、Navicat Premium…等等，可选择自己喜欢的客户端连接即可

2. Oracle远程连接常见问题

1. 提示ORA-12170:TNS:连接超时

1. 查看1521 端口是否能通

# telnet 192.168.3.246 1521

C:\Users\dell>telnet 192.168.3.246 1521
正在连接192.168.3.246...无法打开到主机的连接。 在端口 1521: 连接失败

2. 1521端口不同说明防火墙没关，关闭防火墙后此问题解决

2. Ora-12514:TNS:监听程序当前无法…

1. 这种情况下一般是连接数据库时的用户名密码及数据库信息输入有误造成的
2. 还有一种情况是在服务器安装目录下sqlnet.ora中缺少TNSNAMES配置也会出现这种情况

sqlnet.ora文件配置如下：

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)
# Oracle11g安装后是没有下面这句的
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

3. ORA-15241:TNS

tnsnames.ora

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

MAIN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.246)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = main.domain)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.246)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MAIN)
      (PRESENTATION = RO)
    )
  )

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.246)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MAIN)
(PRESENTATION = RO)
)
)