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
Ref-newbiedba.wordpress.com
