DBaaS

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

Never stop Learning – why I love Oracle LiveLabs

Since over one and a half year, this week I was back in an onsite training, live people, live teaching. With a motivated junior DBA class, we started with all about Oracle architecture based on our Trivadis training O-AI – Oracle Architecture and Internals. The training is a mix between slides, demos and labs. Therefore during the course we run the training environments in Oracle Cloud Infrastructure, build by Terraform (Credits to Stefan Oehrli from oradba.ch which has ramped up the whole stuff). After the course at the end of the month, the environments will be cleaned up. And what’s next?

Training Environments

There are a lot of possibilities to get a deeper knowledge of all this Oracle stuff like processes, data encryption, multitenancy, datapump and so on:

But my actual favorite is Oracle LiveLabs!

Oracle LiveLabs

This platform is not only for DBAs, it has a lot of workshops for Application Developers, Data Scientists and DevOps Engineers too. There are different workshop types available:

  • get your free Oracle Cloud Infrastructure training environment for free during a time period like Oracle Database 19c New Features – run on LiveLabs
  • workshops which are running in a free tenancy
  • workshops what you can do in your own paid tenancy

At the moment there are 21 workshops where you get a live environment with all components you need virtual machines or database in Oracle Cloud Infrastructure like Oracle Multitenant Fundamentals, Database 19c – Automatic Indexing, 21c New Features on Autonomous Database and many more. All workshops are very well described, from the access to the initial setup and finally for the workshop himself too.

In this case I have decided to start the Oracle Multitenant lab to gather more information how PDB Snapshot Copy works.

1st – Search your training in the available workshops  and press Launch

2nd – Define where the Workshop should run

In this case, I want to reserve an environment. This is not possible for all workshops, you can see that in the workshop details if it’s possible to the an Oracle Cloud Infrastructure setup.

3rd – Define the Start Date and propose your SSH Public Key

With the key, you can get access to the training servers by SSH. In this case I want to start the workshop immediately. Otherwise define a start and end date. If you don’t want to start now, you will get a confirmation that the workshop is reserved and an email at the day where the workshop starts with the credential information.

4rd – View my Reservation

After some minutes, the status for the workshop is updated. As you can see here, in about three minutes from now, the environment should be ready. You will receive a confirmation mail.

5th – Launch Workshop

When the workshop is ready, the workshop can be launched.

6th – Workshop Details

All information you need is in the details like:

  • User name
  • Initial password for OCI
  • Compartment
  • Instance public IP

Here you have also the chance to extend your workshop reservation time. Follow the Get Started instructions to the bottom and push the button to move on the introduction. Step by step you are guide through the login and setup process. All labs contain a manual how to connect and to do the initial setup like starting listeners or get scripts from the OCI Object Storage.

There are the connection options how you can interact with the LiveLab:

  1. Connect using Cloud Shell
  2. Connect using MAC or a Windows CYGWIN Emulator
  3. Connect using Putty

Example code for the multitenancy lab preparation:

Summary

Oracle LiveLabs is another great opportunity to learn and train new stuff. All you have to take care now is to follow the workshop instructions and take care about the limited time. Enjoy it, learn new stuff and have fun! Oracle LiveLabs are easy to join, easy to set up and well described. This is why I love it 🙂

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

Oracle Cloud Infrastructure – Security first: Cloud Guard and Security Zones – a first View

Two new Oracle Cloud Infrastructure Cloud Security Services

Good news: Oracle has provided two new services for cloud security. Cloud Guard to get an overview of existing possible security breaches and Security Zones, which allows to create a full restricted compartment.  In this blog, I will give you a short overview about this brand new services.

Cloud Guard

The Cloud Guard service helps you to identify security issues in your tenancy. Before the first use, it has to be enabled and a base region and for a minimum one compartment has to be selected. It needs a new policy which Cloud Guard allows to gather information in your tenancy. Oracle Cloud Guard discovers available object in compartments like Compute Instances, Object Storage and many more and checks Oracle Cloud Infrastructure security best practices.

Link to documentation: https://docs.cloud.oracle.com/en-us/iaas/cloud-guard/using/index.htm

Enable Cloud Guard first

Based on recipes, it show you security recommendations for the findings and can execute corrective actions. There are two different receipes types available:

  • Oracle Managed Detector Recipe – provided by Cloud Guard, doesn’t allow to disable rules
  • User Managed Detector Recipe – a clone of an Oracle managed recipe, allows to disable individual rules

Examples for recipes – docs.cloud.oracle.com

  Oracle Managed Recipe User Managed Recipe
Rule Status Risk Level Status Risk Level
Bucket is public ENABLED HIGH DISABLED HIGH
Instance has public IP address ENABLED CRITICAL ENABLED HIGH
VCN has no inbound Security List ENABLED MEDIUM DISABLED MEDIUM

 

Based on detected findings, Cloud Guard is able to to corrective actions. This feature called Responder Rules requires a policy. Problems can be fixed on three ways:

  • Remediated – Fix using Cloud Guard responder
  • Resolved – Fixed by other process
  • Dismissed – Ignore and close

Example for a Cloud Guard Responder Action

Example – Cloud Guard has detected a Public IP 

Cloud Guard Dashboard

The dashboard gives you an overview of the findings and actions. There are direct links to the findings and recommendations. Ok, It looks I have to review my test compartment 😉

Security Zones

A security zone is associated on a compartment and a security zone recipe. For example when in the recipe is defined, users cannot create an Internet Gateway in a defined compartement, an error message occurs when he tries to create one.

Link to documentation: https://docs.cloud.oracle.com/en-us/iaas/security-zone/using/security-zones.htm

Create a new Security Zone

Recipes

There are some basic rules in the Oracle defined recipe (at the moment you can not create a customer based recipe) – for example:

  • Resources can’t be moved out from a security zone to a regular compartment
  • Resources are not accessible by Internet
  • Resources must be regularly backed up

 

Test – Create an Internet Gateway in the new created Security Zone

A violation message occurs, the security zone recipe doesn’t allow creating Internet Gateways.

Summary

I really like these two new services. Cloud Guard which helps me to identify possible security issues and Security Zones to create secure compartments without writing manual policies. This is only a short overview, in next days I will definitely take a deeper look, especially in Cloud Guard and the corrective actions. I have a great interest to find out how it works in the background for example when a public IP is detected and so on. The Oracle Cloud Infrastructure security is definitely on track!

Enterprise Manager 13c – Let’s use the Hybrid Agent for Amazon EC2 and Azure Instances

I like the concept behind the Oracle Enterprise Manager Hybrid Cloud Architecture to connect my on-premise OMS with targets in the Oracle cloud. The Agent communicates via SSH tunnel to target servers, no other ports than SSH 22 are open against the world wide web. An I was interested to find out, if the installation of such an agent works for other cloud providers than Oracle too.

Create a Oracle Linux Instance in Amazon AWS

I have created a small Oracle Linux instance in Amazon AWS and inserted the public IP into the /etc/hosts file of the Oracle Management Server. Why I have used an Oracle Linux? According the documentation, at the moment only Oracle Linux x86-64 is supported to use this hybrid feature.

On the Amazon instance I installed the 12c prerequisite package (yum install oracle-rdbms-server-12cR1-preinstall)  to be sure that libraries etc. are available and the user oracle is created. And finally I added the public key in the authorized_keys file of the user oracle so that connects via SSH without a password are possible.

hybrid_01

Hint: Test your passwordless SSH connection with a tool like Putty or MobaXterm.

Login Credential

For the login via SSH tunnel, in Enterprise Manager 13c a named credential has to be created with the SSH keys which were used by the Amazon instance. This credential us used later in the agent deployment process. For further information how to create such a credential, please take a look into the Hybrid Coud documentation https://docs.oracle.com/cd/E24628_01/doc.121/e24473/hybrid-cloud.htm#BABJACHI.

hybrid_02

 

 

 

 

 

 

 

 

 

 

Hybrid Agent Deployment – First Run

After setting the DNS information and SSH configuration, it’s time to start a Hybrid Agent deployment. EM13c – Setup – Add Target – Install Agent on Host. The most important thing is that the checkbox for the Hybrid Cloud Agent is enabled at the bottom of the browser window.

hybrid_03

 

 

 

 

 

 

 

 

 

Host Name does not map to an Oracle Public Cloud Virtual Host – Investigation

The deployment has started. But in the prerequisites phase the remote validation fails with this message: The provided host name does not map to an Oracle Public Cloud virtual host. You can deploy Hybrid Cloud Agents only on Oracle Public Cloud virtual hosts.

hybrid_04

 

 

It looks like the prerequisites check is verifying the hostname. In the deployment logfile from the Oracle Management Server, I found these lines:

Oracle is doing a simple hostname -d command to verify if the host is running in the Oracle Cloud. I have verified the hostname -d command on Oracle Cloud instance, and the output there is different:

But my Amazon instance has this output here:

Lets fake the Amazon instance hostname and try the deployment again. I added this line into the Amazon instance /etc/hosts with the new domainname.

Now the hostname -d command shows me the new name according the Oracle cloud instances.

Hybrid Agent Deployment – Second Run

EM13c – Retry, using same inputs. And the error is gone, the agent is installed successfully. After running the scripts

  • /u00/app/oracle/product/agent13c/agent_13.1.0.0.0/root.sh
  • /home/oracle/oraInventory/orainstRoot.sh

as user root, the Amazon instance is added as new host.

hybrid_05

Summary

With a simple change for the hostname -d command, you can install the Oracle Hybrid Agent on targets outside the Oracle cloud. BTW, this works for local instances too. All other ports than SSH 22 are closed. And that’s an important thing when you work with cloud products.