Resize redolog files in an Data Guard configuration.

When it is required to resize the online redo logfiles in a Data Guard configuration you need to perform the resize for both the online redo logfiles as well as the standby redo logfiles.

In this blog post an example how to accomplish this task. In another post will explain an issue which can occur in Rac One, where one thread is not active

We start on the STANDBY database, so the standby is ready before the primary is, we do this to eliminate potential impact when a large amount of redo is generated on the primary during the modifications. Start with the standby only when you increase the size of the logfiles.

When you want to change the redo log sizes to smaller files start on the PRIMARY database.

SQL> select thread#,group#,bytes,status from v$standby_log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------ 1 5 268435456 ACTIVE 1 6 268435456 UNASSIGNED 1 7 268435456 UNASSIGNED 2 8 268435456 UNASSIGNED 2 9 268435456 UNASSIGNED 2 10 268435456 UNASSIGNED SQL> select thread#,group#,bytes,status from v$log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------------------------ 1 1 268435456 CURRENT 1 2 268435456 CLEARING 2 3 268435456 CURRENT 2 4 268435456 CLEARING
Lets resize from to 1G, we start with adding of the new groups before we drop the old groups.

If recovery is still active and you try to add standby redo logfiles you get:
ORA-01156: recovery or flashback in progress may need access to files, so make sure recover process is stopped DGMGRL> edit database set state=apply-off; Below statement if we take the Data Guard best practice into account where we only have one file per standby redo logfile group.

alter database add standby logfile thread 1 group 11 ('+RECO') size 1G, group 12 ('+RECO') size 1G, group 13 ('+RECO') size 1G; alter database add standby logfile thread 2 group 14 ('+RECO') size 1G, group 15 ('+RECO') size 1G, group 16 ('+RECO') size 1G;

Checking the files again we see the new groups.

Time to drop the old standby redo logfiles
alter system switch logfile; alter system archive log all; alter system checkpoint; alter database drop logfile group 5; alter database drop logfile group 6; alter database drop logfile group 7; alter database drop logfile group 8; alter database drop logfile group 9; alter database drop logfile group 10;

Checking once again, the new standby redo logfiles have a size of 1GB.
SQL> select thread#,group#,bytes,status from v$standby_log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------ 1 11 1073741824 UNASSIGNED 1 12 1073741824 ACTIVE 1 13 1073741824 UNASSIGNED 2 14 1073741824 UNASSIGNED 2 15 1073741824 UNASSIGNED 2 16 1073741824 UNASSIGNED

So one thing which we didn’t change was the online redo logfile in the standby, is this requires? YES it is. Online redo log file changes are not propegated to the standby.

After a switchover non existing online logfiles are created/cleared but based on the value in the controlfile.

Let’s replace the online logs on the standby

SQL> select database_role from v$database; DATABASE_ROLE ------------------------------------------------ PHYSICAL STANDBY SQL> select thread#,group#,bytes,status from v$log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------------------------ 1 1 268435456 UNUSED 1 2 268435456 UNUSED 2 3 268435456 UNUSED 2 4 268435456 UNUSED SQL> alter system set standby_file_management=manual; alter database add logfile thread 1 group 5 ('+DATA', '+RECO') size 1G, group 6 ('+DATA', '+RECO') size 1G; alter database add logfile thread 2 group 7 ('+DATA', '+RECO') size 1G, group 8 ('+DATA', '+RECO') size 1G;

If you try to execute the above before standby_file_management is set to manual you will get an ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic.

Let’s drop the old online logfiles
alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; alter database drop logfile group 4;

SQL> alter system set standby_file_management=auto;

So now the standby/online redo logfiles are modified on the STANDBY database time to go to the primary and execute the steps for the online redo logfiles as well as the standby redo logfiles. If you receive an message ORA-01623: log # is current log for instance (thread #) – cannot drop. Check out this blog for a solution.

The last one is easy so lets start with this one.

Current status:
SQL> select database_role from v$database; DATABASE_ROLE ------------------------------------------------ PRIMARY SQL> select thread#,group#,bytes,status from v$log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------------------------ 1 1 268435456 CURRENT 1 2 268435456 INACTIVE 2 3 268435456 INACTIVE 2 4 268435456 INACTIVE SQL> select thread#,group#,bytes,status from v$standby_log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------ 1 5 268435456 UNASSIGNED 1 6 268435456 UNASSIGNED 1 7 268435456 UNASSIGNED 2 8 268435456 UNASSIGNED 2 9 268435456 UNASSIGNED 2 10 268435456 UNASSIGNED
As redo logsize needs to be the same on primary and standby we also use the file size of 1 GB.

SQL> select thread#,group#,bytes,status from v$standby_log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------ 1 5 268435456 UNASSIGNED 1 6 268435456 UNASSIGNED 1 7 268435456 UNASSIGNED 2 8 268435456 UNASSIGNED 2 9 268435456 UNASSIGNED 2 10 268435456 UNASSIGNED 1 11 1073741824 UNASSIGNED 1 12 1073741824 UNASSIGNED 1 13 1073741824 UNASSIGNED 2 14 1073741824 UNASSIGNED 2 15 1073741824 UNASSIGNED 2 16 1073741824 UNASSIGNED alter database drop logfile group 5; alter database drop logfile group 6; alter database drop logfile group 7; alter database drop logfile group 8; alter database drop logfile group 9; alter database drop logfile group 10;

Now the standby log files are dropped we can add the online redo logfiles

alter database add logfile thread 1 group 5 ('+DATA', '+RECO') size 1G, group 6 ('+DATA', '+RECO') size 1G; alter database add logfile thread 2 group 7 ('+DATA', '+RECO') size 1G, group 8 ('+DATA', '+RECO') size 1G;
Remark:

– Notice we have one group less than the standby redo logfiles, this is correct and not an error. The best practice for standby redo logfiles is that their will be one more group than the online redo logfiles

– Also a group will exists of 2 members for online redo logfiles, where with the standby redo logfiles the best practice is to have just one member per group

SQL> select thread#,group#,bytes,status from v$log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------------------------ 1 1 268435456 CURRENT 1 2 268435456 INACTIVE 2 3 268435456 INACTIVE 2 4 268435456 INACTIVE 1 5 1073741824 UNUSED 1 6 1073741824 UNUSED 2 7 1073741824 UNUSED 2 8 1073741824 UNUSED

Now drop the old online redo log files, but before we perform this step we switch logfiles and perform a checkpoint.

alter system switch logfile; alter system archive log all; alter system checkpoint; SQL> select thread#,group#,bytes,status from v$log; THREAD# GROUP# BYTES STATUS ---------- ---------- ---------- ------------------------------------------------ 1 1 268435456 INACTIVE 1 2 268435456 INACTIVE 2 3 268435456 INACTIVE 2 4 268435456 INACTIVE 1 5 1073741824 CURRENT 1 6 1073741824 UNUSED 2 7 1073741824 UNUSED 2 8 1073741824 UNUSED alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; alter database drop logfile group 4;

When you receive an error about the drop of a logfile in a RACOne database this is expected as thread 2 is not active and therefore can not be dropped, to fix this

SQL> alter database drop logfile group 3; alter database drop logfile group 3 * ERROR at line 1: ORA-01623: log 3 is current log for instance DBA1_2 (thread 2) - cannot drop ORA-00312: online log 3 thread 2: '+DATA/dba1_02/onlinelog/group_3.303.870101525' ORA-00312: online log 3 thread 2: '+RECO/dba1_02/onlinelog/group_3.828.870101525' SQL> alter database clear unarchived logfile group 3; Database altered. SQL> alter database drop logfile group 3; Database altered.

Resize redolog files in an Data Guard configuration.

Share This Story, Choose Your Platform!