Oracle Database 11g/12c To Amazon Aurora with PostgreSQL ...

Oracle Database 11g/12c To Amazon Aurora with PostgreSQL Compatibility (9.6.x)

Migration Playbook

Version: 1.1, January 2018 Written by: David Yahalom, Nimrod Keinan

1

? 2017, Amazon Web Services, Inc. or its affiliates. All rights reserved. Document written by David Yahalom and Nimrod Keinan.

Notices

This document is provided for informational purposes only. It represents AWS's current product offerings and practices as of the date of issue of this document, which are subject to change without notice. Customers are responsible for making their own independent assessment of the information in this document and any use of AWS's products or services, each of which is provided "as is" without warranty of any kind, whether express or implied. This document does not create any warranties, representations, contractual commitments, conditions or assurances from AWS, its affiliates, suppliers or licensors. The responsibilities and liabilities of AWS to its customers are controlled by AWS agreements, and this document is not part of, nor does it modify, any agreement between AWS and its customers.

2

Introduction

To migrate an Oracle database to Amazon Aurora with PostgreSQL Compatibility, you usually need to perform both automated and manual tasks. The automated tasks involve data migration and schema conversion using the AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT). The manual tasks involve post-migration "touch-ups" for certain database objects that can't be migrated automatically. This whitepaper primarily focuses on the manual aspects of database migration and includes step-by-step instructions that you can adapt for your own requirements. In this document, we focus on how to manually migrate specific Oracle database objects and features to Amazon Aurora with PostgreSQL Compatibility equivalents. We also include a brief overview that explains how to use the AWS Schema Conversion Tool (AWS SCT) for automatic migrations of schema objects. You can use this document as a supplementary guide for your database migrations ? both as a guide to expand your PostgreSQL competency if you come from an Oracle database background and as a reference to help build Oracle-comparable functionality in Amazon Aurora with PostgreSQL compatibility

This document does not yet cover all Oracle Database features and capabilities from a migration perspective. For the first release, we focus on some of the most important features and will continue to expand the Playbook document over time. Not all Oracle features have direct and fully compatible equivalents in PostgreSQL. In these cases, we present our recommendations for the best-possible equivalent features in Amazon Aurora with PostgreSQL compatibility. We also plan to expand this document in the future and add new chapters specifically dedicated to advanced topics such as Oracle security, High Availability and Disaster Recovery, Performance Tuning, and more.

Disclaimer

The various code snippets, commands, guides, best practices, and scripts included in this document should be used for reference only and are provided as-is without warranty. Please be sure to test all of the code, commands, best practices, and scripts outlined in this document in a non-production environment first. Amazon and its affiliates are not responsible for any direct or indirect damage that can occur from the information contained in this document.

3

Automatic Schema Migration Help Section

Link Using the AWS Schema Conversion Tool for automatic schema conversion

SQL & PL/SQL (Manual)

Oracle Feature

Aurora PostgreSQL

Compatibility

Feature

Link Anonymous Block

Do

Yes

Link Execute Immediate

Execute & Prepare

Yes

Link DBMS_RANDOM

random()

Yes*

Link DBMS_OUTPUT

RAISE

Yes

Link Procedures & Functions

Functions

Yes*

Link User Defined Functions

Functions

Yes*

(UDFs)

Link UTL_FILE

N/A

None

Link JSON Document Support

JSON Document Support

Yes*

Link OLAP Functions

Window Functions

Yes

Link PL/SQL Cursors

Cursors

Yes

Link Single Row & Aggregate

Single Row & Aggregate

Yes

Functions

Functions

Link Merge

SQL Merge

Yes

Link Create Table As Select (CTAS) Create Table As Select (CTAS)

Yes

Link Common Table Expression

Common Table Expression

Yes

(CTE)

(CTE)

Link Insert From Select

Insert From Select

Yes

Link Inline Views

Inline Views

Yes

Link DB Hints

Query Planning

Yes*

4

Tables & Indexes (Manual)

Oracle Feature

Aurora PostgreSQL

Feature

Link Index Organized Tables (IOTs) PostgreSQL "Cluster" Tables

Link Common Data Types

Common Data Types

Compatibility

Yes* Yes

Link Table Constraints

Table Constraints

Yes

Link Table Partitioning including: Table Partitioning including:

RANGE, LIST, HASH,

RANGE, LIST

COMPOSITE, Automatic LIST

Link Exchange and Split Partitions N/A

Link Temporary Tables

Temporary Tables

Link Unused Columns

ALTER TABLE DROP COLUMN

Link Virtual Columns

Views and/or Function as a Column

Link User Defined Types (UDTs) User Defined Types (UDTs)

Link Read Only Tables and Table Read Only Roles and/or

Partitions

Triggers

Link Index Types

Index Types

Link B-Tree Indexes

B-Tree Indexes

Link Composite Indexes

Multi-Column Indexes

Link BITMAP Indexes

BRIN Indexes

Link Function-Based Indexes

Expression Indexes

Link Local and Global Partitioned Partitioned Indexes Indexes

Link Identity Columns

Serial Data Type

Link MVCC (Table and Row Locks)

MVCC (Table and Row Locks)

Link Character Sets

Encoding

Link Transaction Model

Transactional Model

Link LOBs and SecureFile LOBs

LOBs

Yes*

None Yes* Yes Yes*

Yes Yes*

Yes* Yes Yes Minimal Yes Yes*

Yes* Yes*

Yes* Yes* Yes*

Database Objects (Manual)

Oracle Feature

Link Materialized Views Link Common Data Types Link Oracle Triggers Link Views Link Sequences

Aurora PostgreSQL Feature

Materialized Views Common Data Types PostgreSQL Trigger Procedure Views Sequences

Compatibility

Yes* Yes Yes* Yes Yes

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download