Migrating and Running DBs on Amazon RDS for Oracle

© 2016 Pythian 2 Maris Elsins Lead Database Consultant At Pythian since 2011 Located in Riga, Latvia Oracle [Apps] DBA since 2005 Speaker at conferences since 2007 @MarisDBA elsins@pythian.com http://bit.ly/getMOSPatchV2

© 2016 Pythian 9 DISCLAIMER OK, YOU CAUGHT ME ... I may have a few “Ouch!” slides in the presentation, that probably will make you think Oracle RDS is no good. It's not the intention. I honestly think Oracle RDS is a great service for the appropriate workload, and if it's done right.

© 2016 Pythian 13 INTRODUCTION WHAT IS RDS • Relational Database Service • PaaS (DBaaS) • Managed by AWS – Setup / Provisioning – Backups – Maintenance / Patching – Alerting – DR – ... • SSH Access / SQL*Net connectivity

© 2016 Pythian 14 • 11gR2: – 11.2.0.2.v3 - 11.2.0.2.v7 – 11.2.0.3.v1 - 11.2.0.3.v4 – 11.2.0.4.v1 - 11.2.0.4.v9 • 12cR1: – 12.1.0.1.v1 - 12.1.0.1.v6 – 12.1.0.2.v1 - 12.1.0.2.v5 • Engine versions differ by included patches – PSU / DST / GG / + critical one-offs INTRODUCTION DB ENGINES http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.PatchComposition.html What if I need a specific one-off?

© 2016 Pythian 16 INTRODUCTION LICENSING • Two important sources: – Licensing Oracle Software in the Cloud Computing Environment – Virtual Cores by Amazon EC2 and RDS DB Instance Type • Virtual cores != Virtual CPUs (vCPUs) – vCPU is actually a HyperThread • Core Factor of 0.5 applies to "Virtual cores" • Max sizing for SE: – SE: 16 Virtual Cores | 32 vCPU – SE: One/Two: 8 Virtual Cores | 16 vCPU

© 2016 Pythian 18 INTRODUCTION INSTANCE CLASSES AND TYPES https://aws.amazon.com/rds/oracle/details/ • Instance Classes (available for RDS) – Standard - Latest Generation (db.m4.{type}) – Standard - Previous Generation (db.m3.{type}) – Memory Optimized - Current Generation (db.r3.{type}) – Micro instances (db.t1.{type}) • Types: micro - small - ... - xlarge - ... - 10xlarge – vCPUs: 1 - 40 – Memory: 1 - 160 GiB – PIOPS-Optimized: Yes / No – Network Performance: Low to Moderate - 10 Gigabit Pay attention to these when choosing the storage type!

© 2016 Pythian 19 INTRODUCTION STORAGE TYPES http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html Magnetic General Purpose SSD Provisioned IOPS • Spinning disks • Previous gen / obsolete • Pay per Use + Size • ~ 100 IOPS • 1MiB IO size • SSD • Cheap & Low Latency • 3 IOPS * 1GiB • Pay per Size • Up to 160 MiB/s • Accounted IO size = 32K • Burstable • SSD • Expensive & Low Latency • 10 IOPS * 1GiB • Pay per Size + PIOPS • Up to 500 MiB/s (or 320MiB/s ???) • Accounted IO size = 32K This is super - complicated, read this! The documentation is not very clear PIOPS = Provisioned IO per Second

© 2016 Pythian 21 INTRODUCTION STORAGE-RELATED LIMITATIONS https://calculator.s3.amazonaws.com/index.html • Different limits apply based on – Instance Type – Storage Type – Storage Size – PIOPS • For example: – EBS Throughput (Mbps) per Instance – EBS Throughput (Mbps) per EBS Volume – Max IOPS per Instance – Max IOPS per Volume – PIOPS you reserved and paid for – IOPS differ depending on the size of the IO I guess I'll have to do some real benchmarking !

© 2016 Pythian 22 INTRODUCTION OPTION GROUPS - OPTIONS http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.html Option Option ID Oracle Application Express APEX, APEX-DEV Oracle Enterprise Manager OEM, OEM_AGENT Oracle Label Security OLS Oracle Native Network Encryption NATIVE_NETWORK_ENCRYPTION Oracle SSL SSL Oracle Statspack STATSPACK Oracle Time Zone Timezone Oracle Transparent Data Encryption TDE Oracle UTL_MAIL UTL_MAIL Oracle XML DB XMLDB

© 2016 Pythian 23 • Option group – Defines the set of enabled options – can be assigned to multiple RDS instances • Default options groups: – “default:oracle-ee-11-2” and “default:oracle-ee-12-1” – has no options enabled by default – Can't be modified • Separate PROD, QA, TEST, … INTRODUCTION OPTION GROUPS http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.html

© 2016 Pythian 24 INTRODUCTION PARAMETER GROUPS • Parameter Groups – Defines the init parameters • Remember, you don't have access to “/ as sysdba” • How do I run “alter system …” – Can be assigned to multiple instances – Some parameters are derived from instance settings • i.e. DB_NAME={DbName} • Default parameter groups can't be changed – Even modifiable settings require reboot. – We used a workaround in few cases - a logon trigger http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.html

© 2016 Pythian 25 • MEMORY_TARGET = {DBInstanceClassMemory*3/4} – AMM means hugepages are not used – Swapping is possible AWS RDS FOR ORACLE SOME PARAMETERS I FOUND INTERESTING … Swap usage MB Connection pool misconfiguration Ouch !

© 2016 Pythian 26 • DB_FILE_MULTIBLOCK_READ_COUNT not set (128) – IO size up to 1MiB issues by the DB AWS RDS FOR ORACLE SOME PARAMETERS I FOUND INTERESTING … Ouch ! A single 1MiB-IO (128 data blocks) from the DB ... … Is handled by 4 256K-IOs on SSD and ... … Is accounted as 32 32K-IOs by AWS Used for IOPS calculation By AWS

© 2016 Pythian 27 • UNDO_RETENTION not set (the default is 900 seconds) • We're using parameter group “default.oracle-ee-11.2” AWS RDS FOR ORACLE SOME PARAMETERS I FOUND INTERESTING … Ouch ! • Suddenly: – “expdp … consistent=y” – ORA-31693: Table data object "XYZ"."BLAH_BLAH" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01555: snapshot too old: rollback segment number 82 with name "_SYSSMU82_540458409$" too small

© 2016 Pythian 29 SIZING IT CORRECTLY IT'S NOT VERY SIMPLE... • Instance size (CPU / RAM) is Important: – Size it too big - cost-efficiency reduces – Size it too small - performance suffers • Storage size (GB / IOPS) is Important: – Size it too big - cost-efficiency reduces – Size it too small - performance suffers

© 2016 Pythian 30 • How to check the current usage? – sar - CPU/RAM/IO ? • 10 minute intervals for past workloads • Difficult to derive IOPS info – SGA sizing – iostat ? – data from storage? • What if it's not 1-to-1 move to RDS? – 1DB with 20+ schemas -> 4 RDS instances? SIZING IT CORRECTLY TOOLS Peak load?

© 2016 Pythian 31 • CPU is simple – sar 1 99999 > cpu_usage.lst • Provides CPU usage % for every issue – ASH ? • Probably still not granular enough (binary indicator) • Good to for deriving the proportions of CPU usage – v$sesstat / v$sysstat could be sampled too • … we didn't go this way ... SIZING IT CORRECTLY CPU IS SIMPLE

© 2016 Pythian 32 SIZING IT CORRECTLY CPU DISTRIBUTION AMONG SCHEMAS • Using ASH to derive max CPU usage per hour by schema – (but again it ignores “spikes”) select username, avg(CPUTIME_S), avg(DBTIME_S), avg(CPUTIME_PCT_OF_4CPU), avg(DBTIME_PCT_OF_4CPU), min(CPUTIME_S), min(DBTIME_S), min(CPUTIME_PCT_OF_4CPU), min(DBTIME_PCT_OF_4CPU), max(CPUTIME_S), max(DBTIME_S), max(CPUTIME_PCT_OF_4CPU), max(DBTIME_PCT_OF_4CPU) from (select d.sample_time, u.username, 14400 AVAILABLE_TIME, CPUTIME_S, DBTIME_S, round(CPUTIME_S/14400*100,1) CPUTIME_PCT_OF_4CPU, round(DBTIME_S/14400*100,1) DBTIME_PCT_OF_4CPU from (select snap_id, trunc(min(sample_time),'HH24') sample_time, user_id, 10*count(decode(event,null,1,null)) CPUTIME_S, 10*count(*) DBTIME_S from dba_hist_active_sess_history where sample_time>= trunc(sysdate-31) group by snap_id, user_id) d, dba_users u where d.user_id=u.user_id) group by username order by 1;

© 2016 Pythian 34 • IO is not simple – Differently-sized IO done by database • 8K to 1024K • … 1 to 32 RDS-IOs per 1 DB-IO – Identifying spikes is difficult • AWR (DBA_HIST_IOSTAT_DETAIL) • SMALL_READ_MEGABYTES / LARGE_READ_MEGABYTES • SMALL_READ_REQS / LARGE_READ_REQS SIZING IT CORRECTLY IO IS NOT SIMPLE Peak load?

© 2016 Pythian 35 SIZING IT CORRECTLY IO IS NOT SIMPLE • Sampling ASM statistics: – asm_metrics.pl by Bertrand Drouvot – https://bdrouvot.wordpress.com/2013/10/04/... • 1-second sampling: 23:00:03 Kby Avg AvgBy/ Kby Avg AvgBy/ 23:00:03 DBINST DG Reads/s Read/s ms/Read Read Writes/s Write/s ms/Write Write 23:00:03 ----------- ----------- ------- ------- ------- ------ ------ ------- -------- ------ 23:00:03 INST1 146 1360 9.1 9539 435 5578 25.5 13131 23:00:03 INST1 DATA 146 1360 9.1 9539 171 4096 61.7 24528 23:00:03 INST1 RECO 0 0 0.0 0 6 216 86.3 36864 23:00:03 INST1 REDO 0 0 0.0 0 258 1266 0.2 5025 23:00:03 INST2 245 2104 8.3 8794 2109 23955 97.5 11631 23:00:03 INST2 DATA 245 2104 8.3 8794 1998 21648 102.7 11095 23:00:03 INST2 RECO 0 0 0.0 0 3 1968 116.9 671744 23:00:03 INST2 REDO 0 0 0.0 0 108 339 0.1 3214

© 2016 Pythian 36 $ cat process.sh DG=$2 FILE=$1 grep $2 $1 | awk '{RIO=0; WIO=0; IO=0; if ($7>32768) RIO=($5-($4*32))/32; if ($11>32768) RIO=($9-($8*32))/32;IO=$4+$8+RIO+WIO; print $1,$2,$4,$5,$7,$8,$9,$11,RIO,WIO,IO;}' | awk '{if ($1==PREV_TIM) {IO+=$11} else {print PREV_TIM,IO; IO=$11; PREV_TIM=$1}}'| cut -c 1-7,9- | awk '{if ($1==PREV_TIM) {IO+=$2} else {AVGIOPS=IO/10; print PREV_TIM,AVGIOPS; IO=$2; PREV_TIM=$1}}' $ process.sh asm_metrics_per_second_20150309_2300.txt DATA 23:00:0 4965.02 23:00:1 18455.1 23:00:2 14806.9 23:00:3 1460.35 23:00:4 1002.33 23:00:5 1600.22 SIZING IT CORRECTLY IO IS NOT SIMPLE

© 2016 Pythian 38 • Choose the instance and the storage type based on – Max IOPS – HDD size – Max CPU usage – RAM requirement • Workloads vary too much – getting it exactly right may be tricky – But at least you shouldn't size it much too large • Benchmark / Load Test SIZING IT CORRECTLY IO IS NOT SIMPLE https://aws.amazon.com/rds/oracle/details/, http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html

© 2016 Pythian 40 MIGRATING TO ORACLE RDS MIGRATION OPTIONS • Offline options: – Oracle Data Pump – Oracle Export/Import Utilities – Oracle SQL*Loader – Oracle Materialized Views • Additional options (logical replication): – Data Migration Service (DMS) – Oracle GoldenGate (GG) http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html

© 2016 Pythian 41 $ expdp system/***@SOURCE DUMPFILE=data.dmp DIRECTORY=DATA_PUMP_DIR LOGFILE=data.log flashback_time=systimestamp schemas=USER1 $ sqlplus SYS@SOURCE as sysdba SQL> create database link TARGET connect to oracle_master identified by * using 'TARGET'; SQL> exec DBMS_FILE_TRANSFER.PUT_FILE(source_directory_object => 'DATA_PUMP_DIR', source_file_name => 'data.dmp', destination_directory_object => 'DATA_PUMP_DIR', destination_file_name => 'data.dmp', destination_database => 'TARGET') $ impdp oracle_master/***@TARGET DUMPFILE=data.dmp DIRECTORY=DATA_PUMP_DIR LOGFILE=data_imp_1.log $ sqlplus oracle_master@TARGET SQL> exec utl_file.fremove('DATA_PUMP_DIR','data.dmp'); SQL> exec utl_file.fremove('DATA_PUMP_DIR','data_imp_1.log'); MIGRATING TO ORACLE RDS ORACLE DATA PUMP http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.html Make sure you have enough space on your RDS instance!

© 2016 Pythian 43 • Simple version of Logical Replication – Also capable of converting between different types of DBs – Schema migration in a few clicks • New tool (announced on 15 Mar 2016) • A few limitations – Unclarity about the initial load – Lack of "SCN" – Can't adjust the migration task after starting it – DDL is not captured – Some data types not supported • It should be good enough for simple cases MIGRATING TO ORACLE RDS DMS (DATA MIGRATION SERVICE) http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Configuration

© 2016 Pythian 45 RUNNING AND MONITORING... LIFE WITHOUT "SYSDBA" http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html • Only SQL*Net Connectivity • Master Username – Limited set of privileges • Common DBA Tasks for Oracle DB Instances – Review them carefully, and prepare ! – Some tasks are done differently ... • killing a session • exec rdsadmin.rdsadmin_util.kill(..., ..., ...); • Creating an AWR report • DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT – RDSADMIN.RDSADMIN_UTIL

© 2016 Pythian 46 • No host access – Prevents setting up some types of monitoring • Cron jobs • Certain 3rd party monitoring agents • What's available? – Cloudwatch monitoring + alerts • 1 minute interval / difficult to correlate different metrics – Enhanced monitoring (no alerts) • 1s - 60s monitoring interval – OEM Option (11g DB Control / Database Express 12c ) – OEM_AGENT Option !! (Announced Sep, 2016) – Custom monitoring over SQL*Net (Avail) RUNNING AND MONITORING... MONITORING http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.html

© 2016 Pythian 52 RUNNING AND MONITORING... Monitoring - OEM_AGENT I haven't tried this yet, but knowing the DB Express 12 is more limited than DB Control - It's a good thing we have it now! Standalone EM 12c CC is required!

© 2016 Pythian 54 AWS RDS FOR ORACLE EXAMPLE 1: LATENCY ISSUES • In the middle of the migration project – One database was moved to Oracle RDS – Another interfacing system remained on-premise • A batch job runs a LOT SLOWER – Tracing reveals row-by-row processing between 2 DBs – A local DB <-> RDS – Latency issue, each network round-trip took a lot longer. https://calculator.s3.amazonaws.com/index.html

© 2016 Pythian 55 AWS RDS FOR ORACLE EXAMPLE 2: ACCESS TO DATA • A tiny OBIEE Database (40G) – Db.m4.xlarge – 4 vCPU + 16G RAM • The problem – IO slowness – Beyond point where IOPS is reasonable to increase to • The Solution – Upgrade to Db.r3.xlarge (memory optimized) – 4vCPU + 30.5 RAM – Set parameter _serial_direct_reads=never https://calculator.s3.amazonaws.com/index.html

© 2016 Pythian 57 -- Create the procedure that will be used to collect the execution plans create or replace procedure PERF.XXSTABILITY_CAPTURE_PLANS is cur sys_refcursor; cursor clist is with raw_sqlids as (select sql_id from v$sqlarea where sql_id in (select sql_id from v$active_session_history where sample_time>=sysdate-1/24 and sql_plan_hash_value>0 and machine like '%-hesvc-app0%') and plan_hash_value>0 and executions>2 union select sql_id from v$sqlarea where sql_id in (select sql_id from v$open_cursor where sid in (select sid from v$session where machine like '%-hesvc-app0%')) and plan_hash_value>0 and executions>2 and parsing_schema_name not in ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS', 'CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS ','SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL')), sqlids as (select distinct ''''||sql_id||'''' sql_id, trunc((rownum-1)/200) rn from raw_sqlids) select listagg(s.sql_id,',') within group (order by s.sql_id) as sql_id_filter from sqlids s group by s.rn; c clist%rowtype; BEGIN for c in clist loop OPEN cur FOR SELECT VALUE(P) FROM table(dbms_sqltune.select_cursor_cache(basic_filter=>'sql_id in('||c.sql_id_filter||')', attribute_list=>'ALL')) P; DBMS_SQLTUNE.LOAD_SQLSET(load_option=>'MERGE',sqlset_name => 'XXSTABILITY_EXECUTION_PLANS', populate_cursor => cur); CLOSE cur; end loop; Commit; END; / AWS RDS FOR ORACLE EXAMPLE 3: PRESERVING GOOD EXECUTION PLANS

© 2016 Pythian 58 -- Create the Scheduler Job which will run on every hour's 47th minute Begin Dbms_scheduler.create_job (job_name => 'COLLECT_XXSTABILITY_PLANS', job_type => 'STORED_PROCEDURE', job_action => 'PERF.XXSTABILITY_CAPTURE_PLANS', start_date => SYSDATE, repeat_interval => 'FREQ=HOURLY; INTERVAL=1; BYMINUTE=47;', enabled => TRUE); End; / -- Loading the baseline from SELECT_SQLSET for a specific sql_id and plan VARIABLE cnt NUMBER EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( - sqlset_name => 'XXSTABILITY_EXECUTION_PLANS', - basic_filter => 'sql_id=''dtzsc12fbbjk2'' and plan_hash_value=2896891279'); AWS RDS FOR ORACLE EXAMPLE 3: PRESERVING EXECUTION PLANS

© 2016 Pythian 59 AWS RDS FOR ORACLE EXAMPLE 4: COMBINED MAINTENANCE • Combined maintenance - 30 minutes allocated – Take a snapshot – Change the instance size • Oops! – Snapshot takes just few seconds • … before it's copied to S3 – “Creating Snapshot” for 45 minutes https://calculator.s3.amazonaws.com/index.html

© 2016 Pythian 61 • Simple to use, but not suitable for massive workloads – Consider Oracle DB on EC2 • Difficult to size (especially storage) – Load testing and Benchmarking – 3000 IOPS or less -> Use gp2 (General Purpose SSD) volumes • Get the config right from the beginning – Changing certain things require downtime • Practice being an Oracle RDS DBA – Some things are different SUMMARY Oracle RDS

Migrating and Running DBs on Amazon RDS for Oracle

More Related Content

What's hot

Similar to Migrating and Running DBs on Amazon RDS for Oracle

More from Maris Elsins

Recently uploaded

Migrating and Running DBs on Amazon RDS for Oracle