martinberger.com – Deheime@Jurasüdfuss.SO.CH

Latest Posts

Monitor your Oracle Cloud Infrastructure Autonomous Database with your On-Prem Oracle Enterprise Manager 13c

Author: Martin Berger 30. December 2021

This blog post describes how to use an on-prem Oracle Enterprise Manager 13c to monitor Oracle Cloud Infrastructure Autonomous Databases without internet traffic. In this case, a VPN connection from the data center Mohnweg/Jurasüdfuss/Switzerland to the OCI data center in Zurich is up and running. This method works with a Fast Connect too.

From the Oracle Enterprise Manager 13.5 Administrator Guide:

Oracle Enterprise Manager supports the following Autonomous Databases and the term “Autonomous Databases” in this guide collectively refers to them:

Autonomous Data Warehouse – Dedicated

Autonomous Transaction Processing – Dedicated

Autonomous Data Warehouse – Shared

Autonomous Transaction Processing – Shared

Architecture

On-Prem: Oracle Enterprise Manager 13.5 RU2 – OL 7
Oracle Cloud Infrastructure: Autonomous Transaction Processing – Shared

Requirements

VPN connection up and running
EM 13c up and running
Routing and security list from on-prem to OCI and vice-versa in place
An OCI Network Security Group NSG created – required for the ADB private endpoint connection

Network Security Group NSG

Allow ingress traffic for port 443 (Oracle Database Actions) and 1522 (Oracle Net).

Create and Autonomous Transaction Processing Database

Choose workload type, version, OCPU count and license type. Configure the section network access for private access only. Take care, the optional Host name prefix is later used for the connection as hostname. Choose it wisely. With a private endpoint, all network traffic moves in the private subnet within a VCN. It keeps all traffic to and from your Autonomous Database off of the public internet.

ATP – Database Actions – Access

In the ATP page of the new created Autonomous Database, a click on Database Access shows you the URL to the web console. This URL cannot be resolved from your local workstation, the hostname is unknown. The hostname has to be resolved first by your DNS (quick and dirty: local hosts file) or you use the IP address instead which is provided in the Autonomous Database Details page.

The private endpoint IP 192.168.201.11 is in our OCI private subnet. I have added it temporarily to my local workstation hosts file.

192.168.201.11 ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloudapps.com ocieuzurich1adbtp

1	192.168.201.11 ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloudapps.com ocieuzurich1adbtp

ATP – Database Actions – User ADBSNMP

User: ADMIN
Password: Your ADB password

In the Database Actions Launchpad, select DATABASE USERS.

Edit user ADBSNMP, unlock the account and set a password. This password is later used in Oracle Enterprise Manager 13c.

ATP – Download Wallet

On the Autonomous Database Details page, download the DB Connection wallet. The wallet and the wallet password are later used in Oracle Enterprise Manager 13c.

Oracle Enterprise Manager 13c – ATP Host Name Resolution

Oracle uses FQDN in the ADB wallet tnsnames.ora file. Verify if the hostname which is listed above on the details page as Private Endpoint can be resolved by the on-prem OEM host. Alternative: Add this line to the /etc/hosts file. Attention: this is not the same domain as used for the Database Actions console (oraclecloudapps.com vs. oraclecloud.com). Tip for troubleshooting: Test the database connection with your local SQL Developer and user ADBSNMP, for a local SQL*Plus connect you have to extract the wallet file and configure Oracle Net.

192.168.201.11 ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloud.com ocieuzurich1adbtp

1	192.168.201.11 ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloud.com ocieuzurich1adbtp

Connectivity check firewall and routing with Telnet:

oracle@rainstrasse:~/ [rdbms21] telnet ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloud.com 1522
Trying 192.168.201.11...
Connected to ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloud.com.
Escape character is '^]'.

oracle@rainstrasse:~/ [rdbms21] telnet ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloud.com 1522

Trying 192.168.201.11...

Connected to ocieuzurich1adbtp.adb.eu-zurich-1.oraclecloud.com.

Escape character is '^]'.

Oracle Enterprise Manager 13c – Add Targets Manually

Add Non-Host Targets Manually.

As Agent Host, you can choose your local agent which is running on the OEM host. Select target type for Autonomus Data Warehouse or Autonomus Transaction Processing.

Set Target Name, choose the OCI Client Credential Wallet and set Monitoring Password.
Test the connection.
Next and Submit.

The target will be added in the next minutes.

Oracle Enterprise Manager 13c – ADB Detail Page

The Autonomous Database is added as new database target and can be monitored on the same way as the on-prem databases. As it is a service, the metrics are limited. For example: when the ADB is stopped, the Oracle Enterprise Manager shows an Availability Evaluation Error.

Summary

Adding an Oracle Cloud Infrastructure Autonomous Database in an on-prem Oracle Enterprise Manager EM13c by using a private endpoint is a great thing. Take care about the routing and the firewall rules, verify all connections before you add an ADB as a new target. Even when the metrics are limited, for a basic monitoring (+) is it ok.

Next step: I want to deploy an EM13c agent on a DBCS system. The MOS note DBCS: How to Deploy EM Agent on Cloud / DBCS Instance (Doc ID 2400965.1) shows the old OCI classic way, a SR to ask if this is allowed in OCI Gen. 2 too is already raised.

This was the last blog post of the year 2021 – see you in 2022! #happynewyear

Uncategorized

Oracle Enterprise Manager 13c Release 5 – Time for Release Update 2

Author: Martin Berger 21. November 2021

Since a few days the 13.5.0.2 Enterprise Manager Cloud Control Base Platform Monthly Release Update (RU) 2 is available to apply. Time to update my 13.5 lab environment at home to get a first experience about a lot of new features like Oracle Cloud Infrastructure Resource Discovery, Oracle Enterprise Manager Dashboards, Database Scheduler Jobs Metrics and many more.

Notes and Links

My Oracle Support Notes

13.5.0 Enterprise Manager Cloud Control Base Platform Monthly Release Update (RU) 2 (Doc ID 2822316.1)
Enterprise Manager 13.5 Main Release Update List (Includes Plug-ins) (Doc ID 2760230.2)

Patch Number 33456001

The Environment

Oracle Enterprise Manager 13.5 running on Oracle Linux 7.9
Oracle Enterprise Edition Repository 19.13 Single Tenant
Oracle Restart / ASM 21.0.0
Additional Oracle Linux Server with 19.3.0 Container Databases
All targets up and running
My Oracle Support connected
Patch software staged directory in /u01/app/oracle/stage/33456001

Installed Plugins

Verification by emcli. Plugins actually have release number 13.5.1.0.0.

$ export ORACLE_HOME=/u01/app/oracle/product/em13cr5/
$ORACLE_HOME/bin/emcli login -username=sysman
Enter password

Login successful

$ export ORACLE_HOME=/u01/app/oracle/product/em13cr5/

$ORACLE_HOME/bin/emcli login -username=sysman

Enter password

$ORACLE_HOME/bin/emcli sync
Synchronized successfully

1 2	$ORACLE_HOME/bin/emcli sync Synchronized successfully

$ORACLE_HOME/bin/emcli list_plugins_on_server
OMS name is rainstrasse.kestenholz.net:4889_Management_Service
Plug-in Name Plugin-id Version [revision]

Oracle Cloud Framework     oracle.sysman.cfw       13.5.1.0.0
Oracle Database            oracle.sysman.db        13.5.1.0.0
Oracle Fusion Middleware   oracle.sysman.emas      13.5.1.0.0
Systems Infrastructure     oracle.sysman.si        13.5.1.0.0
Oracle Exadata             oracle.sysman.xa        13.5.1.0.0

$ORACLE_HOME/bin/emcli list_plugins_on_server

OMS name is rainstrasse.kestenholz.net:4889_Management_Service

Plug-in Name Plugin-id Version [revision]

Oracle Cloud Framework oracle.sysman.cfw 13.5.1.0.0

Oracle Database oracle.sysman.db 13.5.1.0.0

Oracle Fusion Middleware oracle.sysman.emas 13.5.1.0.0

Systems Infrastructure oracle.sysman.si 13.5.1.0.0

Oracle Exadata oracle.sysman.xa 13.5.1.0.0

Prerequisites

For the readme:

On the Repository Database, apply Database Release Update 19.11.0.0.0 Patch 32545013 and Overlay patch for 17777718 on top of Database Release Update 19.11.0.0.0. Or apply Database Release Update 19.12.0.0.0 Patch 32904851 or its later Database Release Update version patch.
On Windows, apply Oracle Repository Creation Utility patch 33053642 on all OMSes.
Ensure that you have the latest version of OMSPatcher version 13.9.5.1.0 for Enterprise Manager 13.5.0.0 release on all OMSes.

In my case I had to update OMSPatcher and his little sister OPatch. For upgrading these components, I recommend these notes:

13.5: How To Upgrade Enterprise Manager 13.5 Cloud Control OMSPatcher Utility to Version 13.9.5.1.0 (Doc ID 2809842.1)
Patch 28186730: OPATCH 13.9.4.2.7 FOR EM 13.4, 13.5 AND FMW/WLS 12.2.1.3.0, 12.2.1.4.0 AND 14.1.1.0.0

OMSPatcher

$ export ORACLE_HOME=/u01/app/oracle/product/em13cr5/
$ $ORACLE_HOME/OMSPatcher/omspatcher version

OMSPatcher Version: 13.9.5.1.0
OPlan Version: 12.2.0.1.16
OsysModel build: Tue Apr 28 18:16:31 PDT 2020

OMSPatcher succeeded.

$ export ORACLE_HOME=/u01/app/oracle/product/em13cr5/

$ $ORACLE_HOME/OMSPatcher/omspatcher version

OMSPatcher Version: 13.9.5.1.0

OPlan Version: 12.2.0.1.16

OsysModel build: Tue Apr 28 18:16:31 PDT 2020

OMSPatcher succeeded.

OPatch

$ORACLE_HOME/OPatch/opatch version
OPatch Version: 13.9.4.2.7

OPatch succeeded.

$ORACLE_HOME/OPatch/opatch version

OPatch Version: 13.9.4.2.7

OPatch succeeded.

Software Directory

The release update is transferred to the local stage directory:

$ pwd
/u01/app/oracle/stage/33456001
$ ll
total 140
drwxr-x---. 18 oracle oinstall 4096 Nov 21 05:44 .
drwxr-xr-x. 10 oracle oinstall 4096 Nov 20 23:55 ..
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32941631
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32941662
drwxr-x---. 5 oracle oinstall 4096 Nov 13 05:24 32971615
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971629
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971644
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971692
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971710
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971724
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971751
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972346
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972360
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972370
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972378
drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972412
drwxr-xr-x. 2 oracle oinstall 69 Aug 23 01:40 6880880
-rw-rw-r--. 1 oracle oinstall 65361 Nov 17 06:21 README.html
drwxr-x---. 2 oracle oinstall 92 Nov 13 05:24 automation
-rw-rw-r--. 1 oracle oinstall 7137 Nov 13 06:54 bundle.xml
-r-xr-xr-x. 1 oracle oinstall 979 Nov 13 05:25 pluginDep.txt

$ pwd

/u01/app/oracle/stage/33456001

$ ll

total 140

drwxr-x---. 18 oracle oinstall 4096 Nov 21 05:44 .

drwxr-xr-x. 10 oracle oinstall 4096 Nov 20 23:55 ..

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32941631

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32941662

drwxr-x---. 5 oracle oinstall 4096 Nov 13 05:24 32971615

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971629

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971644

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971692

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971710

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971724

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32971751

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972346

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972360

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972370

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972378

drwxr-x---. 4 oracle oinstall 4096 Nov 13 05:24 32972412

drwxr-xr-x. 2 oracle oinstall 69 Aug 23 01:40 6880880

-rw-rw-r--. 1 oracle oinstall 65361 Nov 17 06:21 README.html

drwxr-x---. 2 oracle oinstall 92 Nov 13 05:24 automation

-rw-rw-r--. 1 oracle oinstall 7137 Nov 13 06:54 bundle.xml

-r-xr-xr-x. 1 oracle oinstall 979 Nov 13 05:25 pluginDep.txt

Pre-Check

Run the pre-check to analyze the existing Oracle Enterprise Manager system.

$ export ORACLE_HOME=/u01/app/oracle/product/em13cr5/
$ cd /u01/app/oracle/stage/33456001
$ $ORACLE_HOME/OMSPatcher/omspatcher apply -analyze

$ export ORACLE_HOME=/u01/app/oracle/product/em13cr5/

$ cd /u01/app/oracle/stage/33456001

$ $ORACLE_HOME/OMSPatcher/omspatcher apply -analyze

These warning messages about patches which can not applied can be ignored, I don’t have installed these plugins like SMF (Enterprise Manager for Fusion Applications):

WARNING: Could not apply the patch "32971751" because the "oracle.sysman.smf.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "32972370" because the "oracle.sysman.vt.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "32972412" because the "oracle.sysman.empa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

WARNING: Could not apply the patch "32971751" because the "oracle.sysman.smf.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

WARNING: Could not apply the patch "32972370" because the "oracle.sysman.vt.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

WARNING: Could not apply the patch "32972412" because the "oracle.sysman.empa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

Apply

1st – Stop the Oracle Management Server

$ $ORACLE_HOME/bin/emctl stop oms

1	$ $ORACLE_HOME/bin/emctl stop oms

2nd – Apply RU with OMSPatcher

As in some blog posts before, I don’t use a credential file, therefore the username (if not using weblogic which is default) and the password for the Weblogic AdminServer has to be provided. The job runs some minutes. The OMS will be started automatically.

$ $ORACLE_HOME/OMSPatcher/omspatcher apply
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation. All rights reserved.


OMSPatcher version : 13.9.5.1.0
OUI version : 13.9.4.0.0
Running from : /u01/app/oracle/product/em13cr5
Log file location : /u01/app/oracle/product/em13cr5/cfgtoollogs/omspatcher/opatch2021-11-21_05-52-12AM_1.log

OMSPatcher log file: /u01/app/oracle/product/em13cr5/cfgtoollogs/omspatcher/33456001/omspatcher_2021-11-21_05-52-13AM_deploy.log

Please enter OMS weblogic admin server URL(t3s://rainstrasse.kestenholz.net:7102):>
Please enter OMS weblogic admin server username(weblogic):>
Please enter OMS weblogic admin server password:>

Enter SYS Password :
Checking if current repository database is a supported version
Current repository database version is supported


Prereq "checkComponents" for patch 32971615 passed.

Prereq "checkComponents" for patch 32941631 passed.
WARNING: Could not apply the patch "32971751" because the "oracle.sysman.smf.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "32972370" because the "oracle.sysman.vt.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.


<some output here>

<some output here>

<some output here>

7) Could not apply the patch "32941662" because the "oracle.sysman.emfa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
8) Could not apply the patch "32972360" because the "oracle.sysman.ssa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
--------------------------------------------------------------------------------
OMSPatcher Session completed with warnings.
Log file location: /u01/app/oracle/product/em13cr5/cfgtoollogs/omspatcher/33456001/omspatcher_2021-11-21_05-52-13AM_deploy.log

OMSPatcher completed with warnings.

$ $ORACLE_HOME/OMSPatcher/omspatcher apply

OMSPatcher Automation Tool

OMSPatcher version : 13.9.5.1.0

OUI version : 13.9.4.0.0

Running from : /u01/app/oracle/product/em13cr5

Log file location : /u01/app/oracle/product/em13cr5/cfgtoollogs/omspatcher/opatch2021-11-21_05-52-12AM_1.log

OMSPatcher log file: /u01/app/oracle/product/em13cr5/cfgtoollogs/omspatcher/33456001/omspatcher_2021-11-21_05-52-13AM_deploy.log

Please enter OMS weblogic admin server URL(t3s://rainstrasse.kestenholz.net:7102):>

Please enter OMS weblogic admin server username(weblogic):>

Please enter OMS weblogic admin server password:>

Enter SYS Password :

Checking if current repository database is a supported version

Current repository database version is supported

Prereq "checkComponents" for patch 32971615 passed.

Prereq "checkComponents" for patch 32941631 passed.

7) Could not apply the patch "32941662" because the "oracle.sysman.emfa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

8) Could not apply the patch "32972360" because the "oracle.sysman.ssa.oms.plugin with version 13.5.1.0.0" core component of the "OMS" or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

--------------------------------------------------------------------------------

OMSPatcher Session completed with warnings.

Log file location: /u01/app/oracle/product/em13cr5/cfgtoollogs/omspatcher/33456001/omspatcher_2021-11-21_05-52-13AM_deploy.log

OMSPatcher completed with warnings.

Verification

Get the Release Update Information, the inventory was updated:

$ $ORACLE_HOME/OPatch/opatch lsinventory | grep Platform
Patch description: "Oracle Enterprise Manager 13c Release 5 Platform Update 2 (13.5.0.2) for Oracle Management Service"

1 2	$ $ORACLE_HOME/OPatch/opatch lsinventory \| grep Platform Patch description: "Oracle Enterprise Manager 13c Release 5 Platform Update 2 (13.5.0.2) for Oracle Management Service"

Next Step

Plugin Upgrade
Agent Patch

Summary

Some weeks ago there was a presentation with all these new features like customizable dashboards. The update process himself was easy and straight forward as we knew from other releases. Finally the Release Update 2 is arrived. Let’s see in the next days what’s in the box.

Uncategorized

Oracle Cloud Infrastructure Resource Naming Conventions – A short Friday Blog Post

Author: Martin Berger 19. November 2021

Cleaning up the OCI Resource Chaos

This week I have removed all my Oracle Cloud Infrastructure lab resources and cleaned up my compartment to start from scratch building environments with the Automation Manager. There were a lot of resources with names like webserver01, vcn-lab-01, block-volume-web-clone-47 and so on. When I have realized this naming chaos, I have decided to spend some minutes today Friday to think about a small naming convention for my future Oracle Cloud Infrastructure projects.

Why having a naming convention? Wikipedia says about Naming Convention:

In computer programming, a naming convention is a set of rules for choosing the character sequence to be used for identifiers which denote variables, types, functions, and other entities in source code and documentation.

Sounds good, this is what I need. There are some proposal docs in the web about naming convention from Azure and TrendMicro – I used them as an inspiration.

Naming Convention

To identify an OCI resource, tagging is not enough. I like to see it at a glance what a resource is doing, which project and, if required, some additional information. This here is just a idea how it could work. Any other ideas are welcome :-). I have decided to define a resource by:

A resource type like bv for Block Volumes
A region key – based on https://docs.oracle.com/en-us/iaas/Content/General/Concepts/regions.htm
An environment code – like letter t for test, d for development and p for production
A stack – this could be a project name or a purpose
A instance – the number of the resource if more than one is created

And in some cases where this information is not enough:

An additional – like read-only, protected etc.

Examples

Resource Name	Purpose
vcn-zrh-t-newcrm-001	VCN, region Zurich, test, NEWCRM project
sn-prv-fra-p-transfer-001	Private Subnet, region Frankfurt, production, TRANSFER project
nsg-zrh-d-ipsec-001	Network Security Group, region Zurich, development, IPSEC project
bv-zrh-t-ipsec-olvm-engine-001	Block Volume, region Zurich, test, IPSEC project, compute instance olvm-engine
log-ams-p-crm-read-001	Log Group ,region Amsterdam, production, CRM project, read only
ci-fra-t-science-ngnix-003	Compute Instance , region Marseille, test, SCIENCE project, NGINX server

A first View – Network Visualizer

This looks not so bad.

Summary

If you want to bring an order into your naming chaos, then you have to define a naming convention. There are for sure many other ideas around the world. How do you handle it? Just drop me a message by LinkedIn or Twitter and let me knolw if I can add your idea here in this blog too.

Uncategorized

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

Author: Martin Berger 2. November 2021

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

BACKUP AND RECOVERY IN ORACLE CLOUD FOR DOCKER CONTAINER DEPLOYED DATABASE (Doc ID 2772375.1)
Oracle Database Tablespace Encryption Behavior in Oracle Cloud (Doc ID 2359020.1)
https://docs.oracle.com/en-us/iaas/Content/Database/Tasks/mig-11g-11g.htm
https://docs.oracle.com/en-us/iaas/Content/Database/Tasks/mig-onprembackup.htm
https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/installing-oracle-database-cloud-backup-module-oci-classic.html
https://docs.oracle.com/en/cloud/paas/db-backup-cloud/csdbb/restoring-new-database-host.html

Migration Steps

Create OCI Object Storage
Configure on-prem database to use the Oracle Database Backup Service
Create a new OCI Database Virtual Machine
Configure OCI cloud database to use the Oracle Database Backup Service
Clean up OCI cloud database
Restore OCI cloud database from Object Store
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

SQL> COL tablespace_name FORMAT a20
SQL> COL file_name FORMAT a70
SQL> SET LINES 300

SQL> SELECT tablespace_name, file_name
2 FROM dba_data_files
3 ORDER BY 1;

TABLESPACE_NAME FILE_NAME
-------------------- ----------------------------------------------------------------------
HRDATA               /u02/oradata/DB11/DB11/datafile/o1_mf_hrdata_jq2x77dc_.dbf
SHDATA               /u02/oradata/DB11/DB11/datafile/o1_mf_shdata_jq4mqyyl_.dbf
SOEDATA              /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp7sy_.dbf
SOEDATA              /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jq2x7f6v_.dbf
SOEDATA              /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp4ol_.dbf
SYSAUX               /u02/oradata/DB11/DB11/datafile/o1_mf_sysaux_jpdvb315_.dbf
SYSTEM               /u02/oradata/DB11/DB11/datafile/o1_mf_system_jpdvb09z_.dbf
UNDOTBS1             /u02/oradata/DB11/DB11/datafile/o1_mf_undotbs1_jpdvb513_.dbf
USERS                /u02/oradata/DB11/DB11/datafile/o1_mf_users_jpdvbbh5_.dbf

9 rows selected.

SQL> COL tablespace_name FORMAT a20

SQL> COL file_name FORMAT a70

SQL> SET LINES 300

SQL> SELECT tablespace_name, file_name

2 FROM dba_data_files

3 ORDER BY 1;

TABLESPACE_NAME FILE_NAME

-------------------- ----------------------------------------------------------------------

HRDATA /u02/oradata/DB11/DB11/datafile/o1_mf_hrdata_jq2x77dc_.dbf

SHDATA /u02/oradata/DB11/DB11/datafile/o1_mf_shdata_jq4mqyyl_.dbf

SOEDATA /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp7sy_.dbf

SOEDATA /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jq2x7f6v_.dbf

SOEDATA /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp4ol_.dbf

SYSAUX /u02/oradata/DB11/DB11/datafile/o1_mf_sysaux_jpdvb315_.dbf

SYSTEM /u02/oradata/DB11/DB11/datafile/o1_mf_system_jpdvb09z_.dbf

UNDOTBS1 /u02/oradata/DB11/DB11/datafile/o1_mf_undotbs1_jpdvb513_.dbf

USERS /u02/oradata/DB11/DB11/datafile/o1_mf_users_jpdvbbh5_.dbf

9 rows selected.

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.

RMAN> LIST BACKUPSET 542;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
542 Incr 0 13.23G SBT_TAPE 00:15:29 31-OCT-21
BP Key: 542 Status: AVAILABLE Compressed: YES Tag: TAG20211031T043005
Handle: /u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-10-31_04-30-03_s544_p1 Media: objectstorage.eu-zurich-..cloud.com/n/zrq012abcaw/onprem-bucket
List of Datafiles in backup set 542
File LV Type Ckp SCN    Ckp Time  Name
---- -- ---- ---------- --------- ----
1    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_system_jpdvb09z_.dbf
2    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_sysaux_jpdvb315_.dbf
3    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_undotbs1_jpdvb513_.dbf
4    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_users_jpdvbbh5_.dbf
5    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_hrdata_jq2x77dc_.dbf
6    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jq2x7f6v_.dbf
7    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_shdata_jq4mqyyl_.dbf
8    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp4ol_.dbf
9    0  Incr 1720448    31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp7sy_.dbf

RMAN> LIST BACKUPSET 542;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

542 Incr 0 13.23G SBT_TAPE 00:15:29 31-OCT-21

BP Key: 542 Status: AVAILABLE Compressed: YES Tag: TAG20211031T043005

Handle: /u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-10-31_04-30-03_s544_p1 Media: objectstorage.eu-zurich-..cloud.com/n/zrq012abcaw/onprem-bucket

List of Datafiles in backup set 542

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_system_jpdvb09z_.dbf

2 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_sysaux_jpdvb315_.dbf

3 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_undotbs1_jpdvb513_.dbf

4 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_users_jpdvbbh5_.dbf

5 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_hrdata_jq2x77dc_.dbf

6 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jq2x7f6v_.dbf

7 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_shdata_jq4mqyyl_.dbf

8 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp4ol_.dbf

9 0 Incr 1720448 31-OCT-21 /u02/oradata/DB11/DB11/datafile/o1_mf_soedata_jqrfp7sy_.dbf

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

[oracle@db11-cloud-12 ~]$ . oraenv
ORACLE_SID = [DB11] ?
The Oracle base has been set to /u01/app/oracle

[oracle@db11-cloud-12 ~]$ . oraenv

ORACLE_SID = [DB11] ?

The Oracle base has been set to /u01/app/oracle

[oracle@db11-cloud-12 ~]$ sqlplus / as sysdba

1	[oracle@db11-cloud-12 ~]$ sqlplus / as sysdba

Query controlfile information.

SQL> SELECT name FROM v$controlfile;

NAME
--------------------------------------------------------------------------------
+RECO/db11_zrh1ws/controlfile/current.256.1087481145

SQL> SELECT name FROM v$controlfile;

NAME

--------------------------------------------------------------------------------

+RECO/db11_zrh1ws/controlfile/current.256.1087481145

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

SQL> COL tablespace_name FORMAT a20
SQL> COL file_name FORMAT a70
SQL> SET LINES 300

SQL> SELECT tablespace_name, file_name
  2> FROM dba_data_files
  3> ORDER BY 1;

TABLESPACE_NAME FILE_NAME
-------------------- ----------------------------------------------------------------------
SYSAUX               +DATA/db11_zrh1ws/datafile/sysaux.262.1087481085
SYSTEM               +DATA/db11_zrh1ws/datafile/system.261.1087481085
UNDOTBS1             +DATA/db11_zrh1ws/datafile/undotbs1.263.1087481085
USERS                +DATA/db11_zrh1ws/datafile/users.266.1087481469

SQL> COL tablespace_name FORMAT a20

SQL> COL file_name FORMAT a70

SQL> SET LINES 300

SQL> SELECT tablespace_name, file_name

2> FROM dba_data_files

3> ORDER BY 1;

TABLESPACE_NAME FILE_NAME

-------------------- ----------------------------------------------------------------------

SYSAUX +DATA/db11_zrh1ws/datafile/sysaux.262.1087481085

SYSTEM +DATA/db11_zrh1ws/datafile/system.261.1087481085

UNDOTBS1 +DATA/db11_zrh1ws/datafile/undotbs1.263.1087481085

USERS +DATA/db11_zrh1ws/datafile/users.266.1087481469

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.

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          YES

SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME ENC

------------------------------ ---

SYSTEM NO

SYSAUX NO

UNDOTBS1 NO

TEMP NO

USERS YES

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

[opc@db11-cloud-12 ~]$ sudo su - oracle

1	[opc@db11-cloud-12 ~]$ sudo su - oracle

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

[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci
[oracle@db11-cloud-12 ~]$ cd /u01/app/oracle/oci

[oracle@db11-cloud-12 oci]$ ll
total 1776
-rw-r--r-- 1 oracle oinstall 1816114 Nov 1 14:29 opc_installer.zip

[oracle@db11-cloud-12 oci]$ unzip opc_installer.zi

[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci

[oracle@db11-cloud-12 ~]$ cd /u01/app/oracle/oci

[oracle@db11-cloud-12 oci]$ ll

total 1776

-rw-r--r-- 1 oracle oinstall 1816114 Nov 1 14:29 opc_installer.zip

[oracle@db11-cloud-12 oci]$ unzip opc_installer.zi

Create directories for wallet and library.

[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci/oci_wallet/
[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci/lib/

1 2	[oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci/oci_wallet/ [oracle@db11-cloud-12 ~]$ mkdir -p /u01/app/oracle/oci/lib/

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

[oracle@db11-cloud-12 oci]$ ll /u01/app/oracle/oci/oci_wallet/
total 4
-rw-r--r-- 1 oracle oinstall 1703 Nov 1 14:31 oci_pvt

[oracle@db11-cloud-12 oci]$ ll /u01/app/oracle/oci/oci_wallet/

total 4

-rw-r--r-- 1 oracle oinstall 1703 Nov 1 14:31 oci_pvt

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.

[oracle@db11-cloud-12 stage]$ . oraenv
ORACLE_SID = [DB11] ?
The Oracle base has been set to /u01/app/oracle

[oracle@db11-cloud-12 stage]$ . oraenv

ORACLE_SID = [DB11] ?

The Oracle base has been set to /u01/app/oracle

[oracle@db11-cloud-12 oci]$ java -jar /u01/app/oracle/oci/opc_installer/oci_installer/oci_install.jar \
-host https://objectstorage.eu-zurich-1.oraclecloud.com \
-uOCID ocid1.user.oc1..aaaaaaaaeif6sfiwfjukvylfs6ps34gypvep4703Mohnweg \
-tOCID ocid1.tenancy.oc1..aaaaaaaaxuk4je4t3aorovuzmwyeaq5sftqv3nkyz64sni4703Mohnweg \
-walletDir /u01/app/oracle/oci/oci_wallet \
-libDir /u01/app/oracle/oci/lib \
-pvtKeyFile /u01/app/oracle/oci/oci_wallet/oci_pvt \
-pubfingerprint 16:48:7b:21:f0:31:74:9e:t3:u8:v2:w9:x1:y9:zd:77 \
-bucket onprem-bucket

[oracle@db11-cloud-12 oci]$ java -jar /u01/app/oracle/oci/opc_installer/oci_installer/oci_install.jar \

-host https://objectstorage.eu-zurich-1.oraclecloud.com \

-uOCID ocid1.user.oc1..aaaaaaaaeif6sfiwfjukvylfs6ps34gypvep4703Mohnweg \

-tOCID ocid1.tenancy.oc1..aaaaaaaaxuk4je4t3aorovuzmwyeaq5sftqv3nkyz64sni4703Mohnweg \

-walletDir /u01/app/oracle/oci/oci_wallet \

-libDir /u01/app/oracle/oci/lib \

-pvtKeyFile /u01/app/oracle/oci/oci_wallet/oci_pvt \

-pubfingerprint 16:48:7b:21:f0:31:74:9e:t3:u8:v2:w9:x1:y9:zd:77 \

-bucket onprem-bucket

Installer Output.

Oracle Database Cloud Backup Module Install Tool, build 19.3.0.0.0DBBKPCSBP_2019-10-16
Oracle Database Cloud Backup Module credentials are valid.
Backups would be sent to bucket onprem-bucket.
Oracle Database Cloud Backup Module wallet created in directory /u01/app/oracle/oci/oci_wallet.
Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora created.
Downloading Oracle Database Cloud Backup Module Software Library from Oracle Cloud Infrastructure.
Download complete.

Oracle Database Cloud Backup Module Install Tool, build 19.3.0.0.0DBBKPCSBP_2019-10-16

Oracle Database Cloud Backup Module credentials are valid.

Backups would be sent to bucket onprem-bucket.

Oracle Database Cloud Backup Module wallet created in directory /u01/app/oracle/oci/oci_wallet.

Oracle Database Cloud Backup Module initialization file /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora created.

Downloading Oracle Database Cloud Backup Module Software Library from Oracle Cloud Infrastructure.

Download complete.

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

[oracle@db11-cloud-12 oci]$ cat $ORACLE_HOME/dbs/opcDB11.ora
OPC_HOST=https://objectstorage.eu-zurich-1.oraclecloud.com/n/zrq07abcdefgh
OPC_WALLET='LOCATION=file:/u01/app/oracle/oci/oci_wallet CREDENTIAL_ALIAS=alias_oci'
OPC_CONTAINER=onprem-bucket
OPC_COMPARTMENT_ID=ocid1.compartment.oc1..aaaaaaaat5uo2xh77edws4huwvqorengp7x4xdv6x3giw3vr4703Mohnweg
OPC_AUTH_SCHEME=BMC

[oracle@db11-cloud-12 oci]$ cat $ORACLE_HOME/dbs/opcDB11.ora

OPC_HOST=https://objectstorage.eu-zurich-1.oraclecloud.com/n/zrq07abcdefgh

OPC_WALLET='LOCATION=file:/u01/app/oracle/oci/oci_wallet CREDENTIAL_ALIAS=alias_oci'

OPC_CONTAINER=onprem-bucket

OPC_COMPARTMENT_ID=ocid1.compartment.oc1..aaaaaaaat5uo2xh77edws4huwvqorengp7x4xdv6x3giw3vr4703Mohnweg

OPC_AUTH_SCHEME=BMC

5. Clean up OCI Cloud Database and restart NOMOUNT

[opc@db11-cloud-12 ~]$ sudo su - grid

1	[opc@db11-cloud-12 ~]$ sudo su - grid

Set environment to database DB11.

[grid@db11-cloud-12 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? DB11
The Oracle base has been set to /u01/app/oracle

[grid@db11-cloud-12 ~]$ . oraenv

ORACLE_SID = [+ASM1] ? DB11

The Oracle base has been set to /u01/app/oracle

Shutdown the database instance by using the database unique name.

[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws
Instance DB11 is running on node db11-cloud-12

[grid@db11-cloud-12 ~]$ srvctl stop database -d db11_zrh1ws

[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws
Instance DB11 is not running on node db11-cloud-12

[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws

Instance DB11 is running on node db11-cloud-12

[grid@db11-cloud-12 ~]$ srvctl stop database -d db11_zrh1ws

[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws

Instance DB11 is not running on node db11-cloud-12

Error message when environment for +ASM is set:

[grid@db11-cloud-12 ~]$ srvctl stop database -d db11_zrh1ws
PRCD-1229 : An attempt to access configuration of database db11_zrh1ws was rejected because its version 11.2.0.4.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/11.2.0.4/dbhome_1.

1 2	[grid@db11-cloud-12 ~]$ srvctl stop database -d db11_zrh1ws PRCD-1229 : An attempt to access configuration of database db11_zrh1ws was rejected because its version 11.2.0.4.0 differs from the program version 19.0.0.0.0. Instead run the program from /u01/app/oracle/product/11.2.0.4/dbhome_1.

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

[grid@db11-cloud-12 ~]$ . oraenv
ORACLE_SID = [DB11] ? +ASM
ORACLE_HOME = [/home/oracle] ? /u01/app/19.0.0.0/grid
The Oracle base has been changed from /u01/app/oracle to /u01/app/grid

[grid@db11-cloud-12 ~]$ . oraenv

ORACLE_SID = [DB11] ? +ASM

ORACLE_HOME = [/home/oracle] ? /u01/app/19.0.0.0/grid

The Oracle base has been changed from /u01/app/oracle to /u01/app/grid

[grid@db11-cloud-12 ~]$ asmcmd
ASMCMD> cd RECO
ASMCMD> rm -rf DB11_ZRH1WS/
ASMCMD> cd ..
ASMCMD> cd DATA
ASMCMD> cd DB11_ZRH1WS

[grid@db11-cloud-12 ~]$ asmcmd

ASMCMD> cd RECO

ASMCMD> rm -rf DB11_ZRH1WS/

ASMCMD> cd ..

ASMCMD> cd DATA

ASMCMD> cd DB11_ZRH1WS

ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DATAFILE/
Y PARAMETERFILE/
Y TEMPFILE/
PARAMETERFILE UNPROT COARSE NOV 01 14:00:00 N spfileDB11.ora => +DATA/DB11_ZRH1WS/PARAMETERFILE/spfile.265.1087481321

ASMCMD> ls -l

Type Redund Striped Time Sys Name

Y DATAFILE/

Y PARAMETERFILE/

Y TEMPFILE/

PARAMETERFILE UNPROT COARSE NOV 01 14:00:00 N spfileDB11.ora => +DATA/DB11_ZRH1WS/PARAMETERFILE/spfile.265.1087481321

ASMCMD> rm -rf DATAFILE/
ASMCMD> rm -rf TEMPFILE/

1 2	ASMCMD> rm -rf DATAFILE/ ASMCMD> rm -rf TEMPFILE/

Set environment to DB11.

[grid@db11-cloud-12 ~]$ . oraenv
ORACLE_SID = [+ASM] ? DB11
The Oracle base has been changed from /u01/app/grid to /u01/app/oracle

[grid@db11-cloud-12 ~]$ . oraenv

ORACLE_SID = [+ASM] ? DB11

The Oracle base has been changed from /u01/app/grid to /u01/app/oracle

Start database NOMOUNT.

[grid@db11-cloud-12 ~]$ srvctl start database -d db11_zrh1ws -o nomount
[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws
Instance DB11 is running on node db11-cloud-12

[grid@db11-cloud-12 ~]$ srvctl start database -d db11_zrh1ws -o nomount

[grid@db11-cloud-12 ~]$ srvctl status database -d db11_zrh1ws

Instance DB11 is running on node db11-cloud-12

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

[opc@db11-cloud-12 ~]$ sudo su - oracle
Last login: Mon Nov 1 14:40:05 CET 2021

1 2	[opc@db11-cloud-12 ~]$ sudo su - oracle Last login: Mon Nov 1 14:40:05 CET 2021

[oracle@db11-cloud-12 ~]$ . oraenv
ORACLE_SID = [DB11] ?
The Oracle base has been set to /u01/app/oracle

[oracle@db11-cloud-12 ~]$ . oraenv

ORACLE_SID = [DB11] ?

The Oracle base has been set to /u01/app/oracle

[oracle@db11-cloud-12 ~]$ rman target /
RMAN> SET DECRYPTION IDENTIFIED BY '<my-onprem-encryption-password-here';
RMAN> SET DBID=1617630071;

[oracle@db11-cloud-12 ~]$ rman target /

RMAN> SET DECRYPTION IDENTIFIED BY '<my-onprem-encryption-password-here';

RMAN> SET DBID=1617630071;

Cloud Database – Restore Controlfile from Object Storage

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

RMAN> RUN {
2> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
3> RESTORE CONTROLFILE FROM AUTOBACKUP;
4> }

allocated channel: t1
channel t1: SID=176 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=21.0.0.1

Starting restore at 01-NOV-21

channel t1: looking for AUTOBACKUP on day: 20211101
channel t1: AUTOBACKUP found: c-1617637023-20211101-04
channel t1: restoring control file from AUTOBACKUP c-1617637023-20211101-04
channel t1: control file restore from AUTOBACKUP complete
output file name=+RECO/db11_zrh1ws/controlfile/current.256.1087483283
Finished restore at 01-NOV-21
released channel: t1

RMAN> RUN {

2> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';

3> RESTORE CONTROLFILE FROM AUTOBACKUP;

4> }

allocated channel: t1

channel t1: SID=176 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=21.0.0.1

Starting restore at 01-NOV-21

channel t1: looking for AUTOBACKUP on day: 20211101

channel t1: AUTOBACKUP found: c-1617637023-20211101-04

channel t1: restoring control file from AUTOBACKUP c-1617637023-20211101-04

channel t1: control file restore from AUTOBACKUP complete

output file name=+RECO/db11_zrh1ws/controlfile/current.256.1087483283

Finished restore at 01-NOV-21

released channel: t1

Cloud Database – Mount Instance

RMAN> ALTER DATABASE MOUNT;

1	RMAN> ALTER DATABASE MOUNT;

Cloud Database – Restore Instance

Allocate channel for maintenance first.

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

allocated channel: ORA_MAINT_DISK_1
channel ORA_MAINT_DISK_1: SID=318 device type=DISK

RMAN> ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

allocated channel: ORA_MAINT_DISK_1

channel ORA_MAINT_DISK_1: SID=318 device type=DISK

Start restore.

RMAN> RUN {
2> SET NEWNAME FOR DATABASE TO '+DATA';
3> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
4> ALLOCATE CHANNEL t2 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
5> RESTORE DATABASE;
6> SWITCH DATAFILE ALL;
7> }

executing command: SET NEWNAME

allocated channel: t1
channel t1: SID=177 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=21.0.0.1

allocated channel: t2
channel t2: SID=13 device type=SBT_TAPE
channel t2: Oracle Database Backup Service Library VER=21.0.0.1

Starting restore at 01-NOV-21
Starting implicit crosscheck backup at 01-NOV-21
Crosschecked 2 objects
Finished implicit crosscheck backup at 01-NOV-21

Starting implicit crosscheck copy at 01-NOV-21
Finished implicit crosscheck copy at 01-NOV-21

searching for all files in the recovery area
cataloging files...
no files cataloged


channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to +DATA
channel t1: restoring datafile 00002 to +DATA
channel t1: restoring datafile 00003 to +DATA
channel t1: restoring datafile 00004 to +DATA
channel t1: restoring datafile 00005 to +DATA
channel t1: restoring datafile 00006 to +DATA
channel t1: restoring datafile 00007 to +DATA
channel t1: restoring datafile 00008 to +DATA
channel t1: restoring datafile 00009 to +DATA
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-11-01_04-30-04_s569_p1

channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-11-01_04-30-04_s569_p1 tag=TAG20211101T043005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:16:45
Finished restore at 01-NOV-21

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/system.267.1087483371
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/sysaux.268.1087483371
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/undotbs1.261.1087483371
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/users.262.1087483371
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/hrdata.269.1087483371
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/soedata.266.1087483371
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/shdata.270.1087483443
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/soedata.263.1087483371
datafile 9 switched to datafile copy
input datafile copy RECID=18 STAMP=1087484377 file name=+DATA/db11_zrh1ws/datafile/soedata.264.1087483371
released channel: t1
released channel: t2

RMAN> RUN {

2> SET NEWNAME FOR DATABASE TO '+DATA';

3> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';

4> ALLOCATE CHANNEL t2 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';

5> RESTORE DATABASE;

6> SWITCH DATAFILE ALL;

7> }

executing command: SET NEWNAME

allocated channel: t1

channel t1: SID=177 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=21.0.0.1

allocated channel: t2

channel t2: SID=13 device type=SBT_TAPE

channel t2: Oracle Database Backup Service Library VER=21.0.0.1

Starting restore at 01-NOV-21

Starting implicit crosscheck backup at 01-NOV-21

Crosschecked 2 objects

Finished implicit crosscheck backup at 01-NOV-21

Starting implicit crosscheck copy at 01-NOV-21

Finished implicit crosscheck copy at 01-NOV-21

searching for all files in the recovery area

cataloging files...

no files cataloged

channel t1: starting datafile backup set restore

channel t1: specifying datafile(s) to restore from backup set

channel t1: restoring datafile 00001 to +DATA

channel t1: restoring datafile 00002 to +DATA

channel t1: restoring datafile 00003 to +DATA

channel t1: restoring datafile 00004 to +DATA

channel t1: restoring datafile 00005 to +DATA

channel t1: restoring datafile 00006 to +DATA

channel t1: restoring datafile 00007 to +DATA

channel t1: restoring datafile 00008 to +DATA

channel t1: restoring datafile 00009 to +DATA

channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-11-01_04-30-04_s569_p1

channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/inc0_DB11_2021-11-01_04-30-04_s569_p1 tag=TAG20211101T043005

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:16:45

Finished restore at 01-NOV-21

datafile 1 switched to datafile copy

input datafile copy RECID=10 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/system.267.1087483371

datafile 2 switched to datafile copy

input datafile copy RECID=11 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/sysaux.268.1087483371

datafile 3 switched to datafile copy

input datafile copy RECID=12 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/undotbs1.261.1087483371

datafile 4 switched to datafile copy

input datafile copy RECID=13 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/users.262.1087483371

datafile 5 switched to datafile copy

input datafile copy RECID=14 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/hrdata.269.1087483371

datafile 6 switched to datafile copy

input datafile copy RECID=15 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/soedata.266.1087483371

datafile 7 switched to datafile copy

input datafile copy RECID=16 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/shdata.270.1087483443

datafile 8 switched to datafile copy

input datafile copy RECID=17 STAMP=1087484376 file name=+DATA/db11_zrh1ws/datafile/soedata.263.1087483371

datafile 9 switched to datafile copy

input datafile copy RECID=18 STAMP=1087484377 file name=+DATA/db11_zrh1ws/datafile/soedata.264.1087483371

released channel: t1

released channel: t2

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

RMAN> RUN {
2> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';
3> RECOVER DATABASE;
4> }

allocated channel: t1
channel t1: SID=177 device type=SBT_TAPE
channel t1: Oracle Database Backup Service Library VER=21.0.0.1

Starting recover at 01-NOV-21

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1009
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_04-30-04_s570_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_04-30-04_s570_p1 tag=TAG20211101T044520
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:03
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1009.259.1087484649 thread=1 sequence=1009
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1009.259.1087484649 RECID=964 STAMP=1087484650
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1010
channel t1: restoring archived log
archived log thread=1 sequence=1011
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_06-00-03_s574_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_06-00-03_s574_p1 tag=TAG20211101T060005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1010.259.1087484653 thread=1 sequence=1010
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1010.259.1087484653 RECID=966 STAMP=1087484653
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1011.258.1087484653 thread=1 sequence=1011
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1011.258.1087484653 RECID=965 STAMP=1087484653
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1012
channel t1: restoring archived log
archived log thread=1 sequence=1013
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_10-00-03_s578_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_10-00-03_s578_p1 tag=TAG20211101T100005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1012.258.1087484655 thread=1 sequence=1012
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1012.258.1087484655 RECID=968 STAMP=1087484654
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1013.259.1087484655 thread=1 sequence=1013
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1013.259.1087484655 RECID=967 STAMP=1087484654
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=1014
channel t1: restoring archived log
archived log thread=1 sequence=1015
channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_14-00-03_s582_p1
channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_14-00-03_s582_p1 tag=TAG20211101T140005
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1014.259.1087484657 thread=1 sequence=1014
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1014.259.1087484657 RECID=970 STAMP=1087484656
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1015.258.1087484657 thread=1 sequence=1015
channel default: deleting archived log(s)
archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1015.258.1087484657 RECID=969 STAMP=1087484656
unable to find archived log
archived log thread=1 sequence=1016
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/01/2021 15:04:18
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1016 and starting SCN of 1782476

RMAN> RUN {

2> ALLOCATE CHANNEL t1 DEVICE TYPE sbt PARMS 'SBT_LIBRARY=/u01/app/oracle/oci/lib/libopc.so ENV=(OPC_PFILE=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/opcDB11.ora)';

3> RECOVER DATABASE;

4> }

allocated channel: t1

channel t1: SID=177 device type=SBT_TAPE

channel t1: Oracle Database Backup Service Library VER=21.0.0.1

Starting recover at 01-NOV-21

starting media recovery

channel t1: starting archived log restore to default destination

channel t1: restoring archived log

archived log thread=1 sequence=1009

channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_04-30-04_s570_p1

channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_04-30-04_s570_p1 tag=TAG20211101T044520

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:03

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1009.259.1087484649 thread=1 sequence=1009

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1009.259.1087484649 RECID=964 STAMP=1087484650

channel t1: starting archived log restore to default destination

channel t1: restoring archived log

archived log thread=1 sequence=1010

channel t1: restoring archived log

archived log thread=1 sequence=1011

channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_06-00-03_s574_p1

channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_06-00-03_s574_p1 tag=TAG20211101T060005

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:01

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1010.259.1087484653 thread=1 sequence=1010

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1010.259.1087484653 RECID=966 STAMP=1087484653

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1011.258.1087484653 thread=1 sequence=1011

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1011.258.1087484653 RECID=965 STAMP=1087484653

channel t1: starting archived log restore to default destination

channel t1: restoring archived log

archived log thread=1 sequence=1012

channel t1: restoring archived log

archived log thread=1 sequence=1013

channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_10-00-03_s578_p1

channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_10-00-03_s578_p1 tag=TAG20211101T100005

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:01

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1012.258.1087484655 thread=1 sequence=1012

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1012.258.1087484655 RECID=968 STAMP=1087484654

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1013.259.1087484655 thread=1 sequence=1013

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1013.259.1087484655 RECID=967 STAMP=1087484654

channel t1: starting archived log restore to default destination

channel t1: restoring archived log

archived log thread=1 sequence=1014

channel t1: restoring archived log

archived log thread=1 sequence=1015

channel t1: reading from backup piece /u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_14-00-03_s582_p1

channel t1: piece handle=/u01/app/oracle/admin/DB11/backup/arc_DB11_2021-11-01_14-00-03_s582_p1 tag=TAG20211101T140005

channel t1: restored backup piece 1

channel t1: restore complete, elapsed time: 00:00:01

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1014.259.1087484657 thread=1 sequence=1014

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1014.259.1087484657 RECID=970 STAMP=1087484656

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1015.258.1087484657 thread=1 sequence=1015

channel default: deleting archived log(s)

archived log file name=+RECO/db11_zrh1ws/archivelog/2021_11_01/thread_1_seq_1015.258.1087484657 RECID=969 STAMP=1087484656

unable to find archived log

archived log thread=1 sequence=1016

released channel: t1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 11/01/2021 15:04:18

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1016 and starting SCN of 1782476

Cloud Database – Open RESETLOGS

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

7. Encrypt Tablespace

Cloud Database – Verify Tablespaces

SQL> COL tablespace_name FORMAT a20
SQL> COL file_name FORMAT a70
SQL> SET LINES 300

SQL> SELECT tablespace_name, file_name
  2 FROM dba_data_files
  3 ORDER BY 1;

TABLESPACE_NAME FILE_NAME
-------------------- ----------------------------------------------------------------------
HRDATA               +DATA/db11_zrh1ws/datafile/hrdata.269.1087483371
SHDATA               +DATA/db11_zrh1ws/datafile/shdata.270.1087483443
SOEDATA              +DATA/db11_zrh1ws/datafile/soedata.264.1087483371
SOEDATA              +DATA/db11_zrh1ws/datafile/soedata.266.1087483371
SOEDATA              +DATA/db11_zrh1ws/datafile/soedata.263.1087483371
SYSAUX               +DATA/db11_zrh1ws/datafile/sysaux.268.1087483371
SYSTEM               +DATA/db11_zrh1ws/datafile/system.267.1087483371
UNDOTBS1             +DATA/db11_zrh1ws/datafile/undotbs1.261.1087483371
USERS                +DATA/db11_zrh1ws/datafile/users.262.1087483371

9 rows selected.

SQL> COL tablespace_name FORMAT a20

SQL> COL file_name FORMAT a70

SQL> SET LINES 300

SQL> SELECT tablespace_name, file_name

2 FROM dba_data_files

3 ORDER BY 1;

TABLESPACE_NAME FILE_NAME

-------------------- ----------------------------------------------------------------------

HRDATA +DATA/db11_zrh1ws/datafile/hrdata.269.1087483371

SHDATA +DATA/db11_zrh1ws/datafile/shdata.270.1087483443

SOEDATA +DATA/db11_zrh1ws/datafile/soedata.264.1087483371

SOEDATA +DATA/db11_zrh1ws/datafile/soedata.266.1087483371

SOEDATA +DATA/db11_zrh1ws/datafile/soedata.263.1087483371

SYSAUX +DATA/db11_zrh1ws/datafile/sysaux.268.1087483371

SYSTEM +DATA/db11_zrh1ws/datafile/system.267.1087483371

UNDOTBS1 +DATA/db11_zrh1ws/datafile/undotbs1.261.1087483371

USERS +DATA/db11_zrh1ws/datafile/users.262.1087483371

9 rows selected.

Cloud Database – Tablespace Encryption

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

SQL> show parameter encrypt_new_tablespaces

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces string CLOUD_ONLY

SQL> show parameter encrypt_new_tablespaces

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

encrypt_new_tablespaces string CLOUD_ONLY

SQL> col WRL_TYPE format a10
SQL> col WRL_PARAMETER format a60
SQL> col STATUS format a10
SQL> set lines 300
SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER                                                STATUS
---------- ------------------------------------------------------------ ----------
file       /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME      OPEN

SQL> col WRL_TYPE format a10

SQL> col WRL_PARAMETER format a60

SQL> col STATUS format a10

SQL> set lines 300

SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS

---------- ------------------------------------------------------------ ----------

file /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME OPEN

Verify existing actual encryption situation, no tablespaces are encrypted.

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
HRDATA                         NO
SOEDATA                        NO
SHDATA                         NO

8 rows selected.

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME ENC

------------------------------ ---

SYSTEM NO

SYSAUX NO

UNDOTBS1 NO

TEMP NO

USERS NO

HRDATA NO

SOEDATA NO

SHDATA NO

8 rows selected.

Take user tablespaces offline – a small syntax provider script.

SQL> SELECT 'ALTER TABLESPACE '||tablespace_name|| ' offline;'
  2> FROM dba_tablespaces
  3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

'ALTERTABLESPACE'||TABLESPACE_NAME||'OFFLINE;'
--------------------------------------------------------
ALTER TABLESPACE USERS offline;
ALTER TABLESPACE HRDATA offline;
ALTER TABLESPACE SOEDATA offline;
ALTER TABLESPACE SHDATA offline;

SQL> SELECT 'ALTER TABLESPACE '||tablespace_name|| ' offline;'

2> FROM dba_tablespaces

3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

'ALTERTABLESPACE'||TABLESPACE_NAME||'OFFLINE;'

--------------------------------------------------------

ALTER TABLESPACE USERS offline;

ALTER TABLESPACE HRDATA offline;

ALTER TABLESPACE SOEDATA offline;

ALTER TABLESPACE SHDATA offline;

As SYS AS SYSDBA, set new Master Key

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "<your_TDE_Master_Key_here>";

1	SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "<your_TDE_Master_Key_here>";

Encrypt tablespaces – a small syntax provider script.

SQL> SELECT 'ALTER DATABASE DATAFILE '''|| file_name||''' encrypt;'
  2> FROM dba_data_files
  3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

'ALTERDATABASEDATAFILE'''||FILE_NAME||'''ENCRYPT;'
--------------------------------------------------------------------------------------
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/users.262.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/hrdata.269.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.266.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/shdata.270.1087483443' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.263.1087483371' encrypt;
ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.264.1087483371' encrypt;

SQL> SELECT 'ALTER DATABASE DATAFILE '''|| file_name||''' encrypt;'

2> FROM dba_data_files

3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

'ALTERDATABASEDATAFILE'''||FILE_NAME||'''ENCRYPT;'

--------------------------------------------------------------------------------------

ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/users.262.1087483371' encrypt;

ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/hrdata.269.1087483371' encrypt;

ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.266.1087483371' encrypt;

ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/shdata.270.1087483443' encrypt;

ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.263.1087483371' encrypt;

ALTER DATABASE DATAFILE '+DATA/db11_zrh1ws/datafile/soedata.264.1087483371' encrypt;

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

SQL> SELECT 'ALTER TABLESPACE '||tablespace_name|| ' online;'
  2> FROM dba_tablespaces
  3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

'ALTERTABLESPACE'||TABLESPACE_NAME||'ONLINE;'
--------------------------------------------------------
ALTER TABLESPACE USERS online;
ALTER TABLESPACE HRDATA online;
ALTER TABLESPACE SOEDATA online;
ALTER TABLESPACE SHDATA online;

SQL> SELECT 'ALTER TABLESPACE '||tablespace_name|| ' online;'

2> FROM dba_tablespaces

3> WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX','TEMP','UNDOTBS1');

'ALTERTABLESPACE'||TABLESPACE_NAME||'ONLINE;'

--------------------------------------------------------

ALTER TABLESPACE USERS online;

ALTER TABLESPACE HRDATA online;

ALTER TABLESPACE SOEDATA online;

ALTER TABLESPACE SHDATA online;

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

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          YES
HRDATA                         YES
SOEDATA                        YES
SHDATA                         YES

8 rows selected.

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME ENC

------------------------------ ---

SYSTEM NO

SYSAUX NO

UNDOTBS1 NO

TEMP NO

USERS YES

HRDATA YES

SOEDATA YES

SHDATA YES

8 rows selected.

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

[oracle@db11-cloud-12 trace]$ dbv file=+DATA/db11_zrh1ws/datafile/soedata.263.1087483371 USERID=system/<your_SYSTEM_password_here>

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Nov 1 15:20:43 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/db11_zrh1ws/datafile/soedata.263.1087483371

DBVERIFY - Verification complete

Total Pages Examined : 1600000
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 84893
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6273
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 1508834
Highest block SCN : 0 (0.0)

[oracle@db11-cloud-12 trace]$ dbv file=+DATA/db11_zrh1ws/datafile/soedata.263.1087483371 USERID=system/<your_SYSTEM_password_here>

DBVERIFY: Release 11.2.0.4.0 - Production on Mon Nov 1 15:20:43 2021

DBVERIFY - Verification starting : FILE = +DATA/db11_zrh1ws/datafile/soedata.263.1087483371

DBVERIFY - Verification complete

Total Pages Examined : 1600000

Total Pages Processed (Data) : 0

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 84893

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 6273

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Total Pages Encrypted : 1508834

Highest block SCN : 0 (0.0)

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

11g, DBaaS, Oracle Cloud Infrastructure, Oracle Linux, Oracle RDBMS

Never stop Learning – why I love Oracle LiveLabs

Author: Martin Berger 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:

Virtual Box Machines – for example the pre-defined HOL VM from Mike Dietrich
Local virtual machines
Test environments in the company
Oracle LiveSQL
Docker Images from Oracle
… 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:

Connect using Cloud Shell
Connect using MAC or a Windows CYGWIN Emulator
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

Latest Posts

Monitor your Oracle Cloud Infrastructure Autonomous Database with your On-Prem Oracle Enterprise Manager 13c

Architecture

Requirements

Network Security Group NSG

Create and Autonomous Transaction Processing Database

ATP – Database Actions – Access

ATP – Database Actions – User ADBSNMP

ATP – Download Wallet

Oracle Enterprise Manager 13c – ATP Host Name Resolution

Oracle Enterprise Manager 13c – Add Targets Manually

Oracle Enterprise Manager 13c – ADB Detail Page

Summary

Oracle Enterprise Manager 13c Release 5 – Time for Release Update 2

Notes and Links

My Oracle Support Notes

Other Links

Patch Number 33456001

The Environment

Installed Plugins

Prerequisites

OMSPatcher

OPatch

Software Directory

Pre-Check

Apply

1st – Stop the Oracle Management Server

2nd – Apply RU with OMSPatcher

Verification

Next Step

Summary

Oracle Cloud Infrastructure Resource Naming Conventions – A short Friday Blog Post

Cleaning up the OCI Resource Chaos

Naming Convention

Examples

A first View – Network Visualizer

Summary

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

Architecture

Q&A

Links and My Oracle Support Notes

Migration Steps

1. OCI Object Storage

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

On-Prem Database – Specifications

On-Prem Database – Tablespaces and Datafiles

On-Prem Database – RMAN Backup to Oracle Cloud Infrastructure

3. Create a new OCI Database Virtual Machine

Cloud Database – Specifications

Cloud Database – Controlfile, Tablespaces and Datafiles

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

Cloud Database – Oracle Database Backup Service Installation and Configuration

5. Clean up OCI Cloud Database and restart NOMOUNT

6. Restore OCI cloud database from Object Store

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

Cloud Database – Restore Controlfile from Object Storage

Cloud Database – Mount Instance

Cloud Database – Restore Instance

Cloud Database – Open RESETLOGS

7. Encrypt Tablespace

Cloud Database – Verify Tablespaces

Cloud Database – Tablespace Encryption

Summary and what’s next

Never stop Learning – why I love Oracle LiveLabs

Training Environments

Oracle LiveLabs

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

2nd – Define where the Workshop should run

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

4rd – View my Reservation

5th – Launch Workshop

6th – Workshop Details

Summary