question

Kastaka avatar image
Kastaka asked

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.

oraclebackup
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Blackhawk-17 avatar image
Blackhawk-17 answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

chandlerdba 1 avatar image
chandlerdba 1 answered
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
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.