Oracle RAC重启数据库后PL/SQL连接报错ORA-12514：listener does not currently know of service requested in connect

最新推荐文章于 2024-08-22 15:38:56 发布

shayuwei

最新推荐文章于 2024-08-22 15:38:56 发布

阅读量773

点赞数

分类专栏：笔记 Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/shayuwei/article/details/103766867

版权

笔记同时被 2 个专栏收录

164 篇文章 5 订阅

订阅专栏

Oracle

129 篇文章 7 订阅

订阅专栏

问题描述

数据库版本：11.2.0.4 两节点RAC
操作系统版本：Redhat linux6.10
根据业务需求，重启了数据库，但是PL/SQL确报错ORA-12514。
1.检查实例状态：

[oracle@testpdb1 ~]$ srvctl status database -d racpdb
Instance racpdb1 is running on node testpdb1
Instance racpdb2 is running on node testpdb2

2.检查监听状态：

[oracle@testpdb1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-12月-2019 14:49:14

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-11月-2019 20:14:17
Uptime                    29 days 18 hr. 34 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/testpdb1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.206)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.207)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racpdb" has 1 instance(s).
  Instance "racpdb1", status READY, has 1 handler(s) for this service...
Service "racpdbXDB" has 1 instance(s).
  Instance "racpdb1", status READY, has 1 handler(s) for this service...
The command completed successfully

3.检查tns文件

[oracle@testpdb1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RACPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.210)(PORT = 1521))
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.207)(PORT=1521))
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.209)(PORT=1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = racpdb)
    )
  )

4.检查监听服务：

[oracle@testpdb1 admin]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-12月-2019 15:20:28

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:46677 refused:0 state:ready
         LOCAL SERVER
Service "racpdb" has 1 instance(s).
  Instance "racpdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:415 refused:0 state:ready
         LOCAL SERVER
Service "racpdbXDB" has 1 instance(s).
  Instance "racpdb1", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: testpdb1, pid: 1383>
         (ADDRESS=(PROTOCOL=tcp)(HOST=testpdb1)(PORT=17388))
The command completed successfully

5.确认scan_listner和scan状态

[grid@testpdb1 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node testpdb1
[grid@testpdb1 ~]$ srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node testpdb1

6.确认scan监听

[grid@testpdb1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-DEC-2019 15:24:41

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-NOV-2019 20:09:00
Uptime                    29 days 19 hr. 15 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0.4/grid/log/diag/tnslsnr/testpdb1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=1521)))
The listener supports no services
The command completed successfully

DB实例没有注册到scan监听，所以应用连接时会报错 ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

问题解决

查看remote_listener参数，如果没有问题，手动注册：alter system register;
如果没有解决，那么就重新注册：

[grid@testpdb1 ~]$ srvctl config scan
SCAN name: test-pro, Network: 1/192.168.1.0/255.255.255.0/em3
SCAN VIP name: scan1, IP: /test-pro/192.168.1.210

SQL>  alter system set remote_listener='';

System altered.

SQL> alter system register;

System altered.

SQL> show parameter remote             

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode         string     TIMESTAMP
remote_listener              string
remote_login_passwordfile         string     EXCLUSIVE
remote_os_authent             boolean     FALSE
remote_os_roles              boolean     FALSE
result_cache_remote_expiration         integer     0
SQL> alter system set remote_listener='test-pro:1521';   # SCAN ip

System altered.

SQL> show parameter remote   

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode         string     TIMESTAMP
remote_listener              string     test-pro:1521
remote_login_passwordfile         string     EXCLUSIVE
remote_os_authent             boolean     FALSE
remote_os_roles              boolean     FALSE
result_cache_remote_expiration         integer     0


[grid@testpdb1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-DEC-2019 15:27:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-NOV-2019 20:09:00
Uptime                    29 days 19 hr. 18 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0.4/grid/log/diag/tnslsnr/testpdb1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.210)(PORT=1521)))
Services Summary...
Service "racpdb" has 2 instance(s).
  Instance "racpdb1", status READY, has 1 handler(s) for this service...
  Instance "racpdb2", status READY, has 1 handler(s) for this service...
Service "racpdbXDB" has 2 instance(s).
  Instance "racpdb1", status READY, has 1 handler(s) for this service...
  Instance "racpdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

经过测试，这个参数在实例重启后需要再次重新注册。