Rman backup & Recovery for a datawarehouse noarchivelog mode
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Rman backup & Recovery for a datawarehouse noarchivelog mode

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    110

    Rman backup & Recovery for a datawarehouse noarchivelog mode

    Hi,

    I need some help on RMAN backup & recovery strategy for a 300GB datawarehouse running Oracle 9i R2.

    Our datawarehouse cannot turn on archiving due to performance issues.
    We need a faster backup strategy, that is why I am exploring RMAN B&R.
    2 concerns our application team have raised regarding the new backup strategy:
    1) need less down time, faster backup
    2) ability to restore the database to the required date.

    My RMAN backup strategy is:
    - Sunday (cumulative incremental level 0)
    - Mon - Tue (cumulative incremental level 2)
    - Wed (cumulative incremental level 1)
    - Thur - Sat (cumulative incremental level 2)
    (currently, rman backup to disk and using Recovery Catalog)


    I have tested this strategy, and I have no problem recovering until the last good backup.

    However, I am not able to restore the database to a specified date(i.e. last good backup is Thur, and need to restore database to Tue).

    Anyone has any suggestions?
    How about recovery to the previous incarnation, will this work?

    Cheers
    Oracbase
    Last edited by oracbase; 05-10-2004 at 07:45 AM.
    What's next after 10g?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I would love to know how you plan to recover to a particular point in time without archivelogs.
    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."

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by marist89
    I would love to know how you plan to recover to a particular point in time without archivelogs.
    ditto ,what performance concerns do you have about turning on archiving?
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Rman backup & Recovery for a datawarehouse noarchivelog mode

    Originally posted by oracbase
    I have tested this strategy, and I have no problem recovering until the last good backup.
    I even doubt this...
    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."

  5. #5
    Join Date
    Apr 2001
    Posts
    110
    Dear Jeff,

    Why do you doubt this strategy recovering to the last valid backup?

    With once a week cumulative level 0, daily cumulative level 1 or 2, I can recover using level 0 and the last cumulative level 1 or 2.

    The performance issue we have regarding archivelog on is during data loading into the warehouse. We are not using SQL Loader, turning on NOLOGGING during data loading has not effect.

    Cheers
    Oracbase
    What's next after 10g?

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    Originally posted by oracbase

    The performance issue we have regarding archivelog on is during data loading into the warehouse. We are not using SQL Loader, turning on NOLOGGING during data loading has not effect.
    That makes no sense, yes you will generate redo.

    Tell me again why archive log mode kills your performance
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Apr 2001
    Posts
    110
    Hi All,

    My test case for RMAN B&R with noarchivelog database is:

    Unix environment:
    export NLS_LANG=american
    export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS'

    RMAN environment:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
    CONFIGURE CONTROLFILE AUTOBACKUP ON;
    CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
    (leave the rest default)

    Using Recovery Catalog, perform RMAN backup and data load

    1) Level 0 incremental cumulative backup
    2) load 10000 records, alter system switch logfile; x 3
    3) Level 1 incremental cumulative backup
    4) load 10000 records, alter system switch logfile; x 3
    5) Level 2 incremental cumulative backup


    Test Recovey:
    shutdown immediate;
    startup nomount;
    list incarnation;
    reset database to incarnation #
    (# is the Incarnation No. prior to rman backup)
    run {
    set until time '2004-05-12:15:03:00';
    restore controlfile;
    alter database mount;
    restore database;
    recover database;
    alter database open resetlogs;
    }


    I managed to recover to both Level1 and Level2 backups sucessfully.

    Appreciate any feedbackup.

    Cheers
    Oracbase
    What's next after 10g?

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ==========
    Our datawarehouse cannot turn on archiving due to performance issues.
    We need a faster backup strategy, that is why I am exploring RMAN B&R.
    2 concerns our application team have raised regarding the new backup strategy:
    1) need less down time, faster backup
    2) ability to restore the database to the required date.
    =============================

    You need "the best" perf team to turn on archivelog.

    Why do the app team raise a concern about backup strategy? Only the DBA needs to worry about the backup and restore.

    For faster backup, use EMC's BCV.

    To restore the DB to a previuos time stamp, you need to turn on archivelog. I run all my DBs (dev, test, preprod, prod) in archivelog mode. No problem. I can write 1.5 GB worh of redo log files in less than 1 min.

    300 GB size is a very small database in 21st century.

    Tamil

  9. #9
    Join Date
    Oct 2002
    Posts
    807
    oracbase - Do you have a question any longer? From what I gather, you're doing COLD incremental backups (with different levels) on a NOARCHIVELOG database through the week. And are now able to restore to a particular day (not really 'point in time'). Fine. What's the big deal? That's the way it works.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Hopefully, this will engender some controversy.

    * Ahem *

    Run your data warehouse in NOARCHIVELOG mode. Screw archiving.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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