Reduce the number of generated archive logs (Increase their sizes?)

[Former Member]

In order to decrease the number of archive logs and increase the size of it. I tried researching it and read the sdn and notes. Didn't find much information. Does Oracle allows to increase the archive log size or there are other things involved in it. Are there any good oss note about it. We get about 3000 oracle archive logs per week. Our goal is to reduce the archive logs (increase their sizes?). Is it possible if it is can please some one tell me the oss note or recommend me their analysis please

[Former Member]

Hello Asad,

Here is the SAP note you're looking for:

309526 - Enlarging redo log files

You can check you EWA reports, whether enlarging redo logs are recommended. For BW systems, which produces many files especially, it's usually recommended.

The procedure is simple. Let me give you the commands that I use when I need to enlarge redo logs:

To create new log groups:

Alter database add logfile group 15 ('/oracle/SID/origlogA/log_g15m1.dbf','/oracle/SID/mirrlogA/log_g15m2.dbf') size 512M;

Alter database add logfile group 16 ('/oracle/SID/origlogB/log_g16m1.dbf','/oracle/SID/mirrlogB/log_g16m2.dbf') size 512M;

Alter database add logfile group 17 ('/oracle/SID/origlogA/log_g17m1.dbf','/oracle/SID/mirrlogA/log_g17m2.dbf') size 512M;

Alter database add logfile group 18 ('/oracle/SID/origlogB/log_g18m1.dbf','/oracle/SID/mirrlogB/log_g18m2.dbf') size 512M;

To see the actual status of log groups:

SQL>desc  v$logfile;

SQL>desc  v$log;

To drop old ones, after creating new ones:

alter database drop logfile group 11;

To inactivate the log files, which are being used:

alter system checkpoint;

Regards,

Serhat

[Former Member]

Hello, All thanks for great input. But I have a question my dilemma is weather I should increase the file size or should I add the group? How should I determine that?

[fidel_vales]

Hello,

the "basic" rule would be:

What is the time between logswitches on peak time?

If you are doing logswitchess in less than 1 minute then increase the size

Are you finding the event "log file switch (checkpoint incomplete)"?

increase the number of redo log groups.

[Former Member]

As I've already written above:

You can check you EWA reports, whether enlarging redo logs are recommended. For BW systems, which produces many files especially, it's usually recommended.

[Former Member]

my dilemma is weather I should increase the file size or should I add the group?

As you wrote in the subject, your objective is to reduce the number of generated archive logs (per day, per hour, or per whatever unit of time).

This should resolve your dilemma.

Archive logs are roughly about the same size as the online redo log files. If you increase their sizes, you will need a smaller number of  them.

On the other hand, a greater number of online redo log files (or groups) will not significantly change the number of files that will have to be copied from the online redo log files to the archive log directory.

hope this helps

[fidel_vales]

hi,

You ask me, but I need to ask you. I need to know what is your objective:

asad tanwir wrote:

In order to decrease the number of archive logs and increase the size of it. I tried researching it and read the sdn and notes. Didn't find much information. Does Oracle allows to increase the archive log size or there are other things involved in it. Are there any good oss note about it. We get about 3000 oracle archive logs per week. Our goal is to reduce the archive logs (increase their sizes?). Is it possible if it is can please some one tell me the oss note or recommend me their analysis please

your current redo log file size is 512 Mb.

You want to increase it size, it is not clear why.

Because, per week you have 3000*500 => aprox 1500 GB of redo log generated.

If your objective is to reduce the 1500 GB, sorry. you will have to investigate why you are generating "so much", if there are operations that you do not need to do or something like that. If there are no "weird" operations, then that is the amount that oracle needs to generate for your "normal" operation and that willl increase as you increase your "activity"

If you generate 3000 logs, that means you are doing 3K logswitches and you want to reduce that for "whatever unknown (for me)" reason. In that case you simple increase the size of the online redo logs

Brindavan will get angry with me but I do not (fully) agree with him.

If your redo log size is 512 and you want to increase it to 1024 (for example) you do not need to change or "play" with the parameters he mentioned (a lot of customers do not even use them) he indicates to add groups so you will have different sizes as the old files will be smaller than the new ones while having all the same size is better.

NEVER EVER have one member per group unless you are using ASM

So, if you simply ask to increase the log size (for whatever reason) the easiest way to change the size is to use BRSPACE, it can do that on one go for all. Check the note 1259767 and the BRTOOLS documentation for more information.

[Former Member]

Thanks Fidel, for great information..I like to answer you first question what is our object?

Our object is to reduce the logs... For that if we have to make the size of log big so that way logs which gets generated are fewer.

Why do we need to reduce the number of logs?

The reason for that is we have script that we created in house for the SYSTEM REFRESH it does not work if the logs are too many like in our case 3000 a week. Script fails every time we run it due to too many logs... Hopefully i have answered all of your question based on this information what do you suggest in our case?

Thanks

Asad Tanwir

[fidel_vales]

Hi,

Thanks for the information.That is an interesting reason.

You need to figure out the amount of redo logs you "need" and increase the size acordingly. For example, in theory doubling the size would produce half of the logs.

As mentioned, you can do it with BRSPACE. The procedure is the note 1259767. There is an option to resize all redo logs in "one go". If you use it do it on period of low activity

[Brindavan_M]

Hi Asad,

First check the size of the archive file as said by "Fidel vales". for checking the size and increase  the following command will help you to increase the archive log size.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE                             VALUE

———————————— ——————————– ————-

db_recovery_file_dest                string                           +ORAFLASH001

db_recovery_file_dest_size         big integer                    10G

Change the Archive log as per need as:

SQL>ALTER SYSTEM SET db_recovery_file_dest_size = 20G SCOPE=BOTH SID=’*’;

Check the parameter again to verify the updates:

SQL> show parameter db_recovery_file_dest

N

AME                                 TYPE                             VALUE

———————————— ——————————– ————-

db_recovery_file_dest                string                           +ORAFLASH001

db_recovery_file_dest_size         big integer                    20G

or you can reduce the group member

3 Log Groups with 1 member each.

alter system set db_create_file_dest='';

alter database add logfile group 1 size <  >;

By this way the system will only find the path of /oracle/oraarch and therefore will create only 1 log member.

Thanks,

Brindavan M

[Former Member]

Hello Fidel,

What is your take on this please let me know thanks

[fidel_vales]

Hi,

What is the size of your redo logs?

Of course Oracle allows to increase it. SAP ships with a default of 50 MB which, usually, is too small.

there is no "one size fits all". the SAP general recomendation is that, during peak periods, you do not have more than one logswitch per minute.

[Former Member]

Hello,

Thanks for responding, My current sizes are below:

-->archive log---> 217.92 gig till 239.00 is average

-->redolog --->mirror log--> 256.00 gig

[Former Member]

attach is the screen shot of the file system

[fidel_vales]

hi,

I will rephrase my question so it is better understood.

what is the size of your individual online redo logs?

[Former Member]

individual file size is 512 MB