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

DBsGuru 11 mins1
()

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.

 

Related articles

, ,



Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

Hello and welcome to DBsGuru,DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!Share Learn Grow!

One thought on “Add & Drop Standby Redolog Files on Standby (Dataguard) in Oracle”

  1. Hi there! Someone in my Myspace group shared this website with us so I came to give it a look.
    I’m definitely loving the information. I’m book-marking and will be tweeting this to my followers!
    Exceptional blog and great style and design.

Comments are closed.

Related Articles