increse of redo log size

[Former Member]

Hi,

Please help me to increase the redo log size.

As i am in DB - oracle 10G and OS - Suse linux 10SP2

SQL> SELECT * FROM v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

-

---

-

---

-

---

-

---

-

---

--- -

---

FIRST_CHANGE# FIRST_TIME

-

---

-

---

1 1 358 157286400 2 YES INACTIVE

2972903289 28-NOV-11

2 1 359 157286400 2 YES INACTIVE

2972957401 28-NOV-11

3 1 357 157286400 2 YES INACTIVE

2972839164 27-NOV-11

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

-

---

-

---

-

---

-

---

-

---

--- -

---

FIRST_CHANGE# FIRST_TIME

-

---

-

---

4 1 360 157286400 2 NO CURRENT

2973005629 29-NOV-11

SQL> SELECT * FROM v$logfile;

GROUP# STATUS TYPE

-

---

-

---

-

---

MEMBER

-

---

IS_

---

4 ONLINE

/oracle/JID/origlogB/log_g14m1.dbf

NO

4 ONLINE

/oracle/JID/mirrlogB/log_g14m2.dbf

NO

GROUP# STATUS TYPE

-

---

-

---

-

---

MEMBER

-

---

IS_

---

3 ONLINE

/oracle/JID/origlogA/log_g13m1.dbf

NO

3 ONLINE

/oracle/JID/mirrlogA/log_g13m2.dbf

GROUP# STATUS TYPE

-

---

-

---

-

---

MEMBER

-

---

IS_

---

NO

2 ONLINE

/oracle/JID/origlogB/log_g12m1.dbf

NO

2 ONLINE

GROUP# STATUS TYPE

-

---

-

---

-

---

MEMBER

-

---

IS_

---

/oracle/JID/mirrlogB/log_g12m2.dbf

NO

1 ONLINE

/oracle/JID/origlogA/log_g11m1.dbf

NO

GROUP# STATUS TYPE

-

---

-

---

-

---

MEMBER

-

---

IS_

---

1 ONLINE

/oracle/JID/mirrlogA/log_g11m2.dbf

NO

8 rows selected.

Please help me how to execute for the above query.

Thanks,

Hariharan

[Former Member]

Hello

Complete step:

Step 1 SQL> select a.group#, a.member, b.bytes/1024/1024 mb from v$logfile a, v$log b where a.group# = b.group#;

This query will show current group with redo log members and their size.

Step 2 Make the last redo log CURRENT one

To find which group is current at this moment use following query

SQL> select group#, status from v$log;

GROUP# STATUS

-

---

-

---

1 CURRENT

2 INACTIVE

3 INACTIVE

4 INACTIVE

Now as you can see that the first group is marked as current but we need to make group 4 as current. So force group 4 to become current one by switching log file. To switch log file use following query.

SQL> alter system switch logfile;

GROUP# STATUS

-

---

-

---

1 INACTIVE

2 CURRENT

3 INACTIVE

4 INACTIVE

SQL> alter system switch logfile;

GROUP# STATUS

-

---

-

---

1 INACTIVE

2 INACTIVE

3 INACTIVE

4 CURRENT

Step 3 Drop the first online redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:

SQL> alter database drop logfile group 1;

Database altered.

Note:

Be aware that if you are going to drop a logfile group, it cannot be the current logfile group. However, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 1 thread 1: ''

Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Step 4 You need to re-create dropped online redo log group with different size. Use the following query to achieve this.

SQL> alter database add logfile group 1 ('<path>/origlogA/log_g11m1.dbf','<path>/mirrlogA/log_g11m2.dbf') size 200M reuse;

Database altered.

Step 5 Force another log switch

After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" group:

SQL> select group#, status from v$log;

GROUP# STATUS

-

---

-

---

1 UNUSED

2 INACTIVE

3 INACTIVE

4 CURRENT

SQL> alter system switch logfile;

SQL> select group#, status from v$log;

GROUP# STATUS

-

---

-

---

1 CURRENT

2 INACTIVE

3 ACTIVE

Step 6 # Loop back to Step 3 until all logs are rebuilt

After re-creating an online redo log group, continue to re-create (or resize) all online redo log groups until all of them are rebuilt.

Regards,

Rajan

[sunny_pahuja2]

Hi,

You can follow procedure describe in SAP note 3809 to resize redo logs.

Thanks

Sunny

[Former Member]

I recommend the brspace moredo option: [1259767 - Management of online redo log files using BRSPACE|https://service.sap.com/sap/support/notes/1259767]

brspace -f moredo -a resize -f all_rf -s 400

Sizes all redo logs to 400mb...

Cheers Michael

[former_member188883]

Hi,

You need to do the following:

1) Create new groups with new(extended) size

2) Switch redo log file and drop inactive, until old size groups are gone.

As a exmaple I demonstrate below

The reso logfile size can't be increased.U have to make a new redo logfile group(of the size u want),and then drop the redolog u wanted to resize.U can drop the redo only when its status is INACTIVE. The newly created logfile will show status UNUSED.

DEMO:--

23:52:17 sys@STRTEST> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

---

-

---

-

---

-

---

-

---

---

-

---

-

---

-

---

1 1 5 104857600 1 NO CURRENT 6131352 01-SEP-08

2 1 3 104857600 1 NO INACTIVE 6131347 01-SEP-08

3 1 4 104857600 1 NO INACTIVE 6131350 01-SEP-08

23:52:45 sys@STRTEST> alter database add logfile group 4 ('D:\ORACLE\ORADATA\CLOVER\REDO04A.LOG','D:\ORACLE\ORADATA\CLOVER\REDO04B.LOG') size 30M;

Database altered.

23:54:00 sys@STRTEST> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

---

-

---

-

---

-

---

-

---

---

-

---

-

---

-

---

1 1 5 104857600 1 NO CURRENT 6131352 01-SEP-08

2 1 3 104857600 1 NO INACTIVE 6131347 01-SEP-08

3 1 4 104857600 1 NO INACTIVE 6131350 01-SEP-08

4 1 0 31457280 2 YES UNUSED 0

23:54:14 sys@STRTEST> alter system switch logfile;

23:54:14 sys@STRTEST>/

23:54:14 sys@STRTEST>/

23:55:01 sys@STRTEST> alter database drop logfile group 1;

Database altered.

23:55:09 sys@STRTEST> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

-

---

-

---

-

---

-

---

-

---

---

-

---

-

---

-

---

2 1 11 104857600 1 NO CURRENT 6131479 01-SEP-08

3 1 8 104857600 1 NO INACTIVE 6131449 01-SEP-08

4 1 10 31457280 2 NO INACTIVE 6131474 01-SEP-08

Hope this helps.

Regards,

Deepak Kori