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

1 1 Oracle Database 11g/12c To Amazon Aurora with PostgreSQL compatibility ( ) Migration Playbook Version: , January 2018 Written by: David Yahalom, Nimrod Keinan 2 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.

2 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. 3 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).

3 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.

4 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.

5 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. 4 Automatic Schema Migration Help Section Link Using the AWS Schema Conversion Tool for automatic schema conversion SQL & PL/SQL (Manual) Oracle Feature Aurora PostgreSQL Feature compatibility 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 (UDFs)

6 Functions Yes* 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 Functions Single Row & Aggregate Functions Yes Link Merge SQL Merge Yes Link Create Table As Select (CTAS) Create Table As Select (CTAS) Yes Link Common Table Expression (CTE) Common Table Expression (CTE) Yes Link Insert From Select Insert From Select Yes Link Inline Views Inline Views Yes Link DB Hints Query Planning Yes* 5 Tables & Indexes (Manual) Oracle Feature Aurora PostgreSQL Feature compatibility Link Index Organized Tables (IOTs) PostgreSQL Cluster Tables Yes* Link Common Data Types Common Data Types Yes Link Table Constraints Table Constraints Yes Link Table Partitioning including: RANGE, LIST, HASH, COMPOSITE, Automatic LIST Table Partitioning including.

7 RANGE, LIST Yes* Link Exchange and Split Partitions N/A None Link Temporary Tables Temporary Tables Yes* Link Unused Columns ALTER TABLE DROP COLUMN Yes Link Virtual Columns Views and/or Function as a Column Yes* Link User Defined Types (UDTs) User Defined Types (UDTs) Yes Link Read Only Tables and Table Partitions Read Only Roles and/or Triggers Yes* Link Index Types Index Types Yes* Link B-Tree Indexes B-Tree Indexes Yes Link Composite Indexes Multi-Column Indexes Yes Link BITMAP Indexes BRIN Indexes Minimal Link Function-Based Indexes Expression Indexes Yes Link Local and Global Partitioned Indexes Partitioned Indexes Yes* Link Identity Columns Serial Data Type Yes* Link MVCC (Table and Row Locks) MVCC (Table and Row Locks)

8 Yes* Link Character Sets Encoding Yes* Link Transaction Model Transactional Model Yes* Link LOBs and SecureFile LOBs LOBs Yes* Database Objects (Manual) Oracle Feature Aurora PostgreSQL Feature compatibility Link Materialized Views Materialized Views Yes* Link Common Data Types Common Data Types Yes Link Oracle Triggers PostgreSQL Trigger Procedure Yes* Link Views Views Yes Link Sequences Sequences Yes 6 Link Database Links PostgreSQL DBLink and FDWrapper Yes* 7 Database Administration (Manual) Oracle Feature Aurora PostgreSQL Feature compatibility Link Recovery Manager (RMAN) Amazon Aurora Snapshots Yes Link Flashback Database Amazon Aurora Snapshots Yes Link 12c Multi-Tenant Architecture.

9 PDBs and CDB Databases Yes* Link Tablespaces and DataFiles Tablespaces Yes* Link Data Pump pg_dump and pg_restore Yes Link Resource Manager Separate Amazon Aurora Clusters Yes Link Database Users Database Roles Yes Link Database Roles Database Roles Yes Link SGA & PGA Memory Memory Buffers Yes Link V$ Views & the Data Dictionary System Catalog Tables, Statistics Collector, Amazon Aurora Performance Insights Yes* Link Log Miner Logging Options Yes Link Instance & Database Parameters (SPFILE) Amazon Aurora Parameter Groups Yes Link Session Parameters Session Parameters Yes Link (error log) Error Log via AWS Management Console Yes Link Automatic and Manual Statistics Collection Automatic and Manual Statistics Collection Yes Link Viewing Execution Plans Viewing Execution Plans Yes 8 Automatic Migration of Oracle Schema Objects Using the AWS Schema Conversion Tool 9 Automatic Schema Migration [Back to TOC] This section provides a step-by-step process for using the AWS Schema Conversion Tool (AWS SCT) to migrate an Oracle Database to an Aurora with PostgreSQL compatibility Database cluster.

10 Amazon SCT can automatically migrate most of the Database objects. While this document primarily covers the best practices, feature-parity aspects of manual Database migrations, and Oracle to Amazon Aurora with PostgreSQL compatibility migration best practices, we recommend using AWS SCT as the first step of the process. AWS SCT is a downloadable Java utility that runs locally on your computer. It connects to the source and target databases, scans the source Database schema objects (tables, views, indexes, procedures, etc.), and converts them to the target Database objects. For more information, see 10 Download AWS SCT and Install JDBC Drivers JDBC drivers are required for Database connectivity to both the source and target databases.