Creating XA Oracle Services

--Create the service with this command
srvctl add service -d -s -r -a

--Start the service
srvctl start service -d -s

--Configure the service to use DTP that will guarantee that all global transaction will stay on the same instance
begin
DBMS_SERVICE.MODIFY_SERVICE(
service_name=>'',
dtp => TRUE
);
end;
/


--Service_name to insert into your tnsnames.ora client file
SERV_XA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP_MACHINE_DB_SERVER)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = )
)
)


--If you reach ORA-12520 error and are using RAC Database then execute the following commands on one instance connected as SYSDBA

1-
sqlplus / as sysdba
SQL> alter system set local_listener='LISTENER_MIDDLE_BWMDPR1' scope=both sid='BWMDPR1'; --Use the same value that is already in use

System altered.

SQL> alter system set local_listener='LISTENER_MIDDLE_BWMDPR2' scope=both sid='BWMDPR2'; --Use the same value that is already in use

System altered.

SQL>alter system register;

System altered.

2-Stop and Start listener (reload didn't help for me!)