Mar 28 2017

Oracle 11g R2 Resize Redo Log Size on Data Guard and Primary Database

Category: Administration,Backup And Recovery,Errors and SolutionsFatih Acar @ 15:07

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.

4,085 total views, 5 views today

Tags: Database Administration, Oracle Administration

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.