Managing Online Redo logfiles

SQL> select group#, thread#, sequence#, bytes, members, archived,

status from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

------ ------  --------- ----- ------- --- ----------

1           1     340   104857600   1 YES INACTIVE

2           1     341   104857600   1 NO  CURRENT

3           1     339   104857600   1 YES INACTIVE

SQL> select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                  

------ ------- ------- ----------------------------------------

1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01.LOG

2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02.LOG

3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03.LOG

Renaming Redo LogFiles.
SQL> shutdown immediate;
Copy or move the files to be rename using any way
Copy D:\ORACLE\ORA92\MUJAZORC\REDO01.LOG D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG

Copy D:\ORACLE\ORA92\MUJAZORC\REDO02.LOG D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG

Copy D:\ORACLE\ORA92\MUJAZORC\REDO03.LOG D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOGSQL> Startup Mount;
SQL> alter database rename file 'D:\ORACLE\ORA92\MUJAZORC\REDO01.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO02.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO03.LOG'
to
'D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG';SQL> alter database open;
SQL> select * from v$logfile
GROUP# STATUS  TYPE    MEMBER                                 

----- ------- ------- ----------------------------------------

1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG

2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG

3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG

Adding Redo Logfile members

# redo log files added individually
SQL> alter database add logfile member 'D:\ORACLE\ORA92\MUJAZORC\REDO01b.LOG' to group 1;
SQL> alter database add logfile
member 'D:\ORACLE\ORA92\MUJAZORC\REDO01c.LOG' to group 1;
# redo log files added in all at once.SQL> alter database add logfile member
'D:\ORACLE\ORA92\MUJAZORC\REDO02b.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO02c.LOG' to group 2;
SQL> alter database add logfile member
'D:\ORACLE\ORA92\MUJAZORC\REDO03b.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO03c.LOG' to group 3;
SQL> select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                 

----- ------- ------- ----------------------------------------

1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG

2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG

3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG

1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01b.LOG

2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02b.LOG

3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03b.LOG

1           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO01c.LOG

2           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO02c.LOG

3           ONLINE      D:\ORACLE\ORA92\MUJAZORC\REDO03c.LOG

Note: If the newly created log file members are showing a status of INVALID. In order to solve this problem execute the “alter system switch logfile” 3 or 4 times.Adding a Redo Logfile Group.SQL>alter database add logfile group 4 ('D:\ORACLE\ORA92\MUJAZORC\REDO04a.LOG',
'D:\ORACLE\ORA92\MUJAZORC\REDO04b.LOG ',
'D:\ORACLE\ORA92\MUJAZORC\REDO04c.LOG’) size 50m;
SQL> select * from v$logfile order by member;

GROUP# STATUS TYPE    MEMBER                                 

----- ------- ------- ----------------------------------------

1             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO01a.LOG

1             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO01b.LOG

1             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO01c.LOG

2             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO02b.LOG

2             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO02a.LOG

2             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO02c.LOG

3             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO03a.LOG

3             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO03b.LOG

3             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO03c.LOG

4             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO04a.LOG

4             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO04b.LOG

4             ONLINE    D:\ORACLE\ORA92\MUJAZORC\REDO04c.LOG

Dropping redo log members.SQL> alter database drop logfile member 'D:\ORACLE\ORA92\MUJAZORC\REDO04c.LOG';
Then directly remove from OS level using any delete command.

D:\>ORACLE>ORA92>MUJAZORC> del REDO04c.LOG;
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- --------

         1          1          7   52428800          3 YES ACTIVE

         2          1          8   52428800          3 YES ACTIVE

         3          1          9   52428800          3 NO  CURRENT

         4          1          0   52428800          2 YES UNUSED

Notice the member’s column for group 4 is now 2. If a group contains only one logfile then you cannot drop that member.
Dropping Redo Log GroupSQL> alter database drop logfile group 4;
SQL> select group#, thread#, sequence#, bytes, members, archived,
status from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ---------

         1          1          8   52428800          3 YES INACTIVE

         2          1          7   52428800          3 YES INACTIVE

         3          1          9   52428800          3 NO  CURRENT

Note: Drop the files from the OS level also. You cannot drop a log group with ‘CURRENT’ Status and also you cannot drop the log group with ‘ACTIVE’ status.

Execute the following command to change the status of group
SQL> alter system switch logfile;
SQL> alter system checkpoint;