Figuring out ORA-24777: reasons for not allowing the use of non-portable database links, and workarounds

Recently a colleague consulted, said there is a SQL containing dblink, in Plsql This SQL can execute normally, and in the Java background call execution this SQL execution to error:
ORA-24777: The use of non-portable database links is not allowed.

After you have asked your colleague to follow the procedure, you find that you are querying a remote library table through Dblink and using an XA distributed transaction database connection.
I used to know that XA had a lot of pits and walked around.
But this time since met, ready to solve the convenience, there is a unified solution.
To check the next Metalink, just one article mentioned the problem: ORA-24777 reported when using a database link from within an XA coordinated transaction ( document ID 1506756.1)
This could be a bug, or a pit of JDBC, and this problem may occur when you use JDBC and then call a distributed transaction that has dblink.

Metalink offers two solutions to solve ORA-24777:

1. Set the Oracle server mode to shared server;
ALTER SYSTEM SET dispatchers= "(protocol=tcp)" Scope=both;
ALTER SYSTEM SET shared_servers = Scope=both;

2. Set the Dblink connection to shared mode.

The first solution has a greater impact and can lead to other problems. The second kind of low cost has little effect on the system itself. It is therefore recommended to choose Scenario Two.

Common types of Dblink are easy to create, and here's how to create a shared dblink
Let's look at the syntax of CREATE DATABASE link first:

CREATE [shared][public] Database link link_name

[CONNECT to [user][current_user] identified by password]
[Authenticated by the user identified by password]
[USING ' connect_string ']

(1) Permissions: the account that created the database link must have the Create Database link or create public database link's system permissions, the account used to log on to the remote databases must have Create session permission.
A public database link is available to all users in the database, and a private link is available only to the user who created it, and cannot be used for authorization.
When the source side of the database global_name=true, the link name must be the global database name of the remote database Global_ Name is the same; otherwise, it can be named arbitrarily.
(3) Current_User: This option is used to create a global type of dblink. When there are multiple databases in a distributed system, if you want to use the same name in each database to access the database test, it is too cumbersome to create a db_link to the database test in each database, so now this option is available. Once created, all databases can be accessed using this db_link. To use this feature, you must have an Oracle NameServer or an Oracle directory server. And the parameters of the database test are global_names=true.
(4) ConnectString: connection string, which defines connection strings for remote databases in Tnsnames.ora, and can be specified directly when creating Dblink. Similar to Ezconnect.
(5) Username, password: user name of the remote database, password. If you do not specify, log on to the remote database with the current user name and password, and when you create a connected user type of Dblink, you need the user name password for both sides of the database to be consistent if you use data dictionary validation.

For shared Dblink
It builds a shared database connection, and you specifythe DB link_authentication.
Database link using shared mode is the number of connections to the remote database that are restricted by the databases so that excessive connections are too stressful for the remote database.
When using the shared database link, the connection to the database link will be disconnected from the local connection after the connection is made to prevent the unauthorized session from using this link to create a shared database You must specify database link_authentication when you link.
(from Oracle document:a shared database link is a link between a local server process and the remote database. The link is a shared because multiple client processes can use the same link simultaneously.)

Create a shard Dblink as follows:
CREATE SHARED Public Database link
Sales.us.americas.acme_auto.com
CONNECT to Scott identified
by Tiger authenticated by
Anupam identified by Bhide USING ' sales ';

which
Connects to Database:sales using NET service name Sales
Connects As:scott using password tiger, authenticated as Anupam using password Bhide
Using the authenticated by clause is somewhat disturbing, but it is necessary to implement a shared link in a way that is safe. The username and password Anupam/bhide In this example must be valid on the remote system.

Let me give you an example:
Example 1
CREATE SHARED Database_link Db_link_name
CONNECT to remote_user_name_1 identified by User_password_1
Authenticated by remote_user_name_2 identified by User_password_2
USING ' 192.168.0.157:1521/sid '
Example 2
CREATE SHARED Database_link Db_link_name
Authenticated by remote_user_name_2 identified by User_password_2
USING ' 192.168.0.157:1521/sid '
Note that two remote_user_name appear in Example 1, respectively, as remote_user_name_1\remote_user_name_2, Because the shared keyword must be specified to require authentication, connect to can be omitted (as in Example 2).

After the shared database link is created, JDBC executes this SQL error problem resolution.

But another problem: each dblink has a connection limit (default is 4), if the connection limit is exceeded, an error will be made: Ora-02020:too many database links in use

1. View the relevant initialization parameters:
Sql> Show Parameters Open_links;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Open_links Integer 4
Open_links_per_instance Integer 4

Two meanings:
open_links_per_instance Specifies the maximum number of migratable open connections globally for each database INSTANCE. XA transactions use migratable open connections so, the connections be cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection's the same as the user who owns The transaction.

Open_links_per_instance is different from Open_links, which indicates the number of connections from a session. The Open_links parameter is not a applicable to XA applications.

Chinese explanation:
Open_links:oracle's official explanation is the maximum number of Dblink allowed per session;
Open_links_per_instance This parameter refers to the maximum number of Dblink allowed per instance.

2. Modify Open_links
Alter system set open_links=255 Scope=spfile;
Alter system set open_links_per_instance=255 Scope=spfile;

3. Restart the database after the change is in effect

Figuring out ORA-24777: reasons for not allowing the use of non-portable database links, and workarounds