backup in no-archive log mode production database..

Hi Guys,
We have a 800GB warehouse running on oracle 8.0.6(soon going to be 9i) in no-archive log mode. For backups, every night we export all the regular tables plus the current partitions of all the partitioned tables(I know this is un-acceptable but unfortunately management has been fine so far with export being the ONLY mode of backup).The export job takes about 5-6 hours.This covers us if we lose a table or so but recently we lost 3 datafiles from 3 different tablespaces due to a hardware problem and we have to recreate all of them which took us a significant amount of time.We are planning to either move to archive log or start taking cold backup every night. My concern is :

1. If we move to archive log mode , how does it impact the loading time ? we load about 6GB data every day(insets/updates) and it takes about 18 hours. Will archive log result in slower loading time ? Does any one here has similar experience ?
2. If we move to cold backup solution in no-archive log mode , is it possible to copy 800GB in 4-5 hours ? Can anyone here please suggest some software/hardware for this purpose ?

Your help will be greatly appreciated .

Thanks.

u are loading 6GB of data and the Database is noarchive mode?
1.If u keep the database in Archive mode take care that ur backup media is good also ur log groups are multiplexed with good size.This size u can calculate after going through ur alert.log how much time it is taking between check points.I don't think that by keeping the database in archive log it will have much impact on the database performance this solely depends up how u are spreading ur datafiles and how good is ur hardware.
2.Really it is bad idea of keeping the database in noarchive and going for cold backup.If u are ready to loose whole day data then u can.Exactly i can't assume how much time it takes for cold backup.
Lets see other members response on this.

candba, think of sceanario after 18 hours of loading process and before your backup can start, the database crashed and you need to do media recovery, which in your case would be only import from last backup, and you need to reprocess all 18 hours loading process.

import also should take considerable amount of time, and ofcourse 18 hour plus.

Try to configure RMAN for the database and you can take Weekly full backup and after that can go for incremental backup. Test all your scripts on some test environment and implement on the production.

Dilip,

Planning backup solutions is a way to assure an acceptable MTTR. However, it's also important to keep MTBF as high as possible. Run your backups, but do everything you can so you never need to use them. Try to increase redundancy, use RAID.

About copying 800 Gb in 4-5 hours, there are hardware solutions like Symmetrix (EMC) and Flashcopy (IBM). You just need a few minutes to synchronize a mirror or create a flashcopy area, the backup itself is then executed over these areas leaving production alone. However, this doesn't make ARCHIVELOG unnecessary.

Also, being a datawarehouse, it's pretty usual to partition data and keep some tablespace READ-ONLY. These don't need to be backed up always, saving space and time.

Quote:

---

Originally posted by candba

1. If we move to archive log mode , how does it impact the loading time ? we load about 6GB data every day(insets/updates) and it takes about 18 hours. Will archive log result in slower loading time ? Does any one here has similar experience ?

---

18 hours to load 6GB of data? This sounds too long to me. I doubt you will even notice any performance degradation if you put your database in archive log mode.

Quote:

---

2. If we move to cold backup solution in no-archive log mode , is it possible to copy 800GB in 4-5 hours ? Can anyone here please suggest some software/hardware for this purpose ?

---

Enough tape drives and enough paths to those drives, sure, you can do it. Depending on your platform, there may be lots of solutions for you. Some to consider:
http://www.storagetek.com/products/tape_storage.html
http://www.spectralogic.com/

If this were my environment, I would:
1. put my database in archive log mode
2. use rman to take a level 0 backup on the weekend
3. use rman to take level 1 incremental backup during the week.

just curious-what is u'r os ?

:cool:

The usual problem in using export and import as your backup strategy in a data warehouse is that the import process creates all the indexes on the fact tables in a serial manner... therefore you might load any 1GB factor table, but then have to scan it 12 times to build all the indexes... therefore this is a terrible strategy.

As soon as you are on 9i you must consider using recovery manager instead of your current method or the regular backup mechanism of putting tablespaces into backup mode and then copying the files etc

Thanks for all the replies .

we are using solaris 2.6 and moving to 2.8 .

I guess we may have to move to 9i first then probably we will have more options for backup. Infact our management is aware that it will take us upto 72 hours or even more to recover DB completely in case of a crash and they are ok with it.

What is your hardware?

Tamil

Hi,
As I understand, the most important to back up the data in most fast way and no system overhead.
You may want to try third party tool WisdomForce FastReader (www.wisdomforce.com). FastReader very quickly unload extract very big Oracle tables (terabytes of data) into portable flat text files. At same time it generates control files for high-speed loaders like sqlldr. It runs on different plattforms including Solaris. With FastReader the export process should take you minutes.
Hope it helps.

Quote:

---

Originally posted by candba
Thanks for all the replies .

we are using solaris 2.6 and moving to 2.8 .

I guess we may have to move to 9i first then probably we will have more options for backup. Infact our management is aware that it will take us upto 72 hours or even more to recover DB completely in case of a crash and they are ok with it.

---

We're backing up 1TB in 4 hours at the moment. Hardware is an ADIC Scalar i2K library. We backup up to three or four LTO2 drives using three or four RMAN channels (not in the office to check) and I belive that that's currently over IP and not Fibre Channel.