Hi all,
Hope you are doing good.
The Summary
I’m working on a project to migrate some databases running on On-Premises IBM AIX, 19.28, RAC with 3 nodes to Autonomous Serverless.
As the target is Autonomous Serverless, the only migration method available is the logical migration.
We are performing an online logical migration, leveraging the use of:
- CPAT to perform the evaluation of the source DB and make it ready for the migration
- ZDM to orchestrate the migration
- OGG to keep source (On-Premises) and target (Autonomous) synchronized
The DB to be migrated has two database links that point to other databases located On-Premises and that will not be migrated for now. So, apart from the migration using ZDM, we will need to create the two database links on Autonomous to point to the remote DBs.
This post is focusing on the DB Link creation and the issue we got.
The Requirement
We must create two database links that will point to two PDBs, both of them running on the same hosts On-Premises.
In the current DB running On-Premises, the client is using Public Database Links. I already let them know that on Autonomous we can’t have Public DB Links.
The Pre-Reqs
- To create a database link, we need to create the credential containing the user and the password that will be used for the DB Link communication in the target DB (On-Premises).
- In our environment, we used a Private Endpoint, as the remote DBs are not publicly accessible.
- We also need Autonomous DB to be able to resolve the hostnames located On-Premises. The network team created DNS Private Resolvers for that.
- Of course, we will need the firewall team to create the firewall rules allowing the connection from Autonomous to the On-Premises DB servers over the SQL*Net port.
- The parameter SEC_CASE_SENSITIVE_LOGON must be set to TRUE in the On-Premises DBs.
- The user to create the credentials and the database links on Autonomous must have the following privileges:
- Execute on DBMS_CLOUD
- Execute on DBMS_CLOUD_ADMIN
- Execute on DBMS_NETWORK_ACL_ADMIN
- Create Database Link
Let’s assume that we need to connect to two PDBs on the same hosts:
- PDB1
- PDB2
Let’s call the hosts as:
- dbnode01.mydomain.com
- dbnode02.mydomain.com
- dbnode03.mydomain.com
The VIPs are:
- dbnode01-vip.mydomain.com
- dbnode02-vip.mydomain.com
- dbnode03-vip.mydomain.com
As of now, I will not be talking about SCAN, as we are still waiting for the firewall rules.
Let’s assume that we have two users:
- USERPDB1, which is on PDB1
- USERPDB2, which is on PDB2
On the Autonomous side, let’s assume that the user who will be creating the DB Links and the Credentials is USERATP.
The Hands-On
Connected with ADMIN on Autonomous, let’s grant the required privileges to USERATP:
grant execute on dbms_cloud to USERATP; Grant succeeded. grant execute on dbms_cloud_admin to USERATP; Grant succeeded. grant execute on DBMS_NETWORK_ACL_ADMIN to USERATP; Grant succeeded. grant create database link to USERATP; Grant succeeded.
Now, connected with USERATP on Autonomous, let’s create the credentials for both remote users:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'PDB1_USERPDB1',
username => 'USERPDB1',
password => 'USERPDB1'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'PDB2_USERPDB2',
username => 'USERPDB2',
password => 'USERPDB2'
);
END;
/
PL/SQL procedure successfully completed.Let us now create the Database Links:
BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'PDB1LINK',
rac_hostnames => '["dbnode01-vip.mydomain.com",
"dbnode02-vip.mydomain.com",
"dbnode03-vip.mydomain.com"]',
port => '1521',
service_name => 'PDB1',
ssl_server_cert_dn => NULL,
credential_name => 'PDB1_USERPDB1',
directory_name => NULL,
private_target => TRUE);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'PDB2LINK',
rac_hostnames => '["dbnode01-vip.mydomain.com",
"dbnode02-vip.mydomain.com",
"dbnode03-vip.mydomain.com"]',
port => '1521',
service_name => 'PDB2',
ssl_server_cert_dn => NULL,
credential_name => 'PDB2_USERPDB2',
directory_name => NULL,
private_target => TRUE);
END;
/
PL/SQL procedure successfully completed.We also need to make sure Autonomous Database will force all outbound connections to go through the private endpoint instead of the public network (if a public network exists).
For this, we change a database property called ROUTE_OUTBOUND_CONNECTIONS:
ALTER DATABASE PROPERTY SET ROUTE_OUTBOUND_CONNECTIONS = 'PRIVATE_ENDPOINT'; Database altered.
Now, we need to make sure that the user USERATP can resolve (DNS resolution) and connect (open a TCP connection) to the remote DB servers.
For this, we use the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure.
Even after the database links are created successfully, Autonomous Database does not automatically allow outbound network connections from a database user. Autonomous uses Network Access Control Lists (ACLs) to explicitly control:
- which users can access the network
- which hosts they can connect to
- what type of access is allowed
By default, a user cannot resolve or connect to external hosts, including On-Premises database servers.
Let’s go:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'dbnode01-vip.mydomain.com',
ace => xs$ace_type(
privilege_list => xs$name_list('connect', 'resolve'),
principal_name => 'USERTATP',
principal_type => xs_acl.ptype_db
)
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'dbnode02-vip.mydomain.com',
ace => xs$ace_type(
privilege_list => xs$name_list('connect', 'resolve'),
principal_name => 'USERTATP',
principal_type => xs_acl.ptype_db
)
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'dbnode03-vip.mydomain.com',
ace => xs$ace_type(
privilege_list => xs$name_list('connect', 'resolve'),
principal_name => 'USERTATP',
principal_type => xs_acl.ptype_db
)
);
END;
/
PL/SQL procedure successfully completed.Perfect.
Now, let’s test our DB Links:
select sysdate from dual@PDB1LINK; SYSDATE --------- 16-DEC-25
Let’s test the other DB Link:
select sysdate from dual@PDB2LINK;
select sysdate from dual@PDB2LINK
*
ERROR at line 1:
ORA-28040: No matching authentication protocolHummm, that’s weird!
There is a MOS note mentioning this error:
This note is not useful for us, because we confirmed that both On-Premises DBs have the parameter SEC_CASE_SENSITIVE_LOGON=TRUE.
Then we did a quick check on both PDBs for both users.
On PDB1:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME='USERPDB1'; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- USERPDB1 10G 11G 12C
All good, right?
Let’s now check the PDB2:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME='USERPDB2'; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- USERPDB2 10G
That’s it!
The password for USERPDB2 is still case insensitive, as the parameter SEC_CASE_SENSITIVE_LOGON was introduced in Oracle Database 11g Release 1. Why only 10G is exhibited? Because since this user was created, its password never changed. 🙂
I asked the client to change the password (keeping the same one):
alter user USERPDB2 identified by USERPDB2; User altered.
Verifying again the PASSWORD_VERSIONS:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME='USERPDB2'; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- USERPDB2 10G 11G 12C
Great!
Let’s test again our DB Link:
select sysdate from dual@PDB2LINK; SYSDATE --------- 16-DEC-25
Working fine!
Note: As the password was changed and now is in upper case, the client needs to make sure that this is correct for their applications; otherwise, they will face logon issues 🙂
That’s it.
Hope it helps!
Peace.
Vinicius
Useful doc
Create Database Links to Oracle Databases on a Private Endpoint without a Wallet
