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

Transcript

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

   View Slide

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

   View 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 Slide

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

    View Slide

22. View Slide