AbdulWasiq

Check redo log group status:

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL>

Drop old redo log group which is inactive using below command:

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 2;


Add the redo log group with the require size :

ALTER DATABASE ADD LOGFILE GROUP 1 ('+DATA','+DATA') SIZE 300m;

ALTER DATABASE ADD LOGFILE GROUP 2 ('+DATA','+DATA') SIZE 300m;

switch logfile and make inactive third log group and drop it.

ALTER DATABASE DROP LOGFILE GROUP 3;

ALTER DATABASE ADD LOGFILE GROUP 3 ('+DATA','+DATA') SIZE 300m;

ALTER DATABASE ADD LOGFILE GROUP 4 ('+DATA','+DATA') SIZE 300m;

ALTER DATABASE ADD LOGFILE GROUP 5 ('+DATA','+DATA') SIZE 300m;


col member for a35
SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                   BYTES
---------- ----------------------------------- ----------
         3 +DATA/BIPROD/ONLINELOG/group_3.284.  314572800
           912853377

         3 +DATA/BIPROD/ONLINELOG/group_3.267.  314572800
           912853385

         2 +DATA/BIPROD/ONLINELOG/group_2.283.  314572800
           912853625

         2 +DATA/BIPROD/ONLINELOG/group_2.265.  314572800
           912853785

    GROUP# MEMBER                                   BYTES
---------- ----------------------------------- ----------

         1 +DATA/BIPROD/ONLINELOG/group_1.282.  314572800
           912853269

         1 +DATA/BIPROD/ONLINELOG/group_1.263.  314572800
           912853307

         4 +DATA/BIPROD/ONLINELOG/group_4.266.  314572800
           912854001

         4 +DATA/BIPROD/ONLINELOG/group_4.268.  314572800

    GROUP# MEMBER                                   BYTES
---------- ----------------------------------- ----------
           912854067

         5 +DATA/BIPROD/ONLINELOG/group_5.264.  314572800
           912854213

         5 +DATA/BIPROD/ONLINELOG/group_5.286.  314572800
           912854339


10 rows selected.

SQL>



We have 5 redo log group and each group contain 2 members of sizes 300m.

Below setting will generate archive for every 10 mins .

SQL> alter system set archive_lag_target=600 scope=spfile;

System altered.

SQL>