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.
Share this
You May Also Like
These Related Stories
No Comments Yet
Let us know what you think