Redo File Management

1. view the database version

SQL> select * from v$version; 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE11.2.0.1.0Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

2. view the redo log file

SQL> select * from v $ logfile;

GROUP # status type member is _

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

3 ONLINE/home/oracle/app/oracle/oradata/orcl/redo03.log NO

2 ONLINE/home/oracle/app/oracle/oradata/orcl/redo02.log NO

1 ONLINE/home/oracle/app/oracle/oradata/orcl/redo01.log NO

3. View redo log group information

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

    GROUP#BYTES MEMBERS

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

 1   52428800       1

 2   52428800       1

 3   52428800       1

4. view the redo log and redo log group status

SQL> select a. group #, a. member, B. status, B. bytes

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER STATUS BYTES

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log CURRENT 52428800

2/home/oracle/app/oracle/oradata/orcl/redo02.log INACTIVE 52428800

1/home/oracle/app/oracle/oradata/orcl/redo01.log INACTIVE 52428800

4. Add a redo log Group

You can add one or more redo log files to each redo log group.

Syntax:

Alter database add logfile groupGroup sequence number ('file name 1', 'file name 2',... 'file n ')SizeFile size;

SQL> alter database add logfile group 4 ('/home/oracle/app/oracle/oradata/orcl/redo04_01.log ',

'/Home/oracle/app/oracle/oradata/orcl/redo04_02.log') size 100 M;

SQL> select a. group #, a. member, B. bytes/1024/1024 "M", B. status

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER M STATUS

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 50 CURRENT

2/home/oracle/app/oracle/oradata/orcl/redo02.log 50 INACTIVE

1/home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE

4/home/oracle/app/oracle/oradata/orcl/redo04_01.log 100 UNUSED

4/home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 UNUSED

Green Mark: name, size, and status of the newly created redo log file. The file names are redo04_01.log and red04_02.log. The file size is 100 MB,

Status unused (because the log file has just been created, the redo log file has never been used)

Knowledge expansion: redo status

Current: the current active redo log is required for instance recovery!

Active: the active non-current redo log is used when the instance is restored. This status indicates that the checkpoint has not been completed.

Inactive: non-active logs are not required for instance recovery, but are required for media recovery.

Unused: the log has never been written. It may have been reset after being added or resetlogs.

5. Add the redo log file

Add and reset log files. You do not need to specify the file size. The size of the newly created redo log file is the same as that of the existing redo log file.

Syntax:

Alter database add logfile member 'file name' to group No.

SQL> alter database add logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_04.log' to group 4;

SQL> select a.group#,a.member,b.bytes/1024/1024,b.status

    from v$logfile a,v$log b

       where a.group#=b.group#;

    GROUP# MEMBER      B.BYTES/1024/1024 STATUS

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

 3 /home/oracle/app/oracle/oradata/orcl/redo03.log     50 CURRENT

 2 /home/oracle/app/oracle/oradata/orcl/redo02.log     50 INACTIVE

 1 /home/oracle/app/oracle/oradata/orcl/redo01.log     50 INACTIVE

 4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log    100 UNUSED

 4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log    100 UNUSED

 4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log    100 UNUSED

5. Delete the redo log file

The redo log file status is current and active, and cannot be deleted.
The unique member in the group cannot be deleted.
The redo log file is deleted, but the physical file is not deleted. You need to manually delete the physical file.
If you want to delete the current or active redo log file, you must switch the current redo log file to the inactive state. If you want to delete the redo log file, try to archive the redo log file. (Check whether the archived field is in the archive status v $ log view)

Syntax:

Alter database drop logfile member 'file name'

The unique member in the group cannot be deleted.

SQL> select a. group #, a. member, B. bytes, B. status, B. archived

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER BYTES STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 52428800 INACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 52428800 INACTIVE YES

1/home/oracle/app/oracle/oradata/orcl/redo01.log 52428800 CURRENT NO

4/home/oracle/app/oracle/oradata/orcl/redo04_01.log 104857600 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_02.log 104857600 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_04.log 104857600 INACTIVE YES

If the status of the redo log is current, an error message is displayed.

SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo03.log ';

Alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo03.log'

*

ERROR at line 1:

ORA-00361: cannot remove last log member/home/oracle/app/oracle/oradata/orcl/redo03.log for group 3

To delete a redo log file in the current or active state, you must switch the current redo log file to the inactive state.

SQL> alter system switch logfile;

System altered.

SQL> select a. group #, a. member, B. bytes/1024/1024, B. status, B. archived

2 from v $ logfile a, v $ log B

3 where a. group # = B. group #;

GROUP # MEMBER BYTES STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 52428800 ACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 52428800 INACTIVE YES

1/home/oracle/app/oracle/oradata/orcl/redo01.log 52428800 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_01.log 104857600 CURRENT NO

4/home/oracle/app/oracle/oradata/orcl/redo04_02.log 104857600 CURRENT NO

4/home/oracle/app/oracle/oradata/orcl/redo04_04.log 104857600 CURRENT NO

If the deleted redo log is in the current or active status, the current log for thread 1-cannot drop members is reported.

SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_01.log ';

Alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_01.log'

*

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: '/home/oracle/app/oracle/oradata/orcl/redo04_01.log'

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

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

The redo log file is deleted, but the physical file is not deleted. You need to manually delete the physical file.

SQL> alter system switch logfile;

System altered.

SQL> select a. group #, a. member, B. bytes/1024/1024 "M", B. status, B. archived

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER M STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO

1/home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_01.log 100 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 INACTIVE YES

SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_01.log ';

Database altered.

[Oracle @ localhost orcl] $ ls-ls | grep redo

51260-rw-r ----- 1 oracle oinstall 52429312 12-10 redo01.log

51260-rw-r ----- 1 oracle oinstall 52429312 12-10 redo02.log

51260-rw-r ----- 1 oracle oinstall 52429312 12-10 redo03.log

102508-rw-r ----- 1 oracle oinstall 104858112 12-10 redo04_01.log

102508-rw-r ----- 1 oracle oinstall 104858112 12-10 redo04_02.log

102508-rw-r ----- 1 oracle oinstall 104858112 12-10 redo04_03.log

102508-rw-r ----- 1 oracle oinstall 104858112 12-10 redo04_04.log

You need to manually delete physical files!

Rm-rf/Home/oracle/app/oracle/oradata/orcl/redo04_01.log

6. Delete the redo log Group

An instance must have two groups of redo logs. If there are only two groups of redo logs, they cannot be deleted.

To delete a redo log group, you must manually delete the physical files (not OMF)

The redo log file in the redo log group is in the current or active status. You cannot delete the redo log group.

If the redo log files in the redo log group coexist in the null and inavtive statuses, you can only delete the redo log files whose status is inactive.

Syntax:

Alter database drop logfile group no.

The redo log file in the redo log group is in the current or active status. You cannot delete the redo log group.

SQL> select a. group #, a. member, B. bytes/1024/1024 "M", B. status, B. archived

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER M STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO

1/home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 INACTIVE YES

SQL> alter database drop logfile group 2;

Alter database drop logfile group 2

*

ERROR at line 1:

The ORA-01623: log 2 is current log for instance orcl (thread 1)-cannot drop

ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log'

To delete a redo log group, you must manually delete the physical files (not OMF)

SQL> select a. group #, a. member, B. bytes/1024/1024 "M", B. status, B. archived

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER M STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO

1/home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 INACTIVE YES

4/home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 INACTIVE YES

SQL> alter database drop logfile group 4;

Database altered.

You need to manually delete physical files.

[Oracle @ localhost orcl] $ ls-ls | grep redo

51260-rw-r ----- 1 oracle oinstall 52429312 12-10 redo01.log

51260-rw-r ----- 1 oracle oinstall 52429312 12-10 redo02.log

51260-rw-r ----- 1 oracle oinstall 52429312 12-10 redo03.log

102508-rw-r ----- 1 oracle oinstall 104858112 12-10 redo04_02.log

102508-rw-r ----- 1 oracle oinstall 104858112 12-10 redo04_04.log

Two groups of redo logs are required for an instance.

SQL> select a. group #, a. member, B. bytes/1024/1024 "M", B. status, B. archived

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # MEMBER M STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO

An instance must have two groups of redo day groups. If a group is deleted, an error is returned.Wocould leave less than 2 log files for instance orcl (thread 1)

SQL> alter database drop logfile group 3;

Alter database drop logfile group 3

*

ERROR at line 1:

ORA-01567: dropping log 3 wocould leave less than 2 log files for instance orcl (thread 1)

ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log'

6. Clear the redo log Group

Clear the redo log: Initialize all the redo log files, which is equivalent to deleting the redo log file and re-creating the redo log file. An instance must have two groups of redo logs, if there are only two redo log groups, they cannot be cleared.

The redo log file in the redo log group is in the current or active state, and the redo log group cannot be cleared.

SQL> select a. group #, a. member, B. bytes/1024/1024, B. status, B. archived

From v $ logfile a, v $ log B

Where a. group # = B. group #;

GROUP # member B. BYTES/1024/1024 STATUS ARC

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

3/home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES

2/home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO

5/home/oracle/app/oracle/oradata/orcl/redo05_01.log 100 UNUSED YES

6./home/oracle/app/oracle/oradata/orcl/redo06_01.log 100 INACTIVE NO

Clear the redo log group:

SQL> alter database clear logfile group 3;

Clear redo log files

SQL> alter database clear logfile '/home/oracle/app/oracle/oradata/orcl/redo05_01.log ';

If the redo log is not archived, you must use unarchived to clear it.

SQL> alter database clear unarchived logfile group 6;

Database altered.

6. Dynamic View related to redo logs

V $ log

V $ logfile

V $ log_history

Important Fields in the v $ log view

Status of the log member:

UNUSED-Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

CURRENT-Current redo log. This implies that the redo log is active. The redo log cocould be open or closed.

ACTIVE-Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

CLEARING-Log is being re-created as an empty log after an alter database clear logfile statement. After the log is cleared, the status changes to UNUSED.

CLEARING_CURRENT-Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

INACTIVE-Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.

Important field in the v $ logfile view: Status of the log member:

• INVALID-File is inaccessible

  STALE-File's contents are incomplete

  DELETED-File is no longer used

  Null-File is in use