OCI: ADB 23ai to ADB 19c Database link

This one is very similiar to OCI: Database Link between 2 Autonomous Databases 19c but this time it is the creation of a database link from a 23ai ADB to a 19c ADB. The only difference on top of the version is that I will be creating a private DB Link and not a public DB Link. This is more to ensure that everything is working as expected. I'll put very less comment here aseach step has already been detailed in OCI: Database Link between 2 Autonomous Databases 19c.

Get the 19c DB wallet:

[fred@myvm ~]$ mkdir adb19c_wallet
[fred@myvm ~]$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.l4pq7nwusgtxorjiszblek2aqqe2b4jusqtcw5fuev6bwcntifaaq5lna3bl --file adb19c_wallet/adb19c_wallet.zip --password "abc123456"
Downloading file  [####################################]  100%
[fred@myvm ~]$ cd adb19c_wallet
[fred@myvm adb19c_wallet]$ ls -ltr
-rw-rw-r--. 1 frdenis frdenis 21964 Nov 20 12:21 adb19c_wallet.zip
[fred@myvm adb19c_wallet]$ unzip adb19c_wallet.zip
Archive:  adb19c_wallet.zip
  inflating: ewallet.pem
  inflating: README
  inflating: cwallet.sso
  inflating: tnsnames.ora
  inflating: truststore.jks
  inflating: ojdbc.properties
  inflating: sqlnet.ora
  inflating: ewallet.p12
  inflating: keystore.jks
[fred@myvm adb19c_wallet]$

Create a directory into the 23ai database:

[fred@myvm adb19c_wallet]$ cd ../wallet_23ai/
[fred@myvm wallet_23ai]$ export TNS_ADMIN=$(pwd)
[fred@myvm wallet_23ai]$ sqlplus admin/"Iw0ntt3lly0u"@sandbox01_high
SQL>*Plus: Release 21.0.0.0.0 - Production on Wed Nov 20 12:22:22 2024
Version 21.14.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Last Successful login time: Tue Nov 19 2024 15:35:47 +00:00
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.10
SQL> create directory wallet19c as 'wallet19c';
Directory created.
SQL> select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where DIRECTORY_NAME = 'WALLET19C';
DIRECTORY_NAME        DIRECTORY_PATH
----------------   -----------------------------------------------------------------
WALLET19C            /u03/dbfs/27409389D44E5891E063D911000A6123/data/wallet19c
SQL> select * from dbms_cloud.list_files('WALLET19C');
no rows selected
SQL>

Move the cwallet.sso file into a directory into the 23ai database (long journey to copy a file):

[fred@myvm ~]$ oci os bucket create --name wallet --compartment-id $COMP_DEV
[fred@myvm ~]$ oci os object put --bucket-name wallet --file adb19c_wallet/cwallet.sso
[fred@myvm ~]$ oci iam auth-token create --user-id ocid1.user.oc1..4p2paj5vy5ghhx7ayavkqoiqarqaqomx3jroqabkl32aze7qpamp7wfkfayo --description fred_cred_for_wallet_dblink
. . .
    "token": "9m>1x0heLR4bDe:{8lnT",
. . .
[fred@myvm ~]$ sqlplus admin/"Iw0ntt3lly0u"@sandbox01_high
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.10
SQL> show user
USER is "ADMIN"
SQL> begin
  2  DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'fred_cred_for_wallet_dblink', username => 'fred', password => '9m>1x0heLR4bDe:{8lnT') ;
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL>
[fred@myvm ~]$ NAMESPACE=$(oci os ns get | jq -r '.data')
[fred@myvm ~]$ REGION="us-ashburn-1"
[fred@myvm ~]$ BUCKET_NAME="wallet"
[fred@myvm ~]$ FILE="cwallet.sso"
[fred@myvm ~]$ echo "https://${NAMESPACE}.objectstorage.${REGION}.oci.customer-oci.com/n/${NAMESPACE}/b/${BUCKET_NAME}/o/${FILE}"
https://qdci7zudoklt.objectstorage.us-ashburn-1.oci.customer-oci.com/n/qdci7zudoklt/b/wallet/o/cwallet.sso
[fred@myvm ~]$ 
SQL> begin
  2  DBMS_CLOUD.GET_OBJECT(
  3  object_uri => 'https://qdci7zudoklt.objectstorage.us-ashburn-1.oci.customer-oci.com/n/qdci7zudoklt/b/wallet/o/cwallet.sso',
  4  directory_name => 'WALLET19C',
  5  credential_name => 'fred_cred_for_wallet_dblink');
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL> SELECT OBJECT_NAME, BYTES, CREATED from DBMS_CLOUD.LIST_FILES('WALLET19C');
OBJECT_NAME           BYTES CREATED
---------------- ---------- -----------------------------------
cwallet.sso            5349 20-NOV-24 02.35.52.642528 PM +00:00
SQL>

Create some credentials on the 23ai DB (a user and password from the target 19c database are needed):

SQL> show user
USER is "ADMIN"
SQL> begin
  2  DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'TO_19C', username => 'ADMIN', password => 'xxxx');
  3  end;
  4  /
PL/SQL procedure successfully completed.
SQL>

And create the database link:

SQL> BEGIN
 DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
 db_link_name => 'TO_19C',
 hostname => '19cDB_host.adb.us-ashburn-1.oraclecloud.com',
 port => '1522',
 service_name => '1a0e3d6a347c8g0_19cDB_high.adb.oraclecloud.com',
 credential_name => 'TO_19C',
 directory_name => 'WALLET19C',
 private_target => TRUE,
 public_link => FALSE);
 END;
 /
 PL/SQL> procedure successfully completed.
SQL>

And finally time to test!

SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.10
SQL> select banner_full from v$version@TO_19C;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.1.0
SQL>