Modifying the Oracle Redo log file size

1. Create 3 new log groups

sql> ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log ') SIZE 500M;

sql> ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log ') SIZE 500M;

sql> ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log ') SIZE 500M;

View Log Groups

Sql> select * from V$logfile;

2. Switch the current log to a new log group

sql> alter system switch logfile;

sql> alter system switch logfile;

sql> alter system switch logfile;

Cut to be deleted as incactive.

Sql> select Group#,sequence#,bytes,members,status from V$log;

group# sequence# BYTES Members STATUS
---------- ---------- ---------- ---------- ----------------
1 139 52428800 1 INACTIVE
2 137 52428800 1 INACTIVE
3 138 52428800 1 INACTIVE
4 524288000 1 ACTIVE
5 141 524288000 1 Current
6 136 524288000 1 INACTIVE

3. Delete the old log group

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;

To see if a log group has been deleted

Sql> select Group#,sequence#,bytes,members,status from V$log;

group# sequence# BYTES Members STATUS
---------- ---------- ---------- ---------- ----------------
4 524288000 1 INACTIVE
5 141 524288000 1 Current
6 136 524288000 1 INACTIVE

4. The operating system deletes files from the original log group 1, 2, 3

[Email protected] log]$ CD/U01/APP/ORACLE/ORADATA/ORCL
[Email protected] orcl]$
[Email protected] orcl]$ pwd
/u01/app/oracle/oradata/orcl
[[email protected] orcl]$ ls
Control01.ctl example01.dbf redo01.log redo02.log redo03.log redo04.log redo05.log redo06.log sysaux01.dbf System 01.DBF temp01.dbf undotbs01.dbf users01.dbf
[Email protected] orcl]$ RM-RF Redo01.log
[Email protected] orcl]$ RM-RF Redo02.log
[Email protected] orcl]$ RM-RF Redo03.log

5. Rebuilding log groups 1, 2, 3

sql> ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log ') SIZE 500M;

sql> ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log ') SIZE 500M;

sql> ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log ') SIZE 500M;

7. Delete log groups 4, 5, 6 for intermediate transitions, and query select Group#,sequence#,bytes,members,status from v$log when deleting; Delete whether the group is inactive, if not inactive to execute the ALTER system switch logfile;

Query to see

Sql> select Group#,sequence#,bytes,members,status from V$log;

group# sequence# BYTES Members STATUS
---------- ---------- ---------- ---------- ----------------
1 148 524288000 1 Current
2 143 524288000 1 INACTIVE
3 144 524288000 1 INACTIVE
4 146 524288000 1 INACTIVE
5 147 524288000 1 INACTIVE
6 145 524288000 1 INACTIVE

sql> ALTER DATABASE drop logfile Group 4;

Database altered.

sql> ALTER DATABASE drop logfile Group 5;

Database altered.

sql> ALTER DATABASE drop logfile Group 6;

Database altered.

8. Remove Groups 4, 5, 6 from the operating system

[Email protected] orcl]$ RM-RF Redo04.log
[Email protected] orcl]$ RM-RF Redo05.log
[Email protected] orcl]$ RM-RF Redo06.log

9. Back up the current control file

sql> ALTER DATABASE backup controlfile to trace resetlogs;

Modifying the Oracle Redo log file size