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

Oracle dataguard redo network best practices (simplified translation)

Benefits of oracle dataguard:

1 has the least impact on system performance

There are two most available architecture (MAA) scenario configurations, and when sufficient bandwidth is available, the following conclusions are drawn:

1 DG in New York and Montreal (300mil distance, round trip latency of 10MS), using real-time mode, can achieve 5% performance impact on production system and zero data loss at redo 4MB/s generation rate

2 between Boston and London (3300 miles, 100MS round trip latency), using asynchronous mode, under the log generation rate of 20MB/s, the impact on the system can be less than 5%.

2 highest-level data protection mode:

1 synchronous mode, 0 data lost, the distance between the two is recommended to be about 100 kilometers

2 Asynchronous transfer mode, suitable for long-distance, high-delay environment, for example: from Hong Kong to Singapore, more than 1600 miles, there is 50MS delay, 2MB/s log generation rate, can accept data loss environment within 3 seconds.

3 enhanced archive log transfer: can be accelerated when resynchronization is needed in the event of a network or standby system failure. For example, 1GB log files need to be transferred by the archiving process, which can reduce the transfer time to 55%. In the process of fast resynchronization, critical data is in a protected mode.

3 fast switchover/failover, second switching

Best practices for REDO delivery

DG provides three modes of transmission: arch,lgwr async,lgwr sync, which are described below for network requirements and optimal configuration.

1 bandwidth selection

According to the peak period of awr reports:3MB/s, 3*8*1024*1024/1000/1000=25.2Mbps is required. Generally speaking, 53KM increases the latency of 1ms, while taking into account network repeaters, system performance, and network-owned packet traffic. To test the latency of RTT, you can trace it with traceroute

2Protective mode setting of DATAGuard

If you use LGWR SYNC synchronization mode, if the network is not sufficient to handle the amount of redo generated, it will affect the performance of the production machine; on the contrary, if you use LGWR ASYNC mode or online redolog transmission, it will exceed the network traffic during the peak period of redo generation without affecting the production system. Using ARCH to transfer local archive files also has no impact on the production system (but the risk of data loss is greatly increased compared to lgwr async), so once the mode of transfer between networks is determined, choose the following best practices:

2.1 ARCH transfer mode

Increase the number of ARCN processes, starting from 2 to control the maximum number of archiving processes; in versions later than oracle 10.2, this value will be up to 30, and the previous version will be up to 10 archive gap processes to speed up processing. When the host side sets the MAX_CONNECTIONS parameter, a large number of ARCn processes can speed up parallel processing.

Note:

A) log_archive_max_Processes will compete with other programs for the same network resources. It is recommended to set the optimal log_archive_max_processes according to the actual network and ARCHIVE GAP scenarios.

b)

The source max_connections setting is greater than or equal to 2, which reduces the transmission time of archive log, up to a maximum of 5

2.2 lgwr async transfer mode

Starting with oracle 10.2, the production library needs to ensure that there is sufficient bandwidth for LNS to process online transaction logs. Additional readings are measured in the performance test

2.3 LGWR SYNC Redo transmission

Set the NET_TIMEOUT property (how many seconds does the network service of the production library wait for LGWR requests to reduce the impact of network packet loss on the production library

In oracle 10.2, the NET_ timeout value is 180 seconds; it is officially recommended that the minimum value is 10 seconds, of course, you can set it to a minimum value of 1 second, but if the network is unstable, the delay will lead to constant switching of data protection mode.

Using lgwr sync mode, the local and remote databases are confirmed when the transaction commits; for two commit methods: commit wait&commit nowait: commit nowait mode can only return the application results without confirming whether the redo is written to disk; therefore, commit nowait can improve the response time of the application or transaction compared with the default wait value.

In addition, pay attention to:

1 configure a single 1MB with full write requests. For details, please see Best practices for creating a low Cost Storage Grid for oracle Databases

2 standby redlog on the fast disk

Do not have more than one standby redolog in a group. If there are more than one, additional write requests will be added.

Network best practices

Note: all the following settings are for Linux, and other platform settings can be referenced by yourself.

1 the bandwidth between master and standby must be sufficient

2 configure RECV_BUF_SIZE,SEND_BUF_SIZE equal to 3 times BDP (bandwidth delay product, propagation delay * network bandwidth), which can increase the bandwidth traffic of the network

Translator's note:

RECV_BUF_SIZE: in the TCP/IP/SSL protocol, specify the accept buffer of the session in bytes

Configuration: sqlnet.ora

Net_service_name= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=tcp) (HOST=sales1-server) (PORT=1521) (RECV_BUF_SIZE=11784)) (ADDRESS= (PROTOCOL=tcp) (HOST=sales2-server) (PORT=1521) (RECV_BUF_SIZE=11784)) (CONNECT_DATA= (SERVICE_NAME=sales.us.example.com)

SEND_BUF_SIZE is similar

Reference: http://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF274

3 configure SDU (Session data unit) = 32767

(4) increase the queue size of network devices. For example, linux adds TXQUEUELENGTH,NET_DEV_MAX_BACKLOG

TXQUEUELENGTH can be equipped with ifconfig eth2 TXQUEUELENGTH 5000 when linux configures the network card with ifconfig.

Set it at startup

Vi / etc/rc.local/sbin/ifconfig eth0 txqueuelen 5000

NET_DEV_MAX_BACKLOG belongs to kernel parameters.

Vi / etc/sysctl.confnet.core.netdev_max_backlog = 32768

5 configure orcle Net TCP_NODELAY=yes

Oracle SDU (session data unit) size description

When data is transmitted over the network, the network component of oracle buffers SDU. When large or continuous data needs to be transferred, increasing the SDU buffer can improve the utilization of the network.

Configuration of SDU

Vi sqlnet.oradefault_sdu_size=32767 can also configure tnsnames.ora: sales.us.acme.com= (DESCRIPTION= (SDU=32767) (ADDRESS= (PROTOCOL=tcp) (HOST=sales-server) (PORT=1521)) (CONNECT_DATA= (SID=sales.us.acme.com)

Configure listener.ora

SID_LIST_listener_name= (SID_LIST= (SID_DESC= (SDU=32767) (GLOBAL_DBNAME=sales.us.acme.com) (SID_NAME=sales) (ORACLE_HOME=/usr/oracle)

TCP socket buffer size

The TCP socket buffer controls the amount of network bandwidth available and does not take into account the actual bandwidth available in the network. When the network delay is relatively high, the larger socket buffer can make more efficient use of network bandwidth.

BDP size calculation:

BDP= 1000 Mbps * 25msec (.025 sec)

1000000000 * .025

25000000 Megabits / 8 = 3125000 bytes

Socket buffer = 3*BDP

Socket buffer size = 3 * bandwidth * delay

= 3125000 * 3

= 9375000 bytes

The socket buffer is controlled by the application, here is the network service control of oracle; edit sqlnet.ora

RECV_BUF_SIZE=9375000

SEND_BUF_SIZE=9375000

Or configure the service name:

Vi tnsnames.orastandby = (DESCRIPTION= (SEND_BUF_SIZE=9375000) (RECV_BUF_SIZE=9375000) (ADDRESS= (PROTOCOL=tcp) (HOST=hr1-server) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=standby)

Configure the listening file listener.ora

LISTENER= (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=sales-server) (PORT=1521 (SEND_BUF_SIZE=9375000) (RECV_BUF_SIZE=9375000)

It's not over yet.

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.