I had this rather surprisingly difficult puzzle of changing sizes of all members of both redo log groups this past weekend. And that too using one shell script. There are two challenges here.
First is the fact that there is no resize option available for redo logs and my database had only two redo log groups which is the minimum requirement. Oracle writes on all members of a redo log group simultaneously and once it is full it switches to the next group. In order to change to the size of the members of any redo log group you had to drop that group first and then add again with the desired size. Having only two redo log groups means that you can't drop any of them; Oracle won't let you do that.
The solution to this first problem is to add an interim redo log group, drop the first redo log group and recreate it with desired size, then drop the second redo log group and recreate it and finally drop the 3rd interim redo log group.
First is the fact that there is no resize option available for redo logs and my database had only two redo log groups which is the minimum requirement. Oracle writes on all members of a redo log group simultaneously and once it is full it switches to the next group. In order to change to the size of the members of any redo log group you had to drop that group first and then add again with the desired size. Having only two redo log groups means that you can't drop any of them; Oracle won't let you do that.
The solution to this first problem is to add an interim redo log group, drop the first redo log group and recreate it with desired size, then drop the second redo log group and recreate it and finally drop the 3rd interim redo log group.
