Oracle RDBMS

Never stop Learning – why I love Oracle LiveLabs

Author: 8. October 2021

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 🙂

19c, Autonomous Database, DBaaS, Oracle Cloud Infrastructure, Oracle RDBMS, Testing

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

Author: 30. October 2020

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 ACE, Oracle Cloud Infrastructure, Oracle Linux, Oracle RDBMS, TrivadisContent

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

Author: 26. October 2020

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.

19c, Oracle ACE, Oracle Linux, Oracle RDBMS, Oracle Tools, Release Update, TrivadisContent

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

Author: 15. September 2020

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!

DBaaS, Oracle Cloud Infrastructure, Oracle RDBMS, Security, TrivadisContent

Oracle Cloud Infrastructure and SSH Keys – Jump!

Author: 27. April 2020

Jump!

In our Trivadis Oracle Cloud Infrastructure training environments, we never use direct access to an application or database server by a public IP address. For this case, we use an Oracle Linux based bastion host which acts as a jump host. For security reasons, I never put any SSH keys on a bastion host to connect from there to the target instances. If your bastion host is compromitted, your SSH keys are lost! In one of the last trainings, some participants had problems with. So I decided to blog about. This blog post shows you the different methods to connect to an Oracle Cloud Infrastructure private/public network by using a bastion host. 

SSH Keys

Oracle Cloud Infrastructure Linux based offerings like compute instances and virtual machines for databases are accessible by SSH key as per default. For working with these machines, I use these three types of SSH keys:

  • id_rsa_oci – Private key generated by ssh-keygen
  • id_rsa_oci.pub – Public key generated by ssh-keygen
  • id_rsa_oci.ppk – Puttygen-converted private key

This gives me the flexibility, to connect to running OCI instances on different ways like Putty, MobaXterm, Windows Subsystem for Linux, WinSCP etc. 

Oracle Cloud Infrastructure Sample Setup

 

Host Public IP Private IP Accessible by
Bastion Host 140.238.216.114 10.0.0.2 SSH 
Windows Application Server   10.0.1.2 RDP
Oracle Database Server   10.0.2.2 SSH

 

Reminder: In OCI only SSH port 22 is open in the subnet security lists as per default when the VCN is created by the VCN Wizard. If you want to allow connection from the public to the private subnet by RDP and Oracle Net, then port 3389 and 1521 must be added in the security list for the private subnet. Create stateful ingress rules and restrict the source connections to the bastion host private IP range.

Build your own SSH Tunnel

There different ways to build a SSH (tunnel) configuration to Oracle Cloud Infrastructure instances on a Windows based platform, my favourites:

  1. Windows Subsystem for Linux (WSL)
  2. MobaXterm
  3. Putty

Here are some connection examples how to work with instances in a private subnet via bastion host with this three methods. As a Windows 10 user, for some connections I d’ like to use WSL Ubuntu more and more – now available in version 20 🙂

1. Connect by using Windows Subsystem for Linux (WSL)

Test: Verify the Connection to the Bastion Host public IP Address

Database Server: SSH Connect via Bastion Host

This opens a session on the database server as user opc.

Database Server: Create a new SSH Tunnel to forward port 1521 as port 15210

This opens a connection to the bastion host.

Database Server: Connect to the Database by SQL Developer

Use port 15210 and localhost as hostname.

Verify the Oracle Net service name from the DBA panel menu.

Application Server: Create a new SSH Tunnel to forward port 3389 as port 33890

This opens a connection to the bastion host.

Application Server: Connect to the Windows Desktop by Remote Desktop Connection

Use port 33890 and localhost as hostname.

2. Connect by using MobaXterm

Database Server: SSH Connect via Bastion Host

This opens a session ion the database server as user opc.

Fill in Remote Host, Specify username and Port. Activate Use private key and select the local private SSH key in Putty format.

Activate Connect through SSH gateway, fill in Gateway SSH server, Port, User. Activate Use private key and select the local private SSH key in Putty format.

Start the session.

As you can see in the MobaXterm Header, X-Forwarding works too.

Database Server: Create a new SSH Tunnel to forward port 1521 as port 15210

Open MobaXterm Tunneling menu and add a New SSH tunnel. Fill in Forwarded port, Remote server, Remote port, SSH server, SSH login and SSH port. Save the tunnel settings. For an application server tunnel, just replace Remote server, Remote port and Forwared port settings.

Add the private SSH key in Putty format by click on the key icon. Start the tunnel.

Database Server: Connect to the Database by SQL Developer

Use port 15210 and localhost as hostname.

Verify the database control file settings from the DBA panel menu.

3. Connect by using Putty

Database Server: SSH Connect via Bastion Host

As prerequisite, I have created a Putty session called OCI Bastion Host for the jump host connection with the SSH private key in Putty format and user opc. This session is now used as Proxy.

Fill in database server private IP. The red one is the already existing session.

Add proxy command and save session settings. Optioanl enable proxy diagnostics.

Open the new created session to connect to database server with user opc.

Application Server: Create a new SSH Tunnel to forward port 3389 as port 33890

This opens a connection to the bastion host. Fill in bastion host public IP. 

Add private key file in Putty format and enable checkbox Allow agent forwarding.

Add a port forwarding rule for RDP. Save session.

Open the new created session to enable port forwarding for Remote Desktop Protocol.

Application Server: Connect to the Windows Desktop by Remote Desktop Connection

Use port 33890 and localhost as hostname.

Alternative Method – Start Putty from Command Line

Start Putty with the port forwarding settings by command line. This opens a Putty session and port 3389 can be used. No addtional settings are required.

Summary

A bastion host is an “easy-to-setup” alternative to a VPN connection without any huge infrastructure overhead. There are several ways how to connect & tunnel to the target servers. Use the method which are you familiar with it, but NEVER place SSH keys on a bastion host. 

And now: click here to make some noise – Jump by Van Halen

Links

Oracle Cloud Infrastructure, Oracle Linux, Oracle RDBMS, TrivadisContent