OCI: Manual creation of a 23ai Autonomous Database

Command line is always more powerful than fancy GUIs; let's then push our mouse away and use our keyboard to create an OCI 23ai Autonomous Database in command line!

Keep in mind that you will need to have your API keys set up to be able to achive this.

Details about the oci db autonomous-database options I have used (many more options are available in the documentation but I did not need more than that below):

• --compartment-id $COMP_DEV
The compartment ID you want to create your ADB in; as these OCID are very big, I set them up in my environment then I can use $COMP_DEV and $COMP_PROD in my command lines which is very easier. Example how I set them up in my ~/.bash_profile:

export  COMP_DEV="ocid1.compartment.oc1..4vhfbmozrpwnoq55axatbwaazgpa6qjkxnaua6rziz7w7t6chcbwlaba5kx3"
export COMP_PROD="ocid1.compartment.oc1..bwekagnozpeaiaddbadzyc5e3u4tumnp5uaa2jyu4zajptweazuzqtzqnlbu"

To easily get all our compartment IDs (again, see how the command line is more powerful than the GUI), you may want to use this command:

[fred@ocivm ~]$ oci search resource structured-search --limit 1000 --query-text "query compartment resources" | jq -r '.data.items[] | ."display-name", ."identifier", ."lifecycle-state", ."time-created"' | paste - - - - | sort | column -t -o " | " -N "CompartmentName,CompartmenId,Status,TimeCreated"
CompartmentName   | CompartmenId                                                                        | Status | TimeCreated
dev               | ocid1.compartment.oc1..4vhfbmozrpwnoq55axatbwaazgpa6qjkxnaua6rziz7w7t6chcbwlaba5kx3 | ACTIVE | 2023-11-16T18:56:37.105000+00:00
prod              | ocid1.compartment.oc1..bwekagnozpeaiaddbadzyc5e3u4tumnp5uaa2jyu4zajptweazuzqtzqnlbu | ACTIVE | 2023-07-27T01:21:27.399000+00:00
. . .
[fred@ocivm ~]$

• --db-name SANDBOX01
The database name. Note that:

"message": "The Autonomous Database name can contain only alphanumeric characters."

• --display-name SANDBOX01
This is the name which you will see displayed in the Autonomous Database list in the console; it can be different from the database name; I would recommend keeping things clear and simple so here I used the same as db name for the 23ai sandbox I was creating.


• --admin-password "Iw0ntt3lly0u"
Self explanatory; admin is the DBA user of your database. I would have liked better the old system user but it is what it is. To be fair system may have been confusing so a new admin user makes sense.


• --backup-retention-period-in-days 7
This is a sandbox database so I do not need a big retention period (which you will pay for); I even wanted not to back it up but I could not find a way (so far).


• --compute-model ECPU --compute-count 2
2 ECPUs will be enough for my sandbox; it also auto scales and can be increased very easily so better start small to reduce the bill and see how it goes


• --data-storage-size-in-tbs 1
1 TB will be enough for my testing; as above, it is also easy to increase and will scale up so no need to start with huge sizes


• --db-workload OLTP
To get an ATP (Autonomous Transaction Processing) database and not an ADB (Autonomous DataWarehouse) database which would be --db-workload OLTP; other possible values in this documentation.


• --db-version 23ai
Self explanatory


• --is-auto-scaling-for-storage-enabled TRUE --is-auto-scaling-enabled TRUE
To auto scale storage and ECPU.


• --is-mtls-connection-required FALSE
I do not need MTLS authentication so I set it to FALSE


• --subnet-id ocid1.subnet.oc1.iad.5wauf57el5sryyzarwk3ga2akt6a5yaa5aacp3a25etmid7i2hgwtsyjqfxa
The subnet for the database; you can find it using the below command:

[fred@ocivm ~]$ oci network subnet list --compartment-id ocid1.compartment.oc1..aaaaaaaa5mo7xe6lo4kd7qcs4lncynnpbhhekno6p3kmbgct5gt35yszs5iq | jq -r '.data[] | ."display-name", .id' | paste - - | column -t -o " | " -N "Subnet,OCID"
Subnet        | OCID
dev-subnet | ocid1.subnet.oc1.iad.5wauf57el5sryyzarwk3ga2akt6a5yaa5aacp3a25etmid7i2hgwtsyjqfxa
[fred@ocivm ~]$

If your tenancy is kind of complex, you may not know which compartment your subnet is in; then use the search command to find your subnet-id:

[fred@ocivm ~]$ oci search resource structured-search --limit 1000 --query-text "query subnet resources" | jq -r '.data.items[] | ."display-name", ."compartment-id", .identifier' | paste - - - | column -t -o " | " -N "Subnet,CompartmentID,OCID"
Subnet         | CompartmentID                                                                       | OCID
dev-subnet     | ocid1.compartment.oc1..anlaaascjgudaqxpqbwgsluealssn5atihgwdqnf5xmandazdoj233xjmfpa | ocid1.subnet.oc1.iad.5wauf57el5sryyzarwk3ga2akt6a5yaa5aacp3a25etmid7i2hgwtsyjqfxa
prd-subnet     | ocid1.compartment.oc1..4kosa3azap6geam5mh36gsptc7ylds5q5hannec7inbab4alkonycqtx5oak | ocid1.subnet.oc1.iad.gzx5ryiqa3ata2mka7ywai35lye55aad2fwpeawhuagfk652arat5ytssjc7
. . .
[fred@ocivm ~]$

Let's now run the command:

[fred@ocivm ~]$ oci db autonomous-database create --compartment-id $COMP_DEV --db-name SANDBOX01 --display-name SANDBOX01 --admin-password "Iw0ntt3lly0u" --backup-retention-period-in-days 7 --compute-model ECPU --compute-count 2 --data-storage-size-in-tbs 1 --db-workload OLTP --db-version 23ai --is-auto-scaling-for-storage-enabled TRUE --is-auto-scaling-enabled TRUE --is-mtls-connection-required FALSE --subnet-id ocid1.subnet.oc1.iad.5wauf57el5sryyzarwk3ga2akt6a5yaa5aacp3a25etmid7i2hgwtsyjqfxa
. . .
    "backup-retention-period-in-days": 7,
    "compute-count": 2.0,
    "compute-model": "ECPU",
. . .
  "opc-work-request-id": "ocid1.coreservicesworkrequest.oc1.iad.vkzqanhcknwelipjs2w2cbjd2a33q3673bvft7t7lf62uf6zq3akvr6j2ubv"
}
[fred@ocivm ~]$

You will end up with a bunch of JSON data on the screen and a work id as the autonomous database creation will run in the background. You can check the status using the below command:

[fred@ocivm ~]$ oci db autonomous-database list --compartment-id $COMP_DEV | jq -r '.data[] | ."display-name", ."defined-tags"."Oracle-Tags".CreatedBy, (."time-created" | gsub("[.][0-9].*$";"")), ."lifecycle-state"' | paste - - - - | column -t -o " | " -N "DBName,CreatedBy,TimeCreated,Status"
DBName       | CreatedBy                          | TimeCreated         | Status
SANDBOX01    | default/myemail@company.com        | 2024-10-29T08:45:30 | PROVISIONING
[fred@ocivm ~]$

Just wait a bit (it is pretty fast) and your ATP will be created (I also add the OCID below):

[fred@ocivm ~]$ oci db autonomous-database list --compartment-id $COMP_DEV | jq -r '.data[] | ."display-name", ."defined-tags"."Oracle-Tags".CreatedBy, (."time-created" | gsub("[.][0-9].*$";"")), ."lifecycle-state", .id' | paste - - - - - | column -t -o " | " -N "DBName,CreatedBy,TimeCreated,Status,OCID"
DBName       | CreatedBy                          | TimeCreated         | Status    | OCID
SANDBOX01    | default/myemail@company.com        | 2024-10-29T08:45:30 | AVAILABLE | ocid1.autonomousdatabase.oc1.iad.wq2q2ba6a2butxrnakxeww3zgwe2blznjw6ic26ndzjxcklvu2kbk2m2cur6
[fred@ocivm ~]$

And below the output of a script I have started working on to nicely show your tenancy resources (a kind of a rac-status but for your OCI tenancy; I'll share it when ready):

    ADBName   |      AvailDomain     |  Tag |  OpenMode  | Type | Version | CPUs | Model|  DataTB | AllocTB |  UsedTB |     TimeCreated     |   MaintenanceBegin  |    MaintenanceEnd   |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   SANDBOX01  | mdRX:US-ASHBURN-AD-3 | null | READ_WRITE | OLTP |   23ai  |  2.0 | ECPU |    1    |   0.01  |  0.01   | 2024-10-29T08:45:30 | 2024-11-03T08:00:00 | 2024-11-03T10:00:00 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Let's now connect to that new 23ai database; first download and unzip the wallet:

[fred@ocivm ~]$ mkdir wallet_23ai  
[fred@ocivm ~]$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.wq2q2ba6a2butxrnakxeww3zgwe2blznjw6ic26ndzjxcklvu2kbk2m2cur6 --file "wallet_23ai/wallet_23ai.zip" --password "abc123456"  
Downloading file  [####################################]  100%
[fred@ocivm ~]$ cd wallet_23ai/
[fred@ocivm wallet_23ai]$ ls -ltr  
total 24
-rw-rw-r--. 1 fred fred 21984 Oct 29 12:29 wallet_23ai.zip
[fred@ocivm wallet_23ai]$ unzip wallet_23ai.zip  
Archive:  wallet_23ai.zip
  inflating: ewallet.pem
  inflating: README
  inflating: cwallet.sso
  inflating: tnsnames.ora
  inflating: truststore.jks
  inflating: ojdbc.properties
  inflating: sqlnet.ora
  inflating: ewallet.p12
  inflating: keystore.jks
[fred@ocivm wallet_23ai]$

Update the sqlnet.ora file with your wallet location:

[fred@ocivm ~]$ vi sqlnet.ora
[fred@ocivm ~]$ cat sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/home/fred/wallet_23ai/")))
SSL_SERVER_DN_MATCH=no
[fred@ocivm ~]$

Get your TNS admin alias:

[fred@ocivm ~]$ vi sqlnet.ora
[fred@ocivm ~]$ head -1 tnsnames.ora
sandbox01_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=xxx.adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=yyy_sandbox01_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
[fred@ocivm ~]$

And connect:

[fred@ocivm ~]$ export TNS_ADMIN=$(pwd)
[fred@ocivm ~]$ sqlplus admin/"Iw0ntt3lly0u"@sandbox01_high
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Nov 19 15:35:47 2024
Version 21.14.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Last Successful login time: Tue Nov 19 2024 15:28:30 +00:00
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.10
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.6.0.24.10
SQL>

Finally, if you just wanted to manual create a 23 ai autonomous database as a learning experience, you may not want to delete it which is also easy:

[fred@ocivm ~]$ oci db autonomous-database delete --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.wq2q2ba6a2butxrnakxeww3zgwe2blznjw6ic26ndzjxcklvu2kbk2m2cur6
Are you sure you want to delete this resource? [y/N]: y
[fred@ocivm ~]$

Check the status of the operation:

[fred@ocivm ~]$ oci db autonomous-database list --compartment-id $COMP_DEV | jq -r '.data[] | ."display-name", ."defined-tags"."Oracle-Tags".CreatedBy, (."time-created" | gsub("[.][0-9].*$";"")), ."lifecycle-state", .id' | paste - - - - - | column -t -o " | " -N "DBName,CreatedBy,TimeCreated,Status,OCID"
DBName       | CreatedBy                                | TimeCreated         | Status      | OCID
SANDBOX01    | default/myemail@company.com              | 2024-10-29T08:45:30 | TERMINATING | ocid1.autonomousdatabase.oc1.iad.wq2q2ba6a2butxrnakxeww3zgwe2blznjw6ic26ndzjxcklvu2kbk2m2cur6
[fred@ocivm ~]$

And done!

[fred@ocivm ~]$ oci db autonomous-database list --compartment-id $COMP_DEV | jq -r '.data[] | ."display-name", ."defined-tags"."Oracle-Tags".CreatedBy, (."time-created" | gsub("[.][0-9].*$";"")), ."lifecycle-state", .id' | paste - - - - - | column -t -o " | " -N "DBName,CreatedBy,TimeCreated,Status,OCID"
DBName       | CreatedBy                                | TimeCreated         | Status     | OCID
SANDBOX01    | default/myemail@company.com              | 2024-10-29T08:45:30 | TERMINATED | ocid1.autonomousdatabase.oc1.iad.wq2q2ba6a2butxrnakxeww3zgwe2blznjw6ic26ndzjxcklvu2kbk2m2cur6
[fred@ocivm ~]$

Some bash tips -- 18 -- paste

This blog is part of a shell tips list which are good to know -- the whole list can be found here.

I really like finding a real usage for a Unix command you heard of, you have somewhere in your quiver but you never really used because you never found an opportunity to or you never found the good combo which makes it very powerful. Let's explore the power of paste which we will end up combining with fold, shuf and column.

First, let's generate a simple list of numbers, one number per line using seq:

$ seq 1 7
1
2
3
4
5
6
7
$

Now let's say you want to organize these numbers by columns of two numbers. Hmmm not that easy right? this where paste shines:

$ seq 1 7 | paste - -
1       2
3       4
5       6
7
$

See these 2 hyphens in paste - -? There are the number of columns you want paste to organize your data in; you want 4 columns? just use 4 hyphens:

$ seq 1 7 | paste - - - - 
1       2       3       4
5       6       7
$

It also makes very easy something oftenly hapenning: rows to columns. paste has the -s/--serial option for that, check below:

$ seq 1 7 | paste -s
1       2       3       4       5       6       7
$

Pretty cool, right? And maybe a CSV type output would be a very good idea as well. paste also got you covered; Indeed, by default paste uses a TAB as a separator which we can change using -d/--delimiters:

$ seq 1 7 | paste -s -d ","
1,2,3,4,5,6,7
$

And what about a CSV with 2 columns per line?

$ seq 1 7 | paste - -  -d ","
1,2
3,4
5,6
7,
$

Note that all of that also works from a file as well:

$ seq 1 7 > test_file
$ cat test_file | paste - -  -d ","
1,2
3,4
5,6
7,
$

Cool but what about a real life example. Let's say we want to create a nice table like list of our OCI Autonomous Databases. The command to get that would be:

$ oci search resource structured-search --limit 1000 --query-text "query AutonomousDatabase resources return allAdditionalFields where lifecyclestate != 'TERMINATED'" |
$

This would generate a JSON with tons (too much) of information and JSON is not really human readable. We could then first use jq to only get the information we are interested in:

$ oci search resource structured-search --limit 1000 --query-text "query AutonomousDatabase resources return allAdditionalFields where lifecyclestate != 'TERMINATED'" | jq -r '.data.items[] | ."additional-details".dbName,."additional-details".ecpuCount,."additional-details".workloadType, ."additional-details".dataStorageSizeInTBs,."lifecycle-state", (."time-created" | gsub("[.][0-9].*$";""))'
PROD2
32.0
ATP
8
AVAILABLE
2024-09-02T07:49:32
PROD1
64.0
ADW
0
AVAILABLE
2023-12-06T02:20:58
TEST1
48.0
ADW
0
AVAILABLE
2023-11-29T02:07:21
$

We indeed get the information we need but it is not really readable. I guess you already guessed how to make it readable: paste! We have 6 values per Autonomous Database, so just use 6 hypens in the paste command:

$ oci search resource structured-search --limit 1000 --query-text "query AutonomousDatabase resources return allAdditionalFields where lifecyclestate != 'TERMINATED'" | jq -r '.data.items[] | ."additional-details".dbName,."additional-details".ecpuCount,."additional-details".workloadType, ."additional-details".dataStorageSizeInTBs,."lifecycle-state", (."time-created" | gsub("[.][0-9].*$";""))' | paste - - - - - -
PROD2        32.0    ATP     8       AVAILABLE       2024-09-02T07:49:32
PROD1        64.0    ADW     0       AVAILABLE       2023-12-06T02:20:58
TEST1        48.0    ADW     0       AVAILABLE       2023-11-29T02:07:21
$

Oh, this is very better! One would tell me that we can also do that using jq using join or @tsv and you would be correct but paste will work with non JSON outputs.

You also know that you can easily add a -d "," to get that output in CSV but let's keep it text based as I want that list from my terminal on my VM quickly when I need it and I would like that output to be nicer. This is where column comes into play! column makes nice columns (adapting the size of the columns) from outputs and can also add some nice column separators to look like a table (I won't re show the whole long oci | jq command for viibility):

$ oci search . . . | paste - - - - - - | sort | column -t -o " | "
PROD1 | 64.0 | ADW | 0 | AVAILABLE | 2023-12-06T02:20:58
PROD2 | 32.0 | ATP | 8 | AVAILABLE | 2024-09-02T07:49:32
TEST1 | 48.0 | ADW | 0 | AVAILABLE | 2023-11-29T02:07:21
$

column can also add a header for each column:

$ oci search . . . | paste - - - - - - | sort | column -t -o " | " -N "DBName,ECPU,Type,TBs,Status,TimeCreated"
DBName | ECPU | Type | TBs | Status    | TimeCreated
PROD1  | 64.0 | ADW  | 0   | AVAILABLE | 2023-12-06T02:20:58
PROD2  | 32.0 | ATP  | 8   | AVAILABLE | 2024-09-02T07:49:32
TEST1  | 48.0 | ADW  | 0   | AVAILABLE | 2023-11-29T02:07:21
$

Last but not least, it is also easy to hide columns; let's hide the ECPU (column2) and the TBs (column 4) numbers (by the way, I contacted support as the TBs for my ADW is 0 which looks like a bug):

$ oci search . . . | paste - - - - - - | sort | column -t -o " | " -N "DBName,ECPU,Type,TBs,Status,TimeCreated" -H2,4
DBName | Type | Status    | TimeCreated
PROD1  | ADW  | AVAILABLE | 2023-12-06T02:20:58
PROD2  | ATP  | AVAILABLE | 2024-09-02T07:49:32
TEST1  | ADW  | AVAILABLE | 2023-11-29T02:07:21
$

Super cool and super easy!

Now, let's get back to another good combo using paste. We sometimes need to anonymize data (when writing a blog for example) and this can be a bit painful; what if we could have a tool automatically shuffling the characters of what we want to anonymize for us? Let's go back to our sequence of 7 numbers I opened with and we let's use the shuf command which will shuffle the lines of this output:

$ seq 1 7 | shuf
4
7
2
5
1
6
3
$

We now automatically recognize the kind of input which can be serialized by paste; let's do it:

$ seq 1 7 | shuf | paste -s -d ''
1547362
$

Note: the output is always different as shuf will shuffle the data differently each time it is executed. This is very cool, we now just need to find something which transform a list of characters to one character per line (indeed, we won't use seq but real data to be anonymized); a kind of unpaste command; as usual, those crazy Unix creators thought about everything and this command is... fold which wraps lines up to a number of character which we can set to 1 for our need:

$ echo "1234567"
1234567
$echo "1234567" | fold -w 1
1
2
3
4
5
6
7
$

We now have the combo we wanted:

$ echo "1234567" | fold -w 1 | shuf | paste -s -d ''
5347162
$ echo "1234567" | fold -w 1 | shuf | paste -s -d ''
3746215
$ echo "1234567" | fold -w 1 | shuf | paste -s -d ''
7253416
$

You have here a string anonymizer; if you use that on an OCID for example:

$ echo "anuwcljt6ubcb2aa6hv52fnv343cvqhvwit7fedl4q7beqd2gw2fkhr4mhma" | fold -w 1 | shuf | paste -s -d ''
4cqah43jdkv23uqu2l22thmlwvrbqbawhnf4ncevewfgh5f6atmb7dvc6i7a
$

Well, good luck to find the original one back!

And all of this for ~ 100 KB:

$ du -sh /usr/bin/shuf
48K     /usr/bin/shuf
$ du -sh /usr/bin/fold
36K     /usr/bin/fold
$ du -sh /usr/bin/paste
36K     /usr/bin/paste
$

Now that you've learnt how to make some cool combos with paste, fold, shuf and column, ask the below question to your colleagues:

That's all for this one; enjoy and... keep it simple!

---

< Previous shell tip / Next shell tip coming soon >

OCI: Database Link between 2 Autonomous Databases 19c

Database links are seen by some as evil and as wonderful tools by others. Whatever your side on this, let's see how we can create database links between 2 autonomous databases. The characteristics of the below example are:

• We want to get data from the PROD1 database
• The database link will be created into the PROD2 database
• A network connectivity is already setup between the databases; the ones in this example are in the same tenancy and in the same region; if your databases are in different tenancies, you want to check this documentation first; if they are in different regions, you please have a look at this documentation first
• The databases can be in the same compartment or not; below example is with 2 databases in different compartments
• The autonomous databases can be ADW or ATP, it does not matter
• I will use one and unique Linux VM with a non privileged user fred@myvm on that VM
• Many operations are only doable by the ADMIN user so be sure you know the ADMIN user credentials
• Below assumes that your OCI client is configured and works

A summary of what needs to be done looks straigthforward on a graph:

But the fact that these are autonomous databases implies many steps which I will be describing in this blog.
Let's follow and detail each of the steps shown on the above picture.

0/ Requirements summary

A short summary of the requirements to be able to create a DB Link:

• Network connectivity between the 2 databases
• A database username and password on PROD1
• The PROD1 database certificate
• OCIDs of PROD1, PROD2, your compartments, etc...

1/ Get the PROD1 wallet

Ge the PROD1 database OCID from the console and use it to get the database wallet from your Linux VM:

[fred@myvm ~] oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.iad.xxx --file adb_wallet.zip --password "abc123456"
Downloading file  [####################################]  100%
[fred@myvm ~] ls -ltr adb_wallet.zip
-rw-rw-r--. 1 fred fred 21974 Sep 30 13:05 adb_wallet.zip
[fred@myvm ~]

Note that we must specify a wallet password using this oci db autonomous-database generate-wallet command; related error message you would face (I tried :)) are:

Error: Missing option(s) --password.
"message": "The wallet download password cannot be an empty string.",
"message": "The wallet download password must be at least 8 characters.",

You can also download this wallet from the console: "Autonomous database details" => "Database connection" => "Download wallet"; we won't use this password so feel free to forget that password immediately! Let's unzip the wallet and points the files we will be interested in:

[fred@myvm ~] unzip adb_wallet.zip
Archive:  adb_wallet.zip
  inflating: ewallet.pem
  inflating: README
  inflating: cwallet.sso     <== the certificate we will work with
  inflating: tnsnames.ora    <== we will need host, service and port information at the end of the procedure
  inflating: truststore.jks
  inflating: ojdbc.properties
  inflating: sqlnet.ora
  inflating: ewallet.p12
  inflating: keystore.jks
[fred@myvm ~]

All right, step 1 was easy, let's jump into step 2.

2/ Transfer the certificate

This one looks simple but many steps are needed to achieve the transfer of the certificate; indeed, we have to transfer the PROD1 cwallet.sso file into a PROD2 directory (DBFS). The steps to achieve that are:

• 2.1/ Create a bucket (unless you already have one)
• 2.2/ Copy the cwallet.sso file from your Linux VM into your bucket
• 2.3/ Create a directory into the PROD2 database
• 2.4/ Copy the cwallet.sso file from your bucket into the PROD2 PROD2 directory
• 2.5/ Drop your bucket if you created one just for this transfer

And on top of that, we will need to create some credentials to get authenticated here and there. Yeaaaahh all of that just to copy a file . . . hopefully it is a one time thing :)

2.1/ Create a bucket (unless you already have one)

You'll find your PROD2 database compartment OCID in the console (search for "Compartment"):

[fred@myvm ~] oci os bucket create --name wallets --compartment-id ocid1.compartment.oc1..aaaaaaabbbbbbbbcccccccccccfffffffff  
{
    . . .
        "CreatedBy": "default/fred",
        "CreatedOn": "2024-09-30T13:07:25.850Z"
    . . .
    "name": "wallets",
    . . .
}
[fred@myvm ~]

2.2/ Copy the cwallet.sso file from your Linux VM into your bucket

[fred@myvm ~] oci os object put --bucket-name wallets --file cwallet.sso  
Uploading object  [####################################]  100%
{
  "etag": "9fba7327-d979-4b21-a898-8b795ea02f76",
  "last-modified": "Mon, 30 Sep 2024 13:09:42 GMT",
  "opc-content-md5": "sstACFWtW/4qBGNkdH/iuQ=="
}
 [fred@myvm ~]

Check that the file has been correctly copied into the wallet:

[fred@myvm ~] oci os object list --bucket-name wallets
{
  "data": [
      . . .
      "name": "cwallet.sso",
      . . .
}
[fred@myvm ~]

2.3/ Create a directory into the PROD2 database

Note that as of now only ADMIN can create directories and DB Links.

SQL> show user
USER is "ADMIN"
SQL> create directory wallets as 'wallets';
Directory created.
SQL> select OWNER, DIRECTORY_NAME, DIRECTORY_PATH from dba_directories where directory_name = 'WALLETS';
OWNER    DIRECTORY_NAME   DIRECTORY_PATH
-------- ---------------- --------------------------------------------------------------------------------
SYS      WALLETS          /u03/dbfs/0B3BHK09F13420009KP8/data/wallets
SQL>

2.4/ Copy the cwallet.sso file from your bucket into the PROD2 database directory

We now need to create somne credentials to achieve this. I use to go with "Auth tokens" which are kind of the same tokens you use for github if you are familiar with. You'll need your user-id which you can find in the console "Your profile" (top right) => "My profile" then copy your OCID in "User information" or in the ~/.oci/config file (grep user ~/.oci/config):

[fred@myvm ~] oci iam auth-token create --user-id ocid1.user.oc1..xxxx --description fred_cred_for_wallet_dblink
  {
  "data": {
    "description": "fred_cred_for_wallet_dblink",  <== it is actually the credential name
    . . .
    "token": "c]CBZfi97_E(;U5",                    <== your token
    "user-id": "ocid1.user.oc1..xxxx"
  },
  "etag": "89c91cb65f03452fabd74c618609d0bb"
}
[fred@myvm ~]

You can also use the console to get a token: "Your profile" (top right) => "My profile" => "Auth tokens" (bottom left) => "Generate token".

Now create the credentials (as ADMIN):

SQL> show user
USER is "ADMIN"
SQL> BEGIN
  2  DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'fred_cred_for_wallet_dblink', username => 'fred', password => 'c]CBZfi97_E(;U5');
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>

Note that I kept "c]CBZfi97_E(;U5')" as token instead of putting "xxxx"; it is to show what an Auth token looks like, it is obviously not my real token. Also, the user used here is the one you connect to your tenancy with the console so most likely your email address.

Now to copy the cwallet.sso file into the WALLETS directory, we need to know the path of that file; for now we just know that it is located into the wallets bucket. You can find this path from the console in "Object Details" on the right of the file when you list the content of your bucket. I could not find a way to get that info using the oci client, this is then an opportunity to learn how these paths are built and build it ourselves.

Object paths are of this form (I put shell variables in the path as this is how we're gonna build it):

https://${NAMESPACE}.objectstorage.${REGION}.oci.customer-oci.com/n/${NAMESPACE}/b/${BUCKET_NAME}/o/${FILE}

We can find these values as below:

[fred@myvm ~] NAMESPACE=$(oci os ns get | jq -r '.data')
[fred@myvm ~] REGION="us-ashburn-1"     <== this can be found in the ~/.oci/config file
[fred@myvm ~] BUCKET_NAME="wallets"
[fred@myvm ~] FILE="cwallet.sso"
[fred@myvm ~] echo "https://${NAMESPACE}.objectstorage.${REGION}.oci.customer-oci.com/n/${NAMESPACE}/b/${BUCKET_NAME}/o/${FILE}"
https://ihdfd7683hjkz.objectstorage.us-ashburn-1.oci.customer-oci.com/n/ihdfd7683hjkz/b/wallets/o/cwallet.sso
[fred@myvm ~]

And we get file path of the file! We can finally copy the cwallet.sso file from our bucket into the directory (DBFS)!

SQL> show user
USER is "ADMIN"
SQL> BEGIN
  2  DBMS_CLOUD.GET_OBJECT(
  3  object_uri => 'https://ihdfd7683hjkz.objectstorage.us-ashburn-1.oci.customer-oci.com/n/ihdfd7683hjkz/b/wallets/o/cwallet.sso',
  4  directory_name => 'WALLETS',
  5  credential_name => 'fred_cred_for_wallet_dblink');
  6  END;
  7  /
PL/SQL procedure successfully completed.
SQL> SELECT OBJECT_NAME, BYTES, CREATED, LAST_MODIFIED from DBMS_CLOUD.LIST_FILES('WALLETS');
OBJECT_NAME             BYTES CREATED                                            LAST_MODIFIED
------------------ ---------- ------------------------------------------------ ---------------------------------------------
cwallet.sso              5349 30-SEP-24 02.21.22.000000 PM +00:00                30-SEP-24 02.21.22.000000 PM +00:00
SQL>

2.5/ Drop your bucket if you created one just for this transfer

If you have created a dedicated bucket for this transfer, you may want to drop it to keep things clean, we won't need it anymore.

[fred@myvm ~] oci os bucket delete --name wallets
Are you sure you want to delete this bucket? [y/N]: y
[fred@myvm ~]

3/ Create the DB Link

OK! we can now create the DB Link -- after we create some more credentials: the credentials to connect to the PROD1 database:

SQL> BEGIN
2  DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'PROD1_USER', username => 'A_PROD1_USER', password => 'xxxxxxxxxx');
3  END;
4  /
PL/SQL procedure successfully completed.
SQL>

And (finally !) create the DB Link. You'll need the below information from the tnsnames.ora file from the wallet we generated during step 1:

• hostname => xxxxxxx.adb.us-ashburn-1.oraclecloud.com
• port => 1522
• service_name => yyyyyyy.adb.oraclecloud.com

SQL> BEGIN
  2  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
  3  db_link_name => 'TO_PROD1',
  4  hostname => 'xxxxxxx.adb.us-ashburn-1.oraclecloud.com',
  5  port => '1522',
  6  service_name => 'yyyyyyy.adb.oraclecloud.com',
  7  credential_name => 'PROD1_USER',
  8  directory_name => 'WALLETS',
  9  private_target => TRUE,
 10  public_link => TRUE);
 11  END;
 12  /
PL/SQL procedure successfully completed.
SQL>

And now the moment of truth: testing the DB Link !

SQL> select count(*) from user_objects@SOURCE_DATABASE;
  COUNT(*)
----------
      1994
SQL>

Congratulations, now go enjoy your new DB Link between your 2 autonomous databases!