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):--db-name SANDBOX01
The database name. Note that:
--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:
Let's now run the command:
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:
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 ~]$
"message": "The Autonomous Database name can contain only alphanumeric characters."
[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 ~]$
Hi, Thank you very much for this post. Really helpful.
ReplyDelete