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?
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.
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
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.
==========
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.
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.
Reply With Quote
Bookmarks