Oracle Linux

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

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

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

# rpm -ihv mv2oci-2.0.2-3.el7.x86_64.rpm
warning: mv2oci-2.0.2-3.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 939112d6: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:mv2oci-2.0.2-3 ################################# [100%]

mv2oci-2.0.2.3 binary has been installed on /opt/mv2oci succesfully!

# rpm -ihv mv2oci-2.0.2-3.el7.x86_64.rpm

warning: mv2oci-2.0.2-3.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 939112d6: NOKEY

Verifying... ################################# [100%]

Preparing... ################################# [100%]

Updating / installing...

1:mv2oci-2.0.2-3 ################################# [100%]

mv2oci-2.0.2.3 binary has been installed on /opt/mv2oci succesfully!

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.

$ ssh -i $HOME/.ssh/id_rsa_oci_29012020 opc@172.16.0.8
Last login: Fri Oct 30 05:39:27 2020 from 192.168.1.235
[opc@srv-cdb118 ~]$

$ ssh -i $HOME/.ssh/id_rsa_oci_29012020 opc@172.16.0.8

Last login: Fri Oct 30 05:39:27 2020 from 192.168.1.235

[opc@srv-cdb118 ~]$

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

# ./mv2oci.bin encpass

Please enter the password :
Please re-enter the password :
79F510449D6D43B028EB2E5FCC7CC71A336F363F123456789ABCDEFGHIJKLMNO

# ./mv2oci.bin encpass

Please enter the password :

Please re-enter the password :

79F510449D6D43B028EB2E5FCC7CC71A336F363F123456789ABCDEFGHIJKLMNO

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

# ./mv2oci.bin expdp --conf conf/CDB118.mv2oci.cfg

INFO: 2020-10-30 06:32:51: Please check the logfile '/opt/mv2oci/out/log/mv2oci_314519.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│
mv2oci - Move data to Oracle Cloud Infrastructure (OCI)
Version: 2.0.2-3
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton &lt;ruggero.citton@oracle.com&gt;
RAC Pack, Cloud Innovation and Solution Engineering Team
│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 06:32:51: Reading configuration file 'conf/CDB118.mv2oci.cfg'
INFO: 2020-10-30 06:32:51: Using Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1/'
INFO: 2020-10-30 06:32:51: Getting source DB version
INFO: 2020-10-30 06:32:52: Checking schemas on source database '//heckenweg/pdb11801.kestenholz.net'
INFO: 2020-10-30 06:32:53: Creating expdp directory 'MV2OCI_DP_DIR' for path '/tmp'
INFO: 2020-10-30 06:32:54: Getting latest SCN
INFO: 2020-10-30 06:32:55: Expdp parallelism set by parameter '2'
INFO: 2020-10-30 06:32:55: Executing Expdp as jobname 'MV2OCI_JOB'
INFO: 2020-10-30 06:32:55: Expdp log location available at the end of the process is '/opt/mv2oci/out/log/mv2oci_expdp_314519.log'

FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."MV2OCI_JOB": system/********@//heckenweg/pdb11801.kestenholz.net SILENT=BANNER JOB_NAME=MV2OCI_JOB SCHEMAS=SOE VERSION=19.0.0 DIRECTORY=MV2OCI_DP_DIR DUMPFILE=exp_soe_18102020_%u.dmp PARALLEL=2 REUSE_DUMPFILES=y LOGFILE=mv2oci_expdp_314519.log FILESIZE=4G FLASHBACK_SCN=6980091
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SOE"."ORDERS" 129.1 MB 1429790 rows
. . exported "SOE"."ORDER_ITEMS" 380.1 MB 7138088 rows
. . exported "SOE"."CUSTOMERS" 108.1 MB 1000000 rows
. . exported "SOE"."ADDRESSES" 110.0 MB 1500000 rows
. . exported "SOE"."CARD_DETAILS" 63.87 MB 1500000 rows
. . exported "SOE"."LOGON" 51.24 MB 2382984 rows
. . exported "SOE"."PRODUCT_DESCRIPTIONS" 220.8 KB 1000 rows
. . exported "SOE"."PRODUCT_INFORMATION" 188.6 KB 1000 rows
. . exported "SOE"."WAREHOUSES" 35.42 KB 1000 rows
. . exported "SOE"."ORDERENTRY_METADATA" 5.609 KB 4 rows
. . exported "SOE"."INVENTORIES" 15.27 MB 901957 rows
Master table "SYSTEM"."MV2OCI_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.MV2OCI_JOB is:
/tmp/exp_soe_18102020_01.dmp
/tmp/exp_soe_18102020_02.dmp
Job "SYSTEM"."MV2OCI_JOB" successfully completed at 00:00

INFO: 2020-10-30 06:33:20: Following expdp dump has been created:
DUMP_FILES=/tmp/exp_soe_18102020_01.dmp,/tmp/exp_soe_18102020_02.dmp
SUCCESS: 2020-10-30 06:33:20: Expdp executed successfully

# ./mv2oci.bin expdp --conf conf/CDB118.mv2oci.cfg

INFO: 2020-10-30 06:32:51: Please check the logfile '/opt/mv2oci/out/log/mv2oci_314519.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

mv2oci - Move data to Oracle Cloud Infrastructure (OCI)

Version: 2.0.2-3

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

Author: Ruggero Citton <ruggero.citton@oracle.com>

RAC Pack, Cloud Innovation and Solution Engineering Team

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 06:32:51: Reading configuration file 'conf/CDB118.mv2oci.cfg'

INFO: 2020-10-30 06:32:51: Using Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1/'

INFO: 2020-10-30 06:32:51: Getting source DB version

INFO: 2020-10-30 06:32:52: Checking schemas on source database '//heckenweg/pdb11801.kestenholz.net'

INFO: 2020-10-30 06:32:53: Creating expdp directory 'MV2OCI_DP_DIR' for path '/tmp'

INFO: 2020-10-30 06:32:54: Getting latest SCN

INFO: 2020-10-30 06:32:55: Expdp parallelism set by parameter '2'

INFO: 2020-10-30 06:32:55: Executing Expdp as jobname 'MV2OCI_JOB'

INFO: 2020-10-30 06:32:55: Expdp log location available at the end of the process is '/opt/mv2oci/out/log/mv2oci_expdp_314519.log'

FLASHBACK automatically enabled to preserve database integrity.

Starting "SYSTEM"."MV2OCI_JOB": system/********@//heckenweg/pdb11801.kestenholz.net SILENT=BANNER JOB_NAME=MV2OCI_JOB SCHEMAS=SOE VERSION=19.0.0 DIRECTORY=MV2OCI_DP_DIR DUMPFILE=exp_soe_18102020_%u.dmp PARALLEL=2 REUSE_DUMPFILES=y LOGFILE=mv2oci_expdp_314519.log FILESIZE=4G FLASHBACK_SCN=6980091

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

. . exported "SOE"."ORDERS" 129.1 MB 1429790 rows

. . exported "SOE"."ORDER_ITEMS" 380.1 MB 7138088 rows

. . exported "SOE"."CUSTOMERS" 108.1 MB 1000000 rows

. . exported "SOE"."ADDRESSES" 110.0 MB 1500000 rows

. . exported "SOE"."CARD_DETAILS" 63.87 MB 1500000 rows

. . exported "SOE"."LOGON" 51.24 MB 2382984 rows

. . exported "SOE"."PRODUCT_DESCRIPTIONS" 220.8 KB 1000 rows

. . exported "SOE"."PRODUCT_INFORMATION" 188.6 KB 1000 rows

. . exported "SOE"."WAREHOUSES" 35.42 KB 1000 rows

. . exported "SOE"."ORDERENTRY_METADATA" 5.609 KB 4 rows

. . exported "SOE"."INVENTORIES" 15.27 MB 901957 rows

Master table "SYSTEM"."MV2OCI_JOB" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.MV2OCI_JOB is:

/tmp/exp_soe_18102020_01.dmp

/tmp/exp_soe_18102020_02.dmp

Job "SYSTEM"."MV2OCI_JOB" successfully completed at 00:00

INFO: 2020-10-30 06:33:20: Following expdp dump has been created:

DUMP_FILES=/tmp/exp_soe_18102020_01.dmp,/tmp/exp_soe_18102020_02.dmp

SUCCESS: 2020-10-30 06:33:20: Expdp executed successfully

Dump files created in /tmp.

# ls -la /tmp/*.dmp
-rw-r-----. 1 oracle oinstall 569135104 Oct 30 06:33 /tmp/exp_soe_18102020_01.dmp
-rw-r-----. 1 oracle oinstall 331845632 Oct 30 06:33 /tmp/exp_soe_18102020_02.dmp

# ls -la /tmp/*.dmp

-rw-r-----. 1 oracle oinstall 569135104 Oct 30 06:33 /tmp/exp_soe_18102020_01.dmp

-rw-r-----. 1 oracle oinstall 331845632 Oct 30 06:33 /tmp/exp_soe_18102020_02.dmp

5. Transfer Data – mv2oci putdump

# ./mv2oci.bin putdump --conf conf/CDB118.mv2oci.cfg
INFO: 2020-10-30 06:42:41: Please check the logfile '/opt/mv2oci/out/log/mv2oci_319424.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│
mv2oci - Move data to Oracle Cloud Infrastructure (OCI)
Version: 2.0.2-3
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton &lt;ruggero.citton@oracle.com&gt;
RAC Pack, Cloud Innovation and Solution Engineering Team
│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 06:42:41: Reading configuration file 'conf/CDB118.mv2oci.cfg'
INFO: 2020-10-30 06:42:41: Checking OCHost '172.16.0.8' connectivity
INFO: 2020-10-30 06:42:42: Determining if using rsync or scp to transfer dump files
INFO: 2020-10-30 06:42:42: Using 'rsync' to transfer dump files
INFO: 2020-10-30 06:42:42: Making required dump dir '/tmp' on remote '172.16.0.8'
INFO: 2020-10-30 06:42:42: Uploading dump files over '172.16.0.8' using 'rsync'
INFO: 2020-10-30 06:42:42: Copying /tmp/exp_soe_18102020_01.dmp....
INFO: 2020-10-30 06:42:42: Copying /tmp/exp_soe_18102020_02.dmp....
SUCCESS: 2020-10-30 06:43:06: Dump file ' /tmp/exp_soe_18102020_02.dmp' uploaded successfully
SUCCESS: 2020-10-30 06:43:24: Dump file '/tmp/exp_soe_18102020_01.dmp' uploaded successfully
INFO: 2020-10-30 06:43:24: Setting required permission for dump dir '/tmp' on remote '172.16.0.8'
SUCCESS: 2020-10-30 06:43:28: Dump files uploaded over '172.16.0.8' successfully

# ./mv2oci.bin putdump --conf conf/CDB118.mv2oci.cfg

INFO: 2020-10-30 06:42:41: Please check the logfile '/opt/mv2oci/out/log/mv2oci_319424.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

mv2oci - Move data to Oracle Cloud Infrastructure (OCI)

Version: 2.0.2-3

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

Author: Ruggero Citton <ruggero.citton@oracle.com>

RAC Pack, Cloud Innovation and Solution Engineering Team

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 06:42:41: Reading configuration file 'conf/CDB118.mv2oci.cfg'

INFO: 2020-10-30 06:42:41: Checking OCHost '172.16.0.8' connectivity

INFO: 2020-10-30 06:42:42: Determining if using rsync or scp to transfer dump files

INFO: 2020-10-30 06:42:42: Using 'rsync' to transfer dump files

INFO: 2020-10-30 06:42:42: Making required dump dir '/tmp' on remote '172.16.0.8'

INFO: 2020-10-30 06:42:42: Uploading dump files over '172.16.0.8' using 'rsync'

INFO: 2020-10-30 06:42:42: Copying /tmp/exp_soe_18102020_01.dmp....

INFO: 2020-10-30 06:42:42: Copying /tmp/exp_soe_18102020_02.dmp....

SUCCESS: 2020-10-30 06:43:06: Dump file ' /tmp/exp_soe_18102020_02.dmp' uploaded successfully

SUCCESS: 2020-10-30 06:43:24: Dump file '/tmp/exp_soe_18102020_01.dmp' uploaded successfully

INFO: 2020-10-30 06:43:24: Setting required permission for dump dir '/tmp' on remote '172.16.0.8'

SUCCESS: 2020-10-30 06:43:28: Dump files uploaded over '172.16.0.8' successfully

Files are available now on target server.

$ ls -la /tmp/*.dmp
-rw-rw-rw- 1 oracle oinstall 569135104 Oct 30 05:43 /tmp/exp_soe_18102020_01.dmp
-rw-rw-rw- 1 oracle oinstall 331845632 Oct 30 05:43 /tmp/exp_soe_18102020_02.dmp

$ ls -la /tmp/*.dmp

-rw-rw-rw- 1 oracle oinstall 569135104 Oct 30 05:43 /tmp/exp_soe_18102020_01.dmp

-rw-rw-rw- 1 oracle oinstall 331845632 Oct 30 05:43 /tmp/exp_soe_18102020_02.dmp

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.

# ./mv2oci.bin impdp --conf conf/CDB118.mv2oci.cfg
INFO: 2020-10-30 06:49:33: Please check the logfile '/opt/mv2oci/out/log/mv2oci_322277.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│
mv2oci - Move data to Oracle Cloud Infrastructure (OCI)
Version: 2.0.2-3
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton &lt;ruggero.citton@oracle.com&gt;
RAC Pack, Cloud Innovation and Solution Engineering Team
│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 06:49:33: Reading configuration file 'conf/CDB118.mv2oci.cfg'
INFO: 2020-10-30 06:49:33: Using Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1/'
INFO: 2020-10-30 06:49:33: Expdp parallelism set by parameter '2'
INFO: 2020-10-30 06:49:33: Creating impdp directory 'MV2OCI_DP_DIR' for path '/tmp' on '172.16.0.8'
INFO: 2020-10-30 06:49:34: Executing import datapump as jobname 'MV2OCI_JOB'
INFO: 2020-10-30 06:49:34: Impdp log location available at the end of the process is '/opt/mv2oci/out/log/mv2oci_impdp_322277.log'
Master table "SYSTEM"."MV2OCI_JOB" successfully loaded/unloaded
Starting "SYSTEM"."MV2OCI_JOB": system/********@//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com SILENT=BANNER JOB_NAME=MV2OCI_JOB DIRECTORY=MV2OCI_DP_DIR DUMPFILE=exp_soe_18102020_01.dmp,exp_soe_18102020_02.dmp LOGFILE=mv2oci_impdp_322277.log PARALLEL=2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SOE"."ORDERS" 129.1 MB 1429790 rows
. . imported "SOE"."CUSTOMERS" 108.1 MB 1000000 rows
. . imported "SOE"."ADDRESSES" 110.0 MB 1500000 rows
. . imported "SOE"."ORDER_ITEMS" 380.1 MB 7138088 rows
. . imported "SOE"."CARD_DETAILS" 63.87 MB 1500000 rows
. . imported "SOE"."LOGON" 51.24 MB 2382984 rows
. . imported "SOE"."PRODUCT_DESCRIPTIONS" 220.8 KB 1000 rows
. . imported "SOE"."PRODUCT_INFORMATION" 188.6 KB 1000 rows
. . imported "SOE"."WAREHOUSES" 35.42 KB 1000 rows
. . imported "SOE"."ORDERENTRY_METADATA" 5.609 KB 4 rows
. . imported "SOE"."INVENTORIES" 15.27 MB 901957 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39082: Object type PACKAGE BODY:"SOE"."ORDERENTRY" created with compilation warnings

Job "SYSTEM"."MV2OCI_JOB" completed with 1 error(s) at 00:00

ERROR: 2020-10-30 06:51:14: Executing impdp on remote '//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com'

# ./mv2oci.bin impdp --conf conf/CDB118.mv2oci.cfg

INFO: 2020-10-30 06:49:33: Please check the logfile '/opt/mv2oci/out/log/mv2oci_322277.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

mv2oci - Move data to Oracle Cloud Infrastructure (OCI)

Version: 2.0.2-3

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

Author: Ruggero Citton <ruggero.citton@oracle.com>

RAC Pack, Cloud Innovation and Solution Engineering Team

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 06:49:33: Reading configuration file 'conf/CDB118.mv2oci.cfg'

INFO: 2020-10-30 06:49:33: Using Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1/'

INFO: 2020-10-30 06:49:33: Expdp parallelism set by parameter '2'

INFO: 2020-10-30 06:49:33: Creating impdp directory 'MV2OCI_DP_DIR' for path '/tmp' on '172.16.0.8'

INFO: 2020-10-30 06:49:34: Executing import datapump as jobname 'MV2OCI_JOB'

INFO: 2020-10-30 06:49:34: Impdp log location available at the end of the process is '/opt/mv2oci/out/log/mv2oci_impdp_322277.log'

Master table "SYSTEM"."MV2OCI_JOB" successfully loaded/unloaded

Starting "SYSTEM"."MV2OCI_JOB": system/********@//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com SILENT=BANNER JOB_NAME=MV2OCI_JOB DIRECTORY=MV2OCI_DP_DIR DUMPFILE=exp_soe_18102020_01.dmp,exp_soe_18102020_02.dmp LOGFILE=mv2oci_impdp_322277.log PARALLEL=2

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SOE"."ORDERS" 129.1 MB 1429790 rows

. . imported "SOE"."CUSTOMERS" 108.1 MB 1000000 rows

. . imported "SOE"."ADDRESSES" 110.0 MB 1500000 rows

. . imported "SOE"."ORDER_ITEMS" 380.1 MB 7138088 rows

. . imported "SOE"."CARD_DETAILS" 63.87 MB 1500000 rows

. . imported "SOE"."LOGON" 51.24 MB 2382984 rows

. . imported "SOE"."PRODUCT_DESCRIPTIONS" 220.8 KB 1000 rows

. . imported "SOE"."PRODUCT_INFORMATION" 188.6 KB 1000 rows

. . imported "SOE"."WAREHOUSES" 35.42 KB 1000 rows

. . imported "SOE"."ORDERENTRY_METADATA" 5.609 KB 4 rows

. . imported "SOE"."INVENTORIES" 15.27 MB 901957 rows

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

ORA-39082: Object type PACKAGE BODY:"SOE"."ORDERENTRY" created with compilation warnings

Job "SYSTEM"."MV2OCI_JOB" completed with 1 error(s) at 00:00

ERROR: 2020-10-30 06:51:14: Executing impdp on remote '//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com'

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

This is an easy thing:

SQL&gt; GRANT EXECUTE ON dbms_lock TO SOE;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_lock TO SOE;

Grant succeeded.

7. Reporting – mv2oci report

The report compares the objects on source and target database.

# ./mv2oci.bin report --conf conf/CDB118.mv2oci.cfg
INFO: 2020-10-30 07:00:13: Please check the logfile '/opt/mv2oci/out/log/mv2oci_327866.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│
mv2oci - Move data to Oracle Cloud Infrastructure (OCI)
Version: 2.0.2-3
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton &lt;ruggero.citton@oracle.com&gt;
RAC Pack, Cloud Innovation and Solution Engineering Team
│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 07:00:13: Reading configuration file 'conf/CDB118.mv2oci.cfg'
INFO: 2020-10-30 07:00:14: using Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1/'
INFO: 2020-10-30 07:00:14: Getting Obj report for '//heckenweg/pdb11801.kestenholz.net'

0
Database Objects REPORT for //heckenweg/pdb11801.kestenholz.net

 SOE       |INDEX       |        27
 SOE       |PACKAGE     |         1
 SOE       |TABLE       |        11
 SOE       |VIEW        |         2
 SOE       |SEQUENCE    |         5
 SOE       |PACKAGE BODY|         1
           |            |----------
 sum       |            |        47

INFO: 2020-10-30 07:00:15: Getting Segments report for '//heckenweg/pdb11801.kestenholz.net'

0
         Database Segments REPORT for //heckenweg/pdb11801.kestenholz.net

 SOE       |PRD_DESC_PK                   |INDEX          |      .125
 SOE       |WHS_LOCATION_IX               |INDEX          |     .0625
 SOE       |ORD_CUSTOMER_IX               |INDEX          |   27.1875
 SOE       |INVENTORIES                   |TABLE          |       176
 SOE       |ORD_WAREHOUSE_IX              |INDEX          |   30.1875
 SOE       |INVENTORY_PK                  |INDEX          |        18
 SOE       |CARD_DETAILS                  |TABLE          |        80
 SOE       |ORDERS                        |TABLE          |       160
 SOE       |CUSTOMERS_PK                  |INDEX          |    18.625
 SOE       |PROD_SUPPLIER_IX              |INDEX          |      .125
 SOE       |PRODUCT_INFORMATION           |TABLE          |       .25
 SOE       |ORDER_ITEMS_PK                |INDEX          |     146.5
 SOE       |ORDER_PK                      |INDEX          |   25.9375
 SOE       |ORD_ORDER_DATE_IX             |INDEX          |    31.875
 SOE       |INV_WAREHOUSE_IX              |INDEX          |   16.6875
 SOE       |ORDERENTRY_METADATA           |TABLE          |     .0625
 SOE       |CARD_DETAILS_PK               |INDEX          |   27.1875
 SOE       |ORD_SALES_REP_IX              |INDEX          |   25.5625
 SOE       |PROD_CATEGORY_IX              |INDEX          |     .0625
 SOE       |CUST_ACCOUNT_MANAGER_IX       |INDEX          |    18.625
 SOE       |CUSTOMERS                     |TABLE          |       136
 SOE       |ADDRESSES                     |TABLE          |       136
 SOE       |ORDER_ITEMS                   |TABLE          |       464
 SOE       |PRODUCT_INFORMATION_PK        |INDEX          |     .0625
 SOE       |ADDRESS_CUST_IX               |INDEX          |   28.4375
 SOE       |ITEM_PRODUCT_IX               |INDEX          |  120.5625
 SOE       |LOGON                         |TABLE          |        64
 SOE       |PRODUCT_DESCRIPTIONS          |TABLE          |     .3125
 SOE       |ADDRESS_PK                    |INDEX          |    27.125
 SOE       |WAREHOUSES_PK                 |INDEX          |     .0625
 SOE       |PROD_NAME_IX                  |INDEX          |      .125
 SOE       |CARDDETAILS_CUST_IX           |INDEX          |   28.4375
 SOE       |WAREHOUSES                    |TABLE          |     .0625
 SOE       |ITEM_ORDER_IX                 |INDEX          |   130.875
 SOE       |INV_PRODUCT_IX                |INDEX          |     16.75
 SOE       |CUST_DOB_IX                   |INDEX          |   23.1875
 SOE       |CUST_EMAIL_IX                 |INDEX          |     41.25
 SOE       |CUST_FUNC_LOWER_NAME_IX       |INDEX          |   29.0625
           |                              |               |----------
 sum       |                              |               |  2049.375

INFO: 2020-10-30 07:00:16: Getting Obj report for '//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com'

0
 Database Objects REPORT for //172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.or

 SOE       |INDEX       |        27
 SOE       |PACKAGE     |         1
 SOE       |SEQUENCE    |         5
 SOE       |TABLE       |        11
 SOE       |VIEW        |         2
 SOE       |PACKAGE BODY|         1
           |            |----------
 sum       |            |        47


INFO: 2020-10-30 07:00:17: Getting Segments report for '//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com'

0
 Database Segments REPORT for //172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.o

 SOE       |WHS_LOCATION_IX               |INDEX          |     .0625
 SOE       |PRD_DESC_PK                   |INDEX          |     .0625
 SOE       |ORD_CUSTOMER_IX               |INDEX          |        26
 SOE       |INVENTORIES                   |TABLE          |       176
 SOE       |ORD_WAREHOUSE_IX              |INDEX          |        29
 SOE       |INVENTORY_PK                  |INDEX          |        18
 SOE       |ORDERS                        |TABLE          |       160
 SOE       |CARD_DETAILS                  |TABLE          |        80
 SOE       |PROD_SUPPLIER_IX              |INDEX          |     .0625
 SOE       |CUSTOMERS_PK                  |INDEX          |        17
 SOE       |PRODUCT_INFORMATION           |TABLE          |       .25
 SOE       |ORDER_ITEMS_PK                |INDEX          |       144
 SOE       |ORDER_PK                      |INDEX          |        25
 SOE       |ORD_ORDER_DATE_IX             |INDEX          |        31
 SOE       |INV_WAREHOUSE_IX              |INDEX          |        15
 SOE       |ORDERENTRY_METADATA           |TABLE          |     .0625
 SOE       |PROD_CATEGORY_IX              |INDEX          |     .0625
 SOE       |ORD_SALES_REP_IX              |INDEX          |        24
 SOE       |CUST_ACCOUNT_MANAGER_IX       |INDEX          |        17
 SOE       |CARD_DETAILS_PK               |INDEX          |        26
 SOE       |ADDRESSES                     |TABLE          |       136
 SOE       |CUSTOMERS                     |TABLE          |       136
 SOE       |ORDER_ITEMS                   |TABLE          |       464
 SOE       |ADDRESS_CUST_IX               |INDEX          |        27
 SOE       |ITEM_PRODUCT_IX               |INDEX          |       120
 SOE       |PRODUCT_INFORMATION_PK        |INDEX          |     .0625
 SOE       |PRODUCT_DESCRIPTIONS          |TABLE          |     .3125
 SOE       |LOGON                         |TABLE          |        64
 SOE       |WAREHOUSES_PK                 |INDEX          |     .0625
 SOE       |ADDRESS_PK                    |INDEX          |        26
 SOE       |PROD_NAME_IX                  |INDEX          |      .125
 SOE       |CARDDETAILS_CUST_IX           |INDEX          |        27
 SOE       |WAREHOUSES                    |TABLE          |     .0625
 SOE       |ITEM_ORDER_IX                 |INDEX          |       136
 SOE       |CUST_DOB_IX                   |INDEX          |        22
 SOE       |CUST_EMAIL_IX                 |INDEX          |        40
 SOE       |INV_PRODUCT_IX                |INDEX          |        15
 SOE       |CUST_FUNC_LOWER_NAME_IX       |INDEX          |        28
           |                              |               |----------
 sum       |                              |               | 2030.1875

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

# ./mv2oci.bin report --conf conf/CDB118.mv2oci.cfg

INFO: 2020-10-30 07:00:13: Please check the logfile '/opt/mv2oci/out/log/mv2oci_327866.log' for more details

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

mv2oci - Move data to Oracle Cloud Infrastructure (OCI)

Version: 2.0.2-3

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

Author: Ruggero Citton <ruggero.citton@oracle.com>

RAC Pack, Cloud Innovation and Solution Engineering Team

│▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒│

INFO: 2020-10-30 07:00:13: Reading configuration file 'conf/CDB118.mv2oci.cfg'

INFO: 2020-10-30 07:00:14: using Oracle Home '/u01/app/oracle/product/19.0.0/dbhome_1/'

INFO: 2020-10-30 07:00:14: Getting Obj report for '//heckenweg/pdb11801.kestenholz.net'

Database Objects REPORT for //heckenweg/pdb11801.kestenholz.net

SOE |INDEX | 27

SOE |PACKAGE | 1

SOE |TABLE | 11

SOE |VIEW | 2

SOE |SEQUENCE | 5

SOE |PACKAGE BODY| 1

| |----------

sum | | 47

INFO: 2020-10-30 07:00:15: Getting Segments report for '//heckenweg/pdb11801.kestenholz.net'

Database Segments REPORT for //heckenweg/pdb11801.kestenholz.net

SOE |PRD_DESC_PK |INDEX | .125

SOE |WHS_LOCATION_IX |INDEX | .0625

SOE |ORD_CUSTOMER_IX |INDEX | 27.1875

SOE |INVENTORIES |TABLE | 176

SOE |ORD_WAREHOUSE_IX |INDEX | 30.1875

SOE |INVENTORY_PK |INDEX | 18

SOE |CARD_DETAILS |TABLE | 80

SOE |ORDERS |TABLE | 160

SOE |CUSTOMERS_PK |INDEX | 18.625

SOE |PROD_SUPPLIER_IX |INDEX | .125

SOE |PRODUCT_INFORMATION |TABLE | .25

SOE |ORDER_ITEMS_PK |INDEX | 146.5

SOE |ORDER_PK |INDEX | 25.9375

SOE |ORD_ORDER_DATE_IX |INDEX | 31.875

SOE |INV_WAREHOUSE_IX |INDEX | 16.6875

SOE |ORDERENTRY_METADATA |TABLE | .0625

SOE |CARD_DETAILS_PK |INDEX | 27.1875

SOE |ORD_SALES_REP_IX |INDEX | 25.5625

SOE |PROD_CATEGORY_IX |INDEX | .0625

SOE |CUST_ACCOUNT_MANAGER_IX |INDEX | 18.625

SOE |CUSTOMERS |TABLE | 136

SOE |ADDRESSES |TABLE | 136

SOE |ORDER_ITEMS |TABLE | 464

SOE |PRODUCT_INFORMATION_PK |INDEX | .0625

SOE |ADDRESS_CUST_IX |INDEX | 28.4375

SOE |ITEM_PRODUCT_IX |INDEX | 120.5625

SOE |LOGON |TABLE | 64

SOE |PRODUCT_DESCRIPTIONS |TABLE | .3125

SOE |ADDRESS_PK |INDEX | 27.125

SOE |WAREHOUSES_PK |INDEX | .0625

SOE |PROD_NAME_IX |INDEX | .125

SOE |CARDDETAILS_CUST_IX |INDEX | 28.4375

SOE |WAREHOUSES |TABLE | .0625

SOE |ITEM_ORDER_IX |INDEX | 130.875

SOE |INV_PRODUCT_IX |INDEX | 16.75

SOE |CUST_DOB_IX |INDEX | 23.1875

SOE |CUST_EMAIL_IX |INDEX | 41.25

SOE |CUST_FUNC_LOWER_NAME_IX |INDEX | 29.0625

| | |----------

sum | | | 2049.375

INFO: 2020-10-30 07:00:16: Getting Obj report for '//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com'

Database Objects REPORT for //172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.or

SOE |INDEX | 27

SOE |PACKAGE | 1

SOE |SEQUENCE | 5

SOE |TABLE | 11

SOE |VIEW | 2

SOE |PACKAGE BODY| 1

| |----------

sum | | 47

INFO: 2020-10-30 07:00:17: Getting Segments report for '//172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.oraclevcn.com'

Database Segments REPORT for //172.16.0.8/pdboci.subnetvcnmohnwe.vcnmohnwegvpn.o

SOE |WHS_LOCATION_IX |INDEX | .0625

SOE |PRD_DESC_PK |INDEX | .0625

SOE |ORD_CUSTOMER_IX |INDEX | 26

SOE |INVENTORIES |TABLE | 176

SOE |ORD_WAREHOUSE_IX |INDEX | 29

SOE |INVENTORY_PK |INDEX | 18

SOE |ORDERS |TABLE | 160

SOE |CARD_DETAILS |TABLE | 80

SOE |PROD_SUPPLIER_IX |INDEX | .0625

SOE |CUSTOMERS_PK |INDEX | 17

SOE |PRODUCT_INFORMATION |TABLE | .25

SOE |ORDER_ITEMS_PK |INDEX | 144

SOE |ORDER_PK |INDEX | 25

SOE |ORD_ORDER_DATE_IX |INDEX | 31

SOE |INV_WAREHOUSE_IX |INDEX | 15

SOE |ORDERENTRY_METADATA |TABLE | .0625

SOE |PROD_CATEGORY_IX |INDEX | .0625

SOE |ORD_SALES_REP_IX |INDEX | 24

SOE |CUST_ACCOUNT_MANAGER_IX |INDEX | 17

SOE |CARD_DETAILS_PK |INDEX | 26

SOE |ADDRESSES |TABLE | 136

SOE |CUSTOMERS |TABLE | 136

SOE |ORDER_ITEMS |TABLE | 464

SOE |ADDRESS_CUST_IX |INDEX | 27

SOE |ITEM_PRODUCT_IX |INDEX | 120

SOE |PRODUCT_INFORMATION_PK |INDEX | .0625

SOE |PRODUCT_DESCRIPTIONS |TABLE | .3125

SOE |LOGON |TABLE | 64

SOE |WAREHOUSES_PK |INDEX | .0625

SOE |ADDRESS_PK |INDEX | 26

SOE |PROD_NAME_IX |INDEX | .125

SOE |CARDDETAILS_CUST_IX |INDEX | 27

SOE |WAREHOUSES |TABLE | .0625

SOE |ITEM_ORDER_IX |INDEX | 136

SOE |CUST_DOB_IX |INDEX | 22

SOE |CUST_EMAIL_IX |INDEX | 40

SOE |INV_PRODUCT_IX |INDEX | 15

SOE |CUST_FUNC_LOWER_NAME_IX |INDEX | 28

| | |----------

sum | | | 2030.1875

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).

# ./mv2oci.bin auto --conf conf/CDB118.mv2oci.cfg

1	# ./mv2oci.bin auto --conf conf/CDB118.mv2oci.cfg

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: Martin Berger 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).

oracle@mohnweg:/u01/app/19.0.0/grid/OPatch/ [em13cr4] u

TYPE (Cluster|DG) : SID/PROCESS       STATUS      HOME      [2020-10-25 19:47:47]
---------------------------------------------------------------------------------
Dummy em13cr3      : em13cr4           n/a         /u01/app/oracle/product/em13cr4
Dummy emagent      : emagent           n/a         /u01/app/oracle/product/agent13c/agent_13.4.0.0.0
Dummy emagent      : emagent_a         n/a         /u01/app/oracle/product/agent13c/agent_inst
Dummy grinf        : grinf19           n/a         /u01/app/19.0.0/grid
Dummy rdbms_ee     : rdbms19           n/a         /u01/app/oracle/product/19.0.0/dbhome_1

ASM-instance       : +ASM              up          /u01/app/19.0.0/grid
DB-instance (N|N)  : EMREPO            open        /u01/app/oracle/product/19.0.0/dbhome_1

Listener           : LISTENER          up          /u01/app/19.0.0/grid

Ohs 12c            :                   up          /u01/app/oracle/product/em13cr4/ohs
Agent              : emagent_a         up          /u01/app/oracle/product/agent13c/agent_inst

WLS admin          : EMGC_ADMINSERVER  up          /u01/app/oracle/product/em13cr4/wlserver
WLS mgmt           : EMGC_OMS1         up          /u01/app/oracle/product/em13cr4/wlserver
WLS node           : 1                 up          /u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/nodemanager

Oracle Restart
Process            : ocssd             up          /u01/app/19.0.0/grid
Process            : ohasd             up          /u01/app/19.0.0/grid

oracle@mohnweg:/u01/app/19.0.0/grid/OPatch/ [em13cr4] u

TYPE (Cluster|DG) : SID/PROCESS STATUS HOME [2020-10-25 19:47:47]

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

Dummy em13cr3 : em13cr4 n/a /u01/app/oracle/product/em13cr4

Dummy emagent : emagent n/a /u01/app/oracle/product/agent13c/agent_13.4.0.0.0

Dummy emagent : emagent_a n/a /u01/app/oracle/product/agent13c/agent_inst

Dummy grinf : grinf19 n/a /u01/app/19.0.0/grid

Dummy rdbms_ee : rdbms19 n/a /u01/app/oracle/product/19.0.0/dbhome_1

ASM-instance : +ASM up /u01/app/19.0.0/grid

DB-instance (N|N) : EMREPO open /u01/app/oracle/product/19.0.0/dbhome_1

Listener : LISTENER up /u01/app/19.0.0/grid

Ohs 12c : up /u01/app/oracle/product/em13cr4/ohs

Agent : emagent_a up /u01/app/oracle/product/agent13c/agent_inst

WLS admin : EMGC_ADMINSERVER up /u01/app/oracle/product/em13cr4/wlserver

WLS mgmt : EMGC_OMS1 up /u01/app/oracle/product/em13cr4/wlserver

WLS node : 1 up /u01/app/oracle/product/gc_inst/user_projects/domains/GCDomain/nodemanager

Oracle Restart

Process : ocssd up /u01/app/19.0.0/grid

Process : ohasd up /u01/app/19.0.0/grid

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:

oracle@mohnweg:/u01/app/oracle/stage/31720429/ [rdbms19] ll
total 32
drwxrwxrwx. 4 oracle oinstall    80 Oct 25 19:51 .
drwxr-xr-x. 3 oracle oinstall    63 Oct 25 19:51 ..
drwxr-xr-x. 4 oracle oinstall    67 Oct 25 19:51 31668882
drwxr-x---. 5 oracle oinstall    56 Oct 25 19:51 31750108
-rw-rw-r--. 1 oracle oinstall  4891 Oct 20 11:24 PatchSearch.xml
-rw-r--r--. 1 oracle oinstall 22562 Oct 20 17:55 README.html

oracle@mohnweg:/u01/app/oracle/stage/31720429/ [rdbms19] ll

total 32

drwxrwxrwx. 4 oracle oinstall 80 Oct 25 19:51 .

drwxr-xr-x. 3 oracle oinstall 63 Oct 25 19:51 ..

drwxr-xr-x. 4 oracle oinstall 67 Oct 25 19:51 31668882

drwxr-x---. 5 oracle oinstall 56 Oct 25 19:51 31750108

-rw-rw-r--. 1 oracle oinstall 4891 Oct 20 11:24 PatchSearch.xml

-rw-r--r--. 1 oracle oinstall 22562 Oct 20 17:55 README.html

OPatch

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

oracle@mohnweg:/u01/app/19.0.0/grid/OPatch/ [+ASM] ./opatch version
OPatch Version: 12.2.0.1.21

OPatch succeeded.

oracle@mohnweg:/u01/app/19.0.0/grid/OPatch/ [+ASM] ./opatch version

OPatch Version: 12.2.0.1.21

OPatch succeeded.

Version Verification

+ASM – Grid Infrastructure Standalone

oracle@mohnweg:~/ [+ASM] asmcmd -V
asmcmd version 19.8.0.0.0

ASMCMD> showversion
ASM version         : 19.8.0.0.0

oracle@mohnweg:~/ [+ASM] asmcmd -V

asmcmd version 19.8.0.0.0

ASMCMD> showversion

ASM version : 19.8.0.0.0

EMREPO – RDBMS

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 25 19:44:39 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select PATCH_ID,PATCH_TYPE,SOURCE_VERSION,TARGET_VERSION,STATUS from dba_registry_sqlpatch;

  PATCH_ID PATCH_TYPE SOURCE_VERSION  TARGET_VERSION  STATUS
---------- ---------- --------------- --------------- -------------------------
  31219897 INTERIM    19.1.0.0.0      19.1.0.0.0      SUCCESS
  31281355 RU         19.1.0.0.0      19.8.0.0.0      SUCCESS

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 25 19:44:39 2020

Version 19.8.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> select PATCH_ID,PATCH_TYPE,SOURCE_VERSION,TARGET_VERSION,STATUS from dba_registry_sqlpatch;

PATCH_ID PATCH_TYPE SOURCE_VERSION TARGET_VERSION STATUS

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

31219897 INTERIM 19.1.0.0.0 19.1.0.0.0 SUCCESS

31281355 RU 19.1.0.0.0 19.8.0.0.0 SUCCESS

CheckConflictAgainstOHWithDetail

+ASM – Grid Infrastructure Standalone

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31771877
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31772784
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31773437
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31780966

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31771877

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31772784

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31773437

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31780966

EMREPO – RDBMS

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31771877
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31772784

1 2	$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31771877 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108/31772784

Take care, this line here produces an error:

oracle@mohnweg:~/ [+ASM] $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/19.0.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/19.0.0/grid/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.2.0.7.0
Log file location : /u01/app/19.0.0/grid/cfgtoollogs/opatch/opatch2020-10-25_20-00-57PM_1.log

This command doesn't support System Patch.

OPatch failed with error code 21

oracle@mohnweg:~/ [+ASM] $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/app/oracle/stage/31720429/31750108

Oracle Interim Patch Installer version 12.2.0.1.21

PREREQ session

Oracle Home : /u01/app/19.0.0/grid

Central Inventory : /u01/app/oraInventory

from : /u01/app/19.0.0/grid/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.2.0.7.0

Log file location : /u01/app/19.0.0/grid/cfgtoollogs/opatch/opatch2020-10-25_20-00-57PM_1.log

This command doesn't support System Patch.

OPatch failed with error code 21

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

$ cat /tmp/patch_list_gihome.txt

/u01/app/oracle/stage/31720429/31750108/31771877
/u01/app/oracle/stage/31720429/31750108/31772784
/u01/app/oracle/stage/31720429/31750108/31773437
/u01/app/oracle/stage/31720429/31750108/31780966

$ cat /tmp/patch_list_gihome.txt

/u01/app/oracle/stage/31720429/31750108/31771877

/u01/app/oracle/stage/31720429/31750108/31772784

/u01/app/oracle/stage/31720429/31750108/31773437

/u01/app/oracle/stage/31720429/31750108/31780966

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt

1	$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt

EMREPO – RDBMS

$ cat /tmp/patch_list_dbhome.txt
/u01/app/oracle/stage/31720429/31750108/31771877
/u01/app/oracle/stage/31720429/31750108/31772784

$ cat /tmp/patch_list_dbhome.txt

/u01/app/oracle/stage/31720429/31750108/31771877

/u01/app/oracle/stage/31720429/31750108/31772784

$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt

1	$ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt

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.

[root@mohnweg ~]# /u01/app/19.0.0/grid/OPatch/opatchauto apply /u01/app/oracle/stage/31720429/31750108

OPatchauto session is initiated at Sun Oct 25 20:21:31 2020

System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-10-25_08-21-34PM.log.

Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2020-10-25_08-21-39PM.log
The id for this session is TERC

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1
Patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Verifying SQL patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1
SQL patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid
Patch applicability verified successfully on home /u01/app/19.0.0/grid

Preparing to bring down database service on home /u01/app/oracle/product/19.0.0/dbhome_1
Successfully prepared home /u01/app/oracle/product/19.0.0/dbhome_1 to bring down database service

Bringing down database service on home /u01/app/oracle/product/19.0.0/dbhome_1
Following database has been stopped and will be restarted later during the session: emrepo
Database service successfully brought down on home /u01/app/oracle/product/19.0.0/dbhome_1

Bringing down CRS service on home /u01/app/19.0.0/grid
Prepatch operation log file location: /u01/app/oracle/crsdata/mohnweg/crsconfig/hapatch_2020-10-25_08-24-02PM.log
CRS service brought down successfully on home /u01/app/19.0.0/grid

Start applying binary patch on home /u01/app/oracle/product/19.0.0/dbhome_1
Binary patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Start applying binary patch on home /u01/app/19.0.0/grid

Binary patch applied successfully on home /u01/app/19.0.0/grid

Starting CRS service on home /u01/app/19.0.0/grid
Postpatch operation log file location: /u01/app/oracle/crsdata/mohnweg/crsconfig/hapatch_2020-10-25_08-37-45PM.log
CRS service started successfully on home /u01/app/19.0.0/grid

Starting database service on home /u01/app/oracle/product/19.0.0/dbhome_1
Database service successfully started on home /u01/app/oracle/product/19.0.0/dbhome_1

Preparing home /u01/app/oracle/product/19.0.0/dbhome_1 after database service restarted
No step execution required.........

Trying to apply SQL patch on home /u01/app/oracle/product/19.0.0/dbhome_1
SQL patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:mohnweg
SIDB Home:/u01/app/oracle/product/19.0.0/dbhome_1
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /u01/app/oracle/stage/31720429/31750108/31773437
Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /u01/app/oracle/stage/31720429/31750108/31780966
Reason: This patch is not applicable to this specified target type - "oracle_database"


==Following patches were SUCCESSFULLY applied:

Patch: /u01/app/oracle/stage/31720429/31750108/31771877
Log: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-24-30PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31772784
Log: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-24-30PM_1.log


Host:mohnweg
SIHA Home:/u01/app/19.0.0/grid
Version:19.0.0.0.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u01/app/oracle/stage/31720429/31750108/31771877
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31772784
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31773437
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31780966
Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

OPatchauto session completed at Sun Oct 25 20:41:38 2020
Time taken to complete the session 20 minutes, 7 seconds
[root@mohnweg ~]#

100

[root@mohnweg ~]# /u01/app/19.0.0/grid/OPatch/opatchauto apply /u01/app/oracle/stage/31720429/31750108

OPatchauto session is initiated at Sun Oct 25 20:21:31 2020

System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2020-10-25_08-21-34PM.log.

Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2020-10-25_08-21-39PM.log

The id for this session is TERC

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1

Patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Verifying SQL patch applicability on home /u01/app/oracle/product/19.0.0/dbhome_1

SQL patch applicability verified successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid

Patch applicability verified successfully on home /u01/app/19.0.0/grid

Preparing to bring down database service on home /u01/app/oracle/product/19.0.0/dbhome_1

Successfully prepared home /u01/app/oracle/product/19.0.0/dbhome_1 to bring down database service

Bringing down database service on home /u01/app/oracle/product/19.0.0/dbhome_1

Following database has been stopped and will be restarted later during the session: emrepo

Database service successfully brought down on home /u01/app/oracle/product/19.0.0/dbhome_1

Bringing down CRS service on home /u01/app/19.0.0/grid

Prepatch operation log file location: /u01/app/oracle/crsdata/mohnweg/crsconfig/hapatch_2020-10-25_08-24-02PM.log

CRS service brought down successfully on home /u01/app/19.0.0/grid

Start applying binary patch on home /u01/app/oracle/product/19.0.0/dbhome_1

Binary patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

Start applying binary patch on home /u01/app/19.0.0/grid

Binary patch applied successfully on home /u01/app/19.0.0/grid

Starting CRS service on home /u01/app/19.0.0/grid

Postpatch operation log file location: /u01/app/oracle/crsdata/mohnweg/crsconfig/hapatch_2020-10-25_08-37-45PM.log

CRS service started successfully on home /u01/app/19.0.0/grid

Starting database service on home /u01/app/oracle/product/19.0.0/dbhome_1

Database service successfully started on home /u01/app/oracle/product/19.0.0/dbhome_1

Preparing home /u01/app/oracle/product/19.0.0/dbhome_1 after database service restarted

No step execution required.........

Trying to apply SQL patch on home /u01/app/oracle/product/19.0.0/dbhome_1

SQL patch applied successfully on home /u01/app/oracle/product/19.0.0/dbhome_1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:mohnweg

SIDB Home:/u01/app/oracle/product/19.0.0/dbhome_1

Version:19.0.0.0.0

Summary:

==Following patches were SKIPPED:

Patch: /u01/app/oracle/stage/31720429/31750108/31773437

Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /u01/app/oracle/stage/31720429/31750108/31780966

Reason: This patch is not applicable to this specified target type - "oracle_database"

==Following patches were SUCCESSFULLY applied:

Patch: /u01/app/oracle/stage/31720429/31750108/31771877

Log: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-24-30PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31772784

Log: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-24-30PM_1.log

Host:mohnweg

SIHA Home:/u01/app/19.0.0/grid

Version:19.0.0.0.0

Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /u01/app/oracle/stage/31720429/31750108/31771877

Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31772784

Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31773437

Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

Patch: /u01/app/oracle/stage/31720429/31750108/31780966

Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-10-25_20-29-10PM_1.log

OPatchauto session completed at Sun Oct 25 20:41:38 2020

Time taken to complete the session 20 minutes, 7 seconds

[root@mohnweg ~]#

Version Verification

+ASM – Grid Infrastructure Standalone

oracle@mohnweg:~/ [+ASM] asmcmd -V
asmcmd version 19.9.0.0.0

ASMCMD> showversion
ASM version         : 19.9.0.0.0

oracle@mohnweg:~/ [+ASM] asmcmd -V

asmcmd version 19.9.0.0.0

ASMCMD> showversion

ASM version : 19.9.0.0.0

EMREPO – RDBMS

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 25 19:44:39 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select PATCH_ID,PATCH_TYPE,SOURCE_VERSION,TARGET_VERSION,STATUS from dba_registry_sqlpatch;

  PATCH_ID PATCH_TYPE SOURCE_VERSION  TARGET_VERSION  STATUS
---------- ---------- --------------- --------------- -------------------------
  31219897 INTERIM    19.1.0.0.0      19.1.0.0.0      SUCCESS
  31281355 RU         19.1.0.0.0      19.8.0.0.0      SUCCESS
  31771877 RU         19.8.0.0.0      19.9.0.0.0      SUCCESS

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 25 19:44:39 2020

Version 19.8.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> select PATCH_ID,PATCH_TYPE,SOURCE_VERSION,TARGET_VERSION,STATUS from dba_registry_sqlpatch;

PATCH_ID PATCH_TYPE SOURCE_VERSION TARGET_VERSION STATUS

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

31219897 INTERIM 19.1.0.0.0 19.1.0.0.0 SUCCESS

31281355 RU 19.1.0.0.0 19.8.0.0.0 SUCCESS

31771877 RU 19.8.0.0.0 19.9.0.0.0 SUCCESS

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

oracle@mohnweg:~/ [EMREPO] srvctl stop database -db emrepo

1	oracle@mohnweg:~/ [EMREPO] srvctl stop database -db emrepo

Change to OJVM Patch Directory

oracle@mohnweg:~/ [EMREPO] cd /u01/app/oracle/stage/31720429/31668882/

1	oracle@mohnweg:~/ [EMREPO] cd /u01/app/oracle/stage/31720429/31668882/

Apply the OJVM Patch

oracle@mohnweg:/u01/app/oracle/stage/31720429/31668882/ [EMREPO] $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.21
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-26_12-48-13PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   31668882

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '31668882' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...
Patch 31668882 successfully applied.
Sub-set patch [31219897] has become inactive due to the application of a super-set patch [31668882].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-26_12-48-13PM_1.log

OPatch succeeded.

oracle@mohnweg:/u01/app/oracle/stage/31720429/31668882/ [EMREPO] $ORACLE_HOME/OPatch/opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1

Central Inventory : /u01/app/oraInventory

from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc

OPatch version : 12.2.0.1.21

OUI version : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-26_12-48-13PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 31668882

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '31668882' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.javavm.server.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 31668882 successfully applied.

Sub-set patch [31219897] has become inactive due to the application of a super-set patch [31668882].

Please refer to Doc ID 2161861.1 for any possible further required actions.

Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-10-26_12-48-13PM_1.log

OPatch succeeded.

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

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 26 12:51:48 2020
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 5049940880 bytes
Fixed Size                  9145232 bytes
Variable Size            2248146944 bytes
Database Buffers         2785017856 bytes
Redo Buffers                7630848 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 26 12:51:48 2020

Version 19.9.0.0.0

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 5049940880 bytes

Fixed Size 9145232 bytes

Variable Size 2248146944 bytes

Database Buffers 2785017856 bytes

Redo Buffers 7630848 bytes

Database mounted.

Database opened.

SQL> alter pluggable database all open upgrade;

Pluggable database altered.

Run datapatch

oracle@mohnweg:~/ [EMREPO] $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 19.9.0.0.0 Production on Mon Oct 26 12:53:30 2020
Copyright (c) 2012, 2020, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2268_2020_10_26_12_53_30/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 31219897 (OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)):
  Binary registry: Not installed
  PDB CDB$ROOT: Applied successfully on 13-SEP-20 12.47.47.620859 PM
  PDB PDB$SEED: Applied successfully on 13-SEP-20 12.47.50.075046 PM
  PDB PDBEMREPO01: Applied successfully on 13-SEP-20 12.47.50.075046 PM
Interim patch 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):
  Binary registry: Installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
  PDB PDBEMREPO01: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.9.0.0.0 Release_Update 200930183249: Installed
  PDB CDB$ROOT:
    Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 25-OCT-20 08.41.35.152797 PM
  PDB PDB$SEED:
    Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 25-OCT-20 08.41.35.369001 PM
  PDB PDBEMREPO01:
    Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 25-OCT-20 08.41.35.578682 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED PDBEMREPO01
    The following interim patches will be rolled back:
      31219897 (OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897))
    No release update patches need to be installed
    The following interim patches will be applied:
      31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882))

Installing patches...
Patch installation complete.  Total patches installed: 6

Validating logfiles...done
Patch 31219897 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219897/23619699/31219897_rollback_EMREPO_CDBROOT_2020Oct26_12_54_11.log (no errors)
Patch 31668882 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31668882/23790068/31668882_apply_EMREPO_CDBROOT_2020Oct26_12_54_11.log (no errors)
Patch 31219897 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219897/23619699/31219897_rollback_EMREPO_PDBSEED_2020Oct26_12_54_13.log (no errors)
Patch 31668882 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31668882/23790068/31668882_apply_EMREPO_PDBSEED_2020Oct26_12_54_13.log (no errors)
Patch 31219897 rollback (pdb PDBEMREPO01): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219897/23619699/31219897_rollback_EMREPO_PDBEMREPO01_2020Oct26_12_54_13.log (no errors)
Patch 31668882 apply (pdb PDBEMREPO01): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31668882/23790068/31668882_apply_EMREPO_PDBEMREPO01_2020Oct26_12_54_13.log (no errors)
SQL Patching tool complete on Mon Oct 26 12:54:16 2020

oracle@mohnweg:~/ [EMREPO] $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 19.9.0.0.0 Production on Mon Oct 26 12:53:30 2020

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2268_2020_10_26_12_53_30/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Note: Datapatch will only apply or rollback SQL fixes for PDBs

that are in an open state, no patches will be applied to closed PDBs.

Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation

(Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:

Interim patch 31219897 (OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)):

Binary registry: Not installed

PDB CDB$ROOT: Applied successfully on 13-SEP-20 12.47.47.620859 PM

PDB PDB$SEED: Applied successfully on 13-SEP-20 12.47.50.075046 PM

PDB PDBEMREPO01: Applied successfully on 13-SEP-20 12.47.50.075046 PM

Interim patch 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):

Binary registry: Installed

PDB CDB$ROOT: Not installed

PDB PDB$SEED: Not installed

PDB PDBEMREPO01: Not installed

Current state of release update SQL patches:

Binary registry:

19.9.0.0.0 Release_Update 200930183249: Installed

PDB CDB$ROOT:

Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 25-OCT-20 08.41.35.152797 PM

PDB PDB$SEED:

Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 25-OCT-20 08.41.35.369001 PM

PDB PDBEMREPO01:

Applied 19.9.0.0.0 Release_Update 200930183249 successfully on 25-OCT-20 08.41.35.578682 PM

Adding patches to installation queue and performing prereq checks...done

Installation queue:

For the following PDBs: CDB$ROOT PDB$SEED PDBEMREPO01

The following interim patches will be rolled back:

31219897 (OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897))

No release update patches need to be installed

The following interim patches will be applied:

31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882))

Installing patches...

Patch installation complete. Total patches installed: 6

Validating logfiles...done

Patch 31219897 rollback (pdb CDB$ROOT): SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219897/23619699/31219897_rollback_EMREPO_CDBROOT_2020Oct26_12_54_11.log (no errors)

Patch 31668882 apply (pdb CDB$ROOT): SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31668882/23790068/31668882_apply_EMREPO_CDBROOT_2020Oct26_12_54_11.log (no errors)

Patch 31219897 rollback (pdb PDB$SEED): SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219897/23619699/31219897_rollback_EMREPO_PDBSEED_2020Oct26_12_54_13.log (no errors)

Patch 31668882 apply (pdb PDB$SEED): SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31668882/23790068/31668882_apply_EMREPO_PDBSEED_2020Oct26_12_54_13.log (no errors)

Patch 31219897 rollback (pdb PDBEMREPO01): SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31219897/23619699/31219897_rollback_EMREPO_PDBEMREPO01_2020Oct26_12_54_13.log (no errors)

Patch 31668882 apply (pdb PDBEMREPO01): SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31668882/23790068/31668882_apply_EMREPO_PDBEMREPO01_2020Oct26_12_54_13.log (no errors)

SQL Patching tool complete on Mon Oct 26 12:54:16 2020

Shutdown Database in SQL*Plus

SQL> shutdown immediate

1	SQL> shutdown immediate

Start the RDBMS with srvctl

oracle@mohnweg:~/ [EMREPO] srvctl start database -db emrepo

1	oracle@mohnweg:~/ [EMREPO] srvctl start database -db emrepo

Version Verification

EMREPO – RDBMS

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 25 19:44:39 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select PATCH_ID,PATCH_TYPE,SOURCE_VERSION,TARGET_VERSION,STATUS,ACTION from dba_registry_sqlpatch;

  PATCH_ID PATCH_TYPE SOURCE_VERSION  TARGET_VERSION  STATUS                    ACTION
---------- ---------- --------------- --------------- ------------------------- ---------------
  31219897 INTERIM    19.1.0.0.0      19.1.0.0.0      SUCCESS                   APPLY
  31281355 RU         19.1.0.0.0      19.8.0.0.0      SUCCESS                   APPLY
  31771877 RU         19.8.0.0.0      19.9.0.0.0      SUCCESS                   APPLY
  31219897 INTERIM    19.9.0.0.0      19.9.0.0.0      SUCCESS                   ROLLBACK
  31668882 INTERIM    19.9.0.0.0      19.9.0.0.0      SUCCESS                   APPLY

oracle@mohnweg:~/ [EMREPO] sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 25 19:44:39 2020

Version 19.8.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> select PATCH_ID,PATCH_TYPE,SOURCE_VERSION,TARGET_VERSION,STATUS,ACTION from dba_registry_sqlpatch;

PATCH_ID PATCH_TYPE SOURCE_VERSION TARGET_VERSION STATUS ACTION

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

31219897 INTERIM 19.1.0.0.0 19.1.0.0.0 SUCCESS APPLY

31281355 RU 19.1.0.0.0 19.8.0.0.0 SUCCESS APPLY

31771877 RU 19.8.0.0.0 19.9.0.0.0 SUCCESS APPLY

31219897 INTERIM 19.9.0.0.0 19.9.0.0.0 SUCCESS ROLLBACK

31668882 INTERIM 19.9.0.0.0 19.9.0.0.0 SUCCESS APPLY

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

The Grafana Plugins for Oracle Cloud Infrastructure Monitoring are back!

Author: Martin Berger 7. October 2020

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

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

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

Install and configure the OCI CLI

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

OS User oci and Installer

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

# groupadd oci
# useradd oci -g oci
# su - oci

# groupadd oci

# useradd oci -g oci

# su - oci

Run the installer script.

$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

1	$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

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

-- Installation successful.
-- Run the CLI with /home/oci/bin/oci --help

1 2	-- Installation successful. -- Run the CLI with /home/oci/bin/oci --help

Configure the OCI CLI

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

Required values to finish the setup:

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

Run the setup.

$ /home/oci/bin/oci setup config

1	$ /home/oci/bin/oci setup config

OCI Console API Key

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

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

OCI CLI Configuration Test

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

$ export var_compartment_id= [YOUR_COMPARTMENT_OCID]
$ oci compute image list --compartment-id $var_compartment_id --query "data[?\"operating-system\"=='Oracle Linux']".{"name:\"display-name\",os:\"operating-system\""} --output table

+----------------------------------------+--------------+
| name | os |
+----------------------------------------+--------------+
| Oracle-Linux-8.2-2020.09.23-0          | Oracle Linux |
| Oracle-Linux-8.2-2020.08.27-0          | Oracle Linux |
| Oracle-Linux-8.2-2020.07.28-0          | Oracle Linux |
| Oracle-Linux-7.8-Gen2-GPU-2020.09.23-0 | Oracle Linux |
| Oracle-Linux-7.8-Gen2-GPU-2020.08.27-0 | Oracle Linux |
| Oracle-Linux-7.8-Gen2-GPU-2020.07.28-0 | Oracle Linux |
| Oracle-Linux-7.8-2020.09.23-0          | Oracle Linux |
| Oracle-Linux-7.8-2020.08.26-0          | Oracle Linux |
| Oracle-Linux-7.8-2020.07.28-0          | Oracle Linux |
| Oracle-Linux-7.8-2020.05.26-0          | Oracle Linux |
| Oracle-Linux-6.10-2020.09.22-0         | Oracle Linux |
| Oracle-Linux-6.10-2020.09.03-0         | Oracle Linux |
| Oracle-Linux-6.10-2020.07.28-0         | Oracle Linux |
+----------------------------------------+--------------+

$ export var_compartment_id= [YOUR_COMPARTMENT_OCID]

$ oci compute image list --compartment-id $var_compartment_id --query "data[?\"operating-system\"=='Oracle Linux']".{"name:\"display-name\",os:\"operating-system\""} --output table

+----------------------------------------+--------------+

| name | os |

+----------------------------------------+--------------+

| Oracle-Linux-8.2-2020.09.23-0 | Oracle Linux |

| Oracle-Linux-8.2-2020.08.27-0 | Oracle Linux |

| Oracle-Linux-8.2-2020.07.28-0 | Oracle Linux |

| Oracle-Linux-7.8-Gen2-GPU-2020.09.23-0 | Oracle Linux |

| Oracle-Linux-7.8-Gen2-GPU-2020.08.27-0 | Oracle Linux |

| Oracle-Linux-7.8-Gen2-GPU-2020.07.28-0 | Oracle Linux |

| Oracle-Linux-7.8-2020.09.23-0 | Oracle Linux |

| Oracle-Linux-7.8-2020.08.26-0 | Oracle Linux |

| Oracle-Linux-7.8-2020.07.28-0 | Oracle Linux |

| Oracle-Linux-7.8-2020.05.26-0 | Oracle Linux |

| Oracle-Linux-6.10-2020.09.22-0 | Oracle Linux |

| Oracle-Linux-6.10-2020.09.03-0 | Oracle Linux |

| Oracle-Linux-6.10-2020.07.28-0 | Oracle Linux |

+----------------------------------------+--------------+

OCI Console Group Policy

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

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

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

Grafana

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

# wget https://dl.grafana.com/oss/release/grafana-7.2.0-1.x86_64.rpm
# yum install grafana-7.2.0-1.x86_64.rpm

1 2	# wget https://dl.grafana.com/oss/release/grafana-7.2.0-1.x86_64.rpm # yum install grafana-7.2.0-1.x86_64.rpm

Start and enable the service.

# systemctl daemon-reload
# systemctl enable grafana-server.service
# systemctl start grafana-server.service

# systemctl daemon-reload

# systemctl enable grafana-server.service

# systemctl start grafana-server.service

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

# firewall-cmd --permanent --zone=public --add-port=3000/tcp
# firewall-cmd --reload

1 2	# firewall-cmd --permanent --zone=public --add-port=3000/tcp # firewall-cmd --reload

Oracle Cloud Infrastructure Metrics Data Source Plugin

List the available OCI Grafana plugins.

# grafana-cli plugins list-remote | grep oci
id: oci-logs-datasource version: 1.1.0
id: oci-metrics-datasource version: 2.0.0

# grafana-cli plugins list-remote | grep oci

id: oci-logs-datasource version: 1.1.0

id: oci-metrics-datasource version: 2.0.0

Install the metric plugin.

# grafana-cli plugins install oci-metrics-datasource
installing oci-metrics-datasource @ 2.0.0
from: https://grafana.com/api/plugins/oci-metrics-datasource/versions/2.0.0/download
into: /var/lib/grafana/plugins

✔ Installed oci-metrics-datasource successfully

Restart grafana after installing plugins . <service grafana-server restart>

# grafana-cli plugins install oci-metrics-datasource

installing oci-metrics-datasource @ 2.0.0

from: https://grafana.com/api/plugins/oci-metrics-datasource/versions/2.0.0/download

into: /var/lib/grafana/plugins

✔ Installed oci-metrics-datasource successfully

Restart grafana after installing plugins . <service grafana-server restart>

Restart Grafana Server.

# service grafana-server restart
Restarting grafana-server (via systemctl): [ OK ]

1 2	# service grafana-server restart Restarting grafana-server (via systemctl): [ OK ]

Grafana Data Source Configuration

RSA Key Configuration

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

# cp -r /home/oci/.oci /usr/share/grafana
# chown -R grafana:grafana /usr/share/grafana/.oci

1 2	# cp -r /home/oci/.oci /usr/share/grafana # chown -R grafana:grafana /usr/share/grafana/.oci

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

from:

key_file=/home/oci/.oci/oci_api_key_07102020.pem

1	key_file=/home/oci/.oci/oci_api_key_07102020.pem

to:

key_file=/usr/share/grafana/.oci/oci_api_key_07102020.pem

1	key_file=/usr/share/grafana/.oci/oci_api_key_07102020.pem

Add a new Data Source

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

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

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

Create a new Dashboard and add a new panel.

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

Example

Summary

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

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

Autonomous Database, Oracle ACE, Oracle Cloud, Oracle Cloud Infrastructure, Oracle Linux, TrivadisContent

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

Author: Martin Berger 28. September 2020

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

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

Architecture

Click on the image for a larger view.

Prerequisites

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

Oracle Cloud Infrastructure IPSec Setup

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

Unifi Security Gateway Setup

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

Oracle Cloud Infrastructure Settings

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

Network Configuration

Oracle Cloud Infrastructure IPSec Status Update

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

Unifi Security Gateway Routing

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

Routing Settings

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

Connection Verification

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

A quick Bandwith Test

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

Troubleshooting in USG

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

Show the current VPN configuration

$ sudo swanctl --list-conns
peer-140.238.123.456-tunnel-vti:
local: 139.178.78.910
remote: 140.238.123.456
local pre-shared key authentication:
id: 139.178.78.910
remote pre-shared key authentication:
id: 140.238.123.456
peer-140.238.123.456-tunnel-vti: TUNNEL
local: 0.0.0.0/0
remote: 0.0.0.0/0
remote-access: IKEv1
local: 139.178.78.910
remote: %any
local pre-shared key authentication:
id: 139.178.78.910
remote pre-shared key authentication:
remote-access: TRANSPORT
local: dynamic[udp/l2f]

$ sudo swanctl --list-conns

peer-140.238.123.456-tunnel-vti:

local: 139.178.78.910

remote: 140.238.123.456

local pre-shared key authentication:

id: 139.178.78.910

remote pre-shared key authentication:

id: 140.238.123.456

peer-140.238.123.456-tunnel-vti: TUNNEL

local: 0.0.0.0/0

remote: 0.0.0.0/0

remote-access: IKEv1

local: 139.178.78.910

remote: %any

local pre-shared key authentication:

id: 139.178.78.910

remote pre-shared key authentication:

remote-access: TRANSPORT

local: dynamic[udp/l2f]

Follow the Logfile

$ sudo swanctl --log
08[NET] received packet: from 152.67.12.34[500] to 139.178.78.910[500] (92 bytes)
08[ENC] parsed INFORMATIONAL_V1 request 1450492415 [ HASH N(DPD) ]
08[ENC] generating INFORMATIONAL_V1 request 3141172786 [ HASH N(DPD_ACK) ]
08[NET] sending packet: from 139.178.78.910[500] to 152.67.12.34[500] (92 bytes)
14[NET] received packet: from 152.67.12.34[500] to 139.178.78.910[500] (92 bytes)
14[ENC] parsed INFORMATIONAL_V1 request 2294336399 [ HASH N(DPD) ]
14[ENC] generating INFORMATIONAL_V1 request 1226471776 [ HASH N(DPD_ACK) ]
14[NET] sending packet: from 139.178.78.910[500] to 152.67.12.34[500] (92 bytes)

$ sudo swanctl --log

08[NET] received packet: from 152.67.12.34[500] to 139.178.78.910[500] (92 bytes)

08[ENC] parsed INFORMATIONAL_V1 request 1450492415 [ HASH N(DPD) ]

08[ENC] generating INFORMATIONAL_V1 request 3141172786 [ HASH N(DPD_ACK) ]

08[NET] sending packet: from 139.178.78.910[500] to 152.67.12.34[500] (92 bytes)

14[NET] received packet: from 152.67.12.34[500] to 139.178.78.910[500] (92 bytes)

14[ENC] parsed INFORMATIONAL_V1 request 2294336399 [ HASH N(DPD) ]

14[ENC] generating INFORMATIONAL_V1 request 1226471776 [ HASH N(DPD_ACK) ]

14[NET] sending packet: from 139.178.78.910[500] to 152.67.12.34[500] (92 bytes)

Troubleshooting in Oracle Cloud Infrastructure

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

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

Summary

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

#freedom #network #together #doer #curiosity

DBaaS, Oracle Cloud Infrastructure, Oracle Linux, Oracle Tools, Security, TrivadisContent