Basic Oracle XE and APEX Backup
There are thousands of people around the world using Oracle XE APEX version for developing applications and many of them just don’t know how to backup their database. Oracle XE database comes with 9 essential files that are the database itself. If you backup those 9 files you have your database backed up. To backup these files your database must be shutdown (cold backup).
When you install Oracle, it’s created the initialization parameters file, the server parameter file (SPFILE), the Oracle password file and 2 more not so important files. These files are created in the directory /install_dir/app/oracle/product/10.2.0/server/dbs.
Beside the initialization files, 6 data files are created in the dir /install_dir/oradata/XE. These files are the control file, system, sysaux, undo, temporary and user data files (each one representing one tablespace)… these files represent the database.
If your database is in archivelog mode you’ll have also to backup the redo log files. These files are in /install_dir/app/oracle/flash_recovery_area
So, to backup your XE database in linux you can zip those directories into a tar gzip file with this command:
tar cpvfz myxebackup.tar.gz /install_dir/app/oracle/product/10.2.0/server/dbs /install_dir/oradata/XE /install_dir/app/oracle/flash_recovery_area
If you’re not using Linux, the important thing is you to know what to backup, and sure windows has the tools you need to backup that directories I wrote about.
In Linux the install_dir is /usr/lib/oracle/xe and in windows usually is C:\OracleXE
Hope it helps 😉
Update: RMAN may also be used to hot backup the database without need to shutdown the database. This post came after some talks with starter XE users that just don’t knew which files “are the database itself” and which should be backup.