Redo Log Files are crucial for recovery in case of instance failure. Today I will show a bunch of commands how to manage them in Oracle Database. You can find more details on architecture of Redo Log Files in previous post How to multiplex Redo Logs in Oracle

Adding Redo Log Groups

Each Redo Log Group must contain at least one member (Redo Log File) in it. Oracle database requires at least two Redo Log Groups to operate. Without analyzing the load on a database is almost impossible to determine the right number of Redo Log Groups in a database. Database administrators usually start setting up database with three Redo Log Groups, and then more if necessary. To add a Redo Log Group to database we use sql statement ALTER DATABASE ADD LOGFILE GROUP.

SQL> alter database add logfile group 4 (

  2  '/u01/app/oracle/oradata/orcl/redo04.log',

  3  '/u02/app/oracle/oradata/orcl/redo04.log')

  4  size 50M

  5  /



Database altered.



SQL> select group#, sequence#, bytes / 1024 / 1024 "Size in MB",
    
  2  members, status

  3  from v$log

  4  /






GROUP#


SEQUENCE#


Size in MB


MEMBERS


STATUS




         1


        79


        50


         3


INACTIVE




         2


        80


        50


         3


INACTIVE




         3


        81


        50


         3


CURRENT




         4


         0


        50


         2


UNUSED

Adding Redo Log File to Redo Log Group

To add Redo Log File To Redo Log Group we use ALTER DATABASE ADD LOGFILE MEMBER sql statement.

SQL> alter database add logfile member


  2  '/u03/app/oracle/oradata/orcl/redo04.log'


  3  to group 4


  4  /



Database altered.





SQL> select group#, status, type, member 


  2  from v$logfile


  3  where group# = 4


  4  /






GROUP#


STATUS


TYPE


MEMBER




         4


 


ONLINE


/u01/app/oracle/oradata/orcl/redo04.log




         4


 


ONLINE


/u02/app/oracle/oradata/orcl/redo04.log




         4


INVALID


ONLINE


/u03/app/oracle/oradata/orcl/redo04.log

Dropping Redo Log Files

To drop Redo Log you can use ALTER DATABASE DROP LOGFILE MEMBER command. This command also removes the file from disk. You should remember that Oracle will not let you to drop the Redo Log File, which is a part of CURRENT Redo Log File Group.

SQL> alter system switch logfile
  2  /

System altered.

SQL> select group#, status


  2  from v$log


  3  /






GROUP#


STATUS




         1


INACTIVE




         2


ACTIVE




         3


ACTIVE




         4


CURRENT




SQL> alter database drop logfile member


  2  '/u03/app/oracle/oradata/orcl/redo04.log'


  3  /


alter database drop logfile member
*
ERROR at line 1:




ORA-01609: log 4 is the current log for thread 1 - cannot drop members




ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/redo04.log'




ORA-00312: online log 4 thread 1: '/u02/app/oracle/oradata/orcl/redo04.log'




ORA-00312: online log 4 thread 1: '/u03/app/oracle/oradata/orcl/redo04.log'

To resolve above error we just need to perform ALTER SYSTEM SWITCH LOGFILE, to move to the next Redo Log Group

SQL> alter system switch logfile


  2  /



System altered.





SQL> alter database drop logfile member


  2  '/u03/app/oracle/oradata/orcl/redo04.log'


  3  /



Database altered.





SQL> select group#, member


  2  from v$logfile


  3  /






GROUP#


MEMBER




         1


/u01/app/oracle/oradata/orcl/redo01.log




         2


/u01/app/oracle/oradata/orcl/redo02.log




         3


/u01/app/oracle/oradata/orcl/redo03.log




         1


/u02/app/oracle/oradata/orcl/redo01.log




         1


/u03/app/oracle/oradata/orcl/redo01.log




         2


/u02/app/oracle/oradata/orcl/redo02.log




         2


/u03/app/oracle/oradata/orcl/redo02.log




         3


/u02/app/oracle/oradata/orcl/redo03.log




         3


/u03/app/oracle/oradata/orcl/redo03.log




         4


/u01/app/oracle/oradata/orcl/redo04.log




         4


/u02/app/oracle/oradata/orcl/redo04.log





11 rows selected.

Dropping Redo Log Group

To drop Redo Log we can use ALTER DATABASE DROP LOGFILE GROUP statement. This command also removes all files from the disk. You should remember that Oracle will not let you to drop the CURRENT Redo Log File Group
and Redo Log Group with status ACTIVE, because Redo Log Groups with status ACTIVE are required for instance recovery.

SQL> alter database drop logfile group 4


  2  /


alter database drop logfile group 4
*
ERROR at line 1:




ORA-01624: log 4 needed for crash recovery of instance orcl (thread 1)




ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/orcl/redo04.log'




ORA-00312: online log 4 thread 1: '/u02/app/oracle/oradata/orcl/redo04.log'




SQL> select group#, status




  2  from v$log




  3  /






GROUP#


STATUS




         1


CURRENT




         2


INACTIVE




         3


INACTIVE




         4


ACTIVE

The easiest way to change status from ACTIVE to INACTIVE is to perform several log switches.

SQL> alter system switch logfile


  2  /



System altered.





SQL> alter database drop logfile group 4


  2  /



Database altered.




SQL> select group#, members, status


  2  from v$log


  3  /






GROUP#


MEMBERS


STATUS




         1


         3


ACTIVE




         2


         3


CURRENT




         3


         3


INACTIVE

Renaming Redo Logs

Sometimes we need to move the Redo Log File to new place. Below I presented the procedure, how to perform such operation.
I assume that I want to move /u01/app/oracle/oradata/orcl/redo01.log to /u04/app/oracle/oradata/orcl/ directory.
To rename Redo Log File we first need to shutdown the database and move desired Redo Log File to new location.

SQL> shutdown immediate


Database closed.


Database dismounted.


ORACLE instance shut down.


SQL> host mv /u01/app/oracle/oradata/orcl/redo01.log /u04/app/oracle/oradata/orcl/



SQL> host ls -l /u04/app/oracle/oradata/orcl/


total 51260
-rw-r----- 1 oracle oinstall 52429312 Jul 19 14:26 redo01.log

Then startup it in mount state

SQL> startup mount


ORACLE instance started.






Total System Global Area


 477073408


bytes




Fixed Size


   1300716


bytes




Variable Size


 201328404


bytes




Database Buffers


 268435456


bytes




Redo Buffers


   6008832


bytes




Database mounted.

Then we need to rename Redo Log File using ALTER DATABASE RENAME FILE statement.

SQL>  alter database rename file

  2  '/u01/app/oracle/oradata/orcl/redo01.log' to '/u04/app/oracle/oradata/orcl/redo01.log'

  3  /



Database altered.

The last thing to do is to open a database and verify if the Redo Log File move was successful

SQL> select group#, member 


  2  from v$logfile


  3  /






GROUP#


MEMBER




         1


/u04/app/oracle/oradata/orcl/redo01.log




         2


/u01/app/oracle/oradata/orcl/redo02.log




         3


/u01/app/oracle/oradata/orcl/redo03.log




         1


/u02/app/oracle/oradata/orcl/redo01.log




         1


/u03/app/oracle/oradata/orcl/redo01.log




         2


/u02/app/oracle/oradata/orcl/redo02.log




         2


/u03/app/oracle/oradata/orcl/redo02.log




         3


/u02/app/oracle/oradata/orcl/redo03.log




         3


/u03/app/oracle/oradata/orcl/redo03.log





9 rows selected.