Shulou(Shulou.com)06/01 Report--

Oracle 11g DataGuard data protection mode switching

Data Guard provides three data protection modes: maximum protection (Maximum Protection), highest available (Maximum Availability), and highest performance (Maximum Performance).

If the data is sorted from low to high according to the degree of data protection or the synchronization of master-slave database data, the order of the three protection modes should be: highest performance, highest availability, and maximum protection.

This paper simulates six scenarios of switching between three data protection modes through experiments, and compares the upgrade and switching process of protection modes.

[experimental legend]

[experimental environment] operating system: Red Hat Enterprise Linux Server release 5.4

Database: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

[main database, physical backup database structure information]

[experimental process]

①, highest performance upgrade, highest available

(1) View the current database protection mode: the highest performance mode

Select database_role,protection_mode,protection_level from v$database

Main library Primary:

Physical repository Physical Standby

View log transfer mode

At present, in the maximum performance mode, the log transfer mode is the ARCH ASYNC,arch process asynchronous transfer archive mode.

(2) modify the log transfer mode of the main database

To upgrade to a maximum by modifying the remote archive parameters in the initialization parameter file, change the log transfer mode to LGWR SYNC AFIRM, that is, the lgwr process transmits redo logs synchronously.

Vim $ORACLE_HOME/dbs/initBJ.ora

Open the main library after it is shut down normally.

Shutdown immediate

Startup

(3) add standby logfilegroup to the standby database

First check the number, size and file location of the current log group

Select sequence#,group#,bytes/1024/1024 MB from v$log

Select member from v$logfile

Add 4 standby log groups to the repository, one more than the normal log group.

Alter database add standby logfile group 4'/ u02 size oradata size 50m

Alter database add standby logfile group 5'/ u02 size oradata size 50m

Alter database add standby logfile group 6'/ u02 size oradata size 50m

Alter database add standby logfile group 7'/ u02 size oradata size 50m

Check after the addition is completed, standby logfile group has been added

(4) switch the data protection mode to the highest available

Alter database set standby database to maximize availability

View the current master and slave database data protection mode, which is already the highest available mode.

Select database_role,protection_mode,protection_level from v$database

②, maximum available upgrade maximum protection (1) View current database protection mode: highest available mode

Select database_role,protection_mode,protection_level from v$database

(2) set the database protection mode to maximum protection

Alter database set standby database to maximize protection

Check the current protection mode of the database. It has been upgraded successfully: maximum protection mode

Select database_role,protection_mode,protection_level from v$database

③, maximum protection transferred to maximum available (downgraded)

Query current protection mode status: maximum protection mode

Set the protection mode to the highest available

Query again, protection_level has been successfully switched to: the highest available mode.

Select database_role,open_mode,protection_mode,protection_level from v$database

Alter database set standby database to maximize availability

Select database_role,open_mode,protection_mode,protection_level from v$database

④, highest available to highest performance (degraded)

Query current protected mode status: highest available mode

Set the protection mode to maximum performance

Query again, successfully switched to: highest performance mode.

Select database_role,protection_mode,protection_level from v$database

Alter database set standby database to maximize performance

Select database_role,protection_mode,protection_level from v$database

⑤, maximum performance upgrade maximum protection query current database protection mode: highest performance mode

Select database_role,open_mode,protection_mode,protection_level from v$database

Set the data protection mode to maximum protection. [ORA-01126]: database must be mounted in this instance and not open in any instance

It is required to operate in mounted state, and no instance can be in open state.

Alter database set standby database to maximize protection

Close the library normally and set the library to mount state

Shutdown immediate

Startup mount

In the mount state, set the database protection mode, you can switch, because it is in the mount state, the first protection mode displays unprotected.

Select database_role,protection_mode,protection_level from v$database

Alter database open

Check again after open status, successfully upgraded to maximum protection mode

⑥, maximum protection to maximum performance (degraded)

View current database protection mode: maximum protection mode

Set database protection mode to maximum performance

Check that it has been set up successfully and protection_level has become the highest performance mode.

Select database_role,protection_mode,protection_level from v$database

Alter database set standby database to maximize performance

Select database_role,protection_mode,protection_level from v$database

[summary of experiments] 1. The highest performance mode can use either ARCH or LGWR to deliver logs. When upgrading to a higher-level protection mode, you need to use LGWR, and you need to create standby log groups in the slave database.

2. When the three protection modes are switched, the maximum performance is upgraded to maximum protection mode (⑤). You need to restart the database to mounted state before you can operate. When other situations (①②③④⑥) switch with each other, it can be done directly.

According to the experimental conclusion, if you need to upgrade from the highest performance mode to the maximum protection mode in the production environment, you can first upgrade the highest available mode from the highest performance mode, and then upgrade from the highest available mode to the maximum protection mode.

As shown in the figure below, ①② is upgraded step by step instead of ⑤ to avoid the impact of restarting to mounted on the business in the production environment.

LV Xinghao

2014.8.9

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.