Relocating or Increase Size of Oracle Online Redo Logs on a Live Database

1. Get a list of the existing Online Redo Log Members

SQL> select group#, member from v$logfile ;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/ora_ENV/app/oracle/product/DB/10g_ocs/oradata/CSP2/onlinelog/o1_mf_3_3j3cth7n_.
log

3
/ora_ENV/app/oracle/product/DB/10g_ocs/flash_recovery_area/CSP2/onlinelog/o1_mf_
3_3j3cthgv_.log

2
/ora_ENV/app/oracle/product/DB/10g_ocs/oradata/CSP2/onlinelog/o1_mf_2_3j3ctgrc_.
log

2
/ora_ENV/app/oracle/product/DB/10g_ocs/flash_recovery_area/CSP2/onlinelog/o1_mf_
2_3j3ctgz6_.log

1
/ora_ENV/app/oracle/product/DB/10g_ocs/oradata/CSP2/onlinelog/o1_mf_1_3j3ctg9k_.
log

1
/ora_ENV/app/oracle/product/DB/10g_ocs/flash_recovery_area/CSP2/onlinelog/o1_mf_
1_3j3ctgjp_.log


6 rows selected.

SQL>


2. Find groups and the size of the Online Redo Logs so we know the number of groups and the current size.

SQL> select group#, bytes/1024 from v$log ;

GROUP# BYTES/1024
---------- ----------
1 10240
2 10240
3 10240

SQL>


3. Create New Redo Log Groups and Members for database, we will increase size here in our example.

$sqlplus "/ AS SYSDBA"

SQL*Plus: Release 10.1.0.4.2 - Production on Wed Dec 12 19:05:24 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.2 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('/ora_DB/REDO1/log/CSP2/redo4a.log', '/ora_DB/REDO2/log/CSP2/redo4b.log')
SIZE 30M ;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('/ora_DB/REDO1/log/CSP2/redo5a.log', '/ora_DB/REDO2/log/CSP2/redo5b.log')
SIZE 30M ;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('/ora_DB/REDO1/log/CSP2/redo6a.log', '/ora_DB/REDO2/log/CSP2/redo6b.log')
SIZE 30M ;

Database altered.

SQL>
4. Now that we have created our new log files we need to determine the current log and get past our original log files so that they can now be removed.

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

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SQL>

5. Switch the logfile until the new files are current and the old redo logs are no longer current. In this example we will switch 2 times as that should get us into our new logs.

SQL> alter system switch logfile ;

System altered.

SQL> alter system switch logfile ;

System altered.

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

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 INACTIVE
5 CURRENT
6 UNUSED

6 rows selected.

SQL>



5. Now Remove the old Online Redo Logs, we will remove by groups

SQL> ALTER DATABASE DROP LOGFILE GROUP 1 ;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 2 ;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 3 ;

Database altered.


6. Check that our Online Redo Logs all show correctly as we expect.

SQL> select group#, member from v$logfile ;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
4
/ora_DB/REDO1/log/CSP2/redo4a.log

4
/ora_DB/REDO2/log/CSP2/redo4b.log

5
/ora_DB/REDO1/log/CSP2/redo5a.log

5
/ora_DB/REDO2/log/CSP2/redo5b.log

6
/ora_DB/REDO1/log/CSP2/redo6a.log

6
/ora_DB/REDO2/log/CSP2/redo6b.log


6 rows selected.

SQL>