how to change redo log size in oracle 10g

[Former Member]

Hi Experts,

Can anybody confirm how to change redo log size in oracle 10g?

Amit

[former_member524429]

Continue with the Step-3....

STEP-3. Carefully Drop the Old Online Redo Log Groups

a. check the current status of V$LOG Table to verify the Which Log Group is Acive/Current/Inactive.

Drop the Inactive Log Group, which is not critical.

CAUTION:

Consider the following restrictions and precautions:

-> An instance requires at least two groups of redo log files, regardless of the number of members in the groups. (A group comprises one or more members.)

-> You can drop a redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur.

-> Make sure a redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the V$LOG view.

b. To delete old redo log files, use the following command:

alter database drop logfile group <group_number>;

If the redo log file is being used, you cannot delete the file. Initiate a log switch beforehand:

alter system switch logfile;

CAUTION: When a redo log group is dropped from the database, and you are not using the Oracle-managed files feature, the operating system files are not deleted from disk. Rather, the control files of the associated database are updated to drop the members of the group from the database structure. After dropping a redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped redo log files.

*4. Again verify the result of above performed activities by viewing V$LOG and V$LOGFILE views using ST04.*_

I hope the above mentioned steps will be helpful in fulfilling your requirement..

Regards,

Bhavik G. Shroff

[former_member524429]

Continue with Step-2 .....

STEP-2. Add new online red log files with new groups with New Size

a.Execute following commands to Add new logfile group.

e.g.

SQL> alter database add logfile group 15('/oracle/<dbsid>/origlogA/log_g15m1.dbf','/oracle/<dbsid>/mirrlogA/log_g15m2.dbf') size 100M;

b. Confirm the reflection/ expected output of above executed Query, by checking V$LOG and V$LOG_FILE in ST04 as described above.

c. After confirmation, execute the same SQL quesry for adding other new groups as followed.

e.g

SQL> alter database add logfile group 16('/oracle/<dbsid>/origlogB/log_g16m1.dbf','/oracle/<dbsid>/mirrlogB/log_g16m2.dbf') size 100M;

SQL> alter database add logfile group 17('/oracle/<dbsid>/origlogA/log_g17m1.dbf','/oracle/<dbsid>/mirrlogA/log_g17m2.dbf') size 100M;

SQL> alter database add logfile group 18('/oracle/<dbsid>/origlogB/log_g18m1.dbf','/oracle/<dbsid>/mirrlogB/log_g18m2.dbf') size 100M;

Again verify your last activities by checking V$LOG and V$LOG_FILE suing ST04 as described above. you will find the newly added group of redo log file with "UNUSED" status.

Continue with Next Step-3. ...

[former_member524429]

Dear Amit,

You can enlarge the size of existing Online Redo log files, by adding new groups with different size of files (origlog$/mirrlog$) and then carefully droping the old groups with their associated inactive files.

Please refer SAP Note 309526 - Enlarging redo log files to perform the activity.

Steps to perform:

STEP-1. Analyze the exisiting situation and prepare an action plan.

A. You have to ensure that no more than one log switch per minute occurs during peak times.

It may also be necessary to increase the size of the online redo logs until they are large enough.

Too many log switches lead to too many checkpoints, which in turn lead to a high writing load in the I/O subsystem.

Use ST04 -> Additional Functions --> Display GV$-Views

There you can select

Gv$LOG_HISTORY --->for determing your existing LOG switching frequency.

GV$LOG -

---

> list the status(INACTIVE/CURRENT/ACTIVE) /size/sequence no. of existing online redolog files

GV$LOGFILE --- > list the information of existing online redolog files with their storage paths

You can document the existing situation of Online Redo Log Fiile management before going to enlarge Redo Log Files.

It will be helpful, if something goes wrong while performing activities.

B. Based on above Situation analysis, Plan your New Redo Log Group and there Members with new optimal size.

e.g.

Group No.	    Redo Log File Locations  u201C/oracle/<SID>/u201D		        Size
                             /origlogA                  /mirrlogA             
15 		             log_g15m1.dbf         log_g15m2.dbf 	         100 MB
17 		             log_g17m1.dbf 	      log_g17m2.dbf 	         100 MB
                
                            /origlogB                    /mirrlogB
16 		            log_g16m1.dbf          log_g16m2.dbf 		 100 MB
18 		            log_g18m1.dbf 	      log_g18m2.dbf 		 100 MB

Continue to next.....

[Former Member]

Hi Amit,

You can not change the size of any existing redo log. To effect a change in size, you simply have to add new logs of the new size, and then drop the old logs of the old size.

Just bear in mind that you cannot drop a log which is CURRENT or ACTIVE.

Regards,

José

[Former Member]

Hello,

fortunatly, this function was recently implemented into the brtools which makes this really easy. See note #1259767 for details.

Best Regards,

Michael