Say you would like to create DB link between 2 Autonomous Database in OCI DB1 and DB2. Assume DB1 want to query data from DB2
select owner
,credential_name
from dba_credentials
where credential_name = 'OCI$RESOURCE_PRINCIPAL'
and owner = 'ADMIN';
Create New Database Directory on DB1
CREATE DIRECTORY ATP_WALLET_DIR AS 'ATP_WALLET_DIR';
select directory_name
,directory_path
from dba_directories
where directory_name like upper('ATP_WALLET_DIR');
Next, we need to create 2 credentials. 1 is used to access Bucket, 1 is used for storing schema@pwd to DB2
Credentials to Access Bucket
here multiple ways are available 1. using auth Token 2. use API keys Both are done via OCI console + DBMS_CLOUD.CREATE_CREDENTIAL procedure.
auth Token is bit easier
get the user from OCI (it is normally an email)
get a new auth Token(copy the token)
begin
dbms_cloud.create_credential(credential_name => 'DB001_OCI_AUTH_CREDENTIAL'
,username => 'YOUMAIL@MAIL>COM'
,password => 'TOKEN');
end;
/
API keys
find the user you like to use from oci
click on API keys and generate a new key
you need your user ocid, tenancy ocid, private key, fingerprint
begin
dbms_cloud.create_credential(credential_name => 'DB001_OCI_AUTH_CREDENTIAL'
,user_ocid => 'ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa'
,tenancy_ocid => 'ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a'
,private_key => 'MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg='
,fingerprint => 'f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27');
end;
/
Create Credential to Access DB2 schema
This is the user from the Source Database, e.g if you want to access object from schema ADMIN, should then use ADMIN@PWD or any other schemas.
begin
dbms_cloud.create_credential(credential_name => 'DB001_LINK_CRED'
,username => 'SCHEMA_NAME'
,password => 'PWD');
end;
/
Get Wallet
Unzip wallet and upload to a bucket where your earlier created Credential can access
use the following code to download it and place it in the newly created database direcotry.
begin
dbms_cloud.get_object(credential_name => 'DB001_OCI_AUTH_CREDENTIAL'
,object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/frpnibrn7ulj/b/public/o/DBwallet/cwallet.sso'
,directory_name => 'ATP_WALLET_DIR');
end;
/
Create db link to access DB2
begin
dbms_cloud_admin.create_database_link(db_link_name => 'DB002_LINK'
,hostname => '#CHECK TNS#'
,port => '1522'
,service_name => '#CHECK TNS#'
,credential_name => 'DB001_LINK_CRED'
,directory_name => 'ATP_WALLET_DIR');
end;
/
select sysdate
from dual@db002_link