Oracle Express Edition - Can not connect remotely (plus others)

Question

I'm unable to make a remote connection to an Oracle XE install (through TOAD / SQL Developer). Here's the deal.

I set up a new server (windows 2003). The goal was to make a new image with several applications preinstalled, Oracle XE being one of them. Got Oracle installed no problem, connected locally, remotely and had access to the web interface - the one found at http://127.0.0.1:8081/apex (Note: I changed the port of the web interface manually as we are running our Tomcat dev environment on 8080).

So, everything is going swimmingly, I create the image, wipe the machine and put the newly created image on there. Everything works except Oracle. After much digging, I update the tnsnames.ora file, add environment variables ORACLE_HOME and ORACLE_SID, so I can connect locally. I still can't connect remotely and have completely lost access to the web interface. I've spent 2 days on google and I'm all out of ideas. Please HELP!

Answer 1

Did you check if you have open port 1521 on firewall?

Answer 2

From the Web Interface, you have to login as a DBA [I think], and there is an option in the administration section where you have to activate "Enable connections from outside".

EDIT: Full Path : WebInterface -> Administration -> [drop down menu] Manage HTTP Access -> Available from local server and remote clients.

Answer 3

ManuelF relates to access through the web front end, but isn't relevant to local access through the web front end.

You don't say how hostname/address is being handled, or how you are connecting locally. If you are doing SQLPLUS / AS SYSDBA then you are attaching directly. If you are doing SQLPLUS user/pass@XE then you are attaching via the listener. If the former works and the latter doesn't, look for a file called listener.ora and check the hostname in there. If you still have problems, try

sqlplus user/[email protected]:1521/XE

If that works, then the problem is the tnsnames.ora.

On the remote access, if your image is installed on 'box a', and you want access from 'box b', can you ping 'box a' from 'box b'. Then try, on box_b

sqlplus user/pass@box_a:1521/XE

This assumes you have an Oracle client, with sql*plus, on box_b. If that works, you'll need to look at the tnsnames.ora file on box_b. If you don't have an Oracle client on box_b, Oracle's SQL Developer can connect through the JDBC thin driver (which doesn't need any Oracle client), and for that you'll need to specify the host, listener port (generally 1521) and sid (XE for express edition)