How and what do you backup for a simple Oracle DB?

Question

Background:

I am in the following situation: We have a JIRA instance running on a dedicated server machine (Windows Server). It uses Oracle XE to store all the data. The JIRA instance and a Oracle XE install for the data are both running on this one server box. JIRA does support doing cyclic export to XML, and we export this "XML dump" to a network share twice a day. But due to performance implications, we would like to replace this no-brainer solution with a (hopefully) no-brainer database backup.

Question:

Now, this question is not about the technical details, I can look them up easily enough. This question is about cutting through the blabla and hopefully getting to the point :-)

So I have this very simple one-user database on a single server and single harddisk, and want to do a backup to a network share:

• What do I need to back up so that the database is recoverable if the machine breaks.
  • Is an "export" enough, do I need some setting files, etc?
  • Essentially, what does constitute a "full" backup, so that the database + instance can be reconstructed by a fairly inexperienced Oracle user, even if the instance saw some undocumented tweaking.
• How do I backup, as in:
  • Which tools should I use to do the backup.
  • Should I try to get a live/continuous backup, or just do periodic "exports".
  • Should the backup be "initiated" by the DB or by an external script?

So basically, I have a simple setup. The database was simple to install. How do I backup this so that it'll be simple to maintain and simple to restore?

_{Hope this is not too generic or noobish for this site.}

Answer 1

Use RMAN to back up to the network share. An export is NOT a true backup as you cannot perform a point-in-time recovery with it. XE comes with backup scripts that you can just schedule with AT or similar, the documentation says:

To schedule automatic backups, use any operating system or third party task scheduling software to run the supplied backup script for your platform

If you are running the database in archivelog mode, then the archived redo logs are your "continuous export", so you would ideally want to have those on seperate disks from the DBFs, and hopefully mirrored. RMAN will back those up too. As you say you have a single disk, well, this is a judgement call you will need to make - I would say if the data is valuable enough to back up, it is valuable enough to run on server-grade hardware.

Answer 2

Running on one disk indicates that you are comfortable loosing all your work since the last backup. Since you are backing up twice a day it would seem that you want the software to remain available while the backup is taking place. In order to meet these requirements you will need to be in archivelog mode.

Use Windows Scheduler to schedule two different scripts that use RMAN. The first run of the day would just backup the archive logs and the second would do a full backup including archive logs. Depending on how much archive data is being backed up and how fast you want your recoveries to be, you could consider doing your full backups less frequently.

Answer 3

The stock answer (as cited correctly by Gaius) is to use RMAN to provide full backup coverage.

However, RMAN is complicated and not what I'd call appropriate in your case. As a single user system, you can get away with a standard full system backup overnight, as long as the Oracle database is shutdown during the backup and then restarted. This should be quite easy to script and also easier to use when you need to recover the server.

Rough script - not tested, but might give you the basic idea..

sc stop Oracle   (Whatever the service name is)
robocopy c:\oracle d:\OracleBackup /s  (Copy the directory to a safe location)
sc start Oracle

The restore process would be:

1. Restore the OS from your usual backup
2. Copy back the d:\OracleBackup directory
3. Test

You do lose the ability to perform a point in time restore - but will you need it?