Oracle Cloud

Connect to Autonomous Database using SQLPlus

Connect to Autonomous db using sqlplus

In this post I will describe how to connect to Autonomous Database using SQL*PLUS

Steps: Connecting to Autonomous Database using SQL*PLUS

To connect to Autonomous database we need to download the client credentials wallet

  1. From Autonomous database details page: Click on DB Connection

2. Select Download Wallet

3. Enter the credentials to protect wallet and Click on Download, once downloaded close the window.

Connect using SQL*PLUS

  1. Unzip the credentials on the machine where Oracle client is installed
[oracle@ol7-db wallet]$ pwd
/u01/wallet
[oracle@ol7-db wallet]$ ls -ltrh
total 24K
-rw-r--r--. 1 oracle oinstall 21K Mar 31 23:13 Wallet_orbgadb.zip
[oracle@ol7-db wallet]$ unzip Wallet_orbgadb.zip
Archive:  Wallet_orbgadb.zip
  inflating: README
  inflating: cwallet.sso
  inflating: tnsnames.ora
  inflating: truststore.jks
  inflating: ojdbc.properties
  inflating: sqlnet.ora
  inflating: ewallet.p12
  inflating: keystore.jks
[oracle@ol7-db wallet]$

2. Edit the sqlnet.ora and specify the correct wallet location
In my case Wallet unzipped location is : /u01/wallet

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

To

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u01/wallet")))
SSL_SERVER_DN_MATCH=yes

3. Set the TNS environment variable and verify the connection

export TNS_ADMIN=/u01/wallet
tnsping orbgadb_low
sqlplus admin@orbgadb_low

Output:

[oracle@ol7-db wallet]$ export TNS_ADMIN=/u01/wallet
[oracle@ol7-db wallet]$
[oracle@ol7-db wallet]$ tnsping orbgadb_low

TNS Ping Utility for Linux: Version 18.0.0.0.0 - Production on 31-MAR-2021 23:45:35

Copyright (c) 1997, 2018, Oracle.  All rights reserved.

Used parameter files:
/u01/wallet/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.me-dubai-1.oraclecloud.com))(connect_data=(service_name=atrc9lotdxsjqrj_orbgadb_low.adb.oraclecloud.com))(security=(ssl_server_cert_dn=CN=adb.me-dubai-1.oraclecloud.com,OU=Oracle ADB DUBAI,O=Oracle Corporation,L=Redwood City,ST=California,C=US)))
OK (150 msec)
[oracle@ol7-db wallet]$
[oracle@ol7-db wallet]$
[oracle@ol7-db wallet]$ sqlplus admin@orbgadb_low

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Mar 31 23:45:44 2021
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Wed Mar 31 2021 23:24:18 +04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> 

Hope this helps…

Connect to Autonomous Database using SQL Developer

Connect to Autonomous Database using SQL Developer

In this post I will describe how to connect to Autonomous Database using SQL Developer

Steps: Connecting to Autonomous Database using SQL Developer

To connect to Autonomous database we need to download the client credentials wallet

  1. From Autonomous database details page: Click on DB Connection

2. Select Download Wallet

3. Enter the credentials to protect wallet and Click on Download, once downloaded close the window.

Connect using SQL Developer

Open SQL Developer and create a new connection using Cloud Wallet

Once the connection is Success, click on Connect

Hope this helps…

Autonomous Database creation on Oracle Cloud

Oracle Autonomous database creation

In this post I will describe the step by step creation of Oracle 19c Autonomous Transaction Processing Database in Oracle Cloud using OCI Console.

Oracle Cloud Free Tier will allows you to sign up for an Oracle Cloud account which provides a number of Always Free services and a free credit of 300$ which can be use on all eligible Oracle cloud Infrastructure services for up to 30 days. The Always Free services are available for an unlimited period of time. Kindly click here to know more in detail.

Refer the below article to get the step by step instruction on how to setup Network components and for creation of Virtual Machine on OCI Cloud
Step by step creation of Oracle cloud free tier account and creating a VM

The following steps will be covered in this article:

  1. Creation of Autonomous Database.
  2. Connecting to Autonomous Database.

Creation of Autonomous Database:

  1. Open the navigation menu. Under Core Infrastructure, click on Autonomous Transaction Processing.

2. Click on Create Autonomous Database

3. In Create Autonomous Database section Enter the below details:
Display Name, Database Name

Choose workload type: Transaction Processing
Deployment type: Shared Infrastructure

Choose the Database version, OCPU Count and Storage(TB)
Auto scaling should be enabled

Provide the Administrator Credentials

Choose the Access type & license type
Finally Create Autonomous Database

You can see the Provisioning the ATP database has started

After few minutes the ATP database is up and running

Connecting to Autonomous Database

To connect to Autonomous database we need to download the client credentials wallet

  1. From Autonomous database details page: Click on DB Connection

2. Select Download Wallet

3. Enter the credentials to protect wallet and Click on Download, once downloaded close the window.

Connect using SQL Developer

Open SQL Developer and create a new connection using Cloud Wallet

Once the connection is Success, click on Connect

Detaching a block volume from an instance in Oracle cloud

Oracle cloud detach block volume

In this article I will describe how to detach a block volume from an Oracle cloud Linux instance We will be covering the following steps:

  • Unmount the mount-point from the Linux Instance
  • Disconnect the Block volume from the instance
  • Detach the Block volume from the Instance
  • Delete the volume from Cloud console

1. Unmount the disk from the Linux Instance

Connect to the linux instance and run the umount command to unmount the filesystem with has to be deleted
umount /oracle

2. Disconnect the Block volume from the instance

Once the disk is unmounted, access the cloud console and copy the commands to disconnect the block volume from the instance:
Goto Attached block volumes > Click on ellipsis(…) and select iSCSI Commands & Information

Copy the COMMANDS FOR DISCONNECTING

Connect the VM as root user and run the commands

3. Detach the Block volume from the Instance

Once the Block volume is detached. Access the cloud console, navigate to Instances page and click on Instance name (oc-ol7 here)

Under Resources select Attached Block Volumes and click on the ellipsis(…) then select Detach

Click on Continue Detachment

Click ok

Now we can see the Volume State is Detaching.

4. Delete the volume from Cloud console

Once the volume is Detached, Open the navigation menu. Under Core Infrastructure, goto Block Storage and Click on Block Volumes

In the block volume section click on the ellipsis(…) on which the volume you want to delete, then click Terminate and confirm the selection when prompted.

Now the block volume is terminated successfully.

Attaching a block volume to an instance in Oracle cloud

Oracle cloud Attach block volume

In this article I will describe how to create a block volume and attaching it to an instance in Oracle cloud. We will be covering the following sections:

  1. Creating Block Volume
  2. Attaching the Block Volume to the Linux VM Instance
  3. Format and Mount the volume on the instance

1. Creating Block Volume

Open the navigation menu. Under Core Infrastructure, go to Block Storage and click Block Volumes

Select the root compartment and click on Create Block Volume

Provide the Name and Volume size and Performance, then click on Create Block Volume

The Block volume is successfully created

2. Attaching the Block Volume to the Linux VM Instance

Open the navigation menu. Under Core Infrastructure, go to Compute and click Instances

Click on the instance name

Under Resources select Attached Block Volumes and click on Attach Block Volume

Choose Volume attachment type: ISCSI
Select the volume which created in the previous section
Select the Device Path, then click on Attach

Block Volume is successfully attached

Click on the ellipsis(…) and select iSCSI Commands & Information

Click on copy to copy the COMMANDS FOR CONNECTING

Connect the Linux VM instance as root user and run the iSCSI commands to connect the block volumes

3. Format and Mount the volume on the instance

Verify the newly added disk using fdisk command

Format the newly added Disk /dev/sdb

Verify the new partitioned disk /dev/sdb1

Format and Mount the disk

Create directory “oracle” and mount the newly created disk

 

Step by step creation of Oracle cloud free tier account and creating a VM

Oracle Cloud free tier account creation

Hello Friends!

This is my first blog post and I hope you will ignore mistakes. Thank you in advance.

Description:

In this blog post I will describe the step by step creation of Oracle cloud always free account and creating a Linux Virtual Machine.

Oracle Cloud Free Tier will allows you to sign up for an Oracle Cloud account which provides a number of Always Free services and a free credit of 300$ which can be use on all eligible Oracle cloud Infrastructure services for up to 30 days. The Always Free services are available for an unlimited period of time. Kindly click here to know more in detail.

We will be covering the following steps:

  1. Create a free Oracle account
  2. Sign into your account
  3. Network Configuration
    • Create Virtual Cloud Network (VCN)
    • Configure Internet Gateway
    • Adding Route table
    • Adding Security list
  4. Creation of Oracle Linux Virtual Machine Instance
  5. Accessing the Linux Virtual Machine Instance

1. Create a free Oracle trial account

Open the web browser to access the Oracle Cloud account registration form

Choose your country and enter your name and email address
Once provided the details, check your email. You will see an account validation email from Oracle.

Enter the account information.
Note: Home Region cannot be changed once you sign-up.

Enter the address information, mobile details & Payment details and verify the same
Note: You need to provide the credit card details and 1$ will be charged for the verification and the same will be reserved. You will not be charged unless you elect to upgrade the account.

2. Sign into your account

Go to cloud.oracle.com and click on Sign into cloud

Enter you cloud account name and click next. This is the name we choose during the account creation

Enter the cloud account credentials and click next. Username is your email address.

You are now login to your cloud account

3. Network Configuration

Create Virtual Cloud Network (VCN)

Open the navigation menu. Under Core Infrastructure, go to Networking and click Virtual Cloud Networks

Select the compartment you want to create the network. Then click on Create VCN

Enter a name for the VCN
For CIDR Block, I choose IP 192.168.10.0/24, so that I can use the private IP range b/w 192.168.10.1 to 192.168.10.254.

Now the VCN status is available

Click on VCN Name (ocvcn) and you will be redirected to VCN Details page. Under Resource select Subnets and then click Create Subnet

Enter the information as per the below screenshot and click on Create Subnet.
Note: Public subnet has selected to access the instance in public n/w.

The subnet is now created

Configure Internet Gateway

From VCN Details page under Resources, click on Internet Gateways

Give it a name and choose the compartment, then click on Create Internet Gateway

Your internet gateway is created. but you still need to add a route rule that allows traffic to flow to the gateway.

Adding Route table

From VNC Details page, Under Resources –> Route Tables, click on the route table associated with your public subnet.

Click on Add Route Rules

This is for the public subnet.
select Target Type as Internet gateway
Destination CIDR Block: 0.0.0.0/0 (This means all the internet)
Once ready, click on Add Route Rules

Now you can see the Internet gateway route has been successfully added

Adding Security lists

Check your public IP using the URL and whitelist it to access the cloud VM which you will be creating in the next steps

From Subnet Details page, Under Resources, select Security Lists and click on the Default Security List as shown below

Click on Add Ingress Rules

Source Type: CIDR
Source CIDR: <Your Public IP>/32
IP Protocol: TCP; Source Port Range: All; Destination Port Range: All
Once ready, Click on Add Ingress Rules

Now you can see your my Public IP is whitelisted

Creation of Oracle Linux Virtual Machine Instance

Open the navigation menu. Under Core Infrastructure, go to Compute and click Instances.

Click on create instance

Name: Enter the name for the instance
Create in Compartment: Select the compartment to create the instance in
Availablilty Zone: Select the Availability domain that you want to create the instance
Image: Select the image, by default Oracle Linux 7.x is used to boot the instance
Shape: Select VM.Standard.E2.1.Micro, which is eligible for always free
Add SSH keys: select Generate SSH Key pair; Click Save Private Key and Save Public key on your computer.

In Management section leave the setting as default

In Networking section enter the private IP and Hostname of the VM. Then click on Create

Wait for the instance creation to complete

After a couple of minutes you can see your Instance is in running state

Accessing the Linux Virtual Machine Instance

Connect the newly created VM from the windows system using ssh.
Before connecting you must set the file permissions for the Private key so that only you can read the file. Do the following.
Reference: https://docs.cloud.oracle.com/en-us/iaas/Content/GSG/Tasks/testingconnection.htm

a. Navigate to <private key file path> which we saved during the VM creation, right-click the file, and then click Properties.

b. On the Security tab, click Advanced.

c. Ensure the owner is yours

d. Click Disable Inheritance, and then select Convert inherited permissions into explicit permissions on this object.

e. Select each permission entry that is not your user account and click Remove.

f. Ensure that the access permission for your user account is Full control and save the changes.

Now we are good to connect to the instance. Open windows command prompt and run the following command.

ssh -i “<private key path>” opc@<public-ip-address>

Thanks for reading my post…

Hope this helps..