Monthly Archives: February 2014

Oracle XML DB content easily moved

We have this application where we just store some specific content in an XDB-schema.

After a quick move of the Oracle database from a legacy system to a new environment we found that the XDB-schema and it’s contents were not moved. Okay, this is what happens when you use “good-ole” imp/exp instead of some “newer” technologies like RMAN or expdp.

What now? We can start the entire move again (but that would mean downtime for recreating the database, amongst others) or we could do a specific move for the XDB-schema (but meanwhile new content was being added to the system already). Actually all of these are not the nicest scenario’s and seemingly adding too much complexity. Not what we want…

What about a a smart alternative here too! We could simply use ftp after all.

From the EPG (Embedded PL/SQL Gateway) functionality of the database, we can just enable ftp through the Oracle database listener. With this functionality we can access the application database on the legacy system through ftp and easily copy the content to a local directory, especially since there is just a few hundered Megabytes of data.

Enable this access by:
execute dbms_xdb.setFtpPort(2100);

With a tool like Filezilla, the contents were copied to a local directory.

After the action is complete, ftp-access to the database is closed, you can never be too careful!
execute dbms_xdb.setFtpPort(0);

Loading this contents in the new location is a repetition of the actions. Enable the EPG-ftp-port on the new database, use an ftp-tool to upload the data and don’t forget to disable the EPG-ftp-port afterwards.

One tricky thing is that you should mind the data-ownership. This is easiest done by connecting to the ftp-account with the same user that owned the database in the source database!

When you run into errors, probably there could be something wrong with your XDB-installation. Please look at this post for some more on that!!