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.
