Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle

April 9, 2021

Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle

In this article, we are going to demonstrate add & drop standby redolog files on standby (dataguard) in Oracle. Drop or add standby redolog files require in case we resized/recreate redolog in PRIMARY so need to follow same on STANDBY database or some times we want to move standby redolog file to a different location from the existing location. Below is step-by-step demonstrations on add drop standby redolog file on STANDBY database. Having

Drop Standby Redolog Files

Step1. Validate existing redolog files.

STANDBY> set lines 333
STANDBY>col member for a85
STANDBY>select member,type from v$logfile order by type;

MEMBER                                                                                TYPE
------------------------------------------------------------------------------------- -------
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_2_j4n4cd75_.log           ONLINE
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_1_j4n4cb6n_.log           ONLINE
/u01/app/oracle/redolog/suncdb/redo04a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo05a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo03a.log                                            STANDBY

Step 2. Stop MRP service.

STANDBY> alter database recover managed standby database cancel ;

Database altered.

Step 3. Disable parameter STANDBY_FILE_MANAGEMENT to MANUAL.

STANDBY> alter system set standby_file_management='MANUAL' scope=both;

System altered.

STANDBY> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

Step 4. Drop existing standby redolog files.

STANDBY>alter database drop standby logfile group 3;

Database altered.

STANDBY>alter database drop standby logfile group 4;

Database altered.

STANDBY>alter database drop standby logfile group 5;

Database altered.

Click here for more details on add drop redolog files in Oracle database.

Step 5. Validate logfile after drop all standby redolog.

STANDBY>select member,type from v$logfile order by type;

MEMBER                                                                                TYPE
------------------------------------------------------------------------------------- -------
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_2_j4n4cd75_.log           ONLINE
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_1_j4n4cb6n_.log           ONLINE

Step 6. Enable parameter STANDBY_FILE_MANAGEMENT to AUTO.

STANDBY>alter system set standby_file_management='AUTO' scope=both;

System altered.

STANDBY> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

Step 7. Start MRP service and validate database role.

STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

STANDBY>select name,db_unique_name,open_mode,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ -------------------- ---------------- --------------------
SUNCDB    suncdb_s                       MOUNTED              PHYSICAL STANDBY NOT ALLOWED

Step 8. Validate MRP, RFS processes, and applied archives.

STANDBY>select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         73       3106          1
MRP0      WAIT_FOR_LOG          1         73          0          0

7 rows selected.

STANDBY> select sequence#,first_time,next_time from v$archived_log order by sequence# desc;

  SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        74 15-MAR-2021 10:55:26 15-MAR-2021 11:08:38
        73 15-MAR-2021 10:43:43 15-MAR-2021 10:55:26
        72 15-MAR-2021 10:19:22 15-MAR-2021 10:43:43
        71 15-MAR-2021 10:18:16 15-MAR-2021 10:19:22
        70 15-MAR-2021 10:17:07 15-MAR-2021 10:18:16
        69 12-MAR-2021 16:38:25 15-MAR-2021 10:17:07
        68 12-MAR-2021 16:37:04 12-MAR-2021 16:38:25
        67 12-MAR-2021 16:36:41 12-MAR-2021 16:37:04
        66 11-MAR-2021 18:18:08 12-MAR-2021 16:36:41
        65 11-MAR-2021 18:07:55 11-MAR-2021 18:18:08
        64 11-MAR-2021 17:58:09 11-MAR-2021 18:07:55

Few things remember for this demonstration.

==> Standby redolog file is recommended for real-time apply feature enabled.
==> Standby redolog group should be the same size and the same number of groups as ONLINE redolog files.
==> Recommended keeping N+1 Standby redolog group.
==> Without standby redolog files still archives applied on STANDBY refer above block output but captured below error warnings in database alert log.

Archived Log entry 22 added for thread 1 sequence 72 rlc 1053544965 ID 0xbf7cebe5 dest 2:
RFS[1]: No standby redo logfiles created
RFS[1]: Opened log for thread 1 sequence 73 dbid 3062579685 branch 1053544965
Mon Mar 15 10:43:46 2021

Add Standby Redolog Files

Step 1. Stop MRP service.

STANDBY>alter database recover managed standby database cancel ;

Database altered.

Step 2. Disable parameter STANDBY_FILE_MANAGEMENT to MANUAL.

STANDBY>alter system set standby_file_management='MANUAL' scope=both;

System altered.

STANDBY> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      MANUAL

Step 3. Add new standby group redolog files.

STANDBY> alter database add standby logfile group 3 ('/u01/app/oracle/redolog/suncdb/redo03a.log') size 100m ;

Database altered.

STANDBY> alter database add standby logfile group 4 ('/u01/app/oracle/redolog/suncdb/redo04a.log') size 100m ;

Database altered.

STANDBY> alter database add standby logfile group 5 ('/u01/app/oracle/redolog/suncdb/redo05a.log') size 100m ;

Database altered.

Step 4. Validate standby redolog files after add new group.

STANDBY>select member,type from v$logfile order by type;

MEMBER                                                                                TYPE
------------------------------------------------------------------------------------- -------
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_2_j4n4cd75_.log           ONLINE
/u01/app/oracle/fast_recovery_area/SUNCDB_S/onlinelog/o1_mf_1_j4n4cb6n_.log           ONLINE
/u01/app/oracle/redolog/suncdb/redo04a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo05a.log                                            STANDBY
/u01/app/oracle/redolog/suncdb/redo03a.log                                            STANDBY

Step 5. Enable parameter STANDBY_FILE_MANAGEMENT to AUTO.

STANDBY>alter system set standby_file_management='AUTO' scope=both;

System altered.

STANDBY> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      AUTO

Step 6. Start MRP service and validate database role.

STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

STANDBY>select name,db_unique_name,open_mode,database_role,switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- ------------------------------ -------------------- ---------------- --------------------
SUNCDB    suncdb_s                       MOUNTED              PHYSICAL STANDBY NOT ALLOWED

Step 7. Validate MRP, RFS processes, and applied archives.

STANDBY>select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         74         43          1
MRP0      APPLYING_LOG          1         74         43     204800

7 rows selected.

STANDBY>select sequence#,first_time,next_time from v$archived_log order by sequence# desc;

 SEQUENCE# FIRST_TIME           NEXT_TIME
---------- -------------------- --------------------
        75 15-MAR-2021 11:08:38 15-MAR-2021 11:14:16
        74 15-MAR-2021 10:55:26 15-MAR-2021 11:08:38
        73 15-MAR-2021 10:43:43 15-MAR-2021 10:55:26
        72 15-MAR-2021 10:19:22 15-MAR-2021 10:43:43
        71 15-MAR-2021 10:18:16 15-MAR-2021 10:19:22
        70 15-MAR-2021 10:17:07 15-MAR-2021 10:18:16
        69 12-MAR-2021 16:38:25 15-MAR-2021 10:17:07
        68 12-MAR-2021 16:37:04 12-MAR-2021 16:38:25
        67 12-MAR-2021 16:36:41 12-MAR-2021 16:37:04
        66 11-MAR-2021 18:18:08 12-MAR-2021 16:36:41
        65 11-MAR-2021 18:07:55 11-MAR-2021 18:18:08

Click here to know more about Step by Step Physical Standby Database Creation in Oracle.
Click here to know about ADD-DROP Redo log files in Oracle Database.

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DATABASE, DATAGUARD, ORACLE

Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle

Related articles