There is no command to resize online redo logs in oracle as of now. So to achieve our goal of “How to Resize Online redo logs in oracle” we will do Drop and create online redo logs groups. Sorry to confuse you with title. It’s How to Re-create Online Redo Logs in Oracle.
As promised in older post “How to Recreate Standby Redo logs on Standby Database” , here is how to recreate redologs in oracle. Lets get started.
How often you will have in situation of re-creating redo log groups. once in a blue moon for a stable system. But if redo logs are sized too small can cause Performance issues. In this case you need to resize the redo logs group.
Recreation of redo logs is a online activity now. Though we will still advise to do it in lean hours.
Table of Contents
Step by Step How to Re-create Online Redo Logs in Oracle
Here is test case environment ->
1) ORACLE VERSION -> 11.2.0.4
2) SINGLE INSTANCE PRODUCTION and SINGLE INSTANCE STANDBY
3) OS -> RHEL 6
Prechecks ->
Please check if you have enough space to hold the new size of redologs groups. Once this holds true, you can start with recreation Online Redo logs on your Database.
Hint –> When you are checking for space on Primary database make sure you check it on your all the Standby databases.
Why I said so ? Well what we observed we do everything on production and forget about the Standby database. And when we switchover we see same performance issue and you probably sort it out. But this time you have to give justification to your Manager. 😉
1 Note the name of the database and the status of the database.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
sansui READ WRITE PRIMARY
2 We are going to check the name of the redolog groups. And it is important at the end of the activity.
SQL> select group#,type,member from v$logfile order by group#;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------
1 ONLINE /sansui/redo/redo1.log
2 ONLINE /sansui/redo/redo2.log
3 ONLINE /sansui/redo/redo3.log
4 STANDBY /sansui/redo/standby_redo1.log
5 STANDBY /sansui/redo/standby_redo2.log
6 STANDBY /sansui/redo/standby_redo3.log
7 STANDBY /sansui/redo/standby_redo4.log
7 rows selected.3 Here we will check the status of Online redo logs
SQL> select group#, (bytes/1024/1024) BYTES_IN_MB, status from v$log;
GROUP# BYTES_IN_MB STATUS
---------- ------------ ----------------
1 50 ACTIVE
2 50 CURRENT
3 50 INACTIVE 4 Lets add the new group with 100MB size ( as this is test environment, I created a smaller one just for an example. For you size of new group will be the size you want to make it.)
SQL> alter database add logfile group 8 '/sansui/redo/redo8.log' size 100M;
SQL> alter database add logfile group 9 '/sansui/redo/redo9.log' size 100M;
SQL> alter database add logfile group 10 '/sansui/redo/redo10.log' size 100M;
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 ACTIVE
2 CURRENT
3 INACTIVE
8 UNUSED
9 UNUSED
10 UNUSED 5 For instance, we can see group 2 is Current and group 1 is active. We will do few log switches to make sure Current is on group# 8 ( on new one and old onces are available for drop ) As you can’t drop current groups.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 ACTIVE
3 ACTIVE
8 CURRENT
9 UNUSED
10 UNUSED
6 It’s always a better to do a checkpoint. So it will make the Active groups to Inactive.
SQL> alter system checkpoint;
SQL> select group#, status from v$log;
GROUP# STATUS
--------- ----------------
1 INACTIVE
2 INACTIVE
3 INACTIVE
8 CURRENT
9 UNUSED
10 UNUSED 7 Once all the groups which are in smaller size are INACTIVE and Now we can drop ->
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;8 Just verify the status of the redo log groups ->
SQL> select group#, (bytes/1024/1024) BYTES_IN_MB, status from v$log;
GROUP# BYTES_IN_MB STATUS
--------- ----------- ----------------
8 100 CURRENT
9 100 UNUSED
10 100 UNUSED 9 No, your activity is not yet completed. Those files still present physically on the destination. Which needs to be removed on OS level.
rm /sansui/redo/redo1.log
rm /sansui/redo/redo2.log
rm /sansui/redo/redo3.log
10. Similar activity needs to be performed at Standby database/ you have a choice of restoring control file. Make sure datafile location is exact same if you are going with restoring the control file
Activity is completed!!!
Now it’s time for pointers ….
1 –> Since above example was from a test machine, I didn’t had redo log multiplexing in place. In your environment you might have multiple members in place. In this case your syntax changes for redo log addition as follows –>
alter database add logfile group 1 ('<one member>','<member 2>',....) size <new size>;2 –> In case you have your database on ASM then there here is the change in command –>
alter database add logfile group 1 ('+REDO1','+REDO2') size <size>;3 –> In case you have RAC database on ASM, your database will multiple instances. Though you have to do the above activity repeated for each instance. And we have modification in addition of the redologs as well.
alter database add logfile
thread <thread#> group <group#>
('<member 1>','<member 2>') size <new size>;Here you can see thread <thread#> will tell you for which instance you are adding. If needed I will create a separate blog post for RAC database separately for you.
Reference
Example of How To Resize the Online Redo Logfiles (Doc ID 1035935.6)