How To Resize and/or Add Redo Logs

Applies to:
 

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4 - Release: 9.2 to 10.2
Information in this document applies to any platform.
 

Goal:
The purpose of this document is to demonstrate:

A. How to resize and/or add redo logs.

B. How to determine the optimal size for redo logs


Solution:

 A.  How to resize and/or add redo logs.

1. Review information on existing redo logs.
sql> SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#

2. Add new groups
sql> ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M;
sql> ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;
sql> ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;


3. Check the status on all redo logs again.
sql> SELECT a.group#, b.member, a.status, a.bytes
FROM v$log a, v$logfile b
WHERE a.group#=b.group#

4.  Drop the online redo log groups that are not needed.  You must have the ALTER DATABASE system privilege.
Note: Before dropping an online redo log group, consider the following restrictions and precautions:

a.  An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)

b. You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur.

By using this command :

SQL> ALTER SYSTEM SWITCH LOGFILE;

c. Make sure an online redo log group is archived (if archiving is enabled) before dropping it.  This can be determined by:

GROUP# ARC STATUS
---------   ---     ----------------
1             YES   ACTIVE
2             NO    CURRENT
3             YES   INACTIVE
4             YES   UNUSED
5             YES   UNUSED
6             YES   UNUSED

d.  Check that the group is inactive and archived before dropping it .
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;


SQL> ALTER DATABASE DROP LOGFILE GROUP 3;


e.  After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files


1 comment:

  1. Hello
    Assume,
    db_create_file_dest=+DATA
    db_create_online_log_dest_1=+LOGS
    db_create_online_log_dest_2=+FRA

    If I issue, alter database add logfile clause,

    Does it create a new logfile in the +DATA diskgroup, logfile in the +LOGS disk group, and a logfile in the +FRA disk group.
    or
    a new logfile in the +LOGS disk group, and a logfile in the +FRA disk group
    ?

    ReplyDelete