Oracle DataGuard – How to resize Redo Log and Standby Redo Log

How to resize Redo Log and Standby Redo Log in Oracle DataGuard environment?

I needed to resize the redo log as it takes about 4 days to do a log switch. The recommended log switch frequency is at least once an hour.  It is not possible to resize the redo log or standby redo log (SRL) dynamically. We have to drop and recreate them.

Primary Server

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 15:05:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
OPEN         CSS                    PRIMARY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 GROUP# Size in MB
---------- ----------
 1 500
 2 500
 3 500
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 GROUP# Size in MB
---------- ----------
 4 500
 5 500
 6 500
 7 500

Standby Server

sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 15:05:40 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED css PHYSICAL STANDBY
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 GROUP# Size in MB
---------- ----------
 1 500
 2 500
 3 500
SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;
 GROUP# Size in MB
---------- ----------
 4 500
 5 500
 6 500
 7 500

Do this for both Primary and Standby

SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management      string                                          AUTO
SQL> alter system set standby_file_management=manual;
System altered.
SQL> show parameter standby_file_management;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
standby_file_management      string                                          MANUAL

Primary

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
 1 ONLINE /u01/oradata/css/redolog_1a.dbf NO
 1 ONLINE /u02/oradata/css/redolog_1b.dbf NO
 2 ONLINE /u01/oradata/css/redolog_2a.dbf NO
 2 ONLINE /u02/oradata/css/redolog_2b.dbf NO
 3 ONLINE /u01/oradata/css/redolog_3a.dbf NO
 3 ONLINE /u02/oradata/css/redolog_3b.dbf NO
 4 STANDBY /u01/oradata/css/stby_log_4a.dbf NO
 4 STANDBY /u02/oradata/css/stby_log_4b.dbf NO
 5 STANDBY /u01/oradata/css/stby_log_5a.dbf NO
 5 STANDBY /u02/oradata/css/stby_log_5b.dbf NO
 6 STANDBY /u01/oradata/css/stby_log_6a.dbf NO
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
 6 STANDBY /u02/oradata/css/stby_log_6b.dbf NO
 7 STANDBY /u01/oradata/css/stby_log_7a.dbf NO
 7 STANDBY /u02/oradata/css/stby_log_7b.dbf NO
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
 1 CURRENT
 3 INACTIVE
 2 INACTIVE

SQL> alter database drop logfile group 3;
Database altered.
# rm /u01/oradata/css/redolog_3a.dbf /u02/oradata/css/redolog_3b.dbf
SQL> alter database add logfile group 3 ('/u01/oradata/css/redolog_3a.dbf','/u02/oradata/css/redolog_3b.dbf') size 100M;
Database altered.

Repeat this for all inactive redo log group.  Do a switch log on primary to change the current redo log group before dropping and re-creating the redo log group.

SQL> select group#,status,BYTES/1024/1024 mb from v$log;
GROUP# STATUS MB
---------- ---------------- ----------
 1 CURRENT 500
 3 UNUSED 100
 2 UNUSED 100
SQL> alter system switch logfile;
System altered.
SQL> select group#,status,BYTES/1024/1024 mb from v$log;
GROUP# STATUS MB
---------- ---------------- ----------
 1 INACTIVE 500
 3 UNUSED 100
 2 CURRENT 100

Final output should look similar to this;

SQL> select group#,status,BYTES/1024/1024 mb from v$log;
GROUP# STATUS MB
---------- ---------------- ----------
 1             UNUSED           100
 3             UNUSED           100
 2            CURRENT         100

Now for the standby redo log on primary; Since all is unused, you may just drop and re-create them without any issue. SRL is only used by standby database to apply the redo log. It is being created on the primary so that if the primary is being switch-over to standby role, we do not have to create the SRL again. 

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.
# rm /u01/oradata/css/stby_log_4a.dbf','/u02/oradata/css/stby_log_4b.dbf
SQL> alter database add standby logfile group 4 ('/u01/oradata/css/stby_log_4a.dbf','/u02/oradata/css/stby_log_4b.dbf') size 100M; 

Do it for all the SRL groups. Verify final configuration. 

SQL> select group#,status,bytes/1024/1024 mb from v$standby_log; 
GROUP# STATUS MB ---------- ---------- ---------- 
4 UNASSIGNED 100 
5 UNASSIGNED 100 
6 UNASSIGNED 100 
7 UNASSIGNED 100

On the standby database;

SQL> select * from v$logfile;
 GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
 1 ONLINE /u01/oradata/css/redolog_1a.dbf NO
 1 ONLINE /u02/oradata/css/redolog_1b.dbf NO
 2 ONLINE /u01/oradata/css/redolog_2a.dbf NO
 2 ONLINE /u02/oradata/css/redolog_2b.dbf NO
 3 ONLINE /u01/oradata/css/redolog_3a.dbf NO
 3 ONLINE /u02/oradata/css/redolog_3b.dbf NO
 4 STANDBY /u01/oradata/css/stby_log_4a.dbf NO
 4 STANDBY /u02/oradata/css/stby_log_4b.dbf NO
 5 STANDBY /u01/oradata/css/stby_log_5a.dbf NO
 5 STANDBY /u02/oradata/css/stby_log_5b.dbf NO
 6 STANDBY /u01/oradata/css/stby_log_6a.dbf NO
 6 STANDBY /u02/oradata/css/stby_log_6b.dbf NO
 7 STANDBY /u01/oradata/css/stby_log_7a.dbf NO
 7 STANDBY /u02/oradata/css/stby_log_7b.dbf NO
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CLEARING
3 CLEARING
2 CURRENT
SQL>alter database clear logfile group 3;
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database drop logfile group 3
#rm /u01/oradata/css/redolog_3a.dbf /u02/oradata/css/redolog_3b.dbf

Repeat this for the Redo Log group with status on “CLEARING”. After which do the following;

SQL> alter database recover managed standby database disconnect from session using current logfile;

Do a switch log on the primary database. 

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
 1 CURRENT
 3 UNUSED
 2 CLEARING
SQL> alter database recover managed standby database cancel;
For the SRL group;

SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
 4 ACTIVE
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

Do the same for the “UNASSIGNED” SRL group. 

SQL> alter database clear logfile group 4;
Database altered.
SQL> select group#,status from v$standby_log;
GROUP# STATUS
---------- ----------
 4 UNASSIGNED
 5 UNASSIGNED
 6 UNASSIGNED
 7 UNASSIGNED

Final redo log and SRL group config;

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
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
SQL> alter system set standby_file_management=auto; => For both primary and standby
SQL> alter database recover managed standby database disconnect from session using current logfile; => For standby
Verify MRP has started.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
MRP0 WAIT_FOR_LOG 87
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
 87
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: