Thursday, January 30, 2014

EBS on Oracle RAC / Exadata LOAD BALANCE connections

As you know, techonology used in EBS are actually a collection.. We have Forms, Reports, Concurrent Managers, Apache/Web Server, External Reporting tools etc.
These different technologies --components are connecting to the EBS databases seperately.
These situation brings us an opportunity to isolate database nodes according to the Application Components.
That is, if we want, we can configure these components(Apache, Conc Managers, Forms, Reports and or external tools) to use only Database Node 1 for its sessions or we can configure Apache to use Database Node 1 and Node 2 load balanced..

In a Rac Environment we usually prefer to use a load balanced connection style for EBS Application Tier components. Of course, sometimes in some environments; we feel the need to isolate database nodes according to the components, workload and etc..
In short, to make the long story short, In this post I will describe how to make these configurations in EBS.

Briefly, we have a file called context file in EBS and this file feeds the Autoconfig.. That is, Autoconfig reads this file to make the necessary configurations in EBS components.
So to able to make the configurations described above, we need to change the following lines in context file and run autoconfig.

<TWO_TASK oa_var="s_tools_twotask" osd="unix">EXADEV</TWO_TASK>
<TOOLS_DB_TWOTASK oa_var="s_tools_db_twotask">EXADEV_806_BALANCE</TOOLS_DB_TWOTASK>
<CP_TWOTASK oa_var="s_cp_twotask">EXADEV</CP_TWOTASK>
<TWO_TASK oa_var="s_weboh_twotask" osd="unix">EXADEV</TWO_TASK>
<WEBOH_DB_TWOTASK oa_var="s_weboh_db_twotask">EXADEV_806_BALANCE</WEBOH_DB_TWOTASK>


We change the values stored in xml tags according to tnsnames.ora stored in relevant Oracle Homes ( I mean IAS ORACLE_HOME and 8.0.6 ORACLE_HOME for 11i and 10.1.2 ORACLE_HOME and 10.1.3 ORACLE_HOME for R12)

Normally, when we have 2 nodes database in EBS we have the following tns entries in our Application tier's ORACLE HOMES ..
Lets suppose our database Service Name is EXADEV and also suppose we dont have any scan listeners ( as you know they are not supported in EBS 11i)
EXADEV=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV1)
)
)
EXADEV1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV1)
)
)
EXADEV1_FO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV1)
)
)
EXADEV2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM02)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV2)
)
)
EXADEV2_FO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM02)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV2)
)
)
EXADEV_806_BALANCE=
(DESCRIPTION_LIST=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM02)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV2)
)
)
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV1)
)
)
)
EXADEV_FO=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
(INSTANCE_NAME=EXADEV1)
)
)
EXADEV_BALANCE=
(DESCRIPTION=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM02)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
)
(CONNECT_DATA=
(SERVICE_NAME=EXADEV)
)
)

So by looking to the tns entries above, we can change the context file and run the autoconfig to make the configurations according to our need..
For example;
According to the tns entries above, If we want an application tier component to connect to our cluster in a load balanced manner, we use EXADEV_BALANCE tns entry or If we have a compontent to use only the Database Node 2, we use EXADEV2 tns_entry and so on.

Hope this helps..

Of couse, we have parallel concurrent processing and Application node load balancing but these subject will be described in forthcoming blog posts of mine..

2 comments :

  1. HI Erman,

    as part of the deployment, it seems that Oracle choose the first instance of the rac as
    the instance for DB_Name service (SERVICE_NAME=EXADEV and INSTANCE_NAME=EXADEV1 in your example). How do I change that? In my test, I cleaned up fnd_nodes table, and ran autoconfig in all db hosts, starting with instance 2, instance 2 was the registered as DB_NAME. If I want to change this manually, how do I do?
    In case of this instance is down, how do I get another instance to be the instance for DB_NAME service?

    EXADEV=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=EXADBADM01)(PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=EXADEV)
    (INSTANCE_NAME=EXADEV1)
    )

    ReplyDelete
  2. Hi Henrique,

    If you want to change that manually, then you will have to edit the tnsnames.ora of the application tier via an text editor( for ex: vi) However, your modification will be overwritten in the next autoconfig run.

    The connections from Apps to DB is derived by the two_task variables that you set in your apps tier context file (autoconfig related) and the jdbc_url that is set in the same place.
    So, normally, you should see *_BALANCE there. This *_BALANCE tns entry is configured for load balanced and fail over connections.

    so check your apps tier context file and see what are the values you set for the two_Task variables. After that, check your tnsnames.ora to be sure that it is aligned with your goal. Check your jdbc_url as well..

    ReplyDelete