Oracle Cloud: How to create a database link?

25. November 2020 Uncategorized 0

On my journey to the Oracle Cloud, I got another task to complete – to create a database link between two databases running on the Oracle Cloud.

I will cover the use case of creating a database link between databases in a separate regions (Switzerland > Frankfurt).

Database link between databases in a separate region

First, make sure you have two running databases in separate regions. In this example the DB link will point from the database in the Zurich region to the database in the Frankfurt region.

  1. Download Client Credentials (Oracle Wallet)
    You will need the client credentials and connection information to connect to your database. The client credentials include the wallet.

    Log in onto Oracle Cloud console for your tenant in the Frankfurt region. Navigate to your Autonomous DB details page and click on the “DB Connection“.
    To download your client credentials, select the type of wallet, then click Download Wallet. You will be asked to create a password for the wallet.

    Note: Instance wallets contain credentials only for a single Autonomous Database. Regional wallet contains credentials for all Autonomous Databases in the currently selected region.

    Save the Wallet .zip file somewhere on your computer and extract it. We have to upload the cwallet.sso file to Oracle Bucket.

    DB Connection - Wallet

    Download Oracle Wallet
  2. Upload the Oracle wallet to the Object storage bucket
    Navigate through Menu > Object Storage > Object Storage, and click on one existing Bucket or “Create Bucket” if you do not have an existing one already.

    Then click on “Upload” to upload the cwallet.sso file you downloaded in the last step. When upload finishes, click on “View Object Details” and copy the URL Path of the cwallet.sso file – you will need it later.

    Object Storage

    Upload to Bucket

    Upload to Bucket

    View Object Details

    View Object Details URL PATH


  3. Generate Auth Token
    To be able to connect to the bucket, you have to generate the Auth Token for your user.
    Navigate through the main menu: Identity > Users, and click on your username. On the “User Details” page scroll down to the Resources and click on the “Auth Tokens“. Generate a new Auth Token for your user and copy it.

    Menu - Identity - Users


    Select an user


    Auth Token


    Generate Auth Token

    Generate Auth Token


    Copy Auth Token

  4. Create Tenant Credential
    Log into your Zurich database as the ADMIN user and create the credentials needed to access to the bucket in the region Frankfurt.
    Note: Adapt the username and password below.

    BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'CRED_TENANT_USER_FRANKFURT',

    username => 'office@oracon.at',
    password => 'YourAuthTokenPassword');
    END;
    /

  5. Create directory to store the cwallet.sso
    create directory FRANKFURT_WALLET_DIR as 'FRANKFURT/wallet';

  6. Get the cwallet.sso
    BEGIN
    DBMS_CLOUD.GET_OBJECT (
    credential_name => 'CRED_TENANT_USER_FRANKFURT',
    object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/wefoiojfl/b/BucketStd/o/cwallet.sso',
    directory_name => 'FRANKFURT_WALLET_DIR');
    END;
    /

  7. Grant privileges to the Application user/schema
    GRANT EXECUTE on DBMS_CLOUD_ADMIN to APP_USER_ZURICH;
    GRANT EXECUTE on DBMS_CLOUD to APP_USER_ZURICH;
    GRANT CREATE DATABASE LINK TO APP_USER_ZURICH;


    Note: When you create the database link, you can afterwards revoke the privilege CREATE DATABASE LINK from the user APP_USER_ZURICH.

  8. Create Application user credential for a DB Link
    Log in as the application user into Zurich database and create user credential needed for the database link.

    BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'CRED_DB_LINK2FRANKFURT',
    username => 'APP_USER_FRANKFURT',
    password => 'UserPasswordXX');
    END;
    /


  9. Create a database link
    Note: The hostname, service name and ssl_server_cert_dn values you can find in the tnsnames.ora within Oracle Wallet .zip file you downloaded.

    BEGIN
    DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'DB_LINK2FRANKFURT',
    hostname => 'adb.eu-frankfurt-1.oraclecloud.com',
    port => '1522',
    service_name => 'ke3ff34tfsdkglzp_crm_medium.adb.oraclecloud.com',
    ssl_server_cert_dn => 'CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
    credential_name => 'CRED_DB_LINK2FRANKFURT',
    directory_name => 'FRANKFURT_WALLET_DIR');
    END;
    /


  10. Test the DB Link
    At the end, you have to test the connection via DB link.

    select sysdate from dual@DB_LINK2FRANKFURT;

Data Dictionary queries

Hare are some useful queries you might need.

Display all credentials:
select *
from SYS.dba_credentials;

Display the content of some directory:
SELECT *
FROM table(dbms_cloud.list_files('FRANKFURT_WALLET_DIR'));

Troubleshooting

Error:
ORA-12529: TNS:connect Request Rejected Based On Current Filtering Rules

Cause & Solution:
It’s allowed to use only one Wallet at a given point in time. If you try to create the bidirectional database link between two database in the same region, for example Frankfurt <-> Frankfurt, then you have to download and use the “Regional Wallet” type, and not the “Instance Wallet” type.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Errors:
ORA-20401: Authorization failed for URI
ORA-06512: in “C##CLOUD$SERVICE.DBMS_CLOUD”, Zeile 989

Cause:
If you try to create and use credential for a federated user like oracleidentitycloudservice/YourUsername@domain.com, then you may get such errors.

Solution:
Do not use a federated user like oracleidentitycloudservice/YourUsername@domain.com. Use a non-federated user instead.
Note: If you create such wrong credential with the federated user, and in the same session you drop it and recreate the credential with the correct non-federated user, you may hit the same errors, because it seems, it caches the wrong one in the current session. Reconnect and try again – it should work.
I am not sure, if it’s a bug or not. If someone from Oracle Cloud guys reads this, then please try to reproduce it.



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.