Recreate Redo / Standby Logs in a DG Broker Environment
While increasing the redo log file size for my production environment, I came across a requirement I’d like to share with you. The process should be quite straightforward as we can add / drop redo log files online. However, since the production environment was running with Data Guard (DG) broker, I had some doubts about whether I could complete the process without breaking anything. I shouldn’t have worried. Here are my test environment details, which replicate the production environment.
Test environment configuration
- Single node database (non-RAC) running in rdbms 12.1 version.
- Configured with DG Broker without fast-start failover feature.
- Database files not using OFA architecture. If you’re using OFA in your database, you can avoid mentioning the actual logfile names.
Before I began any activity, I ensured the standby database was in sync with the primary database.
Using sqlplus utility:
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS,client_process FROM V$MANAGED_STANDBY;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS CLIENT_P
--------- ------------ ---------- ---------- ---------- ---------- --------
ARCH CLOSING 197689 1003520 226 0 ARCH
ARCH CLOSING 197688 1003520 231 0 ARCH
ARCH CONNECTED 0 0 0 0 ARCH
ARCH CLOSING 197687 1013760 1885 0 ARCH
MRP0 APPLYING_LOG 197690 613789 1024000 0 N/A
RFS IDLE 197690 613789 1 0 LGWR
RFS IDLE 0 0 0 0 UNKNOWN
7 rows selected.
Using dgmgrl utility:
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Members:
test - Primary database
test_dr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 17 seconds ago)
DGMGRL> show database 'test';
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> show database 'test_dr';
Database - test_dr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.88 MByte/s
Real Time Query: OFF
Instance(s):
test
Database Status:
SUCCESS
As a first step toward meeting this requirement, I disabled the apply process using in dgmgrl utility.
DGMGRL> EDIT DATABASE 'test_dr' SET STATE='APPLY-OFF';
Succeeded.
DGMGRL> show database 'test_dr';
Database - test_dr
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 7 seconds (computed 0 seconds ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
test
Database Status:
SUCCESS
In the primary database, I changed the value of parameter standby_file_management to manual, which disables automatic standby file management. I suggest you keep automatic standby file management disabled even if you’re using OFA architecture. I then added new redo and standby logs of the same size. Finally, I dropped the old logs manually.
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
test READ WRITE ARCHIVELOG
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS,client_process FROM V$MANAGED_STANDBY;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS CLIENT_P
--------- ------------ ---------- ---------- ---------- ---------- --------
ARCH CLOSING 197691 1 23 0 ARCH
ARCH CLOSING 197689 1003520 226 0 ARCH
ARCH CLOSING 197292 778241 108 0 ARCH
ARCH CLOSING 197690 772096 1074 0 ARCH
LNS WRITING 197692 1016 1 0 LNS
SQL> select thread#,max(sequence#) from gv$log where status='INACTIVE' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 197691
SQL> alter system set standby_file_management=manual;
System altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ( '/p01/oradata/test/onlinelog/loggroup4_1.log', '/u01/fra/test/onlinelog/loggroup4_2.log') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ( '/p01/oradata/test/onlinelog/loggroup5_1.log', '/u01/fra/test/onlinelog/loggroup5_2.log') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ( '/p01/oradata/test/onlinelog/loggroup6_1.log', '/u01/fra/test/onlinelog/loggroup6_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 15( '/p01/oradata/test/onlinelog/stbyloggrp15_1.log', '/u01/fra/test/onlinelog/stbyloggrp15_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 16( '/p01/oradata/test/onlinelog/stbyloggrp16_1.log', '/u01/fra/test/onlinelog/stbyloggrp16_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 17( '/p01/oradata/test/onlinelog/stbyloggrp17_1.log', '/u01/fra/test/onlinelog/stbyloggrp17_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 18( '/p01/oradata/test/onlinelog/stbyloggrp18_1.log', '/u01/fra/test/onlinelog/stbyloggrp18_2.log') SIZE 1024M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1 1 197691 524288000 512 2 YES INACTIVE 6.0121E+12 2019-10-10 03:44:03 6.0121E+12 2019-10-10 03:44:05 0
2 1 197690 524288000 512 2 YES INACTIVE 6.0121E+12 2019-10-10 03:40:17 6.0121E+12 2019-10-10 03:44:03 0
3 1 197692 524288000 512 2 YES INACTIVE 6.0121E+12 2019-10-10 03:44:05 6.0121E+12 2019-10-10 03:45:29 0
4 1 197693 1073741824 512 2 YES INACTIVE 6.0121E+12 2019-10-10 03:45:29 6.0121E+12 2019-10-10 03:49:31 0
5 1 197694 1073741824 512 2 NO CURRENT 6.0121E+12 2019-10-10 03:49:31 2.8147E+14 0
6 1 0 1073741824 512 2 YES UNUSED 0 0 0
6 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
4 1 197693 1073741824 512 2 YES INACTIVE 6.0121E+12 2019-10-10 03:45:29 6.0121E+12 2019-10-10 03:49:31 0
5 1 197694 1073741824 512 2 NO CURRENT 6.0121E+12 2019-10-10 03:49:31 2.8147E+14 0
6 1 0 1073741824 512 2 YES UNUSED 0 0 0
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
LAST_CHANGE# LAST_TIME CON_ID
------------ ------------------- ----------
11 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
0
12 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
0
13 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
0
14 UNASSIGNED 1 0 524288000 512 0 YES UNASSIGNED
0
15 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
16 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
17 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
18 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
8 rows selected.
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 13;
Database altered.
SQL> alter database drop logfile group 14;
Database altered.
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
LAST_CHANGE# LAST_TIME CON_ID
------------ ------------------- ----------
15 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
16 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
17 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
18 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
I carried out similar steps in the standby database.
SQL> alter system set standby_file_management=manual;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1 1 0 524288000 512 2 YES UNUSED 0 0 0
3 1 0 524288000 512 2 YES UNUSED 0 0 0
2 1 0 524288000 512 2 YES UNUSED 0 0 0
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
LAST_CHANGE# LAST_TIME CON_ID
------------ ------------------- ----------
11 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
12 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
13 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
14 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ( '/p01/oradata/test_dr/onlinelog/loggroup4_1.log', '/p01/fra/test_dr/onlinelog/loggroup4_2.log') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ( '/p01/oradata/test_dr/onlinelog/loggroup5_1.log', '/p01/fra/test_dr/onlinelog/loggroup5_2.log') SIZE 1024M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ( '/p01/oradata/test_dr/onlinelog/loggroup6_1.log', '/p01/fra/test_dr/onlinelog/loggroup6_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 15( '/p01/oradata/test_dr/onlinelog/stbyloggrp15_1.log', '/p01/fra/test_dr/onlinelog/stbyloggrp15_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 16( '/p01/oradata/test_dr/onlinelog/stbyloggrp16_1.log', '/p01/fra/test_dr/onlinelog/stbyloggrp16_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 17( '/p01/oradata/test_dr/onlinelog/stbyloggrp17_1.log', '/p01/fra/test_dr/onlinelog/stbyloggrp17_2.log') SIZE 1024M;
Database altered.
SQL> alter database add standby logfile group 18( '/p01/oradata/test_dr/onlinelog/stbyloggrp18_1.log', '/p01/fra/test_dr/onlinelog/stbyloggrp18_2.log') SIZE 1024M;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1 1 0 524288000 512 2 YES UNUSED 0 0 0
2 1 0 524288000 512 2 YES UNUSED 0 0 0
6 1 0 1073741824 512 2 YES UNUSED 0 0 0
4 1 0 1073741824 512 2 YES UNUSED 0 0 0
5 1 0 1073741824 512 2 YES UNUSED 0 0 0
3 1 0 524288000 512 2 YES UNUSED 0 0 0
6 rows selected.
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
LAST_CHANGE# LAST_TIME CON_ID
------------ ------------------- ----------
11 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
12 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
13 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
14 UNASSIGNED 1 0 524288000 512 0 NO UNASSIGNED
0
15 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
16 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
17 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
18 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
8 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
4 1 0 1073741824 512 2 YES UNUSED 0 0 0
6 1 0 1073741824 512 2 YES UNUSED 0 0 0
5 1 0 1073741824 512 2 YES UNUSED 0 0 0
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 13;
Database altered.
SQL> alter database drop logfile group 14;
Database altered.
SQL> select * from v$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------------- ------------ -------------------
LAST_CHANGE# LAST_TIME CON_ID
------------ ------------------- ----------
15 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
16 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
17 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
18 UNASSIGNED 0 0 1073741824 512 0 YES UNASSIGNED
0
I enabled the apply process for the standby database using dgmgrl utility.
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Members:
test - Primary database
Warning: ORA-16792: configurable property value is inconsistent with database setting
test_dr - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 34 seconds ago)
DGMGRL> EDIT DATABASE 'test_dr' SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database 'test';
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
Database Status:
WARNING
DGMGRL> show database 'test_dr';
Database - test_dr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 84.60 MByte/s
Real Time Query: OFF
Instance(s):
test
Warning: ORA-16714: the value of property StandbyFileManagement is inconsistent with the database setting
Database Status:
WARNING
For verification purposes, I manually switched a few archive logs in the primary database then reverted the previously changed standby_file_management parameter to overcome the reported ORA-16792 error.
In the primary database:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 197693
Next log sequence to archive 197695
Current log sequence 197695
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select thread#,max(sequence#) from gv$log where status='INACTIVE' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 197696
In the standby database:
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS,client_process FROM V$MANAGED_STANDBY;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS CLIENT_P
--------- ------------ ---------- ---------- ---------- ---------- --------
ARCH CLOSING 197690 772096 1074 0 ARCH
ARCH CLOSING 197696 1 711 0 ARCH
ARCH CONNECTED 0 0 0 0 ARCH
ARCH CLOSING 197695 1 1583 0 ARCH
MRP0 APPLYING_LOG 197697 146835 2097152 0 N/A
RFS IDLE 197697 146835 1 0 LGWR
RFS IDLE 0 0 0 0 UNKNOWN
7 rows selected.
In the primary database:
SQL> alter system set standby_file_management=auto;
System altered.
In the standby database:
SQL> alter system set standby_file_management=auto;
System altered.
I waited for a few minutes then confirmed the standby database was in sync with the primary database.
In the primary database:
SQL> select thread#,max(sequence#) from gv$log where status='INACTIVE' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 197697
In the standby database:
SQL> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS,client_process FROM V$MANAGED_STANDBY;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS CLIENT_P
--------- ------------ ---------- ---------- ---------- ---------- --------
ARCH CLOSING 197697 1144832 681 0 ARCH
ARCH CLOSING 197696 1 711 0 ARCH
ARCH CONNECTED 0 0 0 0 ARCH
ARCH CLOSING 197695 1 1583 0 ARCH
MRP0 APPLYING_LOG 197698 1097869 2097152 0 N/A
RFS IDLE 197698 1097860 10 0 LGWR
RFS IDLE 0 0 0 0 UNKNOWN
7 rows selected.
From DG broker utility:
==========================
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration;
Configuration - dg_test
Protection Mode: MaxPerformance
Members:
test - Primary database
test_dr - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 49 seconds ago)
DGMGRL> show database 'test';
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> show database 'test_dr';
Database - test_dr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 4.85 MByte/s
Real Time Query: OFF
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> exit
Finally, I manually checked the availability of dropped log files in the servers and confirmed none existed.
I hope this is helpful. If you have any questions, please leave them in the comments.
Ready to optimize your Oracle Database for the future?
On this page
Share this
Share this
More resources
Learn more about Pythian by reading the following blogs and articles.
How to Test Data Guard Fast-Start Failover by Shutting Down Standby Server
![]()
How to Test Data Guard Fast-Start Failover by Shutting Down Standby Server
Jan 22, 2020 12:00:00 AM
6
min read
Failovers with Oracle Dataguard
![]()
Failovers with Oracle Dataguard
Nov 3, 2006 12:00:00 AM
5
min read
Oracle Standby redo Logs
![]()
Oracle Standby redo Logs
Aug 15, 2007 12:00:00 AM
3
min read
Ready to unlock value from your data?
With Pythian, you can accomplish your data transformation goals and more.
.svg){kind=small}
.svg){kind=small}