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

I would love to know how you plan to recover to a particular point in time without archivelogs.

Quote:

---

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?

Quote:

---

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

---

I even doubt this...

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

Quote:

---

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

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

==========
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

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.

Hopefully, this will engender some controversy.

* Ahem *

Run your data warehouse in NOARCHIVELOG mode. Screw archiving.

Hi All,

I finally understood what I have done for this test case.

I am actually performing a (noarchivelog mode) restore from an older RMAN backup.

It works perfectly.

Regarding the data load performance issue on a archive log mode database, I tested it out loading the same millions of records from SQLPLUS and the timings for noarchivelog and archivelog are the same.

So, I suspect the issue might be on the datawarehouse loading engine.

Thanks all for your wonderful comments.

Cheers
Oracbase

Quote:

---

Originally posted by oracbase
So, I suspect the issue might be on the datawarehouse loading engine.

---

Informatica?