5.26.2020

Oracle Cloud & Third party tools

There have been several buzz words and offerings since the invention of Cloud concepts. We have been hearing of Private Cloud, Public Cloud, Hybrid cloud and more recently multi-clouds concepts and offering from many leading cloud market players.  All most all leading market players are offering several tools and solutions to support seamless administration, maintenance and operations tasks.

Despite many OCI offerings, still we sometimes might need third-party tools or solutions to achieve some of the goals to move or manage the cloud platforms. So, I gonna walk through some of the third-party tools/solutions that are certified and supported OCI operations.

  • According to Commvault, over 80% of companies are using the multi-cloud today. Commvault Cloud Backup solution provides support backup and recovery for  over 40% clouds. This can ease backup and recovery operations between clouds.
  • Vertitas's Netbackup 7.7.x supports backup and recovery operations on Oracle Cloud as well as supports multi-cloud data protection solutions. With NetBackup multi-cloud data protection, you can achieve seamless deduplication between data centers and multi-clouds.  
  • Using Cyberduck version 6.4.0 or higher, you can connect to OCI object storage
  • With dbm cloud systems, you can easy move data and VMs from Classic to OCI, migrate & replicate data from on-premise to OCI and move data stored from any cloud vendor into OCI.
  • VictorOps with OCI integration can greatly assist in cloud infrastructure and application monitoring, as well as real-time incident response will improve significantly. 
  • ZeroDown software provides HA option for Oracle cloud infrastructure. 
  • Coriolis is the simplest way to migrate your windows or Linux VMs alongside their underlying storage and networking configuration across cloud platforms.

There are many other third-party useful solutions available in the market. This is a just tiny list of useful third-party solutions on OCI.

5.14.2020

Migration methods to Autonomous Database (ADB) - Part IV (DBMS_CLOUD.COPY_DATA)

In this part of Migration methods to Autonomous Database (ADB) article series, we will see how to load data into ADB database using the DBMS_CLOUD package.

Assuming you have an ADB database up & running and you wanna load data from the file stored in any of the supported object storage. The image below from Oracle presentation depicts explains what type of object storage is supported to store the files, and settings required part of DBMS_CLOUD package to load the data.



Below image depicts the important procedures and table details to monitor the loading jobs:


You may review my earlier articles to understand how to load files into an Oracle object storage. Once you have the files put in a bucket, you then need to store the OSS credentials in the ADB database using the example below:



And finally use the example below to load the data into the table:

BEGIN
DBMS_CLOUD.COPY_DATA(
table_name =>'CHANNELS',
credential_name =>DEF_CRED_NAME,
file_uri_list =>'https://swiftobjectstorage.<region>.oraclecloud.com/v1/<tenancy>/<bucketname>/<filename>',
format => json_object('delimiter' value ',')
);
END;
/


If you are loading bulk data, you can query table below to know data loading operation progress:

SELECT * 
FROM  user_load_operations
WHERE type = 'COPY';
SELECT table_name, owner_name, type, status, logfile_table, badfile_table
FROM user_load_operations WHERE type = 'COPY';
Tables below provide information about log file and bad file of data load operations:

SELECT * FROM copy$1_log;
SELECT * FROM copy$1_bad;

So, we have learned how to load data into ADB using the DBMS_CLOUD package.


5.13.2020

Migration methods to Autonomous Database (ADB) - Part III (SQL Developer)

In part III article series of Migration methods to Autonomous Database we will see how to migrate or load data into ADB using SQL Developer.

Below slide from Oracle presentation depicts SQL Developer different use cases to migrate or load data into ADB:





Following screenshots walk through data loading scenario from an excel sheet into an existing table using SQL Developer. To walk though this demonstration, ensure the following is in-place:

  • an ADB is created and running
  • You have downloaded the database connection credentials (as shown in the below screenshots)
  • You have an excel sheet to load the data


Downloading connection credentials

Presuming an ADB is created and it's up & running. From the OCI console, select the ADB, click the DB Connection tab to download the credentails.







You have to input a password before you download the connection credentials.



Start the SQL Developer tool and connect to the ADB using the credentials downloaded previously.

Specify the location of the .zip files (connection credentials) and choose the suitable TNS service, high, medium or low.



Once you connected to the database, select table and choose import data option by clicking the right mouse button on the table, as shown below:

















In this scenario, there is a table 'EMP, and we will load the data from an excel sheet. So, choose the file. Ensure you select Local File option from the Source.







Choose Insert option from Import Method:



Map the columns, if not already selected.



Choose the Match by option drop down list:



Finish gives the import details.



Once you click the Finish button, data will be loaded successfully and below message will appear:



And you can see the data is successfully loaded and can be viewed.














So using SQL Developer, you can easily load the data from files CSV, XLS, XLSX, TSV and TXT.
Hope you got the basic understanding of data loading using SQL Developer.

5.12.2020

Migration methods to Autonomous Database (ADB) - Part II (MV2ADB)

In part II of migration methods to ADB blog series, we will look into MV2ADB tool details and usage. If you haven't read the Part I, I would encourage you to read it to get basic knowledge on various solutions offered by Oracle and some of the prerequisites for on-premise database migration to ADB.

Move to Autonomous Database (aka MV2ADB) tool new tool introduced by Oracle to easily migrate on-premise Oracle database over Autonomous Database levering Oracle data pump capabilities. The tool automates data loading into Oracle Object storage and migrating the data into ADB by one-click option.

The picture below depicts the flow of data migration process using the MV2ADB tool:



Ensure following conditions are met before you launch the migration operation:

  • HTTP connectivity between on-premise and Oracle object storage.  Required to move dump files
  • Download ADB credentials file
  • Download and install latest Oracle client to have new version of expdp/impdp and other essential tools
  • Perl >=5.10
  • Perl-data-dumper
  • Set java in execution path
  • Also download and configure OCI CLI from the below link:

https://github.com/oracle/oci-cli




The MV2ADB tool can be installed on-premise using the RPM shown below:

# rpm -i mv2adb-2.0.1-X.noarch.rpm

Following is the directory structure of the tool:

# tree /opt/mv2adb/
/opt/mv2adb/
├── conf
|   └── DBNAME.mv2atp.cfg
├── lib
|   ├── MV2ADB_LoggingAndTracing.pm
|   ├── MV2ADB_passwd.jar
|   ├── MV2ADB_PFile.pm
|   ├── MV2ADB_Queries.pm
|   └── MV2ADB_Utils.pm
└── mv2adb
└── utils
    ├── install_adb_advisor.sql
    └── premigration.jar


 You can also download the tool zip file mv2adb-2.0.1-X.tar.gz from the MOS specified here and using below tar command to untar the file into a specific directory on the server.

tar xvfz mv2adb-2.0.1.X.tar.gz -C <target directory>

Before you use the tool, ensue the path is set correct, as shown below:

$ export MV2ADB_HOME=/home/oracle/mv2adb/mv2adb-2.0.1.x


Using command below, you can de-install the tool:

# rpm -e mv2adb-2.0.1-X.noarch


You can refer the log files for diagnose issues related to the tool:

"/opt/mv2adb/out/log"


MV2ADB supports following operational modes:

  • Auto Operations
  • ExpDP Operations
  • ImpDP Operation
  • OCI Object Storage/Bucket Operations
  • Database Schema Operations
  • Encrypt Password Operations
The Auto operations (aka one command operation) automates the entire migration operation, performing the following individual operation:
  • Schema based expdp from source database
  • Upload the dump file over OCI object storage
  • Loading data with impdp to ADB
Refer the below sytanx for parameters and usage:


Courtesy MOS 2463574.1

Example with configuration file:
# ./mv2adb auto -conf conf/mv2atp.cfg

As mentioned the different operation modes, you can also use to export, copy, or import data using individual operation modes available with the tool. Refer the examples below:

# ./mv2adb expdp \
--dbcs //<host name/ip address>/DB122H1 \
--schemas SCHEMA_1,SCHEMA_2,SCHEMA_3,SCHEMA_4 \
--dumpname expdp.dmp --dumppath /tmp \
--ohome /u01/app/oracle/product/12.2.0.1/dbhome_1 \
--adbname RCATP \
--adbcfile /opt/mv2adb/source/Wallet_RCATP.zip \

# ./mv2adb impdp \
-ociregion us-phoenix \
-ocinamespace NAMESPACE_NAME \
-ocibucket BUCKET_NAME \
-ociid mail@corp.com \
-adbname RCATP \
-cfile /opt/mv2adb/source/Wallet_RCATP.zip \
-ichome /u01/app/oracle/product/instantclient_18_3 \
-dumpfile rcatp_exp_01.dmp,rcatp_exp_02.dmp \
-encryption \
-enctype AES256

For more usage Syntax and examples, refer MOS 2463574.1.

One of the advantages of MV2ADB is that the tool automatically install, configure and execute the Schema Advisor.

The tool can be used with configuration file or command line options.


References: