Wednesday, September 30, 2020

OCI Cloud shell: Connect ADW database

8:44 AM Posted by Dilli Raj Maharjan No comments


OCI Cloud Shell Overview.

OCI Cloud Shell is a  browser-based terminal accessible from the Oracle Cloud Console. It is free to use and provides access to a Linux shell. Oracle announced Oracle Cloud shell on Feburary 11, 2020. It contains a pre-authenticated OCI command line interface and a lot of other useful tools to perform OCI cloud related tasks. Cloud shell is a feature available to all OCI users that is accessible from the OCI console. It appears in the persistent frame of the console and will stay active while navigating to different pages of the console.

Cloud shell provides an ephemeral machine, that lasts for a short time. It acts as a host for a Linux shell with pre-configured latest version of the OCI command line interface and a number of useful tools. Cloud shell provides 5 GB of storage for our home directory and all the files will be saved during reboot of cloud shell. The storage for Cloud Shell VM's home directory remains persistent from session to session. The administrator of Cloud will receive notification that the storage will be removed in 60 days if the Cloud Shell is not used for 6-months. 

The resource name for Cloud Shell is `cloud-shell`. IAM Policy required to access cloud shell will be something like below

allow group <group name> to use cloud-shell in tenancy

For more details about cloud shell Please visit link below.

Accessing Cloud Shell in the Oracle Cloud Console.

On the top right corner of the console you will get the icon for OCI cloud shell. Click on it to access OCI Cloud Shell.

At the bottom of the OCI cloud console page, a new persistent frame will be available. If you are using cloud shell for the first time it will take few minutes to start. 

From the second time it will be quicker to load cloud shell. It is just provisioning VM and attaching and mounting the storage to home dir.

Once Oracle Cloud shell is connected it looks something like below.

Available buttons in Cloud Shell.

I read the redhat-release file and noticed operating system is RHEL 7.8

Checked disk details with df command and found home is 5 GB in size.

Checked location of utilities.

Following utilities are available with Cloud shell.

  1. Git
  2. Java
  3. Python (2 and 3)
  4. SQL Plus
  5. kubectl
  6. helm
  7. maven
  8. gradle
  9. terraform
  10. ansible

Create Autonomous Data Warehouse Database.

Open Oracle Cloud Console. Click on Create an ADW database on Quick Actions page.

Provide the name of Autonomous Database.

Scroll down and provide Admin user password. Click on Create Autonomous Database.

We can noticed that ADW is in PROVISIONING state.

Once provisioning is completed, status will be changed to available. Click on DB Connection button.

Click on Download Wallet button.

Provide the wallet login password and click on Download.

Create Object Storage to temporarily upload wallet and download to OCI cloud shell.

Click on Navigation menu. Click on Object Storage and click on Object Storage.

Click on Create Bucket.

Provide the name of Bucket.

Click on Create Bucket button to create bucket.

Upload Wallet file to the bucket.

Click on three vertical dots at the right and click on Create Pre-Authenticated Request.

Provide the name of the Pre-Authenticated Request and click on Create Pre-Authenticated Request button.

Copy PRE-Authenticated request URL

Go to Cloud Shell and download the wallet with wget command.

It looks something like below once wallet is downloaded completely.

Create directory to store wallet files. Move wallet files to the directory and extract it.
mkdir -p network/admin
mv network/admin
cd network/admin

Open sqlnet.ora file and modify the Directory value to the directory location where wallet files are located.

Connect to ADW database using sqlplus <username>@<tnsnames>.

Saturday, August 15, 2020

Backup Oracle database to OCI Object storage.

4:54 AM Posted by Dilli Raj Maharjan , 1 comment


        Recovery Manager (RMAN) is an Oracle Database client to perform Oracle database backup and recovery. It is used to automate the administration of our backup strategies and greatly simplifies backing up, restoring, and recovering database files. RMAN connects and authenticates to a database in the same way as SQL*Plus connections to a database. The RMAN client is started by issuing the rman command at the command prompt of your operating system. If a database runs in ARCHIVELOG mode, then we can back up the database while it is open. If the database is not in ARCHIVELOG mode , database needs to stop to create a consistent backup. If the database is backed up during the database is running, the backup is called an inconsistent backup and redo is required during recovery to bring the database to a consistent state. Use the BACKUP command to back up files. Oracle RMAN has a lot of features to backup and restore the database. 

     The Oracle Cloud Infrastructure Object Storage service is an internet-scale, high-performance storage platform. It offers reliable and cost-efficient data durability. The Object Storage service can store an unlimited amount of unstructured data of any content type as analytic data, images, videos, backups, and a lot. Object storage is considered as hot storage. It can be used for data that needs fast, immediate, and frequent access. Data accessibility and performance justifies a higher price to store data in the Object Storage tier. Object Storage can be used to preserve backup and archive data. 

Components of Object storage.
Object Storage Features
    We can directly backup our Oracle database using RMAN to Object Storage. Backups in Object Storage are durable, secure, and efficient. Following is the step by step guide to add opc driver to backup database to OCI object storage.

Create an OCI bucket to store RMAN backup.

Click on Navigation Menu > Object Storage > Object Storage

On the landing page, click on Create Bucket to create new bucket.

Provide the name of Bucket, leave everything default and click on Create Bucket.

Click on Bucket name to upload a file. The Object storage URL is required to install oci. Upload a file and view the Object Detail.

Click on Upload.

Click on Select files. Once the file is selected the name of file will be displayed as below. In my case, I am going to upload Screenshot. Click on Upload to being upload.

Once the upload is completed, we can view status as Finished. Click on Close.

Click on three vertical dots at the right of the object.

Click on View Object Details to view the details of the object.

Now we can see the URL Path, Please note the URL Path. URL path is in the syntax https://objectstorage.<region_name> For my case

Create API key and add key to OCI.

Create a directory to store API key.
mkdir -p /home/oracle/oci/keys
cd /home/oracle/oci/keys

Execute openssl command to create a private API key.
openssl genrsa -out oci_api_key.pem 2048

Execute openssl command to create a public API key.
openssl rsa -pubout -in oci_api_key.pem -out oci_api_key_public.pem

Copy the content of the Public API key
cat oci_api_key_public.pem

Click on the profile icon. Click on username.

Click on API Keys option under Resources in bottom left.

On API Keys page click on Add Public Key button.

Click on PASTE PUBLIC KEYS, Paste the key in the text area, and click on Add to add public keys.

Note the Fingerprint value. This value will be required to install OCI library.

Install OCI library to backup database to OCI object storage.

Download file from URL Once the installer file is downloaded, unzip it.

Check if Java is installed in your machine. Make sure java is installed and the version is greater than or equal to 1.7.
which java
java -version

Create directories to store library files and encryption key files.
mkdir /home/oracle/oci/lib
mkdir /home/oracle/oci/wallet

Install OCI using command below. Please provide the config file location. This location can be any and all the details are written on the configFile. This file will be used while defining the SBT channel inside RMAN. Please note OCI tenancy ocid, OCI user ocid, Fingerprint value,  API key file location, URL of the object storage, and name of the bucket. Provide all these details. Use the lib directory and wallet directory created in the earlier section.

java -jar oci_install.jar \
  -configFile /home/oracle/oci/configfile.ora \
  -host \
  -pvtKeyFile /home/oracle/oci/keys/oci_api_key.pem \
  -pubFingerPrint 22:97:d0:5b:50:00:00:00:00:00:00:00:00:11:da:78 \
  -tOCID ocid1.tenancy.oc1..********************************l7ygvujdqjkaq \
  -uOCID ocid1.user.oc1..**********************************lyz3uo2jqq \
  -libDir /home/oracle/oci/lib \
  -walletDir /home/oracle/oci/wallet \
  -bucket RMAN_BACKUP

Check library and wallet directory content to make sure library files and encryption wallet key files are placed under them.
cd /home/oracle/oci/lib
cd ../wallet

Check the library file and the configuration file. These files are used while defining SBT driver.

Configure RMAN channel and start database backup.

Now login to rman.
rman target /

Verify existing configuration.
show all;

Configure channel from sbt device with command below.
configure channel device type sbt parms='SBT_LIBRARY=/home/oracle/oci/lib/, SBT_PARMS=(OPC_PFILE=/home/oracle/oci/configfile.ora)';

Backup database using the command below.
backup device type sbt database;

Once backup is completed, it looks something like below. 

Restore database from OCI object storage.

In my case, I have deleted all the datafiles. I have mounted database. Connect rman target.
rman target /

Check the current configuration.
show all;

Configure channel for SBT
configure channel device type sbt parms='SBT_LIBRARY=/home/oracle/oci/lib/, SBT_PARMS=(OPC_PFILE=/home/oracle/oci/configfile.ora)';

If decryption key is not provided, then error will be displayed. ORA-19913: unable to decrypt backup

Provide a decryption key.
set decryption identified by "N01_cAn_FIND";

Execute the restore and recover the database. Once it is complete we can notice the Finish restore and Finish recover message.
restore database;
recover database;

Open database with command below
sql 'alter database open';