Rules and Limitations
Note some of below rules and limitations of creating DB link from ADB:
- The target databases (non-ADB) should be configured to use TCP/IP with SSL (TCPS) authentication
- The allowed DB port range is 1521-1525 to ensure the security. You can define the port while creating the DB link
- ADB DB link support databases that are only accessible through a public IP or public hostname
- One directory restricted to one wallet file. Create multiple directories to keep multiple wallet files of multiple databases
- Supported target database versions are 11.2.0.2, 12.1.0.2, 12.2.0.1, 18c and 19c.
Creating DB link from Autonomous Database
Now let's walk through DB link creation procedure from an Autonomous database. As mentioned, you should use DBMS_CLOUD_ADMIN.CREATE_ DATABASE_LINK procedure on ADB. Below steps describe the required procedure:
- Copy your target database wallet file (cwallet.sso) which contains target database certification to an object store
- Using DBMS_CLOUD.CREATE_CREDENTIAL procedure, create access credentials to the object store where the target wallet file is stored.
- Upload the target database wallet file from Object store to database directory using the DBMS_CLOUD.GET_OBJECT procedure. Refer example below:
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => 'DEFF_CRED_CRDB',
object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
directory_name => 'DATA_PUMP_DIR'); -- DATA_PUMP_DIR is the predefined/default directory. It can be another DB directory.
END;
/- Create credentials on ADB, refer below example:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'DBL_CRDB',
username => 'SCOTT', -- username must be specified in CAPITAL
password => 'welcome1'
);
END;
/Details are stored in the database in encrypted format.
You can use the query below to list the stored credentials:
SELECT credential_name, username, comments FROM all_credentials;- You can use below command to drop credentials from the database:
BEGIN
DBMS_CLOUD.DROP_CREDENTIAL('DBL_CRDB');
END;- Now we are ready to create database link from Autonomous database. Use the example below to create:
BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'ADB_TO_CRDB',
hostname => 'adb.eu-frankfurt-1.oraclecloud.com',
port => '1525',
service_name => 'example_medium.adwc.example.oraclecloud.com',
ssl_server_cert_dn => 'CN=adwc.example.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US',
credential_name => 'DBL_CRDB',
directory_name => 'DATA_PUMP_DIR');
END;
/If you are not an admin privileged user, get the permission to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK procedure.
Now, you can use the DB link from ADB to the target database.
To drop DB link from ADB, you can use the example below:
BEGIN
DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK(
db_link_name => 'ADB_TO_CRDB' );
END;
/Creating DB link to ADB
We have learned so far how to create a DB link from ADB to target database. This section will zip through DB link creation to ADB.
As I have mentioned my previous blog entry that ADB only accepts secured client connections. To which, you will have to download client connect credentials. You can refer my previous blog post for the procedure.
Before creating database link, ensure the following is met:
- Download the client connect credentials
- Unzip the downloaded file to a secure location your computer
- Turn of GLOBAL_NAMES parameter using 'ALTER SYSTEM SET GLOBAL_NAMES=FALSE';
- Using below example, create a db LINK ADB:
CREATE DATABASE LINK DBL_TO_ADB
CONNECT TO SYEDJ IDENTIFIED BY welcome1
USING
'(description=(retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=example1.oraclecloud.com))
(connect_data=(service_name=example2_high.adwc.oraclecloud.com))
(security=(my_wallet_directory=/u01/targetwallet)
(ssl_server_dn_match=true)
(ssl_server_cert_dn="CN=example2.oraclecloud.com,OU=Oracle
BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))';At this point in time the DB link is ready.
In nutshell, this blog post explained the do's and don'ts of creating DB link from/to ADB.
