Skip to content

How To Backup an Oracle XE Database

There are several kinds of backups available with most database. I'm using an Oracle XE database on one project and have found different kinds of backups to be useful in different situations.

Manual Backup

Manual backups are done in development situations when a single tables needs to be backed up. Multiple tables can be backed up too, but you run the risk of creating an inconsistent backup (because tables are backed up one at a time).

Steps to create a backup:

  1. Login to XE using a browser: http://192.168.1.99:8888/apex. Use the IP/hostname and port number that is valid for your DB.
  2. For each table you wish to backup:
    1. navigate to Home --> Utilities --> Data Load/Unload --> Unload --> Unload to Text
    2. select the desired database schema, click next
    3. select the desired table, click next
    4. select the desired column, click next
    5. select the desired file export options, click unload data
    6. save the file to your machine

My experience with manual backups has taught me to enclose my data in a character that isn't likely to be used in the data, such as the backtick character((on the US keyboard its normally in the top-left corner of the keyboard, to the left of the number "1" )). I also include column names and use a comma as a separator.

Cold Backup

Cold backups are more reliable. but they require the database to be offline. That isn't always possible with production databases. If your uptime requirements allows you to take the database offline for 10-20 minutes, this method will probably work for you.

There are 3 main folders to backup in XE:

  • the server files (stored in <install dir>/app/oracle/product/10.2.0/server/dbs)
  • the user files (stored in <install dir>/oradata/XE)
  • redo log files (stored in <install dir>/app/oracle/flash_recovery_area)

In RedHat Linux, I back them up as follows:

/etc/init.d/oracle-xe stop    #stop the database
cd 
tar cpvfz myxebackup.tar.gz \
     /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs \
     /usr/lib/oracle/xe/oradata/XE \
     /usr/lib/oracle/xe/app/oracle/flash_recovery_area

/etc/init.d/oracle-xe start   #start the database

I run this process as root, and it creates a file in my home directory called myxebackup.tar.gz. The backup is complete once the tar command finishes. I can transfer the backup to another machine for safe keeping, or put it in another directory for later restoration.

Hot Backup

Hot backups are performed while the database is running. This can be a labor-intensive task for the database, so expect some performance degradation.

There are two backup scripts included with XE:

  • /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/backupdatabase.sh
  • /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/backup.sh
*

The first shell script simply opens backup.sh in an X11 terminal, so you won't use it unless you are at a graphical workstation. All the real work takes place in backup.sh. It performs a hot or cold backup, depending on whether the database is in Archive log mode or not. To set the database to Archive log mode, follow the steps outlined in the Oracle Database Express Edition, 2-Day DBA guide.

Note that turning on the Archive log means you must take regular backups. The Flash Recovery Area contains a journal of the transactions that have taken place since the last backup. In the event a restore is necessary, the last backup and the flash recovery data can be used to bring the database back to its most recent state1.

Unfortunately, not performing a backup when Archive log mode is on allows the flash recovery area to keep growing, possibly filling up and causing database errors. Hence, a DBA must consider the need for 24/7 operation carefully. It comes at a price, especially considering that Oracle XE can only hold 4GB of data.

  1. think of it like an incremental backup: it holds everything since the last backup ↩