optimizing Data Guard Configuration – II


Introduction

In the previous part https://rajat1205sharma.wordpress.com/2015/09/12/optimizing-data-guard-configuration-i/  we have seen optimizing Data Guard configuration for few attributes and parameters for physical standby database, Now we are going to cover few more attributes which are pending, They are Redo compression, Delaying the redo to standby/remote destinations and Finally we will cover with the best practices to optimize the TCP send and receive socket buffer sizes.

Optimizing Primary for Physical Standby database

Optimizing the Data Guard configuration is in broad range based on the features used and licensed. In the second part we are going to discuss with few advanced level of optimizing and tuning part. They are Redo Compression, Delaying the redo data to send to remote destinations, Optimizing and tuning session data unit while transmitting. We will discuss in detail further about each feature and how to handle them with the demo.

Redo Compression

Oracle introduced Advanced compression from Oracle 11g with a set of compression which can be used in various options of database, They are Table compression, Compression for Secure files for deduplication and compression, Compression of network traffic, Compression of database backups with RMAN, Logical backups with expdp/impdp and also for Data Guard redo transport.

Before introduction of Advanced Compression there were various third party software’s were available such as WAN accelerators and so on, That was past. Now Oracle itself introduced Advanced compression and this article section we are going to see how Data Guard redo log network transport works with compression and the configuration. But please note that Advanced compression is an additional license apart from Oracle 11g Enterprise Edition, So you must purchase before use of it.

Redo compression for Data Guard can be used for Asynchronous or Synchronous transport. If existing Data Guard configuration is with Maximum protection or Maximum availability then compression is not required because you have configured synchronous transport based on the Network bandwidth which is more than sufficient to provide availability and protection, in those cases really compression for Redo is not required. Redo compression is much demanding and required only if there is insufficient bandwidth, High latency with the standby system communication.

When redo compression introduced in 11gR1 with Advanced compression, the configuration to enable compression we have two step and now from 11gR2 it is more simplified with single attribute, we will see how to enable Compression for redo. Enabling and disabling redo compression can be handled either by using SQL or Broker

Compressing “Redo” Test Case

With the 11gR1, we need to set the initialization parameter and enable compression attribute in the log_archive_dest_n.

SQL> alter system set log_archive_dest_2=’india async compression=enable db_unique_name=india’;

SQL> alter system set _redo_transport_compress_all=true

Please note that to change the _redo_transport_compress_all to TRUE or FALSE, you must bounce the database for the changes in SPFILE. whereas, from 11gR2 no more bounce of production database and much simplified with a compression=enable attribute with the log_archive_dest_n.

SQL> select banner from v$version;

BANNER

——————————————————————————–

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production

PL/SQL Release 12.1.0.1.0 – Production

CORE 12.1.0.1.0 Production

TNS for Linux: Version 12.1.0.1.0 – Production

NLSRTL Version 12.1.0.1.0 – Production

We are testing with the 12c Database and we can see below output as we are ready to use “Advanced Compression” Feature.

SQL> select parameter, value from v$option where parameter=’Advanced Compression’;

PARAMETER VALUE

—————————— —————

Advanced Compression TRUE

SQL> select dest_id,compression from v$archive_dest where dest_id=2;

DEST_ID COMPRES

———- ——-

2 DISABLE

By default the redo compression will be disabled and we have to enable manually as it’s an additional feature of Advanced Compression(ACO). If you are managing with Data Guard broker then perform updates to the configuration by DGMGRL utility.

DGMGRL> show database india ‘RedoCompression’;

RedoCompression = ‘DISABLE’

DGMGRL> edit database india set property ‘RedoCompression’=enable;

Property “RedoCompression” updated

DGMGRL> show database india ‘RedoCompression’;

RedoCompression = ‘enable’

DGMGRL>

SQL> select dest_id,compression from v$archive_dest where dest_id=2;

DEST_ID COMPRES

———- ——-

2 ENABLE

SQL>

If you are not using Data Guard Broker then of course then we can perform same steps from SQL mode as below.

SQL> alter system set log_archive_dest_2=’india async compression=enable db_unique_name=india’;

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null and username

order by value

/

SID USERNAME REDO_SIZE

———- —————————— ———-

68 PUBLIC 1804

81 C##NASSYAM 191065256

SQL>

SQL> select program, machine from v$session where sid=68;

PROGRAM MACHINE

———————————– —————

oracle@CKPT-ORA-04 (TNS V1-V3) CKPT-ORA-04 <— Standby Host

Compression Ratio

Is it interesting to know how the compression works internally? If you don’t know then ACO uses the compression engine of level 1 with the gzip. We can estimate how much was compressed and how much if its uncompressed so on with this utility. First we need to perform gzip of the archive file and then use “–list” attribute which provides the “List compressed file contents” and hence we can estimate.

-bash-3.2$ rpm -qa |grep gzip

gzip-1.3.5-11.0.1.el5_4.1

-bash-3.2$

-bash-3.2$ gzip -1 o1_mf_1_320_byqrnr1t_.arc

-bash-3.2$

-bash-3.2$ gzip –list o1_mf_1_320_byqrnr1t_.arc.gz

compressed uncompressed ratio uncompressed_name

9172276 49912320 81.6% o1_mf_1_320_byqrnr1t_.arc

-bash-3.2$

Delaying the Redo

Delaying Redo in Data Guard? It looks very weird where the data protection and availability is so critical role in case of disaster recovery but still why such option in Data Guard? Well there are Pros and cons for every feature whatever available. If you go and see the second shade of the feature and you will definitely agree why this feature is good and what key role it plays.

To be frankly, The example here am giving from one of my customer and the database size is around 12TB and the standby is running with the delay and i have wondered why delay for such huge transnational and critical database? Let’s suppose if any user unfortunately or by mistake dropped the table while dropping another table or for any data corruption. Then the changes to the standby destination will not be applied so that still the data can be extracted/exported and then the table can be imported into production database back. If the data availability matters then the best architecture is have two standby database one run with Delay and other one for data protection.

Apart from that if unable to recover from standby database then we can consider the table was dropped from both primary and standby database. Hence it is best practice to configure Flashback on standby. So that while production is running we can perform flashback on standby and the required information can be recovered. There is one limitation, if you want to work delaying redo to application then the standby database should not run in Real-Time apply mode. Before changing the value of DelayMins, If we drop the table and the same will be replicated to standby and hence the table will be dropped from standby as well. Of course this is normal behavior.

Primary:

DGMGRL> show database india ‘DelayMins’

DelayMins = ‘0’

DGMGRL>

SQL> drop table rdgtst;

Table dropped.

SQL>

Standby

SQL> select database_role from v$database;

DATABASE_ROLE

—————-

PHYSICAL STANDBY

SQL> select count(*) from rdgtst;

select count(*) from rdgtst

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

After dropping table from primary database and then the table was no more exist in standby database as well, These all happen in seconds. Now we will see how the DelayMins actually works. We can add delay_mins attribute even in log_archive_dest_n or we can update the value from the Data Guard broker configuration.

DGMGRL> show database india ‘DelayMins’

DelayMins = ‘0’

DGMGRL> edit database india set property DelayMins=5;

Property “delaymins” updated

DGMGRL> show database india ‘DelayMins’

DelayMins = ‘5’

DGMGRL>

————-> Alert Log <———————

Sun Sep 06 14:42:25 2015

Archived Log entry 147 added for thread 1 sequence 327 ID 0xe2211cf8 dest 1:

Sun Sep 06 14:42:25 2015

ARC0: Archive log thread 1 sequence 327 available in 5 minute(s)

Sun Sep 06 14:42:29 2015

Media Recovery Delayed for 5 minute(s) (thread 1 sequence 327)

————-> Alert Log <———————

As soon as we updated DelayMins from Data Guard broker from the standby database alert log we can see that ” ARC0: Archive log thread 1 sequence 327 available in 5 minute(s)”, So the new change is now applicable and even though we did any changes they will be applied on standby after only 5 minutes. We will see how practically it works.

When you manage with Data Guard broker to update parameters, Internally Broker can initiate to stop and start MRP and it ensures standby to run non in Real-Time apply mode.

From the Primary, We will drop the table and will see how it works on standby database and look at the timestamp carefully.

SQL> select count(*) from opt_tab;

COUNT(*)

———-

6000000

19:41:16 SQL> drop table opt_tab;

Table dropped.

19:41:21 SQL> alter system switch logfile;

System altered.

19:41:38 SQL>

We have dropped the table and also performed log switch, now if we check(below) on standby the table and the data is still available. So there is no changes been applied to standby database. The last output if we see after 5 minutes the table is no more available.

19:39:10 SQL> select database_role from v$database;

DATABASE_ROLE

—————-

PHYSICAL STANDBY

19:39:49 SQL> select count(*) from opt_tab;

COUNT(*)

———-

6000000

19:47:23 SQL> select count(*) from opt_tab;

select count(*) from opt_tab

*

ERROR at line 1:

ORA-00942: table or view does not exist

19:44:50 SQL>

Optimizing and Tuning Network

So far we have seen optimizing attributes/parameters at database level, but it is also important to controlling, optimizing and tuning of the Network. In fact the DR is always geographically in different location to achieve disaster recovery for Primary database and there is necessity to have sufficient band width so that the every transaction should reach to standby database with in the acceptable time. So basically we have to configure socket buffer size and configure if the required bandwidth is not sufficient. To know the required bandwidth we can estimate using the Oracle recommended formula

Required network bandwidth = ((Redo rate bytes per sec. / 0.7) * 8) / 1,000,000 = bandwidth in Mbps.

Here the Redo rate can be gathered from the peak hours of AWR report and more or around the value of required network bandwidth we should have for only Data Guard redo shipping.

The rest topic is applicable if there is no sufficient bandwidth available and in that case Tuning of Network comes into the place. Tuning of network is not something critical to estimate, it is just predefined formulas from Oracle in order to configure the Oracle Net parameters SEND_BUF_SIZE, RECV_BUF_SIZE.

Estimate BDP
Bandwidth Delay Product = (network speed * Round Trip Time) / 8

2. Estimating Socket Buffer size

Buffer Size = 3* BDP (output of step 1)

3. Configure buffer size

Based on the socket buffer size value, for example 26214400 bytes which is equivalent to 25MB. The same value we need to update in network files. The parameters can be configured in sqlnet.ora file or directly in the Oracle Net Service.

-bash-3.2$ cat sqlnet.ora

SEND_BUF_SIZE=26214400

RECV_BUF_SIZE=26214400

-bash-3.2$

—————-> Oracle Net Service <————

INDIA =

(DESCRIPTION =

SEND_BUF_SIZE=26214400

RECV_BUF_SIZE=26214400

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.130)(PORT = 1522))

)

(CONNECT_DATA =

(SERVICE_NAME = INDIA)

)

)

If we are replicating the data using materialized views of database links and if the large amount of redo is transmitting then we can consider to increase Session Data Unit(SDU), By increasing the value of SDU and greatly benefited in terms of performance and network utilization. This parameter we can configure again in sqlnet.ora file parameter DEFAULT_SDU_SIZE with starting range from 512 bytes until 32676 bytes as required. This parameter directly we can configure with Listener like below example.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU = 32767)

(GLOBAL_DBNAME = mcdb)

(SID_NAME = mcdb)

(ORACLE_HOME = /u02/app/oracle/product/12.1.0.1/db_1)

)

)

Conclusion

We’ve seen few attributes and parameters to optimize in terms of Data Guard with the first Part, The rest and few of the attributes to delay the redo and usage of Advanced compression so that how compression works with faster redo ship instead of traditional redo transport with examples and finally we have seen Tuning the network by configuring buffer sizes in network files.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s