Data migration to Autonomous Database using MV2ADB

Move to Autonomous Database (MV2ADB) is a tool developed by Oracle (thanks Ruggero Citton, great work!) to fully  automate the process of migrating data both from on-premise as well as OCI Classic Gen 1 to Autonomous Database Cloud on OCI.

Data migration and loading to an Autonomous Database can now be performed using a single mv2db command which internally performs an Oracle Data Pump Export, moves the dump file to Cloud Object Store and also performs an Oracle Data Pump Import followed by a recompilation of the schema as well which was migrated.

The mv2adb tool can be downloaded via the  MOS Note 2463574.1 MV2ADB : move data to Autonomous Database in “one click”  which has a lot of good information about the various modes in which the mv2adb tool can be used like auto, expdp, impdp, createbucket ,putdump etc as well as a complete listing of the parameters which can be used in each case.

When we run mv2db tool with the auto option, it internally will run the expdp, putdump and impdp operations automatically.

Let us have a look at some examples of using mv2adb tool to move data from an Oracle 12c database hosted on OCI Classic Gen 1 to an Oracle 19c Autonomous Transaction Processing (ATP) database.

Install mv2adb on the OCI Classic Compute Node

[root@SITOIC01db mv2adb-2.0.1.80]# cp /home/opc/mv2adb-2.0.1.80.tar.gz /opt

[root@SITOIC01db mv2adb-2.0.1.80]#  gunzip mv2adb-2.0.1.80.tar.gz

[root@SITOIC01db mv2adb-2.0.1.80]# tar -xvf mv2adb-2.0.1.80.tar

mv2adb-2.0.1.80/

mv2adb-2.0.1.80/mv2adb

mv2adb-2.0.1.80/conf/

mv2adb-2.0.1.80/conf/DBNAME.mv2adb.cfg

mv2adb-2.0.1.80/utils/

mv2adb-2.0.1.80/utils/install_adb_advisor.sql

mv2adb-2.0.1.80/lib/

mv2adb-2.0.1.80/lib/MV2ADB_Queries.pm

mv2adb-2.0.1.80/lib/MV2ADB_LoggingAndTracing.pm

mv2adb-2.0.1.80/lib/MV2ADB_passwd.jar

mv2adb-2.0.1.80/lib/MV2ADB_Utils.pm

mv2adb-2.0.1.80/lib/MV2ADB_PFile.pm

[root@SITOIC01db mv2adb-2.0.1.80]#  ls -l

total 156

drwxrwxr-x. 2 opc opc     31 Feb  5 13:02 conf

drwxrwxr-x. 2 opc opc   4096 Feb  5 13:02 lib

-rwxrwxr-x. 1 opc opc 154609 Feb  5 13:02 mv2adb

drwxrwxr-x. 2 opc opc     37 Feb  5 13:02 utils

Create the mv2adb configuration file

We need to encrypt the source database SYSTEM password, the target ATP database ADMIN password and also the authentication token of the Cloud Tenant.

We use the mv2adb encpass command for the purpose (we run it each time for the particular user) and store the encrypted passwords in the mv2adb configuration file.

[root@SITOIC01db mv2adb-2.0.1.80]# export MV2ADB_HOME=/opt/mv2adb-2.0.1.80

[root@SITOIC01db mv2adb-2.0.1.80]# cd $MV2ADB_HOME

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb encpass

Please enter the password :

Please re-enter the password :

70B9236DC3812800F108B3F8B7FB2886

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb encpass

Please enter the password :

Please re-enter the password :

92FB814DE0A2749AAD7A801FD4106C2327832EAF5ADFA3CEF485BF91982A9455

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb encpass

Please enter the password :

Please re-enter the password :

6A3D02330291CD5D96D70667E8BC2B1EB8E25B2E3CCBED8D87F758A349F27CEE

[root@SITOIC01db mv2adb-2.0.1.80]#

[root@SITOIC01db mv2adb-2.0.1.80]# cd conf

[root@SITOIC01db conf]# ls

DBNAME.mv2adb.cfg

[root@SITOIC01db conf]# vi mv2adb.conf

#—————————————————#

# Source DB Parameters #

#—————————————————#

DB_CONSTRING=//10.0.3.8/pdb1.602302376.oraclecloud.internal

SYSTEM_DB_PASSWORD=70B9236DC3812800F108B3F8B7FB2886

SCHEMAS=TEST_MIG

DUMP_NAME=exp_test_mig.dmp

DUMP_PATH=/tmp

DUMP_FILES=/tmp/exp_test_mig.dmp

OHOME=/u01/app/oracle/product/12.1.0/dbhome_1

ICHOME=/u01/app/ociclient/instantclient_18_5

# ADB Properties #

#——————————————#

ADB_NAME=payments

ADB_PASSWORD=92FB814DE0A2749AAD7A801FD4106C2327832EAF5ADFA3CEF485BF91982A9455

ADB_CFILE=/tmp/payment_wallet.zip

ADB_CORES=1

#——————————————#

# Object Store Properties #

#——————————————#

OCI_HOST=https://swiftobjectstorage.ap-sydney-1.oraclecloud.com

OCI_NAMESPACE=coexservices01

OCI_BUCKET=migration_data

OCI_ID=automator

OCI_PASSWORD=A60749EE4206FA82CB68D35EA7F1D501AB0031BB2AC68131A718885B87320BFE

#

#—————————————————#

# End Of File #

#—————————————————#

Export Source Data

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb expdp -conf conf/mv2adb_expdp.conf

INFO: 2020-03-24 17:27:41: Please check the logfile ‘/opt/mv2adb-2.0.1.80/out/log/mv2adb_28482.log’ for more details

——————————————————–

mv2adb – Move data to Oracle Autonomous Database

Version: 2.0.1-80

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: 2020-03-24 17:27:41: Reading the configuration file ‘conf/mv2adb_expdp.conf’

INFO: 2020-03-24 17:27:41: Using Oracle Home ‘/u01/app/oracle/product/12.1.0/dbhome_1’

INFO: 2020-03-24 17:27:43: ADB parallelism set by parameter

INFO: 2020-03-24 17:27:43: Getting source DB version

INFO: 2020-03-24 17:27:45: Checking schemas on source DB

INFO: 2020-03-24 17:27:46: Creating expdp directory ‘MV2ADB_EXPDP_DIR’ for path ‘/tmp’

INFO: 2020-03-24 17:27:46: Getting latest SCN

INFO: 2020-03-24 17:27:47: Checking Cloud Service Type

INFO: 2020-03-24 17:27:48: Executing Expdp

INFO: 2020-03-24 17:28:16: Following expdp dump has been created:

      DUMP_FILES=/tmp/exp_test_mig_01.dmp

SUCCESS: 2020-03-24 17:28:16: Expdp executed successfully

Copy the export dump file to the Cloud Object Store

 [root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb putdump -conf conf/mv2adb_expdp.conf

INFO: 2020-03-24 17:31:32: Please check the logfile ‘/opt/mv2adb-2.0.1.80/out/log/mv2adb_29542.log’ for more details

——————————————————–

mv2adb – Move data to Oracle Autonomous Database

Version: 2.0.1-80

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: 2020-03-24 17:31:32: Reading the configuration file ‘conf/mv2adb_expdp.conf’

INFO: 2020-03-24 17:31:33: Loading ‘/tmp/exp_test_mig_01.dmp’ into bucket ‘magia-gen2-migration’

SUCCESS: 2020-03-24 17:31:34: File ‘/tmp/exp_test_mig_01.dmp’ uploaded on ‘magia-gen2-migration’ successfully

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb listdump -conf conf/mv2adb_expdp.conf

INFO: 2020-03-24 18:16:52: Please check the logfile ‘/opt/mv2adb-2.0.1.80/out/log/mv2adb_11466.log’ for more details

——————————————————–

mv2adb – Move data to Oracle Autonomous Database

Version: 2.0.1-80

Copyright (c) 1982-2019 Oracle and/or its affiliates.

——————————————————–

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

RAC Pack, Cloud Innovation and Solution Engineering Team

——————————————————–

Please enter the ‘automator’ Auth Tokens password:

Please re-enter the ‘automator’ Auth Tokens password:

INFO: 2020-03-24 18:17:03: Getting bucket ‘migration_data’ objects

List dump into bucket ‘migration_data’:

{“name”:”exp_test_mig_01.dmp”,”hash”:”3a03e738e9e9791721d6b8a1fa775e5b”,”bytes”:1609728,”last_modified”:”2020-03-24T07:09:25.710000″,”content_type”:null}

Importing the data to the ATP database

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb impdp -conf conf/mv2adb_expdp.conf

INFO: 2020-03-24 17:32:40: Please check the logfile ‘/opt/mv2adb-2.0.1.80/out/log/mv2adb_30080.log’ for more details

——————————————————–

mv2adb – Move data to Oracle Autonomous Database

Version: 2.0.1-80

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: 2020-03-24 17:32:40: Reading the configuration file ‘conf/mv2adb_expdp.conf’

INFO: 2020-03-24 17:32:41: ADB parallelism set by parameter

INFO: 2020-03-24 17:32:41: Checking Cloud Service Type

INFO: 2020-03-24 17:32:42: Drop Object Store Credential

INFO: 2020-03-24 17:32:42: Creating Object Store Credential

INFO: 2020-03-24 17:32:42: Executing Impdp to ADB

INFO: 2020-03-24 17:34:03: Moving impdp log ‘mv2adb_impdp_20200324-173242.log’ to Object Store

SUCCESS: 2020-03-24 17:34:04: Impdp to ADB ‘payments’ executed successfully

INFO: 2020-03-24 17:34:04: Recompiling schemas on ADB ‘payments_high’…

INFO: 2020-03-24 17:34:04: …recompiling schema ‘TEST_MIG’, it may get some time…

[root@SITOIC01db mv2adb-2.0.1.80]#

Moving data using the single mv2adb ‘auto’ command

[root@SITOIC01db mv2adb-2.0.1.80]# ./mv2adb auto -conf conf/mv2adb.conf

INFO: 2020-03-24 18:29:35: Please check the logfile ‘/opt/mv2adb-2.0.1.80/out/log/mv2adb_15326.log’ for more details

——————————————————–

mv2adb – Move data to Oracle Autonomous Database

Version: 2.0.1-80

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: 2020-03-24 18:29:35: Reading the configuration file ‘conf/mv2adb.conf’

INFO: 2020-03-24 18:29:38: Checking schemas on source DB

INFO: 2020-03-24 18:29:38: Performing schema expdp for ‘TEST_MIG’ from source DB…

INFO: 2020-03-24 18:29:38: Step 1 – …ADB parallelism set by parameter

INFO: 2020-03-24 18:29:38: Step 2 – …getting source DB version

INFO: 2020-03-24 18:29:39: Step 3 – …creating expdp directory ‘MV2ADB_EXPDP_DIR’ for path ‘/tmp’

INFO: 2020-03-24 18:29:39: Step 4 – …getting latest SCN

INFO: 2020-03-24 18:29:40: Step 5 – …checking Cloud Service Type

INFO: 2020-03-24 18:29:40: Step 6 – …executing export datapump

SUCCESS: 2020-03-24 18:29:55: Expdp executed successfully

INFO: 2020-03-24 18:29:55: Performing ‘1’ dump upload to Oracle Object Store…

INFO: 2020-03-24 18:29:55: …loading ‘/tmp/exp_test_mig_01.dmp’ into bucket ‘migration_data’

SUCCESS: 2020-03-24 18:29:56: …file ‘/tmp/exp_test_mig_01.dmp’ uploaded on ‘migration_data’ successfully

SUCCESS: 2020-03-24 18:29:56: Upload of ‘1’ dumps over Oracle Object Store complete successfully

INFO: 2020-03-24 18:29:56: Performing impdp into ADB…

INFO: 2020-03-24 18:29:56: Step 1 – …drop Object Store Credential

INFO: 2020-03-24 18:29:56: Step 2 – …creating Object Store Credential

INFO: 2020-03-24 18:29:57: Step 3 – …executing import datapump to ADB

INFO: 2020-03-24 18:31:36: Moving impdp log ‘mv2adb_impdp_20200324-182957.log’ to Object Store

SUCCESS: 2020-03-24 18:31:37: Impdp to ADB ‘payments’ executed successfully

INFO: 2020-03-24 18:31:37: Recompiling schemas on ADB ‘payments_high’…

INFO: 2020-03-24 18:31:37: …recompiling schema ‘TEST_MIG’, it may get some time…

INFO: 2020-03-24 18:31:39: Recompiling schemas on ADB ‘payments_high’…

INFO: 2020-03-24 18:31:39: …recompiling schema ‘TEST_MIG’, it may get some time…