Best Way to Handle Archive Logs

[Anurag Tripathi]

I have recently introduced archiving on my instance. The Archival logs are increasing hugely and will soon need to be addressed. i am sure i am not the only one who has come across this so i just want to see what others have done to address this.

[Anurag Tripathi]

no no, thats not my question.

My question is, since archive logs are increasing exponentially, we need to take steps to manage it in a way that it doesn't impact us. Now what are my options to handle it?

[sergiu_panaite]

What problem you have with the fact that archive log is big? Can you give some examples?

[Anurag Tripathi]

I am working on an on premise instance and one of our major concern is huge db size, archiving will take data off the primary tables but will also contribute in increasing the complete db size by added logs. We cannot delete logs as its important if we need to restore records(from what i understand).

[sergiu_panaite]

You're right, the goal of archiving is to move data from primary tables so access to those tables remains fast.

Archiving is not going to reduce disk space footprint indeed.

I would check what are the biggest tables contributing overall database size increase and see if any possibilities of cleanup are available. As an example, sys_attachment_doc is one of the largest tables, but in many cases contains lots of duplicate attachments, and a lot of non-archived attachments (ex, plain text files with size over 500MB/1GB). Archiving the files that make sense, removing duplicates can significantly reduce overall database size. It requires some work, but I believe it can be automated up to some levels.

[Anurag Tripathi]

Agreed, but still coming back to the mail issue, is there anything we can do to address logs. to address other stuff only we have enabled archiving

Can you confirm if we will get any benefit if we put archival log on table extension (table rotation of type extension) and also, if we enable it then will we be able to restore the records?