Recreate Redo / Standby Logs in a DG Broker Environment

Posted in: Oracle, Technical Track

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
  1. Single node database (non-RAC) running in rdbms 12.1 version.
  2. Configured with DG Broker without fast-start failover feature.
  3. 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.

email

Interested in working with Suresh? Schedule a tech call.

About the Author

Yet another Mechanical Engineer, who likes Oracle Database.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *