Oracle REDO Script generation - AWS RDS

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
    v_group_id NUMBER;
    v_status VARCHAR2(20);
    v_log_count NUMBER;
    v_loop_count NUMBER := 0;
    v_max_loops NUMBER := 10; -- Prevents infinite loops
    v_min_required_logs NUMBER := 2; -- Oracle requires at least 2 redo log groups
    v_logfile_added BOOLEAN := FALSE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('===== STARTING REDO LOG DROP PROCESS =====');

    -- Check the current number of redo log groups
    SELECT COUNT(*) INTO v_log_count FROM V$LOG;

    -- If the number of redo logs is below the minimum required, add more logs
    IF v_log_count <= v_min_required_logs THEN
        DBMS_OUTPUT.PUT_LINE('Adding new redo log groups to maintain minimum requirement...');
        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.add_logfile(bytes => 1073741824); END;';
        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.add_logfile(bytes => 1073741824); END;';
        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.add_logfile(bytes => 1073741824); END;';
        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.add_logfile(bytes => 1073741824); END;';
        v_logfile_added := TRUE;
    END IF;

    LOOP
        -- Check the current number of redo log groups again
        SELECT COUNT(*) INTO v_log_count FROM V$LOG;

        -- If the number of redo logs is too low, stop dropping
        IF v_log_count <= v_min_required_logs THEN
            DBMS_OUTPUT.PUT_LINE('Minimum required redo logs reached. Exiting drop process.');
            EXIT;
        END IF;

        -- Find the first redo log group that is INACTIVE or UNUSED
        BEGIN
            SELECT GROUP# INTO v_group_id 
            FROM V$LOG 
            WHERE STATUS IN ('INACTIVE', 'UNUSED')
            AND ROWNUM = 1;

            -- Drop the inactive log group
            DBMS_OUTPUT.PUT_LINE('Dropping Log Group: ' || v_group_id);
            EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.drop_logfile(grp => ' || v_group_id || '); END;';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('No more INACTIVE or UNUSED logs to drop. Exiting.');
                EXIT;
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error while dropping log group: ' || SQLERRM);
                EXIT;
        END;

        -- Perform a checkpoint to clear active logs
        DBMS_OUTPUT.PUT_LINE('Performing Checkpoint...');
        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.checkpoint; END;';

        -- Switch log file to move logs into INACTIVE state
        DBMS_OUTPUT.PUT_LINE('Switching Log File...');
        EXECUTE IMMEDIATE 'BEGIN rdsadmin.rdsadmin_util.switch_logfile; END;';

        -- Verify status after dropping and switching logs
        DBMS_OUTPUT.PUT_LINE('Verifying log status...');
        FOR rec IN (SELECT GROUP#, STATUS FROM V$LOG ORDER BY GROUP#) LOOP
            DBMS_OUTPUT.PUT_LINE('Group#: ' || rec.GROUP# || ' | Status: ' || rec.STATUS);
        END LOOP;

        -- Stop the loop after a certain number of cycles to prevent infinite looping
        v_loop_count := v_loop_count + 1;
        EXIT WHEN v_loop_count >= v_max_loops;
        
        -- Pause briefly before checking again
        DBMS_LOCK.SLEEP(2);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('===== REDO LOG DROP PROCESS COMPLETED =====');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No INACTIVE logs found. Exiting.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

/