on 11-11-2009 3:09 PM
Hi
Currently, we are in 9.2.0.7.0 oralce version and having redolog file sizes (Mirrlog and origlog) of 100MB.
Now we are planning to increase the size to 200 MB so that we could reduce the number of archive log files.
Can you please let me know what would be the demerits of bigger size in redolog files?
And also let me know the step by step process how to increase the size of redolog files?
Thank you
> Now we are planning to increase the size to 200 MB so that we could reduce the number of archive log files.
Sorry, but why the heck do you actually care about the number of archive log files?
The only thing you've to care about is whether there is enough storage space to store all of them until they've been backed up properly so that they can be removed from the log file destination.
A valid reason for enlarging the redolog size would be to reduce the number of log switches when you're system is busy changing data all the time.
But the total number of archive log files should never ever be a topic of consideration.
You have to take care of all of them - always.
just my 2p.
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lars
I understand what you are saying but in our situation our backup policy is one time online backup and one time offline backup in a week.....Online backup is on Thu and Offline backup is on Sunday.......
In case of system crash if needed we would need to apply archive log files; If we have lesser number of archive logs; recover database would be faster.......correct me if am wrong.
Hi,
Lets see
> Online backup is on Thu and Offline backup is on Sunday
I hope you are not talking about your productive system.
Now, lets assume your current situation.
Lets assume that between backups you generate 100 offline redo log files. That is 100x100 = 10000 Mb that you have to backup.
Lets assume that you increase the size to 200 Mb
Lets also assume that you have exactly the same activity in your system as before.
That means you will benerate 50 offline redo log files. Thats is 50x200 = 10000 Mb of redo log that you generated.
EXACTLY the same amount as before.
I cannot see any benefit here.
> In case of system crash if needed we would need to apply archive log files;
Depends, you can start the offline backup without applying redo log, of couse, doing so wil means that you will lose all data between your backup and the crash.
> If we have lesser number of archive logs; recover database would be faster
Here is where you are wrong. The number of files does not matter, it matter the ammount of redo you have to apply and in both cases it will be the same.
There are multiple reasons for having bigger redo log files, like avoid having too frequent logswitches on peak times.
But, IMHO, increasing the size for your reason it does not make sense.
Also you have to find a balance with the size, because if you lose one redo you may lose more data.
Hi Fidel
I hope you are not talking about your productive system.
Yes I am speaking about our production system - Due to diskspace- that is management's decision
Here is where you are wrong. The number of files does not matter, it matter the ammount of redo you have to apply and in both cases it will be the same.
You may be right but what I am thinking is applying archives manually one by one definitely will take long time when more archives are there. - COrrect me if it is wrong.
Also you have to find a balance with the size, because if you lose one redo you may lose more data.
What do you mean balance with the size?
> I understand what you are saying but in our situation our backup policy is one time online backup and one time offline backup in a week.....Online backup is on Thu and Offline backup is on Sunday.......
>
> In case of system crash if needed we would need to apply archive log files; If we have lesser number of archive logs; recover database would be faster.......correct me if am wrong.
You are wrong.
Ok, let's see an example:
You took your backup on sunday midnight and your DB needs recovery on wednesday.
Meanwhile you created say, 800 M worth of redolog data per day.
That sums up to (monday, tuesday, wednesday) 3x800 M = 2400 M that need to be recovered.
Going with your current setup (100 M redolog size) the largest archivelog file can be 100 M, makes 24 files to restore and recover.
After changing the redologsize to, say 200 M, you only have 12 files to restore and recover.
But know what? It's still 2400 M of data.
Since you will likely not put every archivelog file to its own tape, but rather change the tape each day (just an assumption) or maybe don't use manually operated tapes at all, the little latency overhead in handling tapes doesn't count in to your overall recovery time.
All in all you still need to feed the same amount of data to the recovery process.
Apart from this:
if you're discussing short recovery times, than you'd never perform just two data backups a week.
You'd make online backups every day - maybe incremental ones.
You' d use the flashback recovery area.
An additional thing often overlooked: in many cases the ultimate performance killer for a restore/recovery scenario is not the technology in use.
It's that when the case is there, the DBA is not sure anymore, what do to.
He wonders:
Where the good backups are.
How to get them back from the 3rd party backup tool.
How to check them.
Where to get a different storage system because the original one is broken.
How to figure out what needs recovery
How the tools work
By ensuring that you always master the theory and the how to of restore and recovery - that's how you make it quick and painless (and dataloss-less).
regards,
Lars
> You may be right but what I am thinking is applying archives manually one by one definitely will take long time when more archives are there. - COrrect me if it is wrong.
Sorry - just saw your re-reply now after I finished my other answer.
Man - you NEED to teach yourself how the tools work.
You NEVER want to apply your archive log files manually one by one.
You don't have to.
Even if you're just using SQL*PLUS (something that you really shouldn't!), you want to use the automatic recovery mode.
Do yourself a favour.
Read the Oracle Concepts Guide, the Backup/Recovery Guide PLUS the DBA Guide for SAP.
Get used to the tools.
Try it out on your playground database instance.
Once you fell very sure that you master this topic - ask your co-workers to destroy your database.
And then: restore/recover it.
When you passed this test a few times, THEN start planning your backup system for your production system.
Before that - sorry to be that direct - you're not qualified to do it properly.
regards,
Lars
Hi,
You have listed one of the best reasons to have a bigger redolog size yourself.
Best way of doing it step by step:
http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_34.shtml
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
it's better to have more groups and make each of them bigger.
making them bigger, if there are not a lot of activity will take a long time before an archive log switch. You will see this especially on quiet system such as DEV or Sandbox.
You can force a log switch by setting archive_lag_target to 3600 (Which forces a switch every hour).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.