Let’s go over Oracle VIP.
At times, in the cluster configuration, SharePlex uses Oracle VIP instead of host clustering VIP.

Why is the Virtual IP necessary in Oracle RAC Environment?
That is because of client(Application) TCP timeouts.

Let’s have a look on diagram.

Here, VIP#1 is up in Node1 and VIP#2 is up in Node2. And, each VIP is mapped to the Ethernet device eth0.
Client is connected through the Listener.

In case of Node1 is shutdown for some reason, VIP#1 moved to Node2 and mapped to eth0:1.
Here, the thing is that VIP#1 is not linked to LISTENER, it is just UP in Node2.

At this point, Client got acknowledged that the connection to VIP#1 is disconnected.
Without this kind of acknowledgement from VIP#1, client should wait until TCP timeout value, it is normally 200s.
It means that for 200s, Client should be stuck.

However, now Client knows that connection is lost, so it can try to reconnect to available node, finally it is connected to VIP#2.
This is how Oracle VIP works and the reason we need it in the cluster environment.

This parameter forces SharePlex to use the defined hostname(IP) wherever it stays…
So, if the hostname can be resolved in the server, SharePlex will be fine.

Another thing is that: If there are multiple network interfaces in the system, normally, SharePlex will use just the "primary" network card to communicate to the target system unless the SP_SYS_HOST_NAME is set.
For this reason, if you want to use the other network interface instead of Primary network, you should set this parameter.

We can think of it as a global hostname.

For example,
Node1’s IP is 12, and Node2’s IP is 13. Global VIP is 15 and its hostname is PRINCE.

If we set SP_SYS_HOST_NAME=PRINCE,
On Node1, SharePlex is mapped with PRINCE, there is no issue to start SharePlex in Node1.
Does it make sense?

Although the Global VIP moved to Node2, as the Global VIP hostname can be resolved in the Node2 and
SharePlex is mapped with PRINCE, there is no issue to start SharePlex in Node2 as well.

This is how the parameter works.

ASM is just a file system Oracle made.
Its configuration is very complex, but DBA is responsible for that and they do everything about ASM configuration.

For SharePlex, ASM is just one of the file systems.
The good thing is that ASM is a cluster file system, so customer can keep archived logs in ASM area, and SharePlex can access the archived log files from other servers easily.

On the normal file system, SharePlex just needs to get the redolog location and access it directly. That’s it.
But, we should consider more on ASM area.

ASM authentication

SharePlex should consider about ASM authentication.
Simply, there are 2 methods, one is OS authentication, and the other is TNS.

You can think of OS authentication as a BEQ method.
TNS connection is the same as database TNS connection.
OSASM group(normally asmdba group) provides OS authentication for the SYSASM system privilege
Members of the OSASM group are authorized to connect using the SYSASM privilege and have full access to Oracle ASM

The point is that to use OS authentication instead of TNS, SharePlex installation owner should be in OSASM group.
Otherwise, you will run into the issues in terms of access denied.

On the other hand, if you want to use TNS method for ASM connection, SharePlex owner doesn’t need to be in OSASM group.

Block level deaklock issue could happen when HCC is enabled.

Hybrid Columnar Compression(HCC) is the way Exadata compresses the data. HCC utilizes a combination of both row and columnar methods for storing data.
A logical construct, called the Compression Unit (CU), is used to store a set of compressed data.

HCC uses one lock per Compression Unit… instead of per row.
That is the reason we could see so many block contention in replication software.

As you can imagine, user could get endless deadlock issues even though they increased NI_TRANS & PCT_FREE a lot.
Actually, as this issue is not from ITL shortage issue.

As a workaround, you could disable HCC on the table in Oracle11g in case most operations were UPDATE and the UPDATE operation decompresses the existing data.
That is an expected behavior on HCC.
You should realize that you don’t need to keep the table with HCC for that reason.

Fortunately,
In Oracle 12c, user can choose a “Row level locking” instead of “Compression Unit Locking”

Query: How many rows are in the Oracle block

You may want to use the query below to check the degree of data contention.

SELECT dbms_rowid.rowid_block_number(rowid) blockno, count(*) cnt
FROM compress_test_table
GROUP BY dbms_rowid.rowid_block_number(rowid);

Apparently, a deadlock issue is related to resource contention between 2 actions.
Then, what could be the resources in this case?

At first, you can think of the real row-level contention.
Normally, it results from the abnormal Application design or replication product defects.

At this time, I would like to focus on other resource-related deadlock issues(block-level contention).

One of those reasons is ITL shortage issue

What is Interested Transaction List (ITL) ?
It is a simple data structure called "Interested Transaction List" (ITL), a list that maintains information on transaction.
The transaction identifier will be stored as an entry in the ITL in the header of the data block.
The ITL contains several placeholders (or slots) for transactions.
ITL slots are required for every transaction.

How many slots are typically available in ITL ?
During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL.
The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.
Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.

If there is available space in the block, the number of ITL should increase to the MAXTRANS. Right?
If that is the case, why the short of ITL issue happens?

Normally the speed of growing up is not fast enough to avoid ITL shortage issue.
Eventually, it ends up post slow issue OR blocking issues. To make it worse, deadlock issue could happen.

Ok. let's look at the diagram below for easy understanding about Oracle dead lock and ITL.

Initially, there are 3 SQL Threads. You can think of it as a Transaction. And, we have 2 data blocks. After the step #4, all ITL slots are consumed.
Eventually, when SQL Thread#2 is going to modify the block 2, it must wait until ITL is available to acquire.
SQL Thread#3 will encounter ORA-60 error as it is a holder of block 2 and waiter of block 1.

This explains that we could have a deadlock issue because there is no available ITL slot.
It is different from the row level locking.

What should I do to avoid this issue?

At least, for heavy tables, please keep higher value of INI_TRANS at the first stage of your implementation. Apparently, that would save your day. If you couldn't reorganize all of tables and indexes, please make sure that you should do for the unique index which should be the first place you consider to change(It has a higher chance of much more contention.).
 

Then, How to increase INI_TRANS?

There are 2 ways to accomplish it.
You can change the value by running ALTER command, but only newly inserted rows are affected.
The other method is Reorganization of TALBLE or INDEX, in this case all rows are affected with new INIT_TRANS value.

What about SHAREPLEX_TRANS internal table in SharePlex?
This table saves transaction information of each session, and usually there are lots of block contention.
So, SharePlex development specified the INI_TRANS value with 20 by default.