Data migration to Autonomous Database using MV2ADB
- Posted by Gavin Soorma
- On April 19, 2020
- 1 Comments
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…
1 Comments