I am looking for a decent way of keeping a secondary Oracle database up to date without exporting and importing the database each time. There are 3 users on the instance that I would essentially like to 'log ship' if thats what it is called on Oracle!

Can anyone suggest anything?

The database is well under a GB total and we are running 10g express (although I have thought about using 10g standard as we have a spare license).




Here's what I do:

My primary database has transaction log shipping turned on. The logs are written to /db/archive.

Every hour, a cron job runs as the oracle user. This cronjob does the following things:

moves the contents of /db/archive/ to /db_archive/YYYYMMDD/ (using the following script (that I didn't write, and so don't hold me accountable for ugliness))

 # args: <src> <dest> <date>

 echo "Processing $datechunk"

 check=`ls $1 | wc -l`

 if [ $check -le 2 ]; then
     exit 0

 let check2=check-2
 echo "Processing $check2 files"
 ls -vA $1 | head -n $check2 > $2/dirlist.$datechunk

 for line in `cat $2/dirlist.$datechunk`
    cp $1/$line $2/$datechunk
    if [ -s $2/$datechunk/$line ]; then
        rm $1/$line

rsyncs the contents of /db_archive/YYYYMMDD/ to /db_archive/YYYYMMDD on the secondary server.

That takes care of getting the files there.

On the secondary server, /db/archive is a symlink to /db_archive/YYYYMMDD. "recover standby database" automatically reads from /db/archive/, so whatever script you use to recover your database should be able to handle the errors generated at the end of a day, so they can switch the symlink to the new day.

Does this help, or do you need more info?

  • Hi and thanks for the comment. Unfortunately this runs on a Windows server box so I'd have to reply on scheduled tasks to execute any scripts and obviously therefore I cant create symlinks :-) . As we do use linux extensively I could quite easily get Oracle up and running on (Debian) Linux if it might actually be easier, the reason I didnt was because of the low size and general low resource requirement of the applications that use it. Can you think of a way to adapt it for Windows or come accross any other 'Windows' ways of doing it? Or should I go Linux? – ItsAMystery Mar 24 '10 at 16:29
  • You could use scheduled batch scripts (or do it all in powershell). That would be the simplest way. It's not strictly necessary to symlink into the target directory. If you've got a little bit of extra disk space, you could copy the individual log files into the target dir, then remove them after they're replayed. Transferring the files could be done using Windows File Shares, as opposed to rsync – Matt Simmons Mar 24 '10 at 18:37
  • Thats a good idea, Many thanks for your help, I will let you know how I get on (although its going to be a while as I am off for a couple of weeks now!). Have a good easter all :-) – ItsAMystery Mar 26 '10 at 9:56

I'm not sure what the differences are between 10g and 10g express, but 10g offered replication similar to that used by LDAP, that they called 'Streams Replication'.

I've also seen approaches used where you set up remote database links back to the master, and a materialized view to copy the data to a local table on each replica. (obviously, this has security implications, and can only copy tables that the replicas know about in advance).

  • Thanks for the pdf, I will have a read and see if I understand it! – ItsAMystery Mar 24 '10 at 16:30

You might check out DBVisit http://www.dbvisit.com/ . It is a system for automating the shipping and apply of Oracle redo logs. It supports XE as well as the more expensive Standard and Enterprise Editions.

I have not used the product, just found it was an option if you needed to avoid costs of Enterprise Edition and were not comfortable rolling your own scripts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.