Solved

Oracle backup: archivelog and tablespaces

Posted on 2009-07-08
8
992 Views
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.
0
Comment
Question by:david-marti
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 47

Accepted Solution

by:
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 ...
0
 
LVL 40

Expert Comment

by:mrjoltcola
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.
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
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.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 24803586
PS: Try the 11g compressed backup, you will be surprised at the compression ratio and speed.
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:mrjoltcola
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.
0
 
LVL 47

Expert Comment

by:schwertner
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
0
 

Author Comment

by:david-marti
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?
0
 
LVL 47

Assisted Solution

by:schwertner
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now