Migrate your on-premises Database to Oracle Cloud Infrastructure Database System Part 2: Overview

 

Migrate your on-premises Database to Oracle Cloud Infrastructure Database System

Part 2 : Overview

Author: Donatien MBADI OUM, Consultant

 

1.    Introduction

You can migrate your on-premises Oracle Database to an Oracle Cloud Infrastructure Database System using a number of different methods and tools. The method that you apply to a given migration scenario depends on several factors including:

-        Database version

-        Database size

-        Data type in the on-premise database

-        Storage for data staging

-        On-premise Operating System

-        Endian format of Source and Target

-        Character set of Source and Target

-        Network Bandwidth

-        Business Downtime

 

Oracle Database Migration Considerations

The supported versions for migration to Oracle Cloud Infrastructure Database System that we are using in this series of documents are:

-        11g (11.2.0.4) on-premise to 12c PDB on Cloud

-        11g (11.2.0.4) on-premise to 19c PDB on Cloud

-        11g (11.2.0.4) on-premise to 21c PDB on Cloud

-        12c Non-CDB/PDB on-premise to 12c PDB on Cloud

-        12c Non-CDB/PDB on-premise to 19c PDB on Cloud

-        12c Non-CDB/PDB on-premise to 21c PDB on Cloud

-        19c Non-CDB/PDB on-premise to 19c PDB on Cloud

-        19c Non-PDB/PDB on-premise to 21c PDB on  Cloud

-        21c PDB on-premise to 21c PDB on Cloud

Note: Today (2022-10-06), below the available database version on OCI

Oracle Database version in OCI

Where PDB stands for Pluggable Database and CDB stands for Container Database in Multi-Tenant Architecture of 12c, 18c and 19c versions.

If your on-premise Database is at lower version than 11.2.0.4, then you need first to upgrade it to a higher version an then you can migrate to the cloud.

Supported Sources and Targets

Last but not least why should you use 19c? Oracle Database 19c is a Long-Term release. Long-Term releases offer the highest level of stability and the longest length of error correction support. These releases have at least 5 years of Premier Support followed by 3 years of Extended Support. In contrast to that Oracle's innovation releases that Oracle delivers in between only provide 2 years of Premier Support without any Extended Support. That means customers should standardize on long term-support versions of Oracle Database. On the other side if you are interested in the newest leading-edge features for your new application development you may choose the innovation release 21c.

Depending on your target database system in the cloud, you might be able to choose logical or/and physical migration methods:

-        Logical migration means extracting the database objects and data from the source database and creating then again on the target database. From the view of the end-user, the data are the same, and SELECT statements will return the same results, but the data would be probably organized differently in data blocks and data files on disk than on the source database. For example using Data Pump.

-        Using a physical migration method, the data blocks and data files on disk are copied as they are on the target system. This method might be much especially for larger databases. For example using Transportable tablespaces or PDB unplug/plug.

The two methods require to move the dump files, data files or backup files to the cloud. Then, you need access to the operating system of the target database machine and also be able to access to the root container in multitenant architecture.

 

Depending on your migration methods, you have:

-        DBCS as target: logical and physical methods, as access to the underlying database infrastructure and root container is possible.

-        Autonomous as target: only logical methods, as there is no access to the operating system of the database machine and the root container database.

 

2.    Database version, Endian and Character sets

Before talking about the migration methods, let’s talk about database version and introduce what are Endian and Database Character sets. Those two are the most important components which drive the migration process.

2.1.Database version

The database version of your source and target database will impact the migration method to choose. If your target database has a higher release version than the source, then you have to choose a logical- migration method or upgrade your database before or after the migration in case of a physical migration. Migration to the same release version but to a higher patch level, for example 19.6 to 19.16, will require to run datapatch on the target database after migration.

To check the database version, you can run the below script:

SQL> SELECT BANNER_FULL FROM V$VERSION

 

BANNER_FULL      

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

2.2.What is Endian?

Endian is the storage method of multi-byte data type in memory. In other words, it determines the byte order of the data. There are two kinds of endian, Big endian and Little endian.

In the big endian, the most significant data is stored earlier in the memory and in the little endian, the least significant data is stored earlier in memory.

Example: Assume that an integer is stored as 4 bytes (32 bits), then a variable with a value of 0x1234567 will be stored in the form 0x01, 0x23, 0x45, 0x67 in the big endian (Solaris OE 64 bits, IBM Power based Linux, Apple Mac OS, HP-UX 64 bits) whereas, it will be stored in the form 0x67, 0x45, 0x23, 0x01 in the little endian (Solaris OS x86-64, Apple Mac OS x86-64, Microsoft Windows x86 and 64 bit, Linux x86 and 64 bit)

It’s not possible to move the database with normal methods between different endian environments.

You can see the current endian platform in your database with the following query:

SQL> select name,platform_id,platform_name from v$database;

NAME      PLATFORM_ID PLATFORM_NAME

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

DB11G              13 Linux x86 64-bit

SQL>

 

The following queries show other environments where your existing database can be moved using normal methods (Normal method stands for Non-cross platform method)

Little Endian Format (Linux x86-64)

SQL> set lines 200

SQL> set pages 200

SQL> COL "Source" FORM a32

SQL> COL "Compatible Targets" FORM a40

SQL>

SQL> select d.platform_name "Source", t.platform_name "Compatible Targets", endian_format

  2  from v$transportable_platform t, v$database d where t.endian_format = (select endian_format from v$transportable_platform t, v$database d where d.platform_name = t.platform_name)

  3  order by "Compatible Targets";

 

 

Source                           Compatible Targets                       ENDIAN_FORMAT

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

Linux x86 64-bit                 Apple Mac OS (x86-64)                    Little

Linux x86 64-bit                 HP IA Open VMS                           Little

Linux x86 64-bit                 HP Open VMS                              Little

Linux x86 64-bit                 HP Tru64 UNIX                            Little

Linux x86 64-bit                 Linux IA (32-bit)                        Little

Linux x86 64-bit                 Linux IA (64-bit)                        Little

Linux x86 64-bit                 Linux x86 64-bit                         Little

Linux x86 64-bit                 Microsoft Windows IA (32-bit)            Little

Linux x86 64-bit                 Microsoft Windows IA (64-bit)            Little

Linux x86 64-bit                 Microsoft Windows x86 64-bit             Little

Linux x86 64-bit                 Solaris Operating System (x86)           Little

Linux x86 64-bit                 Solaris Operating System (x86-64)        Little

 

12 rows selected.

 

Big Endian Format (IBM AIX)

 

SQL> set lines 200

SQL> set pages 200

SQL> COL "Source" FORM a32

SQL> COL "Compatible Targets" FORM a40

SQL> select d.platform_name "Source", t.platform_name "Compatible Targets", endian_format

from v$transportable_platform t, v$database d where t.endian_format = (select endian_format from v$transportable_platform t, v$database d where d.platform_name = t.platform_name)

order by "Compatible Targets";  

 

Source                           Compatible Targets                       ENDIAN_FORMAT

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

AIX-Based Systems (64-bit)       AIX-Based Systems (64-bit)               Big

AIX-Based Systems (64-bit)       Apple Mac OS                             Big

AIX-Based Systems (64-bit)       HP-UX (64-bit)                           Big

AIX-Based Systems (64-bit)       HP-UX IA (64-bit)                        Big

AIX-Based Systems (64-bit)       IBM Power Based Linux                    Big

AIX-Based Systems (64-bit)       IBM zSeries Based Linux                  Big

AIX-Based Systems (64-bit)       Solaris[tm] OE (32-bit)                  Big

AIX-Based Systems (64-bit)       Solaris[tm] OE (64-bit)                  Big

 

8 rows selected.

Note: All the latest operating systems are on little endian.

 

 

2.3.What are Oracle Database character sets?

The NLS_CHARACTERSET of an Oracle Database defines what characters can be stored in the database using the CHAR, VARCHAR2, LONG and CLOB data types.  A Character set does not define languages, it defines a certain range of characters. Any language that uses the characters known by that character set can then be stored.

The characters are stored as numbers. Most of time, the character set is represented as <region><No of bits><Character set Name><S/C>. For example WE8ISO8859P1 is a Western European 8-bit ISO 8859 Part 1.

You can see the current character set value by running the following query:

 

SQL>select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET'

 

PARAMETER                        VALUE

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

NLS_CHARACTERSET                AL32UTF8

 

SQL>

 

Or this:

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

 

VALUE$

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

AL32UTF8

 

SQL>

 

Or

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

 

PARAMETER                            VALUE

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

NLS_CHARACTERSET                   AL32UTF8

 

If you don’t have compatible character sets on source and target databases then you can have data loss during the migration there can be performance implications as well. Oracle recommends choosing AL32UTF8 database character set be3cause it supports most of the languages of the world.

 

3.    Migration methods

There are multiple migration methods available depending on factors listed earlier. These methods includes:

3.1.Conventional Datapump Export/Import

-        Introduced in 10g Database version

-        Designed to handle a large volume of data

-        Very data is very large (few hundred GBs)

-        From a performance point of view, the Transportable Tablespace method could be quicker

-        Nothing to worry about Character set or Endian

-        Can be used for 11g on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 12c Non-CDB/PDB on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 18c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

-        Can be used for 19c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

 

 

3.2.Transportable tablespace Datapump Export/Import

-        Fastest Method to migrate Data

-        Entire Data files in a Tablespace are moved from source to Target Database

-        Moving of Data files is quick compared to each object hence this method is

-        quicker than Data Pump Export/Import

-        Requires some configuration and setup to migrate using Transportable

-        Tablespaces could be complex

-        Can be used for 11g on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 12c Non-CDB/PDB on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 18c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

-        Can be used for 19c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

 

3.3.Full transportable Datapump Export/Import

-        Introduced in 12c, make migration to 12c Database Easy and Quick

-        12c can be configured as Non-CDB or CDB/PDB

-        Can be used to migrate to both Non-CDB or PDB

-        Moves all of System, User, and Application Metadata without complex setup required in Transportable Tablespace Method

-        Combines the ease of Data-pump and speed of Transportable Tablespaces

-        Can be used for 11g on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 12c Non-CDB/PDB on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 18c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

-        Can be used for 19c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

 

3.4. RMAN CONVERT Transportable tablespace with Datapump

-         RMAN Convert TTS + expdp

-        Copy Dump and Image File to Cloud Compute

-        12c Non-CDB/PDB on-premise to 12c PDB Cloud Migration

-        19c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

 

3.5.RMAN Cross-Platform Transportable PDB

-        RMAN Unplug/Plug: 12c PDB on-premise to 12c PDB on Cloud

-        RMAN Unplug/Plug: 19c PDB on-premise to 19c PDB on Cloud

-        If Endian Conversion is Required

-        Unplug PDB + RMAN BACKUP FOR TRANSPORT PLUGGABLE DATABASE

-        Copy PDB to Compute Cloud

-        RMAN RESTORE ALL FOREIGN DATAFILES + Plug PDB

 

3.6.RMAN Cross-Platform Transportable PDB (inconsistent Backups)

-        If Endian Conversion is required RMAN BACKUP TO PLATFORM DATAPUMP TABLESPACE

-        Copy Backup Set & Dump File

-        Restore Foreign Tablespace Dump File to Cloud PDB

-        Can be used for 11g on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 12c Non-CDB/PDB on-premise to 12c/19c PDB Cloud Migration

-        Can be used for 18c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

-        Can be used for 19c Non-CDB/PDB on-premise to 19c PDB Cloud Migration

 

3.7.Unplugging/Plugging (CDB)

-        Uses 12c/18c/19c Multitenant feature of Pluggable Database(PDB)

-        12c Non-CDB/PDB on Premise to 12c PDB on Cloud

-        19c Non-CDB/PDB on Premise to 19c PDB on Cloud

-        If TDE is enabled, export TDE master encryption key on the source before migration and Import TDE Master key in CDB on target (If no key store in CDB then create it)

 

3.8.SQL Developer

-        SQL Developer to migrate data to Cloud has two options

-        DDL statement + SQL*loader control & Data File

-        DDL statement + Insert Statement (Use SQL Developer)

-        User SQL Developer

 

3.9.Oracle Golden Gate

-        Zero or near-zero downtime migrations when an Oracle Data Guard solution is not applicable

-        Bi-directional replication can be configured from the migrated database back to the production database for use as a fallback solution

 

 

3.10.    ZDM (Zero Downtime Migration)

-        Oracle automated tools make it seamless to move your on-premises database to the Oracle Cloud with virtually no downtime.

-        Zero Downtime Migration does not support cross-edition migration.

-        Zero Downtime Migration cannot be used to migrate an Enterprise edition database to a Standard edition database, and vice versa.