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 ~]$
First, let's remind our environment:
