Oracle Cloud: How to create a database link?
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.
- 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. - 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. - 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.
- 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;
/
- Create directory to store the cwallet.sso
create directory FRANKFURT_WALLET_DIR as 'FRANKFURT/wallet';
- 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;
/
- 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.
- 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;
/
- 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;
/
- 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.
Hi,
I received following error when I executed the DBMS_CLOUD.GET_OBJECT package.
Error Message:
ORA-20401: Authorization failed for URI
ORA-06512: in “C##CLOUD$SERVICE.DBMS_CLOUD”