ATP Autonomous Transaction Processing Database Partha Pratim Mahanta
ATP (Autonomous Transaction Processing) Database Partha Pratim Mahanta Solution Engineering Hub Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal
Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 2
Agenda Ø Ø General Introduction to Autonomous Data Trasaction Processing ( ATP ) ATP Business use case Data Loading to ATP Configure OCI CLI Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 3
The Primary Goal Both Services use Oracle Database 18 c Ø ATP has been designed to execute a high volume of transactions – OLTP Ø The primary goal of ADW is to achieve fast complex Analytics Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 4
Oracle Autonomous Transaction Processing is a fully managed database tuned and optimized for transaction processing or mixed workloads with the market leading performance of Oracle Database. Lower Cost & Increased Productivity Lower risk Higher Availability ( 99. 95% uptime guarantee) Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Atonomous Transaction Processing : Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 6
ATP Use Case 1 ( Financial/Banking ) : Understand your customer q Data Sets • Net banking data • Credit card transactions • International spends • Holidaying schedules and choice of destination • Customer support interaction feedback , complaints (Optional ) q Key Benefits • Getting deep insight into customer expenses and making precise customer expense prediction • Build data on targeted customers and understand their frequent needs. • Establish relationship between customers and retailers • Retaining the highly potential but annoyed customers based on complaints and feedbacks ( Optional ) • Offers - bringing a mutual gain to both investor and the financial institution Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ATP Use Case 2 ( Telco) : Revenue Performance Analysis Discover business insight about drop in revenue for the 4 G plan even though the number of transactions for the plan type remain high, determine course of action to fix this issue (alert customers about card expiry). Additionally, find causes for a potential revenue leakage and fraud. q Key Benefits • Top Management will have clear view on revenue trends • Corrective actions from fraud analysis • Joining various data sets enable you to generate rich insights and help determine actions to take for course corrections • Enable top management to get business information near real time Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Data Loading to ATP Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 9
Case 1 Data Loading via DBMS_CLOUD Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 10
ATP DB Provision Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 11
Connectivity with SQL Developer Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 12
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 13
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 14
Object Storage Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 15
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 16
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 17
Case 2 Data Loading via DATA PUMP Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 18
Table “employee” with 7 million data at source Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Export dump from the Source Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Upload the Dump file “employee. dmp” to the target ( ADW ) “Object Storage” Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
“Employee” table not available in ADWC Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Configure the wallet and test the connection to ADW with both Consumer group “High” and “Low” Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Execute the Data Pump Credential Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Crete the “employee” table in ADW Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Table “Employee” import of total 7 million data to ADW Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Case 3 Data Loading via ODI Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 28
File Uploaded to DIPC VM Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ODI Mapping To Extract And Load Data Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
ODI Run Time Statistics for 9 million data Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Test Case 4 Data Loading via SQL Developer Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 32
Import Data Using SQL Developer Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Troubleshooting data loads Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Troubleshooting data loads Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Connecting to various Consumer Group via SQL Plus Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 38
ATP Connection Strings : Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 39
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 40
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 41
HIGH : Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 42
TPURGENT : Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 43
Configure OCI CLI Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 44
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 45
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 46
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 47
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 48
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 49
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 50
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 51
oci db autonomous-database create --admin-password "WElcome##123" -c ocid 1. compartment. oc 1. . aaaag 6 adus 522 s 7 qnqh 55 zex 7 xov 35 dtmuw 2 hqceowu 2 hovesjnckf 7 a --cpu-corecount 1 --data-storage-size-in-tbs 1 --db-name "SEHUBDB" Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 52
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 53
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 54
Deleting the Database : oci db autonomous-database delete --autonomous-database-id ocid 1. autonomousdatabase. oc 1. eu-frankfurt 1. abtheljsvb 2 j 3 noemdgsfmyuyuyu 2 gdvyrmvsjeenp 5 zr 5 vzszugfyhtpo 2 q Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 55
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 56
UPLOAD WITH OCI CLI TO OBJECT STORAGE oci os object put -ns sehubemeaprod -bn Partha_bucket --file D: OE_20181105_08. dmp --part-size 1048 --parallelupload-count 1 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 57
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 58
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 59
Email : partha. Mahanta@oracle. com Linkedin : www. linkedin. com/in/parthamahanta Mobile : +91 9900246531 Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | 60
Q&A Copyright © 2016, Oracle and/or its affiliates. All rights reserved. |
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 62
- Slides: 63