Database backup advice

[nyck]

Hello,

Our DBA has recently left and this role has now been moved too me. Currently we are backing up all our oracle databases using Legato Networker whilst the databases are running, this is not the best of things to do I would imagine. Can someone suggest a way I can dump these databases without spending a fortune on the Legato oracle add-ons?

Also if a database is running whilst I'm backing it up and during that time its not accessed will I be able to restore this database and get it running again?

 

[SantaMufasa]

Also if a database is running whilst I'm backing it up and during that time its not accessed will I be able to restore this database and get it running again?

You absolutely cannot rely upon a backup taken while the database is running unless you use also use Oracle's hot backup mechanism.


Using Legato only to backup your database data files while the database is running is a waste of time and disk space.

The best/easiest mechanism for backing up Oracle databases is Oracle's RMAN system. Obtain documentation on RMAN, read it, practice both the backup and recovery concepts on a "sandbox" database until you are comfortable, then implement on your production system.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[KenCunningham]

Hi. Sorry, I know nothing about Legato, but presumably the databases are being backed up 'hot', ie whilst online and active. This is fine as long as Legato has this capability, but you may wish to look into Oracle's own tools, ie RMAN.

If the backup is being carried out whilst the databases are running but without the capability to 'suspend time' as happens with RMAN, then you will have trouble. Can you (or have you) tried any test restores from your backups.

Hopefully someone who can advise on Legato's capabilities will be along soon.

I want to be good, is that not enough?

 

[KenCunningham]

Whoa - Dave's fingers are faster than mine as usual!!

I want to be good, is that not enough?

 

[SantaMufasa]

Nope...not faster, Ken, just trained to synchronise with yours apparently.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[nyck]

Cheers for the responses!

For some of the more important databases I have been using exp and this seems to work ok. Someone mentioned about cloning the databases, is this an option?

I'm going to push for the purchase of the Legato Oracle add-ons which I believe use RMAN, but these are not cheap and we have got multiple versions of oracle installed on many servers!

 

[SantaMufasa]

As far as I know, Oracle's RMAN facility is a no-extra-cost feature that comes with the database. (I have never paid extra for third-party backup/recovery software, and I've never lost a transaction while using Oracle's standard backup/recovery mechanisms.)

Keep in mind that using exp/imp can only restore the database to its logical state at the time that the exp occurred. If you place your database in ARCHIVELOG mode, then you can recover the database to virtually any point in time, including the most recently COMMITted transaction.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[hoinz]

Hello,

we are working with Oracle and Legato Networker. Legato (or their mother EMC) is selling an additional product to do an Oracle hot backup to tapes. As far as I know, you may choose if you are going to use RMAN for it.

If you don't like the extra costs of this product, you may consider this workaround:
Do an Oracle hot backup to disk, then let Legato save these backup files to tape. As you probably have a Legato license for fileystem backups, there will be no extra costs.
On the other hand, you will have to pay for the extra disk storage in this case.
So find out which is cheaper.
We use this workaround for small databases, but not for the real big ones.

hope this helps

 

[karluk]

Hoinz has some very good advice. In fact, I would suggest that you investigate how the current backups are done. If the backup jobs were set up by the DBA who has left, it seems likely that he/she implemented the "hot rman backup to disk -> let Legato write backups to tape" solution which hoinz outlines. It's practically everyone's favorite poor-man's solution. You get valid rman backups on tape without an expensive third party add-on. The negatives are the need for extra disk space and the need to copy everything twice. But as hoinz says, these are minor issues when the database is small.

 

[Thargy]

Santa is correct, RMAN is standard with Oracle and costs nothing. It is far and away the best backup solution, and is actively being maintained and upgraded by Oracle Corporation.

Grinding away at things Oracular

 

[nyck]

A couple of weeks ago we were rather lucky with our oracle restores as I had got a full backup of all the databases when they were not running. Then three days later one of our contracts wrote a script to remove a database but managed to make a mistake and totally blew away all the databases on the server, 1TB or so. So after spending all night restoring I decided it was time to get something in place which is a bit more reliable. This RMAN hot backup to disk looks good, can someone point me to some good documentation for this and examples?

 

[Thargy]

As always with Oracle, the online documentation is a good place to start.

However, as my Bible, I use "RMAN Backup and Recovery" by Freeman and Hart, from Oracle Press. It gives you a plain English explanation and de-mystifies RMAN.

The best bits are the worked solutions for "How to recover from this dire situation" which show you how to really cash in on RMAN's capabilities.

I only have the 9i edition, so I'd recommend the 10g (if such exists) as there are new features which were introduced with 10g, and make recovery even simpler.

I use the 'trick' of saving RMAN backups to disk, and then using our ordinary backup tapes on the RMAN backup sets. This saves the cost of the software interface, and since disk space is cheap, it's easy to justify.

Also, you can "sell" the idea of extra disks, by pointing out that recovery from a disk is much quicker than from tape. Just leave 2 or 3 days worth of backups on disk, and if you need a "recent past recovery" you'll get your database back really fast.

Grinding away at things Oracular

 

[DrD123]

Hi nyck,

Are you using Legato Single Server Version (LSSV)? If so then that's a "free" single dB license that comes with Oracle Enterprise, it can be used for that single server on the single dB. It already has the connector to RMAN/Oracle in it. No need to buy it.

You can use a HOT backup using RMAN then using LSSV as the MML (Media Manager Level). Otherwise RMAN only has the ability to write to disk. Using LSSV allows RMAN to write to tape.

If they're not already set up in RMA, change these parameters to match your device:

CONFIGURE DEFAULT DEVICE TYPE TO
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(NSR_COMPRESSION=TRUE, NSR_NWPATH=/usr/sbin)';


Good Luck,
DrD