[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

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.
0
david-marti
Asked:
david-marti
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
schwertnerCommented:
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 ...
0
 
mrjoltcolaCommented:
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.
0
 
MikeOM_DBACommented:

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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
MikeOM_DBACommented:
PS: Try the 11g compressed backup, you will be surprised at the compression ratio and speed.
 
0
 
mrjoltcolaCommented:
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.
0
 
schwertnerCommented:
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
0
 
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?
0
 
schwertnerCommented:
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.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now