x

SQL Server-like backups on Oracle?

There seem to be a bewildering variety of backup options on Oracle - and nothing I can just put in a SQL script (I'm running scripts with OSD at the moment) to ensure my schema gets backed up as soon as I finish creating objects in it. Restoring backups appears to be a minefield too - everyone appears to have their own way of doing it!

Is there some kind of 'recommended' backup strategy for Oracle that would be comprehensible to someone with a SQL Server background?

Ideally I'm looking for something that would let be back up individual schemas rather than an entire database. I'm not that worried about space concerns, more about time taken to back up / restore and simplicity of administration. Cross-platform backup and restore (e.g. from a server running on 32-bit Windows to one running 64-bit Red Hat and vice versa) would be a big plus - the 'just copy the files' method has some downsides here.

more ▼

asked Oct 23, 2009 at 11:26 AM in Default

Kastaka gravatar image

Kastaka
219 8 9 11

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

While not the cleanest in terms of administration you could create each schema in its own tablespace. You then could perform tablespace backups. You may want to use bigfile tablespaces (10G+) to ease the management woes.

Think of Oracle tablespaces as SQL filegroups for your RDBMS comparisons.

more ▼

answered Oct 30, 2009 at 02:50 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

(comments are locked)
10|1200 characters needed characters left

Use RMAN. If anyone is doing Oracle backups from 10G onwards and isn't using RMAN, then they really should be. If you are using ASM for your filesystem, they you have to use RMAN. If it's just a JFS or NTFS, then it's still the best option and the only sensible option for Hot backups.

As for copying files between Windows and Linux, RMAN cna do this. You need to be aware of whether your system is Big-endian or Little-Endian and you may need to run an RMAN conversion of files.

However, if you just want a logical copy of a schema, then you could consider using DataPump. It's not a true backup method as it's not transactional (although you can ask it to be transactionally consistent.) The output files are also fully portable across platforms. There is no real SQL Server equivalent of a DataPump, but it basically creates a file containing all of the create object statements, and a whole bunch of insert statements. It's a little more complex than that (like a very complex bcp), but it is a logical copy which can be taken at a logical level (e..g tablespace or schema).

It is very flexible, for exmaple where clauses can be added for data extraction to only get out the data you are interested in.

@ChandlerDBA
more ▼

answered Jan 18, 2012 at 06:52 AM

chandlerdba 1 gravatar image

chandlerdba 1
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x192

asked: Oct 23, 2009 at 11:26 AM

Seen: 1370 times

Last Updated: Oct 23, 2009 at 11:29 AM