REST in Piece - Administration of an Oracle Cluster/Database using REST

BASEL | BERN | BRUGG | BUKAREST | DÜSSELDORF | FRANKFURT A.M. | FREIBURG I.BR. | GENF HAMBURG | KOPENHAGEN | LAUSANNE | MANNHEIM | MÜNCHEN | STUTTGART | WIEN | ZÜRICH Christian Gohmann • Principal Consultant at Trivadis Germany GmbH, Düsseldorf • Instructor since 2014 • O-AI (Oracle Architecture and Internals) • O-AI-DBA (Oracle Architecture and Internals for DBAs) • Tool Owner of TVD-Backup • Experiences with Oracle since 2006 (8i – 19c) • Architecture, Installation & Configuration • High Availability Solutions (RAC, Data Guardm, GoldenGate) • Migration Projects • Backup & Recovery (RMAN, Data Pump) • Cloud (Amazon, Oracle) @CHGohmannDE www.christian-gohmann.de

Agenda 1. What is REST? 2. Oracle REST Data Services (ORDS) 3. Database Management REST API 4. OraChk 5. Call REST Endpoint from PL/SQL

What is REST? • REST is the abbreviation for REpresential State Transfer and is a development paradigm for distributed systems • Architectural style for the modern World Wide Web (WWW) • Provides a uniform and easy-to-use interface using a Uniform Resource Locater (URI) • Interacts with resources rather than commands • No method information in the URI • HTTP calls are used to retrieve or manipulate resources • Uses the infrastructure (Web- and Application Server, HTML-/XML-Parser, …) of the WWW • Communication between client and server is stateless (HTTP, HTTPS) • Implementation can be changed independently • Only the request and output format must stay the same

HTTP Request Methods • Supported methods to be performed for a given resource, also referred as HTTP verbs Verb Description GET Requests a resource and has no side effects (safe) POST Adds a new resource or change an existing resource PUT If the resource does not exist, it will be created, otherwise it will be updated PATCH Updates a resource, side effects are okay DELETE Deletes a resource

Accessing REST API • The simplest way to call a REST API is to access its URI using a Web Browser • On Linux/Unix curl can be used to access it from the command line • The trailing / in the URI is mandatory • On Windows PowerShell can be used $> curl --silent --request "GET" --user "mydba:manager" "http://oaidba-lin:8080/ords/mydba/_/db-api/stable/database/db_links/" PS> $credentials = [System.Convert]::ToBase64String( [System.Text.Encoding]::ASCII.GetBytes("mydba:manager")) PS> Invoke-WebRequest -Method Get -Uri 'http://oaidba- lin:8080/ords/mydba/_/db-api/stable/database/db_links/' -Headers @{ "Authorization" = "Basic $credentials" }

Formatting JSON Response • Normally, curl returns the JSON response in a non-human-readable format • With an external tool like jq (command-line JSON processor) it is possible to format the response easily • Redirect the output of curl to jq $> yum install jq $> curl --silent --request "GET" --user "mydba:manager" "http://.../" | jq { "links": [ { "rel": "self", "href": "http://oaidba-lin.trivadistraining.com:8080/ords/_/db-api/stable/" }, { "rel": "describedby", "href": "http://oaidba-lin.trivadistraining.com:8080/ords/_/db-api/stable/metadata-catalog/" } ] }

Oracle REST Data Services (ORDS)

What is ORDS? • Middle Tier Java application which provides a REST API to interact with an Oracle Database • Provides Insert, Update, Delete functionalities for tables by using simple HTTP calls • Execution of PL/SQL code is also possible • No extra license is required, it is included in the Oracle Database license • Restricted-use license for WebLogic Standard edition for hosting ORDS Source: https://www.thatjeffsmith.com/oracle-rest-data-services-ords/

Installation 1/2 • Download from Oracle Technology Network (OTN) and unzip the file • https://www.oracle.com/database/technologies/appdev/rest.html • To create ORDS related database users, SYS or a user with appropriate privileges is required • SQL script ords_installer_privileges.sql can be used to grant the permissions • Call ords.war (Java 8 or higher is required) to start installation • You can add further databases $> java -jar ords/ords.war install advanced SQL> @ords/installer/ords_installer_privileges.sql myuser In the case of a CDB, all PDBs have to be in the OPEN state. $> java -jar ords/ords.war setup --database tvddb2 $> java -jar ords/ords.war map-url --type base-path /tvddb2 tvddb2

Installation 2/2 $> java -jar ords.war install advanced This Oracle REST Data Services instance has not yet been configured. Please complete the following prompts Enter the location to store configuration data: /u00/app/oracle/config Enter the name of the database server [localhost]:oaidba-lin.trivadistraining.com Enter the database listen port [1521]: Enter 1 to specify the database service name, or 2 to specify the database SID [1]: Enter the database service name:TVDDB1_SITE1.trivadistraining.com Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]: Enter the database password for ORDS_PUBLIC_USER: Confirm password: Requires to login with administrator privileges to verify Oracle REST Data Services schema. Enter the administrator username:sys Enter the database password for SYS AS SYSDBA: Confirm password: Retrieving information. Enter the default tablespace for ORDS_METADATA [SYSAUX]: Enter the temporary tablespace for ORDS_METADATA [TEMP]: Enter the default tablespace for ORDS_PUBLIC_USER [USERS]: Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2 Sep 09, 2019 7:32:38 PM INFO: reloaded pools: [] Installing Oracle REST Data Services version 19.2.0.r1991647 ... Log file written to /home/oracle/ords_install_core_2019-09-09_193239_00018.log ... Verified database prerequisites ... Created Oracle REST Data Services proxy user ... Created Oracle REST Data Services schema ... Granted privileges to Oracle REST Data Services ... Created Oracle REST Data Services database objects ... Log file written to /home/oracle/ords_install_datamodel_2019-09-09_193256_00470.log ... Log file written to /home/oracle/ords_install_apex_2019-09-09_193258_00555.log Completed installation for Oracle REST Data Services version 19.2.0.r1991647. Elapsed time: 00:00:21.662 Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2 Target Database In standalone mode, ORDS is started as foreground process

Standalone Mode • Start ORDS in Standalone Mode • For a production environment, HTTPS should be used • Use URL http://<hostname>:8080/ords to access ORDS • At this point ORDS shows an error 500 page • Configuration is written to $CONFIG_DIR/ords/standalone $> java -jar ords/ords.war standalone Enter 1 if using HTTP or 2 if using HTTPS [1]:1 Enter the HTTP port [8080]: 2019-09-09 19:44:34.247:INFO::main: Logging initialized @5586ms to org.eclipse.jetty.util.log.StdErrLog Sep 09, 2019 7:44:34 PM INFO: HTTP and HTTP/2 cleartext listening on port: 8080 … Use ampersand (&) to start it in the background.

Troubleshooting • Activate debugging to screen to see errors directly in the web browser $> java -jar ords.war set-property debug.printDebugToScreen true

Overview • Overview of REST APIs can be found in the REST APIs for Oracle Database documentation • Link: https://docs.oracle.com/en/database/oracle/oracle-database/19/dbrst/index.html • Endpoints are grouped by tasks • Data Dictionary Query Data Dictionary objects • Environment Create new databases, … • Fleet Patching and Provisioning Install new Oracle Homes, install patches, … • General Data Pump, Tablespace, Users, … • Monitoring Sessions, Alerts, … • Performance Execution Plans, Statistics, … • Pluggable Database Lifecycle Management Create / Drop / Clone / Unplug PDBs, …

Authentication • Two ways to authenticate users to access the Database Management API • Database authentication using username and password • Mid-tier authentication with System or SQL Administrator role • Required role depends on the REST endpoint • Roles, as well username and password are stored in $CONFIG_DIR/ords/credentials # For ODRS < 19.4 $> java -jar ords.war set-property jdbc.auth.enabled true # For ORDS >= 19.4 $> java -jar ords.war set-property restEnabledSql.active true Properties are written to the file $CONFIG_DIR/defaults.xml. $> java -jar ords.war user REST_API "System Administrator, SQL Administrator"

Configuration 1/3 • Available starting with ORDS 19.1 • Per default it is disabled, a 404 (Page not found) error page is displayed when you try to access the API • To activate it, property database.api.enabled must be changed to TRUE • Configure authentication method (see slide before) $> java -jar ords.war set-property database.api.enabled true After changing a property, ORDS has to be restarted.

Configuration 2/3 • A database user with PDB_DBA role (even in a non-CDB database) must be use • Schema must be activated for ORDS • If SYS/SYSTEM is used to run ORDS.ENABLE_SCHEMA, ORA-06598 is raised SQL> GRANT PDB_DBA TO REST_API; SQL> GRANT INHERIT PRIVILEGES ON USER SYSTEM TO ORDS_METADATA; SQL> BEGIN ORDS.ENABLE_SCHEMA( p_schema => 'REST_API', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'rest-api', p_auto_rest_auth => FALSE, p_enabled => TRUE ); END; / Part of the URL Workaround for ORA-06598

Configuration 3/3 • For non-database authentication, grant SQL Administrator to the ORDS enabled user • If role System Administrator is used only, access to REST endpoints will fail $> java -jar ords.war user REST_API "SQL Administrator" REST_API is not authorized to access: oracle.dbtools.ords.db-api.developer REST_API does not have any of the required roles: PrivilegeConstraintImpl [name=oracle.dbtools.ords.db-api.developer, roles=[SQL Administrator, SQL Developer], challenges=[]]

Environment Services • Allows interaction with the environment • List installed Oracle homes or existing databases • Create new DBs using DBCA • Requires ORDS 19.2 or higher • Create mid-tier user with System Administrator role • URIs does not include a username part • Roles are stored in the $CONFIG_DIR/ords/credentials file $> java -jar ords.war user REST_API "System Administrator" Username for login is case sensitive - write username in uppercase.

Pluggable Database Lifecycle Management • Allows create, clone, unplug/plug-in and delete operations for PDB • A common user with SYSDBA privilege is required • Enabling a schema for ORDS is not supported in a CDB • Instead, CDB credentials are defined in the connection pool • Set SQL Administrator role for the user $> vi cdbAdmin.properties db.cdb.adminUser=C##REST_API as SYSDBA db.cdb.adminUser.password=manager $> java -jar ords.war set-properties --conf apex_pu cdbAdmin.properties SQL> CREATE USER C##REST_API IDENTIFIED BY manager; SQL> GRANT SYSDBA TO C##REST_API CONTAINER = ALL; $> java -jar ords.war user C##REST_API "SQL Administrator"

Accessing the API • Use following URL pattern to access the Database REST API • Hostname Name of the server hosting ORDS • Port Default port for HTTP is 8080 and for HTTPS 8443 • URL Mapping Used mapping for the REST enabled DBA schema • Version Access different versions of the API, e.g. 19.1.0, 19.2.0 or latest / stable http(s)://<Hostname>:<Port>/ords/<URL Mapping>/_/db-api/<Version> Use latest to access always the latest version of the API.

Swagger Test Client • Metadata Catalog and OpenAPI endpoints exist • JSON output can be used in editor.swagger.io to generate a full test client http(s)://.../_/db-api/latest/metadata-catalog/openapi.json

List installed Oracle Homes $> curl --silent --request GET –header "Content-Type: application/json" --user "rest_api:manager" http://oaidba-lin:8080/ords/rest-api/_/db-api/latest/environment/homes/ Response { items: [ { name: "OraGI19Home1", version: "19.4.0.0.0", default: false, read_only_home: false, ... }, { name: "OraDB19Home1", version: "19.4.0.0.0", ... } ] }

Database Status $> curl --silent --request GET –header "Content-Type: application/json" --user "rest_api:manager" http://oaidba-lin:8080/ords/rest-api/_/db-api/19.2.0/database/feature_usage/ Response { inst_id: 1, instance_number: 1, instance_name: "TVDDB1", host_name: "oaidba-lin.trivadistraining.com", version: "19.0.0.0.0", version_legacy: "19.0.0.0.0", version_full: "19.4.0.0.0", ... } In the background gv$instance is queried.

Data Pump - Export $> curl --silent --request POST --data-binary @expdp.json –header "Content-Type: application/json" --user "rest_api:manager" http://oaidba-lin:8080/ords/rest-api/_/db-api/latest/database/datapump/export $> vi expdp.json { "datapump_dir": "DATA_PUMP_DIR", "filter": "SCOTT", "job_mode": "SCHEMA" } Request Body { "job_name":"DATAPUMP_REST_EXPORT_20190910215902", "owner_name":"REST_API", "operation":"EXPORT", "job_mode":"SCHEMA", "state":"EXECUTING", "degree":1, "attached_sessions":0, "datapump_sessions":2, "job_state":"EXECUTING“ ... } Response Master Table is not deleted after the job finished

Create Pluggable Database $> curl --silent --request POST --data-binary @create_pdb.json –-header "Content-Type: application/json" --user "rest_api:manager" http://oaidba-lin:8080/ords/_/db-api/latest/database/pdbs/ $> vi create_pdb.json { "method": "CREATE", "pdb_name": ”NEWPDB1", "adminName": "pdbadmin", "adminPwd": "HelloPDB!", "fileNameConversions": "NONE", "unlimitedStorage": true, "reuseTempFile": true, "totalSize": "UNLIMITED", "tempSize": "UNLIMITED" } Request Body { "env": { "defaultTimeZone": "Europe/Berlin" }, "items": [ { "statementId": 1, "response": [ "nPL/SQL procedure successfully completed.nn" ], "result": 0 }, … ] } Response

Using ORAchk over REST • Configure ORDS support for ORAchk (run as root user) • Start ORAchk daemon • Setup asks for a password for ORDSADMIN user • Example URL: https://oaidba-lin.trivadistraining.com:7080/ords/tfaml/orachk • To remove ORDS support use -ordsrmsetup $> ./orachk –ordssetup # Integration into an existing ORDS setup $> ./orachk -ordssetup /u00/app/stage/ords -configdir /u00/app/oracle/config $> ./orachk -ordsrmsetup $> ./orachk -d start -ords

Overview of REST Endpoints • Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/atnms/using- orachk-or-exachk-over-rest.html#GUID-45CA5280-B10A-42C5-8C90-7782589E4570

Start Health Check $> curl --silent --request GET –header "Content-Type: application/json" --user "ordsadmin:manager" https://oaidba-lin.trivadistraining.com:7080/ords/tfaml/orachk/start_client Response { "ID":"UCTW5MLN7O1V1HPG8U", "Status":"SUBMITTED" } To provide special parameters to the Health Check, the POST request must be used.

Call REST Endpoint from PL/SQL

Example 1/2 • Creates a PDB using PL/SQL SQL> SET serveroutput ON SQL> DECLARE -- Internal variables used to call the REST endpoint v_request UTL_HTTP.REQ; v_response UTL_HTTP.RESP; -- URL to REST endpoint v_url VARCHAR2(4000 CHAR) := 'http://.../ords/_/db-api/stable/database/pdbs/'; -- Content body required for the REST endpoint v_body VARCHAR2(4000 CHAR) := q'[{ "method": "CREATE", "pdb_name": "PLSQLPDB1", "adminName": "pdbadmin", "adminPwd": "HelloPDB!", "fileNameConversions": "NONE", "unlimitedStorage": true, "reuseTempFile": true, "totalSize": "UNLIMITED", "tempSize": "UNLIMITED" }]'; ... Set network ACLs to allow communication with the target server.

Example 2/2 ... BEGIN -- Begin HTTP request v_request := UTL_HTTP.BEGIN_REQUEST(url => v_url, method => 'POST'); -- Set header information UTL_HTTP.SET_HEADER(r => v_request, name => 'content-type', value => 'application/json'); UTL_HTTP.SET_HEADER(r => v_request, name => 'content-length', value => LENGTH(v_body)); -- Authenticate with the REST endpoint UTL_HTTP.SET_AUTHENTICATION( r => v_request, username => 'doag', password => 'manager' ); -- Write body UTL_HTTP.WRITE_TEXT(r => v_request, data => v_body); -- Get response v_response := UTL_HTTP.GET_RESPONSE(r => v_request); -- Print status code DBMS_OUTPUT.PUT_LINE('Status Code: ' || v_response.STATUS_CODE); END; /

REST in Piece - Administration of an Oracle Cluster/Database using REST

REST in Piece - Administration of an Oracle Cluster/Database using REST

More Related Content

What's hot

Similar to REST in Piece - Administration of an Oracle Cluster/Database using REST

Recently uploaded

In this document

REST in Piece - Administration of an Oracle Cluster/Database using REST