Resize Redo Logs

  

        1. Check Current Redo Logs

SQL> column group# format 99999;
column status format a10;
column mb format 99999;
select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS MB
------ ---------- ------
 4 ACTIVE 50
 5 CURRENT50
 6 ACTIVE 50

        2.  Add new 3 Groups with New Size (1GB) 

SQL>alter database add logfile 
group 1 ('/u01/app/oracle/fast_recovery_area/ORACLE_SID/onlinelog/redo01.log') size 1g,
group 2('/u01/app/oracle/fast_recovery_area/ORACLE_SID/onlinelog/redo02.log') size 1g, 
group 3 ('/u01/app/oracle/fast_recovery_area/ORACLE_SID/onlinelog/redo03.log') size 1g;
SQL> select group#, status, bytes/1024/1024 mb from v$log; 
GROUP# STATUS   MB
---------- ---------------- ----------
 1 UNUSED 1024
 2 UNUSED 1024
 3 UNUSED 1024
 4 ACTIVE    50
 5 CURRENT50
 6 ACTIVE    50

6 rows selected.

        3. Switch Logfile to New Groups

Make some redo log switching until you see Log Writer Process (LGWR) is working on the new redo.     

 

SQL>  alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select group#, status, bytes/1024/1024 mb from v$log;
GROUP# STATUS   MB
---------- ---------------- ----------
 1 CURRENT       1024
 2 INACTIVE       1024
 3 INACTIVE       1024
 4 ACTIVE           50
 5 ACTIVE           50
 6 ACTIVE           50
6 rows selected.

4. Force a checkpoint   

 Now, we have to make all of the original groups to be INACTIVE, then drop them. An ACTIVE or CURRENT redo log group cannot be dropped.   

 

  SQL> alter system checkpoint;
 System altered.
 SQL> select group#, status, bytes/1024/1024 mb from v$log;
 GROUP# STATUS                   MB
  ---------- ---------------- ----------
1 CURRENT                1024
2 INACTIVE               1024
3 INACTIVE               1024
4 INACTIVE                 50
5 INACTIVE                 50
6 INACTIVE                 50
6 rows selected.

5. Drop Group 4,5,6   

 

  alter database drop logfile group 4, group 2, group 3;

6. Remove Redo Log Files

Be careful, don't remove online redo logs accidentally.   

 

[oracle@hostname onlinelog]$ ls -lrt
 total 3299364
 -rw-r----- 1 oracle dba 1073742336 Mar 30 18:22 redo02.log
 -rw-r----- 1 oracle dba 1073742336 Mar 30 18:22 redo03.log
 -rw-r----- 1 oracle dba   52429312 Mar 30 18:23 redo06.log
 -rw-r----- 1 oracle dba   52429312 Mar 30 18:23 redo04.log
 -rw-r----- 1 oracle dba   52429312 Mar 30 18:23 redo05.log
 -rw-r----- 1 oracle dba 1073742336 Mar 30 18:25 redo01.log
 [oracle@ hostname onlinelog]$ rm redo04.log redo05.log redo06.log
 You have new mail in /var/spool/mail/oracle
 [oracle@ hostname onlinelog]$ ls -lrt
 total 3145752
 -rw-r----- 1 oracle dba 1073742336 Mar 30 18:22 redo02.log
 -rw-r----- 1 oracle dba 1073742336 Mar 30 18:22 redo03.log
 -rw-r----- 1 oracle dba 1073742336 Mar 30 18:30 redo01.log

 

7. Add Group 4, 5, 6 with New Size (1GB)   

 

  alter database add logfile 
  group 4 ('/u01/app/oracle/fast_recovery_area/ORACLE_SID/onlinelog/redo04.log') size 1g,
  group 5('/u01/app/oracle/fast_recovery_area/ORACLE_SID/onlinelog/redo05.log') size 1g, 
  group 6 ('/u01/app/oracle/fast_recovery_area/ORACLE_SID/onlinelog/redo06.log') size 1g;

8. Switch logfiles several times 

 

 SQL> alter system switch logfile;
 System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.

         9. Check Status of All Redo Logs 

 SQL>  select group#, status, bytes/1024/1024 mb from v$log;
 GROUP# STATUS                   MB
---------- ---------------- ----------
1 ACTIVE                 1024
2 ACTIVE                 1024
3 CURRENT                1024
4 ACTIVE                 1024
5 ACTIVE                 1024
6 ACTIVE                 1024
6 rows selected.