MV2ADB – One-Click Move of your Data into OCI Autonomous Databases – Step by Step

There is a new Oracle Cloud Infrastructure tool available called MV2ADB(ADB) MV2ADB: move data to Autonomous Database in “one-click” (Doc ID 2463574.1). All steps which have to be executed manually to transfer data into an Autonomous Database are now automated:

  • Advisor for local schemas
  • Oracle Data Pump local export
  • Transfer into Oracle Cloud Infrastructure Object Store
  • Create Autonomous Database Credentials to get access on the Object Store
  • Oracle Data Pump local import
  • Verify Oracle Data Pump import logfile

The data transfer job can be done fully automated or step by step (autonomus schema advisor, export data, create bucket, upload dump files etc.). In this blog post I describe the manual steps.

How it works

Image from My Oracle Support Note 2463574.1:

 

 

 

 

 

 

 

 

 

 

 

Prerequisites

  • mv2adb rpm package installed, always download the newest version from My Oracle Support (Doc ID 2463574.1)
  • HTTP/SQL*Net Connectivity from the on premises server to the Autonomous Database
  • Autonomous Database Wallet (can be downloaded from the ATP main page)
  • Instant Client with Basic Package, SQL*Plus Package and Data Pump, SQL*Loader and Workload Replay Client – if there is an existing RDBMS installation 18.3 or higher, you can use it
  • Java executable – same like above, you can use the RDBMS installation too
  • Perl Release 5.10 or above
  • Optional: Oracle OCI Command Line Interface – https://docs.cloud.oracle.com/iaas/Content/API/Concepts/cliconcepts.htm installed and configured

mv2adb – Options

[root@mohnweg mv2adb]# ./mv2adb

--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

Command option is missing!
One of following operation are possible:
- Auto operation:
auto
- Export datapump Operation:
expdp
- Import datapump Operation:
impdp
- OCI Storage Bucket Operations:
createbucket
delbucket
listbucket
- OCI Storage Object Operations:
deldump
getdump
listdump
putdump
- Database Schema operation:
advisor
report
- Encrypt password Operations:
encpass

Configuration File

The mv2adb install process provides an example of a configuration file – here is my version with OCI CLI enabled. Take care and read the example and the comments. At this point, thanks to Ruggero Citton from Oracle’s Cloud Innovation and Solution Engineering Team for his great support to find my configuration mistake. If you dont’ want to use the configuration file, all parameters can be attached to the mv2db command.

All passwords have to be encrypted with the mv2adb encpass command ind advance.

# ./mv2adb encpass

Please enter the password :
Please re-enter the password :

For the parameter OCI_PASSWORD, you have to create an OCI Authentification Token first in the console or by CLI and encrypt the provided password.

In this configuration file, I use the OCI CLI. In this example we transfer the Oracle demo schema HR. Take care about the Expdp/Impdp Parameters, if you want to encrypt the Data Pump export files, you need an additional Advanced Security Option ASO. No license? Just comment it out or let the parameters blank.

 

 

#---------------------------------------------------#
# DB Parameters #
#---------------------------------------------------#
DB_CONSTRING=//mohnweg/pdbhr01.kestenholz.net
SYSTEM_DB_PASSWORD={mv2adb encpass of SYSTEM password}
SCHEMAS=HR
REMAP=
DUMP_NAME=expdp.dmp
DUMP_PATH=/datapump
DUMP_FILES=/datapump/expdp_01.dmp,/datapump/expdp_02.dmp
OHOME=/u01/app/oracle/product/18.0.0/dbhome_1/
ICHOME=/u01/app/oracle/product/18.0.0/dbhome_1/
#------------------------------------------#
# Expdp/Impdp Parameters #
#------------------------------------------#
# ENC_PASSWORD={mv2adb encpass of export/import password}
# ENC_TYPE=AES256
#------------------------------------------#
# ADB Properties #
#------------------------------------------#
ADB_NAME=ATP01
ADB_PASSWORD={mv2adb encpass of ADB admin password}
ADB_CFILE=/opt/mv2adb/conf/Wallet_ATP01.zip
#------------------------------------------#
# Object Store Properties #
#------------------------------------------#
OCI_HOST=https://swiftobjectstorage.eu-zurich-1.oraclecloud.com
OCI_TENNANT={ oci namespace name e.g.  }
OCI_BUCKET=ocibucket01
OCI_ID=oci@mylogin.com
OCI_PASSWORD={mv2adb encpass of OCI user's Authentication Token password}
#
PROXY_HOST=
PROXY_PORT=
PROXY_ID=
PROXY_PASSWORD=
#------------------------------------------#
# OCI-Client Properties #
#------------------------------------------#
OCIC=true
OCIC_SIZE=10
OCIC_PARALLEL=10
OCIC_COMPART_ID=ocid1.compartment.oc1..aaaaaaaaue2ukzuqcgvkub4tg412345678abcdefghi
#---------------------------------------------------#
# End Of File #
#---------------------------------------------------#

Let’s go – we transfer the local HR Schema to ADB

18/12/2019: At the moment I have some trouble with the automated function which is doing all steps at one (option auto)  – this is under investigation.

0. Advisor

It executes the ADB Schema Advisor. This advisor provides information if your data can be transferred into the cloud and which database objects are problematic. If you want to know more, take a look at this My Oracle Support Note: Oracle Autonomous Database Schema Advisor (Doc ID 2462677.1) Excerpt from the output with the hint that user defined tablespaces are not allowed in an ADB environment (If you want to verify it: The manually executed CREATE TABLESPACE command results into ORA-01031: insufficient privileges).

In the background, a temporary user called ADB_ADVISOR is created to analyse the data (Script @/opt/mv2adb/utils/install_adb_advisor.sql). The user will be dropped automatically after the run.

[root@mohnweg mv2adb]# ./mv2adb advisor -conf conf/ATP01.mv2adb.cfg
INFO: 2019-12-18 08:30:41: Please check the logfile '/opt/mv2adb/out/log/mv2adb_9812.log' for more details


--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

INFO: 2019-12-18 08:30:41: Reading the configuration file 'conf/ATP01.mv2adb.cfg'
INFO: 2019-12-18 08:30:41: Due to missing ADB_TARGET parameter, online Cloud Service Type check is performed
INFO: 2019-12-18 08:30:41: Using Oracle Home '/u01/app/oracle/product/18.0.0/dbhome_1/'
INFO: 2019-12-18 08:30:41: Checking Cloud Service Type online
INFO: 2019-12-18 08:30:42: Getting advisor report for '//mohnweg/pdbhr01.kestenholz.net'
INFO: 2019-12-18 08:30:42: ...step1 - installing advisor package on '//mohnweg/pdbhr01.kestenholz.net'
INFO: 2019-12-18 08:30:43: ...step2 - getting advisor report
INFO: 2019-12-18 08:30:43: ...getting advisor report for schema 'HR', it may get some time...

0 ==========================================================================================
== ATP SCHEMA MIGRATION REPORT FOR HR
==========================================================================================

ADB Advisor Version : 19.3.0.0.1
Instance Name : TRNSFR18
Database Name : TRNSFR18
Host Name : mohnweg.kestenholz.net
Database Version : 18.0.0.0.0
Pluggable Database : PDBHR01
Schemas Analyzed : HR
Analyzing for : Autonomous Transaction Processing (Serverless)
Report Start date/time: 18-DEC-2019 08:30

...

------------------------------------------------------------------------------------------
-- SECTION 4: MIGRATION ADDITIONAL INFO
------------------------------------------------------------------------------------------

1) User defined tablespaces are not allowed in ATP-S and ADW-S (Serverless) (Count=1):
--------------------------------------------------------------------------------------
Note: Creation of tablespaces is disallowed in ATP and ADW (Serverless). The tablespace clause gets ignored
and all objects get created in 'DATA' tablespace. The following is the list of schemas and the tablespaces
currently in use.

HR HRDATA

1. Create an OCI Object Storage Bucket called ocibucket01

[root@mohnweg mv2adb]# ./mv2adb createbucket -conf conf/ATP01.mv2adb.cfg
INFO: 2019-12-18 08:10:06: Please check the logfile '/opt/mv2adb/out/log/mv2adb_8039.log' for more details


--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

INFO: 2019-12-18 08:10:06: Reading the configuration file 'conf/ATP01.mv2adb.cfg'
INFO: 2019-12-18 08:10:08: Creating bucket 'ocibucket01' using oci client
SUCCESS: 2019-12-18 08:10:09: Bucket 'ocibucket01' created successfully

2. Execute the local Oracle Data Pump Export

[root@mohnweg mv2adb]# ./mv2adb expdp -conf conf/ATP01.mv2adb.cfg
$INFO: 2019-12-18 08:11:49: Please check the logfile '/opt/mv2adb/out/log/mv2adb_8157.log' for more details


--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

INFO: 2019-12-18 08:11:49: Reading the configuration file 'conf/ATP01.mv2adb.cfg'
INFO: 2019-12-18 08:11:49: Using Oracle Home '/u01/app/oracle/product/18.0.0/dbhome_1/'
INFO: 2019-12-18 08:11:50: Getting ADB parallelism
INFO: 2019-12-18 08:11:51: Getting source DB version
INFO: 2019-12-18 08:11:51: Checking schemas on source DB
INFO: 2019-12-18 08:11:51: Creating expdp directory 'MV2ADB_EXPDP_DIR' for path '/datapump'
INFO: 2019-12-18 08:11:52: Getting latest SCN
INFO: 2019-12-18 08:11:52: Checking Cloud Service Type
INFO: 2019-12-18 08:11:53: Executing Expdp
INFO: 2019-12-18 08:12:53: Following expdp dump has been created:
DUMP_FILES=/datapump/expdp_01.dmp,/datapump/expdp_02.dmp,/datapump/expdp_03.dmp,/datapump/expdp_04.dmp,/datapump/expdp_05.dmp,/datapump/expdp_06.dmp
SUCCESS: 2019-12-18 08:12:53: Expdp executed successfully

3. Upload the Data Pump Export Files into the OCI Bucket

[root@mohnweg mv2adb]# ./mv2adb putdump -conf conf/ATP01.mv2adb.cfg
INFO: 2019-12-18 08:14:33: Please check the logfile '/opt/mv2adb/out/log/mv2adb_8507.log' for more details


--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

INFO: 2019-12-18 08:14:33: Reading the configuration file 'conf/ATP01.mv2adb.cfg'
INFO: 2019-12-18 08:14:34: Loading '/datapump/expdp_01.dmp' into bucket 'ocibucket01' using oci-client
SUCCESS: 2019-12-18 08:14:35: File '/datapump/expdp_01.dmp' uploaded on 'ocibucket01' successfully
INFO: 2019-12-18 08:14:35: Loading '/datapump/expdp_02.dmp' into bucket 'ocibucket01' using oci-client
SUCCESS: 2019-12-18 08:14:37: File '/datapump/expdp_02.dmp' uploaded on 'ocibucket01' successfully
INFO: 2019-12-18 08:14:37: Loading '/datapump/expdp_03.dmp' into bucket 'ocibucket01' using oci-client
SUCCESS: 2019-12-18 08:14:38: File '/datapump/expdp_03.dmp' uploaded on 'ocibucket01' successfully
INFO: 2019-12-18 08:14:38: Loading '/datapump/expdp_04.dmp' into bucket 'ocibucket01' using oci-client
SUCCESS: 2019-12-18 08:14:39: File '/datapump/expdp_04.dmp' uploaded on 'ocibucket01' successfully
INFO: 2019-12-18 08:14:39: Loading '/datapump/expdp_05.dmp' into bucket 'ocibucket01' using oci-client
SUCCESS: 2019-12-18 08:14:40: File '/datapump/expdp_05.dmp' uploaded on 'ocibucket01' successfully
INFO: 2019-12-18 08:14:40: Loading '/datapump/expdp_06.dmp' into bucket 'ocibucket01' using oci-client

4. Import the Data

[root@mohnweg mv2adb]# ./mv2adb impdp -conf conf/ATP01.mv2adb.cfg
INFO: 2019-12-18 08:18:18: Please check the logfile '/opt/mv2adb/out/log/mv2adb_8808.log' for more details


--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

INFO: 2019-12-18 08:18:18: Reading the configuration file 'conf/ATP01.mv2adb.cfg'
INFO: 2019-12-18 08:18:18: Getting ADB parallelism
INFO: 2019-12-18 08:18:19: Checking Cloud Service Type
INFO: 2019-12-18 08:18:20: Drop Object Store Credential
INFO: 2019-12-18 08:18:21: Creating Object Store Credential
INFO: 2019-12-18 08:18:22: Executing Impdp to ADB
INFO: 2019-12-18 08:19:58: Moving impdp log 'mv2adb_impdp_20191218-081822.log' to Object Store
SUCCESS: 2019-12-18 08:19:59: Impdp to ADB 'ATP01' executed successfully

5. Verification

[root@mohnweg mv2adb]# ./mv2adb report -conf conf/ATP01.mv2adb.cfg
INFO: 2019-12-18 08:21:35: Please check the logfile '/opt/mv2adb/out/log/mv2adb_9101.log' for more details


--------------------------------------------------------
mv2adb - Move data to Oracle Autonomous Database
Version: 2.0.1-75
Copyright (c) 1982-2019 Oracle and/or its affiliates.
--------------------------------------------------------
Author: Ruggero Citton <ruggero.citton@oracle.com>
RAC Pack, Cloud Innovation and Solution Engineering Team
--------------------------------------------------------

INFO: 2019-12-18 08:21:35: Reading the configuration file 'conf/ATP01.mv2adb.cfg'
INFO: 2019-12-18 08:21:35: Using Oracle Home '/u01/app/oracle/product/18.0.0/dbhome_1/'
INFO: 2019-12-18 08:21:35: Getting Obj report for '//mohnweg/pdbhr01.kestenholz.net'

0
Database Objects REPORT for //mohnweg/pdbhr01.kestenholz.net

HR |TABLE | 7
HR |SEQUENCE | 3
HR |TRIGGER | 2
HR |PROCEDURE | 2
HR |INDEX | 19
HR |VIEW | 1
| |----------
sum | | 34

INFO: 2019-12-18 08:21:36: Getting Obj report for ADB 'atp01_high'

0
Database Objects REPORT for atp01_high

HR |TABLE | 7
HR |SEQUENCE | 3
HR |TRIGGER | 2
HR |PROCEDURE | 2
HR |INDEX | 19
HR |VIEW | 1
| |----------
sum | | 34

X. Troubleshooting

Logs for all steps are available in the installation sub folder. There you can find all excuted commands, detailed error messages.

[root@mohnweg log]# pwd
/opt/mv2adb/out/log
[root@mohnweg log]# ls -ltr
total 68
-rw-rw-rw-. 1 root root 2434 Dec 18 08:10 mv2adb_8039.log
-rw-rw-rw-. 1 root root 13685 Dec 18 08:12 mv2adb_8157.log
-rw-rw-rw-. 1 root root 8222 Dec 18 08:14 mv2adb_8507.log
-rw-rw-rw-. 1 root root 11564 Dec 18 08:19 mv2adb_8808.log
-rw-rw-rw-. 1 root root 5355 Dec 18 08:21 mv2adb_9101.log
-rw-rw-rw-. 1 root root 13412 Dec 18 08:26 mv2adb_9356.log

My ToDo List for next MV2ADB Blog Post

  • Clarification of the license situation, if the export to the cloud has to be encrypted, Advanced Security Option is required, maybe a special license solution like compression for the OCI backup service is planned.
  • Execution of steps without a parameter file.
  • Transfer data without OCI CLI pre-installed.

Summary

The Oracle Cloud Infrastructure MV2ADB is a great tool to make data moves into the OCI Autonomous Database much easier. I like the concept, a small configuration file, passwords are all encrypted and the logs are very detailed. The advisor is helpful to identify conflict in advance.

#ilikeit