Below are the steps on how to add a new online redo-group. It could be of the same size as other groups or different size.
1) List log groups, their archive status, activity status and size
SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;
GROUP# ARC STATUS BYTES
———- — —————- ———–
1 NO ACTIVE 104857600
2 NO CURRENT 104857600
3 NO INACTIVE 104857600
2) Add a new log group with a different size and query v$log to show the activity status of the new log member is UNUSED
SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;
Database altered.
SQL> select group#, archived, status, bytes from v$log;
GROUP# ARC STATUS BYTES
———- — —————- ———-
1 NO CURRENT 104857600
2 NO INACTIVE 104857600
3 NO INACTIVE 104857600
4 YES UNUSED 209715200
Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”
To drop the online redo log, the files in the group has to be dropped manually.
SQL> select * from v$logfile where group# = 3;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG NO
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG NO
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
alexlima.com 