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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

627 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