How To Add/Increase The Size Of Redo Log Files In Rac Environment? (文档 ID 779306.1)

In this Document

Goal

Solution

Community Discussions

References

---

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 04-Aug-2010***

Oracle Server Enterprise Edition - Version: 9.2 to 11.2

Goal

Problem: Need to increase the amount/size of redo log files in the RAC environment.

The following Metalink note explains this process for single-instance databases :
Note 1035935.6 - Example of How To Resize the Online Redo Logfiles

Here we will address the process for RAC databases.

Note: For 11gR2, this note applies only to administrator-managed databases.
For policy-managed databases using OMF and ASM, RAC automatically allocates redo threads and undo ts when the instance is started.

Solution

In RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.

First, determine what log members each thread has.
Here is a sample of a script to show what log members you currently have and their sizes:

-- Script begins here --
--
-- Please note, this is a sample script
-- provided for educational purposes only
-- and is not certified by Oracle Support for any purpose.

spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"

select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/

spool off

-- End of script --

Sample output:

GROUP# THREAD# MEMBER                            ARCHIVED STATUS    MB
------ ------- --------------------------------- -------- --------- ---
     3       2 /u04/oradata/redologs/redo03.log  NO       INACTIVE   10
     4       2 /u04/oradata/redologs/redo04.log  NO       CURRENT    10
     5       1 /u04/oradata/redologs/redo05.log  NO       CURRENT   100
     6       1 /u04/oradata/redologs/redo06.log  NO       INACTIVE  100
     7       1 /u04/oradata/redologs/redo07.log  NO       INACTIVE  100
     8       1 /u04/oradata/redologs/redo08.log  NO       INACTIVE  100

EXAMPLE:
Consider the above sample output.  For Thread 2, you have 2 redo log groups with 1 10MB member each. You want to create 4 100MB logs for thread 2.

1. Add 4 new redo log groups to Thread 2, with one member each, the member 100MB in size :

alter database add logfile
thread 2 group 9
('/u06/oradata/redologs/redo09.log') size 100M;

alter database add logfile
thread 2 group 10
('/u06/oradata/redologs/redo10.log') size 100M;

alter database add logfile
thread 2 group 11
('/u06/oradata/redologs/redo11.log') size 100M;

alter database add logfile
thread 2 group 12
('/u06/oradata/redologs/redo12.log') size 100M;

2. Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that
the active log is 100MB and both of the 10MB logs are inactive.

Rotate the logs:

alter system switch logfile;

select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize
from v$log l, v$logfile f where f.group# = l.group#
and l.thread#=2 ;

repeat until the active log is 100MB and both of the 10MB logs are inactive.

3. Once both of the 10MB logs are inactive, you can drop the redo log groups with the 10mb members.

alter database drop logfile group 3;
alter database drop logfile group 4;

Result:

GROUP# THREAD# MEMBER                            ARCHIVED STATUS     MB
------ ------- --------------------------------- -------- ---------- ---
     5       1 /u04/oradata/redologs/redo05.log  NO       CURRENT    100 
     6       1 /u04/oradata/redologs/redo06.log  NO       INACTIVE   100 
     7       1 /u04/oradata/redologs/redo07.log  NO       INACTIVE   100 
     8       1 /u04/oradata/redologs/redo08.log  NO       INACTIVE   100 
     9       2 /u06/oradata/redologs/redo09.log  NO       CURRENT    100 
    10       2 /u06/oradata/redologs/redo10.log  NO       INACTIVE   100 
    11       2 /u06/oradata/redologs/redo11.log  NO       INACTIVE   100 
    12       2 /u06/oradata/redologs/redo12.log  NO       INACTIVE   100 

Documentation References:
===================
For further information on redo log maintenance:

Chapter 7 of the Oracle 9i Database Administrator's guide
Part No. A96521-01

Chapter 6 of the Oracle 10g Database Administrator's guide
Part No. B10739-01

Chapter 10 of the Oracle 11g Database Administrator's guide
Part No. B28310-04

Chapter 12 of the Oracle 11gR2 Database Administrator's Guide
Part Number E25494-01