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 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:
http://192.168.1.99:8888/apex. Use the IP/hostname and port number that is valid for your DB.
Unload to Text
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 character1). I also include column names and use a comma as a separator.
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:
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 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:
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 state2).
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.