How To Add/Increase The Size Of Redo Log Files In Rac Env

How To Add/Increase The Size Of Redo Log Files In Rac Env

n 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 --

GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)

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

1 1 /acme/oraredo/acme/redo01.log NO CURRENT 50

1 1 /acme/oraredo/acme/redo01a.log NO CURRENT 50

2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50

2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50

3 2 /acme/oraredo/acme/redo03a.log YES ACTIVE 50

3 2 /acme/oraredo/acme/redo03.log YES ACTIVE 50

4 2 /acme/oraredo/acme/redo04a.log NO CURRENT 50

4 2 /acme/oraredo/acme/redo04.log NO CURRENT 50

Importent Points

1) Thread is same as Instance number in RAC env .

2) Our Goal is here to Add 6 group , 3 for each Thread(instance) size of 200MB .

3) At the end drop 50 MB group

ACTION PLAN

##############

1) Add 6 new redo log groups to Thread 1 AND 2, with 2 member each, the member 200 mb in size.

2) Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that

the active log is 200mb and both of the 50mb logs are inactive

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

Thread # 1

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '/acme/oraredo/acme/redo5a.log','/acme/oraredo/acme/redo5b.log') SIZE 200M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '/acme/oraredo/acme/redo6a.log','/acme/oraredo/acme/redo6b.log') SIZE 200M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 ( '/acme/oraredo/acme/redo7a.log','/acme/oraredo/acme/redo7b.log') SIZE 200M;

Thread#2

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '/acme/oraredo/acme/redo8a.log','/acme/oraredo/acme/redo8b.log') SIZE 200M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ( '/acme/oraredo/acme/redo9a.log','/acme/oraredo/acme/redo9b.log') SIZE 200M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ( '/acme/oraredo/acme/redo10a.log','/acme/oraredo/acme/redo10b.log') SIZE 200M;

-> Result of Above query

GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)

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

1 1 /acme/oraredo/acme/redo01a.log YES ACTIVE 50

1 1 /acme/oraredo/acme/redo01.log YES ACTIVE 50

2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50

2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50

3 2 /acme/oraredo/acme/redo03.log NO CURRENT 50

3 2 /acme/oraredo/acme/redo03a.log NO CURRENT 50

4 2 /acme/oraredo/acme/redo04a.log YES ACTIVE 50

4 2 /acme/oraredo/acme/redo04.log YES ACTIVE 50

5 1 /acme/oraredo/acme/redo5a.log NO CURRENT 200

5 1 /acme/oraredo/acme/redo5b.log NO CURRENT 200

6 1 /acme/oraredo/acme/redo6a.log YES UNUSED 200

6 1 /acme/oraredo/acme/redo6b.log YES UNUSED 200

7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200

7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200

Action :

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

To move Current logs from old redo log file to newly added logfile .

4. Issue the below statement to make the Inactive status of active status and avoid the 'ORA-01624'.

SQL> alter system checkpoint global;

Check ####

GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)

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

1 1 /acme/oraredo/acme/redo01.log YES INACTIVE 50

1 1 /acme/oraredo/acme/redo01a.log YES INACTIVE 50

2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50

2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50

3 2 /acme/oraredo/acme/redo03.log YES INACTIVE 50

3 2 /acme/oraredo/acme/redo03a.log YES INACTIVE 50

4 2 /acme/oraredo/acme/redo04.log YES INACTIVE 50

4 2 /acme/oraredo/acme/redo04a.log YES INACTIVE 50

5 1 /acme/oraredo/acme/redo5a.log YES INACTIVE 200

5 1 /acme/oraredo/acme/redo5b.log YES INACTIVE 200

6 1 /acme/oraredo/acme/redo6a.log NO CURRENT 200

6 1 /acme/oraredo/acme/redo6b.log NO CURRENT 200

7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200

7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200

8 2 /acme/oraredo/acme/redo8a.log NO CURRENT 200

8 2 /acme/oraredo/acme/redo8b.log NO CURRENT 200

9 2 /acme/oraredo/acme/redo9a.log YES UNUSED 200

9 2 /acme/oraredo/acme/redo9b.log YES UNUSED 200

10 2 /acme/oraredo/acme/redo10a.log YES UNUSED 200

10 2 /acme/oraredo/acme/redo10b.log YES UNUSED 200

Action :

## Time to Drop old REDO LOG file groups ( 50 MB redo Log groups )

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

SQL> alter database drop logfile group 4;

Check ####

GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)

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

5 1 /acme/oraredo/acme/redo5a.log YES INACTIVE 200

5 1 /acme/oraredo/acme/redo5b.log YES INACTIVE 200

6 1 /acme/oraredo/acme/redo6a.log NO CURRENT 200

6 1 /acme/oraredo/acme/redo6b.log NO CURRENT 200

7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200

7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200

8 2 /acme/oraredo/acme/redo8a.log NO CURRENT 200

8 2 /acme/oraredo/acme/redo8b.log NO CURRENT 200

9 2 /acme/oraredo/acme/redo9a.log YES UNUSED 200

9 2 /acme/oraredo/acme/redo9b.log YES UNUSED 200

10 2 /acme/oraredo/acme/redo10a.log YES UNUSED 200

10 2 /acme/oraredo/acme/redo10b.log YES UNUSED 200

Action : Check your log file for some time .

Result : You are Done now .