backup in no-archive log mode production database..
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: backup in no-archive log mode production database..

  1. #1
    Join Date
    Sep 2004
    Posts
    3

    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.

  2. #2
    Join Date
    May 2001
    Posts
    736
    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.

  3. #3
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    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,

  4. #4
    Join Date
    Feb 2001
    Posts
    295
    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.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    Re: backup in no-archive log mode production database..

    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.


    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jul 2003
    Posts
    323
    just curious-what is u'r os ?


  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2004
    Posts
    3
    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.

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    What is your hardware?

    Tamil

  10. #10
    Join Date
    Aug 2004
    Posts
    3
    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.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width