Oracle Database Management REST APIs

An overview of our REST APIs for managing your Oracle Database.

An overview of how it works, what's included, and a few examples including how to create and run a Data Pump job to export and import your data, and clone an existing pluggable database - all via HTTPS.

Links from the deck:
Install ORDS - https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-A16BCCA2-8081-4062-A635-9F7C36FC394F

Enabling the DB API
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/enabling-ords-database-api.html#GUID-8730051B-7C03-487B-954A-7D6786B7EC74

DB API Docs (OpenAPI)
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbrst/api-data-dictionary.html

Blog post on Data Pump Export
https://www.thatjeffsmith.com/archive/2019/05/data-pump-via-ords/

Blog post on Cloning and Unplugging a PDB
https://www.thatjeffsmith.com/archive/2020/04/pdb-lifecycle-management-operations-via-the-ords-db-api/

ORDS Videos
https://www.youtube.com/watch?v=rvxTbTuUm5k&list=PL_qNe_aTKkIXN3WW6HOq1O0ENOcH2TcFl

Transcript

1. Oracle Database Jeff Smith, Master Product Manager @thatjeffsmith jeff.d.smith@oracle.com https://www.thatjeffsmith.com Management REST API 1
2. 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
3. Oracle REST Data Services REST-enable your Oracle Database HTTPS/REST JDBC Containers, Pluggables, or multiple separate instances
4. 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
5. • 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)
6. 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)
7. 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
8. 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
9. 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
10. Getting Started 1. Install ORDS 2. Enable the DB API 3. REST Enable an Oracle USER 4. Browse the DB API Service Documentation <entry key="database.api.enabled">true</entry>
11. Create/Privilege a User for the DB API Option #1 - Mid Tier User (SQL Administrator Role): Option #2 - REST Enabled DB User:
12. Explore the DB API Documentation Oracle Docs OpenAPI.JSON Endpoint
13. 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
14. Example: All Objects 1. Find the right end point 2. Try it out! 14
15. 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
16. 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
17. 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!
18. 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
19. Example: Data Pump • Inputs {POST Body} • Directory? • Type of export? • # of threads? • What are we exporting? • Response • Links to GET status & log
20. Data Pump: Export two specific TABLES { "datapump_dir":"DATA_PUMP_DIR", "filter" :"HOCKEY_STATS, UNTAPPD", "job_mode" :"TABLE", "threads" : 2 } POST BODY Response
21. 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
22. Data Pump: The Job’s Log File
23. Data Pump: The Job’s Details
24. Data Pump: The .DMP Output File
25. Data Pump: The Job in the Database
26. Updates • Database Creation Assistant • Data Pump Import 26 New for v19.2!!
27. 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
28. Data Pump Import: Log File
29. Example: Clone a PDB • Inputs {POST Body} • New PDB name • File Mapping • Storage Limits • Response • PL/SQL block output
30. 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. 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
32. 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
33. 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
34. 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