Connect to Oracle Database System in Private Subnet Using OCI Connection

The Database Tools service allows you to create connections to Oracle database systems running in Oracle OCI. Connections work with Autonomous Database (ADB), Oracle Base Database (VM, BM, and Exadata DB Systems), and customer-managed Oracle databases running on OCI compute instances.

When an Oracle DB System (VM or BM) is configured to restrict network access using a private subnet, then a Database Tools private endpoint should be setup in a subnet such that network traffic can be routed from the Database Tools service to the target database.

Prerequisites:
- An Oracle cloud fee trial or paid account.
- OCI Virtual Cloud Network (VCN) with a private subnet.
- Oracle DB system (VM) located in a VCN’s private subnet.

Step #1: Create OCI Vault

Vaults let you centrally manage the encryption keys that protect your data and the secret credentials that you use to securely access resources.

1. Open the navigation menu, click “Identity & Security”, and then click “Vault”.

2. Click “Create Vault”.

3. In the “Create Vault“ dialog box, select the compartment where you want to put your vault and enter vault name, then click “Create Vault”.

4. Create Master Encryption Key: Click your vault name to view vault details. Under “Resources” section, click “Master Encryption Keys”, then click “Create Key”.

5. In “Create Key” dialog window, select the compartment where you want to put your and enter key name. Leave all other options using default values, then click “Create Key”.

6. Create vault’s secret: the secret will be used to store database’s user password. Click your vault name to view vault details. Under “Resources” section, click “Secrets”, then click “Create Secret”.

7. In “Create Secret” dialog window, select the compartment where you want to put your, enter secret name, select master key created in previous step, for “Secret Contents” enter database’s user password, then click “Create Secret”.

Step #2: Create Private Endpoint

Private endpoints allow Database Tools to access databases securely via private networks.

1. Open the navigation menu, click “Developer Services”, and then click “Private Endpoints”. 

2. Click “Create private endpoint”.

3. In “Create private endpoint” dialog window. Select/specify below options then click “Create”.

- Select the compartment where you want to put your private end point.
- Select “select database” option.
- For “Database cloud service” select “Oracle Base Database”.
- For “Database system” select DB system name from the drop list.
- For Subnet select private subnet name where the DB system is located.

Step #3: Create Database Connection

Connections are resources that contain the necessary information for accessing an Oracle Database in Oracle Cloud Infrastructure. Along with information about the database, the connection also contains the user used to connect to the database as well as the location of the password that is stored in the Oracle Cloud Infrastructure vault. Other connection details like the JDBC string and if the connection uses a private endpoint are also stored. 

The database connection will be linked to the private endpoint created in step #2. 

1. Open the navigation menu, click “Developer Services”, and then click “Connections”.

2. Click “Create connection”.

3. In “Create connection” dialog box, select/specify below options then click “Next”.

- Enter connection name.

- Select the compartment where you want to put your connection.

- Select “Select database” option.

- For “Database cloud service” select “Oracle Base Database”.

- For “Database system” select DB system name from the drop list. Database and oracle home will automatically be populated.

- Optionally select “Pluggable database” name from the list if you want to connect to a PDB.

- Enter “Username” and select database user’s “Role” type.

- Select “user password secret”. Select vault’s secret created in step #1 for the database user entered.

Keep wallet format to none and click “Create”.

Step #4: Connect to DB System from SQL Worksheet Service

Use the new connection created in step #3 to connect to Oracle database system from SQL Worksheet Service.

1. In “Database Tools” scree, click “SQL Worksheet”.

2. Select a database connection: select the compartment where the connection has been located and the connection which has been created in step #3 form the list of available connections.

Now we can run SQL commands on Oracle DB system located in private subnet from OCI SQL Worksheet.

 

-

Attach Oracle Cloud Block Volume to OCI Compute Instance

Block Volume provides network storage to use with your Oracle Cloud compute instances. After you create, attach, and mount a volume to your instance, you can use it just as you would a physical hard drive on your computer. A volume can be attached to a single instance at a time, but you can detach it from one instance and attach to another instance, keeping your data intact.

In this blog, we will cover the steps to attach Block Volume to an existing OCI compute instance.

Prerequisites:

- An Oracle cloud fee trial or paid account.

- OCI Virtual Cloud Network (VCN).

- OCI compute instance located in a VCN’s private subnet or public subnet with API RSA private key.

Step #1: Create a Block Volume

1. Open the navigation menu and click “Storage”. Under “Block Storage”, click “Block Volumes”.

2. Click “Create Block Volume”.

3. In the “Create block volume” dialog window, enter volume name, select compartment and availability domain. Under “volume size and performance” section, select “custom” option and specify block volume size. Leave all other options as default and click “Create Block Volume”.

Once you create volume, new block volume information will be as shown below.

Step #2: Attach Volume to OCI Compute Instance

1. Enable Block Volume Management Cloud Agent: Open the navigation menu and click Compute. Under Compute, click Instances. Click your compute instance name to view its details. Navigate to “Oracle Cloud Agent” tab page and enable “Block Volume Management” plugin. 

2. Open the navigation menu and click “Storage”. Under “Block Storage”, click “Block Volumes”. Click your block volume name to view its details. Under “Resources” section, click “Attach Instances”, then click “Attach to Instance”. 

3. In “Attach to instance” dialog window, select “Attachment type” – ISCSI (default), “Access Type” – Read/Write (default). Under “Instance” section, select compute instance tick option “Use Oracle Cloud Agent to automatically connect to ISCSI-attached volumes”, then click “Attach”. 

Note: block volume will be automatically attached to compute instance, so there is no need to run ISCSI Commands.

Step #3: Format and mount Block Volume from within Compute Instance

1. SSH to your compute instance (you can use cloud shell to access the instance). Run “lsblk” Linux command to get the list of volumes/devices. In this example, the device path is “/dev/sdb”.  

2. Create device new partition using “fdisk” Linux command as shown below.

3. Format new device partition using “mkfs” Linux command as shown below.

4. Mount new partition using “mount” Linux command as shown below.

5. Permanently mount the device by adding below entry to “/etc/fstab” file.

Now block volume is permanently available in OCI compute instance for read/write access!

Access OCI Compute Instance in Private Subnet with OCI Cloud Shell

There are several options for securely accessing OCI compute instances located in a VCN’s private subnet. The efficient option is the OCI’s Bastion service. Another secure and faster option is using Oracle OCI Cloud Shell. In this blog, we will cover the steps to use OCI Cloud Shell.

Prerequisites:

- An Oracle cloud fee trial or paid account.

- OCI VCN with private subnet.

- OCI compute instance located in a VCN’s private subnet with API RSA private key.

The Oracle Cloud Shell is a web browser-based terminal in the OCI Console that provides access to a Linux shell, with a pre-authenticated OCI Command Line Interface (CLI). It includes a Network Private Access feature. This feature allows you to create an endpoint in private subnet. This endpoint is governed by the rules in the private subnet’s Security Lists.

Restrictions:

- This feature is supported only in the tenancy’s home region. However, with Regular Remote Peering connections you can extend the accessibility to other regions. 

Step #1: Launch Cloud Shell and configure the Private Network Access

1. Navigate to "Cloud Shell" under 'Developer tools' next to your Home Region.

2. In the Cloud Shell menu, “Network:Public” > “Private Network Definition List”.

3. In “Private Network Definition List” screen, click “Create Private Network Definition”.

4. In “Create Private Network Definition” screen, provide a name, select VCN and private subnet where OCI compute instance is located, then click “Create”.

Now, OCI Cloud Shell is connected to private subnet using new created private network definition.

Step #2: Connect to OCI Compute Instance

1. In Cloud Shell command line, change directory to “.ssh”, create a file and past the contents of compute instance’s API private key.

2. SSH to compute instance using compute instance private IP.

You are now connected to the Compute Instance in the Private Subnet!

MySQL: Migrate MySQL to OCI MySQL Heatwave - Part 2/3

In previous blog post "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 1/3", we came over the steps to install MySQL on OCI compute instance. 

In this blog post, we will cover the steps to dump MySQL data into OCI object storage.

Prerequisites

- An Oracle cloud fee trial or paid account.

- MySQL server installed on OCI compute instance. Kindly complete previous blog "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 1/3".

Step #1: Add OCI user profile API Key

1. Sign in to your Oracle Cloud account. Navigate to the “Profile” icon on the top-right. From there click on “User settings”.

2. Under “Resources” section, click on “API Keys” then click “Add API Key”.

3. When you click on 'Add API Key' a popup will appear saying "Add API Key". On that popup, select ‘Generate API Key Pair’ and download both the “Private Key” and “Public Key”. Afterwards, click “Add”.

4. Once you ‘Add’ the API Key, a new popup will appear saying “Configuration File Preview”. Copy the contents to a file and click 'Close' afterwards to exit out of the Configuration File Preview.

Step #2: Setup the “config” file in the OCI compute instance

1. SSH to OCI compute instance using Cloud Shell tool. Paste the 'Configuration File Preview' contents from last step in a ".oci" directory.

ssh -i .ssh/id_rsa opc@192.18.157.156

Where: 192.18.157.156 is compute instance public IP

2. Copy the contents of the private key downloaded from previous step into file “/home/opc/.oci/privapikey.pem” on the compute instance.

Step #3: Create OCI Object Storage

1. Navigate back to Oracle cloud console and create object storage bucket. On the Buckets page, make sure you have the right Compartment selected, then click “Create Bucket”.

2. Name the bucket “MySQL-Bucket”, keep the ‘Default Storage Tier’ to “Standard” and click "Create".

3. Click on the Bucket Name and note down the “Bucket Name” as well as “Namespace”. 

Step #4: Perform the MySQL Shell Dump

1. Navigate back to Cloud Shell window, where you are already ssh into the compute instance, and login to MySQL database instance using MySQL Shell.

2. Make sure you are in ‘JavaScript’ mode of MySQL Shell by executing “\js” and perform the command “util.dumpInstance()” to export the dump data into Oracle Cloud Object Storage bucket. The util.dumpInstance() command will take a dump of all the databases except “mysql, sys, performance schema, and information schema”. The dump comprises of DDL files for the schema structure and tab-seperated .tsv files containing the actual data.

Note: “sampledump” is the prefix under which all our dump files will be stored in Object Storage. Change the ‘osBucketName’ and ‘osNamespace’ to match with what you have. “ocimds”: “true” option ensures compatibility of the dump with MySQL Database Service/HeatWave.

util.dumpInstance("sampledump", {"osBucketName": "MySQL-Bucket", "osNamespace": "yza00k7tuks0", "ocimds": "true", "compatibility": ["strip_restricted_grants", "strip_definers"], users: "true", dryRun:"false"})

3. Once the dump is complete, navigate back to Oracle Cloud and to “MySQL-Bucket” Object Storage bucket. Check to see if you see your files under “sampledump” from the util.dumpInstance().

Next blog "MySQL: Migrate MySQL from on-premises to OCI MySQL Heatwave - Part 3/3", we will cover Create MySQL Heatwave System.