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

Continuous Integration & Delivery (CD/CD) with Oracle Database and APEX

Continuous Integration & Delivery (CD/CD) with Oracle Database and APEX

With the rising popularity of DevOps, Oracle Database and APEX developers must evolve to embrace CI/CD pipelines for developing, testing, and deploying database code changes.

Today, many database developers struggle with the concept of using a central repository for storing, reviewing, and deploying code, where traditionally, all it took was a ZIP file of scripts and SYSDBA. This session will cover using Oracle’s free development tools combined with open source repository tools to create a full CI/CD pipeline for database and APEX development groups.

#DevOps #100daysofcode #CICD #Programming #Technology #Innovation #continuousintegration

Resources:
Oracle GitHub Repo for DevOps:
https://github.com/oracle/oracle-db-tools/tree/master/devops

YouTube Presentation:
https://youtu.be/P3La7ig37CU

0ed10d1154c696886ca483fe827cb299?s=128

thatjeffsmith

April 08, 2021
Tweet

Transcript

  1. CI/CD for Oracle Database & APEX Developers Brian Spendolini Product

    Manager Oracle Database Development Tools @btspendo https://blogs.oracle.com Join our Discord Server! oracledevs Jeff Smith Distinguished Product Manager Oracle Database Development Tools @thatjeffsmith https://www.thatjeffsmith.com
  2. What is CI/CD? Copyright © 2021, Oracle and/or its affiliates

    2
  3. CI/CD is continuous integration, continuous delivery, and continuous deployment •

    Introduces automation into all stages of app/database development • Helps developers work on the same app and merge their code changes back to a shared branch frequently • Development changes are automatically tested on push/commits and merges Why? • Consistency/Repeatability • Accountability • Security • Standardization • Find Issues Faster/Better Code/Quality Releases • More Frequent Releases What is CI/CD and Why do we need it? Copyright © 2021, Oracle and/or its affiliates 3
  4. Traditionality this has been hard. Why? • APEX/DB bucks the

    trend of isolated development environments • Dev instances can be expensive • Licensing issues • DB/APEX changes are different from traditional files/code seen with DevOPs and CI/CD processes • Metadata driven • Versioning or lack there of • Rollback issues • Stateful vs Stateless • Very manual process and a lot of individual accountability • DB change tracking • APEX change tracking CI/CD with the database and APEX? Copyright © 2021, Oracle and/or its affiliates 4
  5. What tools do I need to start? • Git/code repository

    • SQLcl/Liquibase • Jenkins Extra Credit • VS Code/SQL Developer • utPLSQL • Terraform • OCI account (free/paid) CI/CD with the database and APEX? Copyright © 2021, Oracle and/or its affiliates 5
  6. SQLcl and Liquibase Copyright © 2021, Oracle and/or its affiliates

    6 What’s SQLcl? Oracle SQLcl (SQL Developer Command Line) is a Java-based command-line interface for Oracle Database. Using SQLcl, you can execute SQL and PL/SQL statements interactively or as as a batch file. SQLcl provides inline editing, statement completion, command recall, and also supports existing SQL*Plus scripts. What’s Liquibase? Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. How do they work together? The Liquibase feature in SQLcl enables you to execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs).
  7. Liquibase and SQLcl Copyright © 2021, Oracle and/or its affiliates

    7 SQL> lb genobject -type table -name zipcodes SQL> lb genobject -type ords –name myModule Generates change logs for APEX, ORDS RESTful Service Modules, or database objects SQL> lb genschema Generates changelogs and controller file for the connected schema SQL> lb update –changelog controller.xml Applies the specified change log using the current connection. SQL> lb rollbacksql -changelog controller.xml -count 100 Writes SQL to roll back the database to the state requested.
  8. Copyright © 2021, Oracle and/or its affiliates 8

  9. Liquibase and SQLcl Copyright © 2021, Oracle and/or its affiliates

    9 DATABASECHANGELOG_DETAILS is a view that consolidates information from the DATABASECHANGELOG and DATABASECHANGELOG_ACTIONS tables
  10. Copyright © 2021, Oracle and/or its affiliates 10

  11. SQLcl and Liquibase and APEX Copyright © 2021, Oracle and/or

    its affiliates 11 SQL> lb genobject -type apex -applicationid 101 Creates a single f101.xml file for your application SQL> lb genobject -type apex -applicationid 101 -skipExportDate - expOriginalIds –split Creates multiple folders/files per component and an install file (like with the database) NOTE: Liquibase will only update the new/altered files when exporting so that your repository only reflects the changes SQL> lb update -changelog f101.xml To install the single file or SQL> lb update –changelog controller.xml To install the multiple files
  12. Copyright © 2021, Oracle and/or its affiliates 12

  13. SQLcl and Liquibase and Table Data Copyright © 2021, Oracle

    and/or its affiliates 13 SQL> lb data –object TABLE_NAME,TABLE_NAME... Exports the data from the indicated table(s) SQL> lb update -changelog data.xml Imports the data into the table(s) Great for metadata tables, not great for millions of rows.. (use this thing called ORDS for that)
  14. Liquibase and SQLcl Copyright © 2021, Oracle and/or its affiliates

    14 Roll your own! SQL> lb gencontrolfile Action successfully completed please review created file controller.xml SQL> !more controller.xml <?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <include file="{filename.xml}"/> ... </databaseChangeLog>
  15. • Terraform is a tool for building, changing, and versioning

    infrastructure safely and efficiently. • Think Infrastructure as code • Full set of APIs/Resources for OCI via Terraform • https://registry.terraform.io/providers/hashicorp/oci/latest/docs • Create and Destroy OCI resources quickly and consistently • Similar to OCI CLI Terraform and OCI Copyright © 2021, Oracle and/or its affiliates 15 resource "oci_database_autonomous_database" "new_autonomous_database" { #Required compartment_id = var.compartment_ocid cpu_core_count = "1" data_storage_size_in_tbs = "1" db_name = "TESTDB${random_string.db_name.result}" admin_password = random_string.password.result db_workload = "OLTP" display_name = "TESTDB${random_string.db_name.result}" }
  16. What’s a sample environment look like for CI/CD with the

    Autonomous database and OCI? Sample Environment Copyright © 2021, Oracle and/or its affiliates 16 Github OCI Virtual Cloud Network Public Subnet Compute VM with Jenkins Code Repository Jenkins Webhook Autonomous Database Internet Gateway OCI Resources Compute Load Balancer Object Store OCI Resources can be created via instance principles for the CI/CD process Network SQLcl Git Terraform Created on demand via Terraform Developers Compute VM with Spinnaker
  17. The Development Process Putting it all together Copyright © 2021,

    Oracle and/or its affiliates 17
  18. High Level Flow Copyright © 2021, Oracle and/or its affiliates

    18
  19. Development Copyright © 2021, Oracle and/or its affiliates 19

  20. Development Copyright © 2021, Oracle and/or its affiliates 20

  21. Copyright © 2021, Oracle and/or its affiliates 21

  22. Individual environments need • APEX (Apps and workspace) • DB

    Schema(s) • Code from the repository (latest) or deployment Automate the process with APIs • OCI CLI/PLSQL SDK • ORDS and the SQL Endpoint • Terraform Individual Environments and Creation Copyright © 2021, Oracle and/or its affiliates 22
  23. • Autonomous Database Clones • OCI DB VMs • Clone

    a VM DB • Create a new DB VM from a backup • Using OCI ExaCS • Sparse Cloning • Using Multi-tenancy • DB-APIs via ORDS • Using REST-Enabled SQL Service • Reusable Instance • Guaranteed Restore Points/Flashback Database • RMAN duplicate/clone • Datapump • Docker/Virtual Machines • ACFS/gDBClone Individual Environments and Creation Copyright © 2021, Oracle and/or its affiliates 23
  24. Every code pull request/merge (maybe push) should spawn a CI/CD

    pipeline The pipeline should • Clone from an environment with the latest version of main • Apply the developers branch/merged main into that database • Run unit tests • Report back on the status of the pipeline • Destroy the environment if it is successful/Keep it running if not successful CI Process for code pushes Copyright © 2021, Oracle and/or its affiliates 24
  25. Copyright © 2021, Oracle and/or its affiliates 25

  26. Pipeline Process Copyright © 2021, Oracle and/or its affiliates 26

  27. Copyright © 2021, Oracle and/or its affiliates 27

  28. • Pipelines can also be scheduled or started manually •

    Choose deployment target with pipelines • Deploy Manually • Zip up the repo and deploy when you want • Eliminate surprises by having a logical progression of environments • Dev • UAT • Production • Use ADB/DB clones to practice deployments manually or via pipelines Deployment Targets and Automating the Process Copyright © 2021, Oracle and/or its affiliates 28
  29. Deployment Copyright © 2021, Oracle and/or its affiliates 29

  30. Deployment Copyright © 2021, Oracle and/or its affiliates 30

  31. Copyright © 2021, Oracle and/or its affiliates 31

  32. Rollback Copyright © 2021, Oracle and/or its affiliates 32 Or

    Roll Forward? In some cases, it may be easier/better to fix the issue in production and bring the fix back to the main code line Constant testing/automation should help with these incidents
  33. Next Steps Where do we go from here? Copyright ©

    2021, Oracle and/or its affiliates 33
  34. Go Slow Cultivate an environment that encourages CI/CD Incorporate pieces/adopt

    what works for you and your teams All code is hosted in github (devops folder) https://github.com/oracle/oracle-db-tools Any issues let us know and the projects will be updated on a regular basis Oracle LiveLabs https://apexapps.oracle.com/pls/apex/dbpm/r/livelabs/home Demos and Labs to get you started in OCI and with the Oracle database Next Steps Copyright © 2021, Oracle and/or its affiliates 34
  35. Reach out directly Let us know about your journey and

    any issues/enhancements you would like to see Join our Discord Group! Next Steps Copyright © 2021, Oracle and/or its affiliates 35
  36. Copyright © 2021, Oracle and/or its affiliates 36