US6 - Move - To - The - Cloud

Move to the Cloud
Not only for Techies ...
Roy Swonger, Mike Dietrich & Daniel Overby Hansen

Database Upgrade, Utilities and Patching
1 Copyright © 2021, Oracle and/or its affiliates

1
Roy F. Swonger
Vice President
Database Upgrade,
Utilities & Patching
@RoyFSwonger
Mike Dietrich
Distinguished Product Manager

Database Upgrade
and Migrations
https://MikeDietrichDE.com
@MikeDietrichDE
mikedietrich
Daniel Overby Hansen
Senior Principal Product Manager

Cloud Migration
https://dohdatabase.com
@dohdatabase
dohdatabase
Autonomous Database
How to Move Your Data into an

Autonomous Database
”An autonomous database is a cloud database that uses
machine learning to automate database tuning, security,
backups, updates, and other routine management tasks
traditionally performed by DBAs.”
What Is an Autonomous Database – Oracle.com

Autonomous Database | Infrastructure
Shared A simple and elastic choice. Oracle autonomously

operates all aspects of the database life cycle from
database placement to backup and updates.
Dedicated A private cloud in public cloud choice. A completely

dedicated compute, storage, network and database
service for only a single tenant. Dedicated infrastructure
provides for the highest levels of security isolation and
governance. The customer has customizable operational
policies to guide Autonomous Operations for workload
placement, workload optimization, update scheduling,
availability level, over provisioning and peak usage

Autonomous Database | Restrictions
Restrictions apply • Parameters
• SQL commands
• Data types
• Features
Pro tip: Cloud services evolve rapidly, check the

documentation for up-to-date information

Autonomous Database | Always Free
• Free of charge
• Usage:
• Small-scale apps
• Development
• Testing
• Quick start showcase

• MuShop
Watch on YouTube

”When it comes to migration, Autonomous
Database differs because you will be migrating
data, not databases”

Autonomous Database | Migration Techniques
Direct Autonomous
Database
Stage Object
Storage

Photo by Alejandro Morelos on Unsplash
Importing
Death Metal
Demo | Data Set
Watch on YouTube

Options | Overview
1. 2. 3. 4. 5. 6.
SQL Developer Web SQL Developer SQL*Loader Data Pump DBMS_CLOUD MV2ADB

Options | SQL Developer Web
Easily accessible from OCI console
Quick and simple
Works on:
• CSV
• XML
• JSON
• XLS/XLSX
• Avro
Quickstart lab
Jeff Smith blog post

Options | SQL Developer Web
Watch on YouTube

Options | Overview
1. 2. 3. 4. 5. 6.

Options | SQL Developer
Local installation - Download

Quick and simple
Works on:
• CSV
• XML
• JSON
• XLS/XLSX
• Avro
Loads from:
• Local file
• OCI object stage
Quickstart lab

Options | SQL Developer
Watch on YouTube

Options | Overview
1. 2. 3. 4. 5. 6.

Options | SQL*Loader
Highly configurable
Can transform data
Loads from:
• Local file
Works on:
• CSV
• Text

Options | SQL*Loader
Watch on YouTube

Options | Overview
1. 2. 3. 4. 5. 6.

Options | Data Pump
Fast unload and load
All or selected data
Best option for big data
Can transform metadata
Loads from:
• Oracle Database (database link)
• OCI Object Storage
Pro tip: Use SQL Developer

Options | Data Pump
Parameter MAX_DATAPUMP_PARALLEL_PER_JOB is 50 -
and can't be changed
Network mode does not support

parallel meta data load

Options | Data Pump
Watch on YouTube

Options | Overview
1. 2. 3. 4. 5. 6.

Options | DBMS_CLOUD
PL/SQL interface
Loads from:
• OCI Object Storage
• Amazon AWS S3
• Microsoft Azure Object Store
Works on:
• CSV
• Data Pump
• ORC
• Parquet
• Avro
• Zipped files
Quickstart lab

Troubleshooting
Coming to on-premises
Documentation
Use SQL Developer

Watch on YouTube

Options | Overview
1. 2. 3. 4. 5. 6.

Options | MV2ADB
"One button approach"
Uses Data Pump (schema mode)
Loads from:
• Oracle Database
Documentation: Doc ID 2463574.1
Support import over DB link (--netlink)
Runs on Linux / Solaris
Pro Tip:
Check log file to find out how MV2ADB uses
Data Pump

Options | MV2ADB
Enable Data Pump metrics (METRICS=Y and LOGTIME=ALL)

$ mv2adb.bin auto --conf mv2adb_metal.cfg --nosudo -dpdebug
Exclude statistics
EXTRA_EXPDP=EXCLUDE=STATISTICS
Enable Data Pump compression (license required)

COMPRESSION=ALL

Options | MV2ADB
Watch on YouTube

Options | Overview
SQL Developer Web
SQL Developer Web

SQL Developer
SQL Developer
DBMS_CLOUD
DBMS_CLOUD
SQL*Loader
SQL*Loader
Data Pump
Data Pump
MV2ADB
MV2ADB
DATA FORMAT DATA SOURCE
CSV x x x x x Local file x x x x x x
Text x x Object storage x x x x
Text - advanced x Amazon S3 x
XML x Amazon S3 compatible source x
JSON x Azure BLOB x
Delimited x x x
Data Pump x x x
Excel x
ORC x
Parquet x
Avro x x

Options | Even More Options
Insert via REST API
SQL Developer Database Copy
CTAS/IAS over database link

Options | Big Data
Only one option: Data Pump
Follow recommendations and best practices

Options | Zero Downtime
Only option: Oracle GoldenGate
ADB support from Oracle GoldenGate 12.3.0.1.2

GoldenGate | Explained
Statements
replayed
Initial copy
Statements
captured
INSERT INTO ...
UPDATE ... INSERT INTO ...

UPDATE ...
DELETE FROM ... DELETE FROM ...

GoldenGate | Cloud Native
New Cloud Native service: OCI GoldenGate
Runs GoldenGate 21c, managed by Oracle
Auto-scale: true cloud elasticity, low operations cost
Very attractive pricing
Supports:
• Oracle Database 11.2.0.4 and higher
• DBCS, ExaCS, ATP, ADW
Pro Tip: Watch a short intro on YouTube

GoldenGate | Cloud Native
Watch on YouTube

GoldenGate | Certification Matrix
GoldenGate version: 19.1.0.0.200714

OS Update
Processor Type OS Version
Level 11.2.0.4 12.1.0.1 12.1.0.2 12.2 18 19
HP-UX Itanium (64-bit) 11,31 1409+ x x x x x x
IBM AIX on POWER Systems (64-bit) 7,1 5.SP1+ x x x x x x
IBM AIX on POWER Systems (64-bit) 7,2 2.SP1+ x x x x x
Linux on System Z (64-bit) Red Hat Enterprise Linux 6 32+ x x x x x
Linux on System Z (64-bit) Red Hat Enterprise Linux 7 x x x x x
Linux on System Z (64-bit) SLES 12 3+ x x x x
Linux x86-64 Oracle Linux 6 4+ x x x x x
Linux x86-64 Oracle Linux 7 x x x x x
Linux x86-64 Red Hat Enterprise Linux 6 4+ x x x x x
Linux x86-64 Red Hat Enterprise Linux 7 x x x x x
Microsoft Windows x64 (64-bit) 2012 x x x x x
Microsoft Windows x64 (64-bit) 2016 x x x
Microsoft Windows x64 (64-bit) 2019 x
Microsoft Windows x64 (64-bit) 2012 R2 x x x x x
Oracle Solaris on SPARC (64-bit) 11,3 SRU 3.31+ x x x x x x
Oracle Solaris on SPARC (64-bit) 11,4 SRU 2+ x x x x x x
Oracle Solaris on x86-64 (64-bit) 11,3 SRU 3.31+ x x x x x x
Oracle Solaris on x86-64 (64-bit) 11,4 SRU 2+ x x x x x x
Oracle GoldenGate 19.1 certification matrix

GoldenGate | Marketplace Image
https://cloudmarketplace.oracle.com
Watch on YouTube

Autonomous JSON Database
The New Kid on the Block

”
Oracle Autonomous JSON Database is a cloud document
database service that makes it simple to develop JSON-
centric applications. It features simple document APIs,
serverless scaling, high performance ACID transactions,
comprehensive security, and low pay-per-use pricing.

Autonomous JSON Database | Provision
Watch on YouTube

Options | SQLCl
Uses SODA
Natively available in SQLCl

Options | SQLCl
Watch on YouTube

Options | REST API
Fast and easy
Via ORDS
ORDS already configured in ADB
Jeff Smith blog post

Options | REST API
Watch on YouTube

Autonomous JSON Database | Further Reading
Introducing Oracle Autonomous JSON Database for

application developers
Julian Dontcheff: How is Oracle Autonomous JSON Database

different from Oracle ATP and MongoDB?

Photo by Jakob Boman on Unsplash
Deep Dive
Recommendations | Schema Advisor
Oracle Autonomous Database Schema Advisor (Doc ID 2462677.1)
@install_adb_advisor.sql ADB_ADVISOR ADB_ADVISOR
CONNECT ADB_ADVISOR/ADB_ADVISOR
SET SERVEROUTPUT ON FORMAT WRAPPED
SET LINES 3000
EXEC ADB_ADVISOR.REPORT(schemas=>'METAL',adb_type=>'ADW');
Pro Tip:
Other migration targets are ATD, ATPD
and ADWD

Recommendations | Cloud Premigration Advisor Tool (CPAT)
Cloud Premigration Advisor Tool (CPAT) Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1)
• Successor to the Schema Advisor
------------------------------------------------------------------------------------------
==========================================================================================
Premigration Advisor
Cloud Premigration Advisor Tool
Check(CPAT)
Details List
Report
------------------------------------------------------------------------------------------
==========================================================================================
Report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Details
Check Name:
~~~~~~~~~~~~~~ has_user_defined_objects_in_sys
CPAT Check Result:Version:
Application BLOCKER 22.1.4-1
CPAT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Checks Version: 22.1.4-1
Description:
Target Cloud Type: User-defined
ATPD objects in SYS and SYSTEM schemas will not migrate.
Failure
Migration Impact: Any applications
Method(s): DATAPUMP relying on user-defined objects in SYS and SYSTEM will fail.
Action:
Report Generated On: User-defined objects were detected in SYS/SYSTEM schemas. Consider moving them
2021-04-05T17:48:03Z
Report Result: out prior to the migration.
BLOCKER
Database Relevant
Details Objects:
OWNER OBJECT_NAME
~~~~~~~~~~~~~~~~ SUBOBJECT_NAME OBJECT_TYPE
Source ------ -------------------------
Database Container Name: ORCLPDB1-------------- ------------------
Source SYS MY_VERIFICATION_FUNCTION
Database Host Name: 4bea9faa8231 FUNCTION
Source SYSTEM IDXSIZE_UQ2
Instance Name: ORCLCDB INDEX
SYSTEM IDXSIZE_UQ3 INDEX
SYSTEM ISEQ$$_83287 SEQUENCE

Recommendations | Cloud Premigration Advisor Tool (CPAT)
Does not install anything in your database

• Supports read only databases (e.g. physical standby)
JSON or Text output

Recommendations | Multipart Uploads
Recommended for files larger than 100 MB
Use OCI CLI
oci os object put \

--namespace ... -bn ... --file ... --name ... \
--part-size 1024 \
--parallel-upload-count 4
Max part size is 50 GB
OCI CLI installation guide

Recommendations | Bulk Uploads
Recommended for many files

Use OCI CLI
oci os object bulk-upload \

-ns ... -bn ... --src-dir ...
Does multipart and parallel uploads automatically
Optionally,
• Finetune uploads using --parallel-upload-count and --part-size
• Prefix all file names with --object-prefix
• Include or exclude files selectively using patterns and --include and --exclude

Recommendations | Checksum
• Avoid in-flight corruption

[oracle@hol]$ md5sum metal*.dmp SQL> SELECT object_name, checksum
FROM DBMS_CLOUD.LIST_OBJECTS(
'<credential_name>',
'<location_uri>');
5edf66ed92086b4f69580fc27b75f662 metal_01.dmp metal_01.dmp 5edf66ed92086b4f69580fc27b75f662

59eb25ff2a0f648c051a9212e0861979 metal_02.dmp metal_02.dmp 59eb25ff2a0f648c051a9212e0861979
29951a56abe074d9151c27728d88e9eb metal_03.dmp metal_03.dmp 29951a56abe074d9151c27728d88e9eb
c8860e7a71e74f8013068240b598c116 metal_04.dmp metal_04.dmp c8860e7a71e74f8013068240b598c116
0d05d258e4b501c657cd9490b7e48715 metal_05.dmp metal_05.dmp 0d05d258e4b501c657cd9490b7e48715
1e367394a31e2ce45d2aeb6a3d4f9507 metal_06.dmp metal_06.dmp 1e367394a31e2ce45d2aeb6a3d4f9507
9c276aa580c0e57c0829f274d04d15de metal_07.dmp metal_07.dmp 9c276aa580c0e57c0829f274d04d15de
0d560d0ce57c47425424e17604d8ec49 metal_08.dmp metal_08.dmp 0d560d0ce57c47425424e17604d8ec49
• Windows: Get-FileHash *.dmp -Algorithm MD5
• Manifests as ORA-31693 ORA-29913 ORA-29104

Recommendations | ADB Resources
When loading database, scale up on CPUs
I/O scales linear with CPU
Connect to high service
Loading from the object store

typically faster than database link

Recommendations | ADB Resources
Allocate adequate storage before import
ALTER DATABASE DATAFILE 2556 RESIZE 100G;
Tablespaces are bigfile - 32 TB limit
For ADB dedicated:

• use allow_rowid_column_type=true to allow tables with rowid columns

Recommendations | Data Pump
Don't use SYS AS SYSDBA
Applies to export and import

$ more export.par
DIRECTORY=my_data_pump_dir
DUMPFILE=dumpfile%U.dmp
LOGFILE=logfile.log
Always use a parameter file SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
FLASHBACK_TIME=SYSTIMESTAMP
PARALLEL=4
FILESIZE=5G
TRANSFORM=OMIT_ENCRYPTION_CLAUSE:Y
$ expdp parfile=export.par

$ more export.par
LOGFILE=logfile.log
Always export to multiple files SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
PARALLEL=4
FILESIZE=5G
DUMPFILE applies to export and import $ expdp parfile=export.par

FILESIZE applies only to export
More than 99 files, use DUMPFILE=dumpfile%L.dmp

$ more export.par
LOGFILE=logfile.log
Always use schema mode SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
PARALLEL=4
FILESIZE=5G
Applies to export

$ more export.par
LOGFILE=logfile.log
Always exclude statistics SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
PARALLEL=4
FILESIZE=5G

$ more export.par
LOGFILE=logfile.log
Always include diagnostics SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRICS=YES
PARALLEL=4
FILESIZE=5G
Applies to export and import $ expdp parfile=export.par

LOGTIME available from 12.1, METRICS from 11.2.0.4

Recommendations | Data Pump - Diagnostics
No diagnostics
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "METAL"."ALBUMS" 988.8 KB 28069 rows
. . imported "METAL"."BANDS" 3.444 MB 37723 rows
. . imported "METAL"."REVIEWS" 66.47 MB 21510 rows
All diagnostics
16-OCT-20 17:26:57.158: Processing object type SCHEMA_EXPORT/TABLE/TABLE
16-OCT-20 17:26:58.262: Startup took 1 seconds
16-OCT-20 17:26:58.264: Startup took 1 seconds
16-OCT-20 17:26:59.082: Completed 3 TABLE objects in 1 seconds
16-OCT-20 17:26:59.082: Completed by worker 1 1 TABLE objects in 1 seconds
16-OCT-20 17:26:59.313: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
16-OCT-20 17:27:01.943: . . imported "METAL"."ALBUMS" 988.8 KB 28069 rows in 2 seconds using external_table
16-OCT-20 17:27:03.778: . . imported "METAL"."BANDS" 3.444 MB 37723 rows in 2 seconds using external_table
16-OCT-20 17:27:12.644: . . imported "METAL"."REVIEWS" 66.47 MB 21510 rows in 13 seconds using external_table

$ more export.par
LOGFILE=logfile.log
Always make consistent exports SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRIC=YES
PARALLEL=4
FILESIZE=5G
Applies to export only

$ more export.par
LOGFILE=logfile.log
Always use parallel SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
Enterprise Edition only METRIC=YES
PARALLEL=4
FILESIZE=5G
OCI: Number of OCPUs $ expdp parfile=export.par

On-premises: Number physical cores x 2

$ more export.par
LOGFILE=logfile.log
Always remove column encryption SCHEMAS=HR
EXCLUDE=STATISTICS
LOGTIME=ALL
METRIC=YES
PARALLEL=4
FILESIZE=5G
Applies to import only

$ more export.par
LOGFILE=logfile.log
SCHEMAS=HR
Consider using compression EXCLUDE=STATISTICS
LOGTIME=ALL
Advanced Compression Option license required METRIC=YES
PARALLEL=4
FILESIZE=5G
COMPRESSION=ALL
COMPRESSION_ALGORITHM=MEDIUM
Applies to export only $ expdp parfile=export.par

Algorithms: BASIC, LOW, MEDIUM, HIGH

Recommendations | Data Pump - Compression Comparison
12.2 EBS Database - export
Example 1 Example 2
File Size MB Ratio Time File Size MB Ratio Time
NONE 5500 1 4m 54s NONE 5800 1 2m 33s
ALL BASIC 622 8,9 4m 58s ALL BASIC 705 8,2 3m 3s
ALL LOW 702 7,8 5m 24s ALL LOW 870 6,6 8m 11s
ALL MEDIUM 567 9,7 4m 55s ALL MEDIUM 701 8,2 3m 1s
ALL HIGH 417 13,2 5m 13s ALL HIGH 509 11,3 12m 16s

Tweaks | Data Pump
SQL> CREATE VIEW v1 AS

SELECT *
FROM t1
Use VIEWS_AS_TABLES to export WHERE origin='INTERNAL'
subset of data
$ expdp tables=v1
Applies to export only

Tweaks | Data Pump
$ impdp dumpfile=http://...../exp01.dmp,
http://...../exp02.dmp,
Use wildcards for URL based http://...../expnn.dmp
file names
$ impdp dumpfile=http://...../exp%u.dmp

ADB Compliance | Data Pump
TRANSFORM=DWCS_CVT_IOTS:Y
TRANSFORM=CONSTRAINT_USE_DEFAULT_INDEX:Y
TRANSFORM=SEGMENT_ATTRIBUTES:N
REMAP_TABLESPACE=%:DATA
Transform IOTs to tables EXCLUDE=INDEX,CLUSTER,INDEXTYPE,
aaaaaaaaMATERIALIZED_VIEW,
aaaaaaaaMATERIALIZED_VIEW_LOG,
aaaaaaaaMATERIALIZED_ZONEMAP,DB_LINK
DATA_OPTIONS=GROUP_PARTITION_TABLE_DATA
PARTITION_OPTIONS=MERGE

Enforce proper naming standard by REMAP_TABLESPACE=%:DATA
using constraint name for PK and FK EXCLUDE=INDEX,CLUSTER,INDEXTYPE,
indexes aaaaaaaaMATERIALIZED_VIEW_LOG,

Remove segment customization EXCLUDE=INDEX,CLUSTER,INDEXTYPE,

Remap any tablespace to REMAP_TABLESPACE=%:DATA
EXCLUDE=INDEX,CLUSTER,INDEXTYPE,
DATA tablespace aaaaaaaaMATERIALIZED_VIEW,

For expdp:
MATERIALIZED_VIEW,
MATERIALIZED_VIEW_LOG,
MATERIALIZED_ZONEMAP,DB_LINK
Follow ADW best practice and exclude

these object types For impdp:
MATERIALIZED_VIEW,
MATERIALIZED_VIEW_LOG,
MATERIALIZED_ZONEMAP,DB_LINK

For expdp:
EXCLUDE=CLUSTER,DB_LINK
Follow ATP best practice and exclude

these object types For impdp:
EXCLUDE=CLUSTER,DB_LINK

Only SecureFiles LOBs are allowed

SQL> select value from v$parameter where name ='db_securefile';
ALWAYS
No need to transform
TRANSFORM=LOB_STORAGE:SECUREFILE
Applies to import

Data Pump | Troubleshooting
ORA-39001: invalid argument value

ORA-39000: bad dump file specification
ORA-31640: unable to open dump file for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Validate Object Storage URI and Credentials

MOS Doc ID 2468298.1
Photo by Randy Fath on Unsplash

Photo by Dave
JakobHoefler
BomanononUnsplash
Unsplash
Almost there...
Autonomous Database | SQLcl
Watch on YouTube
Pro Tip: It is a full client, so you
Kris Rice: SQLcl and OCI Cloud Shell can use impdp and expdp as well

Autonomous Database | Quickstart Workshop
Autonomous Database Quickstart Workshop

Database Cloud Service
Support Timeline | Cloud 11.2.0.4
2009
2020
2026
2024
2025
2022
2023
2027
2010
2014
2016
2018
2019
2015
2017
2013
2021
2012
2011
MARKET
Oracle 11.2 Waived EXTENDED EXTENDED DRIVEN
Oracle 12.1 EXTENDED
Oracle 12.2.0.1 LIMITED
Oracle 18
(12.2.0.2)
Oracle 19 EXTENDED
(12.2.0.3)
Premier Support Waived Extended Support Paid Extended Support Market Driven Support Limited Error Correction

Support Timeline | Cloud 11.2.0.4
2020
31 December 2020 Extended Support ends

31 March 2021 Extended Support ends Only applies to:
2021
ExaCC (Gen 1 + 2)
DBCS (OCI-C + OCI)
ExaCS (OCI)
2022
31 December 2022 Market Driven Support ends

2023
Release Schedule of Current Database Releases (Doc ID 742060.1)

Database Cloud Service | Virtual Machines
Entry-level, provision with GI or LVM (fast-provision)
Restrictions:
• Only one CDB - the pre-created one
• PDB management is up to the user
• Can't upgrade OS or GI or database
• Can't install another Oracle Home
• COMPATIBLE is always default - unless
• Drop and create database not supported (drop and restore is)

Database Cloud Service | Bare Metal
Mid-level, provision with GI
Restrictions:
• As many CDBs as you want
• Can't upgrade OS or GI
• DATA disk group, max. 16 TB
• Only one database edition
• Only one database pr. Oracle Home

Database Cloud Service | Exadata
World's best database machine, provision with GI
Restrictions:
• As many CDBs as you want

Database Cloud Service | Database Images
For VM and BM
Customize your database software:

• Oracle Home
• Release Update
• One-offs
Watch on YouTube

Migration | Scaling
Typically, during migration, you need:
CPU I/O Network

throughput throughput

Migration | Scaling Virtual Machines
Change shape up and down

Shape changes offline
X7 offers from 2 to 24 OCPUs
Scales online, but up only with amount of total storage

Allocated in quota between DATA and RECO
Storage is network attached - needs network bandwidth to read/write
Scales with number of OCPUs

Migration | Scaling Virtual Machines
Blog post
Network throughput I/O throughput
Shape Throughput MB/s Storage (GB) Throughput MB/s

VM.Standard.2.1 128 256 120
VM.Standard.2.2 256 1024 480
VM.Standard.2.4 512 2048 960
VM.Standard.2.8 1024 4096 1280
VM.Standard.2.16 2048 10240 1600
VM.Standard.2.24 3200 20480 3200

Migration | Scaling Bare Metal
Scales up and down

Scales online
Locally attached NVMe disks
25 Gbps network interface

Theoretically 3200 MB/s

Migration | Scaling Exadata
Scales up and down

Scales online
Exadata storage system
25 Gbps network interface

Theoretically 3200 MB/s

Migration | Transfer Speed
Use multipart and bulk uploads

Migration | Multitenant
Oracle Database 19c: Max PDBs 3

DBCS EE-HP or EE-EP or ExaCS 4096
Keep within limits - MAX_PDBS:
SQL> ALTER SYSTEM SET MAX_PDBS=3 SCOPE=BOTH;
DBCS tooling don't care about PDBs - create and drop as you want

Migration | TDE
TDE Tablespace Encryption is a must-have in the cloud
Databases in OCI are allowed to use Isolated Keystore mode

• But it is currently not supported by tooling
Learn encryption:
Basic introduction AskTOM Office Hours

MIGRATION
methods
DATA TRANS- DATA PUMP

ZDM GUARD PORTABLE GOLDENGATE

Introduction | Birds-Eye
Build Sync Switch

Introduction | Key Features
• Simple
• Reliable
• Scalable
• Secure
• Proven
• Free

Prerequisites And Features | Location
Location Source database can be located

Release • On-premises
Platform • Oracle Cloud Infrastructure Classic (OCI-C)
Edition • Oracle Cloud Infrastructure (OCI)
Architecture • Cross-region / cross-location migration
Encryption • System migration

Prerequisites And Features | Release
Location Source databases

Release • 11.2.0.4
Platform • And anything newer
Edition
Architecture
Encryption Target databases
• Physical migrations: Database release must be the same
• Logical migrations: Database release can be the same or higher
Pro Tip: It is possible to migrate to a higher

patch level. If needed, ZDM invokes datapatch

Prerequisites And Features | Platform
Location Supported source platform

Release • Linux
Platform
Edition Supported target platforms
Architecture • Autonomous (dedicated and shared)
Encryption • ATP
• ADW
• DB Systems (VM, BM and Exadata)
• Exadata Cloud at Customer
• Exadata (on-prem)

Prerequisites And Features | Edition
Location Supported editions

Release • Enterprise Edition
Platform • Standard Edition
Edition
Architecture Standard Edition restriction:
Encryption
For zero downtime approach select logical migration
No migration between editions

Pro Tip: True Zero Downtime requires
an MAA compliant application

Prerequisites And Features | Architecture
Location Any architecture supported (non-CDB and CDB)

Release • Optionally, convert to PDB
Platform • For CDBs, all PDBs included
Edition
Architecture
Encryption

Location Any type (single instance, RAC One Node, RAC)

Release • Single instance can be migrated to RAC
Platform • RAC One Node will be migrated to RAC
Edition • RAC will be migrated to RAC
Architecture
Encryption

Location Any type (single instance, RAC One Node, RAC)

Release • Single instance can be migrated to RAC
Platform • RAC One Node will be migrated to RAC
Edition • RAC will be migrated to RAC
Architecture
Encryption

Prerequisites And Features | Encryption
Location Source database

Release • Unencrypted
Platform • Encrypted
Edition
Architecture Target database
Encryption • Is always encrypted
• Unencrypted databases gets encrypted on-the-fly
Network connectivity
• Always and transparently encrypted

Migration | Options
PHYSICAL LOGICAL
Online: Data Guard + switchover Online: Data Pump + GoldenGate
Offline: Backup + restore Offline: Data Pump
Via dump file or database link
Standard Edition - offline only Standard Edition - any approach

Physical Migration | Overview
Users are connected to source database
Source Database
Provision target database in OCI
Source Database Target Database

Download and install ZDM
ZDM host

ZDM connects to source and target database
ZDM host
SSH

Back up source database to object storage
ZDM host
Object Storage

Instantiate standby database from backup
ZDM host
Object Storage

Synchronize via redo apply
ZDM host
Object Storage
SQLNet
At your will, switchover sessions
ZDM host
Object Storage
SQLNet
Physical Migration | Demo
Watch on YouTube

Physical Migration | Testing
Watch on YouTube

Migration | Options
PHYSICAL LOGICAL
Online: Data Guard + switchover Online: Data Pump + GoldenGate
Offline: Backup + restore Offline: Data Pump
Via dump file or database link
Standard Edition - offline only Standard Edition - any approach

Logical Migration | Overview
Users are connected to source database
Source Database
Provision target database in OCI

Download and install ZDM
ZDM host

ZDM connects to source and target database
ZDM host
SSH

Provision GoldenGate and capture on source
ZDM host
GoldenGate
insert into ...

delete from ...
update ...
insert into ...
Data Pump export to object storage
GoldenGate
ZDM host
Object Storage
Source Database
Target Database
Data Pump import from object storage
GoldenGate
ZDM host
Object Storage
Source Database
Target Database
Configure apply on target
GoldenGate
ZDM host
Object Storage
Source Database
Target Database
At your will, switchover sessions
GoldenGate
ZDM host
Object Storage
Source Database
Target Database
Wrapping Up | Blog Posts
A walthrough with all the details
- includes one on ExaCS

Wrapping Up | YouTube
YouTube Playlist

Wrapping Up | Further Information
Oracle Zero Downtime Migration Product Page
Oracle Zero Downtime Migration Documentation
Oracle Zero Downtime Migration Release Notes
Oracle Zero Downtime Migration Whitepaper
MAA Practices for Cloud Migration Using ZDM (Doc ID

2562063.1)
Hybrid Data Guard to Oracle Cloud Infrastructure

MIGRATION
methods


Heterogeneous Data Guard
Photo by Adam Muise on Unsplash
Copyright © 2021 Oracle and/or its affiliates.

Little Endian | Heterogeneous Data Guard
Redo
apply
Primary
database
Standby
database

Little Endian | Linux Standby
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (Doc ID 413484.1)

Little Endian | Linux Standby
SQL> SELECT platform_name, endian_format

FROM v$transportable_platform
WHERE endian_format='Little';
PLATFORM_NAME ENDIAN_FORMAT
____________________________________ ________________
Apple Mac OS (x86-64) Little
HP IA Open VMS Little
HP Open VMS Little
HP Tru64 UNIX Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Linux x86 64-bit Little
Microsoft Windows IA (32-bit) Little
Microsoft Windows IA (64-bit) Little
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
Solaris Operating System (x86-64) Little

Little Endian | Hybrid Data Guard
Hybrid Data Guard to Exadata Cloud Services

Production Database on Premises and Disaster Recovery
with Oracle Cloud Infrastructure Exadata Cloud Service

MIGRATION
methods


Transport
Basics
Photo by Florian Klauer on Unsplash

Endianness | The Basics
Big-endian Little-endian
Source: https://en.wikipedia.org/wiki/Endianness

Endianness Migration | Cloud is Linux
SQL> SELECT platform_name, endian_format

FROM v$transportable_platform
WHERE endian_format!='Little';
PLATFORM_NAME ENDIAN_FORMAT
____________________________________ ________________
AIX-Based Systems (64-bit) Big
Apple Mac OS Big
HP-UX (64-bit) Big
HP-UX IA (64-bit) Big
IBM Power Based Linux Big
IBM zSeries Based Linux Big
Linux OS (S64) Big
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big

Endianness Migration | Transport
Big Endianness platforms

• HP-UX (64-bit)
• HP-UX IA (64-bit)
• AIX-Based Systems (64-bit)
• IBM zSeries Based Linux
• IBM Power Based Linux
• Solaris[tm] OE (32-bit)
Convert
• Solaris[tm] OE (64-bit)
Pro Tip: You can use Transportable

Tablespace even for little Endian migrations

Endianness Migration | Convert
RMAN DBMS_FILE_TRANSFER
Out-of-place conversion In-flight conversion
2 x disk space needed 1 x disk space needed
Supported in newest version of Perl scripts Not supported in Perl scripts version 4

Endianness Migration | Transportable Tablespace Concept
To move data we need two things:
META DATA DATA
How is the following defined: The actual rows

User/schema
Table
Columns
Indexes
Triggers
Grants
PL/SQL
...

To move data we need two things:
META DATA DATA

CREATE USER u1 ... INSERT INTO t1 (...) VALUES (...)
CREATE TABLE t1 ( ... INSERT INTO t1 (...) VALUES (...)
CREATE INDEX i1 ON t1.c1 ... INSERT INTO t1 (...) VALUES (...)
CREATE TRIGGER trig1 ... INSERT INTO t1 (...) VALUES (...)
GRANT SELECT ON t1 ...
CREATE PROCEDURE p1 ...
Stored in Stored in
SYSTEM tablespace user tablespaces

SYSTEM DATA
Export with Copy

Data Pump data files
SYSTEM DATA
Tied to database version Independent of database version

Works only in same database version Works in same or newer database version
Works only in same database architecture Works in same or different database architecture
Data Pump works across:
• Database version
• Database architecture (non-CDB / CDB)

Transportable tablespaces works:
• To the same or newer database version
• For non-CDB to PDB conversion (and vice-versa)

Transport
Methods
Photo by Fahrul Azmi on Unsplash

Endianness Migration | Full Transportable Export Import (FTEX)
Data Pump
Full Transportable
Export/Import
New, empty
Tablespace
Meta plug-in
data transfer 19c PDB
Source database (users, tables, indexes, triggers, PL/SQL ...)
12.1.0.2
SYSTEM SYSTEM
Copy data files

RMAN convert
Tablespaces
DATA DATA data file
Set read-only
Different
endian format

Endianness Migration | Full Transportable Export Import (FTEX)
Watch on YouTube

Endianness Migration | FTEX plus Incremental Backups
Data Pump
Full Transportable
Export/Import
New, empty
Tablespace
Meta plug-in
data transfer 19c PDB
Source database (users, tables, indexes, triggers, PL/SQL ...)
12.1.0.2
SYSTEM SYSTEM
Final
Level
Level10incremental
imagefile backup
backup
Tablespaces
DATA DATA
Convert
Different data file
Set read-only on restore
endian format

Endianness Migration | FTEX plus Incremental Backups
Watch on YouTube

Data Guard | Transportable
Data Pump
Full Transportable
Export/Import
Source Target
Primary
Tablespace plug-in
Redo
via redo apply
Tablespaces Target
Restore Standby

Data Guard | Transportable
Step by Step Process of Migrating non-CDBs and PDBs Using ASM for File Storage (Doc ID 1576755.1)

Transport
Checklist
Photo by Sebastian Herrmann on Unsplash

Transportable | Starter Checklist
Database Creation Target database requirements

Backup / Recovery • COMPATIBLE must be the same or higher
TDE
PERL Scripts Target database requirements with workarounds
• Identical character set
• Identical national character set
• Identical time zone (only with TIMESTAMP WITH LOCAL TIME ZONE)
• Identical time zone file version (only with TIMESTAMP WITH TIME ZONE)

Database Creation Blog post on how to create a database in OCI with custom
Backup / Recovery COMPATIBLE setting
TDE
PERL Scripts

Database Creation To determine character set:

Backup / Recovery SQL> select * from nls_database_parameters;
TDE
PERL Scripts Convert source database to Unicode with DMU
A few Character set exceptions

Database Creation OCI databases are AL32UTF8

Backup / Recovery • Change in Advanced Options
TDE
PERL Scripts Database 12.2 and higher
• PDB can use different character set
Recommendation
• Keep production CDB on AL32UTF8
• Provision temporary CDB with desired character set
• Create new empty PDB in temporary CDB
• Clone custom PDB to production CDB

Database Creation OCI DB Systems are in UTC time zone

Backup / Recovery • Change in Advanced Options
TDE
PERL Scripts Sets the OS time zone, which affects:
• SYSDATE
• SYSTIMESTAMP
How to change the Time Zone in Oracle Database Hosted in OCI

with an Example (Doc ID 2459830.1)

Database Creation To determine database time zone:

Backup / Recovery SQL> select dbtimezone from v$instance;
TDE
PERL Scripts If source and target database time zone doesn't match
• Tables with TSLTZ are skipped
• Import using Data Pump afterwards
Database time zone is only relevant for columns of TIMESTAMP

WITH LOCAL TIME ZONE
Documentation

Database Creation OCI database time zone defaults to UTC

Backup / Recovery
TDE Change for CDB:
PERL Scripts SQL> alter database cdb1 set time_zone = '+02:00';
PDBs can have different DB Time Zone:

SQL> alter pluggable database pdb1 set time_zone = '+04:00';
Only relevant for

• TSLTZ
• CURRENT_DATE
• CURRENT_TIMESTAMP
• LOCALTIMESTAMP

Database Creation To determine database time zone file version:

Backup / Recovery SQL> select * from v$timezone_file;
TDE
PERL Scripts If source and target database time zone file version doesn't match
• Tables with TSTZ are skipped
• Import using Data Pump afterwards
Database time zone file version is only relevant for columns of

TIMESTAMP WITH TIME ZONE
How to create a database with a non-default time zone file version
Documentation

Database Creation Enable Block Change Tracking on source for incremental backups
Backup / Recovery
SQL> SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
TDE SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
PERL Scripts
• Conversion on destination is usually faster than on source
• PERL scripts will do the conversion
• Requires
• Enterprise Edition (on-prem)
• Enterprise Edition Extreme Performance (DBCS)
• Exadata

Database Creation TDE Encryption

Backup / Recovery • Not supported
TDE • Only for same-Endianness migration
PERL Scripts

Database Creation RMAN Incremental Backups

Backup / Recovery • MOS Note: 2471245.1
TDE V4 PERL Scripts to reduce Transportable Tablespace Downtime using
Cross Platform Incremental Backup
PERL Scripts
• Source: 10.2.0.3 or newer
• Target: 11.2.0.4 or newer

Transportable | General Best Practices
• Practice, practice, practice

• Start on small database
• Prove it works on production-size database
• Automate
• To ensure consistency and avoid human error
• Save all logs and output

• Data Pump, RMAN
• Clean-up procedure
• In case of failure and rollback
• To repeat tests
• Ensure that source database gets offlined afterwards

Transport
Advanced
Photo by Alexander Andrews on Unsplash

FTEX | Recommendations
Exclude SYS (at least in a PDB)

EXCLUDE=SYS_USER
Create TEMP tablespaces in advance, and exclude TEMP tablespaces

EXCLUDE=TABLESPACE:"IN('TEMP')"
Exclude statistics
EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS
Exclude Spatial users (removed in 19c)

EXCLUDE=SCHEMA:"IN('SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR')"

FTEX | Known Issues
Ignorable error: Package is removed in 12.2

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:ORA-04042: procedure, function,
package, or package body does not exist
Failing sql is:

BEGIN
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_DEFER_SYS TO "DBA"');COMMIT; END;
ORA-39083 And ORA-04042 Errors On DBMS_DEFER_SYS When Importing Into 12.2 Database (Doc ID 2335846.1)

FTEX | Known Issues
Ignorable error: Multimedia desupported in 19c, but code is still there

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
ORA-39342: Internal error - failed to import internal objects tagged with ORDIM due to ORA-00955:
name is already used by an existing object.

Transportable | Important MOS Notes
• Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Doc ID 1166564.1)
• Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable
(Doc ID 1454872.1)
• MOS Note: 2471245.1 - V4 PERL Scripts to reduce Transportable Tablespace Downtime using Cross Platform
Incremental Backup
• MOS Note: 2460552.1 – Cross Platform Database Migration using ZDLRA
• MOS Note:1389592.1 – 11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental
Backup
• MOS Note: 2005729.1 – 12C – Reduce Transportable Tablespace Downtime using Cross Platform Incremental
Backup

MIGRATION
methods


Data Pump
Use information from "Autonomous Database Deep Dive"
Except "ADB Compliance"

Data Pump | SecureFiles
"
SecureFiles is the default storage mechanism for LOBs starting with Oracle Database 12c, and Oracle strongly recommends
SecureFiles for storing and managing LOBs, rather then BasicFiles. BasicFiles will be deprecated in a future release.
Database SecureFiles and Large Objects Developer's Guide
Always transform LOBs to SecureFiles LOBs

$ impdp ... TRANSFORM=LOB_STORAGE:SECUREFILE

Data Pump | SecureFiles
Importing as BasicFiles
10-OCT-20 21:43:21.848: W-3 . . imported "SCHEMA"."TABLE" 31.83 GB 681025 rows in 804 seconds using direct_path
Importing as SecureFiles
15-OCT-20 18:16:48.663: W-13 . . imported "SCHEMA"."TABLES" 31.83 GB 681025 rows in 261 seconds using external_table

Data Pump | ZDM

Data Pump | MV2OCI
"One button approach"
Uses Data Pump (schema mode)
Documentation: Doc ID 2514026.1
Support import over DB link (--netlink)
Runs on Linux / Solaris

Pro Tip: Check log file to find out
how MV2OCI uses Data Pump

Data Pump | MV2OCI
Dump files are moved directly to target host

- not staged on Object Store
Requires SSH and SQL*Net connectivity
Java executable in PATH
Pro Tip: For troubleshooting check

/opt/mv2oci/out/log

Data Pump | MV2OCI
Watch on YouTube

Data Pump | MV2OCI
Enable Data Pump metrics (METRICS=Y and LOGTIME=ALL)

$ mv2oci.bin auto --conf mv2oci_metal.cfg --nosudo -dpdebug
Exclude statistics
EXTRA_EXPDP=EXCLUDE=STATISTICS
Enable Data Pump compression (license required)

EXTRA_EXPDP=COMPRESSION=ALL COMPRESSION_ALGORITHM=MEDIUM
Transform LOBs to SecureFiles

EXTRA_IMPDP=TRANSFORM=LOB_STORAGE:SECUREFILE

Data Pump | ZDM
ZDM automatically sets recommended defaults for Data Pump

• METRICS=Y
• LOGTIME=ALL
• COMPRESSION=ALL
• ENCRYPTION=ALL
Oracle Data Pump Defaults for Zero Downtime Migration
Note: use of compression and encryption by ZDM does not require additional licenses for the Advanced
Security Option or Advanced Compression Option

GoldenGate
Use ZDM: Logical Online Migration
Alternatively: Use OCI GoldenGate

Photo by Carolina Pimenta on Unsplash
Finally ...
It is time to wrap up

Migration | Keep It Simple
Simplicity Downtime
Migration | More Information
Watch the recordings
Get the slides

NEW WEBINAR
PERFORMANCE TESTING
Using the Oracle Cloud for Upgrades and Migrations
Photo by NOAA on Unsplash
Coming on May 19, 2021 - SIGN UP

Thank you!

US6 - Move - To - The - Cloud

Uploaded by

Copyright:

Available Formats

US6 - Move - To - The - Cloud

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

US6 - Move - To - The - Cloud

Uploaded by

Copyright:

Available Formats

Move to the Cloud

Not only for Techies ...

Roy Swonger, Mike Dietrich & Daniel Overby Hansen

1 Copyright © 2021, Oracle and/or its affiliates

Distinguished Product Manager

Senior Principal Product Manager

How to Move Your Data into an

6 Copyright © 2021, Oracle and/or its affiliates

Shared A simple and elastic choice. Oracle autonomously

Dedicated A private cloud in public cloud choice. A completely

7 Copyright © 2021, Oracle and/or its affiliates

Restrictions apply • Parameters

Pro tip: Cloud services evolve rapidly, check the

8 Copyright © 2021, Oracle and/or its affiliates

• Quick start showcase

9 Copyright © 2021, Oracle and/or its affiliates

11 Copyright © 2021, Oracle and/or its affiliates

12 Copyright © 2021, Oracle and/or its affiliates

14 Copyright © 2021, Oracle and/or its affiliates

15 Copyright © 2021, Oracle and/or its affiliates

Easily accessible from OCI console

Quick and simple

Jeff Smith blog post

16 Copyright © 2021, Oracle and/or its affiliates

17 Copyright © 2021, Oracle and/or its affiliates

18 Copyright © 2021, Oracle and/or its affiliates

Local installation - Download

19 Copyright © 2021, Oracle and/or its affiliates

20 Copyright © 2021, Oracle and/or its affiliates

21 Copyright © 2021, Oracle and/or its affiliates

Can transform data

22 Copyright © 2021, Oracle and/or its affiliates

23 Copyright © 2021, Oracle and/or its affiliates

24 Copyright © 2021, Oracle and/or its affiliates

Fast unload and load

All or selected data

Best option for big data

Can transform metadata

25 Copyright © 2021, Oracle and/or its affiliates

Network mode does not support

26 Copyright © 2021, Oracle and/or its affiliates

27 Copyright © 2021, Oracle and/or its affiliates

28 Copyright © 2021, Oracle and/or its affiliates

29 Copyright © 2021, Oracle and/or its affiliates

Use SQL Developer

30 Copyright © 2021, Oracle and/or its affiliates

31 Copyright © 2021, Oracle and/or its affiliates

32 Copyright © 2021, Oracle and/or its affiliates

"One button approach"

Uses Data Pump (schema mode)

Support import over DB link (--netlink)

Runs on Linux / Solaris

33 Copyright © 2021, Oracle and/or its affiliates

Enable Data Pump metrics (METRICS=Y and LOGTIME=ALL)

Enable Data Pump compression (license required)

34 Copyright © 2021, Oracle and/or its affiliates

35 Copyright © 2021, Oracle and/or its affiliates

SQL Developer Web

SQL Developer Web