ORA-31050: Access denied

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

After discovering two undocumented procedures in package DBMS_XDB in Oracle XE (10gR2), i was eager to try out a configuration of a secondary listening endpoint.
These two undocumented procedures, called DBMS_XDB.SetListenerEndpoint and DBMS_XDB.GetListenerEndpoint, are actually documented in the 11gR1 manual, so i guess they were added to the package when it was too late to be described in the manuals.
When i attempted to execute the following block as a normal user, i got the exception shown below:

begin
DBMS_XDB.SETLISTENERENDPOINT (
endpoint  => DBMS_XDB.XDB_ENDPOINT_HTTP2,
host      => NULL,
port      => 8082,
protocol  => DBMS_XDB.XDB_PROTOCOL_TCP);
end;
/

ORA-31050: Access denied
ORA-06512: at "XDB.DBMS_XDB", line 382
ORA-06512: at "XDB.DBMS_XDB", line 671
ORA-06512: at line 2

After connecting as SYS and executing the block again, i got my second listening endpoint up and running on port 8082.

Note that existing sessions won't pick up the new configuration until you refresh it.
Indeed i when executed the following block from an existing session as a normal user:

declare
l_host varchar2(255);
l_prot number;
l_port number;
begin
DBMS_XDB.GETLISTENERENDPOINT (
endpoint  => DBMS_XDB.XDB_ENDPOINT_HTTP2,
host      => l_host,
port      => l_port,
protocol  => l_prot);

dbms_output.put_line(l_host);
dbms_output.put_line(l_port);
dbms_output.put_line(l_prot);
end;
/

i only got null values.
As soon as i executed DBMS_XDB.CFG_REFRESH the new values took effect for that session.

According to the documentation for Oracle 11g, the second listening endpoint is most useful for implementing the HTTPS protocol. I configured a normal (secondary) HTTP port just to verify the possibility of connecting to a specific Apex box from remote using a non standard port.

See message translations for ORA-31050 and search additional resources.

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