Using database links with ADB-S without wallet

In the previous post, we saw about how to setup a database link connecting two different ADB-S accessible over the public endpoint using wallet in place. In this blogpost we will see about how to establish database link connecting two different ADB-S accessible over the public endpoint without wallet in place.
 
On the target ADB details page, under Network, click Edit in the Mutual TLS (mTLS) authentication field & change the value to allow TLS Authentication by deselecting Require mutual TLS (mTLS) authentication and click update, the ADB lifecycle state changes to updating and post that mutual TLS (mTLS) authentication field changes to show Not required. 

 

To create database link to a public target, the target database must be accessible, some database including ADB limit access (using ACL), so make sure to enable target database to allow access from source database using database link, if we limit access with ACL, then make sure to find the outbound IP address of source database and allow that IP address to connect to your target database. By adding outbound IP address of source database to ACL of the target database.
 
On the source database
 
demo-user@ATP19C> select jt.*
  2  from v$pdbs, json_table( cloud_identity,'$.OUTBOUND_IP_ADDRESS[*]'
  3      columns( outbound_ips varchar2(20) path '$') ) jt;
 
OUTBOUND_IPS
----------------
150.136.133.92
 
On the target Autonomous database 
 

 
Then create credentials to access the target database, the username and the password for the dbms_cloud.create_credentials are the credentials to the target database.
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name =>'DB_LINK_CRED',
  4             username =>'DEMO_USER',
  5             password=>'Good2go1!1234' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
Then create the database link to the target database using DBMS_CLOUD_ADMIN package, like this
 
demo-user@ATP19C> begin
  2     dbms_cloud_admin.create_database_link(
  3             db_link_name => 'DB_LINK_TEST',
  4             hostname => 'adb.us-ashburn-1.oraclecloud.com',
  5             port => 1522,
  6             service_name => 'g26be7c92912cdb_atp21c_low.adb.oraclecloud.com',
  7             credential_name =>'DB_LINK_CRED',
  8             directory_name => null );
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select * from dual@DB_LINK_TEST;
 
D
-
X
 
To create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to a target Autonomous Database on a public endpoint using a secure TCP connection without a wallet, the directory_name parameter must be NULL.
 
demo-user@ATP19C> select banner_full from v$version;
 
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
 
 
demo-user@ATP19C> select banner_full from v$version@db_link_test;
 
BANNER_FULL
------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

Using database links with ADB-S with wallet

Autonomous database serverless (ADB-S) users have an option to deploy their instance on either public or private endpoints, whether your connections are made over the pubic internet or through the Virtual client network (VCN), there is one thing in common, they are all secure and uses the Transport layer security (TLS1.2) protocol, so any connection between the client and database is encrypted and both the client and database can authenticate each other. When it comes to authenticating the client and server, there are couple of options.
 

• Both client and server authenticate each other (mutual TLS)
• Only the client authenticates the server (one-way TLS)

 
ADB-S uses the mutual TLS by default regardless of network configuration, so both the client and database can verify each other certificates. To complete server side authentication, any client connecting to an ADB-S instance must present their client credentials which can be downloaded as a zip file and contains SSO wallet, keystore, truststore and other network config files, this pretty much sums up how mTLS works and why you need to download a wallet to connect to autonomous database.
 
In this blogpost, we will see about how to create a database link from an ADB-S (source Autonomous Transaction processing 19c) to publicly accessible another ADB-S (Autonomous JSON database 21c)  with a wallet (mTLS)
 
Copy the Target database wallet, cwallet.sso containing the certificates for target database to an object storage bucket.
 
demo-user@ATP19C> variable uri varchar2(200)
demo-user@ATP19C> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DBLINK_TEST/';
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name
  2  from dbms_cloud.list_objects('my_demo_cred',:uri);
 

no rows selected

demo-user@ATP19C>

 
use dbms_cloud.get_object to upload the target database wallet into a directory created / available  on the source database.
 
demo-user@ATP19C> begin
  2     dbms_cloud.get_object(
  3             credential_name =>'my_demo_cred',
  4             object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DBLINK_TEST/cwallet.sso',
  5             directory_name => 'DATA_PUMP_DIR' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
demo-user@ATP19C> select object_name
  2  from table( dbms_cloud.list_files('DATA_PUMP_DIR') )
  3  order by created desc
  4  fetch first 1 row only;
 
OBJECT_NAME
------------------------------
cwallet.sso
 
 
on the ADB-S instance create credentials to access the target database, the username and the password for the dbms_cloud.create_credentials are the credentials to the target database.
 
demo-user@ATP19C> begin
 2    dbms_cloud.create_credential(
 3            credential_name => 'target_db_cred',
 4            username => 'demo_user',
 5            password => 'Good2go1!1234' );
 6 end;
 7 /
 
PL/SQL procedure successfully completed.
 
Then create the database link to the target database using DBMS_CLOUD_ADMIN package, like this
 
demo-user@ATP19C> begin
 2    dbms_cloud_admin.create_database_link(
 3            db_link_name=>'target_db_link',
 4            hostname => 'adb.us-ashburn-1.oraclecloud.com',
 5            port => 1522,
 6            service_name => 'g26be7c92912cdb_ajd21c_low.adb.oraclecloud.com',
 7            credential_name => 'target_db_cred',
 8            directory_name => 'DATA_PUMP_DIR' );
 9 end;
 10 /
 
PL/SQL procedure successfully completed.
 
Then when we try to access the data on target database using database link, it fails like this
 
demo-user@ATP19C> select * from dual@target_db_link;
select * from dual@target_db_link
                  *
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TARGET_DB_LINK
 
But the real problem is not due to the Incorrect password, instead it was due to USERNAME listed in lowercase, instead it should be in upper case.
 
demo-user@ATP19C> begin
  2     dbms_cloud.create_credential(
  3             credential_name => 'target_db_cred',
  4             username => 'DEMO_USER',
  5             password => 'Good2go1!1234' );
  6  end;
  7  /
 
PL/SQL procedure successfully completed. 

Once that was fixed, the database link works perfect.
 
demo-user@ATP19C> select host from dba_db_links where db_link = 'TARGET_DB_LINK';
 
HOST
-----------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST="adb.us-ashburn-1.oraclecloud.com")(PORT=1522))
(CONNECT_DATA=(SERVICE_NAME=g26be7c92912cdb_ajd21c_low.adb.oraclecloud.com))
(SECURITY=(MY_WALLET_DIRECTORY="/u03/dbfs/E47379BFF4313E4EE0539118000A6636/data/dpdump")
(SSL_SERVER_DN_MATCH=TRUE)))
 
demo-user@ATP19C>
demo-user@ATP19C> select banner_full from v$version;
 
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0
 
 
demo-user@ATP19C> select banner_full from v$version@target_db_link ;
 
BANNER_FULL
-------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0