Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Managing Redo Logs in Oracle

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.

Categories: