Oracle Database in Google Compute Engine

Question

I have installed Oracle 11g Standard Edition in a Google Compute Engine (GCE) virtual machine (Windows Server 2012 Datacenter). I have created an inbound and outbound rule for tcp:1521 in the Windows Firewall. At the same time, I have created a tcp:1521 firewall rule for GCE network.

When I connect to my database using SQL Developer from localhost everything works. However, if I try to connect to the database from a remote host (also using SQL Developer) I obtained an error:

Status: Failure - Test failed: IO Error: The Network Adapter could not establish the connection.

I successfully ping the virtual machine that hosts the database from a remote host, nonetheless when I telnet it the connection fails:

Connecting To <host-ip>... Could not open connection to the host, on port 1521: Connect failed

Moreover, nmap states that port 1521 is filtered in the virtual machine.

What am I missing? Thanks for your help.

Answer 1

These are some steps that you can take to troubleshoot and resolve this issue:

First, for troubleshooting purposes, turn off firewall of the Windows VM's:

netsh firewall set opmode mode=Disable

You can turn it on later using this command:

netsh firewall set opmode mode=Enable

Make sure your remote IP address is listed in the Source filter of your GCE firewall rule for tcp:1521. If the GCE firewall rule has a Target tag, your Windows VM instance has to be tagged with the same label as well, so this firewall rule applies to it.

If the firewall rule is properly configured, the other point that you want to check is the Oracle Net Listener Configuration. As your VM's internal IP address is not a static IP address and may change, configure TCP/IP or TCP/IP with SSL and enter the host name of the computer in the Host field of Listening Protocol Addresses.

Answer 2

I research a little bit more taking into account your answer and this is what we should consider:

1. Configure Windows Server Firewall: Go to Control Panel -> System and Security -> Windows Firewall -> Advanced settings. Then create a new inbound rule with the follow features:

Rule type: Port

Protocol: TCP

Port number: 1521 (unless you have specified a different one at your Oracle database)

Action: Allow the connection

Profile: Domain + Private + Public (or the one that applies to your case)

Name: Oracle DB (or any name you want)

Once you have finished, create an outbound rule with the same features specified for the inbound rule.

---

2. Configure Google Compute Engine Network Firewall: At Google Compute Engine (GCE) console go to Networking -> Firewall rules. Then click on New firewall rule, and configure it:

Name: oracle-db (or any name you want)

Network: default (or the one where you have deployed your virtual machine. Verify it form VM instances dashboard)

Source filter: Allow from any source (0.0.0.0/0) (WARN: you should select IP ranges if you just want to enable a host or a set of hosts. Then specify your domain or IP address)

Allowed protocols and ports: tcp:1521 (or the one that applies to your case - the same that you have defined for your Windows Firewall rules)

Finally, click on Create.

---

3. Configure Oracle Net Listener: By default Oracle database does not accept remote connections. It just accepts localhost requests. Therefore, at your Oracle database server open a CMD as administrator. We will stop the database net listener while executing:

lsnrctl STOP

Now, go to the Oracle installation path ORACLE_PATH (usually: ORACLE_PATH = C:\app\<user_name>). Then navigate to: ORACLE_PATH\product\11.2.0\dbhome_1\NETWORK\ADMIN. There you will find two important files: listener.ora and tnsnames.ora. Open listener.ora and change all LOCALHOST ocurrences by your server hostname (WARN: no the IP address, instead the server HOSTNAME).

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = <server_hostname>)(PORT = 1521))
    )
  )

Save your changes. Then, open the tnsnames.ora file. And do the same for your services:

YOUR_SERVICE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <server_hostname>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = your_service_name)
    )
  )

LISTENER_YOUR_SERVICE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = <server_hostname>)(PORT = 1521))

Save your changes. Finally, we should restart the net listener. So go to the CMD as an administrator and execute the following command:

lsnrctl START

Open your browser and type: http://localhost:1158/em. Your database and listener should be running. Now you can connect from remote hosts.

For more information, please refer to: http://docs.oracle.com/cd/B28359_01/network.111/b28316/listenercfg.htm