Sometimes, you may need to change redo log size to perform optimum size for performance. If you want to change redo log size of both standby side and primary side, you can use below commands.
Current Status Of Redo Logs
Primary Side
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#; GROUP# Size in MB ------- ---------- 1 50 2 50 3 50 SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#; GROUP# Size in MB ------- ---------- 4 50 5 50 6 50 7 50
Standby Side
SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$loggroup by group#; GROUP# Size in MB ------ ------------- 1 50 2 50 3 50 SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#; GROUP# Size in MB ------ ---------- 4 50 5 50 6 50 7 50
Change Size Of Redo Logs
Auto File Management Disable on Standby Side
SQL> alter system set standby_file_management=manual System altered.
Change Redo Log Size Of Primary Side
Note: You can drop log file when status is INACTIVE or UNUSED. Check status of redo log files. SQL> select group#,status from v$log; GROUP# STATUS ------ ------------------- 1 INACTIVE 2 INACTIVE 3 CURRENT SQL> alter database drop logfile group 1; Database altered. SQL> alter database add logfile group 1 size 100M; Database altered. SQL> select group#,status from v$log; GROUP# STATUS ------ ------- 1 UNUSED 2 INACTIVE 3 CURRENT SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 100M; Database altered. SQL> select group#,status from v$log; GROUP# STATUS ------ ------- 1 UNUSED 2 UNUSED 3 CURRENT Note : You can use switch logfile command to change status of group 3 redo log files. SQL> alter system switch logfile; System altered. SQL> select group#,status from v$log; GROUP# STATUS ------ ------- 1 CURRENT 2 UNUSED 3 INACTIVE SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 100M; Database altered. SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#; GROUP# size in MB ------ ------------- 1 100 2 100 3 100 Standby Redo Log Resizing Note : If you do not switchover, your standby log file status on primary side should be UNASSIGNED SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ ------------- 6 50 4 50 5 50 7 50 SQL> select group#,status from v$standby_log; GROUP# STATUS ------ ------------ 4 UNASSIGNED 5 UNASSIGNED 6 UNASSIGNED 7 UNASSIGNED SQL> alter database drop standby logfile group 4; Database altered. SQL> alter database add standby logfile group 4 size 100M; Database altered. SQL> alter database drop standby logfile group 5; Database altered. SQL> alter database add standby logfile group 5 size 100M; Database altered. SQL> alter database drop standby logfile group 6; Database altered. SQL> alter database add standby logfile group 6 size 100M; Database altered. SQL> alter database drop standby logfile group 7; Database altered. SQL> alter database add standby logfile group 7 size 100M; Database altered. SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ ------------- 4 100 5 100 6 100 7 100
Change Redo Log Size Of Standby Side
Note : Firstly, cancel appling from primary. SQL> alter database recover managed standby database cancel; Database altered. Note : Redo log status have to be UNUSED. You can use clear command and then you can drop redo log files. SQL> select group#,status from v$log; GROUP# STATUS ------ ------------------- 1 CURRENT 2 CLEARING 3 CLEARING Note : If you attempt to drop redo log file when status is not UNUSED, you can take below error. SQL> alter database drop logfile group 2; alter database drop logfile group 2 * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files. Note : You have to clear redo log file before drop. SQL> alter database clear logfile group 2; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 100M; Database altered. SQL> alter database clear logfile group 3; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 100M; Database altered. SQL> select group#,status from v$log; GROUP# STATUS ------ ------------------- 1 CURRENT 2 UNUSED 3 UNUSED Note : You can use switch logfile command on primary side to change log file status of standby database. On primary side SQL> alter system switch logfile; System altered. Note : You can clear and drop redo log file on standby site. SQL> alter database clear logfile group 3; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 100M; Database altered. SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#; GROUP# size in MB ------ ------------- 1 100 2 100 3 100 Standby Redo Log Resizing SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ -------------- 4 50 5 50 6 50 7 50 SQL> select group#,status from v$standby_log; GROUP# STATUS ------ ------------- 4 UNASSIGNED 5 UNASSIGNED 6 UNASSIGNED 7 ACTIVE SQL> alter database drop standby logfile group 4; Database altered. SQL> alter database add standby logfile group 4 size 100M; Database altered. SQL> alter database drop standby logfile group 5; Database altered. SQL> alter database add standby logfile group 5 size 100M; Database altered. SQL> alter database drop standby logfile group 6; Database altered. SQL> alter database add standby logfile group 6 size 100M; Database altered. Note : You can use switch logfile command on primary side to change log file status of standby database. On primary side SQL> alter system switch logfile; System altered. Note : You can clear and drop redo log file on standby site. SQL> alter database drop standby logfile group 7; Database altered. SQL> alter database add standby logfile group 7 size 100M; Database altered. SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ ------------- 4 100 5 100 6 100 7 100
Auto File Management Enable on Standby Side
SQL> alter system set standby_file_management=auto System altered.
Enable Apply Redo From Primary Side
SQL> alter system set standby_file_management=auto System altered. SQL> alter database recover managed standby database using current logfile disconnect; Database altered.