问题描述
数据库版本:11.2.0.4 两节点RAC
操作系统版本:Redhat linux6.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:1521
local listener的host是应该vip
- 注册
local_listener
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.215)(PORT=1521))))' sid='racpdb1';
System altered.
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.217)(PORT=1521))))' sid='racpdb2';
System altered.
SQL> alter system set remote_listener='';
System altered.
SQL> alter system register;
System altered.
SQL> alter system set remote_listener='test-pro: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.215)(PORT=1521))))
remote_listener string test-pro: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
- 在节点2查看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.218)(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
- 查看实例状态及tms文件
[grid@new-rac2 ~]$ srvctl status database -d racpdb
Instance racpdb1 is running on node new-rac1
Instance racpdb2 is running on node new-rac2
[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.218)(PORT = 1521)) # SCAN IP
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1217)(PORT=1521)) # node1的VIP
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.215)(PORT=1521)) # node2的VIP
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racpdb) # 注意,在异机恢复后需要修改service name
)
)