Database link without wallet

Question

I am currently working on creating a database link to query data from an on-premises Oracle database. Although I have a working connection string, I don't have access to the server itself. When I attempt to query data from a table using the database link, I encounter the error ORA-28864. It appears this issue is due to the absence of the on-premises database wallet in my OCI environment. The question is: Is it possible to make it work event without the wallet?

First i tried to create it directly using

CREATE DATABASE LINK PRODATA
CONNECT TO user IDENTIFIED BY password
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))
(CONNECT_DATA=
(SERVICE_NAME=service)
)';

This returned the error

ORA-013031: insufficient privileges

But I'm using the admin user, who theoretically has all the privileges.

Then I tried

BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'PRODATA',
hostname => host,
port => port,
service_name => service,
credential_name => 'PRODATA_CREDENTIALS',
ssl_server_cert_dn => NULL,
directory_name => NULL);
END;
/

It runs without any errors by when I query a table like

select usr_name from users@prodata;

It returns the error

ORA-28864: SSL connection closed gracefully

should not have also "private_target => TRUE" in the CREATE_DATABASE_LINK params ? — p3consulting
– p3consulting, Commented May 15, 2024 at 18:48
@p3consulting private_target returns the error: ORA-20000: private_target => TRUE cannot be specified for a non-PE database when I execute the function — Jean Simas
– Jean Simas, Commented May 15, 2024 at 19:07
@PaulW I don't know how to quote by I said "It appears this issue is due to the absence of the on-premises database wallet in my OCI environment." and it is on the tags "oracle-cloud-infrastructure" — Jean Simas
– Jean Simas, Commented May 15, 2024 at 19:08

Connor McDonald · Accepted Answer · 2024-05-21 02:17:58Z

The docs suggest a wallet is required

https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-database-links-target-oracle-db.html#GUID-A397B7E0-A1BE-4CBE-BA9E-F97EC6BF0415

but those same docs also show the instructions on how to upload an on-premises wallet into the object store, and then reference that from your cloud database.

CREATE DIRECTORY dblink_wallet_dir AS 'directory_path_of_your_choice';

BEGIN 
    DBMS_CLOUD.GET_OBJECT(
        credential_name => 'DEF_CRED_NAME',
        object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
        directory_name => 'DBLINK_WALLET_DIR'); 
END;
/

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'DB_LINK_CRED',
        username => 'SCOTT',
        password => 'tiger');
END;
/

BEGIN
    DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
        db_link_name => 'SALESLINK',
        hostname => 'adb.eu-frankfurt-1.oraclecloud.com', 
        port => '1522',
        service_name => 'example_medium.adb.example.oraclecloud.com',
        ssl_server_cert_dn => 'CN=adb.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
        credential_name => 'DB_LINK_CRED',
        directory_name => 'DBLINK_WALLET_DIR');
END;
/

Collectives™ on Stack Overflow

Database link without wallet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related