Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle Database Management REST APIs

thatjeffsmith
February 03, 2020

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

thatjeffsmith

February 03, 2020
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  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
  2. Oracle REST Data Services REST-enable your Oracle Database HTTPS/REST JDBC

    Containers, Pluggables, or multiple separate instances
  3. 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
  4. • 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)
  5. 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)
  6. 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
  7. 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
  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
  9. 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>
  10. Create/Privilege a User for the DB API Option #1 -

    Mid Tier User (SQL Administrator Role): Option #2 - REST Enabled DB User:
  11. 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
  12. 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
  13. 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
  14. 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!
  15. 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
  16. Example: Data Pump • Inputs {POST Body} • Directory? •

    Type of export? • # of threads? • What are we exporting? • Response • Links to GET status & log
  17. Data Pump: Export two specific TABLES { "datapump_dir":"DATA_PUMP_DIR", "filter" :"HOCKEY_STATS,

    UNTAPPD", "job_mode" :"TABLE", "threads" : 2 } POST BODY Response
  18. 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
  19. 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
  20. Example: Clone a PDB • Inputs {POST Body} • New

    PDB name • File Mapping • Storage Limits • Response • PL/SQL block output
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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