Detailed explanation of ADG three mode switching

Introduction to AGD

Data Guard can open the database in a read-only mode, but at this time, the process of data synchronization using logs by Media Recovery stops. If the physical standby database is in the process of recovery, the database cannot open queries, that is, the log application and read-only opening are mutually exclusive (before 10g).

The Active Data Guard function introduced in Oracle 11g solves the contradiction (10g physical DG). While using logs to recover data, you can open the database in a read-only way. Users can query and report on the standby database, which is similar to the function of logical Data Guard standby database (query function). However, the efficiency of data synchronization is higher Lower resource requirements for hardware. In this way, the efficiency of the hardware resources of the physical standby database can be brought into greater play.

ADG architecture

Three modes of ADG

1. Maximum Performance mode

1) This mode is the default mode, which can ensure the highest availability of the main database;

2) The master node submits transactions at any time. When a transaction is committed, redo writes to at least one slave node, but the writes are not necessarily synchronized;

2) Ensure that the operation of the main database is not affected by the standby database, the transactions of the main database are submitted normally, and the operation of the main database is not affected by any problems of the standby database;

4) Advantages: it avoids the impact of the standby database on the performance and availability of the primary database;

5) Disadvantages: if the recovery data related to the transactions submitted by the primary database is not sent to the standby database, these transaction data will be lost, and no data loss can be guaranteed.

2. Maximum Availability

1) This mode provides data protection capability second only to "maximum protection mode" (between maximum protection and maximum performance);

2) The transaction of the primary database can only be committed after at least one physical standby database receives the redo log;

3) When the primary database cannot find a suitable standby database to write, the primary database will not be shut down, but temporarily reduced to the "maximum performance mode". After recovering from the database, it will return to the "maximum available mode";

4) Advantages: this mode can ensure that there is no data loss in the standby database without problems, which is a compromise method;

5) Disadvantages: in the process of normal operation, the disadvantage is that the performance of the main library is affected by many factors.

3. Maximum Protection mode

1) This mode provides the highest level of data protection capability;

2) Before the transaction of the primary node is committed, the redo is written into the online log, and the transaction of the primary database can be committed only after at least one physical standby database receives the redo log;

3) When the primary database cannot find a suitable standby database to write, the primary database will automatically shut down to prevent unprotected data;

4) Advantages: this mode can ensure that there is no data loss in the standby database;

5) Disadvantages: the automatic shutdown of the primary database will affect the availability of the primary database. At the same time, it can only be submitted after the backup database is restored. It has very high requirements for network and other objective conditions, and the performance of the primary database will be greatly impacted.

Comparison of three modes

Comparison itemRedo write or transfer processNetwork transmission modeConfirm whether to drop the discstandby redologs
Maximum protectionlgwrsyncaffirmneed
Maximum availablelgwrsyncaffirmneed
Maximum performancelgwr or archsync or asyncConfirm or noaffirmnot essential

Confirm: before the log writing process, the archive log and standby database log must be written synchronously.
NOFFIRM: the log writing process in the main library does not wait for all disk IO to complete.
(the default is NOFFIRM)

Parameter correlation

Set parameter location

Main database log_archive_dest_n parameter setting

Example: *. log_archive_dest_2=‘service=orcl lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=orcl’

Analysis of important parameters

Arch: only maximum performance mode is supported. The archive log is transferred to the standby rfs process through the arch process on the primary node, saved to the specified path (if there is a standby redo logfile, it is saved to this location, and then archived), and then redo is applied.

LGWR: the primary module uses LGWR to instantly transfer logs to the standby rfs process and save them to the standby redo logfile. Instead of waiting until the archiving operation, the logs are saved to the standby redo logfile, then archived, and then redo applications.

Transmission with LGWR is roughly as follows:

1) Primary database: as long as a new redo log is generated, the lgwr process will trigger the LNSn process to transfer the newly generated log to the standby database rfs process.

2) Standby database: after receiving the log, the rfs process writes it into the standby redo log. If the standby database has enabled the real-time application, it will apply the log immediately. If it has not been enabled, it will be applied after the standby redo log is archived.

3) The difference between async and sync is that sync starts transmission when the redo is still in memory, while async starts transmission after the redo is written to the online redo log.

In terms of real-time synchronization, LGWR (sync) > LGWR (async) > arch

In sync mode, when the primary database generates any redo, the network transmission to the standby database RFS process is triggered immediately;

In async mode, when the primary database generates redo, it is first written to the local online redo logfile file. The LNSn process takes the redo data from the online redo logfile file and transmits it to the standby database RFS process through the network.

Switching between three modes

The first performance > > availability > > protection sequence needs to be executed in the main database, and the main database must be in mount state;

If you have operated once by the performance > > availability > > protection data protection level, you can operate directly when you operate again;

Protection > > availability > > performance can be operated directly in sequence

Example demonstration

Switch from maximum performance mode to maximum available mode

  • Check the current mode of ADG first
--Main library execution
SQL>  select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

--Standby database execution
SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
  • Change the log_archive_dest_n parameter configuration of the main library
--Main library execution
SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=ruiadg2 NOAFFIRM ASYNC
                                                  VALID_FOR=(ONLINE_LOGFILES,PR
                                                 IMARY_ROLE) db_unique_name=rui
                                                 adg
SQL> alter system set log_archive_dest_2='service=ruiadg2 NOAFFIRM LGWR SYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=ruiadg';

System altered.

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=ruiadg2 NOAFFIRM LGWR
                                                 SYNC  VALID_FOR=(ONLINE_LOGFIL
                                                 ES,PRIMARY_ROLE) db_unique_nam
                                                 e=ruiadg
  • Then proceed as follows
--Main library operation
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

--Standby database operation
SQL>alter database recover managed standby database cancel;

--Main library execution
SQL> startup mount
Total System Global Area 2466250752 bytes
Fixed Size                  2927384 bytes
Variable Size             671089896 bytes
Database Buffers         1778384896 bytes
Redo Buffers               13848576 bytes
Database mounted.

SQL> alter database set standby database to maximize availability;--Switch to maximum available mode

Database altered.

SQL> alter database open;

Database altered.

So far, the switching action has been completed.

  • Query current protection mode
--Main library execution:

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM AVAILABILITY RESYNCHRONIZATION

--Standby database execution
SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

reference resources:
https://blog.csdn.net/weixin_33873846/article/details/92438388
https://www.cnblogs.com/kawashibara/p/10054471.html

Tags: Database Oracle

Posted on Tue, 23 Nov 2021 19:12:22 -0500 by iMiles