Data Guard -- theoretical explanation

Posted by azaidi on Mon, 14 Feb 2022 03:01:37 +0100

1.Data Guard

2 detailed explanation of DG services – Redo Transport Services

3 DG Services details – Apply Services

3.1 Apply Services

The application service can automatically apply the received redo data in the standby database, so as to maintain the synchronization of the primary and standby databases. By default, only after the standby redo log is archived can the apply services apply this information.
If real-time apply is enabled, the application services will directly apply the data in the current standby redo. If there is data, it will be applied directly, instead of waiting for archiving.

Redo Apply (physical standby databases only): use media recovery to keep the main database and physical standby database synchronized.
SQL Apply (logical standby databases only): rebuild SQL statements from the received redo data, and then execute these SQL statements on the logical standby to achieve synchronization.

Process corresponding to Apply Service on Standby Database:

If it is a physical standby database, it is managed recovery by MRP
Process) process to be responsible for Redo Apply logs.
If it is a logical standby database, LSP (Logical Standby)
Process) process to take charge of SQL Apply.

3.2 configuration options of application services

3.2.1 real time application of redo data using real time apply

(1) physical standby databases:

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

(2)logical standby databases:

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

3.2.2 specify backup archive delay apply

Set Time Delay
We can log in primary database and standby database_ ARCHIVE_ DEST_ N set the DELAY=minutes attribute in the parameter. Default DELAY=30 minutes.

Cancel Time Delay
For physical standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

For logical standby database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

3.2.3 stop Redo Apply

For physical standby database: last but not least ⛔ finish, or DG will be finished

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

For logical standby database:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

3.3.3 monitor the application of primary, physical, snapshot and standby databases

Standby database check process status
select process,status from V$MANAGED_STANDBY;  
Check whether the main library has eeror
select dest_name,status,error from V$ARCHIVE_DEST; 
View log application of primary and standby databases
select sequence#,applied from V$ARCHIVED_LOG;

4 detailed explanation of DG services – Role Transitions

4.1 Role Transitions

Switchover
Switchover switching allows the primary to switch with a standby database, and there is no data loss during this switching.

Failover
When the primary database is unavailable or unresponsive, we can activate the standby database to the primary state. Before the standby database is activated, if it is not in the mode of maximum protection or maximum availability, some data may be lost. If the primary database has started Flashback Database, you can flash the primary database back to the previous state, so that the original primary database can continue to be used as a standby database.

4.1.1 preparation for role transition

Verify the configuration information of the primary and standby databases, including initialization parameters, archiving mode, standby redo logs and online redo log s.
Query V $archive in the main library_ DEST_ In the status view, verify that there is no redo transmission error or redo gap in the standby database.
Ensure that the Temp tablespaces of the primary and standby databases are consistent.
Remove the settings of any delay apply redo on standby.
If the RAC primary database is switched to the physical standby database, only one node can be reserved in the RAC primary database, and other nodes need to be closed. After the switchover is completed, start the closed node.
If the standby database is a physical standby of real-time apply and is in the read only mode, it is recommended to start the standby database to the mount state instead of the open state before switching, so as to achieve the fastest role switching without clearing the user's session connection before switching.

If there are multiple standby, how to choose during Role Transition?
(1) The location of the standby database.
(2) Performance of standby database, such as CPU,I/O, bandwidth, etc.
(3) The time required to execute the role transition mainly depends on how much redo data needs to be applied.
(4) The type of Standby database.

4.1.2 Switchover description

The switchover operation of DG is divided into two steps:

Switch the current primary database to the standby database
Switch the original / standby database to the primary database.

4.1.3 Failover description

Precautions for Failover:

If possible, before activating the standby database, the redo data of the primary database should be transferred to the standby database as much as possible and applied. This reduces data loss.
maximum protection mode cannot fail over.

4.2 switchover of physical standby

Verify whether the primary database can be switched to the standby database

Execute the following SQL query in the main database:

SQL> select switchover_status,database_role,open_mode from v$database;

The query result is TO STANDBY or SESSIONS ACTIVE, indicating that switching can be performed; When session is active, WITH SESSION SHUTDOWN needs to be added for standby database switching;

Switch the primary database to the standby database

SQL> alter database commit to switchover to physical standby with session shutdown;

If you want to do a DUBUG during switching

SQL> oradebug setmypid;
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> oradebug tracefile_name;

The original master library and start it to the mount state

SQL> select open_mode from v$database;
SQL> shutdown immediate;
SQL> startup mount;

Verify whether the original / standby database can be switched to the primary database

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

The result is TO PRIMARY or SESSIONS ACTIVE, indicating that it can be switched to the primary database;

Switch the original / standby database to the primary database

SQL> alter database commit to switchover to primary with session shutdown;
SQL> select open_mode from v$database;

Open new master library

SQL> ALTER DATABASE OPEN;

Start REDO APPLY on the new physical standby

SQL> alter database recover managed standby database disconnect from session;

verification

SQL> alter system switch logfile; 
SQL> select sequence#,applied from v$archived_log;

4.3 Failover of physical standby

Generally, the Failover switch is caused by the failure of the primary database and problems. However, as long as the primary database can be started to the mount state, any archive and current online redo log s that are not sent can be brushed to the standby database in the 11g environment. As long as the Flush is successful, the Failover will not lose data. If it is a 10g environment, you need to manually copy the archive and redo logs to the standby database, and manually register the file information and apply it

Brush the Redo data that has not been sent from the original primary database to the standby database

SQL> ALTER SYSTEM FLUSH REDO TO 'study_st';

Verify the archive information recently received by the standby database

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

Confirm and resolve GAP
Query V $archive in the standby database_ GAP, confirm whether there is GAP. If there is a copy from the main database, register it.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Stop Redo Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

End applying all Redo data
Execute the following SQL in the standby database to end all applications:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Verify whether the standby database can be switched to the primary database

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Switch the standby database to the primary database
Execute the following SQL to complete the switch:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;