Migrate On-Premises Database to Autonomous Database Cloud

Today we will learn how to migrate an Oracle database (On-Premises) to Autonomous Database Cloud.

In this environment will be show the usage of a new tool called MV2ADB. This tool can, after completing the configuration file, execute all the steps to export, transport and import a database to the Oracle Autonomous Cloud.

INSTALL THE MV2ADB SCRIPT

The script that allows you easy migration to ADB can be downloaded from My Oracle Support through note 2463574.1.

INSTALL THE MV2ADB TOOL AS ROOT USER

[root@hol /]# cd binaries/

[root@hol binaries]# ll

total 52532

-rw-r--r--. 1 oracle dba    31216 Jul 13 19:08 mv2adb-2.0.1-40.noarch.rpm

-rw-r--r--. 1 oracle dba 53758240 Jul 13 19:08 oracle-instantclient19.3-basic-19.3.0.0.0-1.x86_64.rpm

[root@hol binaries]# rpm -ivh mv2adb-2.0.1-40.noarch.rpm

warning: mv2adb-2.0.1-40.noarch.rpm: Header V4 RSA/SHA1 Signature, key ID e7004b4d: NOKEY

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

Updating / installing...

   1:mv2adb-2.0.1-40                  ################################# [100%]




MV2ADB has been installed on /opt/mv2adb succesfully!




[root@hol binaries]#

Please note that the install script shows the location where the tool has been installed. In this case /opt/mv2adb. We need this later.

INSTALLED ORACLE INSTANT CLIENT

We have already downloaded and unzipped the required files for the Oracle Instant Client. In the directory /opt/instalclient we have unzipped the base Instant Client, the SQL*Plus zipfile and the Tools zipfile. All have been downloaded from OTN.

https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

instantclient-basic-linux.x64-19.3.0.0.0dbru.zip

instantclient-tools-linux.x64-19.3.0.0.0dbru.zip

instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip

[root@hol opt]# unzip /binaries/instantclient-basic-linux.x64-19.3.0.0.0dbru.zip

Archive:  /binaries/instantclient-basic-linux.x64-19.3.0.0.0dbru.zip

  inflating: instantclient_19_3/adrci

  inflating: instantclient_19_3/BASIC_LICENSE

  inflating: instantclient_19_3/BASIC_README

  inflating: instantclient_19_3/genezi

  inflating: instantclient_19_3/libclntshcore.so.19.1

    linking: instantclient_19_3/libclntsh.so  -> libclntsh.so.19.1

    linking: instantclient_19_3/libclntsh.so.10.1  -> libclntsh.so.19.1

    linking: instantclient_19_3/libclntsh.so.11.1  -> libclntsh.so.19.1

    linking: instantclient_19_3/libclntsh.so.12.1  -> libclntsh.so.19.1

    linking: instantclient_19_3/libclntsh.so.18.1  -> libclntsh.so.19.1

  inflating: instantclient_19_3/libclntsh.so.19.1

  inflating: instantclient_19_3/libipc1.so

  inflating: instantclient_19_3/libmql1.so

  inflating: instantclient_19_3/libnnz19.so

    linking: instantclient_19_3/libocci.so  -> libocci.so.19.1

    linking: instantclient_19_3/libocci.so.10.1  -> libocci.so.19.1

    linking: instantclient_19_3/libocci.so.11.1  -> libocci.so.19.1

    linking: instantclient_19_3/libocci.so.12.1  -> libocci.so.19.1

    linking: instantclient_19_3/libocci.so.18.1  -> libocci.so.19.1

  inflating: instantclient_19_3/libocci.so.19.1

  inflating: instantclient_19_3/libociei.so

  inflating: instantclient_19_3/libocijdbc19.so

  inflating: instantclient_19_3/liboramysql19.so

   creating: instantclient_19_3/network/

  inflating: instantclient_19_3/ojdbc8.jar

  inflating: instantclient_19_3/ucp.jar

  inflating: instantclient_19_3/uidrvci

  inflating: instantclient_19_3/xstreams.jar

   creating: instantclient_19_3/network/admin/

  inflating: instantclient_19_3/network/admin/README

finishing deferred symbolic links:

  instantclient_19_3/libclntsh.so -> libclntsh.so.19.1

  instantclient_19_3/libclntsh.so.10.1 -> libclntsh.so.19.1

  instantclient_19_3/libclntsh.so.11.1 -> libclntsh.so.19.1

  instantclient_19_3/libclntsh.so.12.1 -> libclntsh.so.19.1

  instantclient_19_3/libclntsh.so.18.1 -> libclntsh.so.19.1

  instantclient_19_3/libocci.so -> libocci.so.19.1

  instantclient_19_3/libocci.so.10.1 -> libocci.so.19.1

  instantclient_19_3/libocci.so.11.1 -> libocci.so.19.1

  instantclient_19_3/libocci.so.12.1 -> libocci.so.19.1

  instantclient_19_3/libocci.so.18.1 -> libocci.so.19.1

[root@hol opt]# unzip /binaries/instantclient-tools-linux.x64-19.3.0.0.0dbru.zip

Archive:  /binaries/instantclient-tools-linux.x64-19.3.0.0.0dbru.zip

  inflating: instantclient_19_3/exp

  inflating: instantclient_19_3/expdp

  inflating: instantclient_19_3/imp

  inflating: instantclient_19_3/impdp

  inflating: instantclient_19_3/libnfsodm19.so

  inflating: instantclient_19_3/sqlldr

  inflating: instantclient_19_3/TOOLS_LICENSE

  inflating: instantclient_19_3/TOOLS_README

  inflating: instantclient_19_3/wrc

[root@hol opt]# unzip /binaries/instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip

Archive:  /binaries/instantclient-sqlplus-linux.x64-19.3.0.0.0dbru.zip

  inflating: instantclient_19_3/glogin.sql

  inflating: instantclient_19_3/libsqlplusic.so

  inflating: instantclient_19_3/libsqlplus.so

  inflating: instantclient_19_3/sqlplus

  inflating: instantclient_19_3/SQLPLUS_LICENSE

  inflating: instantclient_19_3/SQLPLUS_README

[root@hol opt]#

Change the owner and group of the installclient folder

[root@hol opt]# chown oracle:dba instantclient_19_3/

[root@hol opt]# ll -l instantclient_19_3/

total 240144

-rwxr-xr-x. 1 root root     41840 Apr 17 08:38 adrci

-r-xr-xr-x. 1 root root      5780 Apr 17 08:38 BASIC_LICENSE

-rw-r--r--. 1 root root      1632 Apr 17 08:38 BASIC_README

-rwxr-xr-x. 1 root root   1018968 Apr 17 08:39 exp

-rwxr-xr-x. 1 root root    220640 Apr 17 08:39 expdp

-rwxr-xr-x. 1 root root     59296 Apr 17 08:38 genezi

-rw-r--r--. 1 root root       342 Apr 17 08:39 glogin.sql

-rwxr-xr-x. 1 root root    502536 Apr 17 08:39 imp

-rwxr-xr-x. 1 root root    232768 Apr 17 08:39 impdp

-rwxr-xr-x. 1 root root   8041608 Apr 17 08:38 libclntshcore.so.19.1

lrwxrwxrwx. 1 root root        17 Jul 13 21:53 libclntsh.so -> libclntsh.so.19.1

lrwxrwxrwx. 1 root root        17 Jul 13 21:53 libclntsh.so.10.1 -> libclntsh.so.19.1

lrwxrwxrwx. 1 root root        17 Jul 13 21:53 libclntsh.so.11.1 -> libclntsh.so.19.1

lrwxrwxrwx. 1 root root        17 Jul 13 21:53 libclntsh.so.12.1 -> libclntsh.so.19.1

lrwxrwxrwx. 1 root root        17 Jul 13 21:53 libclntsh.so.18.1 -> libclntsh.so.19.1

-rwxr-xr-x. 1 root root  79961792 Apr 17 08:38 libclntsh.so.19.1

-r-xr-xr-x. 1 root root   3609536 Apr 17 08:38 libipc1.so

-r-xr-xr-x. 1 root root    478432 Apr 17 08:38 libmql1.so

-rwxr-xr-x. 1 root root     60024 Apr 17 08:39 libnfsodm19.so

-rwxr-xr-x. 1 root root   6587832 Apr 17 08:38 libnnz19.so

lrwxrwxrwx. 1 root root        15 Jul 13 21:53 libocci.so -> libocci.so.19.1

lrwxrwxrwx. 1 root root        15 Jul 13 21:53 libocci.so.10.1 -> libocci.so.19.1

lrwxrwxrwx. 1 root root        15 Jul 13 21:53 libocci.so.11.1 -> libocci.so.19.1

lrwxrwxrwx. 1 root root        15 Jul 13 21:53 libocci.so.12.1 -> libocci.so.19.1

lrwxrwxrwx. 1 root root        15 Jul 13 21:53 libocci.so.18.1 -> libocci.so.19.1

-rwxr-xr-x. 1 root root   2339896 Apr 17 08:38 libocci.so.19.1

-rwxr-xr-x. 1 root root 130515320 Apr 17 08:38 libociei.so

-r-xr-xr-x. 1 root root    153624 Apr 17 08:38 libocijdbc19.so

-rwxr-xr-x. 1 root root    115976 Apr 17 08:38 liboramysql19.so

-rwxr-xr-x. 1 root root   1660752 Apr 17 08:39 libsqlplusic.so

-rwxr-xr-x. 1 root root   1573416 Apr 17 08:39 libsqlplus.so

drwxr-xr-x. 3 root root        19 Apr 17 08:38 network

-rw-r--r--. 1 root root   4210517 Apr 17 08:38 ojdbc8.jar

-rwxr-xr-x. 1 root root   1660560 Apr 17 08:39 sqlldr

-rwxr-xr-x. 1 root root     24792 Apr 17 08:39 sqlplus

-r-xr-xr-x. 1 root root      5780 Apr 17 08:39 SQLPLUS_LICENSE

-rw-r--r--. 1 root root      1640 Apr 17 08:39 SQLPLUS_README

-r-xr-xr-x. 1 root root      5780 Apr 17 08:39 TOOLS_LICENSE

-rw-r--r--. 1 root root      1632 Apr 17 08:39 TOOLS_README

-rw-r--r--. 1 root root   1680074 Apr 17 08:38 ucp.jar

-rwxr-xr-x. 1 root root    236960 Apr 17 08:38 uidrvci

-rwxr-xr-x. 1 root root    751072 Apr 17 08:39 wrc

-rw-r--r--. 1 root root     74263 Apr 17 08:38 xstreams.jar

[root@hol opt]#

CREATING A NEW AUTONOMOUS ATP ENVIRONMENT

Log into Oracle Cloud and click the “Create Instance” link.

Click on the “Create” button in the Autonomous Transaction Processing

Select the compartment you want to build the service in, then click the “Create Autonomous Database” button.

Enter the details of the service you want to create. The default sizes are 1 CPU core and 1TB of storage. Remember to select the appropriate licensing model. Click the “Create Autonomous Database” button.

Compartment : <keep value>

Display Name : MIGRATE_DBON_DBOCI

Database Name : MIGRATE

Workload Type : Transaction Processing

Choose a deployment type:  Serverless

CPU Core Count : 1

Storage (TB) : 1

Password : OracleMIG#2019

License Type : My Organization Already owns Oracle Database (etc..)

This process will take between 5 and 10 minutes to complete.

CREATE AN OBJECT STORE BUCKET

As we need to upload the export to the OCI environment, we need to create a location to do this. The MV2ADB script could create a new location but this would require the setup of the OCI Commandline tools. Since this environment is using a more generic approach, we need to pre-create the directory (called Bucket).

NAVIGATE TO OBJECT STORAGE IN THE OCI CONSOLE

Select the compartment you want to build the service in, then click the “Create Bucket” button.

Enter the details of the service you want to create. Click the “Create Bucket” button.

Write down the name of the bucket as we will need it in our configuration file. The name of the bucket in the configuration file is case-sensitive.

CHECK SOURCE SCHEMAS FOR COMPATIBILITY

Not everything is supported in the Autonomous Database Cloud environment. To make sure you do not run into any issues, a tool called ADB Schema Advisor has been created. This PL/SQL Package can generate a report to show you any issues you might encounter before you actually execute the migration.

PACKAGE SOURCE ALREADY DOWNLOADED

The ADB Schema advisor can be downloaded from MOS note 2462677.1 (Oracle Autonomous Database Schema Advisor).

We have already downloaded the latest version to the /source directory in your client image.

Please note; in a regular environment, this package does not require SYS or SYSTEM user to be installed. When installing it into a non-SYS and non-SYSTEM user, please check the manual for the exact installation steps. Very important are the GRANTS needed to give the package access to the information it needs.

[oracle@hol ~]$ . oraenv

ORACLE_SID = [oracle] ? UPGR

ORACLE_BASE environment variable is not being set since this

information is not available for the current user ID oracle.

You can set ORACLE_BASE manually if it is required.

Resetting ORACLE_BASE to its previous value or ORACLE_HOME

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

[oracle@hol ~]$ sqlplus




SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 13 23:17:26 2019




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




Enter user-name: / as sysdba




Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options




SQL> @/home/oracle/adb_advisor.plb




Package created.







Package body created.




SQL>

SQL>

As you can see, there are some directory objects that cannot be migrated as ADB does not support access to the local filesystem (besides the DP_DUMP_DEST directory).

A second issue are 1 tables that apparently need changes before they can be imported. A little bit further down in the report, the issues are explained:

NOLOGGING options will be automatically changed to LOGGING options
Index Organized Tables (IOT) are not supported. You need a special option for IMPDP to change this during import.

GATHERING REQUIRED DETAILS

The configuration file for MV2ADB needs a series of parameters for export, upload of the dumpfile and import of the dumpfile. A full file with hints can be found in the /opt/mv2adb/conf directory. For this lab we will use only the parameters needed for a simple migration.

CREATE A NEW FILE FOR THE CONFIGURATION

sudo vi /opt/mv2adb/conf/ATP.mv2adb.conf

Cut-and-paste the below parameters in this new document so that we can start entering the required data. At this moment, only copy-and-paste the below, we will make changes to the values in the following sections.

# DB Parameters




DB_CONSTRIG=//hol:1521/UPGR

SYSTEM_DB_PASSWORD=D5E036E7E7A09843561DCB5AEA05BCD2

SCHEMAS=CARLOCA

DUMPFILES=/tmp/DB112-UPGR.dmp

OHOME=/u01/app/oracle/product/11.2.0.4

ICHOME=/opt/instantclient_19_3

# Expdp/Impdp Parameters

ENC_PASSWORD=D5E036E7E7A09843561DCB5AEA05BCD2

ENC_TYPE=AES256

# Object Store Properties

BMC_HOST=

BMC_TENNANT=

BMC_BUCKET=

BMC_ID=

BMC_PASSWORD=

# ADB Parameters

ADB_NAME=

ADB_PASSWORD=

CFILE=

GATHERING (SOURCE) DB PARAMETERS

The initial section is regarding the source database. Please enter the following information for the source environment. Since this is a environment, we have pre-entered most of the DB parameters for you. Here is some information where you can find the details:

DB_CONSTRIG	Connecting string from the local system (where mv2adb is running) to the database instance that needs to be migrated
SYSTEM_DB_PASSWORD	Password for SYSTEM user for this source database
SCHEMAS	Schema’s to be exported; only transport the schema’s that you need, do not include any default schema’s like HR, OE, SYSTEM, SYS etc as they already exist in ADB and might result in errors (or get ignored)
DUMPFILES	File system location of dumpfiles. If you want parallelism during export and import, specify as many files as you want the parallelism to be. Make sure the files are unique in the source but also in the destination (ATP) environment.
OHOME	The source database Oracle Home
IHOME	*The installed Oracle Instant Client home (basic, SQLPlus and Tools unzipped)**

GATHERING EXPDP/IMPDP PARAMETERS

In this section you specify the encryption password and the encryption type for your export. To make sure your data cannot be compromised when exporting and uploading your data, the script requires a password.

ENC_PASSWORD	A password that will encrypt your datapump exports. Has nothing to do with any existing user or password. Please note that this password cannot be plain text. The password needs to be encrypted using the mv2adb binaries on your system
END_TYPE	Type of encryption of your data. The higher the number, the more encryption but also slower export/import. Options are AES128, AES192 and AES256

ENCRYPT THE ENCRYPTION PASSWORD AND PUT IT IN THE FILE

The password we will use for this environment is oracle

[oracle@hol ~]$ /opt/mv2adb/mv2adb encpass

Please enter the password :  oracle

Please re-enter the password : oracle

D5E036E7E7A09843561DCB5AEA05BCD2

Make sure your encrypted password is entered in your ATP.mv2adb.conf config file.

GATHERING OBJECT STORE PROPERTIES

The Autonomous database can only use dumpfiles uploaded to Swift compatible storage. The following parameters specify where the dumpfiles should be uploaded to after the export. This is also the location where the logfiles will be stored. Instead of the below SWIFT details, you can also choose to locally install the OCI Client and use that setup. See the example config for more information.

BMC_HOST	This is the Swift object storage URL for your environment.
BMC_TENNANT	Name of your Tenancy. Be aware, for SWIFT we only use lower case
BMC_BUCKET	The name of a new bucket that will be created for this migration.
BMC_ID	Your username in OCI
BMC_PASSWORD	The Swift Authentication Token encrypted using the mv2adb password encoder.

GENERATE TOKEN FOR SWIFT API

Click in Identity and Select Users

Select the user for creating the authentication token.

Click in Generate Token

Put the Description about this token and Click in Generate Token

Copy this token for using in the connect the tool mv2adb with Object Storage.

LOCATE THE SWIFT AUTHENTICATION PASSWORD AND ENCRYPT USING THE MV2ADB TOOL

[oracle@hol ~]$ cat /home/oracle/auth_token.txt

cn#Wi]6xv4hO(:j0l0SX

[oracle@hol ~]$ /opt/mv2adb/mv2adb encpass

Please enter the password : <cut-and-paste auth_key>

Please re-enter the password : <cut-and-paste auth_key>

E54C941DA0DBA8EB467DCC7F0C04(...)ED747D3AF6B6184BC173B78DE426CEBE4

FILL IN ALL OF THE DETAILS FOR THE OBJECT STORE SETTINGS

# Object Store Properties

BMC_HOST=https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com

BMC_TENNANT= andreontalba

BMC_BUCKET= BC_MIGRATE 

BMC_ID=andre.ontalba

BMC_PASSWORD= E54C941DA0DBA8EB467DCC7F0C04(...)ED747D3AF6B6184BC173B78DE426CEBE4

GATHERING ADB PARAMETERS

During the gathering of the other parameters, your ADB environment should have been created. As a last step we will now gather the information needed for the last section

ADB_NAME	Name of your ADB instance
ADB_PASSWORD	Database Admin password
CFILE	Zipfile containing database credentials

First parameter requires the name of your created ADB environment. Navigate to the ADB Console and find the name of your database. My Database is MIGRATE

Second parameter is the password you have entered while creating the Autonomous environment. If you have used the suggested password, it would be OracleMIG#2019. If you have chosen another password, you need to remember it.

ENCRYPT YOUR DATABASE PASSWORD USING THE MV2ADB ENCRYPT OPTION

[oracle@hol ~]$ /opt/mv2adb/mv2adb encpass

Please enter the password :  

Please re-enter the password :  

DE3D105A8E6F6A4D5E86EXSW6BC1D3BA

For the 3rd parameter we need to download something from the OCI console, the so-called Wallet file.

CLICK ON THE BUTTON 'DB CONNECTION'

The following screen will be displayed:

CLICK ON THE BUTTON 'DOWNLOAD' TO DOWNLOAD THE WALLET ZIP

In the following screen a password is requested. This is the password that protects the keystore inside the zipfile. For this exercise we will not be using this keystore so enter any random password twice.

ENTER PASSWORD AND PRESS ‘DOWNLOAD‘

Your zipfile will be downloaded to the default location /home/oracle/Downloads. Please note the name of the wallet.zip and enter this in your parameters.

# ADB Parameters

ADB_NAME=MIGRATE

ADB_PASSWORD= DE3D105A8E6F6A4D5E8XXXSW6BC1D3BA

CFILE=/home/oracle/Downloads/wallet.zip

MAKE SURE ALL PARAMETERS ARE ENTERED AND SAVE THE FILE TO /opt/mv2adb/conf/ATP.mv2adb.conf

START THE MIGRATION USING THE MV2ADB SCRIPT

START THE MV2ADB SCRIPT USING THE CONFIGURATION FILE YOU JUST CREATED.

[oracle@hol ~]$ sudo /opt/mv2adb/mv2adb auto -conf /opt/mv2adb/conf/ATP.mv2adb.conf 

INFO: 2019-08-31 14:08:27: Please check the logfile '/opt/mv2adb/out/log/mv2adb_12765.log' for more details

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

mv2adb - Move data to Oracle Autonomous Database

Author: Ruggero Citton <[email protected]>

RAC Pack, Cloud Innovation and Solution Engineering Team

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




Version: 2.0.1-29

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

INFO: 2019-08-31 14:08:27: Reading the configuration file '/opt/mv2adb/conf/ATP.mv2adb.conf'

INFO: 2019-08-31 14:08:28: Checking schemas on source DB

...

INFO: 2019-03-31 14:08:54: ...loading '/tmp/DB112-UPGR.dmp' into bucket 'UPGRADEBUCKET-RP'

SUCCESS: 2019-03-31 14:09:27: ...file '/tmp/DB112-UPGR.dmp' uploaded on 'UPGRADEBUCKET-RP' successfully

SUCCESS: 2019-03-31 14:09:27: Upload of '1' dumps over Oracle Object Store complete successfully

INFO: 2019-03-31 14:09:27: Performing impdp into ADB...

INFO: 2019-03-31 14:09:27: Step1 - ...drop Object Store Credential

INFO: 2019-03-31 14:09:29: Step2 - ...creating Object Store Credential

INFO: 2019-03-31 14:09:36: Step3 - ...executing import datapump to ADB

INFO: 2019-03-31 14:12:42: Moving impdp log 'mv2adb_impdp_20190831-140936.log' to Object Store

SUCCESS: 2019-03-31 14:12:43: Impdp to ADB 'MIGRATE' executed successfully

After about 10 minutes, all the steps should have been executed successfully. If you encounter any error, please check the logfile that was displayed immediately after you started the script. This will contain all of the individual steps, commands used and the output of those commands.

LOGIN AND CHECK THE MIGRATED DATABASE

USE **SQL*DEVELOPER TO CHECK IF THE CARLOCA USER HAS BEEN MIGRATED TO ATP**

On your Desktop, you can see SQL*Developer. Start this application

CREATE A NEW CONNECTION TO ATP BY CLICKING ON THE GREEN + SIGN IN THE CONNECTIONS PANE

Connection Name : MYATP 

Username : admin 

Password : OracleMIG#2019 (or any other password you have used) 

Connection Type : Cloud Wallet 

Configuration File : 

<select the wallet you downloaded in /home/oracle/Downloads> 



Service : migrate_tp

ENTER THE REQUIRED DETAILS AND PRESS CONNECT

After connecting, a new SQL Window will be displayed. Here you can execute queries on the ATP environment.

ENTER THE QUERY AND PRESS THE GREEN ARROW TO EXECUTE IT

In the Query Result window, the result of the query will be displayed:

I hope this helps you!!!

This article was written by Andre Ontalba e Rodrigo Mufalani

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful.”