Oracle Cloud : Create a Database VM & Database as a Service (DBaaS) & Patch DBaaS

Bare Metal, VM and Exadata -> Create DB System

My VM DB System name: ovmdb

Before create db system we need to generate ssh public key. We can create this from Cloud Shell.

orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$ ssh-keygen -b 2048 -t rsa -f myKey
Generating public/private rsa key pair.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in myKey.
Your public key has been saved in myKey.pub.
The key fingerprint is:
SHA256:MpUSUwqMitSkb2vOeX0lp9FSx8Q/2NVCc1uFN0Au6lg orhaneripe@a2b3e34e679d
The key's randomart image is:
+---[RSA 2048]----+
| o+. o.. o++.*|
| .o... + . .oo+*|
|oo o o .o.++o|
|o . o ...+ + |
| o o SEo . .|
| . . o++ + |
| o .. .B |
| + .. . o |
| +. . |
+----[SHA256]-----+
orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$

orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$ pwd
/home/orhaneripe/.ssh
orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$ ls -la
total 12
drwx------. 2 orhaneripe oci 55 May 31 15:03 .
drwxr-xr-x. 3 orhaneripe oci 109 May 26 12:55 ..
-rw-r--r--. 1 orhaneripe oci 854 May 26 12:57 known_hosts
-rw-------. 1 orhaneripe oci 1679 May 31 15:03 myKey
-rw-r--r--. 1 orhaneripe oci 405 May 31 15:03 myKey.pub
orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$


orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$ ls -la
total 16
drwxrwxrwx. 2 orhaneripe oci 55 May 31 15:03 .
drwxr-xr-x. 4 orhaneripe oci 4096 May 31 15:51 ..
-rwxrwxrwx. 1 orhaneripe oci 854 May 26 12:57 known_hosts
-rwxrwxrwx. 1 orhaneripe oci 1679 May 31 15:03 myKey
-rwxrwxrwx. 1 orhaneripe oci 405 May 31 15:03 myKey.pub
orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$ cat myKey.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQC03gucQokYYCMFxhdsq7OiaJFibOMEqOtRCXEfdUFw3+4Z/AwVMaKaP6E3K+N+uObv19hftQyFOWkp48EUjBlPtb5Hjz9B9jDmuqdfxlPOi8OamhVImQXc1+iHq57SFhH8cs9N8MuBppqRFKz2GPjy7+UVzJ3zvmICxTF4bMA7LOCvXQUCSjH5dyRNAsnFuYyhOZeWWwDoUn85+EAAxMhaOL65cfNiZgPI8YxRNYTXLBP9FdUicD2qeK0JU42nXhc7PgeNmzV85m9b9SHHYhdL7qlMBe4dFLsFoNKt6rH1eoBJb0K3oQLjPuSS71p1RN4OnFlgLyQWKnDmHmq9mGFX orhaneripe@a2b3e34e679d
orhaneripe@cloudshell:.ssh (eu-frankfurt-1)$

You can see available db patches and if you want to apply patch you can precheck.

You can add public ssh key after create VM DB system.

To run oci commands, we need to create API key and add it from Identity-> Users-> “Add Public Key”.

https://docs.cloud.oracle.com/en-us/iaas/Content/API/Concepts/apisigningkey.htm

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ openssl genrsa -out OCI_api_key.pem 2048
Generating RSA private key, 2048 bit long modulus
..................................................................+++
............................+++
e is 65537 (0x10001)
orhaneripe@cloudshell:~ (eu-frankfurt-1)$ chmod go-rwx OCI_api_key.pem
orhaneripe@cloudshell:~ (eu-frankfurt-1)$ openssl rsa -pubout -in OCI_api_key.pem -out OCI_api_key-pub.pem
writing RSA key
orhaneripe@cloudshell:~ (eu-frankfurt-1)$ ls -la
total 40
drwxr-xr-x. 3 orhaneripe oci 4096 May 31 15:48 .
drwxr-xr-x. 1 root root 35 May 31 14:43 ..
-rw-------. 1 orhaneripe oci 3898 May 31 15:48 .bash_history
-rw-r--r--. 1 orhaneripe oci 18 May 26 10:59 .bash_logout
-rw-r--r--. 1 orhaneripe oci 193 May 26 10:59 .bash_profile
-rw-r--r--. 1 orhaneripe oci 261 May 26 10:59 .bashrc
-rw-r--r--. 1 orhaneripe oci 334 May 26 10:59 .emacs
-rw-------. 1 orhaneripe oci 1675 May 31 15:48 OCI_api_key.pem
-rw-r--r--. 1 orhaneripe oci 451 May 31 15:48 OCI_api_key-pub.pem
-rw-------. 1 orhaneripe oci 1024 May 31 15:48 .rnd
drwx------. 2 orhaneripe oci 55 May 31 15:03 .ssh
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

user OCID: ocid1.user.oc1..aaaaaaaaazykusnrdfs6d7fez55wlnaftlinabsm4sy3kqzd6i22lkrt3a3a
tenancy OCID: ocid1.tenancy.oc1..aaaaaaaagamg2aagmpk5uwltpxvltcuku3goqbihanu5bphwpndsreue3saq
region: eu-frankfurt-1
Fingerprint: 85:87:d6:90:27:26:b2:89:85:92:a0:be:70:f0:a4:6c

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ oci setup config
This command provides a walkthrough of creating a valid CLI config file.

The following links explain where to find the information required by this
script:

User API Signing Key, OCID and Tenancy OCID:

https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#Other

Region:

https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm

General config documentation:

https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm


Enter a location for your config [/home/orhaneripe/.oci/config]: 
Enter a user OCID: ocid1.user.oc1..aaaaaaaaazykusnrdfs6d7fez55wlnaftlinabsm4sy3kqzd6i22lkrt3a3a
Enter a tenancy OCID: ocid1.tenancy.oc1..aaaaaaaagamg2aagmpk5uwltpxvltcuku3goqbihanu5bphwpndsreue3saq
Enter a region (e.g. ap-hyderabad-1, ap-melbourne-1, ap-mumbai-1, ap-osaka-1, ap-seoul-1, ap-sydney-1, ap-tokyo-1, ca-montreal-1, ca-toronto-1, eu-amsterdam-1, eu-frankfurt-1, eu-zurich-1, me-jeddah-1, sa-saopaulo-1, uk-gov-london-1, uk-london-1, us-ashburn-1, us-gov-ashburn-1, us-gov-chicago-1, us-gov-phoenix-1, us-langley-1, us-luke-1, us-phoenix-1): eu-frankfurt-1
Do you want to generate a new API Signing RSA key pair? (If you decline you will be asked to supply the path to an existing key.) [Y/n]: Y
Enter a directory for your keys to be created [/home/orhaneripe/.oci]: 
Enter a name for your key [oci_api_key]: 
Public key written to: /home/orhaneripe/.oci/oci_api_key_public.pem
Enter a passphrase for your private key (empty for no passphrase): 
Private key written to: /home/orhaneripe/.oci/oci_api_key.pem
Fingerprint: 85:87:d6:90:27:26:b2:89:85:92:a0:be:70:f0:a4:6c
Config written to /home/orhaneripe/.oci/config


If you haven't already uploaded your API Signing public key through the
console, follow the instructions on the page linked below in the section
'How to upload the public key':

https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How2

orhaneripe@cloudshell:~ (eu-frankfurt-1)$

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ cat ~/.oci/config
[DEFAULT]
user=ocid1.user.oc1..aaaaaaaaazykusnrdfs6d7fez55wlnaftlinabsm4sy3kqzd6i22lkrt3a3a
fingerprint=85:87:d6:90:27:26:b2:89:85:92:a0:be:70:f0:a4:6c
key_file=/home/orhaneripe/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaagamg2aagmpk5uwltpxvltcuku3goqbihanu5bphwpndsreue3saq
region=eu-frankfurt-1
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ cat OCI_api_key-pub.pem
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAshNtSVTmY+RDUK5iq/O6
A3+wlSAzy4G7jpVeRtNgYvClBKk6A+lc18DWgL0ubYCNrSln1N5TmT8zioZIPYJ/
iJF4Hlx6qVmw1A9brweCrC2IixwC4kqHH0wBzfgl0E0ogaKN/YZb0EpL+BXsD2eK
3c3+XRtizNhaX69F5vQjKSQvRofg2HWOVKshHj5CnnDrXligZeK9KU/YKr7zy8R8
LRdQc4n2sNc+bGeYdo/Vs3+pBV+eiFbXlsU/NO0BRkmvNdoRp4ya1XAvqvR8muWY
bV8ymTgLZX2N9ntZdfOdHs37khd6gxLDnkEONi5F53uRDb144XGNfeU3BoRER9K4
EwIDAQAB
-----END PUBLIC KEY-----
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

oci iam region list --output table
oci os ns get
oci iam compartment list

my compartment (comp1) OCI: ocid1.compartment.oc1..aaaaaaaahyy47qcb5bk7yqsdlqfmfkldpb7ko6ktfiupelrcnekqoxdf6iza

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ oci db system list -c ocid1.compartment.oc1..aaaaaaaahyy47qcb5bk7yqsdlqfmfkldpb7ko6ktfiupelrcnekqoxdf6iza --output table --query 'data[*].{ClusterName:"cluster-name", shape:shape}'
+-------------+----------------+
| ClusterName | shape |
+-------------+----------------+
| None | VM.Standard2.2 |
| None | VM.Standard2.1 |
+-------------+----------------+
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ oci compute instance list -c ocid1.compartment.oc1..aaaaaaaahyy47qcb5bk7yqsdlqfmfkldpb7ko6ktfiupelrcnekqoxdf6iza --output table --query 'data[*].{AD:"availability-domain", Name:"display-name", shape:"shape", region:region}'
+--------------------------+------+----------------+------------------------+
| AD | Name | region | shape |
+--------------------------+------+----------------+------------------------+
| tSVz:EU-FRANKFURT-1-AD-2 | ovm1 | eu-frankfurt-1 | VM.Standard.E2.1.Micro |
+--------------------------+------+----------------+------------------------+
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ oci db system launch --generate-full-command-json-input
{
"adminPassword": "string",
"autoBackupEnabled": true,
"availabilityDomain": "string",
"backupNetworkNsgIds": [
"string",
"string"
],
"backupSubnetId": "string",
"characterSet": "string",
"clusterName": "string",
"compartmentId": "string",
"cpuCoreCount": 0,
"dataStoragePercentage": 0,
"databaseEdition": "STANDARD_EDITION|ENTERPRISE_EDITION|ENTERPRISE_EDITION_HIGH_PERFORMANCE|ENTERPRISE_EDITION_EXTREME_PERFORMANCE",
"dbName": "string",
"dbUniqueName": "string",
"dbVersion": "string",
"dbWorkload": "string",
"definedTags": {
"tagNamespace1": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
},
"tagNamespace2": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
}
},
"diskRedundancy": "HIGH|NORMAL",
"displayName": "string",
"domain": "string",
"faultDomains": [
"string",
"string"
],
"freeformTags": {
"tagKey1": "tagValue1",
"tagKey2": "tagValue2"
},
"hostname": "string",
"initialDataStorageSizeInGb": 0,
"licenseModel": "LICENSE_INCLUDED|BRING_YOUR_OWN_LICENSE",
"maintenanceWindowDetails": {
"daysOfWeek": [
{
"name": "string"
},
{
"name": "string"
}
],
"hoursOfDay": [
0,
0
],
"leadTimeInWeeks": 0,
"months": [
{
"name": "string"
},
{
"name": "string"
}
],
"preference": "string",
"weeksOfMonth": [
0,
0
]
},
"maxWaitSeconds": 0,
"ncharacterSet": "string",
"nodeCount": 0,
"nsgIds": [
"string",
"string"
],
"pdbName": "string",
"shape": "string",
"sparseDiskgroup": true,
"sshAuthorizedKeysFile": "/path/to/file",
"storageManagement": "LVM|ASM",
"subnetId": "string",
"timeZone": "string",
"waitForState": [
"PROVISIONING|AVAILABLE|UPDATING|TERMINATING|TERMINATED|FAILED"
],
"waitIntervalSeconds": 0
}
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

orhaneripe@cloudshell:~ (eu-frankfurt-1)$ oci iam compartment list --output table --query 'data [*].{id:id}'
+-------------------------------------------------------------------------------------+
| id |
+-------------------------------------------------------------------------------------+
| ocid1.compartment.oc1..aaaaaaaahyy47qcb5bk7yqsdlqfmfkldpb7ko6ktfiupelrcnekqoxdf6iza |
| ocid1.compartment.oc1..aaaaaaaakznuwkbdbwtk4mtvw53klkgiyujq2tkouchwizp2jkkwmm7bf2cq |
+-------------------------------------------------------------------------------------+
orhaneripe@cloudshell:~ (eu-frankfurt-1)$

ssh -i <private_key> opc@<public_IP>
ssh -i ~/.ssh/myKey opc@193.122.59.123


[opc@ovmdb ~]$ sudo su - oracle
[oracle@ovmdb ~]$ . oraenv
ORACLE_SID = [CDB] ?
The Oracle base has been set to /u01/app/oracle
[oracle@ovmdb ~]$ sqlplus / as sysdba

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO