Update 13-Feb-07: Setting the datapump table_exists_action to replace does not overwrite views, sequences, plsql objects. They have to be dropped separately before datapump is called, see gotchas and code below.
Update 02-Mar-07: dba_ views rather than all_ views have to be used to identify what objects to drop prior to datapump, for reasons explained in this post.
For a nightly process to copy small schemas from one database to another, the normal, or old style, way to do that is with export/import in a cron controlled shell script, which would include a sql-from-sql script to drop all the target schema objects before doing the import.
But that presents problems in RAC, which is what I was working on – which node does the cron script reside on? What happens if that node is down?
An option would be to have the script run on another machine, one unrelated to the RAC cluster, but that introduces more points of failure, and means sending data back and forward over sql*net.
So instead I used the oracle’s 10g scheduler and datapump, as below. This data pumps straight over a database link, without having to write to a dump file in between, which is nice. And because it is all in the database, it ought to be unaffected by particular RAC nodes being down.