We can provision 2 Autonomous Databases (ADB) within our Oracle “always free” OCI tenancy, each with a maximum storage limit of 20GB.
This article explains how to create a Database Link from one ADB to the other using SQL Developer as an intermediary.
Instructions on how to do this using Cloud Storage as an intermediary are at https://blogs.oracle.com/datawarehousing/post/making-database-links-from-adw-to-other-database
I had difficulties making that work - in particular the parameter "ssl_server_cert_dn" for creating the DB LINK didn't work plus I didn't fancy creating a bucket in Cloud Storage to perform such a simple task as copying a 6k file from my “target” ADB wallet to the DATA_PUMP_DIR in my “source” ADB.
For subscribers to Always Free, particularly those already using SQL Developer, basically all you need to do in order to create a DB LINK in a “source” database to access data in a “target” database are the following …
Always Free users that connect through SQL Developer are probably already familiar with Step 1 - it's how we create a secure connection of type “Cloud wallet”. To create a DB LINK we need 2 of the files inside the downloaded zip, namely cwallet.sso and tnsnames.ora
Sign in to your OCI tenancy and provision a second “target” ADB database - remember the password when configuring the ADMIN user
Click on the “target” database in the OCI console, click on the “Database Connection” button, then click on the “Download wallet” button, enter a password for the wallet and download it.
The wallet is downloaded as a zip file which can be used to configure secure connections of type “Cloud wallet” to an ADB database in SQL Developer.
Create and test a SQL Developer connection to the ADMIN user in the “target” ADB - this is the first time you will need the ADMIN user password.
Unzip the wallet - we are interested in these 2 files -
Make an SQL Developer connection to the ADMIN schema on your “source” database and create a table where we will upload the contents of the downloaded “cwallet.sso” file:
CREATE TABLE blob2file(
content BLOB
);
This post also explains how to upload local files to BLOB columns - https://www.thatjeffsmith.com/archive/2012/01/sql-developer-quick-tip-blobs-and-images/
In case you have difficulties making that work - follow these steps:
Now we have the content of cwallet.sso stored as a blob in table blob2file, we can run the following script to create it as a file in the DATA_PUMP_DIR directory:
DECLARE
l_file_handle UTL_FILE.FILE_TYPE;
l_amount PLS_INTEGER:=32767;
l_offset PLS_INTEGER := 1;
l_binary_buffer RAW(32767);
BEGIN
FOR C IN (SELECT content FROM blob2file) LOOP
l_file_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'cwallet.sso', 'wb', 1024);
WHILE l_offset < dbms_lob.getlength(C.content)
LOOP
DBMS_LOB.READ(C.content, l_amount, l_offset, l_binary_buffer);
UTL_FILE.PUT_RAW(l_file_handle,l_binary_buffer);
l_offset := l_offset + l_amount;
END LOOP;
UTL_FILE.FFLUSH(l_file_handle);
UTL_FILE.FCLOSE(l_file_handle);
END LOOP;
END;
/
/*
** Check that it worked - should see "cwallet.sso" listed under the object_name column
*/
SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');
Finally we can create the DB LINK.
To do that we must first look at the contents of the file “tnsnames.ora” downloaded in Step 1.
This will include TNS alias definitions describing 3 different connections to the ADB - mine looks like this:
restoretestdb1_high = (description= (retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))
(connect_data=(service_name=hl7offzwezq2cal_restoretestdb1_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
restoretestdb1_low = (description= (retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))
(connect_data=(service_name=hl7offzwezq2cal_restoretestdb1_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
restoretestdb1_medium = (description= (retry_count=20)(retry_delay=3)
(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))
(connect_data=(service_name=hl7offzwezq2cal_restoretestdb1_medium.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
These describe different consumer groups that you can connect with - in an Always Free scenario I suggest using details of the “medium” entry in the script.
Before creating the db link we need first to create a CREDENTIAL of the ADMIN user in your “target” database.
/*
** Create a credential for the ADMIN user in the "target" ADB
*/
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'RESTORE_CREDENTIAL',
username => 'ADMIN',
password => '************' /* this is the second time you need the password of the ADMIN user in the "target" ADB */
);
END;
/
/*
** Create the DATABASE LINK referencing the credential and relevant details from the "medium" entry in tnsnames.ora
*/
BEGIN
DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
db_link_name => 'RESTORE_LINK',
hostname => 'adb.uk-london-1.oraclecloud.com', /* <-- the "host" entry in tnsnames.ora */
port => '1522', /* <-- the "port" entry in tnsnames.ora */
service_name => 'hl7offzwezq2cal_restoretestdb1_medium.adb.oraclecloud.com', /* <-- the "service_name" entry in tnsnames.ora */
credential_name => 'RESTORE_CREDENTIAL' /* <-- the credential of your "target" ADMIN user created previously */
);
END;
/
/*
** Test that it works
*/
SELECT * FROM V$PDBS@RESTORE_LINK; /* You should recognize the contents of the NAME column from when you provisioned the "target" ADB in Step 1 */
Of course, you can create a credential for any user/schema in the “target” ADB - I used ADMIN because it comes pre-configured and you can test it quickly.
Now you have a DB LINK to your “target” ADB, what can you use it for?
We intend using it to make copies of each article before it is opened in the CMS editor. This would enable a user to recover their content as it existed in the past.
It's probably also worth noting that with the DB LINK in place, you have doubled the effective database storage capacity available in your OCI Always Free tenancy.