Change sizes of all redo logs using one shell script

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.

The second challenge comes from the requirement of doing all this using one shell script. If redo log group 1 is active then you can not drop it and same is the case for redo log group 2. You have to make sure inside the script that the redo log group you are aiming to drop and recreate is currently not active.

The below PL/SQL code will do it for you and can be used to change the sizes of all redo log groups of any database with very little modification.

You have to figure out the logic by yourself. :)

DECLARE
LOGGROUP NUMBER;
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE ADD LOGFILE GROUP 3 ''/opt/mydata/redo03.log'' SIZE 200M';
SELECT GROUP# INTO LOGGROUP FROM V$LOG WHERE STATUS='CURRENT';
IF LOGGROUP != 3 THEN
WHILE LOGGROUP != 3 LOOP
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
SELECT GROUP# INTO LOGGROUP FROM V$LOG WHERE STATUS='CURRENT';
END LOOP;
END IF;
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE 'ALTER DATABASE DROP LOGFILE GROUP 2';
EXECUTE IMMEDIATE 'ALTER DATABASE ADD LOGFILE GROUP 2 ''/opt/mydata/redo2.log'' SIZE 400M';
EXECUTE IMMEDIATE 'ALTER DATABASE DROP LOGFILE GROUP 1';
EXECUTE IMMEDIATE 'ALTER DATABASE ADD LOGFILE GROUP 1 ''/opt/mydata/redo1.log'' SIZE 400M';
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT';
EXECUTE IMMEDIATE 'ALTER DATABASE DROP LOGFILE GROUP 3';
END;
/