General
cancel
Showing results for 
Search instead for 
Did you mean: 

Recommend backup for this Oracle database

kenny chia
Regular Advisor

Recommend backup for this Oracle database

Hi
I need to design a backup and restore plan for a particular Oracle database. Any hardware and software backup suggestions for this system? 3rd party backup software can be considered too.

1. Oracle8i
2. About 100GB worth of datafiles in a DS2300 disk system. Disk system configured for 400GB storage
3. Possible to stop database updates for 1 hour
4. HP-UX 11.00

Thank you
All Your Bases Are Belong To Us!
13 REPLIES
Yogeeraj_1
Honored Contributor

Re: Recommend backup for this Oracle database

hi,

1. No need to buy any additional software
2. No need to stop the database

Use RMAN.

Using RMAN, schedule/automate backup your database to disk then send them to your backup device using any software (as you would do for any file on the file system).

If you need any further help, please let us know.

hope this helps!

best regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Michael Tully
Honored Contributor

Re: Recommend backup for this Oracle database

If you need a tape management system, you could consider omniback. Omniback can also integrate with RMAN.
Anyone for a Mutiny ?
Sanjay Kumar Suri
Honored Contributor

Re: Recommend backup for this Oracle database

RMAN is an ideal solution as suggested by Yogi.

Else you can think of developing some shell scripts which enables:

- online backups without shutting down the database (alter tablespace begin backup)
- offline backups (after shutting down the database as you have 1 hour window)

These backups can be taken on disk (400 GB) or on the tapes.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Indira Aramandla
Honored Contributor

Re: Recommend backup for this Oracle database

Hi,

We use HP storage works LSL5000 series (Ultrium 230 Tape Library) and LTO tapes. To backup our oracle databases and other software / applications on the HP RP5430 L class server.

The backup tool we use is HP Openview OmniBack / Data Protector and we do cold (offline) and hot (online) backups for the 24 x 7 databases. With the super fast LTO tapes it can backup 15 GB of data withtin 12 minutes. To restore the same from the backup tape takes 25 â 30 minutes. And the backup / restore procedures are very simple using the Omniback GUI tool.

But you need to buy the OmniBack product and have the liscence. Like Michael said you can integrate OmniBack with RMAN.

On the other hand if you are not intending to spend then as Yogeeraj said "No need to but additional software", RMAN is the way.

Indira
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Recommend backup for this Oracle database

hi again,

Important to know that by using RMAN, you get two most important advantages:

A. RMAN decreases the chance of you making a mistake.

Question -- do you regularly test your backups? There was one customer who wanted to know why their system was down for days -- yes, days. Why so long? Well, they had a problem, had to restore from backup. Never tested said backup. Discovered all of a sudden their script kicked off the copies BEFORE the alter tablespace begin backup. :-( -> System was fried. Support helped them scavenge what they could but basically, it was a rebuild of the entire big database. That would not happen with RMAN -- less for them to think about, less mistakes possible.

B. RMAN allows fast recovery

How about this -- you have a single bad block in a 16GB datafile, you are using RMAN in 9i. What do you do? You do an in place recovery of just the bad block, you do not offline the datafile, you do not offline the tablespace, you do not do 16GB of IO to restore the file, you do not roll 16GB of data forward. You recover an individual 8k block.

But -- you are not using RMAN? You would have to do the following steps:
- take tablespace offline
- restore 16GB
- roll it forward
- bring it online.
(you may guess how much time this would require!!)

best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
KapilRaj
Honored Contributor

Re: Recommend backup for this Oracle database

RMAN could be a choise ... it is better for file - to file recovery,

I hv no idea what a DS ???? is but does it support snapshots ?. Something like a BCV timefinder or ESS flash copy which allows you to take a snap of one set of Master devices on to a copy devices (U need 2 * 100 GB for this.

Or you may,

1.mirror the volumes ,
2.split them daily
3. mount the cpoy on a diffrent mount point.
4. take backup
5. unmount
6. merge the mirror set again

Kaps
Nothing is impossible
Alexander M. Ermes
Honored Contributor

Re: Recommend backup for this Oracle database

Hi there.
RMAN is a good solution. You have your database online and you can recover it very easy.
If possible, do a cold backup once a week.
That would mean, bring the database down, backup all databasefile, controlfiles, logfiles and offlinelogs.
Then do a backup to tape.
Other possibility, if the database is down,
is to copy these files to a filesystem of your choice.
After the offline backup you can bring up your database again.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
kenny chia
Regular Advisor

Re: Recommend backup for this Oracle database

Hi
Thanks for all the suggestions. DS2300 is a particular disk system.

1. If I have to do a online backup, the database must be in archive mode?

2. For my full database backup which is not in archive mode, is the syntax correct? Do I need to enclose allocate and backup in run{}?

RMAN > connect target
RMAN > shutdown
RMAN > startup mount
RMAN > allocate channel disk1 type disk format '/users1/ora_backup';
RMAN > backup database;

3. Most important, How do I calculate full backup disk space required?
All Your Bases Are Belong To Us!
Volker Borowski
Honored Contributor

Re: Recommend backup for this Oracle database

Hi Kenny.

1) you should consider to switch on archiving ! You will sleep better at night!

2) Yes, to do Online Backup archive mode must be enabled. Concerning your script, a simple shutdown will wait for every transaction to finish, so may be a "shutdown immediate" might be more secure to get the backup on the way.

3)RMAN saves used blocks, so

SELECT SUM(BYTES) / 1024 / 1024 as MB
FROM
DBA_SEGMENTS;

will be a bit too much, but a good calculation start.

Hoper this helps
Volker

Leon Allen
Regular Advisor

Re: Recommend backup for this Oracle database

I was put off using rman when I had to fork out big bucks (big for us) to enable omniback to integrate with rman.

Instead, I do both a logical backup (export) and a physical backup (hot backup) on a nightly basis.

Time's fun when your having flys (ancient frog saying)
Yogeeraj_1
Honored Contributor

Re: Recommend backup for this Oracle database

hi again,

For Backing Up in NOARCHIVELOG Mode, you should do the following:

# Shut down the database cleanly using immediate priority. This type of shutdown lets
# current calls to the database complete, but prevents further logons or calls.
# If the database is not up now, you will get a message saying so but RMAN will not
# treat this situation as an error.

shutdown immediate;

# Start up the database in case it crashed or was not shutdown cleanly prior to
# starting this script. This will perform a crash recovery if it is needed. Oracle
# uses the default INIT.ORA file. Alternatively, use this form: startup force dba
# pfile=. Use the DBA option because you are going to shut down again right
# away and do not want to let users in during the short interval. Use the FORCE
# option because it cannot hurt and might help in certain situations.

startup force dba;
shutdown immediate;

# Here, we know that the database is cleanly closed and is now ready for a cold
# backup. RMAN requires that the database be started and mounted to perform a backup,
# so do that now.

startup mount;
run {
# duplex the backup
set duplex = 2;

# allocate channel t1 type 'SBT_TAPE';
# allocate channel t2 type 'SBT_TAPE';
allocate channel t1 type disk;
allocate channel t2 type disk;

set limit channel t1 kbytes 2097150;
set limit channel t2 kbytes 2097150;

backup
incremental level 0
filesperset 5
database;
}

# now that the backup is complete, open the database.
alter database open;

============================================

see also: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76990/usingrma.htm#435660

I would recommend that you turn on ARCHIVELOG mode.

Hope this helps!
best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
R. Allan Hicks
Trusted Contributor

Re: Recommend backup for this Oracle database

I use the standard RMAN and the Legato that comes with 9i and came with 8i.

To your question about being or not being in Archive log mode (from a more recent post). You definitely need to be in Archive log mode. The only exception to this dogma is if you are doing developmental work and really don't care if you have to throw everything away.

I'll bore you with a basic description of archive log mode. (Feel free to skip ahead if you've already endured a similar lecture)

I. Archive Log Mode Lecture

Archive logs are simply copies of the redo logs. All changes made to the database are written to the db via dbwr (database writer) and when you are in archive log mode, they are written to the redo logs via lgwr (log writer). Yes, archive log mode has more overhead because there are more processes. But, it's a form of automatic incremental backup. When the database starts, it checks change numbers for each tablespace to determine if the change number in the tablespace matches the controlfiles. (BTW multiple control files are a real good idea as well as multiple redo logfiles in each group). If the change numbers do not match up, then the archivelogs and the redo logs are searched. The changes are pulled from the logs and applied to the tablespaces.

If you are not in archive log mode, then you can get the changes out of the redologs. Otherwise, if the changes weren't applied to the tablespace and the redo log have been overwritten then you are out of luck.

II Dangers of Archive Log Mode

BTW if you go to archivelog mode, realize that you may have to add redolog groups. In non-archivelog mode, switching from one redo to the next is no big deal and two redo groups is fine. However, in archivelog mode, you cannot switch to the next group until that group is archived. You cannot switch from group 1 to group 2 until the archiver has completed copying it. Oracle will go into a "polite" wait and simply hang until the archive is complete. The more groups you have, the less likely you'll enter this wait state.

You also need to be mindful of the number of log switches that occur at peak times. Not only will the system hang if there is not an available redo log group, but if the archive destination fills up, the system will enter another polite wait until there is space in the destination.

III Back Up with RMAN

As far as backup schemes go, RMAN is a very good solution. However, years ago there was a backup and recovery workshop from Oracle. I cannot recommend this course strongly enough.

1.) It lets you practice recovery when you job is not on the line. Ask yourself. "What would I do if I completely lost my controlfile?" "What would I do if the building housing my server and my backup tapes burned completely to the ground?" If you have a chance to see and successfully recover under a various set of disasters, you will have the confidence that you will need to face a real one. I once ran the wrong script and completely wiped out my production database. (I still have a job go figure) I got the whole thing back except for one hour.

2.) You gain a complete understanding of archivelogs, redologs and system change numbers (SCN). This lets you use RMAN wisely. It's not magic anymore.

I have used legato for my tape backups mainly because it was included in 8i release. You can use omniback or other media library management. I have issues with legato.

1.) Legato is not really supported by Oracle unless you get nasty with them. I don't like getting nasty. However, if you are willing to pay Legato, they are willing to help you. Not something you really want to have to deal with when you are putting your system back together.

2.) Legato knows what you want to do with your tapes better than you. For example, Legato knows that if you made a backup on computer A that there is no way that you'd want to do a restore to computer B. So, it protects you from yourself. It does set expiration dates on your backups to keep you from shooting yourself in the foot by writing over a recent backup. You can override this protection with very minor pleading with the admin product.

3.) Watch your /etc/hosts file like a hawk. If you put another machine in it and it the new machine appears before the local machine, Legato may give you a cryptic error message and refuse to work.

If I had the disk space, I'd do hot copies of the tablespaces to an alternate area of the disk and then do a tape backup of it using standard UNIX backup utilities. I lose the protection that Legato provides, but I feel more in control of things.

IV. Disc to Disc to Tape (D2D2T)

I recently attended a backup and recovery seminar hosted by some SANS and NANS people and they were recommending disk to disk to tape backups. Basically use RMAN or some scripts of your choice backup data across the network to a place where a tape librarian can handle the backups.

V. Diaster Recovery

I'd like to encourage you to think beyond just getting the data to tape, and think of a disaster recovery plan. Recent events have made people more aware of the legal responsibilites of maintaining a recoverable database. I'm in the process of reading Unix Backup and Recovery by (Preston or Curtis or Preston Curtis) I forget his name published by O'Reilly. I heard him speak and he makes you think beyond what you're doing and on to why you are doing it.

--Best of Luck
"Only he who attempts the absurd is capable of achieving the impossible
Steven E. Protter
Exalted Contributor

Re: Recommend backup for this Oracle database

How to go depends on a couple of simple factors in my opinion.

1) Is it a 24 x 7 system? If so, you need to minimize downtime.

In that scenario I recommend OnlineJFS be purchased and snapshots be made to disk. This lets you down the database, set up that changed blocks go to an alternate disk during backup. This lets you bring up the database after a disruption of less then 5 minutes.

2) If you can tolerate some downtime then down the database and copy the files to tape/disk or your choice of media. When the copy.

I like option 1, simply because it deals with a future that squeezes backup windows. Even if you use rman, the snapshot idea works nicely because its fast and just because you have an hour, why use the hour. All you need is a temporary logical volume equal to about 20% of the actual space allocated to the database to handle block updates.

The part I think is cool is you get a good cold backup, the best kind to have while the databse is hot, up and running.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com