RESIZE REDOLOG FILE IN ORACLE 12c

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2   14 52428800 YES INACTIVE
3   15 52428800 YES INACTIVE

SQL> alter database add logfile group 4 '/ora/app/oracle/oradata/inforln/redo04.log' size 6G;

Database altered.

SQL> alter database add logfile group 5 '/ora/app/oracle/oradata/inforln/redo05.log' size 6G;

Database altered.

SQL> alter database add logfile group 6 '/ora/app/oracle/oradata/inforln/redo06.log' size 6G;

Database altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2   14 52428800 YES INACTIVE
3   15 52428800 YES INACTIVE
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.



SQL> alter database drop logfile group 2;




Database altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
3   15 52428800 YES INACTIVE
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

SQL> alter database add logfile group 2 '/ora/app/oracle/oradata/inforln/redo02.log' size 6G;
alter database add logfile group 2 '/ora/app/oracle/oradata/inforln/redo02.log' size 6G
*
ERROR at line 1:
ORA-00301: error in adding log file
'/ora/app/oracle/oradata/inforln/redo02.log' - file cannot be created
ORA-27038: created file already exists
Additional information: 1


$ rename the redo file

$ mv redo02.log redo02.old.log


SQL> alter database add logfile group 2 '/ora/app/oracle/oradata/inforln/redo02.log' size 6G;

Database altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2    0 6442450944 YES UNUSED
3   15 52428800 YES INACTIVE
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.



SQL> alter database drop logfile group 3;

Database altered.


$ rename the redo file

$ mv redo03.log redo03.old.log

SQL> alter database add logfile group 3 '/ora/app/oracle/oradata/inforln/redo03.log' size 6G;

Database altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 NO  CURRENT
2    0 6442450944 YES UNUSED
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.



SQL> alter system switch logfile;

System altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 YES ACTIVE
2   17 6442450944 NO  CURRENT
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.




SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance inforln (thread 1)
ORA-00312: online log 1 thread 1: '/ora/app/oracle/oradata/inforln/redo01.log'


SQL> alter system checkpoint global;

System altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1   16 52428800 YES INACTIVE
2   17 6442450944 NO  CURRENT
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

$ rename the redo file

$ mv redo01.log redo01.old.log

SQL> alter database add logfile group 1 '/ora/app/oracle/oradata/inforln/redo01.log' size 6G;

Database altered.

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

    GROUP#  SEQUENCE#   BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1    0 6442450944 YES UNUSED
2   17 6442450944 NO  CURRENT
3    0 6442450944 YES UNUSED
4    0 6442450944 YES UNUSED
5    0 6442450944 YES UNUSED
6    0 6442450944 YES UNUSED

6 rows selected.

SQL>