Oracle Linux

Oracle 11g – let’s move that old Stuff to the Oracle Cloud

This blog post describes the lift and shift of an on-prem Oracle 11g Enterprise Edition to Oracle Cloud Infrastructure by using Oracle RMAN paired with OCI Object Storage. Works for other versions > Oracle 11g / Enterprise Edition too (the tablespace encryption method may change).

Architecture

Q&A

What’s the motivation of the lift and shift of a good old on-prem Oracle 11g database to Oracle Database Service?

  • Hardware runs out of lifecycle
  • Out scoping of an on-prem database which is only used for several times to compare old data
  • Regulatory, for example we have the keep and access the data for 10 yrs
  • Changing from on-prem licenses to the license included model
  • Upgrade tests for 19c and convert to Multitenancy Architecture
  • Part of company’s cloud strategy

Why using the OCI database service instead of a cheaper compute instance?

Only the database service allows tablespace encryption without any additional costs in the license included model. From my point of view, this is a must when running Oracle databases outside of the on-prem datacenter in any cloud. And for this case, 11g to 11g, it fit’s best without any configuration overhead. And finally, the root OS access gives me flexibility.

Are the other methods available than using RMAN backup/restore/recovery with the Oracle Database Backup Service?

Sure, according the Oracle docs:

  • Golden Gate
  • Data Transfer Service
  • Oracle Data Pump
  • Database Migration Service
  • RMAN Transportable Tablespaces

Why I like the Oracle Database Backup Cloud Service?

  • The on-prem RMAN backups have to be encrypted, no encryption, no Database Backup Service.
  • The configuration of the Backup Service Module is not really complicated, well documented and can be used 1:1 in on-prem and in the cloud. For 11.2.0.4,I need to install on-prem a patch to encrypt the backups (18339044 – RMAN-06770: backup encryption requires Enterprise Edition).
  • The backup configuration can be done in advanced without any pressure, the target database can be recovered at any time. This reduces once the restore is done the downtime when the final move should be done. Backing up an Oracle database by RMAN locally and to the cloud can co-exist without any problems.
  • The backup is stored in the Object Storage. With a replication policy, I can rebuild a new database in another region in an easy way.
  • Oracle Database Backup Module allows proxy configurations to backup to the cloud.

Links and My Oracle Support Notes

Migration Steps

  1. Create OCI Object Storage
  2. Configure on-prem database to use the Oracle Database Backup Service
  3. Create a new OCI Database Virtual Machine
  4. Configure OCI cloud database to use the Oracle Database Backup Service
  5. Clean up OCI cloud database
  6. Restore OCI cloud database from Object Store
  7. Encrypt tablespace

1. OCI Object Storage

A Object storage bucked called onprem-bucket is created in region eu-zurich-1:

 

2. Configure on-prem database to use the Oracle Database Backup Service

The installation of the Backup Service is described in this link here, you can download the OCI Backup Service Module: www.oracle.com/technetwork/database/availability/oracle-cloud-backup-2162729.html

On-Prem Database – Specifications

  • 11.2.0.4 Enterprise Edition
  • Database name DB11
  • Oracle Linux 7.9
  • About 60GB size
  • Oracle Backup Service to Object Storage enabled and configured
  • Regular RMAN inc0, inc1c and arc backups to the Cloud
  • Enabled RMAN compression and encryption (for the usage of the Backup Service the license is included)
  • Backup encrypted by password

On-Prem Database – Tablespaces and Datafiles

On-Prem Database – RMAN Backup to Oracle Cloud Infrastructure

Example RMAN Output where the media is the Oracle Cloud Infrastructure Object Storage in Zurich / Switzerland.

 

3. Create a new OCI Database Virtual Machine

A new 11g Oracle Database virtual machine is created, license included. 11g is not available for Logical Volumes unfortunately, therefore we need to use Grid Infrastructure.

Cloud Database – Specifications

  • 11.2.0.4 Enterprise Edition Database Virtual Machine
  • Oracle Linux 7.9
  • Grid Infrastructure (11g is not available for Logical Volumes unfortunately)
  • Database Name is DB11
  • Connected from the on-prem data center a) by VPN or b) by SSH-Tunnel via Bastion Host

Cloud Database – Controlfile, Tablespaces and Datafiles

Login as OS user opc, sudo to oracle and set environment.

Login as SYSDBA.

Query controlfile information.

Query datafile information. The existing datafiles will be dropped later before the restore starts.

Query tablespace information for encryption, the USERS tablespace is already encrypted. Encryption of SYSTEM, SYSAUX etc. is introduced in a later Oracle version and not available in 11g.

4. Configure OCI Cloud Database to use the Oracle Database Backup Service

Note: When the database is located in a VCN private subnet, to install and using the Oracle Database Backup Service module you have to configure these two resources:

  • Service Gateway for Object Storage access
  • NAT Gateway used by the Database Backup Service Module Library installation routine

For the installation the SSH private key from the existing on-prem Database Backup Service configuration is required and has to be transferred to cloud server. The fingerprint of the public ssh key is required.

A list of object storage endpoints is avaliable here: Object Storage Service API | Oracle Cloud Infrastructure API Reference and Endpoints

Cloud Database – Oracle Database Backup Service Installation and Configuration

Login to cloud virtual machine and sudo to OS user oracle

Create directory for the Database Backup Module installation, transfer and extract it.

Create directories for wallet and library.

Transfer the SSH key from on-prem to the cloud virtual machine into oci_wallet directory.

Install Oracle Backup Service Module, use the private key and public fingerprint from the on-prem installation. Set environment and start the installer. Use the bucket name from OCI Object Storage bucket which you have created first.

Installer Output.

A new parameter file is created which contains the OCI Object Storage information. This configuration file is used later in RMAN.

5. Clean up OCI Cloud Database and restart NOMOUNT

Login to cloud database virtual machine as OS user grid.

Set environment to database DB11.

Shutdown the database instance by using the database unique name.

Error message when environment for +ASM is set:

ASM Cleanup with environment +ASM – you have to add ORACLE_HOME manually

Login in ASM and remove existing controlfiles, datafiles, tempfiles and directories.

Set environment to DB11.

Start database NOMOUNT.

6. Restore OCI cloud database from Object Store

Restore and recovery of the database, the database has same name as the on-prem database. The datafile is migrated from file system to ASM. Required information for restore and recovery:

  • On-prem database DBID
  • Encryption password

Cloud Database – Login in RMAN and set Decryption Password set Source DBID

Cloud Database – Restore Controlfile from Object Storage

Use the library path and the path to the configuration file (OPC_FILE) properly.

Cloud Database – Mount Instance

Cloud Database – Restore Instance

Allocate channel for maintenance first.

Start restore.

Recover database – ignore the last line of the incomplete recovery.

Cloud Database – Open RESETLOGS

7. Encrypt Tablespace

Login as OS user oracle / SYS as SYSDBA to verify the existing situation.

Cloud Database – Verify Tablespaces

Cloud Database – Tablespace Encryption

We use the existing wallet and add a new TDE master key to the configuration. Show parameter for tablespace encryption.

Verify existing actual encryption situation, no tablespaces are encrypted.

Take user tablespaces offline – a small syntax provider script.

As SYS AS SYSDBA, set new Master Key

Encrypt tablespaces – a small syntax provider script.

Take encrypted tablespaces online, the encryption starts and the taking online action needs some time (depends on CPU and I/O).

A few minutes later, the user tablespaces are shown as encrypted.

Verify encrypted tablespaces by DBVerify – as you can see here, Total Pages Encrypted is shown.

Summary and what’s next

Migration of an on-prem database to Oracle Cloud Infrastructire by RMAN and Object Storage is a very nice method to bring not only older databases into the Oracle Cloud Infrastructure. Once there, you can leverage of OCI features like Data Safe, monitoring, backup to Object Storage and many more.

#ilikeit

MV2OCI – One-Click Move of your Data into Oracle Cloud Infrastructure Database

mv2oci is a tool which helps to migrate on-premise data to the Oracle Cloud Infrastructure based on Oracle Data Pump and works as a data load tool. The local Data Pump export is transferred and imported to/on the target cloud server automatically. There is no use of Oracle Cloud Object Storage, the dump files are transferred with rsync or scp to the target database node. This is the different behavior to mv2adb – see my blogpost here – which uses the Object Storage. As an option, the data can be transferred via Database Link (mv2oci Parameter –netlink).

All you need to know about mv2oci is written in the My Oracle Support Note (OCI) MV2OCI: move data to Oracle Cloud Database in “one-click” (Doc ID 2514026.1).  The newest version of the rpm package can be downloaded there. The package has to be installed on the source server.

Prerequisites

  • SQL*Net connection between the two databases
  • A Java executable – in my case I have installed jre (yum install jre)
  • Verify if the firewall to the VCN Subnet is open for Port 1521 – Port 22 is open as per default
  • Password of database user SYSTEM

The Use Case

Let’s move the database schema SOE from my on-premise Oracle Linux Server into the cloud step-by-step. An Oracle Cloud Infrastructure database instance is already up and running, the target tablespace is created. The data centers are connect by VPN.

 

Database Information

Source Target
CDB Name CDB118 CDB118
PDB Name pdb11801 pdboci
Hostname heckenweg srv-cdb118
IP Address 192.168.1.184 172.16.0.8
PDB Service Name pdb11801.kestenholz.net pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com

1. Package Installation

Download and transfer the package to the on-premise server, for example in directory /tmp. As user root, install the package.

Verify that the SSH private key which is used for the connection to the Oracle Cloud Infrastructure server is available and the connection is working. Here is the OCI SSH key available in the $HOME/.ssh.

2. Encrypt the SYSTEM passwords for both databases – mv2oci encpass

3. Configuration File

A template of the configuration file is located in /opt/mv2oci/oci. I used the following parameters – other parameters like ICHOME for Instance Client configuration are well described.

Source DB Parameters

Parameter Value
DB_CONSTRING //heckenweg/pdb11801.kestenholz.net
SYSTEM_DB_PASSWORD Encrypted SYSTEM password
SCHEMAS SOE
DUMP_FILES /tmp/exp_soe_18102020_01.dmp, /tmp/exp_soe_18102020_02.dmp
OHOME /u01/app/oracle/product/19.0.0/dbhome_1

Expdp/Impdp Parameters

Parameter Value
Dump Name exp_soe_18102020.dmp
DUMP_PATH /tmp
PARALLEL 2 – creates two Dumpfiles called exp_soe_18102020_01.dmp and exp_soe_18102020_02.dmp

OCI Parameters

Parameter Value
OC_HOST 172.16.0.8
OC_SSHKEY /home/oracle/.ssh/id_rsa_oci_29012020
OC_DB_CONSTRING //172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com
OC_DB_PASSWORD Encrypted SYSTEM password
OC_DUMP_LOC /tmp

 

4. Export Data – mv2oci expdp

Dump files created in /tmp.

5. Transfer Data – mv2oci putdump

Files are available now on target server.

6. Import Data

Tablespace SOEDATA exists on target server, otherwise you can use to the EXTRA_IMPDP parameters in the mv2oci configuration file to do a remapping etc.

Analysis of the error in the SQL*Developer – there is a missing execution permission on package DBMS_LOCK.

This is an easy thing:

7. Reporting – mv2oci report

The report compares the objects on source and target database.

8.  All in One – mvoci auto

We did the steps one-by-one, by using the parameter auto, the steps above are done automatically (except reporting).

9. Logfiles

Logfiles tom the mv2oci actions are located in:

mv2oci /opt/mv2oci/out/log
Data Pump Directory in parameter DUMP_PATH

Summary

mv2oci is another great tool to support the movement to Oracle Cloud Infrastructure. Easy to configure, easy to use. #ilike

Oracle Release Update 19.9 – Lab Update Time (Grid Infrastructure Standalone & RDBMS)

The Oracle Release update 19.9 for Linux is available since a few days. Time to upgrade my lab environment at home which consists of the following components:

  • Oracle Grid Infrastructure Standalone 19.8.0 with ASM Normal Redundancy – +ASM
  • Oracle 19.8.0 RDBMS as Repository for Oracle Enterprise Manager – EMREPO

The running 19.8 Environment

Output from Trivadis base environment tool TVD-Basenv(TM).

Patch Download, Transfer and Extract

I have downloaded the Combo which contains the RU for Grid Infrastructure and Oracle Java Virtual Machine.

  • COMBO OF OJVM RU COMPONENT 19.9.0.0.201020 + GI RU 19.9.0.0.201020(Patch 31720429)
    • OJVM RELEASE UPDATE 19.9.0.0.0(Patch 31668882)
    • GI RELEASE UPDATE 19.9.0.0.0(Patch 31750108)

The local stage directory with the extracted files:

OPatch

OPatch in Grid Infrastructure home directory has to be version 12.2.0.1.19 or later.

Version Verification

+ASM – Grid Infrastructure Standalone

EMREPO – RDBMS

CheckConflictAgainstOHWithDetail

+ASM – Grid Infrastructure Standalone

EMREPO – RDBMS

Take care, this line here produces an error:

According My Oracle Support Note opatch CheckSystemSpace Command For Grid Infrastructure RU Fails With: “This command doesn’t support System Patch” (Doc ID 2634165.1), this error can be ignored and the line removed in future patch apply actions.

CheckSystemSpace

To check for space, we create there two files which contain the patch directories. The checks have to be successful.

+ASM – Grid Infrastructure Standalone

EMREPO – RDBMS

Release Update Apply

As first action I stop the OEM. The I run opatchauto as user root. Grid Infrastructure and RBDMS components are stopped, started and patch automatically one by one. Here is the full output of the patch apply where you can see the executed steps. In my lab environment, it took about 20 minutes.

Version Verification

+ASM – Grid Infrastructure Standalone

EMREPO – RDBMS

As you can see, the components were updated successfully. Time to start Oracle Enterprise Manager 13c Release 4 Update.

OJVM Apply

At the end, the OJVM patch has to applied. Set ORACLE_SID and ORACLE_HOME according the RDBMS environment.

Stop the RDBMS with srvctl

Change to OJVM Patch Directory

Apply the OJVM Patch

Startup Upgrade – Container Database and Pluggable Database – in SQL*Plus

Run datapatch

Shutdown Database in SQL*Plus

Start the RDBMS with srvctl

Version Verification

EMREPO – RDBMS

Summary

There were no issues. Ok, it’s just a GI Standalone environment. But this was really a pleasure.

The Grafana Plugins for Oracle Cloud Infrastructure Monitoring are back!

In September 2019 I wrote a blog post how to monitor an Oracle Cloud Infrastructure Autonomous database with Grafana plugin oci-datasource. But some weeks after publication, the plugin was not available on the Grafana page anymore. And only Oracle and Grafana had a clue why.

Now everything will be fine now. Since the 6th of October, there are two new Grafana plugins available for download. They both don’t require a Grafana enterprise account.

The first one is a successor of the former oci-datasource plugin, the second allows to get logs from OCI resources like Compute or Storage. As an infrastructure guy, let’s install the Oracle Cloud Infrastructure Metrics on an local Oracle Enterprise Linux 8 installation!

Install and configure the OCI CLI

Link: https://docs.cloud.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm

OS User oci and Installer

As OS user root, create a new user mentioned oci, change to new created user oci.

Run the installer script.

In this demo case, I use the default settings and the tab completion. After some seconds, all packages are installed and the OCI CLI is ready to configure.

Configure the OCI CLI

If you have already a created SSH key pair from a former OCI action, then you can use it here. Otherwise this setup process creates a new private and public key for you. Take care, the public key has to be in the PEM format!

Required values to finish the setup:

config location /home/oci/.oci/config
user OCID OCI > Identity > Users > [YOUR_USER] > OCID
tenancy OCID OCI > Administration > Tenancy Details > [YOUR_TENANCY] > OCID
region choose your region, e.g. eu-zurich-1
generate a new API signing RSA key pair Y -> only if you don’t have already an existing key pair
key directory /home/oci/.oci
key name oci_api_key_07102020

 

Run the setup.

OCI Console API Key

The content of the created public key has to be added in OCI Console as API key – just copy and paste it. OCI Console >> Identity >> Users >> User Details >> API Keys >> Add Public Key.

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

OCI CLI Configuration Test

Verify the configuration by execute a CLI command. Example to list images based on Oracle Linux.

OCI Console Group Policy

If your user is not part of the Administrator group, a new group and a group policy is needed which has the permissions to read tenant metrics. OCI Console >> Identity >> Groups >> Create Group.

Create the policy in the root compartment of your tenant. OCI Console >> Identity >> Policy>> Create Policy.

Install and configure Grafana and the Oracle Cloud Infrastructure Metrics Data Source Plugin

Grafana

Link: https://docs.cloud.oracle.com/en-us/iaas/Content/API/SDKDocs/grafana.htm

Start and enable the service.

Don’t forget to open the firewall port 3000 for the Grafana UI.

Oracle Cloud Infrastructure Metrics Data Source Plugin

List the available OCI Grafana plugins.


Install the metric plugin.

Restart Grafana Server.

Grafana Data Source Configuration

RSA Key Configuration

Grafana needs the configuration file and the RSA Key from the user oci. One solution: as user root, copy the files and set the ownership to OS user grafana.

Change the path to the key file in /usr/share/grafana/.oci/config.

from:

to:

Add a new Data Source

Login into the Grafana server by address <server-ip>:3000. The initial username and password is admin. It’s recommended to change the password at the first login. Add a new data source. Configuration >> Data Sources >> Add data source.

Filter by oracle and select the Oracle Cloud Infrastructure Metrics plugin.

Set Tenancy OCID, select your Default Region and set the Environment to local. Press Save & Test to verify the functionality.

Create a new Dashboard and add a new panel.

Now you can query the data, for example the VPN bandwidth for region eu-zurich1 in my personal compartment. Feel free to add new panels based on the available metrics.

Example

Summary

Great to have the Oracle Cloud Infrastructure Grafana plugins back. To get an idea, which metrics are all available, verify it in the OCI Console >> Monitoring >> Metrics Explorer. The free ADB is not available in the collected metrics. But this is a general issue.

This was a review of the first OCI plugin. In the next week I will take a deeper look into the Oracle Cloud Infrastructure Logging Data Source plugin.

Let’s IPSec VPN – How to connect your Unifi Security Gateway to Oracle Cloud Infrastructure

When I connect from home to the Oracle Cloud Infrastructure normally I used a Bastion Host, an Open VPN compute instance or Public IPs.  Some of the cool stuff like MV2OCI (which transfers data from on-premises to OCI) or integration of an ADB instance in my local running Oracle Enterprise Manager are referred to direct cloud connections. A SSH reverse tunnel works fine, but this cannot be a permanent solution for my lab environment.

At home I have an Unifi Security Gateway (USG) up an running at home. This gateway has the capability, to create site-to-site VPN connections. Good: The Oracle Cloud Infrastruicture VPN service is for free, and I don’t expect over 10 TB outbound traffic. Time to create a VPN setup from home to OCI. Take care about the USG, it needs a “direct” internet contact, this is why my FTTH modem is configured in bridge mode on port 4. Small hint: If your modem is not bridged, ask your internet provider. Here in Switzerland, almost all internet providers support this function.

Architecture

Click on the image for a larger view.

Prerequisites

  • Unifi Security Gateway Public IP – visible in the USG web interface or on webpage (search term: what’s my IP)
  • Oracle Cloud Infrastructure network setup according the setup guide
  • Knowledge about IPSec details which are used by OCI and as described in the setup guide: Key Exchange Version (IKEv1), Encryption (AES-256), Hash (SHA-1), DH Group (5)
  • VCN and local network ranges
  • The IPSec endpoint IP addresses and the secrets

Oracle Cloud Infrastructure IPSec Setup

My Oracle Cloud infrastructure network is configured 1:1 as described in the manual Setting Up VPN Connect: https://docs.cloud.oracle.com/en-en/iaas/Content/Network/Tasks/settingupIPsec.htm. Here in the IPSec connection you can see the endpoint IPs, the IPSec status is actually shown as down. The secrets are provided in the detail view.

Unifi Security Gateway Setup

Here you find the details of the USG site-to-site configuration: https://help.ui.com/hc/en-us/articles/360002668854#3. Create a new network in Settings – Networks.

Oracle Cloud Infrastructure Settings

VPN Type Manual IPsec
Enabled Checkbox activated
Route Distance 30
Peer IP OCI VPN endpoint IP
Local WAN IP Local public address of the USG
Pre-Shared Key OCI IPsec tunnel secret
IPsec Profile Customized
Key Exchange Version IKEv1
Encryption AES-256
Hash SHA1
DG Group 5
PFS Checkbox activated
Dynamic Routing Checkbox activated

 

Network Configuration

Oracle Cloud Infrastructure IPSec Status Update

After about two minutes, the OCI tunnel status turns into green. The VPN tunnel is now ready to use.

Unifi Security Gateway Routing

To be sure that local connections to instances running in the Oracle Cloud Infrastructure private subnet are working properly, we need a routing entry in the USG. Create a new routing entry in Settings – Routing & Firewall.

Routing Settings

Enabled Checkbox activated
Type Bullet activated
Destination Network CIDR of the OCI VCN network / subnet
Local WAN IP Local public address of the USG
Static Route Type: Interface
Interface Select interface created above, in my case OCI – Tunnel 1

 

Connection Verification

For testing purposes, I have created a compute instance in the OCI private subnet with IP 172.16.0.2, no public access – works!

A quick Bandwith Test

I am using iperf for this small test between my Windows client and the OCI compute instance. It’s not for production, just for the feeling. 68.7 Mbits/sec 🙂

Troubleshooting in USG

The connection can be verified when logged in as administrator in the Unifi Security Gateway as user ubnt / admin. Link to the documentation: https://help.ui.com/hc/en-us/articles/360002668854-UniFi-UDM-USG-Verifying-and-Troubleshooting-IPsec-VPNs

Show the current VPN configuration

Follow the Logfile

Troubleshooting in Oracle Cloud Infrastructure

There is a small document available to verify the basic configuration, maybe in future some log access will be provided. In a past project where we had VPN connection issues with a Fortigate firewall, I had a good experience with the guys from My Oracle Support.

Link: https://docs.cloud.oracle.com/en-us/iaas/Content/Network/Troubleshoot/ipsectroubleshoot.htm

Summary

Finally I have a stable VPN connection to Oracle Cloud Infrastructure for free. If all requirements are met, the configuration can be done in a few minutes. Next steps: Activation of the second tunnel to get VPN redundancy, enable notifications when a IPsec tunnel is down and some other Oracle Enterprise Manager 13c monitoring stuff. The weather conditions in Switzerland are bad for the next days, so there is enough time in the evenings to do further research.

#freedom #network #together #doer #curiosity