resizeing the log file

http://dbataj.blogspot.com/2009/07/how-to-resize-redolog-file-size.html

How to resize Redolog File Size


Today I resized one of our production database redolog size from 10m to 25m.

I want to share with all.

Procedure (Tested on PROD)

1. Check the logfile member , group and current size

SQL> column member format a50

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

2 from v$logfile a, v$log b

3 where a.group# = b.group#;
GROUP# MEMBER size--------- -------------------------------------------------- --------- 1 /db/app/oracle/Db_1/oradata/orcl/redo01.log 10 2 /db/app/oracle/Db_1/oradata/orcl/redo02.log 10 3 /db/app/oracle/Db_1/oradata/orcl/redo03.log 10 4 /db/app/oracle/Db_1/oradata/orcl/redo04.log 10 5 /db/app/oracle/Db_1/oradata/orcl/redo05.log 10

2. Check the logfile status of the existing redolog file

SQL> select group#,status from v$log; GROUP# STATUS
--------- ----------------
1 INACTIVE

2 CURRENT

3 INACTIVE

4 INACTIVE

5 INACTIVE

3. Switch the logfile upto last group

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

Again check the logfile status

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

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

1 INACTIVE

2 ACTIVE

3 ACTIVE

4 ACTIVE

5 CURRENT

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

SQL> alter system checkpoint global;

5. Once logfile status show "INACTIVE" then issue the following statement.

SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.

6. At least minimum 2 group must be exist in the database at a time Or if we trying to drop group 4 then we receive the following error.

SQL> alter database drop logfile group 4;alter database drop logfile group 4

*

ERROR at line 1:ORA-01567: dropping log 4 would leave less than 2 log files for instance orcl (thread 1)

ORA-00312: online log 4 thread 1: '/db/app/oracle/Db_1/oradata/orcl/redo04.log'

To avoid above error please add the group first then drop group 4,5 respectivily.

SQL> alter database add logfile group 1 2 ('/db/app/oracle/Db_1/oradata/orcl/redo01.log') size 25m reuse;
Database altered.
SQL> alter database add logfile group 2 2 ('/db/app/oracle/Db_1/oradata/orcl/redo02.log') size 25m reuse;
Database altered.
SQL> alter database add logfile group 3 2 ('/db/app/oracle/Db_1/oradata/orcl/redo03.log') size 25m reuse;
Database altered.

Note drop the group 4,5 and add the group 4,5 with above statement.

Check the group status and size...

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

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

1 CURRENT

2 UNUSED

3 UNUSED

4 UNUSED

5 UNUSED

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

2 from v$logfile a, v$log b

3 where a.group# = b.group#;
GROUP# MEMBER size

--------- -------------------------------------------------- --------- 1 /db/app/oracle/Db_1/oradata/orcl/redo01.log 25 2 /db/app/oracle/Db_1/oradata/orcl/redo02.log 25 3 /db/app/oracle/Db_1/oradata/orcl/redo03.log 25 4 /db/app/oracle/Db_1/oradata/orcl/redo04.log 25 5 /db/app/oracle/Db_1/oradata/orcl/redo05.log 25

You can receive the following error message during the above procedure ...

1. ORA-01624

To avoid this error issue the "alter system checkpoint global;"

2. ORA-01567

To avoid this error we need to keep at least two redolog group in the database.

3. ORA-01623

To avoid this error issue the "alter system switch logfile;"

I use the below references:

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_34.shtml