DBLINK ADBs

This article describes a simple method for creating a Database Link between 2 Oracle ADB databases.

To create a database link in ADB-1 ("source") to a schema in ADB-2 ("target") requires that we first create a copy of the “cwallet.sso” file of ADB-2's Database Connections wallet in the DATA_PUMP_DIR directory of ADB-1 

The steps are:

1. Log on to Oracle OCI console and download the Connection Wallet of ADB-2
2. Upload cwallet.sso as an Apex static workspace file in ADB-1
3. Create database credential in ADB-1 for the target schema in ADB-2
4. Create database link in ADB-1 that points to schema in ADB-2

Step 1

Sign in to your OCI tenancy.

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 to your local client.

Unzip the wallet - we are interested in these 2 files - 

1. cwallet.sso
2. tnsnames.ora

Step 2

Using Apex on the “source” ADB-1 create a workspace static file by uploading "cwallet.sso" obtained in Step 1.

Then run the following PLSQL script to copy the file to 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 file_content FROM apex_workspace_static_files WHERE file_name='cwallet.sso')
    	LOOP
          l_file_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'cwallet.sso', 'wb', 1024);
          WHILE l_offset < dbms_lob.getlength(C.file_content)
            LOOP
                DBMS_LOB.READ(C.file_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;
/

Step 3

Create credential in ADB-1 for the target schema in ADB-2

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_CREDENTIAL',
    username => 'ADMIN',
    password => '**********'
  );
END;
/

Step 4

To create the database link we need the host, port and service_name values from the medium entry in “tnsnames.ora”. For example if the tnsnames entry looks like this…

db202103270929_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_db202103270929_medium.adb.oraclecloud.com))(security=(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")))

…then we create the database link as…

BEGIN
  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'RESTORE_LINK',
    hostname => 'adb.uk-london-1.oraclecloud.com',                               
    port => '1522',                                                              
    service_name => 'hl7offzwezq2cal_db202103270929_medium.adb.oraclecloud.com',
    credential_name => 'MY_CREDENTIAL'                                  
  );
END;
/

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