Tuesday, November 28, 2017

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

No comments:

Post a Comment