Migrating Your Oracle Database to AWS ... - s3.amazonaws.com

1 February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Migrating Your Oracle Database to AWS with Minimal Downtime Suzanne Strasser, Return Path, Inc. February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO About Me Senior Database administrator at Return Path Worked with Oracle since 1994 Areas of expertise Database performance tuning and monitoring Replication Past presenter: RMOUG Training Days, Collaborate February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO About Return Path Global leader in email intelligence Specialize in analysis of email data Provider of industry-leading email intelligence solutions Ensure that only wanted emails reach the inbox Visit us at February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Agenda Oracle databases in AWS: RDS vs.

2 EC2 migration methods: pros and cons Case study: replicating Oracle Database to RDS Configuring continuous replication Validation and testing Switchover steps February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Oracle AWS Databases: RDS vs EC2 Pros and cons of: Relational Database Service (RDS) Elastic Compute Cloud (EC2) February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Relational Database Service (RDS): Pros Easy to set up, operate, and scale Manages common DBA tasks for you Backups, software patching, monitoring, hardware scaling, .. High availability via Multi-AZ Built-in automated failover to standby Database Includes Oracle licenses in pricing (SE1 and SE2) February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Relational Database Service (RDS): Cons No support for many options: Real Application Clusters (RAC) Data Guard / Active Data Guard Database Vault Automated Storage Management (ASM) Multitenant Database Java Support Unified Auditing February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO RDS: Cons (cont'd) No operating system-level or SYSDBA access Restricted access to some SYS procedures & tables Enterprise Edition requires BYOL Database size <= 16 TB Maximum IOPS <= 40,000 February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Elastic Compute Cloud (EC2).

3 Pros Most similar to running Oracle on your own servers Command line and SYSDBA access Gives full control of the Database All options for Enterprise Edition included No restrictions on max Database size or IOPS February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Elastic Compute Cloud (EC2): Cons More work to set up, configure, and tune Requires self management of: Database backups, point-in-time recoveries Standby databases Storage Security patches All Oracle version upgrades February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO migration Methods: Pros and Cons Database migration Service (DMS) Oracle GoldenGate Oracle Streams February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO DMS Diagram Oracle Source Database AWS Target Database DMS Replication Instance 1.

4 Start replication instance 2. Connect to source, target databases 3. Create task to select tables 4. DMS uses CDC to keep tables in sync 5. Switch applications over to target 6. Stop replication task (CDC) Bulk load, then Change Data Capture and Apply Turn on supplemental logging February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Database migration Service (DMS): Pros Supports homogeneous and heterogeneous migrations Oracle to Oracle Oracle to PostgreSQL, MySQL, .. Change Data Capture technology Can migrate subsets of tables and data February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Database migration Service (DMS): Cons Clobs are difficult and slow to migrate Limited LOB mode for clobs > 64kb Full LOB mode runs very slow 4-byte UTF-8 characters not supported Not as flexible as Streams February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO GoldenGate Diagram GoldenGate Hub (EC2 or on-premises) tables from source DB for fixed SCN tables to target DB 4.

5 Configure EXTRACT process on source DB 5. Configure REPLICAT process on target DB starting after fixed SCN 6. Start processes to replicate changes Ship trail files Apply transactions Read transactions Create trail files EXTRACT Oracle Source DB REPLICAT AWS Target DB trail files GoldenGate Hub February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Oracle GoldenGate: Pros Supports homogeneous and heterogeneous migrations Change Data Capture technology Can migrate subsets of tables and data Supports multitenant architecture Low performance impact on source and target DBs February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Oracle GoldenGate: Cons Extra cost option DDL replication is not supported in Oracle RDS EC2 only Must maintain and configure GoldenGate Hub EC2 instance or on-premises Monitor hub to restart processes in case of RDS failover February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Oracle Streams: Pros Free!

6 Very customizable (can apply filters and transformations) Works reasonably well with clob replication February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Oracle Streams: Cons Deprecated in Oracle 12c Supports Oracle to Oracle migration only Does not work in multitenant architecture Cannot apply filters to tables with clob columns May encounter unique constraint bug during apply February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Case Study: Replicating Oracle to RDS Merge 2 multi-terabyte Oracle databases into 1 target RDS Database Set up continuous replication using Streams Phased approach Migrate subsets of tables Over a period of months February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Case Study migration Diagram Oracle Source SRC1 SCHEMA1 TABLE1, TABLE2, TABLE3.

7 Replicate and merge table data Oracle Source SRC2 SCHEMA1 TABLE1, TABLE2, TABLE3, .. AWS Target RDS SCHEMA1 TABLE1, TABLE2, TABLE3, .. Replicate most recent 90 days of data Transform key_ids February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Configuring Continuous Replication Initial Database setup Determine instance class and storage type (CPU, memory, size) Create target Oracle RDS Database Tablespaces (use BIGFILE) Profiles Roles Users (schemas) February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Set Up Streams SRC1_APPLY SRC2_APPLY SRC1_CAPTURE SRC2_CAPTURE PROPAGATE_RDS PROPAGATE_RDS Strmadmin schema Strmadmin schema Strmadmin schema via RDS_DBLINK via RDS_DBLINK Add DML and DDL rules to capture February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Set Up Streams Create strmadmin user & tablespace in source & target DBs Create Database link to RDS in source DBs Create streams capture and propagation in source DBs Create streams apply (one for each source)

8 In target DB See Addendum for source code create streams processes February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Configure Streams Rules in Src1 Capture Stop streams capture and propagation in Src1 Add DDL rules, 1 for each table Add DML rules, 1 for each table Add subset rules for filtered tables Add rule transformations as needed Instantiate tables February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Stop streams capture and propagation exec ('SRC1_CAPTURE', '_checkpoint_force', 'Y'); exec ('SRC1_CAPTURE', FALSE); exec ('PROPAGATE_RDS'); February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Add DDL Rules ( table_name => <table name>, streams_type => 'capture', streams_name => 'SRC1_CAPTURE', queue_name => ' ', include_dml => FALSE, include_ddl => TRUE, include_tagged_lcr => TRUE, ddl_rule_name => <ddl rule name>, inclusion_rule => TRUE).

9 February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Add DML Rules ( table_name => <table name>, streams_type => 'capture', streams_name => 'SRC1_CAPTURE', queue_name => ' ', include_dml => TRUE, include_ddl => FALSE, include_tagged_lcr => TRUE, dml_rule_name => <dml rule name>, inclusion_rule => TRUE); February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO DML: Add rules for Filtered Tables Use ADD_SUBSET_RULES instead of ADD_TABLE_RULES Example: capture changes for table where key_id >= 15000 February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Add DML Subset Rules ( table_name => <table name>, dml_condition => '(key_id >= 15000)', streams_type => 'capture', streams_name => 'SRC1_CAPTURE', queue_name => ' ', include_tagged_lcr => TRUE, insert_rule_name => <insert rule name>, update_rule_name => <update rule name>, delete_rule_name => <delete rule name>).

10 February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Instantiate Tables SELECT INTO <SCN> FROM dual; (<table name>); @RDS_DBLINK ( source_object_name => <table name>, source_database_name => ' ', instantiation_scn => <SCN>, apply_database_link => NULL); February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Instantiate Tables (cont'd) -- Wait a few seconds to make sure instantiation is complete exec (5); -- Get current scn and use it for the export col scn format 99999999999999999999999999 select timestamp_to_scn(systimestamp) as scn from dual; February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Export Tables from Src1: parfile DIRECTORY = data_pump_dir DUMPFILE = SCHEMAS = schema1 INCLUDE = TABLE:"IN ('TABLE1', 'TABLE2', 'TABLE3', 'TABLE4')" QUERY = :"WHERE (key_id >= 15000)" LOGFILE = FLASHBACK_SCN = <scn from query above> COMPRESSION = ALL February 20-22, 2018 | Westin Westminster Hotel | Westminster, CO Export from Src1 and Transfer to RDS -- Run the next command / as sysdba on the Src1 Database server expdp parfile = -- Transfer the export file to RDS.