I found that I can connect the database via VIP or SCAN, but not Public IP. I checked the endpoint listener. It did not listen the public IP.
[grid@primary01 ~]$ cat $ORACLE_HOME/network/admin/endpoints_listener.ora
#Backup file is /u01/app/12.1.0/grid/network/admin/endpoints_listener.ora.bak.primary01 line added by Agent
LISTENER_PRIMARY01=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)(IP=FIRST)))) # line added by Agent
It was listening 127.0.0.1, no wonder I can't connect to the database via public IP. After rethinking the symptom, it also reminded me that it could be a hostname resolution problem.
So I switched the order of name resolution temporarily as following:
[root@primary01 ~]# vi /etc/nsswitch.conf
...
hosts: dns files
#hosts: files dns
...
[grid@primary01 ~]$ srvctl stop listener
[grid@primary01 ~]$ srvctl start listener
[grid@primary01 ~]$ cat $ORACLE_HOME/network/admin/endpoints_listener.ora
#Backup file is /u01/app/12.1.0/grid/network/admin/endpoints_listener.ora.bak.primary01 line added by Agent
LISTENER_PRIMARY01=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.89)(PORT=1521)(IP=FIRST)))) # line added by Agent
Yes, it worked. But normally, we don't need to lookup our own hostname by external DNS. So, we still seek for file resolutions (/etc/hosts).
First of all, I made sure /etc/hosts can be read by others.
[root@primary01 ~]# ll /etc/hosts
-rw-r--r-- 1 root root 737 Dec 15 14:49 /etc/hosts
Then I removed the hostname and its FQDN from 127.0.0.1 line, which is a wrong mapping.
[root@primary01 ~]# vi /etc/hosts
# Public
192.168.0.89 primary01 primary01.example.com
...
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
#127.0.0.1 primary01 primary01.example.com localhost localhost.localdomain localhost4 localhost4.localdomain4
...Don't forget to rearrange the order of name resolution in /etc/nsswitch.conf, and then restart the endpoint listener:
[root@primary01 ~]# vi /etc/nsswitch.conf
...
#hosts: dns files
hosts: files dns
...
[grid@primary01 ~]$ srvctl stop listener
[grid@primary01 ~]$ srvctl start listener
[grid@primary01 ~]$ cat $ORACLE_HOME/network/admin/endpoints_listener.ora
#Backup file is /u01/app/12.1.0/grid/network/admin/endpoints_listener.ora.bak.primary01 line added by Agent
LISTENER_PRIMARY01=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=primary01-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.89)(PORT=1521)(IP=FIRST)))) # line added by Agent
Yes, we are back.
Further reading: Why Don’t We Explicitly Bind VIP and SCAN into NIC
Hi Chen, this is w.r.t. “How to Resolve Endpoint Listener is Not Listening to Public IP”
I am facing issue when I have Oracle EBS R12 on Amazon EC2. I can not connect to the database listner from outside Amazon EC2.
I found your blog and have a question. In your /etc/hosts, you have entry for 192.168.0.89. This doesn’t seem to be external (or public IP).
Were you able to connect to DB from remote laptop? Thanks for your help
As I said early in this post, I was able to connect to the database via VIP or SCAN IP, but not the public IP, which does mean that I was from an external client, a different IP address.
“192.168.0.89” is the public IP of the server itself, the first node of RAC. In other words, the hostname “primary01” of the server should be resolved as this IP, not the loopback (127.0.0.1).