On Oracle XDB repository, FTP, WebDAV and firewall issues

After recovering the situation as described in my previous posting, i was finally ready to access my Oracle XE XDB repository via FTP or at least, that was what i thought...

Enabling XDB access via FTP protocol is just a matter of setting a valid port value in the ftp-port parameter inside xdbconfig.xml (theoretically). Typically this value will not be 21, the standard FTP port, because that would conflict with the main FTP server, if any, so the suggested value in many examples is 2100. That is the value i used. If you carefully read the Oracle documentation there is a note about setting XDB FTP on port 21.

I was ready to test my connection and i wanted to try it out with the built-in FTP client of Windows but at first i failed to understand how to specify the port number, so, after a couple of unsuccessful attempts, i gave up with ftp.exe and i went straight to Filezilla.

But even with Filezilla, i hit soon a major problem because it wasn't able to list the root folder after connecting to XDB. Here is the error message that you can receive in such situation:

Transfer channel can't be opened.
Reason: A connection attempt failed because the connected party did not properly
respond after a period of time, or established connection failed
because connected host has failed to respond.

Then a bell rang in my head and i changed the configuration from "Default" to Active Mode.
Using FTP in active mode is mentioned in the documentation, as i found out after a while.
Whether you can get FTP to work in passive mode or not is hard to say. The documentation says that it depends on the presence of either localhost or 127.0.0.1 as HOSTNAME in listener.ora, however i tried using passive mode on a server having listener.ora pointing to an address other than 127.0.0.1 and it didn't work.

After setting active mode, Filezilla started working eventually, however i find that the default 60 seconds session timeout is far too short and the "keep alive" method of Filezilla is giving some troubles, so i disabled it.

Once i got filezilla to work, i went back to ftp.exe. I just couldn't believe that i could not specify a port. Indeed there is a way, but not from the command line (as far as i know), from the command prompt execute ftp.exe, then from the ftp prompt, enter:

open host port

in my case this is:

open localhost 2100

It may take a while for the connection to be established, but then it should work.

So far so good, but what about FTP access from another computer?
Well, it all depends on the firewall(s) in between and/or the firewall rules. When there are no active firewalls between client and server, then you just need to repeat the set up already explained, the only difference being the XDB server IP address.

However, if there are active firewalls, then it may be tricky to configure the firewall rules for the FTP protocol in active mode, because you don't know exactly on which port XDB is going to
talk back to your client. The communication will occur on some port above 1023, but i am not aware of any parameter in xdbconfig.xml where one can force this random port to be chosen in a given range, thus the only option is to open up the whole range between 1024 and 65535.

I made a test on my Mac and after disabling the firewall, i could connect to XDB without problems. Unfortunately disabling the firewall altogether doesn't seem the best idea these days.

Given this situation, the best option is probably to set up a tunnelled FTP connection through SSH. May be this will be the subject of a future posting...

At this point i can draw some conclusions:

• the best option, provided it works flawlessly, is to set up a WebDAV connection to XDB. WebDAV worked out-of-the-box in my case, (save the problem i had when i saved the xdbconfig.xml file on my Mac...) and makes no distinction between local or remote access from a configuration point of view. It's also "firewall friendly" because it uses the same port as Oracle Apex, which means that if Apex is working from a remote machine, then WebDAV access to XDB should be working as well (see the note about HTTP(S) when working on Windows XP SP2).
  
• Ftp works well when client and server reside on the same machine but it can be troublesome when the client is located on a remote machine and there are firewalls in between.
  

ORA-31114: XDB configuration has been deleted or is corrupted

Yesterday, for some reason, i suddenly decided that i wanted to set up a FTP connection with the XDB repository of my XE database running on a xubuntu virtual machine.

After creating the WebDAV connection on my Mac, i edited the /xdbconfig.xml because i needed to set the FTP port to some value other than zero (by default FTP protocol access is disabled on XE).

Upon saving the file i got a file save failure and guess what? xdbconfig.xml was gone!

If you think that as an Apex developer you can easily get on without that file, you are plain wrong.
Apex was gone as well.
I tried connecting to the Apex login page but all i got was a wonderful blank page, not even an error, just an empty page and this is easily explained by the fact that on XE all Apex ancillary files are stored in XDB.

Finally, when i tried to execute a simple query on RESOURCE_VIEW, i got the (in)famous error message:

ORA-31114: XDB configuration has been deleted or is corrupted

At this point i had to take a decision: to restore the whole database just for recovering this tiny bit of information or to find some other solution.

I opted for the latter because i thought i could recover the file from another XE database i had on a different machine and, luckily enough, the operation was easier than expected.

I find that with Oracle, being such a vast product, the main problem is often to locate the right tool for doing a certain operation rather than doing the operation itself. And so it was, because it took me more time to spot the procedure in the Oracle Documentation than to restore that damned file.

Before showing how i did it, let me give you a simple advice.
If you are ever going to tamper with xdbconfig.xml, i suggest you to make an off-line copy of the file, just in case.
So, how to extract the current version of xdbconfig.xml without using WebDAV or FTP?

Piece of cake (when everything is working of course...) , connect as SYS or SYSTEM and execute:

select dbms_xdb.cfg_get().getClobVal()
from dual

copy and paste the result into a text file and call it xdbconfig.backup or whatever.

Now imagine that you need to perform the reverse operation taking the source of xdbconfig.xml from another database or from your saved copy. Again, you need to perform this operations as user SYS or SYSTEM.

First of all you need to ascertain whether the file exists or not (see note at the bottom dec 16):

select * from resource_view
where any_path = '/xdbconfig.xml'

if the resource exists, it means that it has been corrupted in some way, in which case you should be able to overwrite it by using the following procedure:

first of all, open the backup xdbconfig.xml file. If you haven't one, then you can try copying the source contained in the Oracle documentation, you can modify some parameters later, if needed.

set define off
set serveroutput on
declare
xmlf    xmltype;
begin
xmlf := xmltype.CreateXML('-- PASTE HERE THE XML SOURCE COPIED FROM THE FILE --');
dbms_xdb.cfg_update(xmlf);
end;

If the file is missing altogether, you can re-create the resource (this is what i had to do actually...) by using the following script:

set define off
set serveroutput on
declare
xmlf    xmltype;
res     boolean;
begin
xmlf := xmltype.CreateXML('-- PASTE HERE THE XML SOURCE COPIED FROM THE FILE --');
res := dbms_xdb.createresource('/xdbconfig.xml',xmlf);
if res then
dbms_output.put_line('file created');
else
dbms_output.put_line('file not created');
end if;
end;

Comment added on December 16: if the xdbconfig.xml file is corrupt or missing, then you will not be able to execute the query on RESOURCE_VIEW. When i wrote up my posting, i had already recovered it so this explains why i could perform that query. So, you just need to execute the procedure for recreating it (the last one), it if fails, then fall back to the update procedure (the second last one).
-------------------------------------------
ORA-31114: La configurazione XDB è stata eliminata o è danneggiata
ORA-31114: la configuración XDB se ha suprimido o está corrupta
ORA-31114: La configuració XDB s'ha suprimit o està malmesa
ORA-31114: La configuration XDB a été supprimée ou est endommagée.
ORA-31114: XDB-Konfiguration wurde gelöscht oder ist beschädigt
ORA-31114: Η διαμόρφωση XDB έχει διαγραφεί ή έχει αλλοιωθεί
ORA-31114: XDB-konfiguration er slettet eller beskadiget
ORA-31114: XDB-konfigurationen har tagits bort eller är skadad
ORA-31114: XDB-konfigurasjonen er slettet eller ødelagt
ORA-31114: XDB-kokoonpano on poistettu tai virheellinen
ORA-31114: az XDB konfiguráció törölve lett vagy sérült
ORA-31114: Configuraţia XDB a fost ştearsă sau este coruptă
ORA-31114: De XDB-configuratie is verwijderd of beschadigd.
ORA-31114: Configuração de XDB foi deletada ou está danificada
ORA-31114: A configuração de XDB foi apagada ou está corrompida
ORA-31114: Конфигурация XDB удалена или повреждена
ORA-31114: Konfigurace XDB byla odstraněna nebo je porušena
ORA-31114: Konfigurácia XDB je odstránená alebo poškodená
ORA-31114: XDB - konfiguracja została usunięta lub jest uszkodzona
ORA-31114: XDB konfigürasyonu silindi veya bozuk
ORA-31114: XDB configuration has been deleted or is corrupted

See message translations for ORA-31114 and search additional resources

ORA-00942: table or view does not exist

I was checking out a new apex view in the beta release of Apex 3.1, apex_application_groups, when trying to execute a trivial query like:

select * from apex_application_groups;

i got:

ORA-00942: table or view does not exist

After a short exchange of messages with the Apex team, it turned out that they had forgotten to create a public synonym for this view.

So, this could be a good occasion to write down a reminder of all the cases in which such error message can be returned:

• referencing a non-existent table or view, including the following possibilities:
• misspelled name
• mismatching letter casing if the object was created with a case sensitive name
• forgetting the double quotes around the object name when the object was created with a case sensitive name
  
• referencing a table or view in a different schema, without having specified the schema name.
• referencing a table or view in a different schema, misspelling the schema name (or referencing a non-existent schema anyway...)
• referencing a non-existent, misspelled or case sensitive synonym
  
• referencing a table, view or synonym without having the necessary privileges (for instance try SELECT * FROM DBA_USERS as a normal user).

You can even get this message in a very naive situation like that of referencing the correct name of a table, view or synonym and with all the necessary privileges, but the real object created by someone else with the wrong name... :-D

For some unknown reason ORA-00942 has disappeared from the Error Message Guide as of Oracle 10G, but you can still find the description of this error in version 9.2.

See message translations for ORA-00942 and search additional resources

On SSH tunnels, VNC, Oracle and secure remote connections

First of all let me point out that TCP networking isn't my core business, so you'll have to be forgiving with me in case I'm using improper terminology.

If you, like me, have the need of accessing your development box from a remote site and in a reasonably "secure" manner, then you can be interested in reading on and hopefully you'll be able to adapt the following steps to your specific environment.

This exercise had at least two goals:

1. to be able to access my Oracle Application Express development environment from a Windows client without the need of exposing the web server to the internet.
2. to minimize the network traffic by using a simple browser instead of a VNC client, while still being able to open a VNC session in case of need. This was especially important to me as i may need to access my server by using a roaming connection through my mobile phone and you know how much they charge for such services.
   

I am happy to say that i achieved both objectives with little effort and without spending a penny. Well, actually i am going to spend 80 bucks or so, that is the amount for VMWare Fusion once my trial license will expire, but this component is not strictly necessary unless you want to perform db server virtualization...

Soon after completing these the original two goals, it came up to my mind to try to configure a remote SQLDeveloper connection as well, whose details can be found at the end of this posting.

Setting up the SSH tunnel.
On the Mac Pro side first i needed to enable the Remote Login service (SSH). This service uses port 22 and is part of the standard Mac OSX edition (Thanks Steve!).
When you enable this service, the corresponding port is also opened in the Mac firewall.

As my network is part of a larger network where i must explicitly request the ISP for opening a port, i had to ask them for a public IP mapped to the local address of the Mac server where is sufficient to have only port 22 open.


Setting up the HTTP client to server forwarding:
On the Windows XP client i installed Bitvise Tunnelier. This is an excellent product that enables you to easily set up SSH tunnels with IP and port forwarding, moreover is free for individual use.

Most of the configuration is carried out on the client side, so all i need to do is to enter a few parameters in Tunnelier:

• user and password for authenticating on the Mac (see picture above).
• public IP address and port of the SSH server (see picture above).
  
• IP address and port of the client that should be forwarded to the remote server (127.0.0.2 on port 8080, see line 2 of picture below)
  
• IP address and port of the remote server where Apex is running (192.168.1.4 on port 8080, see line 2 of picture below).

there are a couple of things worth noting:

1. When you use IP address forwarding, the IP doesn't need to be the IP of the machine where SSH is running, indeed the machine running Apex is a linux xubuntu virtual machine running on the Mac under VMWare Fusion having its own IP address (192.168.1.4).
   
2. The TCP port on the client can be different from the port forwarded to the server. For my own taste i decided to use a fake address (127.0.0.2) while retaining the same destination port number, but a combination like 127.0.0.1 on port 8081 would work equally well for accessing the remote Apex.

Given this configuration, i can now access Apex from a remote site by starting a Tunnelier session and simply entering the following URL in the browser:

http://127.0.0.2:8080/apex/f?p=4550

If you don't like using straight IP addresses, then you could edit the HOSTS file in (%systemroot%\system32\drivers\etc) and add a line like the following:

127.0.0.2  remotehost

this will allow you to specify the URL as follows:

http://remotehost:8080/apex/f?p=4550

Configuration of VNC:

In a very similar fashion i've also set up VNC connectivity.
This was a good opportunity to try out a few Windows VNC clients and after some testing i picked RealVNC.

This lightweight viewer is fast and free, it's made up of a single executable file, so you don't need to install anything and you can carry it on a USB key, moreover it also allows for window scaling, a useful feature because the gorgeous Apple cinema display doesn't fit well on most screens ;-)

Again, the configuration in Tunnelier is fairly straightforward and consists of two additional parameters (see line 1 of picture above) because the SSH configuration remains the same as above:

• IP address and VNC port of the client (127.0.0.2 on port 5901)
• IP address and port of the remote server where OSX Vine Server is running (127.0.0.1 on port 5901).

Now i can open the Mac desktop remotely by entering 127.0.0.2:5901.

Please note that:

• the standard VNC port is 5900, however Apple Remote Desktop is using that port so i preferred to switch to 5901 for the reason i explain later on.
• in certain environments it's not necessary to use IP/port forwarding for using VNC clients, but this is just another security measure as the VNC server is configured to accept only connections from localhost (127.0.0.1), so the IP/port forwarding combined with SSH tunneling does the trick, that is to transform a remote connection into a local one.

I could have used Apple Remote Desktop as VNC server and indeed i've made a few tests with satisfactory results, however i decided to install Vine Server because it gives me the additional possibility of opening multiple sessions against different OSX users.

As i configured Vine Server to work on port 5901, Apple Remote Desktop is still available on port 5900.

For those who want to perform a similar configuration on platforms other than Mac OS X, i guess they must install either a Unix or Windows version of RealVNC server or TightVNC server. For Windows platforms it's also necessary to install a SSH server first like Bitvise WinSSHD. I didn't investigate if there are free SSH servers for Windows but there should be something out there.

Last but not least, let's have a look at how to configure the connectivity for using SQLDeveloper through a SSH tunnel.

All you need to do, once you have established the SSH connection as explained earlier, is to add IP/port forwarding of whatever port you prefer to use (why not 127.0.0.2 on port 1521, so i can still access my XE db at 127.0.0.1 on port 1521) to the remote server IP address and port. In my case Oracle doesn't run on the same server where i opened the SSH tunnel but, as i've mentioned earlier, on a VM (192.168.1.4), see line 3 of the picture above.

Note that if the oracle database and SSH service were running on the same server, then i could replace 192.168.1.4 with 127.0.0.1 in the Tunnelier configuration screens above.

Finally, let's peek at the connection property's screen in SQLDeveloper:

Again, if you prefer to use the DNS defined locally in the HOSTS file, you can specify remotehost instead:


Accessing an Oracle database running on Windows (instead of Linux as in this case) from a remote client via SQLDeveloper, would most likely require the setting of the environment variable called USE_SHARED_SOCKET in order to force the communication to occur on the listener port only.