Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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.

share|improve this answer
    
+1 for RMAN and great slides –  Jack Douglas Aug 30 '11 at 12:16
    
Hmmm ... I wonder if the fact whether the DB is running in archivelog mode is preserved in created backups? –  Martin Aug 30 '11 at 14:11
    
@Martin yes it is –  Jack Douglas Aug 30 '11 at 14:27
    
Heh. The slides are nice. And show why I want to stay away from RMAN. It certainly sounds complicated to someone who just uses Oracle with what goes out of the box. –  Martin Aug 30 '11 at 15:28
1  
Well, you will not have to touch RMAN yourself, the script Oracle gives you will do it all for you. –  Gaius Aug 30 '11 at 15:33
add comment

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.

share|improve this answer
add comment

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?

share|improve this answer
    
To get a snapshot of the database (which is what you get when doing a file copy) using expdp (or good old exp) is probably better because it does not require to shut down Oracle. –  a_horse_with_no_name Aug 30 '11 at 12:57
1  
It is not complicated, all you need to do is schedule the script that Oracle includes with XE!! –  Gaius Aug 30 '11 at 13:00
    
-1 restore would be more complicated than that and far easier with RMAN –  Jack Douglas Aug 30 '11 at 14:23
    
Hmmm ... I'm with a_horse_with_no_name here. I'd have thought it easier to just use exp[dp] ... except that your solution obviously back ups everything, whereas I'm not so sure that exp[dp] contains everything to restore the full DB + instance. –  Martin Aug 30 '11 at 15:31
2  
sigh Oracle gives you a script with XE to backup, and another one to restore. It's all in the documentation, that I linked to. Why make it more complicated than it has to be? –  Gaius Aug 31 '11 at 8:51
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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