Oracle backup: archivelog and tablespaces

Posted on 2009-07-08
Last Modified: 2013-11-11
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.
Question by:david-marti
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 48

Accepted Solution

schwertner earned 375 total points
ID: 24802495
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 ...
LVL 40

Expert Comment

ID: 24803326
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.
LVL 29

Assisted Solution

MikeOM_DBA earned 125 total points
ID: 24803563

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.
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 29

Expert Comment

ID: 24803586
PS: Try the 11g compressed backup, you will be surprised at the compression ratio and speed.
LVL 40

Expert Comment

ID: 24803636
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.
LVL 48

Expert Comment

ID: 24803693
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

Author Comment

ID: 24811301
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?
LVL 48

Assisted Solution

schwertner earned 375 total points
ID: 24811319
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.

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question