Oracle Dataguard Network Best Practices One

Oracle Dataguard Redo Network Best practices (simplified translation)

Oracle Dataguard Benefits:

1 minimal impact on system performance

Here are two maximum available architecture (MAA) scenario configurations, with sufficient bandwidth to draw the following conclusions:

1 DG in New York and Montreal (300 miles away, 10MS round trip delay), using real-time mode, at redo 4mb/s generation rate, can be achieved on the production system 5% performance impact and 0 data loss;

2 between Boston and London (3300-mile, 100MS round-trip delay), using asynchronous mode, at 20MB/S log generation rate, can be achieved under the system 5% of the impact;

2 highest level data protection mode:

1 synchronous mode, 0 data loss, the distance between the two is recommended in about hundred kilometers;

2 Asynchronous transfer mode, for long-distance, high-latency environments, such as: From Hong Kong to Singapore, more than 1600 miles, there is a delay of 50MS, 2mb/s log generation rate, can accept the data loss environment within 3s.

3 Enhanced Archive Log transfer: When a network or standby system failure occurs, it can be accelerated when resynchronization is required. For example: 1GB log files need to be transferred with the archive process, can reduce the transmission time to 55%, in the process of fast resynchronization, the key data is in a protection mode;

3 Fast Switchover/failover, second level switching

Redo Transfer Best Practices

DG offers three modes of transmission: ARCH,LGWR ASYNC,LGWR Sync, described below for network requirements and optimal configuration.

1 Bandwidth Selection

According to the awr reports:3mb/s at peak times, the need for 3*8*1024*1024/1000/1000=25.2mbps, usually 53KM increases 1ms latency, but also to consider the network repeater, system performance, network-owned packet traffic. In order to test the delay of the RTT, you can trace it with traceroute;

2 Dataguard Protection Mode setting

If you use LGWR Sync Sync mode, if the network is not enough to handle the redo generation, it will affect the performance of the production machine, conversely, in LGWR async mode, or using the online Redolog transmission, the peak of redo generation exceeds the network traffic but does not affect the production system. Using Arch to transfer local archives does not have an impact on the production system (however, the risk of data loss will increase significantly with LGWR async ratio) so once the network transfer mode is determined, choose the following best practices:

2.1 Arch Transfer Mode

Increase the number of ARCN processes, the initial number is 2;log_archive_max_processes control the maximum number of archive processes; In Oracle 10.2 onwards, this value is up to 30, with the previous version having a maximum of 10; ARCN process can speed up the processing of archive gap. When the host side set the max_connections parameters, a large number of ARCN processes can speed up parallel processing,

Attention:

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 based on the actual network and archive gap scene;

b

The source-side max_connections setting is greater than or equal to 2, which reduces the transfer time of archive log, which is a maximum of 5;

2.2 LGWR Async transfer Mode ;

Starting with Oracle 10.2, the production library needs to ensure sufficient I/O bandwidth for the LNS to process the online transaction logs. The additional Reading quantity is measured in the performance test;

2.3 LGWR SYNC Redo Transfer

Set the net_timeout property (the number of seconds that the network service of the production library waits for the response time of the LGWR request to reduce the impact of network drops on the production library;

In Oracle 10.2, the Net_timeout value is 180 seconds; The official recommendation of this value is a minimum value of 10 seconds, of course you can set the minimum value of 1 seconds, but if the network instability delay will cause the data protection mode of non-stop switching;

Using LGWR sync mode, the transaction commits the local and remote databases, and for both commits: Commit Wait&commit Nowait:commit The NoWait method can only be returned to the application result without confirming that the redo is written to disk, so the commit nowait can improve the response time of the application or transaction compared to the default wait value.

In addition to note:

1 Configuring a single 1MB I/O write full write request, in detail in see Best practices for creating a low cost Storage Grid for Oracle Databases;

2 standby Redlog on the fast disk;

3 do not have a group of multiple standby redolog, if more than one, will add additional write requests;

Network Best Practices

Description: All of the following settings are for Linux, other platform settings are self-referencing

1 The bandwidth between the primary and standby must be sufficient

2 configuration recv_buf_size,send_buf_size 3 times times the BDP (bandwidth delay product, propagation delay * network bandwidth), this can increase the bandwidth of the network traffic;

Translator Note:

Recv_buf_size: In the TCP/IP/SSL protocol, specify the session's accept buffer in bytes

Configuration: Sqlnet.ora

Net_service_name= (description= (address_list= (address=) (PROTOCOL=TCP) (Host=sales1-server) (PORT=1521 _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 similar;

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

3 Configuration SDU (Session data unit) =32767

4 increase the queue size of the network device. such as Linux add txqueuelength,net_dev_max_backlog

Txqueuelength when configuring the NIC with Ifconfig in Linux: ifconfig eth1 txqueuelength 5000,

Set at startup

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

Net_dev_max_backlog belongs to the kernel parameter,

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

5 Configuring Orcle Net Tcp_nodelay=yes

Oracle SDU (Session data unit) Size description

When data is transferred over the network, Oracle's network components buffer SDU. When large or continuous data needs to be transmitted, increasing the SDU buffer can improve the utilization of the network.

Configuration of the SDU

VI sqlnet.oradefault_sdu_size=32767 in the configuration Tnsnames.ora can also: sales.us.acme.com= (description= (sdu=32767) (ADDRESS= (P ROTOCOL=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=sal ES) (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 that can be used in the network. When the network latency is high, the larger socket buffers can make more efficient use of the network bandwidth;

BDP Size measurement:

bdp= Mbps * 25MSEC (. 025 sec)

1,000,000,000 *. 025

25,000,000 megabits/8 = 3,125,000 bytes

Socket Zone Buffer =3*BDP

Socket buffer size = 3 * Bandwidth * delay

= 3,125,000 * 3

= 9,375,000 bytes

The socket buffer is controlled by the application, which 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)))

Configuring the Listener file Listener.ora

Listener= (description= (address= (protocol=tcp) (Host=sales-server) (port=1521 (send_buf_size=9375000) (RE cv_buf_size=9375000)))

Not finished ...

This article from "Snowhill" blog, reproduced please contact the author!

Oracle Dataguard Network Best Practices One