本地访问VM中orcle 12c 时报错 ora12170

ゝ一纸荒年。 2023-06-20 10:43 62阅读 0赞

###  ###

*   *  step 1 ping vm机 看能否成功
     *  step 2 tnsping 远程实例
     *  step 3 检查服务器即虚拟机中的监听状态
     *  step4 配置监听文件 listener.ora
     *  step 5 修改服务器 tnsnames.ora
     *  step 6 重启服务器监听
     *  step 7 配置本地tnsnames.ora
     *  step 8 检查服务器防火墙
     *  step 9 再次连接 成功
     *  总结

虚拟机中安装好oracle12c 后，在本地通过sqlplus连接时报错ORA-127170 TNS:Connect timeout occurred![在这里插入图片描述][20191210002127739.png]

## step 1 ping vm机 看能否成功 ##

![在这里插入图片描述][20191210002533964.png]  
看到正常ping通

## step 2 tnsping 远程实例 ##

tnsping  orcl

看到本地无法ping通远程的实例监听 ，还报错TNS-12541: TNS:no listener  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]

## step 3 检查服务器即虚拟机中的监听状态 ##

lsnrctl
    status

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]

看到服务器监听也是正常的。

## step4 配置监听文件 listener.ora ##

cd $ORACLE_HOME/network/admin
    vi  listener.ora

修改后内容如下 把localhost 改为虚拟机IP，同时加上SID\_LIST\_LISTENER，  
注意SID\_NAME = orcl 是cdb，而SID\_NAME = orclpdb是pdb容器

LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.8.128 )(PORT = 1521))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
      )
    SID_LIST_LISTENER=
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = orcl)
          (ORACLE_HOME = /home/oracle/app/oracle/product/12.2.0/dbhome_1)
          (SID_NAME = orcl)
        )
            (SID_DESC =
          (GLOBAL_DBNAME = orclpdb)
          (ORACLE_HOME = /home/oracle/app/oracle/product/12.2.0/dbhome_1)
          (SID_NAME = orcl)
        )
    )

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]

## step 5 修改服务器 tnsnames.ora ##

vi tnsnames.ora  
添加如下内容

ORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.128)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )
    ORCLPDB =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orclpdb)
        )
      )

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 3]

## step 6 重启服务器监听 ##

lsrnctl stop
    lsrnctl start

## step 7 配置本地tnsnames.ora ##

本地 tnsnames.ora文件在TNS\_ADMIN 环境变量中  
orcl\_128 =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.128)(PORT = 1521))  
)  
(CONNECT\_DATA =  
(SERVICE\_NAME= orcl)  
)  
(HS = OK)  
)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 4]

但是再次ping orcl\_128 还是报一样的错  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 5]

就在快绝望时，检查服务器防火墙，才发现1521端口是关闭的

## step 8 检查服务器防火墙 ##

firewall-cmd --zone=public --query-port=1521/tcp

![在这里插入图片描述][20191210004045870.png]

firewall-cmd --permanent --zone=public --add-port=1521/tcp
    systemctl restart firewalld.service # 重启防火墙 使设置生效

–permanent：表示设置为持久； 如果去掉–permanent： 表示临时生效  
再次检查1521 端口，防火墙已经开放该断开

firewall-cmd --zone=public --query-port=1521/tcp

![在这里插入图片描述][20191210004433340.png]

## step 9 再次连接 成功 ##

再次在本地ping 时 终于能ping通  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 6]  
然后也能正常连接

conn sys/123456@192.168.8.128:1521/orclpdb as sysdba
    select sys_context('userenv','con_name') from dual;

![在这里插入图片描述][20191210004848207.png]

## 总结 ##

1、先本地ping服务器ip看能否ping通  
2、tnsping 实例，如果ping不通 需要检查下面  
2.1 检查服务器监听是否启动  
2.2 检查服务器防火墙是否开放1521端口  
2.3 检查下服务器两个监听文件tnsnames.ora与listener.ora  
2.4 检查本地

[20191210002127739.png]: https://img-blog.csdnimg.cn/20191210002127739.png
[20191210002533964.png]: https://img-blog.csdnimg.cn/20191210002533964.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191210002815961.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191210003035341.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191210003313433.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2019121000355720.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191210003715347.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2019121000390640.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[20191210004045870.png]: https://img-blog.csdnimg.cn/20191210004045870.png
[20191210004433340.png]: https://img-blog.csdnimg.cn/20191210004433340.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191210004732835.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doYW5kZ2Ro,size_16,color_FFFFFF,t_70
[20191210004848207.png]: https://img-blog.csdnimg.cn/20191210004848207.png