Oracle Database Jeff Smith, Master Product Manager @thatjeffsmith [email protected] https://www.thatjeffsmith.com Management REST API 1

Secure, performant HTTPS access • Access your data, or the database itself, via HTTP • GET – access your data and stored procedures • PUT – update your data • POST – insert your data, execute PL/SQL • DELETE – remove data • Your favorite protocol AND your favorite data format 2

Oracle REST Data Services REST-enable your Oracle Database HTTPS/REST JDBC Containers, Pluggables, or multiple separate instances

JSON HTTP(s) https://myhost/ords/hr/customerorders/1001 Oracle REST Data Services SQL ORDS maps standard URI requests to corresponding relational SQL: e.g. SQL SELECT from CUSTOMERS & ORDERS tables. ORDS transforms the SQL results into the highly popular JavaScript Object Notation (JSON), other formats include HTML, binary and CSV. SQL result to JSON transformation URI Request or HTTP(s) post mapped to SQL request JDBC Connection Pool URI schema Template (table/:pk) Context Root HTTPS Request and Response

• Java Servlet • Tomcat or WLS • Standalone mode (Jetty) • Included with your Oracle DB License Tomcat, WLS ORDS Apache Static Files -HTML, CSS, JS /ords/… /db1/hr/emp/ JSON Binary HTML … HTTP(s) Results JDBC pool1 pool2 pool3 Request JDBC JDBC Oracle REST Data Services (ORDS)

ORDS Major Features 6 • Web listener for your Oracle PL/SQL programs or APEX • RESTful Services harness for your Oracle Database • SQL Developer Web • Database Management REST API • Mongo style REST API for Oracle Database (SODA)

Business Requirements • {JSON}, cURL, & HTTPS are ubiquitous AND required • The Oracle Database needs to be provisioned, controlled, and monitored – with minimal human interaction • Delivered and supported by the Oracle Database product team • Available anywhere Oracle can run 7 Easily Plug-In Oracle Database Management to existing CI/CD pipelines

The Solution • Manage & Monitor your Oracle Database • Supports 11gR2, 12, 18, & 19c • General, Data Dictionary, Monitoring, Performance, & PDB Lifecycle Management • REST Endpoints backed by ORDS 8

DB API Features 9 Performance Monitoring PDB Lifecyle General Data Dictionary Reports • tables/, tables/{table} • indexes/… Multitenant Management • Create • Clone • Change State • Drop • Reports Problematic Activity • Sessions • Locks • Waits • Alert Log What isn’t running well? • ASH • AWR • RTSM • Top SQL Database Operations • DBCA • Data Pump

Getting Started 1. Install ORDS 2. Enable the DB API 3. REST Enable an Oracle USER 4. Browse the DB API Service Documentation true

Create/Privilege a User for the DB API Option #1 - Mid Tier User (SQL Administrator Role): Option #2 - REST Enabled DB User:

Explore the DB API Documentation Oracle Docs OpenAPI.JSON Endpoint

Examples 1. Report ALL objects in your database 2. Report filtered list of objects in your database 3. Create and Submit a Data Pump Export Job 4. Create and Submit a Data Pump Import Job 5. Clone a PDB 13 HTTPS Requests with payloads and responses

Example: All Objects 1. Find the right end point 2. Try it out! 14

All Objects: Method + URL • ORDS servlet maps to /ords • HR is the REST Enable Schema /hr • DB API services mounted to _/db-api • Version of API /latest • /latest or /stable => current version • /19.1.0 => specific version /19.2.0 • All Objects Service Template /database/objects/ GET http://localhost:8080/ords/hr/_/db-api/latest/database/objects/ URI Breakdown

All Objects: The Response { "items": [ { "owner": "SYS", "object_name": "I_FILE#_BLOCK#", "subobject_name": null, "object_id": 9, "data_object_id": 9, "object_type": "INDEX", "created": "2017-01-26T18:52:53Z", "last_ddl_time": "2017-01-26T18:52:53Z", "timestamp": "2017-01-26:13:52:53", "status": "VALID", "temporary": "N", "generated": "N", "secondary": "N", "namespace": 4, "edition_name": null, "sharing": "NONE", "editionable": null, "oracle_maintained": "Y", "application": "N", "default_collation": null, "duplicated": "N", "sharded": "N", "created_appid": null, "created_vsnid": null, "modified_appid": null, "modified_vsnid": null, "links": [ { "rel": "self", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/database/objects/SYS,I_FILE%23_BLOCK%23" } ] }, DBA_OBJECTS in {JSON} with links to individual objects

Collection Results & Paging "hasMore": true, "limit": 25, "offset": 0, "count": 25, "links": [ { "rel": "self", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/database/objects/" }, { "rel": "describedby", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/metadata-catalog/" }, { "rel": "first", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/database/objects/" }, { "rel": "next", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/database/objects/?offset=25" } ] } • Results are automatically Paged (default pagesize 25) • Lots of links!

All Objects: Filtering ?q={} dynamically updates SQL SELECT * FROM DBA_OBJECTS WHERE STATUS = 'INVALID' Getting ONLY Invalid Objects Query Parameters on the URI /ords/hr/_/db-api/19.1.0/database/objects/?q={"$eq":{"status":"INVALID"}} Query Filtering Examples

Example: Data Pump • Inputs {POST Body} • Directory? • Type of export? • # of threads? • What are we exporting? • Response • Links to GET status & log

Data Pump: Export two specific TABLES { "datapump_dir":"DATA_PUMP_DIR", "filter" :"HOCKEY_STATS, UNTAPPD", "job_mode" :"TABLE", "threads" : 2 } POST BODY Response

Data Pump: The Response (Resources) "links": [ …, { "rel": "related", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/database/datapump/jobs/HR.../EXPDAT...LOG" }, { "rel": "self", "href": "http://localhost:8080/ords/hr/_/db-api/19.1.0/database/datapump/jobs/HR.../" } ] Job Log File The Job w/metadata • The Database Job is created & submitted to run immediately • Instead of waiting for it to finish, we give you links to monitor the progress, and see the results

Data Pump: The Job’s Log File

Data Pump: The Job’s Details

Data Pump: The .DMP Output File

Data Pump: The Job in the Database

Updates • Database Creation Assistant • Data Pump Import 26 New for v19.2!!

27 Example: Data Pump – IMPORT .DMP { "datapump_dir" :"DATA_PUMP_DIR", "job_mode" :"FULL", "file_name" :"EXPDAT%U-10_52_17.DMP" } POST BODY Response POST http://localhost:8080/ords/hr/_/db-api/19.2.0/database/datapump/import

Data Pump Import: Log File

Example: Clone a PDB • Inputs {POST Body} • New PDB name • File Mapping • Storage Limits • Response • PL/SQL block output

PDB Lifecycle Management Prerequisites • ORDS installed to CDB • Common User with SYSDBA configured for ORDS • DB API PDB services mounted to ords/_/db-api/stable/database/pdbs/ • Authenticated user must have ‘SQL Administrator’ role

31 Cloning the PDB POST http://localhost:8080/ords/_/db-api/latest/database/pdbs/PDB2/ { "method" : "CLONE", "clonePDBName" : "PDB2_CLONE", "fileNameConversions": "('/u01/app/oracle/oradata/ORCLCDB/pdb2/','/u01/app/oracle/oradata/ORCLCDB/pdb2_clone/')", "unlimitedStorage" : true, "reuseTempFile" : true, "totalSize" : "UNLIMITED", "tempSize" : "UNLIMITED" } POST BODY Response

Major European retailer successfully integrated ORDS to manage ORCL deployments via DB API 32 Real World Customer Use Case Key Features • Easily Create & Clone PDB • Data Pump Export & Import • PDB Session Monitoring Database Requests seamless integrated into existing Ticketing system (ServiceNow) Customer Datacenter Read-only (refreshable) PDB – master for sync Re mote Cl one & R e f re sh Production Quality 1 1 Hot Clone 2 Writable copy of production data – performance test 2 Writable copy of masked data – quality 3 Hot Clone & Masking 3 Test Re mote Cl one & R e f re sh 4 Read-only masked PDB – master for test 4 Hot Clone 5 6 Hot Clone & Subse tting Writable copy of test data – integration test 5 Writable copy of subset data – integration test 6 Read-only PDB – master for dev data 7 Development data – shared/cloud environment 8 Re mote Cl one & R e f re sh Hot Clone Hot Clone Development 7 8 Re mote Cl one curl --location --request POST 'http://132.145.22.242:8080/ords/_/db- api/stable/database/pdbs/' \ --header 'Content-Type: application/json' \ --data-raw '{ "pdb_name": "TEST", "fileNameConversions": "NONE", "method": "CREATE", "adminName": "admin", "adminPwd": "MAnager1234__", "totalSize": 1000000000, "tempSize": 1000000 }' Developer 1 2 3 4 5 Process Mgt cURL

Road Map • APEX Management/Monitoring • Multitenant • e.g. PDB Hot Cloning, Refresh and Snapshot cloning • Data Guard Broker • RMAN Catalog & Backup Jobs (?) 33 2020 Plans

More Resources Blog Post: Data Pump Import Blog Post: Data Pump Export Blog Post: PDB Clone and Unplug Blog Post: DB API Introduction Video Series: Oracle REST Data Services Slides: ORDS Overview & REST Service Implementation Options 34