Oracle backup: archivelog and tablespaces

Hi experts,

We have an ORACLE 11g and we are planning his online backup.

We have 3 tablespaces but 2 not need to be backed up. Now we have the database in NOARCHIVELOG but we are planning to move to ARCHIVELOG.

First question: Can be ARCHIVELOG activated at tablespace level or only at whole database? Why I ask it? Because when we activate the ARCHIVELOG mode, the database performance goes down (in some moments we have a lot of bulk operations).

Second question if the answer to the first question is not: Can I use the NOLOGGING attribute for a tablespaces not required to be backep up. It's a good strategy?

There are other methods to make the same online backup?

As you can see, ower goal is make an online backup for noly one tablespace and mantein as good performance as posible.

Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1. Archivelog can be activated only on DB level. To speed up - increase the size of the Online Redo Log Groups and the size of the files there.
2. NOLOGGING can not help to avoid logs. It runs only in some very special cases - direct path load, INSERT APPEND ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Agree with schwertner. Be careful about selective backups, though. Sometimes you can leave yourself with a confusing situation in restore if the database will not open due to all tablespaces not being consistent. I think I have done this by putting tablespace offline and then recovering. If the tablespace is offline, Oracle will not try to recover that tablespace.

a) Set database in archive log mode.

b) Set those tablespaces "not requiring backup" as 'READ ONLY' .

c) Use RMAN to do backups:

1) Do first full backup.
2) Backup read-only tablespaces with 'KEEP FOREVER' option.
3) Do subsequent full/incremental backups using 'SKIP READONLY' option.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PS: Try the 11g compressed backup, you will be surprised at the compression ratio and speed.
MikeOM has a good suggestion, but you must make sure if you EVER bring the tablespaces out of READ ONLY mode to make even a slight change, you MUST immediately do a full backup. If not, in the event of a failure, Oracle will want every archive log from the very first backup of the read-only tablespace to recover (could be months or a year). It is not smart enough to know otherwise.
There is no indications that the tablespace doesn't experience inserts and updates.

About the compressed backups and RMAN: Some askers are also surprised by Oracle bugs in SYSAUX tablespace
david-martiAuthor Commented:
Thanks for your replies!! all are very usefull

schwertner, can you explain a little bit your second point answer:

NOLOGGING can not help to avoid logs. It runs only in some very special cases - direct path load, INSERT APPEND ...
If I have a tablespace with NOLOGGING, as far as I know, only minimal information is saved on redo logs (and not usefull for recovery purposes, of course). Then, I supose the speed/performace on this tablespace must be higher than a tablespace with LOGGING option set. Is this correct?
NOLOGGING is misleading parameter. People think that it excludes logging.
It only excludes DDLs registration (but this is under question!).
But if you set NOLOGGING only some kind of INSERT statement will not
make log records:

1. INSERTS with SQL*Loader using Direct Path
2. INSERT statements with /*append*/ hint

For other DMLs it is not possible to exlude logging.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.