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

Oracle Database Change Management (CI/CD)

thatjeffsmith
September 23, 2021

Oracle Database Change Management (CI/CD)

With the rising popularity of DevOps, database developers must evolve to use proper change management when deploying database code across the enterprise. In this session, we will be going over how to incorporate database change management into your development lifecycle. We will take a deep dive into using Liquibase in SQLcl for change management, database migration, rollbacks, and the inner workings of the tooling.

This session will cover:
• Using SQLcl and Liquibase for Database Change Management
• A deep dive into the Liquibase commands available in SQLcl
• Data Movement with Liquibase
• Examination of the files that Liquibase exports
• A look at the change tracking tables created and the content within

VIDEO DEMO HERE:
https://www.youtube.com/watch?v=o-_mdtacxgg

thatjeffsmith

September 23, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Technology

Transcript

  1. Database Change Management
    Jeff Smith
    Distinguished Product Manager
    Brian Spendolini
    Product Manager

    View full-size slide

  2. Copyright © 2021, Oracle and/or its affiliates
    SQL Developer Data Modeler ORDS
    SQLcl Database Actions
    Oracle Database Developer Tools

    View full-size slide

  3. 9/22/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    3
    • Stateful vs Stateless
    • Versioning or lack there of
    • Roll back or roll forward?
    • Missing changes (manual scripting)
    • Testing Environments
    • DBAs/Database Developers are sometimes working in isolation
    • Possible code over-writes when multiple developers are working on the same task/object
    Why is Database Change Management Difficult?

    View full-size slide

  4. 9/22/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    4
    • Ability to capture all changes at once per developer/environment
    • Source Control/Code Repository
    • Adequate test and development databases
    • Tasks assigned so over-writing of code is avoided
    • Smaller, shorter-lived tasks
    • Using the Agile method or versions of it
    • Tracking of changes visible in the target system with ability to rollback
    Is Database Change Management Possible? Yes it is.

    View full-size slide

  5. “If NOTHING ELSE, please start putting
    your database code – that’s DDL, PL/SQL,
    APEX Apps, REST APIs, etc into a Source
    Control System.”
    Jeff & Brian
    Several times a day, every day…
    9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    5

    View full-size slide

  6. 9/22/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    6
    • Standardization
    • Consistency
    • Repeatability
    • Accountability
    • Security
    • Find Issues Faster
    • Better Code
    • Quality Releases
    • More Frequent Releases
    Why should I even consider doing this?

    View full-size slide

  7. Copyright © 2021, Oracle and/or its affiliates
    7
    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. SQLcl is also in the OCI Cloud
    Shell by default.
    What’s Liquibase?
    Liquibase is an open-source database-independent library for tracking, managing
    and applying database schema changes.
    How do they work together?
    We extended the Liquibase community distribution to better support Oracle Database AND
    baked it into SQLcl. This enables you to execute commands to generate a
    changelog for a single object or for a full schema (changeset and changelogs).
    SQLcl handles dependency ordering AND Rollback Scenarios, FOR YOU.
    SQLcl and Liquibase

    View full-size slide

  8. 9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    8
    LB GENOBJECT - Generates change logs for APEX, ORDS RESTful Service Modules, or database objects
    LB GENSCHEMA - Captures all the supported objects in the entire schema that the user is connected to in SQLcl.
    Important flags to remember: -split and –sql (new in 21.3)
    LB GENCONTROLFILE - Creates an empty changelog. Useful if you want to roll our own (more on this later).
    SQLcl and Liquibase – Important Commands
    SQL> lb genobject -type table -name zipcodes
    SQL> lb genobject -type ords –name myModule
    SQL> lb genobject -type apex -applicationid 101
    SQL> lb genschema –split -sql
    SQL> lb gencontrolfile

    View full-size slide

  9. 9/22/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    9
    SQLcl and Liquibase – Objects Supported
    AQ_QUEUE AQ_QUEUE_TABLE AQ_TRANSFORM
    ASSOCIATION AUDIT AUDIT_OBJ
    CLUSTER CONSTRAINT CONTEXT
    DB_LINK DEFAULT_ROLE DIMENSION
    FGA_POLICY FUNCTION INDEX
    JOB LIBRARY MATERIALIZED_VIEW
    MATERIALIZED_VIEW_LOG OBJECT_GRANT OPERATOR
    PACKAGE_SPEC PACKAGE_BODY PROCEDURE
    PROFILE PROXY PUBLIC_SYNONYM
    REF_CONSTRAINT REFRESH_GROUP RESOURCE_COST
    RLS_CONTEXT RLS_GROUP RLS_POLICY
    RMGR_CONSUMER_GROUP RMGR_PLAN RMGR_PLAN_DIRECTIVE
    ROLE ROLLBACK_SEGMENT SEQUENCE
    SYNONYM TABLE TABLESPACE
    TRIGGER TRUSTED_DB_LINK TYPE
    TYPE_SPEC TYPE_BODY USER
    VIEW XMLSCHEMA XS_USER XS_ROLE
    XS_ROLESET XS_ROLE_GRANT XS_SECURITY_CLASS
    XS_DATA_SECURITY XS_ACL XS_ACL_PARAM
    XS_NAMESPACE RMGR_INTITIAL_CONSUMER_GROUP

    View full-size slide

  10. Copyright © 2021, Oracle and/or its affiliates
    10
    SQL> lb gencontrolfile
    Action successfully completed please review created file controller.xml
    SQL> !more controller.xml

    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">

    ...
    ...

    Liquibase and SQLcl – Roll your Own Control File

    View full-size slide

  11. 9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    11
    LB UPDATE – Apply the updates that are in the change log file. (controller.xml, table.xml, etc)
    LB UPDATESQL - Generates the SQL statements to the SQLcl screen that would be applied for a specific change log
    file.
    LB ROLLBACK – Rolls back some or all of the changes in the provided changelog file
    SQLcl and Liquibase – Important Commands
    SQL> lb update –changelog controller.xml
    SQL> lb update –changelog my_table.xml
    SQL> lb updatesql -changelog my_table.xml
    SQL> lb updatesql -changelog data.xml
    SQL> lb rollback -changelog controller.xml -count

    View full-size slide

  12. 9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    12
    LB ROLLBACKSQL – Similar to updatesql, this writes the roll back SQL to the SQLcl screen for some or all of the
    changes.
    LB DIFF - Displays differences between the current connected schema and the specified remote/local database
    schema.
    LB DBDOC - Generates Javadoc-like documentation based on the current database and change log.
    SQLcl and Liquibase – Important Commands
    SQL> lb rollbacksql -changelog controller.xml -count 100
    SQL> lb diff -url localhost:1521/ORCLPDB1 -user hr -password hr
    SQL> lb dbdoc -changelog my_table.xml –dir ./docs

    View full-size slide

  13. 9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    13
    ROLLBACKSQL – show me what you’re going to do, before you do it

    View full-size slide

  14. 9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    14
    DIFF – Show me what’s different between two environments/changelogs

    View full-size slide

  15. 9/22/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    15
    LB CHANGELOGSYNC - Writes the SQL statements to mark all changes in the change log as executed in the
    database to a file.
    LB CLEARCHECKSUMS - Removes current checksums from the database.
    LB LISTLOCKS - Lists who currently has locks on the database change log. (So you can kill their sessions!)
    LB RELEASELOCKS - Releases all locks on the database change log. (If killing their session didn’t work)
    LB STATUS – Creates a list of change sets that have not been applied to the database in the supplied changelog.
    LB VALIDATE - Checks the supplied changelog for errors.
    SQLcl and Liquibase – Advanced Commands (session/locks)

    View full-size slide

  16. Copyright © 2021, Oracle and/or its affiliates
    16
    Export the module (ORDS REST APIs are organized by Modules)
    And to install
    It’s just that simple…..
    SQLcl and Liquibase - ORDS
    SQL> lb genobject -type ords -name myModule
    SQL> lb update –changelog myModule.xml

    View full-size slide

  17. Copyright © 2021, Oracle and/or its affiliates
    17
    Creates a single f101.xml file for your application
    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
    Important Flags for change management:
    -skipExportDate -expOriginalIds -split
    To deploy the single file or
    To deploy multiple files
    SQLcl and Liquibase - APEX
    SQL> lb genobject -type apex -applicationid 101
    SQL> lb genobject -type apex -applicationid 101 –split
    SQL> lb update -changelog f101.xml
    SQL> lb update –changelog controller.xml

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  20. SQLcl & Liquibase in Action – SEE the YouTube Video!
    Also Hot-Linked in the Slide Description
    https://www.youtube.com/watch?v=o-_mdtacxgg
    Demo
    9/23/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    20

    View full-size slide

  21. 9/22/2021
    Copyright © 2021, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
    21
    Thank You

    View full-size slide