EXAMPLE OF HOW TO RESIZE THE ONLINE REDO LOGS:
==============================================
Often times the online redo logs are sized too small causing database
performance problems.
The following is an example of how to resize the online log groups:
1. First see the size of the current logs:
SVRMGR> connect internal (with 9i/10g use SQL> connect / as sysdba)
SVRMGR> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
———- ———- —————-
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE
Logs are 1MB from above, let’s size them to 10MB.
2. Retrieve all the log member names for the groups:
SVRMGR> select group#, member from v$logfile;
GROUP# MEMBER
————— —————————————-
1 /usr/oracle/dbs/log1PROD.dbf
2 /usr/oracle/dbs/log2PROD.dbf
3 /usr/oracle/dbs/log3PROD.dbf
3. Now shutdown the database and startup in restricted mode. We startup in
restricted mode so that only DBAs can login and because we do not want redo
generation during this time:
SVRMGR> shutdown immediate;
SVRMGR> startup open restrict;
4. Let’s create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
size:
SVRMGR> alter database add logfile group 4
‘/usr/oracle/dbs/log4PROD.dbf’ size 10M;
SVRMGR> alter database add logfile group 5
‘/usr/oracle/dbs/log5PROD.dbf’ size 10M;
SVRMGR> alter database add logfile group 6
‘/usr/oracle/dbs/log6PROD.dbf’ size 10M;
5. Now run a query to view the v$log status:
SVRMGR> select group#, status from v$log;
GROUP# STATUS
——— —————-
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED
From the above we can see log group 2 is current, and this is one of the
smaller groups we must drop. Therefore let’s switch out of this group into
one of the newly created log groups.
6. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:
SVRMGR> alter system switch logfile;
SVRMGR> alter system switch logfile;
7. Run the query again to verify the current log group is group 4:
SVRMGR> select group#, status from v$log;
GROUP# STATUS
——— —————-
1 INACTIVE
2 INACTIVE
3 INACTIVE
4 CURRENT
5 UNUSED
6 UNUSED
8. Now drop redo log groups 1, 2, and 3:
SVRMGR> alter database drop logfile group 1;
SVRMGR> alter database drop logfile group 2;
SVRMGR> alter database drop logfile group 3;
Verify the groups were dropped, and the new groups’ sizes are correct.
SVRMGR> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
——— ——— —————-
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED
9. Now shutdown and startup the database in normal mode, so all users can login:
SVRMGR> shutdown immediate;
SVRMGR> startup;
Or you can issue the following command to enable logins without having to
shutdown the database.
SVRMGR> alter system disable restricted session;
At this point, you also might want to consider taking a backup of the
database.
10. You can now go out to the operating system and delete the files associated
with redo log groups 1, 2, and 3 in step 2 above as they are no longer
needed:
% rm /usr/oracle/dbs/log1PROD.dbf
% rm /usr/oracle/dbs/log2PROD.dbf
% rm /usr/oracle/dbs/log3PROD.dbf
Monitor the alert.log for the times of redo log switches. Due to increased
redo log size, the groups should not switch as frequently under the same
load conditions.