Skip to Main Content
  • Questions
  • How frequent should i backup Archive logs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Fahd.

Asked: December 01, 2020 - 12:12 pm UTC

Last updated: December 07, 2020 - 3:26 am UTC

Version: 11.2.0.1.0

Viewed 100+ times

You Asked

i take complete database backup once daily with following command

backup as compressed backupset database  plus archivelog;
delete noprompt obsolete;


now i am planning to backup archive log every 5 mints with following command
 backup archivelog all; 

my archivelog deletion policy is 1 'times to DISK', so there will not be duplicate backups of same log files.

i am doing this because i could not do complete recovery of one of my database due to missing archive log.

my question is that, is this a good approch ? or there is any better way to be able to do complete recovery ?



my RMAN settings are as folows

 
 configure controlfile autobackup on;
 configure backup optimization on;
 configure retention policy to redundancy 5;
 configure archivelog deletion policy to  backed up 1 times to DISK;
 configure retention policy to recovery window of 7 days;

and we said...

The big thing here is what was the *cause* of a missing archive log?

Was it disk failure? If so, they should be mirrored
Was it human error? If so, then that process should be corrected
If it was a complete server/site failure, then perhaps DataGuard should be explored so you have a near real-time copy of your data.

Backing them up more frequently or having more copies of them is worth pursuing as long as you can justify the reasons why you need to do so. Because (for example) if archives go missing because of human error (either directly or indirectly via say a cleanup script) then really I'd be tackling that first.

In my experience, people fall into a few broad categories

a) they don't want lose their database no matter what, so they have DataGuard to ensure that everything (including archive logs) is being sent to an alternate location (where that might be an entirely different site, or a different data centre, or even just a different server).

b) they're confident in their infrastructure that they wont lose archivelogs unless a major incident occurs, so they back them up perhaps once per day

c) a hybrid where DataGuard is not in use, so they ship their archivelogs manually to an alternate destination (either via RMAN backups or home grown scripts).

Hope this helps


Rating

  (3 ratings)

Comments

how to Mirror archive log

Fahd, December 02, 2020 - 7:33 am UTC

" Was it disk failure? If so, they should be mirrored "

electricity went down and server was powered off, that was what happened. after that one of the archive log went missing.

if i do Mirroring of archive log will it solve the problem ? how to Mirror ?
Connor McDonald
December 04, 2020 - 3:30 am UTC

Well....on power up, we don't need the *archive* log, just the redo logs for instance recovery.

And if the archivelog was incorrectly written to disk, then when you went to back it up with RMAN, I'm pretty sure it would have given you an error.

So I'm not entirely convinced the backup *frequency* here is the solution, but definitely you have the right philosophy of being very focussed on the integrity of your archive logs because obviously they are crtical.


Recovery on Power Off

A reader, December 05, 2020 - 12:33 pm UTC

"Well....on power up, we don't need the *archive* log, just the redo logs for instance recovery"

yes you are right, but i still don't fully understand how to recover from power failure, because i have faced this problem many times. and i still don't have a clear cut line of action to take after power failure. if you can give me some commands to start with, it will be a great help.

Thanks
Connor McDonald
December 07, 2020 - 3:26 am UTC

You should *never* have to do a database recovery after a power failure because instance recovery is automatic.

If you *do* need to do a manual recovery, this is indicative of some potential issue with the integrity of the underlying storage on power loss. That is a big deal because if you can't have trust in your storage, then you can't have trust in your redo logs...

..which means you *will* one day day lose data, or worse.

Recovery after power failure

A reader, December 07, 2020 - 7:35 am UTC

"if you can't have trust in your storage, then you can't have trust in your redo logs..."

redo logs are multiplexed (3 copies) , so i think i can trust them, i have fully recovered the database from power failure many times, by doing following.

1) copying one of the redo log file and replacing with other two
2) RESTORE DATABASE
RECOVER DATABASE


but some times i could not recover, and had to use imp/exp DMP files, so i don't fully understand, how to recover database from this situation.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database