Two experiments on Oracle Dataguard Standby Redo Log

In the Data Guard environment, Standby Redo Log is a special Log type. We recommend that you configure the Standby Redo Log on the Primary and Standby ports from the latest DG Installation Guide.
 
To put it simply, Standby Redo Log is the online Redo Log that is gradually executed when the Standby application transmits the redo log. Although the Standby end also has online redo logs, online redo logs are not used in the redo apply process. Even if 11g Active Data Guard supports reading standby Data from apply, this operation is read-only and does not involve the generation of the redo log of the current instance (note that this operation is generated ). Therefore, online redo log is not required if it is always in the Standby role.
 
The redo log information transmitted by Primary is stored in the standby redo log group for apply. In addition, it switches with the switch action of the master database Primary log. This is why standby redo log is recommended to be consistent with Primary online redo log.
 
The following describes how to maintain Standby Redo logs from the maintenance perspective.

-------------------------------------- Split line --------------------------------------

References:

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby

-------------------------------------- Split line --------------------------------------

1. Environment Introduction

 

The database version is 11.2.0.4, and the DG Environment has been built. Primary database instance name ora11g.

 

SQL> select name, DATABASE_ROLE from v $ database;

 

NAME DATABASE_ROLE

-------------------------

ORA11G PRIMARY

 

Standby redo log also needs to be configured on Primary. However, this redo log is the same as the online redo log on the Standby end. It only takes effect after the switch/failover role.
 
 

SQL> col dbid for a20;

SQL> select group #, dbid, sequence #, archived, status from v $ standby_log;

 

GROUP # dbid sequence # ARCHIVED STATUS

----------------------------------------------------------

4 UNASSIGNED 0 YES UNASSIGNED

5 UNASSIGNED 0 YES UNASSIGNED

6 UNASSIGNED 0 YES UNASSIGNED

 

Set automatic file management parameters to Auto.

 

SQL> show parameter standby_file

 

NAME TYPE VALUE

-----------------------------------------------------------------------------

Standby_file_management string AUTO

 

2. Configure the Standby Redo Log on the Primary end.

 

Under the Primary role, Standby Redo Log is not used. In principle, there is no special purpose. Therefore, when viewing the v $ standby_log view, the status of the Primary side is unsigned, that is, it is not used.
 
In Primary's normal Read Write state, we can add the standby log group.

 

SQL> alter database add standby logfile group 8 size 100 m;

Database altered

 

SQL> select group #, dbid, sequence #, archived, status from v $ standby_log;

 

GROUP # dbid sequence # ARCHIVED STATUS

----------------------------------------------------------

4 UNASSIGNED 0 YES UNASSIGNED

5 UNASSIGNED 0 YES UNASSIGNED

6 UNASSIGNED 0 YES UNASSIGNED

8 UNASSIGNED 0 YES UNASSIGNED

 

In v $ logfile, you can see the corresponding added file. Because of the OMF feature, two member groups are created and there is a corresponding image in the recovery area.

 

SQL> select group #, type, member from v $ logfile;

 

GROUP # TYPE MEMBER

-------------------------------------------------------------------------------------------------
 
(Space reasons, omitted ......)

7 ONLINE/u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_7_9pclt1lt _. log
 
8 STANDBY/u01/app/oradata/ORA11G/onlinelog/ow.mf_8_9pgqt9hg _. log

8 STANDBY/u01/app/fast_recovery_area/ORA11G/onlinelog/o1_mf_8_9pgqtcgj _. log
 
 

16 rows selected

 

On the standby end, we can check whether the corresponding standby redo log is automatically added.

 

SQL> select group #, sequence #, dbid, status from v $ standby_log;

 

GROUP # SEQUENCE # DBID STATUS

--------------------------------------------------

4 34 4239941846 ACTIVE

5 0 UNASSIGNED

6 0 UNASSIGNED

 

The Primary end deletes the standby redo log.

 

SQL> alter database drop standby logfile group 8;

Database altered

 

SQL> select group #, dbid, sequence #, archived, status from v $ standby_log;

 

GROUP # dbid sequence # ARCHIVED STATUS

----------------------------------------------------------

4 UNASSIGNED 0 YES UNASSIGNED

5 UNASSIGNED 0 YES UNASSIGNED

6 UNASSIGNED 0 YES UNASSIGNED

 

From the experiment, we can easily manage standby redo logs on the Primary end. There are no excessive restrictions.

 

3. Standby Redo Log Maintenance

 

Current Online Redo Log of Primary.

 

SQL> select group #, sequence #, bytes, status from v $ log;

 

GROUP # SEQUENCE # BYTES STATUS

----------------------------------------------

1 32 104857600 INACTIVE

2 34 52428800 INACTIVE

3 35 52428800 CURRENT

7 33 10485760 INACTIVE

 

In this case, the standby redo log on the standby end is as follows:

 

SQL> select group #, sequence #, dbid, status from v $ standby_log;

 

GROUP # SEQUENCE # DBID STATUS

--------------------------------------------------

4 35 4239941846 ACTIVE

5 0 UNASSIGNED

6 0 UNASSIGNED

 

Standby redo log corresponds to the online current redo log of the current Primary. It is reflected in the same sequence. We try to add the standby log directly.
 
 

SQL> alter database add standby logfile group 8 size 100 m;

Alter database add standby logfile group 8 size 100 m

 

ORA-01156: Recovering or flashing in progress may require access to files

 

When the current standby is in the apply process, terminate the apply action.

 

 

SQL> alter database recover managed standby database cancel;

Database altered

 

SQL> alter database add standby logfile group 8 size 100 m;

Database altered

 

Standby redo log.

 

SQL> select group #, dbid, sequence #, archived, status from v $ standby_log;

 

GROUP # dbid sequence # ARCHIVED STATUS

----------------------------------------------------------

4 4239941846 35 YES ACTIVE

5 UNASSIGNED 0 NO UNASSIGNED

6 UNASSIGNED 0 NO UNASSIGNED

8 UNASSIGNED 0 YES UNASSIGNED

 

Then you can start the redo apply process.

 

SQL> alter database recover managed standby database using current logfile disconnect from session;
 
Database altered

For more details, please continue to read the highlights on the next page:

1 2 Next Page