Home » RDBMS Server » Backup & Recovery » Incremental backups are huge (10.2.0.3)
Incremental backups are huge [message #301070] Tue, 19 February 2008 02:23 Go to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
Good morning,

I'm having a very particuliar problem right now.

My company has bought a product which it turns out has very generic tables generating lots of records to process an information.

Right now, for an information (I don't really want to give some business vocabulary) it generates a 100 records. It's wild!

It has created a lot of problems, on data files size, performance, backups and finally network (backups over the network).

Right now the database is 135 GB and growing 10 GB a month.

I'm trying to explain that the reason of all this pain is at the beginning a poor DB structure.

Currently we are doing incremental backups. The full backup weights about 175 GB and incremental backups can weight about 100 GB if someone run a report there's a weekly report.

That person mentioned to me that his report does only SELECT queries. He does that through the application though.

So right now people come to me saying, how come the incremental backups are so big and how come the full backups are bigger than the DB itself?

Additional information on the backups: there is no windowing recovery period or duplication on RMAN. It goes straight on tape (clogging the network on the way) every day.

So here are my questions:

1) What transactions generates archive logs?
2) Would a poorly structured DB accentuate the trend of archive logs generation? I tend to say yes but I have no way to prove that. Anybody ever faced this kind of demonstration problem? Any metrics I could gather to support my theory?

It's a difficult subject but any thoughts would be welcome.

Thanks,

Nico
Re: Incremental backups are huge [message #301091 is a reply to message #301070] Tue, 19 February 2008 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) All of them. As soon as you modify, you generate logs
2) It could. To prove it you have to build a correct model and compare the 2.
You can create a small example based on your current one.

Regards
Michel
Re: Incremental backups are huge [message #301093 is a reply to message #301070] Tue, 19 February 2008 03:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> how come the incremental backups are so big
Oracle never said the incrementals are slower/lesser.
It is the backup after the last full backup. May your database did more work after the last full.
>> how come the full backups are bigger than the DB itself
Full backups are backups + archived logs.
How frequently are you purging the logs?
>>What transactions generates archive logs?
You know what.
inserts/updates/deletes/DDL (if objects are created with logging options).
In certain cases, even just a select statement may create redo (not directly. Undo creates redo).
>>Any metrics I could gather to support my theory?
Look for scheduled jobs (some still do that index rebuilds), data loading procedures etc.
Re: Incremental backups are huge [message #301111 is a reply to message #301070] Tue, 19 February 2008 03:55 Go to previous message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
Hi.

Thanks for your answers guys.

It's pretty much what I needed.
Actually, I've got so much questioning from unkonwledgeable people that I started questioning my fundamentals x)

It's good to have people reminding what they are.

I'm more confident.

It's not possible for me to re-think a complete structure of this complexe application.

I did show an example using the classical HR DB with employees and departments showing the differences between a generic table having it all and another structure with tables divided by entity.

The problem seems to be politics, not DBA :/
Previous Topic: RMAN-Backup prod to development
Next Topic: RMAN-03009/ORA-19513
Goto Forum:
  


Current Time: Sun Dec 11 06:21:12 CST 2016

Total time taken to generate the page: 0.29040 seconds