Oracle RAC异机恢复后PL/SQL连接报错ORA-12514：无监听程序

数据库版本：11.2.0.4 两节点RAC
操作系统版本：RedHat linux 6.10

问题描述

Oracle RAC异机恢复后，通过PL/SQL连接报错 ORA-12514：无监听程序
按照以往的经验，检查监听文件、监听状态、scan监听状态等，都没有问题，但是依旧无法连接。
最后经过排查和查阅材料，原来是因为local_listener参数没有设置：

SQL> show parameter LISTENER   

NAME                             TYPE                 VALUE
----------------------    -------------- ----------------------
listener_networks            string

local_listener               string              (ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))

remote_listener              string              testpro.XX.XXX.cn:1521

• 设置local_listener参数
  ！注意，local listener的host是vip

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.5)(PORT=1521))))' sid='racpdb1';

System altered.

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=1521))))' sid='racpdb2';

System altered.

• remote listener参数也需要修改

SQL> alter system set remote_listener='';

System altered.

SQL> alter system register;

System altered.

SQL> alter system set remote_listener='test-pro.XX.XXX.cn:1521';

System altered.

SQL> show parameter LISTENER  

NAME                     TYPE            VALUE
------------------------------------ ---------------------- ------------------------------
listener_networks            string
local_listener               string            (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.5)(PORT=1521))))
remote_listener              string            test-pro.XX.XXX.cn:1521

• 查看scan 监听在哪个节点上

[root@new-rac2 ~]# su - grid
[grid@new-rac2 ~]$ srvctl status scan            
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node new-rac2

• 查看scan监听状态

[grid@new-rac2 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-DEC-2019 16:21:08

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                03-DEC-2019 16:05:38
Uptime                    0 days 0 hr. 15 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0/grid/log/diag/tnslsnr/new-rac2/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.8)(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

• 查看实例状态

[grid@new-rac2 ~]$ srvctl status database -d racpdb
Instance racpdb1 is running on node new-rac1
Instance racpdb2 is running on node new-rac2

• 查看tns文件

[grid@new-rac2 ~]$ exit
logout
[root@new-rac2 ~]# su - oracle
[oracle@new-rac2 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RACPDB =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521))          # SCAN IP
   (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.7)(PORT=1521))                 # node1的VIP
   (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.5)(PORT=1521))                 # node2的VIP
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = racpdb)       # 注意，在异机恢复后需要修改service name
  )
)